SQL: Посчитать воронку конверсии

Решение

Понимание задачи

Воронка конверсии показывает, на каком шаге пользователи отсеиваются:

• Сколько пользователей просмотрели товар (view)
• Из них сколько добавили в корзину (add_to_cart)
• Из них сколько прошли к оплате (checkout)
• Из них сколько совершили покупку (purchase)

SQL-запрос

WITH step_users AS (
  -- Считаем уникальных пользователей на каждом шаге
  SELECT
    action_type,
    COUNT(DISTINCT user_id) as users_count
  FROM user_actions
  GROUP BY action_type
),
step_order AS (
  -- Определяем порядок шагов
  SELECT 1 as step_number, "view" as action_type
  UNION ALL SELECT 2, "add_to_cart"
  UNION ALL SELECT 3, "checkout"
  UNION ALL SELECT 4, "purchase"
),
funnel AS (
  -- Объединяем и считаем конверсии
  SELECT
    so.step_number,
    so.action_type as step,
    COALESCE(su.users_count, 0) as users_count,
    FIRST_VALUE(su.users_count) OVER (ORDER BY so.step_number) as first_step_users,
    LAG(COALESCE(su.users_count, 0)) OVER (ORDER BY so.step_number) as prev_step_users
  FROM step_order so
  LEFT JOIN step_users su ON so.action_type = su.action_type
)
SELECT
  step,
  users_count,
  CASE
    WHEN prev_step_users = 0 THEN NULL
    ELSE ROUND(100.0 * users_count / prev_step_users, 2)
  END as conversion_from_prev,
  CASE
    WHEN first_step_users = 0 THEN NULL
    ELSE ROUND(100.0 * users_count / first_step_users, 2)
  END as conversion_from_first
FROM funnel
ORDER BY step_number;

Альтернативный вариант (более простой)

Если последовательность шагов фиксирована, можно использовать простой подход:

WITH funnel_data AS (
  SELECT
    "view" as step,
    COUNT(DISTINCT CASE WHEN action_type IN ("view", "add_to_cart", "checkout", "purchase") THEN user_id END) as users_count
  FROM user_actions
  WHERE action_type = "view"
  
  UNION ALL
  
  SELECT
    "add_to_cart" as step,
    COUNT(DISTINCT CASE WHEN action_type IN ("add_to_cart", "checkout", "purchase") THEN user_id END) as users_count
  FROM user_actions
  WHERE action_type IN ("add_to_cart", "checkout", "purchase")
  
  UNION ALL
  
  SELECT
    "checkout" as step,
    COUNT(DISTINCT CASE WHEN action_type IN ("checkout", "purchase") THEN user_id END) as users_count
  FROM user_actions
  WHERE action_type IN ("checkout", "purchase")
  
  UNION ALL
  
  SELECT
    "purchase" as step,
    COUNT(DISTINCT user_id) as users_count
  FROM user_actions
  WHERE action_type = "purchase"
)
SELECT
  step,
  users_count,
  ROUND(100.0 * users_count / LAG(users_count) OVER (ORDER BY CASE step WHEN "view" THEN 1 WHEN "add_to_cart" THEN 2 WHEN "checkout" THEN 3 ELSE 4 END), 2) as conversion_from_prev,
  ROUND(100.0 * users_count / FIRST_VALUE(users_count) OVER (ORDER BY CASE step WHEN "view" THEN 1 WHEN "add_to_cart" THEN 2 WHEN "checkout" THEN 3 ELSE 4 END), 2) as conversion_from_first
FROM funnel_data
ORDER BY CASE step WHEN "view" THEN 1 WHEN "add_to_cart" THEN 2 WHEN "checkout" THEN 3 ELSE 4 END;

Оптимальное решение (с условием последовательности)

Если важна ПОСЛЕДОВАТЕЛЬНОСТЬ (пользователь не может пойти в checkout без add_to_cart):

WITH user_journey AS (
  -- Определяем максимальный шаг каждого пользователя
  SELECT
    user_id,
    CASE
      WHEN action_type = "purchase" THEN 4
      WHEN action_type = "checkout" THEN 3
      WHEN action_type = "add_to_cart" THEN 2
      ELSE 1
    END as max_step
  FROM (
    SELECT
      user_id,
      action_type,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_time DESC) as rn
    FROM user_actions
  )
  WHERE rn = 1
),
step_counts AS (
  SELECT
    1 as step_num,
    "view" as step,
    COUNT(DISTINCT CASE WHEN max_step >= 1 THEN user_id END) as users_count
  FROM user_journey
  
  UNION ALL
  
  SELECT
    2,
    "add_to_cart",
    COUNT(DISTINCT CASE WHEN max_step >= 2 THEN user_id END)
  FROM user_journey
  
  UNION ALL
  
  SELECT
    3,
    "checkout",
    COUNT(DISTINCT CASE WHEN max_step >= 3 THEN user_id END)
  FROM user_journey
  
  UNION ALL
  
  SELECT
    4,
    "purchase",
    COUNT(DISTINCT CASE WHEN max_step >= 4 THEN user_id END)
  FROM user_journey
)
SELECT
  step,
  users_count,
  ROUND(100.0 * users_count / LAG(users_count) OVER (ORDER BY step_num), 2) as conversion_from_prev,
  ROUND(100.0 * users_count / FIRST_VALUE(users_count) OVER (ORDER BY step_num), 2) as conversion_from_first
FROM step_counts
ORDER BY step_num;

Пример вывода

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

• FIRST_VALUE — значение с первого шага для расчёта конверсии от начала
• LAG — предыдущее значение для конверсии между шагами
• NULL для первого шага — логично, так как нет предыдущего шага
• DISTINCT user_id — каждого пользователя считаем один раз
• Выбор запроса зависит от данных: если нужна строгая последовательность — третий вариант