How to measure MRR from your data?

Imagine you are the only data analyst at your company, sipping your favorite coffee, and your phone buzzes with a notification—” You just earned $5000 in subscriptions this month!” That's the beauty of Monthly Recurring Revenue (MRR) for subscription-based SaaS companies.

MRR isn't just a number; it's the lifeblood of your business, showing how well you're attracting and retaining customers. In this post, we'll explore Monthly Recurring Revenue (MRR), why it's crucial for data-driven decision-making, and how to segment it to gain insights into where your customers come from to scale your products or services effectively.

What are MRR and ARR in business?

Monthly Recurring Revenue (MRR) is like the heartbeat of subscription-based SaaS businesses. It tells you how much predictable cash flow you can expect monthly from your active customers.

Think of MRR as your monthly paycheck from subscriptions. It includes all your customers' recurring fees but excludes one-time charges or random income. This metric is crucial for forecasting future earnings and making intelligent business decisions for revenue growth.

On the other hand, Annual Recurring Revenue (ARR) normalizes the recurring revenue over a year. It summarizes all the predictable revenue you expect from customers annually, providing a broader view of your business's performance.

Measuring is essential, but it is more important to understand which customer segments contribute the most to the company's MRR.

Why is it important to segment your MRR?

Segmenting monthly recurring revenue (MRR) is essential to better understanding your customers. By breaking MRR into different segments, you can spot trends and see which groups are more valuable or likely to churn. This data helps you fine-tune your marketing, sales, and other business aspects.

It also helps you find growth opportunities. If you notice that expansion MRR is higher in a particular region or among specific customer types, you can focus your analysis there. Knowing which features different segments respond to can guide your product development, ensuring you build what customers want.

Segmenting MRR gives you more precise insights into revenue stability and growth. It helps you forecast and plan better. In short, it's all about using data to understand your customers and drive consistent revenue growth!

Why Is MRR calculation and segmentation so complex?

Now that we know how important segmenting our data is, let's explore why calculating it can be pretty complicated.

For example, let's say you have 15 monthly subscriptions at $120 each and five annual subscriptions at $1,200. While adding these up for MRR seems straightforward, things quickly get tricky when you factor in discounts, promotions, upgrades, cancellations, or annual payments.

Imagine when one customer gets a $10 discount; you'll need to adjust your calculations to reflect that. At the same time, two customers paying $40 each cancel their subscriptions—that's $80 in lost revenue (Churned MRR). Meanwhile, another customer upgraded from a Basic plan ($30) to a Pro plan ($50), which added $20 to the Expansion MRR. You need to track these changes carefully to ensure your MRR accurately reflects the current state of your revenue.

Additionally, consider annual payments. If a customer pays $2,400 upfront for a year, you can't just count that as MRR simultaneously. Instead, you should amortize it yearly, treating it as $200 monthly.

I only mentioned a few customers from your subscriber base, but imagine if this were the case with 1,000 paying customers… Consider how that complexity multiplies when you start segmenting different countries or customer attributes.

What tools can help to segment your MRR data?

We understand how tricky calculating MRR can be; let me show you some tools to help you manage this data efficiently. Since MRR data constantly changes from place to place, having the right tools is critical to keeping everything organized.

To address these challenges, consider the following three options:

SQL writing

I know it's not a tool, but it's worth mentioning as an option for segmenting your data. Writing SQL queries gives you a lot of flexibility and lets you dive deep into the data in your warehouse. But let's be honest—it can be time-consuming and requires severe SQL skills, which aren't always easy. Plus, keeping those complex queries updated can become a headache, eating up your time when you could be focusing on actual analysis.

Exporting MRR with subscription analytics tools

If you've ever used ChartMogul or Baremetrics, you know they have advantages such as comprehensive analytics, automated reporting, and great visual boards. Moreover, they can deal with custom deals, taxes, and discounts with nice visualizations.

However, connecting MRR with product or marketing data is essential for understanding user behavior. That's when the problems arise. For example, if you want to determine how much monthly recurring revenue (MRR) is generated by users from France who signed up and paid for their subscriptions. In that case, you first need to extract these relevant customer data. This data usually comes from your product or marketing analytics tool, as it can not be correlated with your revenue data. Once you have that data, export it into ChartMogul and Baremetrics to generate custom reports and deals.

Therefore, pulling data from multiple sources, such as product analytics tools, can lead to discrepancies and is time-consuming, significantly as the complexity of your analysis increases.

In addition, subscription analytics tools can become really expensive as your company grows, which can be frustrating when you're just trying to get clear insights. They charge you based on your current MRR, which means if you are scaling fast, you get the same reports and charts, but you can be charged 10 times more.

Warehouse-native revenue analytics

While we've solved the time-consuming issue with third-party tools, there are still recurring challenges: analyzing complex data and effectively connecting your data with associated costs. So what can be the solution?

Warehouse-native revenue analytics tools connect directly to your data warehouse, eliminating the hassle of data duplication and managing different analytics tools. This integration ensures that your product, marketing, and revenue data are in one place, providing a comprehensive view of your business.

With this unified data, you can quickly identify your ideal customer profile (ICP) by segmenting customers who contribute most to your Monthly Recurring Revenue (MRR). However, segmentation can become complex when comparing user properties like country or plan interval. But not with warehouse-native product analytics. Once you select your segment, integrating their data into the MRR model becomes straightforward, allowing you to see which customers generate the most revenue. This capability aids in better forecasting and planning for business growth. Plus, with real-time data processing, you can access quick insights into your MRR without any delays.

