Как посчитать First-Touch Attribution в SQL

Зачем First-Touch

В B2B-маркетинге long cycle: блог-пост → webinar → demo → purchase через 6 месяцев. Last-touch отдаст credit за sales демо. First-touch — за блог-пост, который привлёк. Без first-touch top-of-funnel каналы недооцениваются.

Формула

First-Touch Attribution: первый канал в пути юзера получает весь credit за conversion

Базовый расчёт

Данные: touchpoints(user_id, channel, touched_at), conversions(user_id, amount, converted_at).

WITH first_touch AS (
    SELECT
        user_id,
        channel,
        touched_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touched_at) AS rn
    FROM touchpoints
)
SELECT
    ft.channel,
    SUM(c.amount) AS attributed_revenue,
    COUNT(DISTINCT c.user_id) AS conversions
FROM conversions c
JOIN first_touch ft ON ft.user_id = c.user_id AND ft.rn = 1
WHERE ft.touched_at <= c.converted_at
GROUP BY ft.channel
ORDER BY attributed_revenue DESC;

rn = 1 — первый touchpoint. Filter touched_at <= converted_at — touchpoint должен быть до conversion.

По каналам

WITH first_touch AS (
    SELECT
        user_id,
        channel,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touched_at) AS rn
    FROM touchpoints
    WHERE touched_at >= CURRENT_DATE - INTERVAL '90 days'
),
conv AS (
    SELECT user_id, SUM(amount) AS revenue
    FROM conversions
    WHERE converted_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
)
SELECT
    ft.channel,
    COUNT(c.user_id) AS conversions,
    SUM(c.revenue) AS revenue
FROM first_touch ft
LEFT JOIN conv c ON c.user_id = ft.user_id
WHERE ft.rn = 1
GROUP BY ft.channel
ORDER BY revenue DESC NULLS LAST;

First vs Last touch

WITH first_last AS (
    SELECT
        user_id,
        FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY touched_at) AS first_channel,
        FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY touched_at DESC) AS last_channel
    FROM touchpoints
)
SELECT
    fl.first_channel,
    fl.last_channel,
    COUNT(DISTINCT c.user_id) AS conversions,
    SUM(c.amount) AS revenue
FROM first_last fl
JOIN conversions c ON c.user_id = fl.user_id
GROUP BY fl.first_channel, fl.last_channel
ORDER BY revenue DESC
LIMIT 50;

Частые ошибки

Ошибка 1. Не фильтровать touchpoints до conversion. Touch после conversion не influence — игнорировать.

Ошибка 2. Multi-conversion users. Юзер сделал 3 покупки. К какой относится first-touch? Standard: к first conversion.

Ошибка 3. Окно lookback. Touchpoint 2 года назад — это first-touch? Обычно cap 90-180 days.

Ошибка 4. Сравнение с last-touch без context. First-touch на blog, last на retargeting — разные истории. Обе важны.

Ошибка 5. Same-day touch. Юзер вошёл через ads и organic в один день. Какой first? По порядку touched_at.

Связанные темы

• Как посчитать ROAS в SQL
• Как посчитать CAC в SQL
• Как посчитать funnel в SQL
• Как посчитать конверсию в SQL

FAQ

First-touch или Last-touch?

First — для top-of-funnel оценки. Last — для closing channels. Идеально использовать multi-touch.

Lookback window?

B2C — 30 дней. B2B — 90-180 дней.

First-touch завышает organic / SEO?

Часто да — organic обычно первый. Это часть why multi-touch важен.

Что делать с UTM-параметрами?

Используйте utm_source как channel. Если utm нет — referer-fallback или 'direct'.

Multi-touch — лучше?

Да, но сложнее: linear, time-decay, U-shaped — разные модели. First/last — простой baseline.