Posted on

Subscription metrics: How to calculate MRR, churn rate, ARPPU and more

Oh sweet MRR! The yardstick of progress for a SaaS / subscription / membership / recurring revenue business.

Talk to the owner or marketing head of any subscription business and they will crib about how their MRR is not growing as expected, or wax eloquent about their phenomenal MRR and the hockey stick growth they witnessed.

Some may go on to describe their churn rates and quick ratios. Their action plan of dunning, recovery emails and ongoing user engagement to bring the churn down by 50 basis points.

Alright, let’s stop.

If you are in a recurring billing business, you certainly know the situation. If you are not, you most likely want to migrate to a subscription system.

So what are all these metrics? What does all that jargon mean?

We have in-depth explanations for these SaaS / subscription / recurring business metrics. For now, let me give you a quick run down.

The simplest explanation of subscription business metrics on the Internet

  • MRR, ARR: Monthly Recurring Revenue and Annual Run Rate. Essentially, how much money are you making per month. Subscriptions that do not recur at monthly interval, are “converted” to monthly. For example, annual subscription amount is divided by 12, or weekly subscription may be multiplied by 4.33 etc.
  • Churn: What you are losing every month – revenue, customers, number of subscriptions. Usually denoted as a percentage of MRR.
  • Switches: People change their subscription plans. You want to track upgrade and downgrades. Upgrades expand MRR, downgrades contract it.
  • Trials: number of trials, conversion percentage of trials to paid.. Trial can be considered a product plan, and you can consider it an “upgrade” when people switch to a paid plan. But it merits measurement on its own.
  • ARPU, ARPPU, ARPA: Average Revenue Per User (or Per Paid User, or Per Account). This is essentially MRR divided by number of customers (or number of paid customers when you have free trials). In some situations, you may want to combine all users from an organization and count their total revenue as Average Revenue Per Account.
  • CLTV, LTV: Lifetime Value of a Customer. In most cases, people invert their churn rate to arrive at average number of months a customer stays active. Then multiply that with ARPU to arrive at LTV.
  • CAC: Cost of Acquiring a Customer. This is often an internal metrics, because reporting systems on their own don’t factor in costs. Comparing CAC with LTV tells you how profitable a customer is over their lifetime. Quicker breakeven means better scalability and better valuations!
  • Quick Ratio: How much money are you adding per month, divided by how much money you are losing. Quickly indicates if you are floating or sinking!
    Formula would be (New MRR + Expansion MRR) / (Contraction MRR + Churned MRR).
    You may even want to measure subscriptions by their status – New, Active, Reactivated, Churned, Cancelled, Suspended etc.
    Another important measurement is “Failed charges”. Credit cards expire all the time and as your customer base grows, the amount of failed charge attempts will increase.

  • Dunning: It’s a process of avoiding and recovering from these failures. A good dunning solution can easily add 10-20% to your revenue.
  • Refunds: Many people don’t include refunds in churn, since it’s more of a cashflow change. If a refund cancels a subscription, that will be counted as churn.
  • Cashflow: Cash is sanity, others are vanity. Keeping an eye on cashflow is critical for business.
    Product and variant wise breakup of all these metrics. Whether you call your subscription products “plans” or anything else, it would certainly help to track important numbers for each product.

Apart from all this, you may also want to compare these metrics with historical data to see trends, and even get forecasts for future planning.

Can you believe it? My simple explanation was this long 😛

Measuring and calculating MRR and other metrics – simple yet comprehensive solution

I am going to talk about databases and queries here. If you are not into programming or databases, don’t worry. I will keep it as simple as possible.

But it’s useful if you understand these. It’s math at the end of the day, and that too simple math.

This is the most comprehensive, yet easy approach we’ve found. You will soon see the elegance in the solution.

Alright, let’s dive in.

Part 1: Storing important information

First, I’m assuming you are storing subscription transactions information in MySQL table – or similar. So every time you receive a new subscription, get a payment on it, or something changes in its status – cancellation, expiry, failure to charge etc – we will have an entry in that table.

