Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 15
Часть 2. Продуктовая аналитика
Глава 13. Аналитические функции. Retention 7 дней
ОглавлениеОписание задачи
Retention – это способность продукта возвращать пользователей. Он показывает, какая доля пользователей вернулась на следующий день, через неделю, через месяц.
Мы посчитаем retention за 7 дней: сколько пользователей, зарегистрировавшихся в определённый день, совершили хотя бы одно действие (например, покупку или вход) в каждый из следующих дней в течение 7 дней после регистрации.
Таблицы: users, users_log.
Основные SQL-конструкции
FIRST_VALUE () – оконная функция, которая возвращает значение из первой строки в рамках окна.
text
FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number)
В нашей задаче мы используем FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number) – берём количество пользователей в день 0 (день регистрации) для каждой когорты.
Оконные функции – выполняют вычисления по набору строк, связанных с текущей строкой (см. главу 12). Ключевое слово OVER.
DATE_TRUNC () – обрезает дату до указанной точности (см. главу 11).
Совет
Retention считают не только по дням, но и по неделям, месяцам. Формула одна и та же, меняется только период группировки.
SQL-запросы с объяснением
Задача 1. Подготовка данных: уникальные действия пользователей по дням.
Сначала найдём все дни, когда пользователь был активен.
text
SELECT DISTINCT
user_id,
DATE (dt_tm) AS activity_date
FROM users_log
ORDER BY user_id, activity_date;
Как это работает
– DATE (dt_tm) – отбрасываем время, оставляем только дату.
– DISTINCT – убираем дубли (несколько действий в один день).
Пример вывода (фрагмент):
text
user_id activity_date
1 2024-01-15
1 2024-02-01
2 2024-02-10
2 2024-02-15
3 2024-01-20
Задача 2. Добавляем дату регистрации.
Для расчёта retention нужно знать, когда пользователь зарегистрировался.
text
SELECT
u.user_id,
u.registered_at::date AS reg_date,
DATE (l. dt_tm) AS activity_date
FROM users u
JOIN users_log l ON u.user_id = l.user_id
GROUP BY u.user_id, reg_date, activity_date
ORDER BY u.user_id, activity_date;
Как это работает
– Присоединяем логи к пользователям.
– GROUP BY убирает дубли (то же, что DISTINCT, но явно).
– registered_at::date – преобразуем в дату.
Задача 3. Считаем дни после регистрации.
Для каждого действия посчитаем, на какой день после регистрации оно произошло.
text
WITH user_activity AS (
SELECT
u.user_id,
u.registered_at::date AS reg_date,
DATE (l. dt_tm) AS activity_date
FROM users u
JOIN users_log l ON u.user_id = l.user_id
GROUP BY u.user_id, reg_date, activity_date
)
SELECT
user_id,
reg_date,
activity_date,
(activity_date – reg_date) AS day_number
FROM user_activity
ORDER BY user_id, day_number;
Как это работает
– activity_date – reg_date – разница в днях (0 – день регистрации, 1 – следующий день и т.д.).
– CTE user_activity – подготовленные данные.
Задача 4. Считаем retention 7 дней.
Для каждого дня регистрации посчитаем, сколько пользователей вернулось в каждый из следующих дней.
text
WITH user_activity AS (
SELECT
u.user_id,
u.registered_at::date AS reg_date,
DATE (l. dt_tm) AS activity_date
FROM users u
JOIN users_log l ON u.user_id = l.user_id
GROUP BY u.user_id, reg_date, activity_date
),
day_numbers AS (
SELECT
reg_date,
(activity_date – reg_date) AS day_number,
COUNT (DISTINCT user_id) AS users
FROM user_activity
WHERE activity_date> = reg_date
AND activity_date <= reg_date +7
GROUP BY reg_date, day_number
)
SELECT
reg_date,
day_number,
users,
FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number) AS day0_users,
ROUND (100.0 * users / FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number), 2) AS retention_rate
FROM day_numbers
ORDER BY reg_date, day_number;
Как это работает
– user_activity – подготовленные данные (день регистрации и дни активностей).
– day_numbers – считаем количество уникальных пользователей для каждой пары (дата регистрации, день после регистрации).
– FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number) – берём количество пользователей в день 0 (день регистрации).
– retention_rate – процент от дня 0.
Пример вывода (фрагмент):
text
reg_date day_number users day0_users retention_rate
2024-01-15 0 1 1 100.00
2024-01-18 0 1 1 100.00
2024-01-18 2 1 1 100.00
2024-01-20 0 1 1 100.00
2024-01-20 5 1 1 100.00
Типичная ошибка
Учитывать пользователей, у которых нет активности в конкретный день, как вернувшихся. Retention считается только по активным пользователям.
Вопросы и ответы
В: Почему retention считается в процентах от дня 0?
О: Чтобы сравнивать когорты с разным количеством пользователей. День 0 всегда 100%.
В: Что делать, если пользователь совершил несколько действий в один день?
О: Используем DISTINCT или GROUP BY – считаем только один раз.
В: Как считать retention не по покупкам, а по любым действиям?
О: Используйте users_log вместо orders. В нашей схеме так и сделано.
В: Какой retention считается хорошим?
О: Зависит от продукта. Для мобильных приложений хороший Day 1 retention – 40—60%, Day 7 – 20—30%.
Что в итоге
Мы научились:
– Подготавливать данные для расчёта когорт.
– Считать количество активных пользователей по дням после регистрации.
– Вычислять retention в процентах с помощью оконных функций.
Retention – ключевая метрика для оценки удержания пользователей.
* Задание со звёздочкой
Посчитайте retention за 7 дней, но используйте только покупки (users_log с action = ’buy’). Измените фильтр в user_activity.
Подсказка: добавьте AND l.action = ’buy’ в условие JOIN или WHERE.
