Shapley value analysis

Stay organized with collections Save and categorize content based on your preferences.

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 paper.

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.

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.

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.)

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.

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')