Since you are logging all events to the subscription, this table will get bigger as time passes.

Calculating MRR from this table is not a good idea.


How to calculate MRR?

Let’s create a new table to store only “significant” events. Things that materially change the subscription. Signing up, upgrading or downgrading, expiry, moving from trial to paid etc.

We should also store product and variation identifiers in the table so we can calculate MRR (and other metrics) down to the variation level.

We also need to store customer’s ID so we can calculate metrics even at customer level – remember, they may have multiple active subscriptions!

Here’s an excerpt from this table.

timestampsubs_id, emailproduct_idvariation_idevent_typeis_trialis_new_customerold_mrrnew_mrrcurrency
2018-07-10123john@domainputlergrowthcreated1100USD
2018-07-25123john@domainputlergrowthupdated10079USD
2018-08-15123john@domainputlerscaleupdated0079249USD
2018-12-10123john@domainputlerscaleheld00249249USD
2018-12-15123john@domainputlerscalecancelled002490USD

In simple English,

  • John signed up for a trial on 10th July. Converted to a paid plan of $79/month on 25th.
  • Upgraded to a higher plan of $249/m on 15th August.
  • Somehow he did not want to continue, so he cancelled on 10th December.
  • But since his monthly subscription was paid till 14th of the month, he used the product till 14th, and on 15th it expired.
  • Tumbling our MRR from $249 to 0.

Let’s add a few more entries to this table for some other users, and then start calculating our metrics.

timestampsubs_idemailproduct_idvariation_idevent_typeis_trialis_new_customerold_mrrnew_mrrcurrency
2018-07-10123john@domainputlergrowthcreated1100USD
2018-07-12124annie@domainputlerstartercreated1100USD
2018-07-13124annie@domainputlerstarterupdated1 0029USD
2018-07-25123john@domainputlergrowthupdated00079USD
2018-08-02125mark@domainputlergrowthcreated11 00USD
2018-08-15123john@domainputlerscaleupdated0 079249USD
2018-08-22125mark@domainputlergrowthupdated00079USD
2018-09-07126annie@domain10xformulastartercreated00099USD
2018-11-12125mark@domainputlerstarterupdated007924.17USD
2018-12-10123john@domain putlerscaleheld0,0249249USD
2018-12-15123john@domainputlerscalecancelled002490USD
  • We won two more customers here – Annie and Mark.
  • Annie started with trial of Putler Starter, upgraded to paid plan the very next day.
  • Eventually, she also bought another product, the 10x Formula, at $99/m, which had no trial.
  • Mark signed up for a trial, started paying $79/m after 20 days.
  • Eventually, he downgraded to a lower plan, with annual payment ($29/m, but $290/year), pulling down the MRR to $290/12 = $24.17.

Part 2: Calculating MRR, Trials to Paid, Churn and more…

Let’s calculate different metrics as on 20th December 2018.

Finding MRR is the simplest!

You may question why to deduct old_mrr from new_mrr? And if you are not familiar with SQL queries, the SUM bit there may confuse you.

So let’s dig in. What we are doing to calculate MRR:

  • Looking up all the entries till the given date – eight entries in our example.
  • We then find the difference of new and old MRR for each entry and total those values for eight entries.
  • Since we store both new and old MRR, taking a sum of differences like this adjusts the impact of switches and expiry.
    Essentially returning the latest MRR value for each subscription, and then totalling them.

Think about this for a bit. Take a pen and paper, calculate differences and total them up.

Then calculate MRR as on different dates with that logic.

Really, take some time and think through that. Once you fully grasp this, everything else will be simple.

Done?

Ok.


How to calculate churn?

That tells you loss in MRR due to churn, and the count of subscriptions that churned.

Not to difficult right?


How to calcualte trials to paid?

Let’s look at something a bit more involved.

To calculate trials to paid conversion rate, we need total number of trials started and total number of trial to paid subscriptions.

