How to Calculate MRR (Monthly Recurring Revenue) in SQL

Intro

Whether you’re a B2B SaaS company, an e-commerce platform, or even a subscription-based business, you most likely have a plan to generate recurring revenue.

Recurring revenue is the lifeblood and backbone of any solid business. Understanding how revenue relates to your business’s different aspects is challenging, and this is precisely why monthly recurring revenue (MRR) is such an important metric.

What is MRR?

While annual recurring revenue (ARR) focuses on providing an accurate yearly representation of your revenue, MRR is the amount of revenue you generate monthly without having to do anything. MRR enables you to track your financial health using data from your active customers.

• Net New MRR: Revenue from new customers
• Upgrade MRR: Revenue gained from upgrades
• Downgrade MRR: Revenue lost from downgrades
• Expansion MRR: Revenue gained from expansions
• Churn MRR: Revenue lost from churned customers
• Reactivation MRR: Revenue gained from previously churned customers

Ultimately there are many different types of MRR, and it’s important to understand them so you can make data-informed decisions.

How to Calculate MRR?

The formula for calculating MRR is relatively straightforward. However, before you can calculate your MRR, you first need to calculate your average revenue per user (ARPU), which is done by dividing your total revenue by your total number of users.

To calculate your MRR, you simply take your total users and multiply that number by your ARPU. For example, if you have 500 active users and your ARPU is $20, you’re MRR would be $10,000. If you base your business model on contracts, you can divide your ARR by 12 to calculate your MRR.

Using this formula, you can calculate your total MRR organization, but you can also go more granular on an individual account/customer basis and calculate MRR for net new business, upgrades/downgrades, expansions, churn, and even reactivation. The formulas for most of these will be relatively similar, and all you need to do is change the numbers as you leverage different subsets of your data.

If you know your net new MRR, you can calculate how much your revenue is growing/shrinking monthly. To calculate this number, you need to add up all your new MRR plus any expansions and upgrades and subtract that total by your churned MRR. Doing this monthly will give you an accurate assessment of your revenue trend.

To calculate your upgrade/downgrade MRR, you must add up all upgrades and subtract the original cost. If a customer were paying $8 per month but then upgrades to your premium subscription for $12, your upgrade MRR would be $4. If your customer downgrades from a $12 subscription to a $5 subscription, your downgrade MRR would be $7.

Additionally, for expansion MRR you need to add up any new MRR gained during the month. You can also calculate your expansion MRR growth rate by taking your newly acquired expansion MRR, dividing that total by your MRR at the beginning of the month, and multiplying that number by 100. If your MRR was $400k at the beginning of the month, you gained an additional $50k of revenue your growth rate would be 12.5%.

Why Does MRR Matter?

MRR is extremely important because it allows you to measure your business’s monthly performance. When you deeply understand your MRR, you can identify exactly what actions positively or negatively affect your revenue and optimize around your various business teams (e.g., sales, marketing, support, etc.)

Visibility into your key MRR metrics enables you to draw a direct correlation between your customer spend data and your existing accounts. For example, maybe customers who churned in the previous month did so because of a bug in your app, or perhaps a new feature/product you launched caused one of your enterprise customers to double their contract size. Maybe all of your net new revenue in the past month can be attributed to a targeted advertising campaign.

Knowing your MRR gives real-time visibility into your revenue so you can be proactive rather than reactive. In addition to this, MRR metrics give you the ability to measure your ROI. Perhaps a new feature launched caused 25% of your churned customers to reactivate their account. Without MRR, tracking your ROI and performing this type of analysis is impossible.

How to Grow Your MRR

By establishing MRR as a core metric for your various business teams, you can optimize your processes to grow your MRR and improve your overall efficiency. Giving your support team access to MRR data means they can build product features that reduce churn, improve onboarding, and encourage customer loyalty.

When MRR is available to your sales team, they can identify expansion opportunities and accounts at risk of churning before it’s too late. Providing your sales teams with MRR also ensures that they are prioritizing the highest value and leads to more opportunities in your sales pipeline.

MRR also has huge implications for your marketing teams because they can use that data to target lookalike audiences in your ad platforms (e.g., Google and Facebook). They can also enroll specific accounts in nurture campaigns to increase orders or encourage product usage.

There are a near limitless number of use cases you can achieve with MRR, and most likely, you’ve already thought of some for your own business. The key takeaway lies in the net revenue retention rate (NRR). NRR is a solid indicator of how sticky your product or service is because it’s the percentage of revenue you retained from your existing customers over a set time period.

You can calculate your NRR by adding your MRR plus any expansions and upgrades and subtracting your churn/downgrades from that total. Your goal with NRR should be to get to 110%. Anything over 100% means your revenue will continue to grow even if you never bring on another customer. Increasing your NRR will directly increase your MRR.

Calculating MRR Using SQL

While you could spend hours calculating your MRR in Google Sheets or Excel, leveraging the customer data in your warehouse and building a simple SQL model is much easier. Here’s an example query showcasing how you can calculate MRR.

Most of your transactional billing data lives in a finance tool like Netsuite or Stripe. The problem is none of this data is actionable because it’s only accessible to your finance team. The good news is that most of your customer data already lives in your data warehouse, which means you can calculate your MRR using simple SQL.

The very first step is to ETL (extract, transform, and load) data from your billing tool (ex; Stripe) into your warehouse.

It’s likely that your Billing tool has objects such as Subscriptions, Plans, and Products/Items, where a customer can have a subscription to a plan in order to receive access to a certain combination of Products.

Next, you need to combine these different types of Objects into a single table.

Here’s how we do that in Stripe by joining Subscriptions, Plans, and Products/Items. These tables all have a common Organization or Customer ID that we can use to determine which customer or organization is tied to a given plan.

SELECT 
  * 
from 
  subscription 
  left join subscription_item on subscription.id = subscription_item.subscription_id 
  left join plan on subscription_item.plan_id = plan.id 
  left join product on plan.product_id = product.id

Your Plan or Subscription object might already have an MRR field for you to use directly, unless your business relies on Invoices. If so, you can run a sum on Invoices to get the MRR. Here’s an example CTE on how to grab that:

With stripe_invoice_mrr_by_date as (
  select 
    'invoice_id', 
    customer_id, 
    organization_id, 
    subscription_id, 
    invoice_id, 
    day, 
    greatest(
      sum(mrr), 
      0
    ) as mrr 
  From 
    < table_name >
)

Then, we group the MRR column by each organization (with a group_by), filtering out customers with an MRR value of zero.

select 
  'Stripe' as source, 
  organization_id, 
  day, 
  MRR, 
  0 as potential_MRR 
from 
  stripe_invoice_MRR_by_date 
where 
  MRR is distinct 
from 
  0

How to Implement MRR

Staring at your MRR in a dashboard is helpful, but it’s not actionable. Once you’ve calculated your MRR metrics in your warehouse, the next order of business is simply to sync that data to your downstream business tools. Unfortunately, this often means uploading manual CSV files, integrating with third-party APIs, or writing custom scripts.

As a Data Activation platform powered by Reverse ETL, Hightouch solves all these problems. With Hightouch, you can sync data directly from your data warehouse to 200+ destinations using your existing data models or standard SQL. You schedule your syncs to run automatically every time the data updates in your warehouse. You can create a Hightouch Workspace for free today to get started!