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

Расчёт Retention Rate на 3 и 7 день

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

Понимание Retention Rate

Определение:

• Cohort: Группа пользователей, которые установили приложение в один день
• Retention Day 1 (D1): % пользователей из cohortа, которые вернулись на день 1
• Retention Day 3 (D3): % пользователей, которые были активны на день 3 после установки
• Retention Day 7 (D7): % пользователей, активные на день 7 после установки

Формула:

Retention Day N = (Количество уникальных пользователей активных на день N) / (Общее количество пользователей в cohortе) × 100%

Шаг 1: Подготовка данных

Нужные события:

• Событие установки/первого запуска (install_date, first_open_date)
• События активности (app_open, click, purchase и т.д.)

SQL для получения данных:

-- Таблица с первым открытием приложения по пользователям
CREATE TABLE IF NOT EXISTS user_first_open AS
SELECT 
    user_id,
    MIN(event_date) as install_date,
    EXTRACT(DATE FROM MIN(event_timestamp)) as install_day
FROM events
WHERE event_name = first_open
GROUP BY user_id;

-- Таблица со всеми днями активности пользователей
CREATE TABLE IF NOT EXISTS user_daily_activity AS
SELECT DISTINCT
    user_id,
    EXTRACT(DATE FROM event_timestamp) as activity_date
FROM events
WHERE event_name IN (app_open, click, purchase, view)
GROUP BY user_id, activity_date;

Шаг 2: Расчёт Day 3 Retention

Логика:

1. Найти всех пользователей, которые были активны в день 0 (install_date)
2. Для каждого пользователя проверить, был ли он активен в день 3 (install_date + 3 дня)
3. Посчитать процент активных пользователей на день 3

SQL запрос:

WITH install_cohorts AS (
    -- Пользователи по дате установки
    SELECT 
        user_id,
        install_date
    FROM user_first_open
    WHERE install_date BETWEEN 2024-01-01 AND 2024-03-26
),
day3_retention AS (
    -- Проверяем активность на день 3
    SELECT 
        install_cohorts.user_id,
        install_cohorts.install_date,
        CASE 
            WHEN user_daily_activity.user_id IS NOT NULL THEN 1 
            ELSE 0 
        END as is_retained_day3
    FROM install_cohorts
    LEFT JOIN user_daily_activity 
        ON install_cohorts.user_id = user_daily_activity.user_id
        AND install_cohorts.install_date + INTERVAL 3 days = user_daily_activity.activity_date
)
SELECT 
    install_date,
    COUNT(*) as total_users,
    SUM(is_retained_day3) as retained_users_day3,
    ROUND(SUM(is_retained_day3)::NUMERIC / COUNT(*) * 100, 2) as retention_rate_day3
FROM day3_retention
GROUP BY install_date
ORDER BY install_date DESC;

Шаг 3: Расчёт Day 7 Retention

Та же логика, но проверяем активность на день 7:

WITH install_cohorts AS (
    SELECT 
        user_id,
        install_date
    FROM user_first_open
    WHERE install_date BETWEEN 2024-01-01 AND 2024-03-26
),
day7_retention AS (
    -- Проверяем активность на день 7
    SELECT 
        install_cohorts.user_id,
        install_cohorts.install_date,
        CASE 
            WHEN user_daily_activity.user_id IS NOT NULL THEN 1 
            ELSE 0 
        END as is_retained_day7
    FROM install_cohorts
    LEFT JOIN user_daily_activity 
        ON install_cohorts.user_id = user_daily_activity.user_id
        AND install_cohorts.install_date + INTERVAL 7 days = user_daily_activity.activity_date
)
SELECT 
    install_date,
    COUNT(*) as total_users,
    SUM(is_retained_day7) as retained_users_day7,
    ROUND(SUM(is_retained_day7)::NUMERIC / COUNT(*) * 100, 2) as retention_rate_day7
FROM day7_retention
GROUP BY install_date
ORDER BY install_date DESC;

Шаг 4: Объединённый отчёт (D3 и D7 вместе)

