Как посчитать воронку в SQL: шаги, конверсия и drop-off

Коротко

Воронка — это последовательность шагов от входа в сценарий до целевого действия. Для каждого шага считаем уникальных пользователей, затем сравниваем соседние уровни. Конверсия шага и общая конверсия — разные показатели, поэтому их нельзя подписывать одним словом “conversion”.

• Сначала назови событие каждого шага и его grain.
• Для пользователей используй COUNT(DISTINCT user_id).
• Локальная конверсия шага считается от предыдущего шага.
• Общая конверсия считается от первого шага воронки.
• Проверь порядок событий и временное окно между шагами.

Определи воронку до SQL

Возьмём сценарий SaaS-продукта: пользователь открыл приложение, создал проект, пригласил коллегу и выполнил core action. Один и тот же человек может повторять события, поэтому шаг воронки — это не количество строк в events, а факт того, что пользователь выполнил условие.

Базовый запрос: пользователи на каждом шаге

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

select
  event_name,
  count(distinct user_id) as users_count
from events
where event_date >= date '2026-06-01'
  and event_date < date '2026-07-01'
  and event_name in (
    'app_open',
    'project_created',
    'invite_sent',
    'core_action'
  )
group by event_name
order by users_count desc;

Последовательная воронка через первые даты

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

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

with first_steps as (
  select
    user_id,
    min(event_date) filter (where event_name = 'app_open') as opened_at,
    min(event_date) filter (where event_name = 'project_created') as project_at,
    min(event_date) filter (where event_name = 'invite_sent') as invite_at,
    min(event_date) filter (where event_name = 'core_action') as core_at
  from events
  group by user_id
),
ordered_users as (
  select *
  from first_steps
  where opened_at is not null
    and project_at > opened_at
    and invite_at > project_at
    and core_at > invite_at
)
select
  count(*) as completed_funnel_users
from ordered_users;

Локальная и общая конверсия

Пусть воронка имеет 10 000 входов, 6 200 проектов, 2 800 приглашений и 1 700 core actions. Конверсия вход → проект равна 62%, проект → приглашение — 45%, приглашение → ценность — 61%. Общая конверсия от входа до ценности — 17%. Эти числа отвечают на разные вопросы.

Окно между шагами меняет ответ

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

• События одной сессии — узкое окно для коротких сценариев.
• 24 часа — часто разумный старт для активации.
• 7 или 14 дней — вариант для длинного B2B-онбординга.
• Слишком широкое окно смешивает разные попытки пользователя.
• Слишком узкое окно записывает нормальную задержку как потерю.

Ошибки в SQL-воронках

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

• Считать события вместо уникальных пользователей.
• Использовать разные периоды для разных шагов.
• Не фиксировать порядок событий и временное окно.
• Сравнивать локальную конверсию одного шага с общей конверсией другого.
• Менять определение шага посреди эксперимента и сравнивать несопоставимые периоды.
• Делать вывод по маленькому сегменту без показа его размера.

Сначала собери user-level флаги

Для сложной воронки удобнее сначала получить одну строку на пользователя и boolean-флаги шагов. Так видно, кто дошёл до каждого этапа, а повторные события не меняют размер аудитории. На этом уровне можно добавить первые timestamps и проверить порядок отдельно от финальной агрегации.

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

После user-level слоя проверь монотонность строгой последовательной воронки: пользователей на шаге 4 не может быть больше, чем на шаге 3. Если это произошло, либо шаги не последовательны, либо запрос считает разные базы. Такая проверка быстро ловит методологическую ошибку.

with user_steps as (
  select
    user_id,
    bool_or(event_name = 'app_open') as opened,
    bool_or(event_name = 'project_created') as created_project,
    bool_or(event_name = 'invite_sent') as invited,
    bool_or(event_name = 'core_action') as reached_value
  from events
  where event_date >= date '2026-07-01'
    and event_date < date '2026-08-01'
  group by user_id
)
select
  count(*) filter (where opened) as opened_users,
  count(*) filter (where opened and created_project) as project_users,
  count(*) filter (where opened and created_project and invited) as invited_users,
  count(*) filter (where opened and created_project and invited and reached_value) as value_users
