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 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
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
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
Now things become a bit more tricky: we take the v_charges 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
Again we query from v_charges but we join both with the next month’s charge and the v_mrr 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 v_mrr 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
Now we simply sum the values of new/contraction/expansion MRR from v_mrr_changes to get the total value per month.
Step 6: Join Everything and Calculate Net New MRR
The last step is to simply join all the different metrics from v_totals, v_mrr_churn and v_mrr and calculate the Net New MRR.
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.
Now that we have the data, we can produce some useful visualizations with it. Some examples we use:
MRR Change Over Time
Total MRR & Accounts Count
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.