Как посчитать grace period recovery в SQL

Зачем recovery rate

Grace period — окно (3-30 дней), когда после failed payment подписка ещё активна, шлются retry/email-напоминания. Recovery rate показывает, сколько таких юзеров возвращаются с успешным платежом. Это «involuntary churn defense». Хороший grace period может спасти 30-60% failed payments — буквально миллионы revenue.

Структура данных

Минимум таблица событий: failed payment, retry payment, success payment, expired grace.

user_id | event_type    | event_date
42      | payment_failed| 2026-04-01
42      | payment_retry | 2026-04-03
42      | payment_success| 2026-04-03

Recovery rate в SQL

WITH failed_payments AS (
    SELECT user_id, MIN(event_date) AS first_failure
    FROM payment_events
    WHERE event_type = 'payment_failed'
      AND event_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
),
recovered AS (
    SELECT
        f.user_id,
        MIN(p.event_date) AS recovery_date
    FROM failed_payments f
    JOIN payment_events p
      ON p.user_id = f.user_id
     AND p.event_type = 'payment_success'
     AND p.event_date BETWEEN f.first_failure AND f.first_failure + INTERVAL '14 days'
    GROUP BY f.user_id
)
SELECT
    (SELECT COUNT(*) FROM failed_payments) AS failed,
    (SELECT COUNT(*) FROM recovered) AS recovered,
    (SELECT COUNT(*) FROM recovered)::NUMERIC * 100
    / NULLIF((SELECT COUNT(*) FROM failed_payments), 0) AS recovery_rate_pct;

INTERVAL '14 days' — длина grace period. Адаптируйте под продукт.

Time to recovery

WITH events AS (
    SELECT user_id, MIN(event_date) AS first_failure FROM payment_events
    WHERE event_type = 'payment_failed' GROUP BY user_id
),
recovery_time AS (
    SELECT
        f.user_id,
        EXTRACT(EPOCH FROM (
            MIN(p.event_date) - f.first_failure
        )) / 86400 AS days_to_recovery
    FROM events f
    JOIN payment_events p
      ON p.user_id = f.user_id
     AND p.event_type = 'payment_success'
     AND p.event_date > f.first_failure
    GROUP BY f.user_id, f.first_failure
)
SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_recovery) AS median_days,
    AVG(days_to_recovery) AS avg_days,
    COUNT(*) FILTER (WHERE days_to_recovery <= 1) AS recovered_in_1_day,
    COUNT(*) FILTER (WHERE days_to_recovery <= 7) AS recovered_in_7_days
FROM recovery_time;

Большая часть recovery — в первые 1-3 дня. После недели — почти потеряны.

По причине failure

SELECT
    pf.failure_reason,
    COUNT(DISTINCT pf.user_id) AS failures,
    COUNT(DISTINCT ps.user_id) AS recoveries,
    COUNT(DISTINCT ps.user_id) * 100.0 / NULLIF(COUNT(DISTINCT pf.user_id), 0) AS recovery_rate_pct
FROM payment_failures pf
LEFT JOIN payment_successes ps
  ON ps.user_id = pf.user_id
 AND ps.event_date BETWEEN pf.event_date AND pf.event_date + INTERVAL '14 days'
GROUP BY pf.failure_reason
ORDER BY recovery_rate_pct DESC;

Insufficient funds → высокий recovery (когда зарплата). Expired card → low recovery, нужно update card flow.

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

Ошибка 1. Считать recovery как любой success. Только success в пределах grace period считается recovery. Позже — это уже re-subscription.

Ошибка 2. Multiple failures в cohort. Один customer мог failed 3 раза подряд. Считайте на first_failure уровне.

Ошибка 3. Не различать voluntary cancel vs grace expiry. Customer cancel = voluntary churn, отдельная категория от grace expiry.

Ошибка 4. Игнорировать pre-dunning emails. Если шлёте reminder за 3 дня ДО expiry, recovery начинается раньше failed payment.

Ошибка 5. Не сегментировать. Stripe vs PayPal vs Apple Pay имеют разную failure recovery. Сегментируйте.

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

• Как посчитать payment success rate в SQL
• Как посчитать involuntary churn в SQL
• Как посчитать win-back rate в SQL
• Как посчитать chargeback rate в SQL

FAQ

Какой recovery rate хороший?

Industry: 30-50% для B2C SaaS. С smart dunning system — 60-80%.

Сколько дней grace?

7-14 для consumer. 30 для enterprise.

Recovery в первый день?

В B2C 40-60% recovery в первые 24 часа. Это retry чарж от системы.

Email retry или auto retry?

Smart dunning делает оба. Stripe Billing — встроенное.

Что не recovery?

Voluntary cancel, chargeback, expired card без update.