from user_steps;

Drop-off нужно разложить на причины

Большой отвал между шагами — не диагноз. Пользователь мог не понять интерфейс, получить ошибку, не иметь нужного права или просто не считать шаг обязательным. SQL показывает место потери, но причина требует дополнительных полей: platform, error_code, plan, traffic_source или версию приложения.

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

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

Сквозной пример: воронка «Маяка» от регистрации до отчёта

Разберём воронку целиком на учебной базе SQL-тренажёра. Продукт — сервис для совместной работы: пользователь регистрируется, открывает приложение, создаёт рабочее пространство и делает первый отчёт. Четыре шага, восемнадцать пользователей, первая неделя июня.

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

Запрос: одна строка на пользователя, потом свод

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

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

WITH steps AS (               -- зерно: один пользователь
  SELECT
    u.user_id,
    u.channel,
    max(CASE WHEN e.event_name = 'app_open'          THEN 1 ELSE 0 END) AS opened,
    max(CASE WHEN e.event_name = 'workspace_created' THEN 1 ELSE 0 END) AS activated,
    max(CASE WHEN e.event_name = 'report_created'    THEN 1 ELSE 0 END) AS reported
  FROM users u
  LEFT JOIN events e ON e.user_id = u.user_id
  GROUP BY u.user_id, u.channel
)
SELECT
  count(*)                                        AS signups,
  sum(opened)                                     AS opened,
  sum(activated)                                  AS activated,
  sum(reported)                                   AS reported,
  round(100.0 * sum(activated) / count(*), 1)     AS activation_rate,
  round(100.0 * sum(reported) / nullif(sum(activated), 0), 1) AS report_rate
FROM steps;

Разрез по каналам: где именно теряются люди

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

На учебной базе картина получается выразительная. Organic доходит до активации в 83% случаев, paid_search — в 33%, при абсолютно одинаковом числе регистраций. Шесть человек против шести, пять активаций против двух. При взгляде только на общее число регистраций оба канала выглядели бы одинаково успешными.

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

WITH steps AS (
  SELECT
    u.user_id,
    u.channel,
    max(CASE WHEN e.event_name = 'workspace_created' THEN 1 ELSE 0 END) AS activated,
    max(CASE WHEN e.event_name = 'report_created'    THEN 1 ELSE 0 END) AS reported
  FROM users u
  LEFT JOIN events e ON e.user_id = u.user_id
  GROUP BY u.user_id, u.channel
)
SELECT
  channel,
  count(*)                                    AS signups,
  sum(activated)                              AS activated,
  sum(reported)                               AS reported,
  round(100.0 * sum(activated) / count(*), 1) AS activation_rate
FROM steps
GROUP BY channel
ORDER BY activation_rate DESC;

Время между шагами говорит больше, чем сам процент

Конверсия отвечает на вопрос «сколько дошло», но молчит о том, насколько тяжело далась дорога. Две воронки с одинаковыми 55% выглядят одинаково, хотя в одной люди активируются за десять минут, а в другой — за три дня и после двух возвратов.

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

Этот же расчёт подсказывает, где ставить границу воронки. Если 90% активаций происходят в первые сутки, то окно в семь дней ничего не добавляет, зато заставляет ждать неделю ради каждого отчёта. А если активации размазаны на две недели, то недельная воронка систематически недосчитывает результат.

WITH first_activation AS (
  SELECT user_id, min(event_time) AS activated_at
  FROM events
  WHERE event_name = 'workspace_created'
  GROUP BY user_id
)
SELECT
  count(*)                                                          AS activated_users,
  min(CAST(a.activated_at AS DATE) - u.signup_date)                 AS min_days,
  max(CAST(a.activated_at AS DATE) - u.signup_date)                 AS max_days,
  count(*) FILTER (
    WHERE CAST(a.activated_at AS DATE) = u.signup_date
  )                                                                 AS same_day_activations
