First touch и last touch: куда записать продажу

Клиент нашёл магазин через органический поиск, затем вернулся из письма и оформил покупку. Какому каналу записать выручку? Разберём first touch и last touch на одной и той же продаже, включая таймстамп, окно 7 дней и случай без подходящего касания.

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;
Ложная сумма после JOIN
joined_rowsfalsely_attributed_kopecks
848000

Исходная сумма четырёх заказов — 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_idfirst_channellast_channelamount_kopecks
1001organicemail10000
1002socialpaid_search5000
1003——2000
1004organicemail3000

Значит, в модели 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, прежде чем строить отчёт по каналам.

Для проверки логики: почему JOIN размножает суммы, ROW_NUMBER и ничьи при одинаковом времени, клиенты и корректный знаменатель конверсии.

Синтаксис оконных функций и datetime в SQLite. В отдельном курсе Stepik используется MySQL 8: для сдвига даты обычно применяют DATE_SUB(..., INTERVAL 7 DAY), а не модификатор SQLite datetime(..., '-7 days').

Задание с автопроверкой: P099 «Первый визит клиента». Смежная практика: найдите первое касание клиента. Для полноценной first/last-touch атрибуции ещё понадобятся окно до покупки и связь с конкретным заказом.


Если после бесплатной практики нужен последовательный курс на MySQL 8 с итоговым аналитическим отчётом, есть отдельный курс на Stepik. Он не требуется для выполнения запросов в SQL Studio.