Воронка конверсий по платформам
// Условие
Постройте воронку конверсий по шагам: page_view → search → view_item → add_to_cart → begin_checkout → purchase. Для каждой платформы (platform) и каждого шага воронки посчитайте количество уникальных пользователей, дошедших до этого шага. Выведите platform, step_name, unique_users и conversion_from_start (доля от page_view в процентах, округлить до 1 знака). Отсортируйте по platform, затем по порядку шагов воронки.
// Таблицы
event_id | BIGINT |
user_id | INT |
event_name | VARCHAR(30) |
event_time | TIMESTAMP |
platform | VARCHAR(10) |
page_url | VARCHAR(100) |
// Что тренирует
// Решение
Подсказка
Для порядка шагов используйте CASE WHEN step_name = 'page_view' THEN 1 ... END. Для conversion_from_start нужен COUNT пользователей на page_view — получите через оконную функцию или подзапрос.
Сначала попробуй сам → Показать решение
WITH step_order AS (
SELECT
platform,
event_name AS step_name,
COUNT(DISTINCT user_id) AS unique_users,
CASE event_name
WHEN 'page_view' THEN 1
WHEN 'search' THEN 2
WHEN 'view_item' THEN 3
WHEN 'add_to_cart' THEN 4
WHEN 'begin_checkout' THEN 5
WHEN 'purchase' THEN 6
END AS step_num
FROM sandbox.events
GROUP BY platform, event_name
),
with_start AS (
SELECT
*,
FIRST_VALUE(unique_users) OVER (
PARTITION BY platform ORDER BY step_num
) AS start_users
FROM step_order
)
SELECT
platform,
step_name,
unique_users,
ROUND(100.0 * unique_users / start_users, 1) AS conversion_from_start
FROM with_start
ORDER BY platform, step_num;Воронка — одна из главных задач продуктового аналитика. Ключевые моменты:
(1) COUNT(DISTINCT user_id) — один пользователь может генерировать много событий одного типа.
(2) CASE для порядка шагов — без него шаги отсортируются по алфавиту.
(3) FIRST_VALUE() OVER (PARTITION BY platform ORDER BY step_num) — получает количество пользователей на первом шаге (page_view) для каждой платформы.
(4) Ожидаем: web-конверсия хуже, чем мобилка — это заложено в данных.
Связь со статьёй «Воронка продукта» на сайте.