Posted on

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

Calculating Subscription metrics like MRR, churn, LTV is a task for SAAS businesses. Here's the exact calculation for these metrics & a tool that calculates it accurately.

subscription metrics

Last updated on January 17, 2023

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 these subscription 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 intervals, 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 upgrades 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 the 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 the 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 metric, 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, Canceled, 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 cash flow change. If a refund cancels a subscription, that will be counted as churn.
  • Cashflow: Cash is sanity, others are vanity. Keeping an eye on cash flow 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 transaction information in MySQL tables – 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.


timestamp subs_id email product_id variation_id event_type is_trial is_new_customer old_mrr new_mrr currency
2018-07-10 123 john@domain putler growth created 1 1 0 0 USD
2018-08-15 123 john@domain putler scale updated 0 0 79 249 USD
2018-12-10 123 john@domain putler scale held 0 0 249 249 USD
2018-12-15 123 john@domain putler scale canceled 0 0 249 0 USD

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 canceled 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.

timestamp subs_id email product_id variation_id event_type is_trial is_new_customer old_mrr new_mrr currency
2018-07-10 123 john@domain putler growth created 1 1 0 0 USD
2018-07-12 124 annie@domain putler starter created 1 1 0 0 USD
2018-07-13 124 annie@domain putler starter updated 1 0 0 29 USD
2018-07-25 123 john@domain putler growth updated 0 0 0 79 USD
2018-08-02 125 mark@domain putler growth created 1 1 0 0 USD
2018-08-15 123 john@domain putler scale updated 0 0 79 249 USD
2018-08-22 125 mark@domain putler growth updated 0 0 0 79 USD
2018-09-07 126 annie@domain 10x formula starter created 0 0 0 99 USD
2018-11-12 125 mark@domain putler starter updated 0 0 79 24.17 USD
2018-12-10 123 john@domain putler scale held 0, 0 249 249 USD
2018-12-15 123 john@domain putler scale canceled 0 0 249 0 USD
  • 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.

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.



How to calculate churn?

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

Not too difficult right?

How to calculate trials to be paid?

Let’s look at something a bit more involved.

Wow! You’ve accomplished a lot so far!

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

  • Switches: When the event type is ‘updated’, and the new MRR is more than the 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 canceled 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
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 eCommerce 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 solutions.

  • 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 the same date for creation and first payment – or any other inconsistencies – metrics will be wrong.
  • E-commerce systems and payment gateway APIs change: 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 on 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 intervals 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 deduping is intensive.

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

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.

Still confused? To make things simpler, here’s an article that compares different subscriptions billing software.

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.

Look at Putler’s Subscription Dashboard

Putler's full blown subscription dashboard
Putler’s full blown subscription dashboard

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


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.

Here’s how Putler compares with competitors

Features Putler ChartMogul Baremetrics Metorik GetControl
(Out of business)
(Out of business)
SaaS Metrics
Non SaaS Metrics
Website metrics
Number of Integrations 17 7 4 4
Integrates with PayPal
Team sharing available
Realtime updates
Multi-currency support
Aggregated reports
Individual reports
Customer Segmentation (RFM)
Send money functionality
Subscription Management
Process refunds
Desktop App
Chrome extension
Intuitive Search
Pricing $20 $100 $50 $50

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.

Here are some questions you can ask to discover the best solution for your needs.

  • Does it work only with Stripe? Or a specific gateway or e-commerce system? If so, this could limit you in the future.
    Even if you are using the gateway the system is built for, will it work in your case? For instance, some solutions need plans / products defined at Stripe / gateway level. If you use an e-commerce system – like WooCommerce – and use Stripe only for payments, most of the solutions will not work.
  • Can the solution handle non-recurring payments? Even for SaaS, not every dollar is recurring. You need something that can handle everything.
  • Does the platform have a ready integration with your payment / e-commerce system? Everyone has an API, but using an API to populate your data can be a big task.
  • Does it give you most (if not all) metrics you want to track? Can it pull data from other systems – like Google Analytics – to give you a better understanding of your business?
  • How does the system handle complexities we outlined earlier? Changes in system, plan switches, refunds, multiple currencies etc?
  • Do you have multiple payment gateways / businesses / websites? If so, can the solution accurately consolidate them all in one place?
  • Can you give restricted access to your team members? To the marketing or customer support team?
  • Is it just a reporting tool or goes beyond? Does it enrich customer profiles? Can it email reports? Can it handle dunning / failed charges?
  • What’s the price? Even if it’s free, how much time and effort will you have to spend to get it to work? How are premium upsells offered?
  • Is it easy to use? Do you get information you need without a lot of jumping here and there?
  • Will the platform survive? Or will it get lost over the next few years?

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?

Try them out. Then decide.


Additional resources

One thought on “Subscription metrics analytics: How to calculate MRR, churn rate, ARPPU and more

  1. Very impressive article!! SaaS metrics are used to calculate MRR which will help to understand the health of the company, set goals for the future. Along with this most of the organizations are using CRM software or adopting the AI tools for evaluating and improving the business performance. There are some AI-powered tools such as CSAT.AI, MaestroQA, ScorebuddyQA, and Salesforce Einstein are well known in the current market trend.

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.