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…