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

Коротко

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

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

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

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

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

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

На учебной базе «Маяк» за июнь это 1 042 открывших приложение, 710 создавших пространство, 238 пригласивших и 239 выгрузивших отчёт. Четвёртый «шаг» больше третьего: это размеры событий, а не ступени.

select
  event_name,
  count(distinct user_id) as users_count
from events
where event_time >= timestamp '2026-06-01 00:00:00'
  and event_time < timestamp '2026-07-01 00:00:00'
  and event_name in (
    'app_open',
    'workspace_created',
    'invite_sent',
    'export_completed'
  )
group by event_name
order by users_count desc, event_name;

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

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

В примере шаги сравниваются по времени события, а не по календарному дню. На учебной базе это принципиально: пространство создают в день первого открытия, и строгое сравнение дат отсеяло бы всех, а нестрогое не проверило бы порядок внутри дня. Если в таблице есть только дата, SQL не сможет восстановить реальную последовательность. Весь путь по порядку прошли 246 человек из 4 613 — при 988 выгрузивших отчёт: остальные обошлись без приглашения.

with first_steps as (
  select
    user_id,
    min(event_time) filter (where event_name = 'app_open') as opened_at,
    min(event_time) filter (where event_name = 'workspace_created') as workspace_at,
    min(event_time) filter (where event_name = 'invite_sent') as invite_at,
    min(event_time) filter (where event_name = 'export_completed') as export_at
  from events
  group by user_id
),
ordered_users as (
  select *
  from first_steps
  where opened_at is not null
    and workspace_at > opened_at
    and invite_at > workspace_at
    and export_at > invite_at
)
select
  count(*) as completed_funnel_users
from ordered_users;

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

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

Конверсии шагов перемножаются, и это меняет порядок работ

Три локальных конверсии условной воронки из предыдущего раздела — 62, 45 и 61 — в сумме дают 168%. Такого числа в воронке нет и быть не может: каждый процент посчитан от своего знаменателя, и складывать их так же бессмысленно, как складывать курсы валют. Общая конверсия получается умножением: 0,62 × 0,452 × 0,607 = 0,17, те самые 17% от входа до ценности.

Из умножения следует свойство, которое меняет приоритеты. Любой прирост на раннем шаге проходит через все множители, стоящие после него, и доезжает до конца сильно уменьшенным. Поднять вход → проект с 62% до 65% — значит получить 6 500 проектов вместо 6 200, но до ценности из них дойдут не все: 6 500 × 0,452 × 0,607 = 1 783 вместо 1 700. Три процентных пункта на входе превратились в 83 человека на выходе.

Теперь посмотрите, куда показывает drop-off. В абсолютных людях больше всего теряет первый переход: 3 800 человек против 3 400 и 1 100. По локальной конверсии худший — второй: 45% против 62% и 61%. Два способа прочитать одну таблицу указывают на разные шаги, и ни один из них ещё не является ответом на вопрос, что чинить.

Ответ даёт третий расчёт: сколько людей на выходе добавит один процентный пункт на шаге. Это размер базы шага, умноженный на конверсию всего, что идёт после. Для входа — 100 × 0,452 × 0,607 ≈ 27. Для перехода к приглашению — 62 × 0,607 ≈ 38. Для последнего шага — 28, потому что после него множителей нет. Второй переход выигрывает не потому, что у него самая низкая конверсия, а потому, что у него достаточно большая база и длинный хвост впереди.

Последняя поправка — про реалистичный потолок. Шаг, который уже отдаёт 61%, поднять на десять пунктов почти невозможно, а шаг на 45% — задача обычная. Поэтому вклад считают не на абстрактный пункт, а на тот прирост, в который команда верит: плюс два пункта на входе и плюс восемь на приглашении — это 55 и 301 человек, разница уже не в полтора раза, а в пять с половиной.

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

-- учебная вставка: условные числа из примера, в учебной базе их нет
WITH funnel(step_no, step, users) AS (
  VALUES (1, 'вход', 10000),
         (2, 'проект', 6200),
         (3, 'приглашение', 2800),
         (4, 'ценность', 1700)
)
SELECT
  step,
  users,
  lag(users) OVER w - users                                        AS lost_users,
  round(100.0 * users / lag(users) OVER w, 1)                      AS from_previous,
  round(100.0 * users / first_value(users) OVER w, 1)              AS from_first
FROM funnel
WINDOW w AS (ORDER BY step_no)
ORDER BY step_no;

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

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

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

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

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

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

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

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

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

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

