Как посчитать воронку конверсии в SQL

Что такое воронка

Funnel — последовательность шагов, через которые проходит пользователь. На каждом шаге часть пользователей отваливается.

Примеры:

• E-commerce: landing → product page → cart → checkout → paid
• SaaS: signup → email confirm → first login → activation → paid
• Mobile: install → open → onboarding → main screen → purchase

Схема данных

Предположим таблицу событий:

events (user_id, event_name, event_at)

1. Простая воронка (без учёта порядка)

Сколько уникальных пользователей на каждом шаге:

SELECT
    COUNT(DISTINCT CASE WHEN event_name = 'landing_view'   THEN user_id END) AS step1,
    COUNT(DISTINCT CASE WHEN event_name = 'product_view'   THEN user_id END) AS step2,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart'    THEN user_id END) AS step3,
    COUNT(DISTINCT CASE WHEN event_name = 'checkout_start' THEN user_id END) AS step4,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase'       THEN user_id END) AS step5
FROM events
WHERE event_at >= '2026-04-01'
  AND event_at <  '2026-05-01';

Проблема: пользователь мог совершить purchase в прошлом месяце и add_to_cart в этом. Не учитывает последовательность.

2. Строгая воронка (с учётом порядка)

Каждый шаг — пользователи, которые прошли все предыдущие:

WITH step1 AS (
    SELECT DISTINCT user_id, MIN(event_at) AS step1_at
    FROM events WHERE event_name = 'landing_view' GROUP BY user_id
),
step2 AS (
    SELECT s1.user_id, MIN(e.event_at) AS step2_at
    FROM step1 s1
    JOIN events e ON e.user_id = s1.user_id
        AND e.event_name = 'product_view'
        AND e.event_at > s1.step1_at
    GROUP BY s1.user_id
),
step3 AS (
    SELECT s2.user_id, MIN(e.event_at) AS step3_at
    FROM step2 s2
    JOIN events e ON e.user_id = s2.user_id
        AND e.event_name = 'add_to_cart'
        AND e.event_at > s2.step2_at
    GROUP BY s2.user_id
),
step4 AS (
    SELECT s3.user_id, MIN(e.event_at) AS step4_at
    FROM step3 s3
    JOIN events e ON e.user_id = s3.user_id
        AND e.event_name = 'purchase'
        AND e.event_at > s3.step3_at
    GROUP BY s3.user_id
)
SELECT
    (SELECT COUNT(*) FROM step1) AS step1,
    (SELECT COUNT(*) FROM step2) AS step2,
    (SELECT COUNT(*) FROM step3) AS step3,
    (SELECT COUNT(*) FROM step4) AS step4;

3. Воронка с окном времени

Только пользователи, прошедшие все шаги в течение 24 часов:

WITH first_events AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'landing_view' THEN event_at END) AS t1,
        MIN(CASE WHEN event_name = 'product_view' THEN event_at END) AS t2,
        MIN(CASE WHEN event_name = 'add_to_cart'  THEN event_at END) AS t3,
        MIN(CASE WHEN event_name = 'purchase'     THEN event_at END) AS t4
    FROM events
    GROUP BY user_id
)
SELECT
    COUNT(CASE WHEN t1 IS NOT NULL THEN 1 END) AS step1,
    COUNT(CASE WHEN t2 > t1 AND t2 < t1 + INTERVAL '24 hours' THEN 1 END) AS step2,
    COUNT(CASE WHEN t3 > t2 AND t3 < t1 + INTERVAL '24 hours' THEN 1 END) AS step3,
    COUNT(CASE WHEN t4 > t3 AND t4 < t1 + INTERVAL '24 hours' THEN 1 END) AS step4
FROM first_events;

4. Конверсии шагов

WITH funnel AS (
    SELECT
        COUNT(DISTINCT CASE WHEN event_name = 'landing_view' THEN user_id END) AS step1,
        COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS step2,
        COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart'  THEN user_id END) AS step3,
        COUNT(DISTINCT CASE WHEN event_name = 'purchase'     THEN user_id END) AS step4
    FROM events
)
SELECT
    step1,
    step2,
    step3,
    step4,
    100.0 * step2 / NULLIF(step1, 0) AS cr_1_2,
    100.0 * step3 / NULLIF(step2, 0) AS cr_2_3,
    100.0 * step4 / NULLIF(step3, 0) AS cr_3_4,
    100.0 * step4 / NULLIF(step1, 0) AS cr_total
FROM funnel;

5. Воронка по когортам

Смотрим, как конверсия меняется в зависимости от месяца регистрации:

WITH cohorts AS (
    SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
    FROM events WHERE event_name = 'signup'
    GROUP BY user_id
),
funnel_by_cohort AS (
    SELECT
        c.cohort_month,
        COUNT(DISTINCT c.user_id) AS signups,
        COUNT(DISTINCT CASE WHEN e.event_name = 'activation' THEN c.user_id END) AS activated,
        COUNT(DISTINCT CASE WHEN e.event_name = 'purchase' THEN c.user_id END) AS paid
    FROM cohorts c
    LEFT JOIN events e ON e.user_id = c.user_id
    GROUP BY c.cohort_month
)
SELECT
    cohort_month,
    signups,
    activated,
    paid,
    100.0 * activated / signups AS cr_activation,
    100.0 * paid / signups AS cr_paid