Let’s review how we’re storing trial information.

  • We set “is_trial” to 1 if a subscription was in trial, or upgraded from trial to paid. A new trial will have its event type as ‘created’. A trial to paid conversion will have new MRR above 0, and event type as ‘updated’.
  • So our database query will be something like:
    SELECT
    SUM( CASE WHEN event_type = ‘created’ THEN 1 ELSE 0 END ) as trial_count,
    SUM( CASE WHEN event_type = ‘updated’ and new_mrr > 0 THEN 1 ELSE 0 END ) as paid_count,
    SUM(new_mrr) as conversion_mrr
    WHERE timestamp <= '2018-12-20' and is_trial = 1
  • We can now compare trial_count and paid_count to calculate percentage conversion.
  • And conversion_mrr tells us MRR expansion from trial to paid conversions.

Wow! You’ve accomplished a lot so far!

Let me quickly tell you possible ways of finding some other KPIs.

  • Switches: When event type is ‘updated’, and new MRR is more than old MRR, it’s an upgrade. Downgrade otherwise.
    Similarly, all new MRR + upgrades = expansion in MRR. All churn + downgrades = contraction in MRR.
  • Active Subscriptions: Unique Subscriptions IDs, excluding cancelled or non-converted trials.
  • Average Revenue Per Paid User: MRR divided by Active Subscriptions count. (If you want “users” and not “subscriptions”, you can pick the count of unique customers with active subscriptions.)

You get the picture!

So why in the world I’m calling this Pandora’s box???

Pandora's box

Who’s Pandora? And what’s in her box?

Pandora is a character from Greek mythology.

Prometheus stole fire from heaven, as a punishment, Zeus (the king of the gods) presented Pandora to Prometheus’ brother Epimetheus.

A jar was left in Pandora’s care, and she opened it – only to release sickness, death and many other evils to the world. She quickly closed the container, and Hope was left behind.

Today, the idiom “to open a Pandora’s box” means to do or start something that causes many great and unexpected troubles. It’s similar in meaning to “opening a can of worms”.

Calculating subscription business metrics becomes harder and harder as you try making it more and more accurate.

Metrics are a measurement of progress. People plan their future actions based on what metrics report. So it’s highly important to have correct metrics.

If your calculation shows a $12000 MRR, but you forgot to deduct cancellations from it, then that’s not going to work.

If you made any mistake in calculating the metrics, you will end up taking wrong decisions.

Alright, so we agree that accurate metrics are essential. But how does it get more and more complex??

Here’s how subscription revenue reporting gets really complex!

To tell you the truth, we avoided building Subscription reports in Putler – our e-commerce analytics solution – for a long long time. Our first few attempts failed quickly.

Finally, we built a solution that handled all complications and edge cases.

Eventually that proved to be insufficient too. Which is when we rebuilt everything again based on the approach I outlined above.

I will tell you a bit more about Putler later on, but here is a list of major problems we’ve observed building SaaS analytics / metrics solution.

  • No universally accepted method of calculating all these metrics:Different reporting solutions have different methods for calculation. So if you are comparing your data with someone else, you may see mismatches.
  • Garbage In, Garbage Out: If the log of all transactions is incomplete or inconsistent, our subscription events table will have insufficient entries. For example, if you build subscription events data from last two years’ transactions, you may miss critical events that happened before this period. Or if your payment gateway / e-commerce system is setting same date for creation and first payment – or any other inconsistencies – metrics will be wrong.
  • E-commerce systems and payment gateway APIs changes: they may change the kind of data they provide. This means two things: one, you need to constantly update your logic – which is still ok; but second is that old data may be in old format, new data in new standard. In such a case, you will need to normalize and bring everything in the same format!
  • New subscription events: Every time a new subscription event occurs, you need to check and update the table if needed. Most gateways do not indicate upgrades / downgrades. Many do not indicate trial information. So we need to smartly identify these patterns.
  • Multiple currencies: If you accept payments in different currencies, you’d need to look up exchange rates, and convert everything into a “base” currency. This can be a challenge in its own.
  • Multiple payment gateways / e-commerce systems: If you accept both Stripe and PayPal for payments, the kind of information they provide about a transaction later on, is different. For example, PayPal API does not provide subscription interval and end date. In such cases, we have to build a “fuzzy” method of detecting subscriptions and their details. Consolidating such differences across gateways and unifying data is extremely hard.
    We’ve already provisioned for product and variation level metrics. But product / plan names change all the time. For higher accuracy, we need to build a system to merge / group products.
  • Data inaccuracy e-commerce systems: When you use an e-commerce system, it may not have the most accurate data. You need to correlate with payment gateways to confirm. This process of de-duping is intensive.

