Last-touch атрибуция в SQL: один рекламный контакт на заказ

Рекламных касаний больше, чем заказов: пользователь мог перейти из поиска, затем из письма и только потом оплатить покупку. Если связать все контакты с заказом, выручка размножится по каналам. Построим в SQLite last-touch атрибуцию с семидневным окном и одной строкой на заказ.

Определяем допустимый контакт

Для каждого заказа берём контакты того же клиента не позже оплаты и не раньше семи дней до неё. Контакт точно в момент оплаты допустим по нашему правилу. Для другой модели границу можно сделать строгой. Время в обоих источниках должно иметь одну часовую зону; в примере используем одинаковые UTC timestamp в формате YYYY-MM-DD HH:MM:SS.

Last-touch назначает весь заказ последнему допустимому контакту. Это правило учёта, а не оценка причинного вклада рекламы. Оно удобно для воспроизводимого отчёта, но не доказывает, что без письма покупки бы не было. Влияние маркетинга проверяется другими дизайнами, например контрольной группой на уровне кампании.

Окно отсчитывается от каждого заказа отдельно. Один и тот же контакт может участвовать в атрибуции нескольких покупок, если правило бизнеса это разрешает. Если требуется связать контакт только с первой последующей покупкой, это другая задача и дополнительное ограничение. Не стоит незаметно смешивать эти модели внутри одного отчёта.

LEFT JOIN и детерминированный выбор

Условия окна ставим в ON, сохраняя заказы без контакта. При переносе фильтра на правую таблицу в WHERE такие покупки исчезли бы. Затем ROW_NUMBER нумерует кандидатов внутри order_id по времени контакта в обратном порядке. При одинаковом времени второй ключ touch_id делает выбор воспроизводимым.

В примере до заказа 1 есть поиск и два контакта в одинаковое время. По принятому правилу выбирается контакт с большим touch_id — email. Заказ 2 сделан слишком поздно для старых контактов. Для заказа 3 касаний нет, а для 4 единственный контакт произошёл после оплаты. Все эти покупки должны остаться в результате с пустой атрибуцией.

WITH orders(order_id,customer,paid_at) AS (
 VALUES (1,'A','2026-10-10 10:00:00'),(2,'A','2026-11-01 10:00:00'),
 (3,'B','2026-10-10 10:00:00'),(4,'C','2026-10-10 10:00:00')
), touches(touch_id,customer,touched_at,channel) AS (
 VALUES (1,'A','2026-10-09 12:00:00','search'),
 (2,'A','2026-10-10 09:00:00','social'),
 (3,'A','2026-10-10 09:00:00','email'),
 (4,'C','2026-10-10 11:00:00','search')
), ranked AS (
 SELECT o.order_id,o.customer,t.touch_id,t.channel,
 ROW_NUMBER() OVER(PARTITION BY o.order_id ORDER BY t.touched_at DESC,t.touch_id DESC) AS rn
 FROM orders o LEFT JOIN touches t ON t.customer=o.customer
 AND t.touched_at<=o.paid_at
 AND t.touched_at>=datetime(o.paid_at,'-7 days')
)
SELECT order_id,customer,touch_id,channel FROM ranked WHERE rn=1 ORDER BY order_id;

Ожидаемые строки результата в порядке столбцов SELECT:

[[1, "A", 3, "email"], [2, "A", null, null], [3, "B", null, null], [4, "C", null, null]]

Проверяем, что заказ не потерялся

Результат содержит четыре строки — по одной на исходный заказ. Только первый получил email, остальные сохранились с NULL. При добавлении суммы заказа общая выручка результата должна совпасть с исходной. Если строк больше, выбор кандидата ещё не завершён; если меньше, вероятно, фильтр удалил покупки без контакта.

Пустой channel не нужно автоматически превращать в organic. Это может быть отсутствие контакта в окне, потеря трекинга или неизвестный идентификатор клиента. Для отчёта удобна отдельная категория unattributed с сохранением причины, когда она доступна. Изменение её доли часто требует проверки данных до решения о бюджете.

Что согласовать перед сравнением каналов

Определите, какие контакты участвуют: только платная реклама, все маркетинговые сообщения или также прямые посещения. Если добавить прямой заход как обычный последний контакт, он может забрать заказ у рекламы. Если исключить, получится last non-direct, и это нужно назвать явно.

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

Для воспроизводимого выбора полезно отдельно разобрать равное время и дополнительный ключ: Последняя запись на клиента в SQL: как разрешить ничью по времени.

Следующий уровень SQL-практики

В «Продвинутом SQL» вы разбираете сложные соединения, оконный выбор строк и последовательности событий. Автоматические проверки на SQLite требуют учитывать равные даты, дубли и пустые случаи. Затем вы применяете приёмы в цельных кейсах и собираете аналитическую витрину магазина. Откройте программу «Продвинутого SQL».