attribution.sql

gistfile1.txt

{{ config( 
        tags=["unit_test", "ootb_attribution", "attribution_test"],
        materialized='table',
        enabled=true
) 
}}



with 

--input
    activity_events as (

            select * from {{ ref("activity_events") }} 

    )

    , conversion_events as (

            select * from {{ ref("conversion_events") }}

    )

--logic
    ,before_conversion as (
        select
              s.case_id      
            , s.companyname
            , s.timestamp_utc       
            , to_date(cast(s.timestamp_utc as TEXT),'YYYY-MM-DD')                                   as session_date      
            , to_date(cast(c.converted_at as TEXT),'YYYY-MM-DD')                                    as conversion_date
            
            
            , s.source                                                  as utm_source                  
            , s.medium                                                  as utm_medium                  
            , s.campaign                                                as utm_campaign                
            , c.converted_at     
            , c.event_name       
            , c.revenue          
            , s.session_id                                              as session_id                  

            , row_number() over(
                partition by
                    s.case_id
                    , c.event_name
                    , c.converted_at 
                order by 
                    s.timestamp_utc
            )                                                           as session_idx                 

            -- DISTINCT is not implemented for window functions in Postgres
            -- , count(distinct session_id) over(
            , count(session_id) over(
                partition by
                    s.case_id
                    , c.event_name
            )                                                           as sessions_before_conversion  

        from 
            activity_events s
        left join
            conversion_events c
        using 
            (case_id)

        where 
            1=1
            and s.timestamp_utc <= c.converted_at

            and s.timestamp_utc >= c.converted_at - interval '60' day -- conversion window
    )

    , touch_points as (
        select
            case_id                    
            , companyname                
            , timestamp_utc              
            , session_date                 
            , utm_source                 
            , utm_medium                 
            , utm_campaign               
            , converted_at               
            , event_name                 
            , revenue                    
            , session_idx                
            , sessions_before_conversion 

            , case
                when sessions_before_conversion = 1           then 1.0
                when sessions_before_conversion = 2           then 0.5
                when session_idx = 1                          then 0.4
                when session_idx = sessions_before_conversion then 0.4
                else 0.2 / (sessions_before_conversion - 2)
            end
                                                                        as forty_twenty_forty_points

            , case
                when session_idx = 1 then 1.0
                else 0.0
            end
                                                                        as first_touch_points

            , case
                when session_idx = sessions_before_conversion then 1.0
                else 0.0
            end
                                                                        as last_touch_points

            , 1.0 / sessions_before_conversion
                                                                        as linear_points

            , conversion_date
            -- , week_start
            -- , date_month

        from
            before_conversion
    )

--final
-- 1. Summarise booking counts attributed to campaign source, medium and name on both a first click and last click basis.
select
      case_id                    
    , companyname                
    , timestamp_utc              
    , session_date               
    , utm_source                 
    , utm_medium                 
    , utm_campaign               
    , converted_at               
    , conversion_date            
    , event_name                 
    , revenue                    
    , session_idx                
    , sessions_before_conversion 
    , first_touch_points         
    , last_touch_points          
    , forty_twenty_forty_points  
    , linear_points              
    , (revenue * first_touch_points)                                    as first_touch_revenue    
    , (revenue * last_touch_points)                                     as last_touch_revenue     
    , (revenue * forty_twenty_forty_points)                             as u_shape_revenue        
    , (revenue * linear_points)                                         as linear_touch_revenue   
    -- , week_start
    -- , date_month
    , row_number() over(
            partition by 
                session_date,
                utm_source,
                utm_medium,
                utm_campaign 
            order by 
            timestamp_utc
        )                                                               as ad_spend_daily_rank

from 
    touch_points

order by
    case_id
    , event_name
    , converted_at
    , timestamp_utc