This is legacy documentation, and may not be complete. To see the latest documentation, if you are a marketer, refer to the Marketers site. If you are a measurement partner, refer to the Measurement Partners site.

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

Create the touchpoint and credit tables:
1. touchpoint_temp_table .
2. user_credit_temp_table .
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' 
 )