Distributing Credit amongst the different touchpoints for several attribution models

cazoo_attrb_modelling_code.sql

/* First Touch Attribution */
SELECT
    customer_id
    , order_id
    , touchpoint_id
    , CAST(MAX(total_net_orders) OVER (PARTITION BY buying_period_id) AS FLOAT64) AS first_touchpoint_credit
FROM master_ranked_customer_tocuhpoints
WHERE
    touchpoints_customer_order_rank_asc = 1 /* This ensures all credit goes to the first touchpoint */
;    

/* Last Touch Attribution */
  SELECT
    customer_id
    , order_id
    , touchpoint_id
    , CAST(MAX(total_net_orders) OVER (PARTITION BY buying_period_id) AS FLOAT64) AS last_touchpoint_to_order_credit 
  FROM master_ranked_customer_tocuhpoints
  WHERE
      touchpoints_customer_order_rank_desc = 1 /* This ensures all credit goes to the last touchpoint */
;

/* Last Touch to Lead Attribution */
SELECT
    customer_id
    , order_id
    , touchpoint_id
    , CAST(MAX(total_net_orders) OVER (PARTITION BY buying_period_id) AS FLOAT64) AS last_touchpoint_to_lead_credit
FROM master_ranked_customer_tocuhpoints
WHERE
    touchpoints_customer_lead_order_rank_desc = 1 /* This ensures all credit goes to the last touchpoint before becoming a lead */
;

/* Linear Attribution */
SELECT
    customer_id
    , order_id
    , touchpoint_id
    , CAST(MAX(total_net_orders) OVER (PARTITION BY buying_period_id) AS FLOAT64) / COUNT(touchpoint_id) OVER (PARTITION BY customer_id, order_id) AS linear_attribution_to_order_credit
FROM master_ranked_customer_tocuhpoints
/* No need for where clause because all touchpoints will receive some credit */
;