MRR Retention Rate
In subscription businesses, MRR retention rate is a critical indicator that shows the health of your customer base.
This metric shows how much of the MRR (Monthly Recurring Revenue) from existing customers is being maintained, and is used to track revenue decreases due to cancellations and downgrades. It’s especially essential for SaaS business managers, customer success teams, and finance department leaders when evaluating business sustainability.
Particularly in the SaaS industry, where customer acquisition costs are high, maintaining existing customers significantly impacts profitability, making continuous monitoring essential.
The MRR retention rate can be calculated by taking the MRR obtained from existing customers, subtracting the expansion revenue amount, and dividing by the previous month’s MRR, as shown below:
\[\begin{aligned} \text{MRR Retention Rate} = \frac{\text{MRR from existing customers} - \text{Expansion}}{\text{Previous month's MRR}} \end{aligned}\]How to Calculate MRR Retention Rate
Here, I’ll introduce how to calculate the MRR retention rate using actual data.
In this example, we’ll use payment data from a subscription business where each row represents one payment transaction, including Customer_ID, Payment_Date, Payment _Amount, and other information. (You can download the data from this page)
In Exploratory, there are two methods for creating metrics:
- AI Prompt: Create metrics using natural language processing
- UI Menu: Process data and create metrics using menus accessible from the UI
This note will introduce both methods.
“AI Prompt” is only available for users with paid licenses such as Business Plan or Personal Plan, or users who are currently trialing these plans.
Also, AI Prompt is only available when your device is connected to the internet.
If you are not using the above plans or your device is not connected to the internet, please proceed to the “Calculate MRR Retention Rate with UI” section.
Calculate MRR Retention Rate with AI Prompt
This section introduces how to create metrics using AI Prompt. (For details about AI Prompt, please see here.)
Click the “AI Wrangling” button, and when the AI Prompt dialog appears, enter text like the following and execute:
Calculate MRR retention rate by month
Code to calculate the MRR retention rate will be generated. Check the results and click the “Run as Step” button.
The step will be added, and the MRR retention rate will be calculated.
Calculate MRR Retention Rate with UI
Here I’ll explain how to calculate the MRR retention rate using the UI.
With payment data like we have, the MRR retention rate can be calculated with the following steps:
- Calculate the number of payments from each customer
- Calculate changes in payment amounts for each customer to identify upsells and downgrades
- Aggregate MRR, MRR from existing customers, and expansion (increased payment amounts) by month
- Calculate the MRR retention rate
Preparation
We’ll create a table like the one below to calculate the MRR retention rate:
To create this table, we’ll start with payment data that already has payment count and expansion/contraction information added, so we’ll begin the explanation from Step 3. (You can check how to calculate payment count here and how to calculate expansion and contraction here)
Since we want to aggregate MRR, MRR from existing customers, and expansion, select “Aggregate (Summarize)” from the “Payment_Date” column header.
When the summarize dialog opens, select “Payment_Date” for the group and “Month” for the rounding process.
MRR (Monthly Recurring Revenue) is the monthly total of the amounts paid by each user, so select “Payment_Amount” for the value and SUM for the aggregation function.
Then change the column name to MRR.
Next, we’ll aggregate MRR from existing customers.
MRR from existing customers is the MRR obtained from customers who have made payments two or more times, so we’ll aggregate the sum of payment amounts from customers who have made payments two or more times.
Add “Payment_Amount” to the value and select “Conditional Sum (SUM_IF)” for the aggregation function.
A dialog to specify conditions will appear. Enter “Payment_Count” for the column, “Greater Than or Equal to” for the operator, and “2” for the value.
Then set the column name to “MRR from Retained Customers”.
Now we’ve aggregated MRR from existing customers. Finally, select “Expansion” for the value, select SUM for the aggregation function, and click the “Run” button.
We’re now ready to calculate the MRR retention rate.
Calculating MRR Retention Rate
From the “MRR from Existing Customers” column header menu, select “Create Calculation”, “Standard”.
When the create calculation dialog appears, enter
((`MRR from Retained Customers` - Expansion) / lag(MRR) in
the calculation editor.
Note that the lag function retrieves the value from the previous row
of the specified column, so lag(MRR) calculates the value
from the previous month, which is the previous month’s MRR.
Finally, make sure “Create New column” is checked, set the column name to “MRR Retention Rate”, select “Last column” for “After this column”, and click the “Run” button.
Now we’ve calculated the “MRR Retention Rate”.
