Как посчитать funnel step conversion в SQL

Зачем step conversion

Funnel step conversion — пошаговая воронка с CR на каждом переходе. Раскрывает слабое звено: если landing→signup 50%, signup→trial 80%, trial→paid 8% — проблема в trial→paid (paywall, цена, ценность не донесена).

Структура воронки

Минимум 3–5 этапов: например, для SaaS landing → signup → trial → paid → renewed. Каждый этап = событие в логе.

event_name     | user_id | event_timestamp
landing        | 42      | 2026-05-01 10:00
signup         | 42      | 2026-05-01 10:15
trial_started  | 42      | 2026-05-01 10:17
paid           | 42      | 2026-05-15 09:00

Абсолютная конверсия

CR от верха воронки до каждого шага:

WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_name = 'landing'       THEN 1 ELSE 0 END) AS landing,
        MAX(CASE WHEN event_name = 'signup'        THEN 1 ELSE 0 END) AS signup,
        MAX(CASE WHEN event_name = 'trial_started' THEN 1 ELSE 0 END) AS trial,
        MAX(CASE WHEN event_name = 'paid'          THEN 1 ELSE 0 END) AS paid
    FROM events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    SUM(landing) AS landing_users,
    SUM(signup)  AS signup_users,
    SUM(trial)   AS trial_users,
    SUM(paid)    AS paid_users,
    SUM(signup) * 100.0 / NULLIF(SUM(landing), 0) AS landing_to_signup_pct,
    SUM(trial)  * 100.0 / NULLIF(SUM(landing), 0) AS landing_to_trial_pct,
    SUM(paid)   * 100.0 / NULLIF(SUM(landing), 0) AS landing_to_paid_pct
FROM funnel;

Это и есть «overall conversion» — от total visits до paid.

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

CR между соседними шагами — показывает где именно проваливается:

WITH funnel_counts AS (
    SELECT
        SUM(CASE WHEN event_name = 'landing'       THEN 1 ELSE 0 END) AS landing_n,
        SUM(CASE WHEN event_name = 'signup'        THEN 1 ELSE 0 END) AS signup_n,
        SUM(CASE WHEN event_name = 'trial_started' THEN 1 ELSE 0 END) AS trial_n,
        SUM(CASE WHEN event_name = 'paid'          THEN 1 ELSE 0 END) AS paid_n
    FROM (
        SELECT DISTINCT user_id, event_name FROM events
        WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
    ) u
)
SELECT
    signup_n * 100.0 / NULLIF(landing_n, 0) AS step_landing_signup,
    trial_n  * 100.0 / NULLIF(signup_n, 0)  AS step_signup_trial,
    paid_n   * 100.0 / NULLIF(trial_n, 0)   AS step_trial_paid
FROM funnel_counts;

Шаг с самым низким % — bottleneck.

Сегментация по когортам

CR на каждом шаге по каналу подписания:

WITH user_funnel AS (
    SELECT
        u.user_id,
        u.utm_source AS channel,
        MAX(CASE WHEN e.event_name = 'signup'        THEN 1 ELSE 0 END) AS signup,
        MAX(CASE WHEN e.event_name = 'trial_started' THEN 1 ELSE 0 END) AS trial,
        MAX(CASE WHEN e.event_name = 'paid'          THEN 1 ELSE 0 END) AS paid
    FROM users u
    LEFT JOIN events e ON e.user_id = u.user_id
    WHERE u.created_at >= CURRENT_DATE - INTERVAL '60 days'
    GROUP BY u.user_id, u.utm_source
)
SELECT
    channel,
    COUNT(*) AS cohort_size,
    SUM(signup) * 100.0 / COUNT(*) AS signup_pct,
    SUM(trial)  * 100.0 / NULLIF(SUM(signup), 0) AS trial_after_signup_pct,
    SUM(paid)   * 100.0 / NULLIF(SUM(trial), 0)  AS paid_after_trial_pct
FROM user_funnel
GROUP BY channel
HAVING COUNT(*) >= 100
ORDER BY paid_after_trial_pct DESC;

HAVING COUNT(*) >= 100 — иначе CR с шумом.

Частые ошибки

Ошибка 1. Не учитывать порядок. Юзер «paid» без «trial» — это бесплатные подарки или баг трекинга. WHERE шага i должен включать факт прохождения шагов 1..i−1.

Ошибка 2. Считать без DISTINCT user_id. Если один юзер сделал signup дважды (баг), он удваивается в counter. SELECT DISTINCT user_id, event_name.

Ошибка 3. Считать на all-time без window. В воронку нужно брать конкретную когорту (signup_month) — иначе сравниваете users разных возрастов.

Ошибка 4. Игнорировать time-to-step. Юзер из мая мог не успеть paid → CR кажется ниже. Учитывайте, что для paid нужно min 14 дней trial.

Ошибка 5. Все шаги в одной строке вместо LEFT JOIN. В большой воронке (8+ шагов) пишите как CTE + LEFT JOIN шагов, не одну mega-таблицу с CASE WHEN.

Связанные темы

• Как посчитать funnel в SQL
• Как посчитать funnel drop-off в SQL
• Как посчитать conversion-window в SQL
• Как посчитать customer journey в SQL

FAQ

Strict order или just exposure?

Strict (юзер прошёл шаги 1→2→3 в порядке) — для UX-воронки. Loose (хоть когда был на шаге) — для маркетинг-funnel.

Window между шагами?

Зависит от продукта. SaaS: signup→trial мгновенно, trial→paid 14–30 дней.

Что если шаги ветвятся?

A → B1 или B2 → C — две воронки. Анализируйте каждую отдельно.

Сколько шагов оптимально?

3–7 максимум. Длинные воронки путают.

Funnel step vs cohort retention?

Funnel — переход между шагами для одной cohort. Retention — повторное использование во времени.