limata3

SELECT
    customer_id
    , touchpoint_id
    , lead_id
    , order_id
    , touchpoint_channel
    , touchpoint_campaign
    , touchpoint_datetime
    , RANK() OVER (PARTITION BY customer_id ORDER BY touchpoint_datetime ASC, rand() ) AS touchpoints_customer_rank_asc /* touchpoint -> customer */
    , RANK() OVER (PARTITION BY customer_id ORDER BY touchpoint_datetime DESC, rand() ) AS touchpoints_customer_rank_des /* touchpoint -> customer */    

limata3

/* 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 */
;    

limata3

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