All ComponentsMRR is your business’s financial pulse—predictable, measurable, and essential. Tracking its components (new, expansion, contraction, churn, reactivation, net new, committed) reveals the engines of growth and the risks to watch.
WITH
-- All months with any MRR activity
"all_months" AS (
SELECT DISTINCT
DATE_TRUNC('month', "month_data"."month") AS "month"
FROM (
SELECT "start_date" AS "month" FROM "main"."saas_data_subscriptions"
UNION ALL SELECT "payment_date" FROM "main"."saas_data_payments"
UNION ALL SELECT "event_date" FROM "main"."saas_data_events"
) AS "month_data"
),
-- New MRR: First subscription for a customer
"new_mrr" AS (
SELECT
DATE_TRUNC('month', "subscriptions"."start_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "new_mrr"
FROM "main"."saas_data_subscriptions" AS "subscriptions"
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE NOT EXISTS (
SELECT 1
FROM "main"."saas_data_subscriptions" AS "previous_subscriptions"
WHERE "previous_subscriptions"."customer_id" = "subscriptions"."customer_id"
AND "previous_subscriptions"."start_date" < "subscriptions"."start_date"
)
GROUP BY 1
),
-- Expansion MRR
"expansion_mrr" AS (
SELECT
DATE_TRUNC('month', "events"."event_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "expansion_mrr"
FROM "main"."saas_data_events" AS "events"
JOIN "main"."saas_data_subscriptions" AS "subscriptions"
ON "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."event_date" BETWEEN "subscriptions"."start_date" AND COALESCE("subscriptions"."end_date", CURRENT_DATE)
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "events"."event_type" = 'upgrade'
GROUP BY 1
),
-- Contraction MRR
"contraction_mrr" AS (
SELECT
DATE_TRUNC('month', "events"."event_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "contraction_mrr"
FROM "main"."saas_data_events" AS "events"
JOIN "main"."saas_data_subscriptions" AS "subscriptions"
ON "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."event_date" BETWEEN "subscriptions"."start_date" AND COALESCE("subscriptions"."end_date", CURRENT_DATE)
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "events"."event_type" = 'downgrade'
GROUP BY 1
),
-- Churn MRR
"churn_mrr" AS (
SELECT
DATE_TRUNC('month', "events"."event_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "churn_mrr"
FROM "main"."saas_data_events" AS "events"
JOIN "main"."saas_data_subscriptions" AS "subscriptions"
ON "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."event_date" BETWEEN "subscriptions"."start_date" AND COALESCE("subscriptions"."end_date", CURRENT_DATE)
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "events"."is_churn" = TRUE
GROUP BY 1
),
-- Reactivation MRR
"reactivation_mrr" AS (
SELECT
DATE_TRUNC('month', "subscriptions"."start_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "reactivation_mrr"
FROM "main"."saas_data_subscriptions" AS "subscriptions"
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE EXISTS (
SELECT 1
FROM "main"."saas_data_events" AS "events"
WHERE "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."is_churn" = TRUE
AND "events"."event_date" < "subscriptions"."start_date"
)
GROUP BY 1
),
-- Committed MRR
"committed_mrr" AS (
SELECT
DATE_TRUNC('month', "subscriptions"."start_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "committed_mrr"
FROM "main"."saas_data_subscriptions" AS "subscriptions"
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "subscriptions"."status" = 'active'
GROUP BY 1
)
SELECT
"all_months"."month" AS "date",
ROUND(COALESCE("new_mrr"."new_mrr", 0), 2) AS "new_mrr",
ROUND(COALESCE("expansion_mrr"."expansion_mrr", 0), 2) AS "expansion_mrr",
ROUND(COALESCE("contraction_mrr"."contraction_mrr", 0), 2) AS "contraction_mrr",
ROUND(COALESCE("churn_mrr"."churn_mrr", 0), 2) AS "churn_mrr",
ROUND(COALESCE("reactivation_mrr"."reactivation_mrr", 0), 2) AS "reactivation_mrr",
ROUND(
COALESCE("new_mrr"."new_mrr", 0) +
COALESCE("expansion_mrr"."expansion_mrr", 0) -
COALESCE("contraction_mrr"."contraction_mrr", 0) -
COALESCE("churn_mrr"."churn_mrr", 0) +
COALESCE("reactivation_mrr"."reactivation_mrr", 0),
2
) AS "net_new_mrr",
ROUND(COALESCE("committed_mrr"."committed_mrr", 0), 2) AS "committed_mrr"
FROM "all_months"
LEFT JOIN "new_mrr" ON "all_months"."month" = "new_mrr"."month"
LEFT JOIN "expansion_mrr" ON "all_months"."month" = "expansion_mrr"."month"
LEFT JOIN "contraction_mrr" ON "all_months"."month" = "contraction_mrr"."month"
LEFT JOIN "churn_mrr" ON "all_months"."month" = "churn_mrr"."month"
LEFT JOIN "reactivation_mrr" ON "all_months"."month" = "reactivation_mrr"."month"
LEFT JOIN "committed_mrr" ON "all_months"."month" = "committed_mrr"."month"
ORDER BY "all_months"."month"
Visualization Ideas:
Line Chart: Watch each MRR component move through time.
Stacked Bar Chart: View the full story of growth and loss, color by color.
Scorecard: Make net new MRR or committed MRR your headline.
Implementation Checklist
Define your metrics and add them to a data dictionary.
Ensure your database schema supports the required calculations.
Write and test SQL queries for each metric.
Import results into your BI tool. Get started with DataBrain today!
Build and share dashboards with stakeholders.
Schedule regular reviews and updates.
Unlock the power of your data. Start using this SQL Playbook today!
“In the world of SaaS, data is everyone’s responsibility. With this playbook, anyone can own their metrics and drive better outcomes.”
Now, simply run the SQL queries on your data and unlock the power of your SaaS analytics!
All ComponentsMRR is your business’s financial pulse—predictable, measurable, and essential. Tracking its components (new, expansion, contraction, churn, reactivation, net new, committed) reveals the engines of growth and the risks to watch.
WITH
-- All months with any MRR activity
"all_months" AS (
SELECT DISTINCT
DATE_TRUNC('month', "month_data"."month") AS "month"
FROM (
SELECT "start_date" AS "month" FROM "main"."saas_data_subscriptions"
UNION ALL SELECT "payment_date" FROM "main"."saas_data_payments"
UNION ALL SELECT "event_date" FROM "main"."saas_data_events"
) AS "month_data"
),
-- New MRR: First subscription for a customer
"new_mrr" AS (
SELECT
DATE_TRUNC('month', "subscriptions"."start_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "new_mrr"
FROM "main"."saas_data_subscriptions" AS "subscriptions"
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE NOT EXISTS (
SELECT 1
FROM "main"."saas_data_subscriptions" AS "previous_subscriptions"
WHERE "previous_subscriptions"."customer_id" = "subscriptions"."customer_id"
AND "previous_subscriptions"."start_date" < "subscriptions"."start_date"
)
GROUP BY 1
),
-- Expansion MRR
"expansion_mrr" AS (
SELECT
DATE_TRUNC('month', "events"."event_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "expansion_mrr"
FROM "main"."saas_data_events" AS "events"
JOIN "main"."saas_data_subscriptions" AS "subscriptions"
ON "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."event_date" BETWEEN "subscriptions"."start_date" AND COALESCE("subscriptions"."end_date", CURRENT_DATE)
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "events"."event_type" = 'upgrade'
GROUP BY 1
),
-- Contraction MRR
"contraction_mrr" AS (
SELECT
DATE_TRUNC('month', "events"."event_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "contraction_mrr"
FROM "main"."saas_data_events" AS "events"
JOIN "main"."saas_data_subscriptions" AS "subscriptions"
ON "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."event_date" BETWEEN "subscriptions"."start_date" AND COALESCE("subscriptions"."end_date", CURRENT_DATE)
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "events"."event_type" = 'downgrade'
GROUP BY 1
),
-- Churn MRR
"churn_mrr" AS (
SELECT
DATE_TRUNC('month', "events"."event_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "churn_mrr"
FROM "main"."saas_data_events" AS "events"
JOIN "main"."saas_data_subscriptions" AS "subscriptions"
ON "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."event_date" BETWEEN "subscriptions"."start_date" AND COALESCE("subscriptions"."end_date", CURRENT_DATE)
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "events"."is_churn" = TRUE
GROUP BY 1
),
-- Reactivation MRR
"reactivation_mrr" AS (
SELECT
DATE_TRUNC('month', "subscriptions"."start_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "reactivation_mrr"
FROM "main"."saas_data_subscriptions" AS "subscriptions"
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE EXISTS (
SELECT 1
FROM "main"."saas_data_events" AS "events"
WHERE "events"."customer_id" = "subscriptions"."customer_id"
AND "events"."is_churn" = TRUE
AND "events"."event_date" < "subscriptions"."start_date"
)
GROUP BY 1
),
-- Committed MRR
"committed_mrr" AS (
SELECT
DATE_TRUNC('month', "subscriptions"."start_date") AS "month",
ROUND(SUM("plans"."monthly_price")::DOUBLE, 2) AS "committed_mrr"
FROM "main"."saas_data_subscriptions" AS "subscriptions"
JOIN "main"."saas_data_plans" AS "plans" ON "subscriptions"."plan_id" = "plans"."plan_id"
WHERE "subscriptions"."status" = 'active'
GROUP BY 1
)
SELECT
"all_months"."month" AS "date",
ROUND(COALESCE("new_mrr"."new_mrr", 0), 2) AS "new_mrr",
ROUND(COALESCE("expansion_mrr"."expansion_mrr", 0), 2) AS "expansion_mrr",
ROUND(COALESCE("contraction_mrr"."contraction_mrr", 0), 2) AS "contraction_mrr",
ROUND(COALESCE("churn_mrr"."churn_mrr", 0), 2) AS "churn_mrr",
ROUND(COALESCE("reactivation_mrr"."reactivation_mrr", 0), 2) AS "reactivation_mrr",
ROUND(
COALESCE("new_mrr"."new_mrr", 0) +
COALESCE("expansion_mrr"."expansion_mrr", 0) -
COALESCE("contraction_mrr"."contraction_mrr", 0) -
COALESCE("churn_mrr"."churn_mrr", 0) +
COALESCE("reactivation_mrr"."reactivation_mrr", 0),
2
) AS "net_new_mrr",
ROUND(COALESCE("committed_mrr"."committed_mrr", 0), 2) AS "committed_mrr"
FROM "all_months"
LEFT JOIN "new_mrr" ON "all_months"."month" = "new_mrr"."month"
LEFT JOIN "expansion_mrr" ON "all_months"."month" = "expansion_mrr"."month"
LEFT JOIN "contraction_mrr" ON "all_months"."month" = "contraction_mrr"."month"
LEFT JOIN "churn_mrr" ON "all_months"."month" = "churn_mrr"."month"
LEFT JOIN "reactivation_mrr" ON "all_months"."month" = "reactivation_mrr"."month"
LEFT JOIN "committed_mrr" ON "all_months"."month" = "committed_mrr"."month"
ORDER BY "all_months"."month"
Visualization Ideas:
Line Chart: Watch each MRR component move through time.
Stacked Bar Chart: View the full story of growth and loss, color by color.
Scorecard: Make net new MRR or committed MRR your headline.
Implementation Checklist
Define your metrics and add them to a data dictionary.
Ensure your database schema supports the required calculations.
Write and test SQL queries for each metric.
Import results into your BI tool. Get started with DataBrain today!
Build and share dashboards with stakeholders.
Schedule regular reviews and updates.
Unlock the power of your data. Start using this SQL Playbook today!
“In the world of SaaS, data is everyone’s responsibility. With this playbook, anyone can own their metrics and drive better outcomes.”
Now, simply run the SQL queries on your data and unlock the power of your SaaS analytics!
Make analytics your competitive advantage
Get it touch with us and see how Databrain can take your customer-facing analytics to the next level.