Как посчитать Onboarding Completion в SQL

Зачем Onboarding Completion

Onboarding = первый продуктовый experience. Wider чем tutorial — включает создание профиля, верификацию, первые действия. Completion rate — health-check продукта.

Формула

Onboarding Completion = completed_users / signed_up_users × 100%

«Completed» = достиг defined end-state (e.g., first transaction, profile filled, first goal set).

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

WITH cohort AS (
    SELECT user_id, signed_up_at
    FROM users
    WHERE signed_up_at >= CURRENT_DATE - INTERVAL '60 days'
      AND signed_up_at < CURRENT_DATE - INTERVAL '7 days'  -- give them time
),
completed AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_name = 'onboarding_completed'
)
SELECT
    COUNT(c.user_id) AS signups,
    COUNT(comp.user_id) AS completed,
    COUNT(comp.user_id)::NUMERIC * 100 / NULLIF(COUNT(c.user_id), 0) AS completion_rate_pct
FROM cohort c
LEFT JOIN completed comp USING (user_id);

Multi-step funnel

WITH funnel AS (
    SELECT
        u.user_id,
        u.signed_up_at,
        BOOL_OR(e.event_name = 'profile_filled') AS profile_filled,
        BOOL_OR(e.event_name = 'verified_email') AS verified,
        BOOL_OR(e.event_name = 'first_action') AS first_action,
        BOOL_OR(e.event_name = 'onboarding_completed') AS completed
    FROM users u
    LEFT JOIN events e ON e.user_id = u.user_id
      AND e.event_time <= u.signed_up_at + INTERVAL '7 days'
    WHERE u.signed_up_at >= CURRENT_DATE - INTERVAL '60 days'
      AND u.signed_up_at < CURRENT_DATE - INTERVAL '7 days'
    GROUP BY u.user_id, u.signed_up_at
)
SELECT
    COUNT(*) AS signups,
    SUM(CASE WHEN profile_filled THEN 1 ELSE 0 END) AS step1_profile,
    SUM(CASE WHEN verified THEN 1 ELSE 0 END) AS step2_verified,
    SUM(CASE WHEN first_action THEN 1 ELSE 0 END) AS step3_action,
    SUM(CASE WHEN completed THEN 1 ELSE 0 END) AS step4_completed,
    -- Conversion rates
    SUM(CASE WHEN profile_filled THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS step1_pct,
    SUM(CASE WHEN verified THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS step2_pct,
    SUM(CASE WHEN first_action THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS step3_pct,
    SUM(CASE WHEN completed THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS step4_pct
FROM funnel;

Time-to-completion

WITH timings AS (
    SELECT
        u.user_id,
        u.signed_up_at,
        MIN(e.event_time) FILTER (WHERE e.event_name = 'onboarding_completed') AS completed_at,
        EXTRACT(EPOCH FROM (
            MIN(e.event_time) FILTER (WHERE e.event_name = 'onboarding_completed')
            - u.signed_up_at
        )) / 60 AS minutes_to_complete
    FROM users u
    LEFT JOIN events e USING (user_id)
    WHERE u.signed_up_at >= CURRENT_DATE - INTERVAL '60 days'
    GROUP BY u.user_id, u.signed_up_at
)
SELECT
    COUNT(*) FILTER (WHERE completed_at IS NOT NULL) AS completed,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY minutes_to_complete) AS median_min,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY minutes_to_complete) AS p90_min,
    AVG(minutes_to_complete) AS avg_min
FROM timings
WHERE completed_at IS NOT NULL;

Completion → Retention

WITH groups AS (
    SELECT
        u.user_id,
        EXISTS (SELECT 1 FROM events WHERE user_id = u.user_id AND event_name = 'onboarding_completed') AS completed_onboarding,
        EXISTS (
            SELECT 1 FROM activity
            WHERE user_id = u.user_id
              AND activity_date BETWEEN u.signed_up_at + INTERVAL '7 days' AND u.signed_up_at + INTERVAL '14 days'
        ) AS active_week2
    FROM users u
    WHERE u.signed_up_at >= CURRENT_DATE - INTERVAL '90 days'
      AND u.signed_up_at < CURRENT_DATE - INTERVAL '14 days'
)
SELECT
    completed_onboarding,
    COUNT(*) AS users,
    SUM(CASE WHEN active_week2 THEN 1 ELSE 0 END) AS retained_w2,
    SUM(CASE WHEN active_week2 THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS retention_pct
FROM groups
GROUP BY completed_onboarding;

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

Ошибка 1. Definition of «completed». What counts? Last step? All steps? Define carefully.

Ошибка 2. Optional vs required steps. Optional steps lower completion на paper, но reflective UX.

Ошибка 3. Cohort too recent. Last week's signups still onboarding. Exclude too-fresh cohort.

Ошибка 4. Skipped vs not seen. Did user actively skip or never reach? Different signals.

Ошибка 5. Funnel vs aggregate. «50% completion» single number masks по-step drop-offs.

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

• Как посчитать tutorial completion в SQL
• Как посчитать activation rate в SQL
• Как посчитать funnel в SQL
• Activation framework для продукта

FAQ

Какой Onboarding Completion ok?

SaaS B2C: 30-50%. B2B: 50-70%. Mobile apps: 40-60%.

Onboarding vs Tutorial?

Tutorial — narrow steps. Onboarding — broader (covers tutorial + first value).

Multi-step or single-step?

Multi-step показывает drop-offs. Single-step (boolean done) — simpler.

Time-to-complete?

Quick = good (low friction). Slow = friction в product.

Optional steps?

Track separately. Sometimes optional becomes load-bearing.