FROM funnel_by_cohort
ORDER BY cohort_month;

6. Воронка с декомпозицией по сегменту

По каналу привлечения:

SELECT
    u.attribution_channel,
    COUNT(DISTINCT u.user_id) AS signups,
    COUNT(DISTINCT CASE WHEN e.event_name = 'activation' THEN u.user_id END) AS activated,
    COUNT(DISTINCT CASE WHEN e.event_name = 'purchase'   THEN u.user_id END) AS paid,
    100.0 * COUNT(DISTINCT CASE WHEN e.event_name = 'activation' THEN u.user_id END)
        / COUNT(DISTINCT u.user_id) AS cr_activation
FROM users u
LEFT JOIN events e ON e.user_id = u.user_id
GROUP BY u.attribution_channel
ORDER BY cr_activation DESC;

7. Time-to-convert

Сколько времени пользователю нужно, чтобы дойти до каждого шага:

WITH user_funnel AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'signup'     THEN event_at END) AS signup_at,
        MIN(CASE WHEN event_name = 'activation' THEN event_at END) AS activation_at,
        MIN(CASE WHEN event_name = 'purchase'   THEN event_at END) AS purchase_at
    FROM events
    GROUP BY user_id
)
SELECT
    AVG(EXTRACT(EPOCH FROM (activation_at - signup_at)) / 3600) AS avg_hrs_to_activate,
    AVG(EXTRACT(EPOCH FROM (purchase_at   - signup_at)) / 3600) AS avg_hrs_to_purchase,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY
        EXTRACT(EPOCH FROM (activation_at - signup_at)) / 3600) AS median_hrs_to_activate
FROM user_funnel
WHERE activation_at IS NOT NULL
  AND signup_at IS NOT NULL;

8. Воронка в сессии (все шаги в одной сессии)

WITH sessions AS (
    SELECT
        user_id,
        session_id,
        event_name,
        event_at
    FROM events
    WHERE event_at >= '2026-04-01'
),
session_funnel AS (
    SELECT
        session_id,
        MAX(CASE WHEN event_name = 'landing_view' THEN 1 ELSE 0 END) AS has_step1,
        MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) AS has_step2,
        MAX(CASE WHEN event_name = 'add_to_cart'  THEN 1 ELSE 0 END) AS has_step3,
        MAX(CASE WHEN event_name = 'purchase'     THEN 1 ELSE 0 END) AS has_step4
    FROM sessions
    GROUP BY session_id
)
SELECT
    SUM(has_step1) AS step1,
    SUM(has_step1 * has_step2) AS step2,
    SUM(has_step1 * has_step2 * has_step3) AS step3,
    SUM(has_step1 * has_step2 * has_step3 * has_step4) AS step4
FROM session_funnel;

9. Drop-off analysis

Пользователи, застрявшие на каждом шаге:

WITH user_progress AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_name = 'step1' THEN 1 ELSE 0 END) AS s1,
        MAX(CASE WHEN event_name = 'step2' THEN 1 ELSE 0 END) AS s2,
        MAX(CASE WHEN event_name = 'step3' THEN 1 ELSE 0 END) AS s3,
        MAX(CASE WHEN event_name = 'step4' THEN 1 ELSE 0 END) AS s4
    FROM events
    GROUP BY user_id
)
SELECT
    SUM(CASE WHEN s1=1 AND s2=0 THEN 1 ELSE 0 END) AS dropped_at_step1,
    SUM(CASE WHEN s2=1 AND s3=0 THEN 1 ELSE 0 END) AS dropped_at_step2,
    SUM(CASE WHEN s3=1 AND s4=0 THEN 1 ELSE 0 END) AS dropped_at_step3,
    SUM(CASE WHEN s4=1 THEN 1 ELSE 0 END) AS completed
FROM user_progress;

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

Ошибка 1. Игнорировать порядок

-- считает покупки до корзины
COUNT(DISTINCT CASE WHEN event_name = 'purchase' ...)

Используйте строгую воронку через subsequent JOIN.

Ошибка 2. Не учитывать time window

Пользователь зарегистрировался в январе, купил в апреле. Считать ли это за «conversion»? Зависит от задачи — но обязательно думать про окно.

Ошибка 3. COUNT(*) вместо COUNT(DISTINCT)

-- пользователь, сделавший 5 покупок, учтётся 5 раз
COUNT(CASE WHEN event_name = 'purchase' THEN 1 END)

-- правильно — unique users
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END)

Ошибка 4. Смешивать total и unique

На step1 — 1000 unique users. На step2 — 500 events (не users). Всегда consistent подход.

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

• Воронка конверсии
• Как считать конверсию в SQL
• Как считать retention
• SQL для когортного retention

FAQ

Сколько шагов в типичной воронке?

E-commerce: 4-6 шагов. SaaS: 5-8. Слишком много шагов — запутанный анализ.

Как выбрать time window?

Зависит от продукта. E-commerce: сессия / 24 часа. SaaS: 7 дней. B2B: 30 дней.

Строгая или мягкая воронка?

Для оптимизации UX — строгая (с порядком). Для общего monitoring — мягкая проще.

Что делать с drop-off между шагами?

Найти причину: session recording, surveys, A/B-тест альтернатив.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.