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

Зачем Last-Touch

Last-Touch — самая распространённая модель атрибуции. Дефолт в Google Ads, Yandex Metrica. Просто, понятно, легко считать. Минус — overweights closing channels (retargeting, brand search), недооценивает top-of-funnel.

Формула

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

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

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

WITH last_touch AS (
    SELECT
        c.user_id,
        c.amount,
        c.converted_at,
        (
            SELECT channel
            FROM touchpoints t
            WHERE t.user_id = c.user_id
              AND t.touched_at <= c.converted_at
            ORDER BY t.touched_at DESC
            LIMIT 1
        ) AS last_channel
    FROM conversions c
)
SELECT
    last_channel,
    COUNT(*) AS conversions,
    SUM(amount) AS attributed_revenue
FROM last_touch
GROUP BY last_channel
ORDER BY attributed_revenue DESC;

По каналам

WITH last_touch AS (
    SELECT
        c.user_id,
        c.amount,
        FIRST_VALUE(t.channel) OVER (
            PARTITION BY c.user_id
            ORDER BY t.touched_at DESC
        ) AS last_channel
    FROM conversions c
    JOIN touchpoints t ON t.user_id = c.user_id AND t.touched_at <= c.converted_at
)
SELECT
    last_channel,
    COUNT(DISTINCT user_id) AS conversions,
    SUM(amount) AS revenue
FROM last_touch
GROUP BY last_channel
ORDER BY revenue DESC;

Плюсы и минусы

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

Ошибка 1. Include touchpoints после conversion. Только до conversion.

Ошибка 2. Direct counts as last touch. Юзер пришёл напрямую — это «direct» или «brand awareness»? Бизнес-решение.

Ошибка 3. Lookback window. Touchpoint 30 дней назад vs 2 года — нужно cap.

Ошибка 4. Multi-conversion. Juzер совершил 5 покупок — last-touch к first conversion или каждой? Зависит от вопроса.

Ошибка 5. Сравнение first vs last. Если first и last совпадают — single-touch journey. Это полезный insight.

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

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

FAQ

Last-Touch — стандарт?

Да, в большинстве платформ. Простой и понятный.

Минусы Last-Touch?

Overweights closing channels (retargeting, brand search, paid search на бренд). Top-of-funnel недооценивается.

Когда Last-Touch норм?

Для transactional бизнесов с короткими циклами (e-com, mobile gaming). Для B2B / high-consideration — плохо.

Last-Touch vs multi-touch?

Multi-touch (linear, time-decay) — точнее. Last-Touch — простой baseline.

Direct counted as Last-Touch?

Зависит от бизнес-логики. Часто исключают direct (юзер вернулся, бренд уже awareness'нул).