Как посчитать Reactivation в SQL

Зачем Reactivation

Удержание (retention) показывает, кто остался. Reactivation — кто вернулся после долгого молчания. Это отдельная метрика, важная для подписочных и событийных продуктов: «пользователь не заходил 60+ дней — вернётся ли?»

Что такое Reactivation

Reactivation Rate — доля пользователей, вернувшихся после периода неактивности.

Reactivation = Returned users / Lapsed users × 100%

«Lapsed» обычно — нет активности 30/60/90 дней. «Returned» — был активен после lapsed-периода.

Базовый расчёт

Данные: events(user_id, event_date).

WITH user_activity AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_date < CURRENT_DATE - INTERVAL '60 days' THEN event_date END) AS last_active_before,
        MIN(CASE WHEN event_date >= CURRENT_DATE - INTERVAL '60 days' THEN event_date END) AS first_active_recent
    FROM events
    GROUP BY user_id
),
lapsed AS (
    SELECT user_id
    FROM user_activity
    WHERE last_active_before IS NOT NULL
      AND first_active_recent IS NULL
       OR (first_active_recent IS NOT NULL
           AND first_active_recent - last_active_before > INTERVAL '60 days')
)
SELECT
    COUNT(*) AS lapsed_users,
    COUNT(CASE WHEN first_active_recent IS NOT NULL THEN 1 END) AS reactivated,
    COUNT(CASE WHEN first_active_recent IS NOT NULL THEN 1 END)::NUMERIC
        / NULLIF(COUNT(*), 0) * 100 AS reactivation_pct
FROM user_activity
WHERE last_active_before IS NOT NULL;

Reactivation по когортам

Когда пользователь ушёл — влияет на вероятность возврата:

WITH user_lapse AS (
    SELECT
        user_id,
        MAX(event_date) FILTER (WHERE event_date < CURRENT_DATE - INTERVAL '60 days') AS lapse_date
    FROM events
    GROUP BY user_id
),
returned AS (
    SELECT
        u.user_id,
        u.lapse_date,
        DATE_TRUNC('month', u.lapse_date) AS lapse_month,
        MIN(e.event_date) AS return_date
    FROM user_lapse u
    LEFT JOIN events e ON e.user_id = u.user_id
       AND e.event_date > u.lapse_date + INTERVAL '60 days'
    WHERE u.lapse_date IS NOT NULL
    GROUP BY u.user_id, u.lapse_date
)
SELECT
    lapse_month,
    COUNT(*) AS lapsed,
    COUNT(return_date) AS returned,
    COUNT(return_date)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS reactivation_pct
FROM returned
GROUP BY lapse_month
ORDER BY lapse_month;

По типу события

Полезно различать: «вернулся в продукт» и «вернулся и совершил покупку».

WITH lapsed AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_date < CURRENT_DATE - INTERVAL '60 days'
      AND user_id NOT IN (
          SELECT DISTINCT user_id
          FROM events
          WHERE event_date >= CURRENT_DATE - INTERVAL '60 days'
            AND event_date <  CURRENT_DATE - INTERVAL '30 days'
      )
)
SELECT
    COUNT(DISTINCT l.user_id) AS total_lapsed,
    COUNT(DISTINCT CASE WHEN e.event_type = 'session_start' THEN l.user_id END) AS reactivated_visit,
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN l.user_id END) AS reactivated_purchase
FROM lapsed l
LEFT JOIN events e ON e.user_id = l.user_id
   AND e.event_date >= CURRENT_DATE - INTERVAL '30 days';

Частые ошибки

Ошибка 1. Размытое определение lapse. 30, 60 или 90 дней? Зафиксируйте порог под свой продукт: для SaaS обычно 30 дней, для маркетплейсов — 60–90.

Ошибка 2. Двойной счёт. Пользователь мог пройти цикл lapse → return → lapse → return несколько раз. Считайте по когортам, а не по общему числу.

Ошибка 3. NOT IN с NULL. user_id NOT IN (subquery) ломается, если в subquery есть NULL. Используйте NOT EXISTS.

Ошибка 4. Игнорировать причину ухода. Пользователь ушёл сам или из-за бана? Реактивация забаненных — совсем другая история.

Ошибка 5. Считать новых пользователей как реактивированных. Если у пользователя вообще не было активности раньше — это не реактивация, а привлечение (acquired).

Связанные темы

• Как посчитать retention в SQL
• Как посчитать rolling retention в SQL
• Как посчитать churn в SQL
• Как посчитать MRR Churn в SQL

FAQ

Какой период считать lapse?

Для SaaS и мобильных приложений обычно берут 30 дней, для маркетплейсов — 60. В travel-продуктах порог доходит до 180–365 дней из-за длинного цикла покупки.

Reactivation или Retention?

Удержание (retention) считает тех, кто остался и не уходил. Реактивация — тех, кто уже ушёл, а потом вернулся. Это две разные метрики, и путать их не стоит.

Какой Reactivation хороший?

В SaaS обычно возвращается 5–15% ушедших пользователей, в e-commerce — 20–30%. Конкретные цифры сильно зависят от продукта и качества CRM.

Сколько стоит реактивация?

Это часть работы CRM — email-рассылки, пуши, ретаргетинг. Стоимость этих кампаний стоит считать отдельной статьёй расходов.

Как улучшить?

Начните с сегментации ушедших: «не купил после регистрации», «купил один раз», «ушёл топ-клиент». Под каждый сегмент нужна своя кампания возврата.