Модели атрибуции в SQL: от Last Click до Time Decay на реальных данных

Ты почистил UTM-разметку. Данные стали аккуратные, каналы определяются верно. И вот маркетинг приходит с вопросом: «Клиент зашёл из рекламы, потом из органики, потом ввёл URL руками и купил. Кому засчитать продажу?»

Три канала. Одна покупка. Три команды, каждая из которых уверена, что это её конверсия. Знакомая картина? Добро пожаловать в атрибуцию — место, где данные одни и те же, а выводы прямо противоположные.

Атрибуция — это не «правильный ответ». Это осознанный выбор модели, и от выбора зависит, кто получит бюджет в следующем квартале. В этой статье построим 6 моделей на чистом SQL, прогоним их на одном датасете и посмотрим, как одна и та же покупка превращается в 6 разных историй успеха.

Зачем строить атрибуцию в SQL, если в Метрике уже есть

Короткий ответ: потому что стандартная атрибуция Метрики и GA4 — это «средняя температура по больнице». В 80% случаев хватает. Но когда не хватает — вы принимаете решения на миллионы рублей по данным, которые врут. «Из коробки» ломается, когда:

• У тебя кастомные каналы — affiliate, agents, CVM, — которые не влезают в стандартную группировку Метрики
• Длинный цикл сделки — fintech, B2B, SaaS — и стандартное окно атрибуции слишком короткое
• Нужно подключить CRM-данные — связать визиты с реальными продажами, а не целями на сайте
• Бизнесу нужна своя логика приоритетов — например, «платный канал всегда важнее, даже если последний визит был из органики»

Структура данных: таблица touchpoints

Прежде чем строить модели — договоримся о формате данных. Никакой магии: плоская таблица, где каждая строка — визит пользователя с каналом и временем. Плюс флаг конверсии.

CREATE TABLE touchpoints (
    user_id       INT,
    visit_id      INT,
    visit_dt      DATE,
    channel       TEXT,    -- paid_performance, organic_search, direct...
    campaign      TEXT,
    is_conversion INT,     -- 1 = визит с конверсией
    conv_dt       DATE     -- дата конверсии (NULL если нет)
);

Пример данных — 3 пользователя, разные цепочки касаний:

Пользователь 1 — идеальный кейс: 5 касаний, 4 разных канала, конверсия в конце. На нём будем сравнивать все 6 моделей.

Пользователь 4 — ловушка, на которую попадаются все: платный визит был в ноябре, конверсия аж в феврале. 100 дней — за пределами lookback-окна. Если ваша модель его не отсекает, вы считаете рекламу эффективнее, чем она есть.

Last Click — baseline за 3 строки

Last Click — самая простая модель атрибуции: последний визит перед конверсией получает 100% кредита. Все остальные касания игнорируются.

SELECT
    c.user_id,
    c.conv_dt,
    t.channel AS attributed_channel
FROM touchpoints c
JOIN touchpoints t ON t.user_id = c.user_id
    AND t.visit_dt <= c.conv_dt
WHERE c.is_conversion = 1
  AND t.visit_id = (
      SELECT t2.visit_id
      FROM touchpoints t2
      WHERE t2.user_id = c.user_id
        AND t2.visit_dt <= c.conv_dt
      ORDER BY t2.visit_dt DESC, t2.visit_id DESC
      LIMIT 1
  );

SELECT
    c.user_id,
    c.conv_dt,
    t.channel AS attributed_channel
FROM touchpoints c
JOIN touchpoints t ON t.user_id = c.user_id
    AND t.visit_dt <= c.conv_dt
WHERE c.is_conversion = 1
  AND t.visit_id = (
      SELECT t2.visit_id
      FROM touchpoints t2
      WHERE t2.user_id = c.user_id
        AND t2.visit_dt <= c.conv_dt
      ORDER BY t2.visit_dt DESC, t2.visit_id DESC
      LIMIT 1
  );

Результат для наших данных:

Три из четырёх конверсий — Direct. Маркетинг в ужасе: «Мы потратили миллион на рекламу, а все конверсии якобы пришли сами?!»

Знакомо? Это классика Last Click. Человек увидел рекламу, заинтересовался, через неделю вбил URL руками — а Last Click радостно записал всё в Direct. По этой модели лучший канал привлечения — адресная строка браузера. Серьёзно.

