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.

Markov chain analysis

The Markov chain statistical function uses probabilistic methods to assign credit across advertising touchpoints based on their modeled contribution to a user's likelihood to convert. The output of this experimental function may be useful in assigning credit to a given advertising channel, campaign, or other touchpoint, based on their modeled contribution to conversion events

How it works

The Markov chain statistical function uses your advertising data to create a Markov chain, where each vertex in the ordered graph represents a touchpoint and each edge gives the probability of moving to that next touchpoint, conditional on being at that current touchpoint. It assumes that only the current touchpoint affects the transition probability. The contribution of each touchpoint is then computed by removing the touchpoint from the graph, and calculating the modeled probability of a conversion now that the touchpoint is removed.

Privacy restrictions

Touchpoints must include 50 or more converting users and 50 or more non-converting users to not be removed by privacy filters. Additionally, outlier users that contribute a disproportionate amount of credit to a touchpoint may be filtered. Thus, the output from the Markov chain model may be missing some touchpoints that are in the input touchpoints table.

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

Overview of computing Markov chain 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. Events that follow conversions are considered non-conversion events.

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

Output table

The output table will contain the following schema:

Column name	Type
`touchpoint`	`string` Touchpoint name.
`score`	`integer` Calculated Markov chain 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 
 , 
  
 'MARKOV_CHAINS' 
 )