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

Зачем Reactivation

Retention рассказывает, кто остался. Reactivation — кто вернулся после долгого молчания. Это отдельная метрика, важная для подписочных и event-driven продуктов: «пользователь не заходил 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 по cohort

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

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;

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

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

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. Marketplace — 60-90.

Ошибка 2. Двойной счёт. Юзер мог быть lapse → return → lapse → return. Считайте по cohort, не по total.

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

Ошибка 4. Игнорировать reason for lapse. Юзер ушёл органически или из-за бана? Reactivation бана — это другая история.

Ошибка 5. Считать новых юзеров как reactivated. Если у юзера ВООБЩЕ не было активности до — он не reactivated, а acquired.

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

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

FAQ

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

SaaS / mobile apps — 30 дней. Marketplace — 60. Travel — 180-365 (длинный цикл).

Reactivation или Retention?

Retention — кто остался. Reactivation — кто вернулся после ухода.

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

В SaaS — 5-15% lapsed возвращаются. В e-com — 20-30%.

Reactivation cost?

Это часть CRM — email-кампании, push, retargeting. Считайте отдельно.

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

Сегментация lapsed: «не купил после регистрации», «купил 1 раз», «топ-клиент ушёл». Разные campaign для каждого.