# MRR is a nightmare for data analysts

--

If you work in a software company (or just read Techcrunch), you probably are familiar with the almighty “MRR”.

For those who don’t, it stands for Monthly Recurring Revenue. MRR represents the income that a company with a subscription business expects to receive in payments on a monthly basis. It is a critical revenue metric and is the north star metric for a lot of SaaS out there.

‍I have always been frustrated by those articles. Not only are they pretty useless, they convey the idea that MRR is a simple concept with shared principles.

That’s why I’m attempting in this (long) article to share all the complexity and nuances behind mighty MRR. I hope it will help all the frustrated analytics and finance professionals struggling with MRR out there. Trust me I’ve been there.‍

# TL;DR: if you think MRR computation is easy, you never had to do it

“Why MRR from Stripe dashboard and from Tableau do not match” asks the Finance team. Easy! Everyone has an answer. Everyone, except the Data Team.

When you start computing MRR in-house (be it in your data warehouse, or in a Spreadsheet), it’s hard to think of all the complexity associated with this, unless it’s something you have done before.‍

# MRR is a simple concept, until you actually need to compute it

In theory, MRR is a simple concept. Your company has a recurring business model: customers subscribe to your service, their payments renew contractually on an agreed-upon timeframe (usually monthly or yearly) and tadam you have monthly recurring revenues.

Looking at this, MRR should not be too hard to compute right? Sum the revenue from all your customers’ subscriptions on a monthly basis (ie. divide by 12 for the yearly ones) and you should have your MRR.‍

# ‍‍People saying “MRR computation is simple” never did it in practice

Most articles (and most people) do not go much further than this.

For example, the first one coming up on Google when searching “how to compute MRR” is from Stripe.

‍According to this Stripe article, MRR is calculated by multiplying all your paying customers by your average revenue per user (ARPU). Yes, pretty simple if you sell all your subscriptions at the same price. In this theoretical case, the “ARPU approach” is equivalent to summing all your subscriptions as mentioned above.

Yet, in the real world, businesses usually have more than one subscription, and even for the same subscription rarely sell it at the same price because of discounts or offers.

So where does this leave us? Well, to compute your MRR you need your ARPU, and to know your ARPU you need your MRR… pretty useful computation method (I guess google SEO crawl bots suck at maths)

And this is often the most common method put forward by articles. Some examples here and here.

What’s even more frustrating? MRR is a pretty useless standalone metric.‍

# What really matters is MRR growth accounting (and here the fun really begins)

MRR on its own does not tell you much (as it’s usually the case for a cumulative metric). It gives you no insights about growth, its drivers or to identify areas for improvement.

What really matters is what it’s called growth accounting. Growth accounting is a framework (not only useful for MRR btw) for understanding the underlying components that drive MRR net growth. It breaks down overall growth in some specific segments, and looks at how different segments contribute to or detract from growth.‍

Assuming the activity we are analyzing is customer MRR. There are 6 segments that MRR can be bucketed into:

• New: Gained from customers that were first active in the present period
• Churned: Lost when a customer who was active in the previous period has no revenue in the present one
• Resurrected: Gained from customers who had churned at some point in the past (and thus generated no revenue in the previous time period) but resumed in the present
• Expansion: Gained from customers increasing revenue relative to the previous time period
• Contraction: Lost from customers decreasing (but not to zero, otherwise they would be churned) revenue relative to the previous period
• Retained: Carried over by customers from the previous period to the present one‍

For example, a customer who spent \$100 last month and \$120 this month would have \$20 in expansion revenue and \$100 in retained revenue. However, if this customer instead spent \$80 this month (while still spending \$100 last month), then \$80 would be counted as retained and \$20 as contracted. The Team at Tribe Capital has written a lot on this here and here if you want to dig further.‍

Those growth buckets are what actually provide businesses with insights about their health and performance. Yet, it’s the trickiest part. Forget simple additions or multiplications to get your MRR, now you need to add both temporal dimensions (is it a new customer? has he churned in the past?) and spend dimension (is the customer paying more or less compared to previous period?).

If you read this far, I hope you’re starting to understand that MRR computation is not that simple.

And what I find funny is that billing solutions (Stripe, Chargebee) are pretty useless to help you compute it. Here is a great read from Lago about Stripe specifically.

Yes, they provide out-of-the-box dashboards that are useful in the early days or for small business, but people want to do more with their MRR data as they grow.‍

Enter “Subscription Analytics” businesses like Chartmogul, Profitwell or Fincome. Those businesses only exist because (1) billing solutions do not provide out-of-the-box solutions and (2) MRR computation is a complex topic. And they charge you a hefty subscription fee to compute your own MRR.‍

# Expert mode: compute MRR in your data warehouse

Once you have a data warehouse set-up, the temptation to compute MRR yourself is high. You already are loading data from your business tools in there, so why not load data from Stripe or Chargebee and just sum your subscriptions’ fees?

