Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 16
Часть 3. Событийная аналитика. (7 задач)
Глава 14. COUNT (DISTINCT), CASE, CTE. Воронка событий
ОглавлениеОписание задачи
Представьте, действия пользователя в интернет-магазине. Пользователь сначала заходит на сайт, смотрит товары, добавляет в корзину, покупает. Но на каждом шаге кто-то отсеивается, кто-то смотрит и не покупает, кто-то не доходит даже до просмотра товаров.
Воронка (funnel) – это последовательность шагов, которая показывает, сколько пользователей доходит до каждого этапа.
Почему воронка? Пришли много пользователей, посмотрели товары уже меньше, купило еще меньше, визуально можно представить, что это воронка.
Такая воронка – это один из самых важных отчётов в аналитике событий.
Мы построим воронку: enter → show → add_to_cart → buy.
Таблицы: users_log (логи действий пользователей).
Аналогия из жизни
Представьте торговый центр. На вход зашли 1000 человек. До витрины с телефонами дошли 600. В магазин зашли 300. Купили 100. Это и есть воронка. Наша задача – посчитать такие же цифры по логам.
Основные SQL-конструкции
COUNT (DISTINCT …) – считает уникальные значения. В воронке это важно, потому что один пользователь может совершить действие несколько раз.
CASE – условный оператор, работает как if-then-else (если правда, то одно, иначе другое). Позволяет вернуть одно значение, если условие истинно, и другое – если ложно.
text
CASE
WHEN условие THEN значение1
ELSE значение2
END
В нашей задаче: если действие равно ’enter’, то 1, иначе 0.
CTE (WITH) – временная таблица (см. главу 6). Разбивает сложный запрос на части.
Почему мы не можем просто использовать GROUP BY?
GROUP BY сгруппирует всех пользователей, но не позволит легко посчитать, сколько пользователей выполнили несколько действий подряд. CTE с CASE даёт гибкость.
SQL-запросы с объяснением
Шаг 1. Что нам нужно посчитать?
Нам нужно для каждого пользователя определить по таблице логов:
– Был ли у него enter? Вошёл ли он на сайт?
– Был ли у него show? Посмотрел ли он товары?
– Был ли у него add_to_cart? Добавил ли товары в корзину?
– Был ли у него buy? Совершил покупку?
Если был – ставим 1, если нет – 0.
Аналогия: Как контрольный список. Был пользователь на этапе? Да/нет.
Шаг 2. Подготовим данные: уникальные действия пользователей.
Сначала найдём все уникальные действия каждого пользователя.
text
WITH user_actions AS (
SELECT DISTINCT
user_id,
action
FROM users_log
)
SELECT * FROM user_actions ORDER BY user_id, action;
Как это работает
– DISTINCT убирает дубли (если пользователь сделал enter 5 раз, оставляем один).
– Теперь у нас есть список: пользователь X сделал действия A, B, C.
Пример вывода (реальные данные, фрагмент):
text
user_id action
1 add_to_cart
1 buy
1 enter
1 exit
1 register
1 show
2 add_to_cart
2 buy
Шаг 3. Добавим флаги для каждого действия с помощью CASE.
Теперь для каждого пользователя посчитаем флаги: 1 – если действие было, 0 – если нет. Маркируем действия пользователя 0 или 1 для удобного дальнейшего использования.
text
WITH user_actions AS (
SELECT DISTINCT
user_id,
action
FROM users_log
),
user_flags AS (
SELECT
user_id,
MAX (CASE WHEN action = ’enter’ THEN 1 ELSE 0 END) AS has_enter,
MAX (CASE WHEN action = ’show’ THEN 1 ELSE 0 END) AS has_show,
MAX (CASE WHEN action = ’add_to_cart’ THEN 1 ELSE 0 END) AS has_add_to_cart,
MAX (CASE WHEN action = ’buy’ THEN 1 ELSE 0 END) AS has_buy
FROM user_actions
GROUP BY user_id
)
SELECT * FROM user_flags ORDER BY user_id;
Как это работает
– CASE WHEN action = ’enter’ THEN 1 ELSE 0 END – если действие равно enter, возвращаем 1, иначе 0.
– MAX (…) – если у пользователя была хотя бы одна строка с 1, MAX вернёт 1. Если не было – вернёт 0.
– GROUP BY user_id – сворачиваем все строки пользователя в одну.
Почему MAX, а не SUM?
SUM сложил бы 1+1+1 = 3, если пользователь сделал действие 3 раза. Нам важно только «было или нет», поэтому MAX (максимальное значение – 1).
Пример вывода (реальные данные, фрагмент):
text
user_id has_enter has_show has_add_to_cart has_buy
1 1 1 1 1
2 1 1 1 1
3 1 0 0 1
4 1 0 0 1
Шаг 4. Считаем воронку.
Теперь у нас есть флаги. Посчитаем, сколько пользователей дошло до каждого этапа.
text
WITH user_actions AS (
SELECT DISTINCT
user_id,
action
FROM users_log
),
user_flags AS (
SELECT
user_id,
MAX (CASE WHEN action = ’enter’ THEN 1 ELSE 0 END) AS has_enter,
MAX (CASE WHEN action = ’show’ THEN 1 ELSE 0 END) AS has_show,
MAX (CASE WHEN action = ’add_to_cart’ THEN 1 ELSE 0 END) AS has_add_to_cart,
MAX (CASE WHEN action = ’buy’ THEN 1 ELSE 0 END) AS has_buy
FROM user_actions
GROUP BY user_id
)
SELECT
COUNT (*) AS total_users,
SUM (has_enter) AS enter_count,
SUM (has_show) AS show_count,
SUM (has_add_to_cart) AS add_to_cart_count,
SUM (has_buy) AS buy_count
FROM user_flags;
Как это работает
– COUNT (*) – все пользователи, у которых есть хотя бы одно действие (те, кто вообще залогинился).
– SUM (has_enter) – суммируем единицы. Сколько пользователей с флагом has_enter = 1.
– И так далее по каждому этапу.
Шаг 5. Считаем конверсию в процентах.
Конверсия – это процент пользователей, которые перешли с одного этапа воронки на следующий.
text
Конверсия = (количество на следующем этапе / количество на текущем этапе) * 100
Для нашей воронки:
– Из enter в show: (show_count / enter_count) * 100
– Из show в add_to_cart: (add_to_cart_count / show_count) * 100
– Из add_to_cart в buy: (buy_count / add_to_cart_count) * 100
Почему умножаем на 100? Потому что хотим получить проценты, а не доли. 0.75 → 75%.
Добавим проценты: сколько процентов пользователей дошли до каждого этапа от предыдущего.
text
WITH user_actions AS (
SELECT DISTINCT
user_id,
action
FROM users_log
),
user_flags AS (
SELECT
user_id,
MAX (CASE WHEN action = ’enter’ THEN 1 ELSE 0 END) AS has_enter,
MAX (CASE WHEN action = ’show’ THEN 1 ELSE 0 END) AS has_show,
MAX (CASE WHEN action = ’add_to_cart’ THEN 1 ELSE 0 END) AS has_add_to_cart,
MAX (CASE WHEN action = ’buy’ THEN 1 ELSE 0 END) AS has_buy
FROM user_actions
GROUP BY user_id
),
funnel_counts AS (
SELECT
COUNT (*) AS total_users,
SUM (has_enter) AS enter_count,
SUM (has_show) AS show_count,
SUM (has_add_to_cart) AS add_to_cart_count,
SUM (has_buy) AS buy_count
FROM user_flags
)
SELECT
total_users,
enter_count,
ROUND (100.0 * enter_count / total_users, 2) AS enter_to_total,
show_count,
ROUND (100.0 * show_count / enter_count, 2) AS show_to_enter,
add_to_cart_count,
ROUND (100.0 * add_to_cart_count / show_count, 2) AS add_to_cart_to_show,
buy_count,
ROUND (100.0 * buy_count / add_to_cart_count, 2) AS buy_to_add_to_cart
FROM funnel_counts;
Как это работает
– Конверсия из enter в show: show_count / enter_count * 100.
– Конверсия из show в add_to_cart: add_to_cart_count / show_count * 100.
– И так далее.
– ROUND округляет число до указанного количества знаков после запятой. ROUND (75.666666, 2) → 75.67, делаем число визуально более понятным.
Почему для первого шага мы делим на total_users, а для остальных – на предыдущий этап?
Первый шаг (enter) показывает, какая доля зарегистрированных пользователей вообще зашла на сайт. Дальше мы смотрим, сколько из тех, кто зашёл, дошли до следующего этапа. Это стандартный подход к воронкам.
Вопросы и ответы
В: Почему мы используем DISTINCT в user_actions?
О: Потому что один пользователь может совершить действие много раз. Нам важно только «было или нет». DISTINCT убирает дубли.
В: Можно ли обойтись без CTE?
О: Можно, но запрос станет громоздким и трудным для понимания. CTE позволяет разбить логику на шаги.
В: Что делать, если у пользователя нет действия enter?
О: Он не попадёт в подсчёт enter_count. Это правильно – если он не заходил, считать его в воронке бессмысленно.
В: Как добавить в воронку другие действия?
О: Добавьте новый CASE в user_flags и новую колонку в funnel_counts.
В: Почему конверсия из enter в show считается как show_count / enter_count, а не show_count / total_users?
О: Потому что нас интересует, сколько из тех, кто зашёл, посмотрели товары. Те, кто не заходил, не могли посмотреть.
Что в итоге
Мы научились строить воронку событий:
– Подготавливать уникальные действия пользователей с DISTINCT.
– Создавать флаги для каждого действия с CASE и MAX.
– Считать количество пользователей на каждом этапе.
– Вычислять конверсию в процентах.
Воронка – один из главных инструментов аналитика для поиска узких мест в продукте.
* Задание со звёздочкой
Постройте воронку для действий: enter → add_to_cart → buy (без show). Измените запрос, убрав show.
Подсказка: удалите из user_flags строки с show, а в funnel_counts уберите соответствующие колонки.
