SQL: Расчет конверсии по шагам регистрации

Решение

Задача на расчет фанел-метрик (funnel analytics) — одна из самых важных в Product Analytics. Воронка показывает, на каком этапе процесса пользователи выпадают.

Логика решения:

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

SQL запрос:

WITH step_sequence AS (
  -- Определяем порядок шагов
  SELECT 'start' as step_name, 1 as step_order
  UNION ALL SELECT 'email_entered', 2
  UNION ALL SELECT 'password_set', 3
  UNION ALL SELECT 'profile_filled', 4
  UNION ALL SELECT 'completed', 5
),
step_users AS (
  -- Определяем уникальных пользователей, достигших каждого шага
  SELECT 
    step_name,
    COUNT(DISTINCT user_id) as users_reached
  FROM registration_steps
  GROUP BY step_name
),
result AS (
  SELECT 
    ss.step_name,
    ss.step_order,
    su.users_reached,
    -- Конверсия от первого шага (берём количество на шаге start)
    ROUND(
      100.0 * su.users_reached / 
      (SELECT users_reached FROM step_users WHERE step_name = 'start'),
      2
    ) as conversion_from_start,
    -- Конверсия от предыдущего шага
    ROUND(
      100.0 * su.users_reached / 
      LAG(su.users_reached) OVER (ORDER BY ss.step_order),
      2
    ) as conversion_from_prev
  FROM step_sequence ss
  LEFT JOIN step_users su ON ss.step_name = su.step_name
)
SELECT *
FROM result
ORDER BY step_order;

Альтернативный подход (более эффективный для больших данных):

WITH step_sequence AS (
  SELECT 'start' as step_name, 1 as step_order
  UNION ALL SELECT 'email_entered', 2
  UNION ALL SELECT 'password_set', 3
  UNION ALL SELECT 'profile_filled', 4
  UNION ALL SELECT 'completed', 5
),
step_users AS (
  SELECT 
    step_name,
    COUNT(DISTINCT user_id) as users_reached
  FROM registration_steps
  WHERE step_name IN ('start', 'email_entered', 'password_set', 'profile_filled', 'completed')
  GROUP BY step_name
),
total_start AS (
  SELECT users_reached as start_count FROM step_users WHERE step_name = 'start'
)
SELECT 
  ss.step_name,
  ss.step_order,
  COALESCE(su.users_reached, 0) as users_reached,
  ROUND(100.0 * COALESCE(su.users_reached, 0) / ts.start_count, 2) as conversion_from_start,
  ROUND(
    100.0 * COALESCE(su.users_reached, 0) / 
    LAG(COALESCE(su.users_reached, 0)) OVER (ORDER BY ss.step_order),
    2
  ) as conversion_from_prev
FROM step_sequence ss
LEFT JOIN step_users su ON ss.step_name = su.step_name
CROSS JOIN total_start ts
ORDER BY ss.step_order;

Ключевые моменты:

step_sequence — явно определяем порядок шагов, чтобы воронка была в правильной последовательности.

COUNT(DISTINCT user_id) — считаем только уникальных пользователей (даже если пользователь посетил шаг несколько раз).

LAG() OVER (ORDER BY step_order) — оконная функция для получения значения из предыдущей строки (для расчёта конверсии от предыдущего шага).

COALESCE() — обрабатываем случаи, когда данные отсутствуют (никто не достиг этого шага).

Результат наглядно покажет, где происходит отсев пользователей и какие этапы критичны для улучшения.