Implementing Single-Touch Attribution
You'll learn: How to compute single-touch attribution metrics using actual web analytics and CRM data in SQL and Python.
Core Implementation Steps
Now that you understand first-touch, last-touch, and last non-direct models conceptually, it's time to build them from real data. Single-touch attribution requires two key tables: a touchpoint log (every interaction) and a conversion log (purchases, sign-ups, etc.).
The Basic Data Structure
Your touchpoint table needs:
user_idorsession_idtimestampchannel(organic, paid, email, etc.)touchpoint_id(unique identifier)
Your conversion table needs:
user_idconversion_timestamprevenue(optional, for revenue attribution)
Implementation Pattern
First-Touch Attribution:
SELECT
first_touch.channel,
COUNT(DISTINCT c.user_id) as conversions,
SUM(c.revenue) as attributed_revenue
FROM conversions c
LEFT JOIN (
SELECT user_id, channel,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp ASC) as rn
FROM touchpoints
) first_touch ON c.user_id = first_touch.user_id AND first_touch.rn = 1
GROUP BY first_touch.channel;
Last-Touch Attribution: Change ORDER BY timestamp ASC to DESC.
Last Non-Direct: Add WHERE channel != 'direct' before the window function.
The pattern is always: isolate the qualifying touchpoint per user, join to conversions, aggregate by channel.
Key Takeaway: Single-touch attribution implementation boils down to identifying one touchpoint per user using window functions (first, last, or filtered last), then joining conversions and aggregating by channel to see which sources get credit.