From Customer Touchpoints Table into Attributed Conversion Table

cazoo_attribution_model_sql_code.sql

/* We will only be offering code samples for the original attribution models (we won;t have the to_lead and non_direct variations in here */
/* Note: We are using WITH statements in here, but in a real life example you would want to create actual tables for each step instead having each step as a WITH statement */
/* Note: This is based on BigQuery SQL dialect */

/* This starts with our customer touchpoints table - filtered by Customer XYZ only though */
WITH master_customer_touchpoints_and_ranking AS (
    SELECT 
        /* Master Customer Touchpoints Table Original Fields */
        customer_id
        , device_id
        , touchpoint_id
        , touchpoint_date
        , touchpoint_channel
        , touchpoint_is_lead
        , touchpoint_is_order 
        /* Ranking Customer Touchpoints */
        /* Note, this dataset is already filtered out of touhcpoints that happened after the order - if it weren't we couldn't just partition by customer_id, we would need order_id and other columns as well for example */
        , RANK() OVER (PARTITION BY customer_id ORDER BY touchpoint_id ASC) AS touchpoints_customer_order_rank_asc /* touchpoint -> customer, order */
        , RANK() OVER (PARTITION BY customer_id ORDER BY touchpoint_id DESC) AS touchpoints_customer_order_rank_desc /* touchpoint -> customer, order */
    FROM master_customer_touchpoints
    ORDER BY touchpoint_id
),

/* Now we will create one attribution model at a time*/

/* First Touch Attribution */
first_touch_attribution AS (
SELECT
    customer_id
    , touchpoint_id
    , CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) AS first_touchpoint_credit
FROM master_customer_touchpoints_and_ranking
WHERE
    touchpoints_customer_order_rank_asc = 1 /* This ensures all credit goes to the first touchpoint */
),    

/* Last Touch Attribution */
last_touch_attribution AS (
  SELECT
    customer_id
    , touchpoint_id
    , CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) AS last_touchpoint_to_order_credit 
  FROM master_customer_touchpoints_and_ranking
  WHERE
    touchpoints_customer_order_rank_desc = 1 /* This ensures all credit goes to the last touchpoint */
),

/* Linear Attribution */
linear_attribution AS (
    SELECT
        customer_id
        , touchpoint_id
        , CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) / COUNT(touchpoint_id) OVER (PARTITION BY customer_id) AS linear_attribution_to_order_credit
    FROM master_customer_touchpoints_and_ranking
    /* No need for where clause because all touchpoints will receive some credit */
),

/* Position Based Attribution */
position_based_attribution AS (
    SELECT
    customer_id
    , touchpoint_id
    , CASE
      WHEN COUNT(touchpoint_id) OVER (PARTITION BY customer_id) = 1 THEN CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) * 1.0 /*If Only one touchpoint in journey give it all the credit*/
      WHEN COUNT(touchpoint_id) OVER (PARTITION BY customer_id) = 2 THEN CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) * 0.5 /*If Two touchpoints in journey: 50/50 for first and last*/
      WHEN COUNT(touchpoint_id) OVER (PARTITION BY customer_id) > 2 THEN /*If more than two touchpoints in journey:*/
        CASE 
          WHEN touchpoints_customer_order_rank_asc = 1 THEN CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) * 0.4 /*40% for first touchpoint*/
          WHEN touchpoints_customer_order_rank_desc = 1 THEN CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) * 0.4 /*40% for last touchpoint*/
          ELSE CAST(ANY_VALUE(1.0) OVER (PARTITION BY customer_id) AS FLOAT64) * 0.2 / ( COUNT(touchpoint_id) OVER (PARTITION BY customer_id) - 2) /*20% for the touchpoints in between*/
        END
      ELSE 0.0  
    END AS position_based_to_order_credit
FROM master_customer_touchpoints_and_ranking
/* No need for where clause because all touchpoints will receive some credit */
)
/* Table now containing all customer touchpoints and how much credit they have been attributed in each attribution model i.e. attributed_conversions table*/
SELECT 
    t.*
    , COALESCE(ft.first_touchpoint_credit, 0) AS first_touchpoint_credit
    , COALESCE(lt.last_touchpoint_to_order_credit, 0) AS last_touchpoint_to_order_credit
    , COALESCE(l.linear_attribution_to_order_credit, 0) AS linear_attribution_to_order_credit
    , COALESCE(pb.position_based_to_order_credit, 0) AS position_based_to_order_credit
FROM
    master_customer_touchpoints_and_ranking t
    LEFT JOIN first_touch_attribution ft ON ft.touchpoint_id = t.touchpoint_id  AND ft.customer_id = t.customer_id
    LEFT JOIN last_touch_attribution lt ON lt.touchpoint_id = t.touchpoint_id  AND lt.customer_id = t.customer_id
    LEFT JOIN linear_attribution l ON l.touchpoint_id = t.touchpoint_id  AND l.customer_id = t.customer_id
    LEFT JOIN position_based_attribution pb ON pb.touchpoint_id = t.touchpoint_id  AND pb.customer_id = t.customer_id 
;


/* Then if you want to understand how much each channel has been attributed in total for each attribution model you would run the query below on the attributed_conversions table */
SELECT 
    touchpoint_channel AS channel
    , SUM(first_touchpoint_credit) AS first_touchpoint_credit
    , SUM(last_touchpoint_to_order_credit) AS last_touchpoint_to_order_credit
    , SUM(linear_attribution_to_order_credit) AS linear_attribution_to_order_credit
    , SUM(position_based_to_order_credit) AS position_based_to_order_credit
FROM 
    attributed_conversions   
GROUP BY 1
ORDER BY 1     
;