‍Let’s deep dive into why computing MRR in your data warehouse is hard (the core of the article, after a long introduction).

And disclaimer: you’ll never find the exact same MRR number as your billing tool UI, otherwise won’t be fun.

## #1 — Raw data from billing tools is messy

Assuming all your billing data sits in a billing tool (that’s a big assumption, often some manual invoices still remain somewhere), loading data into your warehouse is pretty easy. There are loads of ELT/ETL tools our there to help you do so.

That does only get you so far. You now have dozens of tables with cryptic names not matching what you find into your billing tool UI.‍

That’s Chargebee ERD, courtesy of Fivetran. Or more precisely half of it (1 page out of two)‍

## #2 — Invoices or subscriptions

Among all those tables loaded into your data warehouse, you’ll probably find the invoice and the subscription tables. The subscription holds information about the when and how much a customer is charged. The invoice is the “physical” accounting document sent to the customer at every payment.‍

MRR can theoretically be computed on either table:

• Using invoice: sum the subscriptions fees by month
• Using subscription: sum the subscription fees for the period where the subscription was active‍

Well, does not really work that way. In practice, invoice table holds a lot of noisy information and get the active period for a subscription is not straightforward.‍

The subscription fees is not straightforward accessible in the invoice table

The subscription fees might be prorated based on the subscription date. For example, if you bill your customer the first day of every month for the upcoming period, you will have a subscription fee inflated by the pro-rated charge (ie. the prorata between the subscription date and the first of the next month added to the regular subscription for the upcoming month)‍

‍Also, non-recurring fees might be there (eg. setup fees). Those should not be accounted for in your MRR as they are one-off revenues.

Get subscriptions history is not simple

Get the “period where the subscription was active” probably involves complex modelisation with snapshot or slowly changing dimension.‍

## ‍#3 — Discounts and offers

Subscription prices in your product catalogue are probably not the one that are billed to customers. Customers are often offered temporal or ad vitaem discounts. Those should be reflected in your MRR, otherwise you might report a pretty inaccurate picture of your revenues. I have seen companies where discounts concerned more than 50% of their subscriptions.

What’s really fun about discounts is that:

• They can be as a fixed amount or a percentage of a given price
• They can be applied to a subscription, an invoice (even the line item of an invoice) or an add-on
• They can be cumulative (ie. you’re a big customer with 10% off their subscriptions, and you have \$100 offered because you referred a friend
• They often have a pecking order (for exemple apply first 10% then -\$100)
• They can be applied anytime during the lifecycle of a customer (you might start without a discount, have a one-time offer because you have a bug)
• (bonus: you rarely have a nice discount table, they are buried in an event table in JSON format)

## #4 — Dates

When computing MRR, dealing with dates is not trivial. Especially if you bill across several time zones at a given calendar date. Is this column in UTC or in local time? Is 11:00 UTC the first day of the month or the last day of the previous month in your reporting timezone?

You want to report all your MRR to the same month. And suddenly your SQL is full of timestamp and date conversions.‍

## ‍#5 — Billing cycle

We started to touch upon this looking at the invoice table. Billing cycle is also a complexity to deal with. It’s all in the name: all recurring revenues should be converted to a monthly basis. And not all your customers will be on a monthly billing cycle, you will probably have yearly as well and quarterly (who knows?).‍

In most SaaS businesses, subscriptions are only half of the picture. Customer often subscribes to add-ons (eg. additional users, additional feature pack) that are recurring as well and should be included in your MRR.‍

Coming back to our growth accounting, you need to record and model every subscription change to assign your MRR into new, expansion, resurrected, contraction, churn or retained categories.

Modelling changes from plan A to plan B seems pretty easy. It’s not. Some edge cases to have in mind that will impact your “simple” computation:

• Plan changes in the middle of a period
• Changes from a monthly to a yearly plan
• Changes from a yearly to a monthly plan
• Plan changes and discounts
• (and as usual this is often in often recorded in a large event table)‍

## #8 — MRR recognition

This is one of my personal favorites. It’s not directly linked to any particular technical complexity. It’s only trying to get humans to agree to a shared definition. The question is simple: what should the reference date to compute our MRR be? This has a long and far reaching impact, especially for our growth accounting.

And the reference date can be a lot of thing:

• when the subscription was created
• when the subscription was “activated” (for exemple if a trial period exists or if the activation depends on some product trigger)
• when the subscription was billed (ie. the invoice sent to the customer)
• when the subscription was paid (ie. the cash was collected)‍

# Conclusion

Billing is a nightmare. And so is computing MRR on top of it.

To all analytics team struggling out there with MRR: you are not alone, and I hope that it’s now clearer as to why this is the case.

I’ve always wanted to build a dbt package to model MRR based on your Chargebee or Stripe data. If you’re interested, ping me.

PS: a huge thanks to the greatest data analysts I know for the inspiration and SQL screenshot.