Три строки SQL, и ты уже видишь главную проблему: Last Click не врёт, но рассказывает только финал истории. А кто написал первую главу — ей неинтересно.

Last Paid Click с приоритетами — как это работает в production

Стандартная трактовка Last Paid Click из учебника: «берём последний платный клик». На собеседовании — отличный ответ. В production — бесполезный. Что если платного клика не было? Что если в цепочке только Direct? Что если визитов вообще нет? Учебник молчит, а отчёт нужен завтра.

В реальности используется расширенная LPC с 3-уровневым приоритетом каналов и fallback-логикой. Это не академическая модель из статьи на Habr — это паттерн, который я видел в продакшене крупных компаний и который реально влияет на распределение бюджетов.

Три уровня приоритета: платные → бесплатные → незначимые

Все каналы делятся на три группы по «ценности» для атрибуции:

Логика выбора канала — fallback-каскад:

Ключевая идея, которая ломает мозг новичкам: платный канал побеждает бесплатный, даже если бесплатный был позже. «Это же нечестно!» — скажет кто-то. Нет, это прагматично. За рекламу заплатили деньги, и бизнесу нужно видеть, вернулись они или нет. Если пользователь сначала кликнул рекламу, а потом вернулся из органики — реклама сделала свою работу. Органика просто дожала.

Lookback-окно: почему 90 дней

Lookback-окно — период назад от конверсии, в котором ищем визиты. Всё, что за пределами — отрезано. Даже если это был клик по рекламе за $500. Жёстко? Да. Но без этого окна вы будете приписывать конверсии визитам годичной давности.

Если сомневаешься — начни с 90 дней и не парься. Потом, когда всё заработает, посмотри на данные: у какого процента конверсий вообще нет визитов в окне? Если больше 10% — расширь. Если таких единицы — сужай. Данные сами подскажут, теоретизировать тут бесполезно.

SQL-реализация: ROW_NUMBER + CASE

Ядро запроса — оконная функция ROW_NUMBER с хитрой сортировкой: сначала по приоритету канала, потом по дате, потом по visit_id для детерминированности. Разберём построчно:

WITH conversion_visits AS (
    SELECT
        c.user_id,
        c.conv_dt,
        t.visit_id,
        t.visit_dt,
        t.channel,
        -- Приоритет канала: 1 = платный, 2 = бесплатный, 3 = незначимый
        CASE
            WHEN t.channel IN ('paid_performance','paid_display',
                               'smm_paid','affiliate','agents','cvm_paid')
                THEN 1
            WHEN t.channel NOT IN ('direct','other','inner_traffic')
                THEN 2
            ELSE 3
        END AS channel_priority,
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.conv_dt
            ORDER BY
                CASE
                    WHEN t.channel IN ('paid_performance','paid_display',
                                       'smm_paid','affiliate','agents','cvm_paid')
                        THEN 1
                    WHEN t.channel NOT IN ('direct','other','inner_traffic')
                        THEN 2
                    ELSE 3
                END,
                t.visit_dt DESC,
                t.visit_id DESC
        ) AS rn_lpc
    FROM touchpoints c
    JOIN touchpoints t
        ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
        AND t.visit_dt >= c.conv_dt - INTERVAL '90 days'   -- lookback
    WHERE c.is_conversion = 1
)
SELECT user_id, conv_dt, channel, channel_priority
FROM conversion_visits
WHERE rn_lpc = 1;

WITH conversion_visits AS (
    SELECT
        c.user_id,
        c.conv_dt,
        t.visit_id,
        t.visit_dt,
        t.channel,
        multiIf(
            t.channel IN ('paid_performance','paid_display',
                          'smm_paid','affiliate','agents','cvm_paid'), 1,
            t.channel NOT IN ('direct','other','inner_traffic'), 2,
            3
        ) AS channel_priority,
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.conv_dt
            ORDER BY
                multiIf(
                    t.channel IN ('paid_performance','paid_display',
                                  'smm_paid','affiliate','agents','cvm_paid'), 1,
                    t.channel NOT IN ('direct','other','inner_traffic'), 2,
                    3
                ),
                t.visit_dt DESC,
                t.visit_id DESC
        ) AS rn_lpc
    FROM touchpoints c
    JOIN touchpoints t
        ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
        AND t.visit_dt >= c.conv_dt - INTERVAL 90 DAY   -- lookback
    WHERE c.is_conversion = 1
)
SELECT user_id, conv_dt, channel, channel_priority
FROM conversion_visits
WHERE rn_lpc = 1;

