# Shapley value analysis

The Shapley value method is an algorithm that assigns credit to numerous advertising channels and touchpoints based on their modeled contribution to conversion. Using the Shapley value method, you can model the contribution that a particular channel has on conversion.

Ads Data Hub uses the "Simplified Shapley Value Method", explained in full detail in the [Shapley Value Methods for Attribution Modeling in
Online Advertising](https://arxiv.org/pdf/1804.05327.pdf) paper.

> [!NOTE]
> **Note:** The Shapley value model can only be used with Data Transfer-based tables: `cm_dt_*`, `dv360_dt_*`.

## Privacy restrictions

Privacy filters will remove touchpoints with fewer than 50 users and outlier users that contribute a disproportionate amount of credit to a touchpoint. Thus, the output from the Shapley value model may be missing some touchpoints that are in the input touchpoints table.

<br />

Privacy messages are shown after each iteration of the Shapley value model. These messages include information on users and touchpoints that were filtered.

## Overview of computing Shapley value values

1. Create the touchpoint and credit tables:
   1. `touchpoint_temp_table`.
   2. `user_credit_temp_table`.
2. Call the `ADH.TOUCHPOINT_ANALYSIS` table-valued function using the temp tables above as arguments.

## Create the touchpoint and credit tables

### Create the touchpoint table

The touchpoint table is where user events related to touchpoints are defined. Example data may include, but isn't limited to: `campaign_id` , `creative_id`, `placement_id`, or `site_id`.

> [!NOTE]
> **Note:** Shapley value analysis is currently limited to maximum of 150 touchpoints.

The table must contain the following columns:

| Column name | Type |
|---|---|
| `touchpoint` | `string` Arbitrary touchpoint name. (Must not be NULL or contain commas.) |
| `user_id` | `string` The id of a user who visits the touchpoint. (Must not be NULL or 0.) |
| `event_time` | `int` The time that the user visited the touchpoint. (Must not be NULL.) |

> [!CAUTION]
> **Caution:** The query will fail if the total number of distinct touchpoints is greater than 150, or if a `user_id` isn't present in the `user_credit_temp_table`.

Sample code for creating the table:

    CREATE TABLE touchpoint_temp_table
    AS (
      SELECT user_id, event.event_time, CAST(event.site_id AS STRING) AS touchpoint
      FROM adh.cm_dt_impressions
      WHERE
        event.event_type IN ('VIEW')
        AND user_id <> '0'
        AND event.campaign_id IN UNNEST(@campaign_ids)

      UNION ALL

        SELECT
          user_id, event.event_time, CAST(event.site_id AS STRING) AS touchpoint
        FROM adh.cm_dt_clicks
        WHERE
          event.event_type IN ('CLICK')
          AND user_id <> '0'
          AND event.campaign_id IN UNNEST(@campaign_ids)
    );

### Create the user credit table

The user credit table is where conversion events are defined.

For each user, only events with a timestamp prior to the conversion are considered.


The table must contain the following columns:

| Column name | Type |
|---|---|
| `user_id` | `string` The id of a user who visits the touchpoint. (Must not be NULL or 0.) |
| `event_time` | `int` The time when the contribution event happened. (Must not be NULL.) |
| `credit` | `integer` The credit contributed by the user. It can be any credit one would like to analyze. For example, the conversion value, the number of conversions, etc. It must be between 1 and 100. |

> [!NOTE]
> **Note:** If a single user has multiple conversion events, upload them as you would any other events. Ensure that no events use a duplicative pair of `user_id` and `event_time`.

Sample code for creating the table:


    CREATE TABLE user_credit_temp_table AS (
      SELECT
        user_id,
        MAX(event.event_time) AS event_time,
        1 AS credit
      FROM adh.cm_dt_activities_attributed
      WHERE user_id <> '0'
        AND event.campaign_id IN UNNEST(@campaign_ids)
        AND DATE(TIMESTAMP_MICROS(event.event_time)) BETWEEN @start_date AND @end_date
        AND event.activity_id IN UNNEST (@activity_ids)
      GROUP BY user_id
    );

## The table-valued function

The table-valued function is a function that returns a table as a result. As such, you can query the table-valued function as you would a normal table.

### Syntax

    ADH.TOUCHPOINT_ANALYSIS(TABLE touchpoints_tmp_table_name, TABLE credits_tmp_table_name, STRING model_name)

### Arguments

| Name |   |
|---|---|
| `touchpoints_tmp_table_name` | The name of the client-created temp touchpoint table. The table is required to have schema which contains the columns of `touchpoint`, `user_id`, and `event_time`. |
| `credits_tmp_table_name` | The name to the client-created temp user credit table. The table is required to have schema which contains the columns `user_id`, `credit`, and `conversion_time`. |
| `model` | `string` Must be SHAPLEY_VALUES. |

### Output table

The output table will contain the following schema:

| Column name | Type |
|---|---|
| `touchpoint` | `string` Touchpoint name. |
| `score` | `integer` Calculated Shapley value score for this touchpoint. |

### Sample code for using the table-valued function

    SELECT *
    FROM ADH.TOUCHPOINT_ANALYSIS(
      TABLE tmp.touchpoint_temp_table,
      TABLE tmp.user_credit_temp_table,
      'SHAPLEY_VALUES')