Last Click Attribution

Imagine a relay race where the last runner crosses the finish line. Last click treats that final runner as the sole winner. It ignores how the baton got there, which is simple but can be biased toward lower-funnel channels.

• Pure Last Click: Includes direct visits as a possible last click.
• Last Non-Direct Click: Ignores direct visits and credits the last identifiable marketing channel before conversion.
• Last Click (Ad-Only): Considers only paid/owned marketing touches (e.g., excludes Organic/Direct).

1. User path: Social → Organic → Email → Purchase
2. Last click: Email
3. Attribution: 100% credit to Email
4. Note: Social and Organic helped, but are ignored here.

1. User path: Paid Search → Organic → Direct → Purchase
2. Pure Last Click: Direct gets 100% credit
3. Last Non-Direct Click: Organic gets 100% credit
4. Tip: Clarify which variant your report uses.

1. User path: Display Prospecting → Paid Social → Retargeting Display → Purchase
2. Last click: Retargeting Display
3. Attribution: 100% to Retargeting
4. Implication: Prospecting looks weaker in last click reports.

1. Sort all events by user_id then timestamp ascending.
2. For each conversion, select the last interaction at or before conversion.
3. Assign conversion to that channel; sum by channel.

Formula hint: Create a helper that marks the max timestamp per user where timestamp ≤ conversion_time, then VLOOKUP/XLOOKUP to retrieve the channel.

WITH purchases AS (
  SELECT user_id, MIN(event_time) AS purchase_time
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
), last_touch AS (
  SELECT e.user_id,
         e.channel,
         ROW_NUMBER() OVER (
           PARTITION BY e.user_id
           ORDER BY e.event_time DESC
         ) AS rn
  FROM events e
  JOIN purchases p
    ON e.user_id = p.user_id
   AND e.event_time <= p.purchase_time
  WHERE e.event_type IN ('visit','click')
)
SELECT channel, COUNT(*) AS conversions
FROM last_touch
WHERE rn = 1
GROUP BY channel
ORDER BY conversions DESC;

• Clarify model name: pure last click vs. last non-direct click.
• Confirm which interaction type counts (session, click, impression).
• Ensure consistent UTM tagging across channels to avoid “(unassigned)” or “Direct”.

Copy this table into your spreadsheet (headers in row 1):

user_id,timestamp,channel,is_conversion
U1,2025-06-01 09:00,Paid Search,0
U1,2025-06-02 10:00,Email,0
U1,2025-06-02 10:05,Direct,1
U2,2025-06-01 12:00,Social,0
U2,2025-06-03 08:00,Organic,0
U2,2025-06-03 08:30,Direct,1
U3,2025-06-02 09:00,Display,0
U3,2025-06-04 18:00,Paid Search,1
U4,2025-06-02 15:00,Referral,0
U4,2025-06-05 16:00,Email,0
U4,2025-06-05 16:20,Email,1

• Task A: For each row where is_conversion=1, find the last channel for that user at or before the same timestamp.
• Task B: Aggregate conversions by last-click channel.

Use a table events(user_id, event_time, channel, event_type). Assume each user has 0–1 purchase. Task: return conversions by last click channel.

• Select each user’s purchase_time.
• Within each user, pick the latest event_type IN ('visit','click') at or before purchase_time.
• Count by channel.

• Marketing Analysts who need fast, interpretable reports.
• Performance Marketers validating closing channels.
• Product and Growth teams running short-cycle experiments.

• Comfort with spreadsheets (sorting, lookups, pivots).
• Basic SQL: window functions and joins.
• Familiarity with UTM parameters and channel taxonomy.

1. Master Last Click (this lesson).
2. Understand Last Non-Direct Click and Direct traffic behavior.
3. Compare with First Click and Position-Based models.
4. Move to data-driven or Markov models when your data supports it.

• Bias: Retargeting touches users late, so last click naturally favors it.
• Bias: Direct or branded search may be filtered out in last non-direct click, changing results.
• Bias: Upper-funnel channels assist but get zero credit.
• Quick check: Compare last click vs. first click by campaign for 4 weeks; quantify shifts.