These tools are more cost-effective because you won't pay extra expenses for data replication, even as your company grows. You'll maintain the same pricing structure, only paying for additional seats within your company profile.

Moreover, you don't need to write complex SQL queries or copy data from an external warehouse. It automatically generates SQL queries on top of your tables. It requires no data modeling and can work on raw datasets.

Below is the SQL code a warehouse native revenue analytics application generates automatically from your data. This code is generated based on the “invoice_payment” table that often occurs in any SaaS business data warehouse. This table can easily be ingested into your data warehouse using Stripe, Paddle, or a similar payment provider solution. A warehouse-native analytics solution can connect to this table and automatically index the columns. After indexing the table, the tool provides a simple interface to measure your MRR.

‍

WITH `anon_2` AS
  (SELECT DATETIME '2022-01-01 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-01-08 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-01-15 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-01-22 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-01-29 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-02-05 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-02-12 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-02-19 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-02-26 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-03-05 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-03-12 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-03-19 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-03-26 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-04-02 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-04-09 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-04-16 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-04-23 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-04-30 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-05-07 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-05-14 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-05-21 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-05-28 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-06-04 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-06-11 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-06-18 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-06-25 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-07-02 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-07-09 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-07-16 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-07-23 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-07-30 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-08-06 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-08-13 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-08-20 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-08-27 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-09-03 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-09-10 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-09-17 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-09-24 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-10-01 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-10-08 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-10-15 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-10-22 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-10-29 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-11-05 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-11-12 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-11-19 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-11-26 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-12-03 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-12-10 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-12-17 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-12-24 00:00:00' AS `_rev_dt`
   UNION ALL SELECT DATETIME '2022-12-31 00:00:00' AS `_rev_dt`),
     `anon_5` AS
  (SELECT t1.user_id AS `_cte_user_id`,
          datetime(t1.event_time) AS `_cte_datetime`,
          CAST(NULL AS STRING) AS `_cte_group`,
          NULL AS `_cte_event_property`,
          datetime(t1.period_start) AS `_cte_rev_period_start`,
          datetime(t1.period_end) AS `_cte_rev_period_end`,
          t1.amount_paid * 1.0 AS `_cte_rev_amount`
   FROM `sample_saas`.`invoice_paid_events` AS `t1`
   WHERE TRUE),
     `anon_4` AS
  (SELECT `anon_5`.`_cte_user_id` AS `_cte_user_id`,
          `anon_5`.`_cte_datetime` AS `_cte_datetime`,
          `anon_5`.`_cte_group` AS `_cte_group`,
          `anon_5`.`_cte_event_property` AS `_cte_event_property`,
          `anon_5`.`_cte_rev_period_start` AS `_cte_rev_period_start`,
          `anon_5`.`_cte_rev_period_end` AS `_cte_rev_period_end`,
          `anon_5`.`_cte_rev_amount` / greatest(1, date_diff(`anon_5`.`_cte_rev_period_end`, `anon_5`.`_cte_rev_period_start`, MONTH)) AS `_cte_rev_amount`
   FROM `anon_5`
   WHERE `anon_5`.`_cte_rev_period_start` <= DATETIME '2023-01-01 00:00:00'
     AND `anon_5`.`_cte_rev_period_end` >= DATETIME '2022-01-01 00:00:00'),
     `anon_1` AS
  (SELECT `anon_2`.`_rev_dt` AS `_cte_datetime`,
          coalesce(`anon_3`.`_cte_rev_amount`, 0) AS `_cte_rev_amount`,
          `anon_3`.`_cte_user_id` AS `_cte_user_id`,
          `anon_3`.`_cte_group` AS `_cte_group`,
          `anon_3`.`_cte_event_property` AS `_cte_event_property`,
          `anon_3`.`_cte_rev_period_start` AS `_cte_rev_period_start`,
          `anon_3`.`_cte_rev_period_end` AS `_cte_rev_period_end`
   FROM `anon_2`
   LEFT OUTER JOIN `anon_4` AS anon_3 ON `anon_2`.`_rev_dt` >= `anon_3`.`_cte_rev_period_start`
   AND `anon_2`.`_rev_dt` <= `anon_3`.`_cte_rev_period_end`)
SELECT TIMESTAMP_TRUNC(`anon_1`.`_cte_datetime`, ISOWEEK) AS `_datetime`,
       CAST(NULL AS STRING) AS `_group`,
       sum(`anon_1`.`_cte_rev_amount`) AS `_agg_value`,
       0 AS `_event_index`
FROM `anon_1`
GROUP BY 1,
         2

‍‍


Conclusion

Managing Monthly Recurring Revenue (MRR) is crucial for any subscription-based SaaS business. A warehouse-native tool like Mitzu makes tracking and analyzing MRR quick because it connects directly to your existing data warehouse, so you always have the most accurate numbers. You can segment your customers as complicatedly as you want and get the reports quickly without further tools.

Mitzu provides real-time insights, letting you quickly adapt to changes in customer behavior or market trends. Using Mitzu helps your team make smarter decisions that increase MRR growth and keep revenue flowing!

‍

‍