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

Зачем Tutorial Completion

Tutorial = первые шаги в продукте (onboarding). Completion rate показывает % новых users, прошедших весь tutorial. Drop-off — leak points. Высокий completion → activated users → high LTV.

Формула

Tutorial Completion = users_finished_step_N / users_started_tutorial × 100%

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

WITH starts AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_name = 'tutorial_started'
      AND event_time >= CURRENT_DATE - INTERVAL '30 days'
),
completions AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_name = 'tutorial_completed'
      AND event_time >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    COUNT(s.user_id) AS started,
    COUNT(c.user_id) AS completed,
    COUNT(c.user_id)::NUMERIC * 100 / NULLIF(COUNT(s.user_id), 0) AS completion_rate_pct
FROM starts s
LEFT JOIN completions c USING (user_id);

Drop-off по шагам

WITH steps AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_name = 'tutorial_step_1' THEN 1 ELSE 0 END) AS step1,
        MAX(CASE WHEN event_name = 'tutorial_step_2' THEN 1 ELSE 0 END) AS step2,
        MAX(CASE WHEN event_name = 'tutorial_step_3' THEN 1 ELSE 0 END) AS step3,
        MAX(CASE WHEN event_name = 'tutorial_step_4' THEN 1 ELSE 0 END) AS step4,
        MAX(CASE WHEN event_name = 'tutorial_step_5' THEN 1 ELSE 0 END) AS step5
    FROM events
    WHERE event_name LIKE 'tutorial_%'
      AND event_time >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    SUM(step1) AS reached_step1,
    SUM(step2) AS reached_step2,
    SUM(step3) AS reached_step3,
    SUM(step4) AS reached_step4,
    SUM(step5) AS reached_step5,
    -- Conversion step N → N+1
    SUM(step2)::NUMERIC * 100 / NULLIF(SUM(step1), 0) AS step1_to_2_pct,
    SUM(step3)::NUMERIC * 100 / NULLIF(SUM(step2), 0) AS step2_to_3_pct,
    SUM(step4)::NUMERIC * 100 / NULLIF(SUM(step3), 0) AS step3_to_4_pct,
    SUM(step5)::NUMERIC * 100 / NULLIF(SUM(step4), 0) AS step4_to_5_pct
FROM steps;

Step с biggest drop-off — fix first.

Time to complete

WITH tutorial_times AS (
    SELECT
        user_id,
        MIN(event_time) FILTER (WHERE event_name = 'tutorial_started') AS start_time,
        MIN(event_time) FILTER (WHERE event_name = 'tutorial_completed') AS complete_time
    FROM events
    WHERE event_name IN ('tutorial_started', 'tutorial_completed')
      AND event_time >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    AVG(EXTRACT(EPOCH FROM (complete_time - start_time))) AS avg_seconds,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (complete_time - start_time))) AS median_seconds,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (complete_time - start_time))) AS p90_seconds
FROM tutorial_times
WHERE complete_time IS NOT NULL;

Completion → Activation correlation

WITH tutorial_status AS (
    SELECT
        user_id,
        EXISTS (
            SELECT 1 FROM events
            WHERE user_id = u.user_id AND event_name = 'tutorial_completed'
        ) AS completed_tutorial,
        EXISTS (
            SELECT 1 FROM events
            WHERE user_id = u.user_id AND event_name = 'activation_event'
            AND event_time <= u.created_at + INTERVAL '7 days'
        ) AS activated_7d
    FROM users u
    WHERE u.created_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    completed_tutorial,
    COUNT(*) AS users,
    SUM(CASE WHEN activated_7d THEN 1 ELSE 0 END) AS activated,
    SUM(CASE WHEN activated_7d THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS activation_pct
FROM tutorial_status
GROUP BY completed_tutorial;

Tutorial completers обычно activate 2-3x чаще.

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

Ошибка 1. «Tutorial skipped» counts? User clicked «Skip» — completed или not? Define.

Ошибка 2. Step events dropped. Tracking bug → missing events → fake low completion.

Ошибка 3. Returning users. User reset tutorial after weeks. Count once or twice?

Ошибка 4. Cohort drift. Tutorial changed → old cohort not comparable.

Ошибка 5. Causation confused. High-engaged users complete tutorial. Doesn't mean tutorial caused engagement.

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

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

FAQ

Какой Tutorial Completion ok?

Mobile games: 60-80%. SaaS: 40-60%. Complex products: 20-40%.

Optional vs Mandatory tutorial?

Mandatory inflates completion (forced). Optional reflects real engagement.

Tutorial too long?

Time-to-complete optimization?

Faster tutorial — usually better. Unless cost = skipping key concepts.

Tutorial completion vs activation?

Tutorial = mechanical. Activation = «aha moment». Tutorial doesn't guarantee activation.