FROM users u
JOIN first_activation a ON a.user_id = u.user_id;

Пользователь, сессия или событие: три разные воронки

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

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

Практическое правило: единицу называют в заголовке отчёта. «Конверсия пользователей в активацию за 7 дней» невозможно прочитать неправильно, а просто «конверсия» — можно тремя способами.

Где воронка обманывает

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

Второе: незрелые пользователи. Тот, кто зарегистрировался вчера, ещё не успел дойти до конца, но в знаменателе уже стоит. Из-за этого воронка за «последние семь дней» всегда выглядит хуже, чем воронка за прошлый месяц, — и разница объясняется не продуктом, а сроком наблюдения. Лечится ограничением когорты: берём только тех, у кого с регистрации прошло не меньше заданного окна.

Третье: неучтённые входы. В нашей воронке 18 из 18 открыли приложение — на реальных данных так не бывает. Если первый шаг закрывает 100%, скорее всего событие пишется автоматически при регистрации, и как шаг воронки оно бесполезно. Такие «шаги» лучше убирать: они создают ощущение работы там, где ничего не измеряется.

И общее правило: рядом с процентом всегда показывайте числитель и знаменатель. «Конверсия 60%» на шести пользователях и на шести тысячах — разные утверждения, и решения по ним принимаются разные.

WITH firsts AS (
  SELECT
    user_id,
    min(CASE WHEN event_name = 'workspace_created' THEN event_time END) AS activated_at,
    min(CASE WHEN event_name = 'report_created'    THEN event_time END) AS reported_at
  FROM events
  GROUP BY user_id
)
SELECT
  count(*)                                                       AS users,
  count(activated_at)                                            AS activated,
  count(*) FILTER (WHERE reported_at >= activated_at)             AS reported_in_order,
  count(*) FILTER (WHERE reported_at IS NOT NULL
                     AND activated_at IS NULL)                    AS reported_without_activation
FROM firsts;

Что делать с найденным провалом

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

Полезно разделять три типа провала. Технический: шаг физически не работает у части аудитории — на определённой платформе, версии приложения или в конкретной стране. Он узнаётся по резкому разрыву между сегментами, которые в остальном похожи. Мотивационный: человек дошёл, посмотрел и ушёл — конверсия ровная по сегментам, но низкая. И провал ожиданий: конкретный канал приводит людей, которым продукт не подходит; тогда падает только этот канал, как paid_search в нашем примере.

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

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

SELECT
  u.user_id,
  u.channel,
  u.signup_date
FROM users u
WHERE EXISTS (
  SELECT 1 FROM events e WHERE e.user_id = u.user_id AND e.event_name = 'app_open'
)
AND NOT EXISTS (
  SELECT 1 FROM events e WHERE e.user_id = u.user_id AND e.event_name = 'workspace_created'
)
ORDER BY u.signup_date, u.user_id;

Чеклист перед публикацией воронки

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

• Единица счёта названа в заголовке: пользователи, сессии или события.
• Знаменатель каждого перехода понятен: доля от предыдущего шага или от всей базы.
• Окно наблюдения задано, и незрелые пользователи не искажают последнюю точку.
• Порядок шагов проверен по первым датам, а не только по факту наличия события.
• Шаг, который проходят 100%, либо убран, либо объяснён (обычно это автоматическое событие).
• Рядом с процентами стоят абсолютные числа.
• К отчёту приложен список застрявших пользователей — с него начнётся следующее исследование.

Мини-практика и следующий шаг

Собери в тренажёре простую воронку app_open → core_action. Сначала посчитай уникальных пользователей по типу события, затем добавь проверку порядка через первые даты. После этого сравни локальную конверсию и drop-off по каждому переходу.