Воронка продаж в SQL: шаги, конверсия и точки отсева

Прямой ответ

Воронка продаж считает число пользователей на каждом шаге и долю перехода между шагами, поэтому точка отсева определяется по наименьшей конверсии.

WITH steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event = 'view'     THEN ts END) AS s1,
    MIN(CASE WHEN event = 'cart'     THEN ts END) AS s2,
    MIN(CASE WHEN event = 'checkout' THEN ts END) AS s3,
    MIN(CASE WHEN event = 'purchase' THEN ts END) AS s4
  FROM events
  GROUP BY user_id
)
SELECT
  COUNT(s1) AS step1,
  COUNT(s2) AS step2,
  COUNT(s3) AS step3,
  COUNT(s4) AS step4
FROM steps
WHERE s2 > s1 AND s3 > s2 AND s4 > s3;

Порядок шагов проверяется сравнением времени событий, поэтому пользователь, выполнивший шаги в ином порядке, не попадает в воронку. О когортах — в статье про когортный анализ SQL.

Шаги воронки и порядок событий

Шаг воронки определяется событием, поэтому состав воронки соответствует пути пользователя, а не набору всех событий.

Событие шага. Каждому шагу соответствует событие, поэтому прохождение шага определяется наличием события.

Порядок шагов. Время события следующего шага должно превышать время предыдущего, поэтому последовательность проверяется явно.

Повторные события. Первое событие шага определяет время прохождения, поэтому повторные вхождения шага не меняют результат.

Практическое следствие: воронка без проверки порядка считает пользователем шага того, кто выполнил события в ином порядке, поэтому конверсия завышается.

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

Конверсия между шагами

Конверсия считается как доля пользователей, перешедших на следующий шаг, поэтому значение показывает долю от предыдущего шага.

Конверсия шага. Число пользователей шага делится на число пользователей предыдущего шага, поэтому значение выражается в долях.

Сквозная конверсия. Число пользователей последнего шага делится на число пользователей первого, поэтому доля показывает итог пути.

Точка отсева. Шаг с наименьшей конверсией определяет место наибольшего отсева, поэтому улучшение направляется на этот шаг.

Практическое следствие: конверсия, посчитанная от первого шага для каждого шага, не показывает отсев между соседними шагами, поэтому точка отсева не видна.

Точки отсева и проверка результата

Точка отсева определяется наименьшей конверсией между шагами, поэтому улучшение направляется на шаг с наибольшим отсевом.

Наименьшая конверсия. Шаг с наименьшей долей перехода определяет место наибольшего отсева пользователей.

Сравнение сегментов. Конверсия сравнивается между сегментами пользователей, поэтому различие указывает на причину отсева.

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

Практическое следствие: отсев, не подтверждённый проверкой порядка событий, может объясняться не поведением пользователей, а определением шагов.

О сегментации пользователей — в статье про сегментацию SQL; о расчёте на ClickHouse — в статье про ClickHouse для аналитики.

Практика: расчёт воронки продаж

1. Определяйте шаги по событиям пути пользователя. Состав воронки соответствует целевому пути, а не набору всех событий.

2. Проверяйте порядок шагов по времени событий. Пользователь, прошедший шаги вне порядка, не попадает в воронку.

3. Берите первое событие шага. Повторные вхождения шага не меняют время прохождения.

4. Считайте конверсию от предыдущего шага. Доля показывает отсев между соседними шагами.

5. Добавляйте сквозную конверсию. Доля от первого шага показывает итог пути.

6. Определяйте точку отсева по наименьшей конверсии. Улучшение направляется на шаг с наибольшим отсевом.

7. Ограничивайте окно времени воронки. Период между первым и последним шагом отсекает нерелевантные пути.

Рекомендации:

1. Не считайте воронку без проверки порядка. Конверсия завышается за счёт пользователей, прошедших шаги вне порядка.
2. Учитывайте повторные события шага. Первое событие определяет время прохождения, поэтому повторные вхождения не учитываются.
3. Сопоставляйте конверсию между соседними шагами. Конверсия от первого шага не показывает точку отсева.
4. Фиксируйте размер выборки каждого шага. Малое число пользователей на шаге даёт разброс конверсии.
5. Пересматриваем шаги при изменении пути пользователя. Новое событие пути требует добавления шага в воронку.

Практический сценарий: конверсия завышалась из-за порядка событий

Контекст: отчёт показывал высокую конверсию в покупку, но фактическое число покупок не соответствовало расчёту.

1. Диагностика. Проверка показала, что часть пользователей выполняла событие покупки до события добавления в корзину. Воронка считала таких пользователей прошедшими все шаги, поскольку проверялось только наличие событий.
2. Причина. Порядок событий не проверялся, поэтому пользователи, прошедшие шаги вне целевого пути, попадали в расчёт конверсии.
3. Действия. (а) Добавили сравнение времени событий, чтобы следующий шаг учитывался только после предыдущего; (б) посчитали долю пользователей, прошедших шаги вне порядка, чтобы оценить масштаб расхождения; (в) пересчитали конверсию с проверкой порядка и сравнили с прежним отчётом; (г) ограничили окно времени воронки, чтобы отсеять пути с большим интервалом между шагами; (д) настроили регулярный пересчёт, чтобы конверсия обновлялась.
4. Результат. Конверсия соответствует фактическому числу покупок, поэтому отчёт отражает целевой путь пользователя.

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

FAQ

Как определить шаги воронки в SQL? По событиям пути пользователя. Каждому шагу соответствует событие, прохождение определяется наличием события в нужном порядке.

Зачем проверять порядок событий? Пользователь, выполнивший шаги вне целевого порядка, не прошёл воронку. Без проверки конверсия завышается.

Как найти точку отсева? По наименьшей конверсии между соседними шагами. Конверсия от первого шага не показывает отсев между шагами.

Что даёт сквозная конверсия? Долю пользователей последнего шага от первого. Значение показывает итог пути, но не указывает точку отсева.

Источники: ClickHouse window view, PostgreSQL aggregate functions, PostgreSQL WITH — ссылки в шапке.