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.

Note: The Markov chain model can only be used with Data Transfer-based tables: cm_dt_*, dv360_dt_*.

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

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: Markov chain 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: 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. 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.

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