9 апреля 2026 г.

Воронка конверсии в SQL — как построить запрос

Коротко

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

Простая воронка: COUNT DISTINCT на каждом шаге

Допустим, есть таблица events с колонками user_id, event_name и created_at. Шаги воронки: visit → registration → first_session → purchase.

SELECT
    COUNT(DISTINCT user_id) AS visit,
    COUNT(DISTINCT CASE WHEN event_name = 'registration' THEN user_id END) AS registration,
    COUNT(DISTINCT CASE WHEN event_name = 'first_session' THEN user_id END) AS first_session,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchase
FROM events
WHERE event_name IN ('visit', 'registration', 'first_session', 'purchase')

Это условная агрегация — один проход по таблице, четыре метрики. COUNT(DISTINCT ...) гарантирует, что каждый пользователь считается один раз.

Конверсия между шагами

Абсолютные числа бесполезны без конверсий. Добавляем расчёт:

WITH funnel AS (
    SELECT
        COUNT(DISTINCT user_id) AS visit,
        COUNT(DISTINCT CASE WHEN event_name = 'registration' THEN user_id END) AS registration,
        COUNT(DISTINCT CASE WHEN event_name = 'first_session' THEN user_id END) AS first_session,
        COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchase
    FROM events
    WHERE event_name IN ('visit', 'registration', 'first_session', 'purchase')
)
SELECT
    visit,
    registration,
    ROUND(100.0 * registration / NULLIF(visit, 0), 1) AS visit_to_reg_pct,
    first_session,
    ROUND(100.0 * first_session / NULLIF(registration, 0), 1) AS reg_to_session_pct,
    purchase,
    ROUND(100.0 * purchase / NULLIF(first_session, 0), 1) AS session_to_purchase_pct,
    ROUND(100.0 * purchase / NULLIF(visit, 0), 1) AS total_conversion_pct
FROM funnel

NULLIF(visit, 0) защищает от деления на ноль. total_conversion_pct — сквозная конверсия от визита до покупки.

Воронка с ограничением по времени

На практике воронку считают не за всё время, а в окне — например, 7 дней после регистрации. Без временного окна пользователь, зарегистрировавшийся год назад, завышает конверсию.

WITH reg AS (
    SELECT user_id, MIN(created_at) AS registered_at
    FROM events
    WHERE event_name = 'registration'
    GROUP BY user_id
),
bounded AS (
    SELECT
        r.user_id,
        r.registered_at,
        e.event_name,
        e.created_at
    FROM reg r
    JOIN events e ON e.user_id = r.user_id
        AND e.created_at BETWEEN r.registered_at AND r.registered_at + INTERVAL '7 days'
)
SELECT
    COUNT(DISTINCT user_id) AS registered,
    COUNT(DISTINCT CASE WHEN event_name = 'first_session' THEN user_id END) AS first_session,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchase,
    ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) /
        NULLIF(COUNT(DISTINCT user_id), 0), 1
    ) AS conversion_pct
FROM bounded

Такой подход честнее: конверсия отражает поведение пользователей в фиксированном окне.

Воронка по сегментам

Руководителю не нужна средняя конверсия — ему нужна конверсия по каналам, устройствам, когортам. Добавляем разбивку:

SELECT
    u.channel,
    COUNT(DISTINCT e.user_id) AS visit,
    COUNT(DISTINCT CASE WHEN e.event_name = 'registration' THEN e.user_id END) AS registration,
    COUNT(DISTINCT CASE WHEN e.event_name = 'purchase' THEN e.user_id END) AS purchase,
    ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN e.event_name = 'purchase' THEN e.user_id END) /
        NULLIF(COUNT(DISTINCT e.user_id), 0), 1
    ) AS conversion_pct
FROM events e
JOIN users u ON u.id = e.user_id
WHERE e.event_name IN ('visit', 'registration', 'purchase')
GROUP BY u.channel
ORDER BY conversion_pct DESC

Тот же принцип работает для device, utm_source, когорты регистрации — любой атрибут пользователя можно добавить в GROUP BY.

Два паттерна построения воронки

Условная агрегация

Всё в одном запросе через CASE WHEN внутри COUNT DISTINCT — как в примерах выше. Компактно, быстро, хорошо для 3-5 шагов.

Цепочка LEFT JOIN

Каждый шаг — отдельный подзапрос, шаги соединяются через LEFT JOIN. Используют, когда нужно учитывать порядок шагов и их временные метки:

WITH step1 AS (
    SELECT user_id, MIN(created_at) AS t1
    FROM events WHERE event_name = 'visit'
    GROUP BY user_id
),
step2 AS (
    SELECT user_id, MIN(created_at) AS t2
    FROM events WHERE event_name = 'registration'
    GROUP BY user_id
),
step3 AS (
    SELECT user_id, MIN(created_at) AS t3
    FROM events WHERE event_name = 'purchase'
    GROUP BY user_id
)
SELECT
    COUNT(s1.user_id) AS visits,
    COUNT(s2.user_id) AS registrations,
    COUNT(s3.user_id) AS purchases
