SQL queries: first-touch, last-touch and linear attribution

Attribution modelling sounds like maths PhD territory. In a warehouse, the classic models are ~20 lines of SQL each. Once you can run all three side by side, channel debates get dramatically shorter.

The shape you need first

One row per marketing touch, tied to a user and (eventually) an order:

-- touchpoints: user_id, touched_at, channel, campaign
-- orders:      user_id, ordered_at, order_id, revenue

Building this table is the real attribution project — sessionising GA4 data, mapping UTMs to channels, stitching users. The models themselves are the easy bit, which is exactly why platform "model comparisons" are a distraction when the underlying data is broken.

Last touch

WITH ranked AS (
  SELECT o.order_id, o.revenue, t.channel,
         ROW_NUMBER() OVER (
           PARTITION BY o.order_id
           ORDER BY t.touched_at DESC
         ) AS rn
  FROM orders o
  JOIN touchpoints t
    ON t.user_id = o.user_id
   AND t.touched_at <= o.ordered_at
   AND t.touched_at >= TIMESTAMP_SUB(o.ordered_at, INTERVAL 90 DAY)
)
SELECT channel, SUM(revenue) AS attributed_revenue
FROM ranked WHERE rn = 1
GROUP BY channel

First touch

Same query, ORDER BY t.touched_at ASC. One word changes; the budget conclusions often invert. Sit with that for a moment.

Linear (every touch shares equally)

WITH counted AS (
  SELECT o.order_id, o.revenue, t.channel,
         COUNT(*) OVER (PARTITION BY o.order_id) AS touches
  FROM orders o
  JOIN touchpoints t
    ON t.user_id = o.user_id
   AND t.touched_at <= o.ordered_at
   AND t.touched_at >= TIMESTAMP_SUB(o.ordered_at, INTERVAL 90 DAY)
)
SELECT channel, SUM(revenue / touches) AS attributed_revenue
FROM counted
GROUP BY channel

How to actually use these

Run all three and put them in one table, channels as rows, models as columns:

• A channel strong under every model is genuinely strong. Fund it confidently.
• A channel that only looks good under last-touch is a closer — it harvests demand.
• A channel that only looks good under first-touch is an opener — it creates demand.

The disagreement between models isn't a flaw. It's the insight.