SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

Analytics Patterns · ROW_NUMBER both ways · attribution

57. First-touch vs last-touch attribution

Concept: ROW_NUMBER both ways · attributionshow

Attribution decides which marketing channel gets credit for a conversion. First-touch credits the earliest touch before the conversion, last-touch the latest one. Number each converter's eligible touches with ROW_NUMBER ordered ascending and descending and keep row 1 of each; touches after the conversion never get credit.

Example (different data):

SELECT donor_id, campaign,
  ROW_NUMBER() OVER (PARTITION BY donor_id ORDER BY sent_on) AS nth_ask,
  ROW_NUMBER() OVER (PARTITION BY donor_id ORDER BY sent_on DESC) AS nth_from_last
FROM appeals;

Your task

Marketing is arguing about budget: paid search says it starts journeys, email says it closes them. Compare both models.

For users with a purchase event, consider only marketing touches on or before their purchase date. Return channel, first_touch (purchases whose earliest touch was this channel) and last_touch (purchases whose latest touch was this channel), one row per channel (email, paid_search, referral, social), sorted by channel.

Starting Postgres in your browser…

Checking your access…