FROM step1 s1
LEFT JOIN step2 s2 ON s2.user_id = s1.user_id AND s2.t2 >= s1.t1
LEFT JOIN step3 s3 ON s3.user_id = s2.user_id AND s3.t3 >= s2.t2

LEFT JOIN гарантирует, что пользователи, не дошедшие до следующего шага, остаются в выборке. Условие t2 >= t1 обеспечивает правильный порядок шагов.

Время до конверсии

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

SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY
        EXTRACT(EPOCH FROM (purchase_at - registered_at)) / 3600
    ) AS median_hours_to_purchase
FROM (
    SELECT
        r.user_id,
        MIN(r.created_at) AS registered_at,
        MIN(p.created_at) AS purchase_at
    FROM events r
    JOIN events p ON p.user_id = r.user_id AND p.event_name = 'purchase'
    WHERE r.event_name = 'registration'
    GROUP BY r.user_id
) t

Медиана информативнее среднего — она не искажается выбросами (пользователи, купившие через полгода).

Практический пример: воронка онбординга

Допустим, онбординг состоит из шагов: open_app → select_topics → start_quiz → complete_quiz. Задача — найти, где отваливаются пользователи. Подробнее про удержание после онбординга — в статье как считать retention.

SELECT
    COUNT(DISTINCT CASE WHEN event_name = 'open_app' THEN user_id END) AS opened,
    COUNT(DISTINCT CASE WHEN event_name = 'select_topics' THEN user_id END) AS selected,
    COUNT(DISTINCT CASE WHEN event_name = 'start_quiz' THEN user_id END) AS started,
    COUNT(DISTINCT CASE WHEN event_name = 'complete_quiz' THEN user_id END) AS completed,
    ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN event_name = 'complete_quiz' THEN user_id END) /
        NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'open_app' THEN user_id END), 0), 1
    ) AS total_pct
FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'

Если 80% открывают, 50% выбирают темы, 45% начинают квиз, но только 20% завершают — проблема на шаге complete_quiz. Можно копать глубже через когортный анализ.

Типичные ошибки

Не дедуплицируют пользователей. COUNT(user_id) вместо COUNT(DISTINCT user_id). Если пользователь делал событие несколько раз, воронка завышается. Всегда COUNT(DISTINCT ...).

Неправильное временное окно. Воронка за всё время смешивает когорты. Пользователь, зарегистрировавшийся 2 года назад, когда-нибудь купит — но это не заслуга текущей версии продукта. Ограничивайте окно.

Игнорируют порядок шагов. Условная агрегация не проверяет, что registration произошла после visit. Если порядок важен — используйте LEFT JOIN с условием на время.

Деление на ноль. Без NULLIF получите ошибку, если в знаменателе 0. Всегда оборачивайте: NULLIF(count, 0).

Считают конверсию только к первому шагу. Сквозная конверсия visit → purchase важна, но конверсия между соседними шагами показывает, где именно проблема.

Вопросы с собеседований

— Как построить воронку одним SQL-запросом? — Условная агрегация: COUNT(DISTINCT CASE WHEN event_name = 'step' THEN user_id END) для каждого шага. Конверсию считаем делением шага N на шаг N-1.

— Как учесть порядок шагов в воронке? — Цепочка LEFT JOIN: каждый шаг — отдельный CTE с MIN(created_at). JOIN-условие включает проверку, что время текущего шага >= времени предыдущего.

— Как посчитать воронку в окне 7 дней после регистрации? — Сначала находим дату регистрации (MIN(created_at) WHERE event_name = 'registration'), затем фильтруем события: created_at BETWEEN registered_at AND registered_at + INTERVAL '7 days'.

— Зачем COUNT(DISTINCT) вместо COUNT? — Пользователь может совершить одно действие несколько раз. Без DISTINCT воронка завысит числа и покажет некорректную конверсию.

— Как найти узкое место в воронке? — Посчитать конверсию между каждой парой соседних шагов. Шаг с наименьшей конверсией — узкое место. Дополнительно можно сегментировать по каналу или устройству.

Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.

FAQ

Чем воронка отличается от retention?

Воронка измеряет прохождение шагов за один цикл: visit → registration → purchase. Retention измеряет возвращаемость со временем: сколько пользователей пришли снова через день, неделю, месяц. Воронка — про первый путь, retention — про привычку.

Когда использовать LEFT JOIN, а когда условную агрегацию?

Условная агрегация проще и быстрее для воронок из 3-5 шагов, когда порядок не критичен. LEFT JOIN нужен, когда важно, чтобы шаг B произошёл строго после шага A, или когда нужны временные метки каждого шага.

Как посчитать воронку для новых пользователей за последнюю неделю?

Отфильтруйте по дате регистрации: добавьте JOIN на таблицу users с условием users.created_at >= CURRENT_DATE - INTERVAL '7 days'. Так воронка покажет поведение только свежей когорты.

Как тренироваться

Воронки — must-have навык для аналитика. Их спрашивают в задачах на SQL, на продуктовых кейсах и при обсуждении метрик. В тренажёре Карьерник есть задачи на условную агрегацию, JOIN-цепочки и расчёт конверсий — с разборами. Больше вопросов по всем темам — в разделе с примерами.

Тренироваться в Telegram