Условие

К вам приходит продакт и говорит: «Хочу посчитать retention клиентов по нашей таблице транзакций (она содержит транзакции пользователей МТС-финтеха с 2021 года)». Опишите свои действия: какие вопросы вы зададите. Затем сделайте предположения по каждому вопросу и напишите код в Python или SQL для извлечения нужных данных.

Решение

Подход

Слово «retention» — самое многозначное в продуктовой аналитике. Junior-чек: уточнить 5 базовых вещей перед расчётом.

Вопросы продакту

Какое событие = «активность»? Любая транзакция? Транзакция определённой суммы? Конкретный продукт?
Какой когортный признак? Дата первой транзакции? Дата регистрации? Месяц активации продукта?
Окно retention. День N? Неделя N? Месяц N? [1d, 7d, 14d, 30d, 90d]?
Тип retention.
- Classic / N-day: вернулся в точный день N.
- Rolling / unbounded: вернулся в любой день между 1 и N.
- Bracket: вернулся в диапазоне [N1, N2].
- Range retention: % когорты, активной в произвольную «корзину» дней.
Cut-off / срок наблюдения. Если когорта только что появилась — у неё нет 30-дневного retention. Включаем ли неполные когорты? Помечаем NULL?

Дополнительно:

Сегментация: по продукту, гео, тарифу.
Bench mark: с чем сравниваем? Прошлый квартал?
Дашборд или одноразовый отчёт?

Предположения

После «допроса» делаем предположения и фиксируем их:

Активность = любая транзакция в transact.
Когорта = месяц первой транзакции (first_tx_month).
Метрика = classic monthly retention: % когорты, имевших транзакцию в M+1, M+2, ..., M+12.
Включаем все когорты с историей ≥12 месяцев (полные); неполные — отдельно с пометкой.

Реализация (SQL)

WITH first_tx AS (
    SELECT
        id_client,
        DATE_TRUNC('month', MIN(tran_time)) AS cohort_month
    FROM transact
    GROUP BY id_client
),
activity AS (
    SELECT
        f.id_client,
        f.cohort_month,
        DATE_TRUNC('month', t.tran_time) AS tx_month,
        EXTRACT(YEAR FROM age(DATE_TRUNC('month', t.tran_time), f.cohort_month)) * 12
        + EXTRACT(MONTH FROM age(DATE_TRUNC('month', t.tran_time), f.cohort_month)) AS month_offset
    FROM first_tx f
    JOIN transact t ON t.id_client = f.id_client
)
SELECT
    cohort_month,
    month_offset,
    COUNT(DISTINCT id_client) AS active_users,
    COUNT(DISTINCT id_client) * 1.0 /
        (SELECT COUNT(DISTINCT id_client)
         FROM first_tx f2
         WHERE f2.cohort_month = activity.cohort_month) AS retention_rate
FROM activity
WHERE month_offset BETWEEN 0 AND 12
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;

Реализация (Python / Pandas)

import pandas as pd
 
df = pd.read_sql("SELECT id_client, tran_time FROM transact", con=conn)
df["tran_month"] = df["tran_time"].dt.to_period("M")
 
first = df.groupby("id_client")["tran_month"].min().rename("cohort_month")
df = df.join(first, on="id_client")
df["month_offset"] = (df["tran_month"] - df["cohort_month"]).apply(lambda x: x.n)
 
retention = (
    df.groupby(["cohort_month", "month_offset"])["id_client"]
      .nunique()
      .reset_index(name="active")
)
 
cohort_size = (
    df[df["month_offset"] == 0]
      .groupby("cohort_month")["id_client"].nunique()
      .rename("size")
)
retention = retention.join(cohort_size, on="cohort_month")
retention["rate"] = retention["active"] / retention["size"]
 
# Триангульная таблица
pivot = retention.pivot(index="cohort_month", columns="month_offset", values="rate")
print(pivot.head())

Что показать продакту

Heat-map когорт (cohort_month × month_offset).
«Кривая retention» для последних 6 когорт.
Один сводный показатель: средний retention M+1, M+3, M+6, M+12.
Бенчмарк vs предыдущий период.

Подводные камни

Cohort definition. «По первой транзакции» vs «по дате регистрации» — разные числа. Если у вас нет даты регистрации, говорите «approximated by first transaction».
Censored cohorts. Когорта апреля-2024 не имеет 12-месячного retention. Не делайте усреднение по недозревшим — занижает.
Missing months. Если клиент пропустил месяц 2 и вернулся в месяце 3 — это retention M+3 (он был активен), но не M+2. Heat-map это покажет.
«Отток» vs «retention». Иногда продакт спрашивает retention, имея в виду churn (1 - retention). Уточняйте.
Размер когорт неравномерен. Если в декабре когорта = 1000, в феврале = 100, средние числа усредняются с весом.
Retention ≠ engagement. «Зашёл и ничего не купил» — это retention или нет? Зависит от определения активности.

Эталонный ответ

Сначала уточняем у продакта 5 вещей: что считать активностью, когортный признак, окно, тип retention (classic/rolling/bracket), что делать с незрелыми когортами. Фиксируем предположения, запрашиваем подтверждение, потом считаем. Код — в SQL CTE с month_offset и в Python pivot. Дашборд: heat-map + retention curves + сводные M+1/3/6/12.

МТС: какие вопросы задать продакту, который просит retention