SQL Studio / Разборы SQL / First touch и last touch
Короткий ответ: first touch присваивает заказ самому раннему подходящему контакту, last touch — самому позднему. «Подходящему» значит: контакт того же клиента произошёл до заказа и попал в согласованное окно. Здесь окно — ровно семь суток, включая нижнюю границу и исключая время заказа. Обе модели перераспределяют одни и те же деньги; ни одна не доказывает, что канал вызвал покупку.
Условие: одно правило, четыре заказа
Все времена записаны в UTC как строки YYYY-MM-DD HH:MM:SS; суммы — целые копейки. Заказ №1001 на 10 000 копеек получен после organic и email, а рекламное касание произошло позже покупки. Заказ №1002 имеет первое касание ровно на нижней границе семидневного окна; у №1003 есть лишь старое касание. Для №1004 два контакта имеют одинаковое время: договоримся разрешать ничью по уникальному touch_id.
В каждом запросе ниже таблицы задаются прямо в WITH ... VALUES. Данные независимы от текущего содержимого учебной базы и не требуют создания таблиц.
1. Ошибка: сложить заказы после соединения со всеми касаниями
Распространённая попытка — присоединить историю контактов по одному user_id и суммировать деньги. Такое соединение не учитывает ни время, ни число касаний.
WITH orders(order_id, user_id, ordered_at, amount_kopecks) AS (
VALUES (1001,1,'2026-09-08 12:00:00',10000),
(1002,2,'2026-09-07 12:00:00',5000),
(1003,3,'2026-09-08 12:00:00',2000),
(1004,4,'2026-09-08 12:00:00',3000)
),
touches(touch_id, user_id, touched_at, channel) AS (
VALUES (11,1,'2026-09-02 10:00:00','organic'),
(12,1,'2026-09-07 09:00:00','email'),
(13,1,'2026-09-08 13:00:00','ads'),
(21,2,'2026-08-31 12:00:00','social'),
(22,2,'2026-09-07 11:00:00','paid_search'),
(31,3,'2026-08-30 12:00:00','ads'),
(41,4,'2026-09-08 11:00:00','organic'),
(42,4,'2026-09-08 11:00:00','email')
)
SELECT COUNT(*) AS joined_rows,
SUM(o.amount_kopecks) AS falsely_attributed_kopecks
FROM orders AS o
JOIN touches AS t ON t.user_id = o.user_id;
| joined_rows | falsely_attributed_kopecks |
|---|---|
| 8 | 48000 |
Исходная сумма четырёх заказов — 20 000 копеек, а JOIN вернул восемь строк и 48 000. Заказ №1001 повторился трижды, включая контакт, случившийся после покупки. Это нельзя исправить конструкцией SUM(DISTINCT amount_kopecks): две разные покупки могут иметь равную стоимость.
2. Правильно: кандидаты и два детерминированных ранга
Сначала выбираем касания в интервале [ordered_at − 7 суток, ordered_at). Для каждого order_id строим два ранга через ROW_NUMBER(): от раннего к позднему и наоборот. Сортировка по touch_id устраняет неопределённость одинаковых timestamp; внешний ORDER BY задаёт порядок результата. LEFT JOIN сохраняет заказ без контактов.
WITH orders(order_id, user_id, ordered_at, amount_kopecks) AS (
VALUES (1001,1,'2026-09-08 12:00:00',10000),
(1002,2,'2026-09-07 12:00:00',5000),
(1003,3,'2026-09-08 12:00:00',2000),
(1004,4,'2026-09-08 12:00:00',3000)
),
touches(touch_id, user_id, touched_at, channel) AS (
VALUES (11,1,'2026-09-02 10:00:00','organic'),
(12,1,'2026-09-07 09:00:00','email'),
(13,1,'2026-09-08 13:00:00','ads'),
(21,2,'2026-08-31 12:00:00','social'),
(22,2,'2026-09-07 11:00:00','paid_search'),
(31,3,'2026-08-30 12:00:00','ads'),
(41,4,'2026-09-08 11:00:00','organic'),
(42,4,'2026-09-08 11:00:00','email')
),
eligible AS (
SELECT o.order_id, o.user_id, o.amount_kopecks, t.channel,
ROW_NUMBER() OVER (
PARTITION BY o.order_id
ORDER BY t.touched_at ASC, t.touch_id ASC
) AS rn_first,
ROW_NUMBER() OVER (
PARTITION BY o.order_id
ORDER BY t.touched_at DESC, t.touch_id DESC
) AS rn_last
FROM orders AS o
LEFT JOIN touches AS t
ON t.user_id = o.user_id
AND t.touched_at >= datetime(o.ordered_at, '-7 days')
AND t.touched_at < o.ordered_at
)
SELECT order_id,
MAX(CASE WHEN rn_first = 1 THEN channel END) AS first_channel,
MAX(CASE WHEN rn_last = 1 THEN channel END) AS last_channel,
MAX(amount_kopecks) AS amount_kopecks
FROM eligible
GROUP BY order_id
ORDER BY order_id;
| order_id | first_channel | last_channel | amount_kopecks |
|---|---|---|---|
| 1001 | organic | 10000 | |
| 1002 | social | paid_search | 5000 |
| 1003 | — | — | 2000 |
| 1004 | organic | 3000 |
Значит, в модели first touch organic получает 13 000 копеек, social — 5 000, без атрибуции остаётся 2 000. Для last touch вместо этих каналов будут email — 13 000 и paid_search — 5 000, те же 2 000 неатрибутированы. Общий объём заказов остаётся 20 000 копеек, а не 40 000: нельзя складывать результаты двух моделей как независимые продажи.
Обратите внимание: «first» здесь — первый контакт в пределах семи суток до каждого заказа, не первое посещение клиента за всю жизнь. Если данные сессий неполные, это результат выбранного журнала, а не полная история маркетинга.
3. Контроль границ: ровно семь суток, секунда раньше и касание после оплаты
Последний мини-набор проверяет строгую сторону интервала. Событие ровно за семь суток включается; событие на секунду раньше исключается; касание в момент заказа и после него не считаем предшествующим.
WITH purchase(at_time) AS (VALUES('2026-09-07 12:00:00')),
touches(touch_id, touched_at) AS (
VALUES (21,'2026-08-31 12:00:00'),
(20,'2026-08-31 11:59:59'),
(22,'2026-09-07 11:59:59'),
(23,'2026-09-07 12:00:00'),
(24,'2026-09-07 12:00:01')
)
SELECT t.touch_id
FROM touches AS t CROSS JOIN purchase AS p
WHERE t.touched_at >= datetime(p.at_time, '-7 days')
AND t.touched_at < p.at_time
ORDER BY t.touch_id;
| touch_id |
|---|
| 21 |
| 22 |
Если бизнес считает касание в ту же секунду допустимым, знак < заменится на <= — это уже другая методика. В системах с миллисекундами нужно согласовать точность времени и часовую зону. Идентификатор в разрешении ничьей — техническое правило, а не доказательство, какой канал повлиял сильнее.
Небольшое упражнение
Скопируйте второй запрос в бесплатную SQL-песочницу и измените окно с семи на пять суток. Какие заказы лишатся first touch и какая сумма перейдёт в группу «без атрибуции»? Сверьте каждый order_id, прежде чем строить отчёт по каналам.
Синтаксис оконных функций и datetime в SQLite. В отдельном курсе Stepik используется MySQL 8: для сдвига даты обычно применяют DATE_SUB(..., INTERVAL 7 DAY), а не модификатор SQLite datetime(..., '-7 days').
Если после бесплатной практики нужен последовательный курс на MySQL 8 с итоговым аналитическим отчётом, есть отдельный курс на Stepik. Он не требуется для выполнения запросов в SQL Studio.