Course contentsShow
← Data Science
Lesson 1725 of 2,14541. Marketing and Growth AnalyticsFree lesson

Implementing Single-Touch Attribution

Practical steps for calculating single-touch attribution from web analytics and CRM data.

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_id or session_id
  • timestamp
  • channel (organic, paid, email, etc.)
  • touchpoint_id (unique identifier)

Your conversion table needs:

  • user_id
  • conversion_timestamp
  • revenue (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.