Что происходит:

1. CTE conversion_visits — для каждой конверсии находим все визиты в lookback-окне
2. CASE / multiIf — присваиваем приоритет: 1 (платный) > 2 (бесплатный) > 3 (незначимый)
3. ROW_NUMBER — сортируем: сначала по приоритету (платные первые), потом по дате (свежие первые), потом по visit_id (детерминированность)
4. WHERE rn_lpc = 1 — берём победителя

Результат для наших данных:

Сравни с Last Click: пользователи 1 и 2 теперь атрибутированы к платным каналам, а не к Direct. Ощути разницу: по Last Click реклама «не работает», по LPC — отлично работает. Те же данные, другая модель, противоположный вывод для бюджета. Вот почему выбор модели — это не техническое решение, а бизнесовое.

Пользователь 4 — нарочно подложенная ловушка: платный визит 1 ноября, конверсия 9 февраля. 100 дней > 90 дней окна. Платный визит безжалостно отрезан, остаётся Direct. Если ваш аналитик забыл про lookback — он бы засчитал эту конверсию рекламе. А реклама тут, возможно, ни при чём.

5 кейсов, которые ломают интуицию

Ниже — пять ситуаций, на которых люди спотыкаются, впервые работая с LPC. Каждый кейс — реальный паттерн из production.

Кейс 1 — тот самый, из-за которого аналитики спорят с маркетологами. «Почему рекламу засчитали, если последний визит из органики?!» — потому что без рекламы клиент бы вообще не узнал о продукте. Органика дожала, но не привела. LPC — модель для тех, кто тратит деньги на рекламу и хочет понимать, возвращаются ли они. Если вам это не нужно — используйте Last Click и радуйтесь Direct.

First Click — кто привёл клиента впервые

First Click — зеркало Last Click, только наоборот: 100% кредита получает первый визит в цепочке. Всё остальное — и месяцы nurturing’а, и финальный клик — игнорируется. Грубо? Да. Но у неё есть своя суперсила.

WITH first_touch AS (
    SELECT
        c.user_id, c.conv_dt, t.channel,
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.conv_dt
            ORDER BY t.visit_dt ASC, t.visit_id ASC
        ) AS rn
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
)
SELECT user_id, conv_dt, channel AS first_click_channel
FROM first_touch WHERE rn = 1;

WITH first_touch AS (
    SELECT
        c.user_id, c.conv_dt, t.channel,
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.conv_dt
            ORDER BY t.visit_dt ASC, t.visit_id ASC
        ) AS rn
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
)
SELECT user_id, conv_dt, channel AS first_click_channel
FROM first_touch WHERE rn = 1;

Когда полезна: оценка acquisition-каналов. «Какой канал впервые привёл пользователя в продукт?» Для контент-маркетинга, бренда, верхней воронки — First Click показывает, кто генерирует awareness.

Ограничение очевидно: клиент мог первый раз зайти случайно, а купил через 3 месяца после 15 ретаргетинговых касаний. First Click бодро запишет всё на «случайный клик». Используй с умом.

Линейная модель — делим поровну

Линейная атрибуция распределяет конверсию поровну: каждый touchpoint получает равную долю. 5 касаний — каждый канал получает 0.2 конверсии.

WITH touch_count AS (
    SELECT
        c.user_id, c.conv_dt, t.channel,
        COUNT(*) OVER (PARTITION BY c.user_id, c.conv_dt) AS total_touches
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
)
SELECT
    user_id, conv_dt, channel,
    ROUND(1.0 / total_touches, 4) AS linear_credit
FROM touch_count
ORDER BY user_id, conv_dt;

WITH touch_count AS (
    SELECT
        c.user_id, c.conv_dt, t.channel,
        count() OVER (PARTITION BY c.user_id, c.conv_dt) AS total_touches
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
)
SELECT
    user_id, conv_dt, channel,
    round(1.0 / total_touches, 4) AS linear_credit
