Как рассчитать Retention Rate на 3-й и 7-й день?

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

Формула: Retention Rate (Day N) = (Кол-во пользователей когорты, активных на день N / Общее кол-во пользователей в когорте) * 100%

Практический пример расчета на SQL (PostgreSQL): Допустим, у нас есть таблица user_events с событиями сессий.

WITH user_first_visit AS (
    SELECT
        user_id,
        DATE(MIN(event_timestamp)) AS cohort_date -- Дата когорты (первый визит)
    FROM user_events
    GROUP BY user_id
),
daily_activity AS (
    SELECT
        user_id,
        DATE(event_timestamp) AS activity_date
    FROM user_events
    GROUP BY user_id, DATE(event_timestamp)
)
SELECT
    ufv.cohort_date,
    COUNT(DISTINCT ufv.user_id) AS cohort_size,
    -- Retention на 3-й день (активность на cohort_date + 3 дня)
    COUNT(DISTINCT CASE 
            WHEN da.activity_date = ufv.cohort_date + INTERVAL '3 days' 
            THEN da.user_id 
        END) AS retained_day_3,
    ROUND(
        COUNT(DISTINCT CASE WHEN da.activity_date = ufv.cohort_date + INTERVAL '3 days' THEN da.user_id END) * 100.0 /
        NULLIF(COUNT(DISTINCT ufv.user_id), 0),
        2
    ) AS retention_rate_day_3,
    -- Retention на 7-й день
    COUNT(DISTINCT CASE 
            WHEN da.activity_date = ufv.cohort_date + INTERVAL '7 days' 
            THEN da.user_id 
        END) AS retained_day_7,
    ROUND(
        COUNT(DISTINCT CASE WHEN da.activity_date = ufv.cohort_date + INTERVAL '7 days' THEN da.user_id END) * 100.0 /
        NULLIF(COUNT(DISTINCT ufv.user_id), 0),
        2
    ) AS retention_rate_day_7
FROM user_first_visit ufv
LEFT JOIN daily_activity da ON ufv.user_id = da.user_id
GROUP BY ufv.cohort_date
ORDER BY ufv.cohort_date;

Ключевые нюансы в моей практике:

• Определение "активности": Четко договоритесь, что считается возвращением — открытие приложения, начало сессии или meaningful event (например, отправка сообщения). Мы в проекте считали старт любой сессии.
• Окно активности: Часто используют не строго день в день, а окно (например, день 7 — это активность с 7 по 8 день). В формуле выше используется строгое равенство.
• Когорта: Обычно это новые пользователи по дням регистрации/первого запуска. Для расчета rolling retention можно брать когорты по неделям или месяцам.
• Инструменты: Для визуализации и регулярного расчета мы использовали связку Airflow (для расчета метрик) → PostgreSQL (хранение агрегатов) → Superset (дашборды).