Skip to lesson
Exit
Data, SQL & Measurement1 / 2

1 min lesson

Window functions earn their keep on touch attribution and cohorts

Talk through the example in "Window functions earn their keep on touch attribution and cohorts", then name the result it is meant to produce.

Step 1 of 2

Window functions earn their keep on touch attribution and cohortsfirst-touch, last-touch, sequencing

First-touch and last-touch live in row_number() over a partition. Cohorts live in date_trunc plus a window count. Sequencing (did enrichment fire before routing?) lives in lag() and lead(). These four patterns answer most of what the funnel round will throw at you.

First-touch channel per lead, then capture-month cohort
with touches as (
  select
    lead_id, channel, occurred_at,
    row_number() over (
      partition by lead_id order by occurred_at asc
    ) as touch_seq
  from marketing_touches
)
select
  date_trunc('month', l.captured_at) as cohort_month,
  t.channel                          as first_touch_channel,
  count(distinct l.lead_id)          as leads
from leads l
join touches t on t.lead_id = l.lead_id and t.touch_seq = 1
group by 1, 2
order by 1, leads desc;