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

Зачем Downgrade Rate

Downgrade — soft churn. Юзер не ушёл, но платит меньше. Если 10% юзеров downgrade каждый квартал — sign того что premium plan не worth для них. Возможно overpriced или underdelivered.

Формула

Downgrade Rate = customers_downgraded / total_customers × 100%
Contraction MRR = SUM(old_plan - new_plan) для downgraded

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

WITH stats AS (
    SELECT
        user_id,
        new_plan_value < old_plan_value AS downgraded
    FROM plan_changes
    WHERE change_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    COUNT(*) AS users_with_changes,
    COUNT(*) FILTER (WHERE downgraded) AS downgraded,
    COUNT(*) FILTER (WHERE downgraded)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS downgrade_rate_pct
FROM stats;

Contraction MRR

SELECT
    DATE_TRUNC('month', change_at) AS month,
    COUNT(*) FILTER (WHERE new_plan_value < old_plan_value) AS downgrade_events,
    SUM(old_plan_value - new_plan_value) FILTER (WHERE new_plan_value < old_plan_value) AS contraction_mrr,
    SUM(new_plan_value - old_plan_value) FILTER (WHERE new_plan_value > old_plan_value) AS expansion_mrr
FROM plan_changes
WHERE change_at >= '2026-01-01'
GROUP BY 1
ORDER BY 1;

Net Expansion = Expansion - Contraction.

Reasons

SELECT
    downgrade_reason,
    COUNT(*) AS events,
    SUM(old_plan_value - new_plan_value) AS contraction_mrr
FROM plan_changes
WHERE new_plan_value < old_plan_value
  AND change_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY downgrade_reason
ORDER BY events DESC;

Top downgrade reasons: «too expensive», «not using features», «budget cut».

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

Ошибка 1. Annual vs Monthly. Annual contract downgrade — at renewal. Monthly — anytime.

Ошибка 2. Включать «paused». Pause vs downgrade — different. Pause = $0 temporarily, downgrade = lower tier.

Ошибка 3. Trial → free. Если есть free tier, trial ended → free counts as downgrade?

Ошибка 4. Pricing changes. Company lowered prices. Existing grandfather at lower → не downgrade.

Ошибка 5. Seat reduction. Enterprise reduced from 100 to 50 seats — это downgrade или contraction? Часто счёт по seats.

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

• Как посчитать upgrade rate в SQL
• Как посчитать MRR Churn в SQL
• Как посчитать NRR в SQL
• Как посчитать MRR в SQL

FAQ

Какой Downgrade Rate ok?

<5% quarterly — норма. >10% — concerning.

Downgrade vs Churn?

Downgrade = stays. Churn = gone. Downgrade — leading indicator of churn.

Как уменьшить?

1. Identify users approaching downgrade (usage decline). 2) Proactive CSM intervention. 3) Better value communication.

Downgrade → Re-upgrade?

Полезный знак — re-engaged. Часто после CSM или new feature release.

Auto-downgrade?

Если карта declined, account downgraded to free. Counts as downgrade, но different cause.