Представьте, что вы запустили мобильное приложение для заказа пиццы. Тысячи людей открывают его каждый день, но до оплаты доходят единицы. И у аналитика возникает вопрос: где они теряются, в какой момент решают покинуть приложение? Ответить на этот вопрос помогает воронка. Воронка – это последовательность этапов, через которые проходит каждый пользователь от первого касания до целевого действия. Например: открыл приложение → зарегистрировался → положил товар в корзину → оплатил.
Почему для расчета воронки стоит использовать SQL? Потому что данные лежат в базе, а один запрос дает точный ответ без оглядки на BI-инструменты или платные дашборды. Причем запрос можно сохранить, переиспользовать, править даты и получить свежий срез за секунды.
Разберем три SQL-запроса, которые закроют 90% задач на воронку: от простого подсчета по этапам до расчета оттока с конверсией. Все примеры написаны на стандартном SQL и работают в популярных системах PostgreSQL, ClickHouse, BigQuery.
Подготовка данных
Прежде чем писать запрос, нужно понять, с чем мы работаем. Допустим, все события пользователей лежат в одной таблице events. Это типичная схема для продуктов, которые собирают аналитику через системы трекинга — например, Google Analytics 4, Amplitude или собственную систему логирования.
Таблица содержит три колонки. user_id — идентификатор пользователя, уникальное число или строка, по которому мы отличаем одного человека от другого. event_name — название события: «открыл приложение», «зарегистрировался», «добавил в корзину», «купил». event_time — момент, когда событие произошло, в формате «год-месяц-день часы:минуты:секунды» (в SQL это называется timestamp — тип данных для хранения даты и времени). Пример того, как выглядят данные:
Воронка, которую будем считать: app_open → registration → add_to_cart → purchase. Четыре этапа — от открытия приложения до покупки.
Перед тем как приступать к запросам, стоит проверить три вещи:
- Первое — нет ли дубликатов. Одна и та же комбинация user_id, event_name и event_time не должна встречаться дважды, иначе в воронке появятся «призраки».
- Второе — часовой пояс. Если события приходят из разных регионов, приведите event_time к единому времени, иначе «вчера» и «сегодня» могут смешаться.
- Третье — мы считаем уникальных пользователей, а не количество событий. Так один человек может десять раз открыть приложение, но в воронку он войдет только один раз.
Простой запрос: считаем воронку за один проход
Начнем с самого понятного подхода.
Идея: для каждого пользователя проверить, было ли у него нужное событие, а затем посчитать количество уникальных user_id по каждому этапу.
Это можно сделать одним запросом, используя условный оператор CASE WHEN и функцию COUNT(DISTINCT).
CASE WHEN — это конструкция SQL, которая работает как «если — то». Она проверяет условие и возвращает одно значение, если условие выполнено, и другое — если нет. В нашем случае: если название события равно ‘app_open’, возвращаем user_id, иначе возвращаем NULL (пустое значение, которое означает «нет данных»).
COUNT(DISTINCT …) — это функция подсчета уникальных значений. Слово DISTINCT здесь ключевое: без него функция посчитала бы все строки, включая повторяющиеся. С ним — только уникальные user_id, что и дает нам количество пользователей, а не количество событий.
Вот как выглядит запрос:
SELECT COUNT(DISTINCT CASE WHEN event_name = 'app_open' THEN user_id END ) AS step1_app_open, COUNT(DISTINCT CASE WHEN event_name = 'registration' THEN user_id END ) AS step2_registration, COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END ) AS step3_add_to_cart, COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END ) AS step4_purchase FROM events WHERE event_time >= '2025-01-01' AND event_time < '2025-02-01';
Разберем по строкам.
SELECT — команда, которая выбирает данные. Дальше четыре блока COUNT(DISTINCT CASE WHEN …) — по одному на каждый этап воронки. Конструкция AS step1_app_open задает имя колонке в результате — это просто подпись, чтобы было удобно читать. FROM events указывает, из какой таблицы берем данные. WHERE event_time >= ‘2025-01-01’ AND event_time < ‘2025-02-01’ ограничивает период — мы смотрим только январь 2025 года. Оператор AND означает «и то, и другое условие одновременно».
Результат — одна строка с количеством пользователей на каждом шаге:
Дальше начинаем интерпретировать результаты. Доли от первого этапа считаются легко: 2800 делим на 5000 и получаем 56%, 1500 на 5000 – 30%, 620 на 5000 – 12,4%. Уже на этом этапе видно, что от регистрации до корзины доходит чуть больше половины людей, а до оплаты только каждый восьмой.
Но у этого подхода есть серьезный недостаток: он не проверяет порядок, по которому шел пользователь.
Правильный запрос: воронка с соблюдением порядка этапов
Итак, недостаток предыдущего запроса в том, что он считает пользователя «дошедшим до корзины», даже если событие add_to_cart произошло раньше registration. Однако в воронке важна последовательность: человек должен проходить этапы по порядку. Если он сначала положил товар в корзину, а потом зарегистрировался – это не воронка, а другой сценарий, и считать его нужно отдельно.
Решение: для каждого пользователя находим время первого наступления каждого события, а затем проверяем, идут ли они в правильном порядке. Здесь пригодятся две вещи – агрегатная функция MIN() и конструкция WITH (CTE).
MIN() – функция, которая находит минимальное значение в группе. В нашем случае, самое раннее время, когда пользователь совершил конкретное событие. Если человек открывал приложение три раза, MIN(event_time) вернет время первого открытия.
WITH – это конструкция, которая создает временные таблицы внутри запроса. Такие таблицы называются CTE (Common Table Expression, общее табличное выражение). Удобно, когда нужно разбить сложный запрос на шаги: сначала подготовить данные, потом посчитать результат. Визуально это делает запрос читаемым, как рецепт, где каждый шаг описан отдельно.
Сам запрос выглядит так:
WITH user_steps AS ( SELECT user_id, MIN(CASE WHEN event_name = 'app_open' THEN event_time END) AS t1, MIN(CASE WHEN event_name = 'registration' THEN event_time END) AS t2, MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS t3, MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS t4 FROM events WHERE event_time >= '2025-01-01' AND event_time < '2025-02-01' GROUP BY user_id ) SELECT COUNT(*) AS step1_app_open, COUNT(CASE WHEN t2 IS NOT NULL AND t2 >= t1 THEN 1 END) AS step2_registration, COUNT(CASE WHEN t3 IS NOT NULL AND t3 >= t2 THEN 1 END) AS step3_add_to_cart, COUNT(CASE WHEN t4 IS NOT NULL AND t4 >= t3 THEN 1 END) AS step4_purchase FROM user_steps WHERE t1 IS NOT NULL;
Разберем логику. Блок WITH user_steps AS (…) – это первый шаг, где для каждого пользователя мы собираем время первого наступления каждого события. GROUP BY user_id группирует строки по пользователю – то есть все события одного человека схлопываются в одну строку. Функция MIN(CASE WHEN … THEN event_time END) для каждого типа события находит самое раннее время. Если события не было, CASE вернет NULL, и MIN тоже вернет NULL – это важно, мы используем это дальше.
Второй шаг – основной SELECT. COUNT(*) считает всех пользователей, у которых есть t1 (то есть кто открыл приложение – это точка входа в воронку, фильтр WHERE t1 IS NOT NULL отсекает тех, у кого даже первого шага не было). Дальше для каждого следующего этапа проверяем два условия: что время события не пустое (IS NOT NULL) и что оно произошло не раньше предыдущего (t2 >= t1, t3 >= t2, t4 >= t3).
Конструкция IS NOT NULL в SQL проверяет, что значение существует. Если хотя бы одно условие не выполнено, CASE возвращает NULL, и COUNT его игнорирует.
Сравнение через >= (больше или равно) позволяет считать события, произошедшие в одну секунду, как корректный переход. Если в вашей системе события могут приходить с разной точностью, можно использовать строгое > – все зависит от специфики данных.
Результат с учетом порядка:
Цифры стали ниже, чем в простом запросе – это нормально. Часть пользователей, которых «насчитал» первый запрос, на самом деле проходила этапы вразнобой: кто-то сначала положил товар в корзину, а потом зарегистрировался. Теперь они исключены, и воронка показывает реальную картину.
Считаем отток и конверсию между этапами
Конечно, здорово знать, сколько людей дошло до этапа. Другое дело – понять, сколько ушло на переходе между ними. Для этого посчитаем два показателя: drop-off rate (доля оттока) и conversion rate (доля перехода).
Drop-off rate показывает, какой процент людей ушел между двумя соседними этапами. Формула простая: берем количество пользователей на предыдущем этапе, вычитаем количество на текущем, делим результат на предыдущий. Conversion rate – это обратный показатель: делим текущий этап на предыдущий. Оба можно выразить в процентах, умножив на 100.
Удобнее всего считать эти показатели через оконную функцию LAG(). Оконные функции – особый класс функций в SQL, которые работают не с группами строк, как агрегатные, а с отдельными строками, но «видят» соседние. Функция LAG() берет значение из предыдущей строки в упорядоченном наборе. Например, если на этапе «регистрация» было 2750 человек, а на этапе «корзина» – 1380, то LAG() подставит 2750 в строку «корзина», и мы сможем вычислить разницу.
Чтобы LAG() понимала, какая строка «предыдущая», нужно задать порядок. Для этого используется конструкция OVER (ORDER BY …). Она указывает, по какому правилу сортировать строки перед тем, как брать соседние. В нашем случае порядок – это номер этапа в воронке.
Вот запрос:
WITH funnel AS ( SELECT 'app_open' AS step_name, 5000 AS users UNION ALL SELECT 'registration', 2750 UNION ALL SELECT 'add_to_cart', 1380 UNION ALL SELECT 'purchase', 540 ) SELECT step_name, users, LAG(users) OVER (ORDER BY CASE step_name WHEN 'app_open' THEN 1 WHEN 'registration' THEN 2 WHEN 'add_to_cart' THEN 3 WHEN 'purchase' THEN 4 END ) AS prev_users, LAG(users) OVER (ORDER BY CASE step_name WHEN 'app_open' THEN 1 WHEN 'registration' THEN 2 WHEN 'add_to_cart' THEN 3 WHEN 'purchase' THEN 4 END ) - users AS lost_users, ROUND( 100.0 * ( LAG(users) OVER (ORDER BY CASE step_name WHEN 'app_open' THEN 1 WHEN 'registration' THEN 2 WHEN 'add_to_cart' THEN 3 WHEN 'purchase' THEN 4 END ) - users ) / LAG(users) OVER (ORDER BY CASE step_name WHEN 'app_open' THEN 1 WHEN 'registration' THEN 2 WHEN 'add_to_cart' THEN 3 WHEN 'purchase' THEN 4 END ), 1 ) AS drop_off_pct FROM funnel;
А теперь разберемся в деталях. UNION ALL — это оператор, который объединяет результаты нескольких SELECT в одну таблицу. Здесь он используется, чтобы собрать тестовые данные. В реальной жизни CTE funnel заменяется подзапросом из предыдущего раздела, но здесь значения внесены отдельно, чтобы показать логику наглядно.
Функция ROUND(x, 1) округляет число x до одного знака после запятой. Умножение на 100.0 переводит долю в проценты, поэтому важна запись именно с точкой, потому что в SQL деление двух целых чисел дает целое число, и 540 / 2750 без точки вернуло бы 0.
Итог:
В результате получился такой срез информации. Первая строка: app_open не имеет предыдущего этапа, поэтому значения NULL. Дальше видна картина – между открытием приложения и регистрацией уходит 45% людей. Между регистрацией и корзиной — почти 50%. А между корзиной и оплатой — больше 60% тех, кто положил товар, но не дошел до покупки. Это самое узкое место воронки, а также важный сигнал для продуктовой команды, что нужно выяснить проблему. Возможно люди отказываются от покупке из-за дорогой доставки, сложного оформления заказа или технической ошибке на последнем экране.
Частые ошибки и как их избежать
Первая и самая частая ошибка при построении воронки с помощью SQL – специалисты считают события вместо пользователей. Один пользователь генерирует десятки событий app_open: открыл приложение, свернул, снова открыл. Если считать сырые строки без DISTINCT, то можно получить 50 000 на первом этапе вместо 5 000 – такая воронка будет бессмысленной. Поэтому всегда используйте COUNT(DISTINCT user_id) или группировку по user_id на раннем шаге.
Вторая ошибка – не учитывается порядок этапов. Без проверки t2 >= t1 в воронку попадают пользователи, которые смотрят корзину до регистрации. Цифры выглядят «хорошими», но не отражают реальный путь. Решение – нужно использовать подход с MIN(event_time) и сравнением временных меток, как это обозначено в разделе про воронку с порядком.
Третья ошибка — нет временного интервала. Если аналитик забыл добавить условие WHERE event_time >= … , то в воронку попали данные за все время существования продукта. Сезонность, изменения интерфейса, миграции данных сделают результат несопоставимым. Чтобы избежать этой ошибки, всегда указывайте период анализа, даже если кажется, что данных немного.
Четвертая ошибка – путаница с NULL в CASE. Если у пользователя не было события purchase, то MIN(CASE WHEN event_name = ‘purchase’ THEN event_time END) вернет NULL. Дальше NULL >= t3 в PostgreSQL дает NULL, а не TRUE – и COUNT это правильно игнорирует. Но в некоторых хранилищах поведение может отличаться: например, в MySQL сравнение с NULL тоже дает NULL, а вот в отдельных аналитических базах могут быть нюансы. Поэтому проверьте, как ваша база обрабатывает сравнения с NULL через тестовый запрос на нескольких строках.
Читаем данные через визуал
Таблица с числами, которые вы получаете по итогу, – хороша для внутреннего использования, например, среди аналитиков. Однако для широкой аудитории, например, менеджера или заказчика собранные данные лучше оформить в виде визуала. График наглядно и сразу показывает, где «сужается» поток и экономит время специалиста.
Самый простой способ – выгрузить результат запроса в Excel или Google Sheets и построить горизонтальную столбчатую диаграмму. Это занимает пару минут и не требует кода, но придется обновлять данные при каждом новом периоде.
Однако если воронка считается регулярно, удобнее автоматизировать ее. Например, добавить скрипт на Python с библиотекой matplotlib. Это буквально 10–15 строк кода поверх SQL-запроса, и воронка генерируется автоматически. Библиотека matplotlib – популярный и бесплатный инструмент для построения графиков в Python, который устанавливается буквально одной командой pip install matplotlib.
Третий вариант визуализации данных – сделать SQL-запрос как источник данных в BI-инструменте: Apache Superset, Yandex DataLens, Tableau. Большинство из них поддерживают тип диаграммы «воронка» из коробки. Этот вариант окупается, если воронку регулярно смотрит команда: можно добавить фильтры по датам, сегментам, источникам трафика.
