Skip to content

Instantly share code, notes, and snippets.

@arikfr
Last active February 20, 2022 14:57
Show Gist options
  • Save arikfr/8b86207746a84ff9d32f97fa6343f567 to your computer and use it in GitHub Desktop.
Save arikfr/8b86207746a84ff9d32f97fa6343f567 to your computer and use it in GitHub Desktop.
SaaS Metrics Query
/*
Explanation of what's going on in this query can be found in this blog post: https://blog.redash.io/sql-query-to-calculate-saas-metrics-dd25d72a0521.
*/
WITH v_charges AS (
SELECT org_id,
date_trunc('month', start_date) AS month,
coalesce((extra::json->>'amount')::float, (extra::json->>'charged_amount')::integer/100) as total
FROM charges
WHERE extra::json->>'months' = '1'
),
v_mrr as (
SELECT month,
sum(total) as mrr,
count(distinct org_id) as accounts,
sum(total) / count(distinct org_id) as arpu
FROM v_charges
GROUP BY 1
),
v_mrr_changes AS (
SELECT this_month.org_id,
this_month.month,
case
when previous_month.month is null then this_month.total
else 0
end as new_mrr,
case
when previous_month.total is null then 0
when previous_month.total > this_month.total then previous_month.total - this_month.total
end as contraction_mrr,
case
when previous_month.total is null then 0
when previous_month.total < this_month.total then this_month.total - previous_month.total
end as expansion_mrr
FROM v_charges as this_month
LEFT JOIN v_charges previous_month ON this_month.org_id = previous_month.org_id AND this_month.month = previous_month.month + interval '1 month'
),
v_mrr_churn AS (
SELECT this_month.month + interval '1 month' as month,
sum(
case
when next_month.month is null then this_month.total
else 0
end) as churned_mrr,
100.0 * sum(
case
when next_month.month is null then this_month.total
else 0
end) / v_mrr.mrr as mrr_churn,
100.0 * (sum(
case
when next_month.month is null then 1
else 0
end) * 1.0) / v_mrr.accounts as accounts_churn
FROM v_charges as this_month
LEFT JOIN v_charges next_month ON this_month.org_id = next_month.org_id AND this_month.month = next_month.month - interval '1 month'
JOIN v_mrr on v_mrr.month = this_month.month
group by 1, v_mrr.mrr, v_mrr.accounts
),
v_totals as (
SELECT v_mrr_changes.month,
sum(new_mrr) as new_mrr,
sum(contraction_mrr) as contraction_mrr,
sum(expansion_mrr) as expansion_mrr
FROM v_mrr_changes
GROUP BY 1
)
SELECT v_totals.month,
v_mrr.mrr,
v_mrr.accounts,
v_totals.new_mrr,
v_totals.expansion_mrr,
v_mrr_churn.churned_mrr*-1 as churned_mrr,
v_totals.contraction_mrr*-1 as contraction_mrr,
new_mrr + expansion_mrr - churned_mrr - contraction_mrr as net_new_mrr,
mrr_churn,
accounts_churn,
v_mrr.arpu
FROM v_totals
LEFT JOIN v_mrr_churn on v_totals.month = v_mrr_churn.month
JOIN v_mrr on v_mrr.month = v_totals.month
WHERE v_totals.month < date_trunc('month', now())
ORDER BY month desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment