MRR-мост в SQL: новые подписки, расширение, сжатие и отток

Месячная повторяющаяся выручка снизилась на 80 тысяч. Это мог быть уход одного клиента или сочетание новых продаж и сокращения крупных аккаунтов. В SQLite построим MRR-мост между двумя снимками: каждое изменение получит тип, а сумма вкладов сойдётся с общим итогом.

MRR отличается от поступления денег

Для примера MRR — месячный эквивалент действующей подписки на дату снимка. Годовая предоплата не становится MRR целиком в месяц платежа. Разовые услуги не включаются. Значения в старом и новом снимке должны быть рассчитаны по одному правилу, иначе мост будет объяснять изменение методики вместо бизнеса.

Единица анализа — аккаунт. Если у него несколько подписок, сначала приведите их к месячному эквиваленту и агрегируйте. Все суммы считаются в одной валюте. В примере снимки содержат только аккаунты с положительным MRR, а отсутствие аккаунта означает ноль действующей повторяющейся выручки на эту дату.

Из двух снимков можно выделить появление, рост, снижение и исчезновение. Чтобы отличить совершенно нового клиента от реактивации, понадобится более длинная история. Поэтому появление в нашем мосте называется new_in_snapshot. Не выдавайте его автоматически за первую продажу за всё время.

Строим полное множество аккаунтов

Сначала UNION собирает ключи обоих снимков без повторов. Два LEFT JOIN добавляют старое и новое значение. Это переносимый способ полного сопоставления, который явно сохраняет ушедших и появившихся. После согласованной трактовки отсутствия как нуля можно рассчитать delta.

Классификация задаётся по значениям: ноль→положительное — появление, положительное→ноль — отток, рост положительного — expansion, снижение — contraction. Равные значения получают unchanged. Сохраняем подписанный вклад: отток и сокращение отрицательны, новые и расширение положительны. Для диаграммы можно дополнительно вывести абсолютный размер, но контрольная сумма использует знак.

WITH previous(customer,mrr) AS (
 VALUES ('A',100),('B',200),('C',80)
), current(customer,mrr) AS (
 VALUES ('A',150),('B',100),('D',50)
), keys AS (
 SELECT customer FROM previous UNION SELECT customer FROM current
), joined AS (
 SELECT k.customer,COALESCE(p.mrr,0) AS old_mrr,COALESCE(c.mrr,0) AS new_mrr
 FROM keys k LEFT JOIN previous p USING(customer) LEFT JOIN current c USING(customer)
)
SELECT customer,old_mrr,new_mrr,
 CASE WHEN old_mrr=0 THEN 'new_in_snapshot'
 WHEN new_mrr=0 THEN 'churn'
 WHEN new_mrr>old_mrr THEN 'expansion'
 WHEN new_mrr<old_mrr THEN 'contraction' ELSE 'unchanged' END AS kind,
 new_mrr-old_mrr AS delta
FROM joined ORDER BY customer;

Ожидаемые строки результата в порядке столбцов SELECT:

[["A", 100, 150, "expansion", 50], ["B", 200, 100, "contraction", -100], ["C", 80, 0, "churn", -80], ["D", 0, 50, "new_in_snapshot", 50]]

Итог и направление работы

Старый MRR равен 380, новый — 300. A дал расширение на 50, B сокращение на 100, C отток на 80, D появление на 50. Вклады складываются в −80. Поэтому общая просадка не означает отсутствие новых продаж: положительные изменения есть, но сокращение и отток больше.

Для команды продаж список expansion показывает, какие аккаунты увеличили потребление. Для клиентского сервиса contraction и churn становятся поводом проверить договор, дату прекращения и причины. Движение в MRR само по себе не объясняет, что произошло: ошибка расчёта тарифа также способна создать ложное сокращение.

Как сохранить сопоставимость месяцев

Фиксируйте момент снимка и правила временной приостановки, скидок и валютной переоценки. Если курс валют меняется, выделите его влияние отдельно или сравнивайте в постоянной валюте. Иначе аккаунт может попасть в expansion без изменения договора.

Для регулярного отчёта группируйте готовые клиентские вклады по kind, сохраняя исходную детализацию. Общая сумма delta должна совпасть с разностью MRR снимков. Не считайте мост по платежам за месяц: годовые предоплаты и задолженность смешают денежный поток с повторяющейся выручкой и изменят смысл всех категорий.

Платёжный расчёт за неполный период — соседняя, но отдельная задача: Подписка на часть месяца: как посчитать пропорциональную сумму в SQL.

Следующий уровень SQL-практики

В «Продвинутом SQL» вы отрабатываете сложные соединения, исторические значения и сверку отчётов. Многошаговые расчёты выполняются на SQLite в браузере с автоматической проверкой на разных данных. Затем вы решаете цельные тестовые задания и собираете витрину интернет-магазина. Откройте программу «Продвинутого SQL».