Воронка продаж в SQL — как посчитать на 5 шагов

Воронка продаж (funnel) — последовательность шагов, через которые проходит пользователь, чтобы стать клиентом. Например: посещение сайта → регистрация → подтверждение email → первая покупка. На каждом шаге часть юзеров теряется. Анализ воронки в SQL — базовый навык аналитика.

Содержание (5 разделов)

Зачем считать воронку
Базовая структура воронки в SQL
Пример: воронка регистрация → оплата
Типичные ошибки в SQL-воронке
Когортная воронка — продвинутый подход

Зачем считать воронку

Воронка отвечает на 3 главных вопроса бизнеса:

Где теряются деньги — какой шаг самый дырявый, туда нужно класть продуктовые усилия
Какая конверсия в продажу — это главная метрика whole funnel
Какой ROI каналов — сравнивать воронку для разных источников трафика
Какой эффект от изменений — A/B-тест меняет воронку или нет

Базовая структура воронки в SQL

Подход через JOIN таблиц с событиями:

Шаг 1: собрать пользователей, прошедших каждый этап
Шаг 2: посчитать число уникальных user_id на каждом этапе
Шаг 3: разделить N+1 этапа на N — это конверсия между шагами
Шаг 4: разделить последний этап на первый — это сквозная конверсия

Пример: воронка регистрация → оплата

Минимальный рабочий SQL:

WITH funnel AS (
SELECT user_id,
MAX(CASE WHEN event = 'signup' THEN 1 ELSE 0 END) AS step1,
MAX(CASE WHEN event = 'email_confirmed' THEN 1 ELSE 0 END) AS step2,
MAX(CASE WHEN event = 'first_purchase' THEN 1 ELSE 0 END) AS step3
FROM events GROUP BY user_id
)
SELECT
SUM(step1) AS signups,
SUM(step2) AS confirmed,
SUM(step3) AS purchased,
100.0 * SUM(step3) / SUM(step1) AS funnel_conversion
FROM funnel;

Типичные ошибки в SQL-воронке

5 граблей, на которые наступают аналитики:

Считают всех событий, а не уникальных пользователей. SUM(quantity) вместо COUNT(DISTINCT user_id)
Не учитывают порядок шагов. Юзер мог купить ДО подтверждения email — это уже не воронка
Берут разные временные окна для разных шагов. Регистрация Q1, покупка любая — неправильно
Игнорируют отказы. Юзер может вернуться через год — это не та же воронка
Не сегментируют. Конверсия на всех 5%, но на mobile 2% и desktop 8% — это разные истории

Когортная воронка — продвинутый подход

Базовая воронка теряет время. Когортная учитывает:

Когорта — пользователи зарегистрированные в одном месяце
Для каждой когорты — воронка до сегодня
Сравнение когорт между месяцами — улучшилась воронка или нет
Внимание к outliers — летние месяцы могут отличаться
Метрика «время до конверсии» — медиана дней от регистрации до покупки

Частые вопросы

Воронка должна идти строго по порядку?

Чаще да. Если бизнес-логика допускает покупку без регистрации (guest checkout) — нужно отдельно считать «зарегистрировались и купили» vs «купили без регистрации».

COUNT(DISTINCT) или WINDOW функция?

COUNT(DISTINCT) — стандарт. На очень больших таблицах используют HyperLogLog (approx-COUNT) для скорости. В ClickHouse — функция uniq().

Что такое drop-off rate?

Процент юзеров, которые НЕ перешли на следующий шаг. Например, signups 1000, confirmed 600 → drop-off 40%. Это противоположность conversion rate.

Какая типичная конверсия воронки?

Очень зависит от бизнеса. E-comm: 1-3% от посещения до покупки. SaaS: 1-5% от signup до paid. Mobile app: 0.5-2% от install до paying user.

Можно ли визуализировать воронку?

Да. В Tableau / Power BI / DataLens есть «funnel chart». Также можно через bar chart с уменьшающимися значениями. Главное — показать число и процент на каждом шаге.

Начать практику бесплатно →