with cohort as (
  -- входная когорта: зарегистрировались в июле
  select user_id, signup_date
  from users
  where signup_date >= date '2026-07-01'
    and signup_date < date '2026-08-01'
),
user_steps as (
  select
    c.user_id,
    bool_or(e.event_name = 'app_open') as opened,
    bool_or(e.event_name = 'workspace_created') as created_workspace,
    bool_or(e.event_name = 'invite_sent') as invited,
    bool_or(e.event_name = 'export_completed') as reached_value
  from cohort c
  join events e
    on e.user_id = c.user_id
   and e.event_time < c.signup_date + interval '7 days'  -- окно: 7 дней с регистрации
  group by c.user_id
)
select
  count(*) filter (where opened) as opened_users,
  count(*) filter (where opened and created_workspace) as workspace_users,
  count(*) filter (where opened and created_workspace and invited) as invited_users,
  count(*) filter (where opened and created_workspace and invited and reached_value) as value_users
from user_steps;

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

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

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

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

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

Разберём воронку целиком на учебной базе «Маяк». Продукт — сервис для совместной работы: пользователь регистрируется, открывает приложение, создаёт рабочее пространство и делает первый отчёт. Четыре шага, 4 613 пользователей, регистрации с 1 июня по 29 августа.

Первое решение принимается до 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;

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

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

На учебной базе картина получается выразительная. Referral доходит до активации в 74,7% случаев, paid_search — в 37,0%, при почти одинаковом числе регистраций: 1 037 человек против 1 015, 775 активаций против 376. При взгляде только на общее число регистраций оба канала выглядели бы одинаково успешными.

Вывод из такого разреза почти никогда не звучит как «канал плохой». Гораздо чаще это разрыв между обещанием рекламы и первым экраном продукта: человек пришёл за одним, а на входе увидел другое. Проверяется это не SQL, а просмотром сессий и разговором с несколькими пришедшими — но найти, кого именно смотреть, помогает как раз запрос. Что тот же канал даёт в деньгах — первую оплату и выручку на зарегистрированного — считает сквозной разбор рекламного канала в 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, channel;

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

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

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

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

Запрос берёт зарегистрированных по 9 августа: срок первой оплаты у них вышел. Активация вырождена — все 1 950 пространств созданы в день регистрации. Первая оплата (756 человек) приходит на 3–20-й день, медиана — 11, в первые семь дней — 20,2%: недельное окно потеряло бы четыре пятых оплат. Так собраны учебные данные.

WITH first_steps AS (           -- зерно: пользователь + шаг
  SELECT user_id, 'активация' AS step, CAST(min(event_time) AS DATE) AS step_date
  FROM events
  WHERE event_name = 'workspace_created'
  GROUP BY user_id
  UNION ALL
  SELECT user_id, 'первая оплата', min(paid_at)
  FROM payments
  GROUP BY user_id
)
SELECT
  s.step,
  count(*)                                                  AS users,
  min(s.step_date - u.signup_date)                          AS min_days,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY s.step_date - u.signup_date)                   AS median_days,
  max(s.step_date - u.signup_date)                          AS max_days,
  round(100.0 * count(*) FILTER (
    WHERE s.step_date - u.signup_date < 7) / count(*), 1)   AS within_7_days_pct
FROM users u
JOIN first_steps s ON s.user_id = u.user_id
WHERE u.signup_date < DATE '2026-08-10'   -- срок первой оплаты (3–20 дней) у них вышел
GROUP BY s.step
ORDER BY s.step;

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

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

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

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

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

Первое: воронка из флагов не проверяет порядок. Пользователь, который каким-то образом создал отчёт раньше рабочего пространства, попадёт в оба шага и не вызовет подозрений. Если порядок важен, шаги сравнивают по первым датам: время второго шага должно быть не раньше первого. На учебной базе проверка нарушений не находит: все 1 775 отчётов созданы после пространства. Зато 456 из 1 135 пригласивших пространства не создавали: флаг без условия на предыдущий шаг их засчитает.

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

Третье: неучтённые входы. В нашей воронке 4 613 из 4 613 открыли приложение — на реальных данных так не бывает. Если первый шаг закрывает 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 в нашем примере.

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

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

SELECT
  u.user_id,
  u.channel,
  u.signup_date
FROM users u
WHERE u.channel = 'paid_search'
AND 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 DESC, u.user_id
LIMIT 20;

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

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

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

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

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