Как посчитать Funnel Drop-off в SQL

Зачем Funnel Drop-off

Общая конверсия воронки показывает итоговый результат. Drop-off показывает, где именно теряются пользователи. Если 50% отваливаются на шаге «подтвердить email», это конкретная проблема UX, а не размытая общая цифра.

Формула

Drop-off Rate (stage N) = (users_at_stage_N - users_at_stage_N+1) / users_at_stage_N × 100%

Базовый расчёт

Данные: events(user_id, event_type, event_date).

WITH stages AS (
    SELECT
        COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END) AS signup,
        COUNT(DISTINCT CASE WHEN event_type = 'verify_email' THEN user_id END) AS verify,
        COUNT(DISTINCT CASE WHEN event_type = 'profile_complete' THEN user_id END) AS profile,
        COUNT(DISTINCT CASE WHEN event_type = 'first_purchase' THEN user_id END) AS purchase
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    signup, verify, profile, purchase,
    (signup - verify)::NUMERIC * 100 / NULLIF(signup, 0) AS drop_signup_to_verify_pct,
    (verify - profile)::NUMERIC * 100 / NULLIF(verify, 0) AS drop_verify_to_profile_pct,
    (profile - purchase)::NUMERIC * 100 / NULLIF(profile, 0) AS drop_profile_to_purchase_pct
FROM stages;

Самый большой drop — узкое место воронки.

По сегментам

WITH stages_by_channel AS (
    SELECT
        u.acquisition_channel,
        COUNT(DISTINCT CASE WHEN e.event_type = 'signup' THEN e.user_id END) AS signup,
        COUNT(DISTINCT CASE WHEN e.event_type = 'verify_email' THEN e.user_id END) AS verify,
        COUNT(DISTINCT CASE WHEN e.event_type = 'first_purchase' THEN e.user_id END) AS purchase
    FROM events e
    JOIN users u ON u.user_id = e.user_id
    WHERE e.event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY u.acquisition_channel
)
SELECT
    acquisition_channel,
    (signup - verify)::NUMERIC * 100 / NULLIF(signup, 0) AS drop_to_verify_pct,
    (verify - purchase)::NUMERIC * 100 / NULLIF(verify, 0) AS drop_verify_to_purchase_pct
FROM stages_by_channel;

Drop-off с ограничением по времени

«А что если пользователь прошёл верификацию, но покупку сделал только через 30 дней — это drop?»

WITH user_stages AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_type = 'signup' THEN event_date END) AS signup_date,
        MIN(CASE WHEN event_type = 'verify_email' THEN event_date END) AS verify_date,
        MIN(CASE WHEN event_type = 'first_purchase' THEN event_date END) AS purchase_date
    FROM events
    GROUP BY user_id
)
SELECT
    COUNT(*) AS users,
    COUNT(*) FILTER (WHERE verify_date IS NOT NULL) AS verified,
    COUNT(*) FILTER (WHERE purchase_date IS NOT NULL
                       AND purchase_date <= verify_date + INTERVAL '7 days') AS purchased_7d,
    COUNT(*) FILTER (WHERE purchase_date IS NOT NULL
                       AND purchase_date <= verify_date + INTERVAL '7 days')::NUMERIC * 100
        / NULLIF(COUNT(*) FILTER (WHERE verify_date IS NOT NULL), 0) AS verify_to_purchase_7d_pct
FROM user_stages
WHERE signup_date >= CURRENT_DATE - INTERVAL '90 days';

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

Ошибка 1. Несогласованное окно. Пользователь зарегистрировался сегодня — он ещё не дошёл до конца воронки. Нужен когортный подход.

Ошибка 2. Пользователь пропустил шаг. Пользователь сделал покупку без подтверждения email — это баг или допустимый путь? Зависит от продукта.

Ошибка 3. Игнорировать порядок событий. Пользователь совершил события в неправильном порядке. Нужно решить: фильтровать по порядку или игнорировать его.

Ошибка 4. Размер выборки в сегменте. Маленький канал даёт шумные цифры. Отсекайте через HAVING > 50.

Ошибка 5. Путать drop-off и конверсию шага. 1 − drop-off = конверсия шага. Это обратные величины.

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

• Как посчитать funnel в SQL
• Как посчитать конверсию в SQL
• Как посчитать cart abandonment в SQL
• Как посчитать bounce rate в SQL

FAQ

Какой drop-off считается высоким?

Зависит от шага воронки. В онбординге drop-off 30–50% между регистрацией и активацией — это норма. На шаге оформления заказа терпимый диапазон уже — примерно 10–30%.

Drop-off растёт — что делать?

Сначала спуститесь на уровень конкретного шага и найдите причину: баг в интерфейсе, медленная загрузка, лишнее трение. Затем проверяйте гипотезу фикса через A/B-тест, а не выкатывайте изменение вслепую.

Как считать drop-off между сессиями?

Пользователь ушёл в первой сессии, но вернулся во второй и продолжил путь — считать ли это оттоком? Зависит от определения. Обычно такой возврат drop-off не считают, но это нужно явно зафиксировать в метрике.

Как измерять drop-off в B2B?

В B2B воронка растянута на месяцы, поэтому drop-off измеряют в длинном цикле. Каждый шаг здесь может занимать недели, и окно наблюдения нужно расширять соответственно.

Drop-off на мобильных и десктопе — в чём разница?

На мобильных drop-off обычно выше. В верхней части воронки мобайл и десктоп могут идти вровень, но ближе к оплате мобильная конверсия часто проваливается. Поэтому drop-off полезно разрезать по типу устройства.