WITH install_cohorts AS (
    SELECT 
        user_id,
        install_date
    FROM user_first_open
    WHERE install_date BETWEEN 2024-01-01 AND 2024-03-26
),
day3_users AS (
    SELECT DISTINCT user_id, install_date
    FROM install_cohorts ic
    INNER JOIN user_daily_activity ua
        ON ic.user_id = ua.user_id
        AND ic.install_date + INTERVAL 3 days = ua.activity_date
),
day7_users AS (
    SELECT DISTINCT user_id, install_date
    FROM install_cohorts ic
    INNER JOIN user_daily_activity ua
        ON ic.user_id = ua.user_id
        AND ic.install_date + INTERVAL 7 days = ua.activity_date
)
SELECT 
    ic.install_date,
    COUNT(DISTINCT ic.user_id) as total_users,
    COUNT(DISTINCT d3.user_id) as retained_day3,
    COUNT(DISTINCT d7.user_id) as retained_day7,
    ROUND(COUNT(DISTINCT d3.user_id)::NUMERIC / COUNT(DISTINCT ic.user_id) * 100, 2) as retention_day3_percent,
    ROUND(COUNT(DISTINCT d7.user_id)::NUMERIC / COUNT(DISTINCT ic.user_id) * 100, 2) as retention_day7_percent
FROM install_cohorts ic
LEFT JOIN day3_users d3 ON ic.user_id = d3.user_id AND ic.install_date = d3.install_date
LEFT JOIN day7_users d7 ON ic.user_id = d7.user_id AND ic.install_date = d7.install_date
GROUP BY ic.install_date
ORDER BY ic.install_date DESC;

Шаг 5: Реализация на Python (PySpark)

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, count, sum, round, date_add, lit
from datetime import datetime, timedelta

spark = SparkSession.builder.appName("retention_analysis").getOrCreate()

# Чтение событий
events_df = spark.read.parquet("s3://bucket/events/")

# Шаг 1: Получить первое открытие каждого пользователя
first_opens = events_df.filter(
    col("event_name") == "first_open"
).groupBy("user_id").agg(
    F.min("event_date").alias("install_date")
).cache()

# Шаг 2: Получить все дни активности
user_activity = events_df.filter(
    col("event_name").isin(["app_open", "click", "purchase", "view"])
).select("user_id", "event_date").distinct()

# Шаг 3: Расчёт Day 3 Retention
day3_retention = first_opens.join(
    user_activity,
    on=[
        (first_opens.user_id == user_activity.user_id),
        (date_add(first_opens.install_date, 3) == user_activity.event_date)
    ],
    how="left"
).select(
    first_opens.user_id,
    first_opens.install_date,
    (col("event_date").isNotNull()).alias("retained_day3")
)

# Шаг 4: Расчёт Day 7 Retention
day7_retention = first_opens.join(
    user_activity,
    on=[
        (first_opens.user_id == user_activity.user_id),
        (date_add(first_opens.install_date, 7) == user_activity.event_date)
    ],
    how="left"
).select(
    first_opens.user_id,
    first_opens.install_date,
    (col("event_date").isNotNull()).alias("retained_day7")
)

# Шаг 5: Объединить и посчитать процент
result = first_opens.join(
    day3_retention.drop("install_date"),
    on="user_id",
    how="left"
).join(
    day7_retention.drop("install_date"),
    on="user_id",
    how="left"
).groupBy("install_date").agg(
    count("user_id").alias("total_users"),
    sum("retained_day3").alias("retained_day3_count"),
    sum("retained_day7").alias("retained_day7_count"),
    round(sum("retained_day3") / count("user_id") * 100, 2).alias("retention_day3_percent"),
    round(sum("retained_day7") / count("user_id") * 100, 2).alias("retention_day7_percent")
).orderBy(col("install_date").desc())

result.show()

# Сохранить результат
result.write.format("parquet").mode("overwrite").save("s3://bucket/retention_metrics/")

Важные замечания

1. Временные зоны:

• Убедись, что все даты в одной временной зоне (UTC)
• День определяется как midnight-to-midnight в нужной зоне (часто Moscow Time)

# ✅ Правильно: конвертируем в нужную зону
from pyspark.sql.functions import from_utc_timestamp

events_df = events_df.withColumn(
    "event_date_msk",
    from_utc_timestamp(col("event_timestamp"), "Europe/Moscow")
)

2. Граничные условия:

• D3 retention можно считать только для пользователей, установивших приложение 3+ дня назад
• Например, если сегодня 26 марта, можно считать D7 только для пользователей, установивших до 19 марта

# Фильтруем по дате установки
cutoff_date_d7 = datetime.now() - timedelta(days=7)
first_opens_valid = first_opens.filter(col("install_date") <= cutoff_date_d7)

3. Различные определения активности:

• Strictest: пользователь открыл приложение (app_open)
• Moderate: пользователь совершил любое действие (click, view)
• Loosest: пользователь был в сессии (даже безактивно)

Типичные значения Retention Rate

Вывод

Расчёт Retention Rate — стандартная задача в аналитике. Ключ — правильно определить когорту (по дате первого открытия) и проверить активность в определённые дни. SQL и PySpark реализации позволяют масштабировать эту метрику для млн пользователей.