Practical SQL – Last non-direct click attribution in GA4 BigQuery

Last non-direct click attribution is back on the menu boys

BigQuery SQL code

with
  direct_to_null as (
    select
      case
        when collected_traffic_source.manual_source = '(direct)' then null
        else collected_traffic_source
      end as clean_collected_traffic_source
      , *
    from
      `myproject.mydataset.mytable`
  )
select 
  last_value(
    clean_collected_traffic_source ignore nulls
  ) over (
    partition by 
      user_pseudo_id
    order by 
      event_timestamp
  ) as last_non_direct_traffic_source
  , *
from
  direct_to_null
;

For people who don’t use GA4 and BigQuery:

If your data store doesn’t support last_value(… ignore nulls), refer to this generic guide on last non-direct click attribution using standard SQL window functions: