Расчет Retention Rate по когортам

После построения когорт следующий критически важный шаг — расчет Retention Rate (коэффициента удержания). Эта метрика показывает, какой процент пользователей из когорты возвращается к продукту через определенное время после регистрации. Retention Rate — один из ключевых индикаторов качества продукта и эффективности работы с пользователями.

Логика расчета Retention Rate

Retention Rate для когорты в период N рассчитывается по формуле:

Retention Rate (N) = (Количество активных пользователей в период N) / (Размер когорты) × 100%

Где: - Размер когорты — количество пользователей, зарегистрировавшихся в определенный период (день 0) - Активные пользователи в период N — количество пользователей из этой когорты, которые совершили хотя бы одно действие в период N

Период N обычно измеряется в днях, неделях или месяцах с момента регистрации.

Базовый запрос для расчета retention

WITH cohorts AS (
    -- Определяем когорту каждого пользователя
    SELECT 
        user_id,
        DATE_TRUNC('month', registration_date) AS cohort_month
    FROM users
),
cohort_size AS (
    -- Размер каждой когорты
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_id) AS total_users
    FROM cohorts
    GROUP BY cohort_month
),
user_activities AS (
    -- Активность пользователей с возрастом когорты
    SELECT 
        c.cohort_month,
        c.user_id,
        DATE_TRUNC('month', e.event_date) AS activity_month,
        EXTRACT(YEAR FROM AGE(e.event_date, c.cohort_month)) * 12 + 
        EXTRACT(MONTH FROM AGE(e.event_date, c.cohort_month)) AS month_number
    FROM cohorts c
    JOIN events e ON c.user_id = e.user_id
    WHERE e.event_date >= c.cohort_month
)
SELECT 
    ua.cohort_month,
    ua.month_number,
    COUNT(DISTINCT ua.user_id) AS active_users,
    cs.total_users AS cohort_size,
    ROUND(100.0 * COUNT(DISTINCT ua.user_id) / cs.total_users, 2) AS retention_rate
FROM user_activities ua
JOIN cohort_size cs ON ua.cohort_month = cs.cohort_month
GROUP BY ua.cohort_month, ua.month_number, cs.total_users
ORDER BY ua.cohort_month, ua.month_number;

Этот запрос создает таблицу, где для каждой когорты показан retention rate в каждом месяце жизни.

Построение когортной таблицы (Cohort Table)

Для визуализации retention удобно представить данные в виде таблицы, где строки — это когорты, а столбцы — периоды (месяцы 0, 1, 2, ...). Такая структура называется когортной таблицей. Для создания такой структуры в SQL используется условная агрегация:

WITH retention_data AS (
    -- предыдущий запрос расчета retention
    ...
)
SELECT 
    cohort_month,
    MAX(CASE WHEN month_number = 0 THEN retention_rate END) AS month_0,
    MAX(CASE WHEN month_number = 1 THEN retention_rate END) AS month_1,
    MAX(CASE WHEN month_number = 2 THEN retention_rate END) AS month_2,
    MAX(CASE WHEN month_number = 3 THEN retention_rate END) AS month_3,
    MAX(CASE WHEN month_number = 4 THEN retention_rate END) AS month_4,
    MAX(CASE WHEN month_number = 5 THEN retention_rate END) AS month_5
FROM retention_data
GROUP BY cohort_month
ORDER BY cohort_month;

Особенности расчета: Day 0 и Day 1

Важный нюанс: Day 0 (или Month 0) — это период регистрации. Retention в этот период всегда 100%, так как все пользователи когорты по определению были активны в момент регистрации. Более информативным является Day 1 Retention — процент пользователей, вернувшихся на следующий день:

WITH cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('day', registration_date) AS cohort_day
    FROM users
),
day1_activity AS (
    SELECT DISTINCT
        c.cohort_day,
        c.user_id
    FROM cohorts c
    JOIN events e ON c.user_id = e.user_id
    WHERE DATE_TRUNC('day', e.event_date) = c.cohort_day + INTERVAL '1 day'
)
SELECT 
    c.cohort_day,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT d1.user_id) AS day1_users,
    ROUND(100.0 * COUNT(DISTINCT d1.user_id) / COUNT(DISTINCT c.user_id), 2) AS day1_retention
FROM cohorts c
LEFT JOIN day1_activity d1 ON c.cohort_day = d1.cohort_day AND c.user_id = d1.user_id
GROUP BY c.cohort_day
ORDER BY c.cohort_day;

Rolling Retention vs Classic Retention

Существует два подхода к расчету retention:

Classic Retention — пользователь считается активным в период N, только если он был активен именно в этот период:

-- Активность строго в месяце 2
WHERE month_number = 2

Rolling Retention — пользователь считается активным в период N, если он был активен в период N или позже:

-- Активность в месяце 2 или позже
WHERE month_number >= 2

Rolling Retention всегда выше или равен Classic Retention и показывает, сколько пользователей "не ушли навсегда" к определенному моменту.

Ключевой вывод: Retention Rate — это процент пользователей когорты, остающихся активными через определенное время. Правильный расчет требует точного определения когорт, подсчета их размера и отслеживания активности в каждом периоде. Когортная таблица делает паттерны удержания наглядными и позволяет быстро сравнивать качество разных периодов привлечения пользователей.