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