FROM touch_count
ORDER BY user_id, conv_dt;

Для пользователя 1 (5 касаний): paid_performance = 0.2, organic_search = 0.4 (два визита), smm_paid = 0.2, direct = 0.2.

Когда полезна: нет гипотезы о приоритетах, хочется «демократии». Минус — и он жирный: линейная модель считает, что клик по рекламе за миллион и случайное тыканье в закладку браузера одинаково ценны. Коммунизм в атрибуции звучит красиво, но бюджеты так не работают.

U-shape — первый и последний получают больше

Position-based модель: первый touchpoint — 40%, последний — 40%, все промежуточные делят 20%.

Логика: первое касание привело клиента (acquisition), последнее — закрыло сделку (conversion). Всё между ними — вспомогательные касания.

WITH ranked AS (
    SELECT
        c.user_id, c.conv_dt, t.channel, t.visit_dt,
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.conv_dt ORDER BY t.visit_dt ASC
        ) AS rn,
        COUNT(*) OVER (PARTITION BY c.user_id, c.conv_dt) AS total
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
)
SELECT user_id, conv_dt, channel,
    CASE
        WHEN total = 1 THEN 1.0
        WHEN total = 2 THEN 0.5
        WHEN rn = 1 THEN 0.4
        WHEN rn = total THEN 0.4
        ELSE ROUND(0.2 / (total - 2), 4)
    END AS u_shape_credit
FROM ranked
ORDER BY user_id, rn;

WITH ranked AS (
    SELECT
        c.user_id, c.conv_dt, t.channel, t.visit_dt,
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.conv_dt ORDER BY t.visit_dt ASC
        ) AS rn,
        count() OVER (PARTITION BY c.user_id, c.conv_dt) AS total
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
)
SELECT user_id, conv_dt, channel,
    multiIf(
        total = 1, 1.0,
        total = 2, 0.5,
        rn = 1, 0.4,
        rn = total, 0.4,
        round(0.2 / (total - 2), 4)
    ) AS u_shape_credit
FROM ranked
ORDER BY user_id, rn;

Edge cases, о которых все забывают: 1 касание → 100% (тут U-shape вырождается в Last Click), 2 касания → 50/50 (нет середины — нечего делить). Формула 40/20/40 включается от 3 касаний.

Time Decay — свежие касания важнее

Time Decay — модель для тех, кто верит в свежесть: чем ближе визит к конверсии, тем больше его вес. Визит вчера важнее визита месяц назад. Логично? Вполне. Реклама, которую ты видел утром, влияет на покупку днём сильнее, чем баннер трёхнедельной давности.

Вес рассчитывается через экспоненциальный decay с параметром half-life (период полураспада). При half-life = 7 дней визит двухнедельной давности получает ~25% веса свежего.

WITH decay AS (
    SELECT
        c.user_id, c.conv_dt, t.channel, t.visit_dt,
        -- days_before = сколько дней до конверсии
        c.conv_dt - t.visit_dt AS days_before,
        -- weight = EXP(-ln(2) * days_before / half_life)
        EXP(-0.693 * (c.conv_dt - t.visit_dt) / 7.0) AS raw_weight
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
),
normalized AS (
    SELECT *,
        raw_weight / SUM(raw_weight) OVER (
            PARTITION BY user_id, conv_dt
        ) AS decay_credit
    FROM decay
)
SELECT user_id, conv_dt, channel, days_before,
       ROUND(raw_weight, 4) AS weight,
       ROUND(decay_credit, 4) AS credit
FROM normalized
ORDER BY user_id, visit_dt;

WITH decay AS (
    SELECT
        c.user_id, c.conv_dt, t.channel, t.visit_dt,
        dateDiff('day', t.visit_dt, c.conv_dt) AS days_before,
        exp(-0.693 * dateDiff('day', t.visit_dt, c.conv_dt) / 7.0) AS raw_weight
    FROM touchpoints c
    JOIN touchpoints t ON t.user_id = c.user_id
        AND t.visit_dt <= c.conv_dt
    WHERE c.is_conversion = 1
),
normalized AS (
    SELECT *,
        raw_weight / sum(raw_weight) OVER (
            PARTITION BY user_id, conv_dt
        ) AS decay_credit
    FROM decay
)
SELECT user_id, conv_dt, channel, days_before,
       round(raw_weight, 4) AS weight,
       round(decay_credit, 4) AS credit
FROM normalized
ORDER BY user_id, visit_dt;

Параметр half-life = 7 дней означает: визит недельной давности получает 50% веса, двухнедельный — 25%, месячный — жалкие ~5%. Подбирай half-life под свой цикл сделки. Для ecommerce 7 дней — нормально. Для B2B с полугодовыми сделками — ставь 30–60, иначе первые касания обнулятся.

Один путь клиента — 6 разных ответов

Пользователь 1 — пять касаний: Paid Performance (10 янв) → Organic (25 янв) → SMM Paid (5 фев) → Organic (9 фев) → Direct (10 фев, конверсия).

Вот что даёт каждая модель для этого пути:

Одни и те же данные. Один и тот же клиент. Шесть моделей — шесть разных героев. Last Click говорит, что реклама бесполезна. LPC говорит, что SMM красавчик. First Click хвалит Paid Performance. А если вы пойдёте с этими отчётами к директору по маркетингу — он спросит: «Так кто прав-то?»

Все. И никто. В этом вся боль атрибуции. Каждая модель отвечает на свой вопрос:

• Last Click: кто закрыл сделку?
• LPC: какой платный канал сработал?
• First Click: кто привёл клиента?
• Линейная: кто участвовал в цепочке?
• U-shape: кто привёл + кто закрыл?
• Time Decay: кто был ближе к покупке?

Какую модель выбрать: матрица решений

Про-tip: считайте две модели параллельно. LPC для перфоманс-отчётов (маркетинг смотрит ROI), First Click или линейную — для общей картины (продукт смотрит acquisition). Одна модель не закроет все вопросы, и если кто-то настаивает на единственной — он просто не хочет видеть неудобную правду.

Атрибуция отвечает на вопрос «КТО получил кредит за конверсию?», а воронки — «ГДЕ теряются пользователи по пути к конверсии?». Это два разных инструмента, и они дополняют друг друга.

Грабли и пограничные случаи

Можно написать идеальный SQL, но реальность найдёт, как его сломать. Вот грабли, на которые я наступал лично — и которые наступят на вас, если не подготовиться:

Следующий шаг: ты построил атрибуцию и знаешь, какой канал привёл клиента. Теперь нужно посчитать, сколько на этот канал потратили и сколько он принёс — полный ROI по каналам. Об этом — в статье про сквозную аналитику без Roistat: 4 таблицы, 5 шагов, от расходов до ROI на чистом SQL.

FAQ

Чем Last Paid Click отличается от Last Click?

Last Click слепо берёт последний визит — хоть Direct, хоть случайный заход. LPC умнее: берёт последний платный визит. А production-LPC — ещё умнее: если платных нет, смотрит на значимые бесплатные, потом на незначимые, и только в полном отчаянии ставит Direct. Это каскад, а не выбор из одного варианта.

Какое lookback-окно выбрать?

Зависит от цикла сделки. Ecommerce: 14–30 дней. SaaS/fintech: 60–90. B2B: 90–180. Не знаете — начните с 90 и проверьте, какой % конверсий «отрезается» (у них нет визитов в окне).

Можно ли комбинировать модели?

Да, и это хорошая практика. Считайте LPC для перфоманс-отчётов, First Click — для acquisition, линейную — для общей картины. Одна модель не закроет все вопросы.

Что делать, если 30%+ конверсий атрибутируется в Direct?

Не модель виновата — виновата разметка. Это классический симптом: кто-то забыл UTM на email-рассылке, редирект съел метки, внутренний переход перезаписал utm_source. Чинить надо не модель, а вход данных. Подробный чеклист — в статье про UTM-разметку.

Работает ли эта атрибуция для мобильных приложений?

Короткий ответ — нет, не напрямую. Мобайл — другой мир: там атрибуция к последнему скачиванию (а не визиту), deep link обычно не учитываются, а за данными ходят в AppsFlyer или Adjust, не в Метрику. Применять веб-модели к аппу без адаптации — верный способ получить мусор в отчётах.