It will probably not surprise you that we’re using Redash to track our business metrics. As those are pretty much standard for all SaaS businesses, I thought it will be useful to share the query we use to calculate these metrics.
What metrics do we track?
- MRR — Monthly Recurring Revenue
- # of Paying Accounts
- New MRR: Additional MRR from new customers
- Expansion MRR: Additional MRR from existing customers upgrades
- Churned MRR: MRR lost from cancellations
- Contraction MRR: MRR lost from existing customers downgrades
- Net New MRR: (New MRR + Expansion MRR) — (Churned MRR + Contraction MRR)
- MRR Churn/Accounts Churn: % of users who left this month, but were active last month
- ARPU: Average Revenue Per User (Account)
The Query
The query we share here was written for PostgreSQL, but as any SQL, can be repurposed for any database. You will just need to replace some functions and maybe the use of CTEs (WITH).
The entire query is a bit long, so let’s break it down to smaller steps:
Step 1: normalize the incoming data
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'
),
What’s going on here? We use PostgreSQL’s CTE feature (WITH) to create a simple to use “view” local to the query, this allows us to normalize the incoming data.
If you want to repurpose this query for your data, all you need to do is to update this single definition and everything else will work as is.
We basically need:
- Account ID
- Date of charge
- Charge amount
By calculating the different metrics using actual charges some metrics will be a little skewed due to failed charges. For example the churn rate for the last month or two will usually update a few times in the following month as charges pass through.
Step 2: calculate MRR, accounts per month and ARPU
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
),
OK, this is very easy: we aggregate the data by month to get the total number of accounts and resulting MRR in a month. Then we use the same numbers to calculate the ARPU.
Step 3: calculate MRR changes
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'
),
Now things become a bit more tricky: we take the vcharges_ view we defined, and LEFT JOIN it with itself. We do this join to get the previous month’s (if available) charge for this account.
- If there was no charge for this account in the previous month, we count this as New MRR (this is the first CASE statement).
- If there was a charge for this account in the previous month and it was higher than this month, we calculate the resulting Contraction MRR(previous month — this month).
- If there was a charge for this account in the previous month and it was lower than this month, we calculate the resulting Expansion MRR (this month — previous month).
Step 4: Calculate Churn
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
),
Again we query from vcharges_ but we join both with the next month’s charge and the vmrr_ view we defined to get the total MRR of the month.
- The first sum/case column is to calculate the churned MRR value. If there was no charge in the following month for this user, we consider this MRR as churned. This is of course not a 100% accurate assumption, as it might be that there was no charge because of credit card failure, but as mentioned, this is a limitation of this model. If you have proper subscriptions data, you can get a more accurate view.
- The second sum/case column is to calculate the churned MRR % (this is why we joined with the vmrr_ view). Basically we divide the churned MRR by the MRR of that month.
- Next we calculate accounts churn % (how many accounts we lost in %).
Step 5: Calculate Total per Month
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
)
Now we simply sum the values of new/contraction/expansion MRR from vmrrchanges to get the total value per month.
Step 6: Join Everything and Calculate Net New MRR
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,
v_totals.net_new_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
The last step is to simply join all the different metrics from vtotals, _vmrrchurn and vmrr and calculate the _Net New MRR.
Done. 🙌
Note that we filter the last month data, because it won’t be accurate: any account not charged yet will be considered as churned. We can fix this by adding any account that wasn’t cancelled but not charged yet to the list of charges.
The nice thing of building the query step by step, is that we only need to “complicate” the first query (v_charges), but everything else stays the same. 👌
You can find the full query in this Gist.
Visualizations
Now that we have the data, we can produce some useful visualizations with it. Some examples we use:
MRR Change Over Time
Visualization Type: Chart, with Net New MRR as Line and the rest as Bars.
Total MRR & Accounts Count
Visualization Type: Counter with the accounts count as the target value
These two are just an example, there are many others you can produce like MRR growth over time, churn over time, compare MRR churn vs. accounts churn and more.
We’re happy to share this query, and hope you will find it useful (feel free to click on the 👏 button to show your love). If you created some elaborate SQL queries (with Redash or not, but you really should use Redash), we are welcoming you to share them. Either in our forum, or as a guest blog post here.
4
1302
