Monthly Recurring Revenue (MRR) is our primary metric at Sequin. This keeps us all focused on what matters.
I went on quite the journey to calculate MRR for our real-time dashboard. Several times it appeared my query was on the money, only to find later in the day it would diverge from Stripe's calculation. How was this happening?
I've since honed in on an MRR query that is accurate and aligns to Stripe's own calculation of MRR:
with active_subscription_items as (
select *
from stripe.subscription_item si
where si.created = (
select MAX(si2.created)
from stripe.subscription_item si2
where si.subscription_id = si2.subscription_id
)
)
select
sum((stripe.price.unit_amount/100.00) * active_subscription_items.quantity)::money
from stripe.subscription
left join active_subscription_items
on active_subscription_items.subscription_id = stripe.subscription.id
left join stripe.price
on active_subscription_items.price_id = stripe.price.id
where stripe.subscription.status != 'canceled';
I'll explain how this query works to accurately calculate MRR so you can build upon it as you need.
Calculating MRR with Stripe
Monthly Recurring Revenue (MRR) is the amount of revenue you can expect to receive on a monthly basis. It's a very popular metric for subscription businesses.
Stripe makes setting up a subscription business easy. And in your Stripe dashboard, you'll actually see an MRR metric from day one. Then, you'll look to slice and dice this metric or pull it into your own dashboard. This is where things get tricky.
Get your data
The first step is to pull your Stripe data into a model that is easy to query. There are a couple options to consider.
Stripe has a great product called Stripe Sigma built in. Stripe Sigma allows you to build custom queries using SQL and comes with some very helpful report templates (except no template for MRR!). Note that Stripe Sigma data will always have a 24-hour lag. Additionally, you can't access the underlying database Stripe Sigma runs on, so funneling your metrics to a dashboard is not possible.
We wanted real-time data, in our Postgres Database, with no scheduling overhead. So ultimately, we added Stripe support to our product, Sequin. It replicates your stripe data to your Postgres database in real-time. It not only solves our analytics needs, but also is at the core of our integration with Stripe.
Note: While we're using Sequin's Stripe Schema from here on out, with minor edits these queries should still work with Stripe Sigma and the data sets generated by other services.
The naive approach
Now, to the MRR query.
Stripe explains in their documentation that, "You can calculate Monthly Recurring Revenue (MRR) by summing the monthly-normalized amounts of all active subscriptions at that time.1"
Seems simple enough. I'll just pull down all my active subscriptions and the amount of revenue from the most recently paid invoice. Here was my initial query:
select
sum(stripe.invoice.amount_paid/100.00)::money
from stripe.subscription
left join stripe.invoice
on stripe.subscription.latest_invoice_id = stripe.invoice.id
where stripe.subscription.status != 'canceled'
This query finds the latest invoice for any active subscription and then sums up the amount_paid on that invoice. Nothing fancy.
But not so fast.
If an invoice includes any non-subscription items or represents just an incremental portion of the subscription for the customer, the data will be messy. In the first instance, I might unintentionally count one-off onboarding or servicing fees as recurring. In the second instance, I'll under-value the full recurring value of a subscription by only incorporating a fraction of the full subscription in my MRR. Neither are accurate.
The Stripe approach
To fix this issue, I realized I needed to go one step beyond the invoice and consider all the current subscription items associated to the most recent period of the subscription. This ensures that I'll capture all the recurring revenue tied to subscriptions:
with active_subscription_items as (
select *
from stripe.subscription_item si
where si.created = (
select MAX(si2.created)
from stripe.subscription_item si2
where si.subscription_id = si2.subscription_id
)
)
select
sum((stripe.price.unit_amount/100.00) * active_subscription_items.quantity)::money
from stripe.subscription
left join active_subscription_items
on active_subscription_items.subscription_id = stripe.subscription.id
left join stripe.price
on active_subscription_items.price_id = stripe.price.id
where stripe.subscription.status != 'canceled';
This query returns an accurate MRR that matches Stripes MRR to the cent.
The challenge this query overcomes is finding just the most recent subscription_items associated to any given subscription. Hence the with active_subscription_items as variable. Then, I filter those subscription items down to just the ones that are associated to active subscriptions and calculate the recurring revenue by multiplying the price of the subscription item to the quantity the customer has subscribed to.
By using subscription_items I'm calculating MRR based on the most up to date, projected revenue on any subscription. It is a "forward-looking" metric. That is, as soon as my customer's subscription is updated to reflect a new item (say they added a couple more seats to their plan), my MRR will update even if the customer won't be billed for that MRR until the next billing cycle.
This is the MRR metric we put on our dashboard. It's the metric we talk about every day in standup and consider when we're prioritizing initiatives. As Paul Graham advises, we find that orienting our company around MRR growth makes it easier to align as a team and focus on what really creates growth.
Backward-looking MRR
We now have an accurate MRR metric to orient our team as we move forward. But often we'll want to pull reports that look backward. How should we calculate MRR in this instance?
When looking back, it's instructive to adjust the MRR calculation to look at what revenue actually came in through paid invoices as opposed to the listed price of the subscription.
So when assessing last month's MRR, I'll use the following query:
select
sum(stripe.line_item.amount/100.00)::money
from stripe.subscription
left join stripe.line_item
on stripe.subscription.id = stripe.line_item.subscription_id
left join stripe.invoice_line_item_map
on stripe.invoice_line_item_map.line_item_id = stripe.line_item.id
left join stripe.invoice
on stripe.invoice_line_item_map.invoice_id = stripe.invoice.id
where
stripe.subscription.status != 'canceled' and
stripe.line_item.type = 'subscription' and
stripe.line_item.period_start = stripe.subscription.current_period_start and
stripe.line_item.period_end = stripe.subscription.current_period_end and
stripe.invoice.paid;
As opposed to tallying subscription items, this query calculates MRR by summing up the receivables from subscription line items on paid invoices. In short, this is the money we actually made from subscriptions.
Next steps
From here, you'll likely need to tailor these queries to fit the nuances of your business. You might need to handle different currencies or consider discounts, taxes, or trials. Luckily, these queries give you a strong foundation to work from.