Как с помощью оконной функции определить первый источник трафика (first touch), приведший каждого пользователя к покупке?

Короткий ответ

Нужно пронумеровать все касания (визиты/клики) каждого пользователя функцией ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time) и оставить только строки с номером 1 — это и будет первое касание (first touch) для атрибуции.

Как это работает подробнее

Как это работает: PARTITION BY user_id группирует все касания одного пользователя, ORDER BY touch_time сортирует их по времени внутри группы, и ROW_NUMBER() = 1 однозначно выбирает самое раннее касание, даже если у пользователя их были десятки.

Пример кода

PostgreSQL: first-touch до первой покупкиКопировать

WITH first_purchase AS (
  SELECT user_id, MIN(purchase_time) AS purchase_time
  FROM purchases
  GROUP BY user_id
), ranked_touches AS (
  SELECT t.user_id, t.channel, t.touch_time,
         ROW_NUMBER() OVER (
           PARTITION BY t.user_id
           ORDER BY t.touch_time, t.touchpoint_id
         ) AS rn
  FROM touchpoints AS t
  JOIN first_purchase AS p ON p.user_id = t.user_id
  WHERE t.touch_time <= p.purchase_time
)
SELECT user_id, channel, touch_time
FROM ranked_touches
WHERE rn = 1;

Что использовать на практике

• На практике: first-touch — одна из множества моделей атрибуции (есть также last-touch, линейная, с затухающим весом и т.д.), и выбор модели — не технический, а методологический вопрос: разные модели по-разному распределяют «вклад» каждого канала и могут давать очень разные выводы о том, какой канал эффективнее. Перед тем как использовать first-touch, стоит явно обозначить, что это осознанный выбор модели, а не единственно верный способ атрибуции.
• Проверка: Проверка: сумма users_cnt по всем каналам должна равняться общему числу уникальных пользователей в touchpoints.

Подводные камни

• Частые ошибки: при равных touch_time (например, два события залогированы в одну и ту же секунду) порядок между ними без дополнительного критерия сортировки не детерминирован — это может давать нестабильные результаты «первого касания» при повторных запусках; путают атрибуцию по первому касанию к покупке с первым касанием вообще за всю историю пользователя, если период анализа не ограничен явно.