Воронка в SQL: как считать шаги и drop-off без неверного знаменателя

Сигнал в данных

Почему сумма конверсий по этапам не совпадает с конверсией от первого шага?

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

Если пользователь сначала оплатил, а потом посмотрел товар, он не должен попадать в последовательность view → cart → purchase. Воронка отвечает на путь, а не на наличие событий когда-либо.

Как устроен расчёт

Сырые события имеют grain user × event × timestamp. Для воронки рабочим слоем становится user × step × first_step_at, иначе повторные события размножат аудиторию.

Собери первую дату каждого шага на пользователя, проверь порядок дат и затем агрегируй пользователей по достигнутому уровню. Отдельно вычисли conversion from previous и from first.

Если показатель строится по событиям, отдельно проверь повторную отправку, идентичность пользователя и часовой пояс. Если данные приходят из бизнес-системы, сопоставь техническое поле с тем, как команда реально принимает решение.

Практический сценарий

Если пользователь сначала оплатил, а потом посмотрел товар, он не должен попадать в последовательность view → cart → purchase. Воронка отвечает на путь, а не на наличие событий когда-либо.

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

WITH steps AS (\n  SELECT user_id, MIN(event_at) FILTER (WHERE event_name = 'view') AS view_at, MIN(event_at) FILTER (WHERE event_name = 'cart') AS cart_at, MIN(event_at) FILTER (WHERE event_name = 'purchase') AS purchase_at\n  FROM events\n  GROUP BY user_id\n)\nSELECT COUNT(*) FILTER (WHERE view_at IS NOT NULL) AS viewed, COUNT(*) FILTER (WHERE cart_at > view_at) AS cart_after_view, COUNT(*) FILTER (WHERE purchase_at > cart_at) AS paid_after_cart\nFROM steps;

Контрольные случаи

Опасны независимые COUNT DISTINCT, фильтр только по названию события, отсутствие окна и смешение новых и старых пользователей в одной базе.

Отдельно протестируй пустой результат, NULL, дубль, граничную дату и объект без связанной записи. Эти случаи не являются редкими исключениями: именно они чаще всего превращают рабочий показатель в красивую, но неверную цифру.

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

Чтение визуализации

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

На визуализации «Пользователи на последовательных шагах» сравнивай не только максимальное значение. Проверь, одинаковы ли базы, не скрыта ли неполная дата и не меняется ли знаменатель от категории к категории.

Небольшое упражнение

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

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

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

Что передать команде

Для продуктового решения сначала показывай абсолютные пользователи, затем конверсию от предыдущего шага и от входа. Drop-off используй как повод для сегментации, а не как готовую причину.

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