Monthly Recurring Revenue more commonly known as MRR is a business metric most widely used in subscription businesses where a portion of your revenue stream is predictable and recurring such as Software as a Service (SaaS). It's one of the most if not the most important metrics in any subscription business.
In this article, we will elaborate on the previous article on how to calculate MRR with a concrete example using SQL and data from Stripe. Even though this article is centered around Stripe you can also get a lot of inspiration if you are using other payment processors software like GoCardless, Adyen, or Square or billing software like Xero, Zoho, or Freshbooks.
This guide assumes that you already have your Stripe ready to analyze in your data warehouse. If not check out this article to learn more about how to extract data from Stripe.
Now follow this step-by-step guide using Weld to create an MRR model using Stripe data with SQL.
Step 1: Get all subscriptions
To begin, it is essential to define MRR from a Stripe perspective with a clear formula. MRR is calculated using this equation:
MRR = sum of recurring revenue from active subscriptions
In other words, we need to find all the active subscriptions and then calculate the total revenue on a monthly basis. Since subscriptions can change over time, let's use this query to get all subscriptions from Stripe.
Tip: Replace or adjust the table names based on your setup.
Step 2: Get subscription plans
The next step is to join the subscription plans which hold information about what plan each subscriber is on. To do this you also need to left join the subscription_item table which holds the price_id. Then you can use the price_id to join the plan as shown below. We need to divide by 100 because the Stripe API returns two additional zeros for all amounts.
Tip: It is important to set up a new subscription whenever changes occur, in order to capture historical data. Otherwise, your model won't be functional. Additionally, you can ensure that your data warehouse's tables incorporate Slowly Changing Dimensions (SCD), so as to store past data. Make certain to use the right filtering methods to prevent any double counting of subscriptions.
Moreover, we join in the coupon and subscription_discount tables in order to calculate any discounts applied to the subscription. You will also note that we do a case when statement to assess whether the start_date equals cancelled_at to avoid duplicates from new subscriptions created but canceled in the same month. Ideally, any subscription is set up correctly from the beginning but mistakes happen.
Step 3: Normalize to monthly revenue
Now let's normalize revenue to MRR and apply currency conversion. To get the monthly amount we need to divide it by the interval_count and ad interval_period. After that, we use a currency conversion table available in the Weld app as a Forex connector. You can use your own here if needed. We average the rate and group by month to join it on our main table which holds the currency for the amount. Finally, we divide by the rate.
Step 4: Identify active subscriptions and sum revenue
Now we will sum the total revenue per customer per subscription and subtract any discount applied. Additionally, we select active and canceled subscriptions separately in conjunction with a coalesce function to assess whether there is an active subscription on a given customer. We also use this to get the latest cancellation date to calculate churn in case no active subscriptions are present anymore.
Tip: It's important to note that discounts are applied on a subscription level which means that you need to make sure that you only subtract it once per subscription. We use the max function to avoid this as we joined the discount on a subscription_item level.
Step 5: Create a timeseries for each customer
To make sure we have data for all months of a given customer's lifetime we create an array with dates starting from 2021-01-01. Apply your own timeframe here as needed.
The idea here is to calculate the correct start and end dates for each customer and then calculate any changes between months in the subscription. If it's the first month we know that it's a new MRR and if it's the last month with an active subscription we know it's a churn and any changes in between will be contraction and expansion relatively. We use the row_number function to rank rows to validate the first and last month. We also remove any months not relevant before any active subscription was present or after the last churn date.
Step 6: Summarize MRR
Finally you are ready to sum it all up and divide MRR into the different types and calculate net new NRR. Additionally, we are adding a total to also show the total MRR over time.
Thats it. Your model is ready and you can see the full lineage graph of the model below:
Additionally, you can visualize your MRR breakdown like the chart below:
Tip: If you want to slice and dice your data based on country, industry, company size or specific companies add those columns to your model from Step 2.
Conclusion:
Depending on your Stripe and data infrastructure set up the model that is needed can change but this should give you an idea of how it can be done.
.png)
