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

Зачем First-Touch

В B2B-маркетинге цикл длинный: блог-пост → вебинар → демо → покупка через полгода. Last-touch отдаст всю заслугу демо от продаж. First-touch — блог-посту, который вообще привёл человека. Без first-touch каналы верха воронки недооцениваются.

Формула

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

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

Данные: 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 — первая точка касания. Фильтр touched_at <= converted_at гарантирует, что касание было до конверсии.

По каналам

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. Не фильтровать касания до конверсии. Касание после конверсии ни на что не влияет — его нужно игнорировать.

Ошибка 2. Пользователи с несколькими конверсиями. Пользователь сделал 3 покупки — к какой из них отнести first-touch? По стандарту — к первой конверсии.

Ошибка 3. Окно lookback. Касание двухлетней давности — это тоже first-touch? Обычно окно ограничивают 90–180 днями.

Ошибка 4. Сравнивать с last-touch без контекста. First-touch на блоге, last-touch на ретаргетинге — это разные истории, и обе важны.

Ошибка 5. Касания в один день. Пользователь пришёл через рекламу и органику в один день — какой из каналов первый? Смотрите на порядок по touched_at.

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

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

FAQ

First-touch или Last-touch?

First-touch удобен, чтобы оценить каналы верха воронки — те, что вообще приводят людей. Last-touch показывает, какие каналы закрывают сделку. В идеале смотрят на обе модели, а лучше — на multi-touch, который делит заслугу между всеми касаниями.

Lookback window?

Размер окна зависит от длины цикла покупки. В B2C обычно берут около 30 дней, потому что решение принимают быстро. В B2B цикл длиннее, поэтому окно раздвигают до 90–180 дней.

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

Часто да: органика и SEO обычно оказываются первым касанием, поэтому first-touch щедро отдаёт им заслугу. Именно поэтому одну модель атрибуции опасно принимать за истину — и нужен multi-touch.

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

Как канал обычно берут utm_source. Если UTM-меток нет, определяйте источник по referer, а когда и его нет — относите касание к каналу 'direct'.

Multi-touch — лучше?

В теории да — multi-touch честнее распределяет заслугу между всеми касаниями. Но он сложнее: моделей несколько (linear, time-decay, U-shaped), и результат зависит от выбранной. First-touch и last-touch остаются простой отправной точкой.