Воронка конверсии в SQL: расчёт пошагово без боли

Воронка — главный инструмент продуктовой аналитики. «Из 1000 юзеров видевших страницу, 200 нажали кнопку, 50 заполнили форму, 20 оплатили». Где отвал больше всего — там фокус оптимизации.

Этот гайд — как посчитать воронку в чистом SQL. С нюансами: сохранение порядка шагов, кохортные воронки, сравнение А/Б-групп, защита от багов.

Простейший случай: считаем юзеров на каждом шаге

Допустим, у тебя таблица событий:

CREATE TABLE events (
    user_id INT,
    event_name TEXT,    -- 'visit', 'signup', 'add_to_cart', 'purchase'
    event_ts  TIMESTAMP
);

Самый прямой запрос:

SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'visit')        AS visits,
    COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'signup')       AS signups,
    COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'add_to_cart')  AS carts,
    COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'purchase')     AS purchases
FROM events
WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days';

Получишь:

Конверсии:

• visit → signup: 35%
• signup → cart: 34.3%
• cart → purchase: 26.7%
• visit → purchase (overall): 3.2%

Строгая воронка: каждый шаг ПОСЛЕ предыдущего

Если хотим «юзер сделал signup ПОСЛЕ visit, потом purchase ПОСЛЕ signup» — нужен self-join или оконные функции.

Подход с MIN(timestamp) для каждого события:

WITH user_steps AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'visit'    THEN event_ts END) AS visit_ts,
        MIN(CASE WHEN event_name = 'signup'   THEN event_ts END) AS signup_ts,
        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_ts END) AS cart_ts,
        MIN(CASE WHEN event_name = 'purchase' THEN event_ts END) AS purchase_ts
    FROM events
    WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    COUNT(*) FILTER (WHERE visit_ts IS NOT NULL)                                          AS step1_visit,
    COUNT(*) FILTER (WHERE visit_ts IS NOT NULL AND signup_ts > visit_ts)                 AS step2_signup,
    COUNT(*) FILTER (WHERE signup_ts > visit_ts AND cart_ts > signup_ts)                  AS step3_cart,
    COUNT(*) FILTER (WHERE cart_ts > signup_ts AND purchase_ts > cart_ts)                 AS step4_purchase
FROM user_steps;

Теперь каждый последующий шаг считается ТОЛЬКО если он произошёл после предыдущего.

Воронка по дням / неделям / когортам

Чаще всего хочешь видеть динамику. Группируем по дате первого visit:

WITH user_steps AS (
    SELECT
        user_id,
        DATE(MIN(CASE WHEN event_name = 'visit' THEN event_ts END)) AS cohort_day,
        MIN(CASE WHEN event_name = 'visit' THEN event_ts END) AS visit_ts,
        MIN(CASE WHEN event_name = 'signup' THEN event_ts END) AS signup_ts,
        MIN(CASE WHEN event_name = 'purchase' THEN event_ts END) AS purchase_ts
    FROM events
    WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    cohort_day,
    COUNT(*) AS visits,
    COUNT(*) FILTER (WHERE signup_ts > visit_ts)                          AS signups,
    COUNT(*) FILTER (WHERE signup_ts > visit_ts AND purchase_ts > signup_ts) AS purchases,
    ROUND(100.0 * COUNT(*) FILTER (WHERE signup_ts > visit_ts) / COUNT(*), 2) AS visit_to_signup_pct,
    ROUND(100.0 * COUNT(*) FILTER (WHERE purchase_ts > signup_ts) / NULLIF(COUNT(*) FILTER (WHERE signup_ts > visit_ts), 0), 2) AS signup_to_purchase_pct
FROM user_steps
GROUP BY cohort_day
ORDER BY cohort_day DESC;

Сравнение воронок A/B-групп

WITH user_steps AS (...) -- как выше, плюс JOIN с тестовой группой
SELECT
    test_group,
    COUNT(*) AS visits,
    COUNT(*) FILTER (WHERE signup_ts > visit_ts) AS signups,
    ROUND(100.0 * COUNT(*) FILTER (WHERE signup_ts > visit_ts) / COUNT(*), 2) AS conv_pct
FROM user_steps us
JOIN ab_assignments a ON a.user_id = us.user_id
GROUP BY test_group;

Получишь:

Дальше chi2 для статистической значимости (см. A/B-тесты в Python).

Время на этап (drop-off analysis)

Важно знать не только «сколько юзеров отвалилось», но и «через сколько времени». Долгий этап — место для оптимизации.

WITH user_steps AS (...)
SELECT
    AVG(EXTRACT(EPOCH FROM (signup_ts - visit_ts))) AS avg_visit_to_signup_sec,
    AVG(EXTRACT(EPOCH FROM (purchase_ts - signup_ts))) AS avg_signup_to_purchase_sec,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (signup_ts - visit_ts))) AS median_visit_to_signup
FROM user_steps
WHERE signup_ts IS NOT NULL;

Если median visit→signup = 2 минуты, average = 30 минут → у тебя выбросы (юзеры которые думают долго).

ClickHouse: нативные функции воронки

В CH есть windowFunnel() — посчитает строгую воронку за один проход:

SELECT
    sum(funnel_level = 1) AS visit,
    sum(funnel_level = 2) AS signup,
    sum(funnel_level = 3) AS cart,
    sum(funnel_level = 4) AS purchase
FROM (
    SELECT
        user_id,
        windowFunnel(7 * 86400)(event_ts,
            event_name = 'visit',
            event_name = 'signup',
            event_name = 'add_to_cart',
            event_name = 'purchase'
        ) AS funnel_level
    FROM events
    GROUP BY user_id
);

Параметр 7 * 86400 — окно в секундах (7 дней). Юзер должен пройти все шаги в пределах окна. Очень быстро на больших данных.

Подводные камни

Дубли событий

Юзер может сделать несколько signup (если разлогинился и заново регнулся через другой email). MIN(timestamp) для каждого события решает.

Юзеры без visit (входящие через прямую ссылку)

В строгой воронке они потеряются. Если важно — отдельный сегмент «no_visit_first».

Mobile vs Web — две разные воронки

Часто разные продукты. Считай отдельно по платформам.

Влияние новых юзеров

Если за период резко набралась когорта новых — voronka может казаться лучше/хуже из-за их специфики. Всегда смотри по когортам.

Не забывай unit

Воронка по уникальным юзерам vs по сессиям vs по визитам — разные числа. На дашборде явно подписывай unit.

Связанные материалы

• SQL-тренажёр: задачи на воронки — реальные собесные задачи
• RFM-сегментация в SQL — другой ключевой подход
• Когортный анализ retention — близкая тема
• Оконные функции SQL — продвинутые воронки
• Метрики продукта — conversion rate, drop-off

Открой SQL-тренажёр, найди задачу с тегом «funnel» или «conversion» — реализуй на реальной схеме данных. Это лучше чем читать любой гайд.