Want to track your recurring revenue metrics? Here are your options…

Without big data analytics, companies are blind and deaf, wandering out onto the Web like deer on a freeway. ~ Geoffrey Moore, Author, Crossing the Chasm & Inside the Tornado Click To Tweet

That’s an apt analogy. If you’re not tracking your key performance indicators, you don’t know where you are going. (And BTW, if you have not read Crossing the Chasm, read it when you get a chance.)

Every serious business person knows the importance of tracking key measurements. And there is no shortage of analytics and reporting tools.

But first: don’t make the mistake of using an Excel (or Google!) spreadsheet to track your SaaS subscription metrics and KPIs. It won’t scale.

So what are your options?

Every e-commerce system has a built in reporting system of sorts. So does every payment gateway. You can start with them.

Even general purpose analytics solutions like Google Analytics and Mixpanel allow tracking e-commerce revenue. You can use them, but won’t get subscription KPIs we’ve been discussing – MRR / Churn etc…

Given the growth of SaaS and the recurring business model, dozens of startups have launched solutions specializing in SaaS metrics. There are plenty of options especially when you use Stripe. ChartMogul, Control, ProfitWell, Compass, Statsbot, Supermetrics… – the list goes on. Many of these solutions also work with other payment gateways.

Then there is Baremetrics – the poster boy of subscription business analytics. It’s a great product, been there for many years, and has made lots of improvements lately. And everyone else copied them.

Even we copied Baremetrics when we built subscription revenue analytics in Putler.

Yes, Putler gives you the full gamut of recurring business reporting.

Our experience of offering e-commerce and subscription business revenue analytics platform

Putler started as a simple PayPal sales tracking tool back in 2010. It stayed a desktop app for many years and gained thousands of users. We revamped the whole system and ported it to the Web in 2016.

Putler's full blown subscription dashboard

Putler is the meaningful e-commerce analytics platform, and one of the best out there.

Why?

Mainly because of our awesome customers. We built Putler with continuous feedback from customers. We’ve solved real problems for people.

Putler does what most other analytics solutions don’t.

What makes Putler the best tool for calculating Subscription metrics?
  • Putler can consolidate multiple payment gateways, e-commerce systems and Google Analytics into a comprehensive dashboard.
  • It can understand and work with multiple currencies, products and team members.
  • Putler cuts down on day to day operational chores like refunds and order lookup.
  • It’s got a full blown subscriptions dashboard. You can track all the KPIs and much more from there.
  • Not just that, Putler even has a fuzzy subscription detection system, so it works with even PayPal and other systems that do have recurring payment profiles but don’t provide all the details in their APIs.

Putler is not just about analytics. It’s about analytics to grow your business.

Sign up for Putler

So, what is the best solution for SaaS / subscription analytics and reporting?

There are many good solutions. A few popular ones as well. There are some free, some charge hefty fees.

Was it a lot more questions than you anticipated?

But I think it’s important to look at all those aspects.

What do you think?

OK, so given all those questions, which would be the best solution?

My answer is Putler. And if you tell me I’m biased, that’s correct. After all, Putler is my brain child, and something I’m really passionate about.

So if you want a solution, I’d recommend taking a trial of 3-4 products you think can work for you. One of them should be Putler.

Try them out. Then decide.

Fair?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.