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

Зачем Funnel Drop-off

Funnel CR показывает overall conversion. Drop-off показывает где конкретно теряются юзеры. Если 50% дропают на «verify 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 — bottleneck.

По сегментам

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;

Time-bounded drop-off

«Что если юзер прошёл verify, но purchase сделал через 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. Window mismatch. Юзер signed up сегодня — он не ещё в funnel. Cohort approach.

Ошибка 2. Юзер пропустил stage. Юзер сделал purchase БЕЗ email verify — это bug или legitimate path? Зависит от продукта.

Ошибка 3. Не учитывать ordering. Юзер сделал event в неправильном порядке. Filter по ordering или ignore.

Ошибка 4. Sample size в bucket. Малый channel дрифтит. HAVING > 50.

Ошибка 5. Drop-off vs conversion rate. 1 - drop-off = stage conversion rate. Inverse.

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

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

FAQ

Какой drop-off считается high?

Зависит. В onboarding 30-50% drop между signup и activation — норма. В check-out — 10-30%.

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

Drill-down: cause of drop. UX bug? Performance? Friction? A/B-test fix.

Cross-session drop-off?

Юзер дропнул в session 1, вернулся в session 2 и continued. Это is drop? Depends on definition.

Drop-off в B2B?

Возможен over months. Long-cycle: каждая stage — недели.

Mobile vs desktop drop-off?

Mobile обычно выше. Top-of-funnel может быть равным, bottom-of-funnel — disaster.