If you want to get rid of the every-month fiddles with metrics spreadsheets, here’s the first step - automatically calculating monthly recurring revenue from the payment gateway entries.
MRR is the number one metric for any SaaS business, and many other metrics are based on it.
There are quite a lot of steps, I’m sorry for the long post.
Real or theoretical plan price?
MRR is often calculated using the theoretical plan price. So if Sally has a $5 discount on her $25 per month plan, her theoretical MRR would still be $25.
But when you have the real entries from Stripe API, you’ll probably want to calculate MRR with real amounts instead. So if Sally pays $20 per month, $20 is the recurring revenue from Sally, not $25. This way the MRR will show the actual amount you’d really earn next month if none of the current customers would churn, upgrade or downgrade.
If you also calculate the discounts & refunds separately, you’ll see the MRR you lost due to them.
Don’t make this common mistake!
Monthly amount billed from subscriptions is not your MRR. Not even if you’d have just monthly subscriptions.
Due to changes in refunds, discounts and delays in payments the MRR figures don’t normally match the amount billed.
Calculating SaaS metrics isn’t accounting. This said, let’s get started.
Finding out what was charged
First, you’ll need to fetch BalanceTransactions of type charge and refund.
The amount field will contain the amount paid. We’ll include Stripe fees in the MRR. They are just an expense like any other expenses.
BalanceTransactions do not include everything as Stripe keeps internal customer balance, but let’s handle that later.
Next, we’ll need to find the plan that should carry the MRR. Even though we are calculating just the total MRR here, we’ll need to know the plan length so that we can normalize the MRR for annual subscriptions.
How to find the plan
The BalanceTransaction entry has a source field that contains the Charge id. The Charge has an invoice field that contains the Invoice id. The Invoice contains invoice lines, which may contain a “subscription” type entry. And those subscription type line items contain the plan information.
The invoices also contain “invoiceitem” type of entries, which contain anything else that’s invoiced on top of the monthly charge. These invoice item entries should not be included in the MRR, unless they really are recurring.
For example, if you are charging extra for customer’s disk space usage, and he can’t easily cut it down, include invoice items. But if you charge extra for every report that he prints out and he prints them whenever he wants, don’t include invoice items to MRR.
How to find the amount
The invoice lines contain the theoretical plan price. Invoice’s total field contains the discounted total that we want to use. Unfortunately, you don’t get discounted line amounts. Fortunately the discounts can’t be targeted to single lines either, so you can just split the total amongst the invoice lines and include the lines belonging to MRR.
If you don’t charge any invoiceitems, the invoice will contain just the subscription line item and you can pick up the total as it is.
Normalizing the MRR
If the invoice you are handling is to a monthly plan, just include the MRR found in the last step.
But if the invoice is to an annual plan, we’ll need to split it. Subscription type invoice lines contain period object that hold the period start and period end for this subscription. You’ll need to split the total to the subscription period. For example, an annual subscription starting at 15th of January 2014 would be split to 12 parts, targeted to Jan-Dec. So no MRR to Jan 2015 for that plan!
You can assign the amounts to the right months right away. There are extra benefits from doing that. Or, you can just include 1/12th of the total and take that subscription item into your calculations during the next 11 months too.
Accounting the MRR to the right month
You should use the subscription’s period start and period end to assign the MRR to the correct months, even when you are handling monthly subscriptions. If user has problems with their card - and they often do - the invoice belonging to one month may get paid in the next.
Handling refunds and special cases
At this point we have the basics down, but we still need to handle special cases and refunds.
Handling refunds is done by reversing the process we did earlier.
BalanceTransactions of type “refund” have negative amount and a link to the Charge that was refunded. You’ll calculate the part of the refund that needs to be removed from current month’s MRR using the same rules that we used for calculating the MRR itself.
The refund needs to be normalized too. A refund given today to a monthly subscription may well affect previous month’s MRR. A refund given today to an annual subscription may affect up to 11 months in future or history.
I fix the previous months’ MRR, but it’s up to you if you want to do that.
For example, if customer bought an annual subscription 6 months ago, and you’ll have to refund her the whole amount now, it’s up to you if you want to fix up the MRR entries from 6 months ago, or if you’ll just fix the entries for the current and future months.
You’ll also want to check from Customer’s Subscription data if the customer was lost, especially if whole MRR got refunded. Otherwise your customer counts will be wrong and you’ll have ghost customers with $0 MRR.
Including payments from customer balance
Stripe keeps an internal balance for each customer, and when they unsubscribe from an annual plan and move to a monthly plan, the difference does not get refunded to them, it goes to their balance. And as long as they have balance, you don’t get any BalanceTransactions when they pay something. You also don’t recognize that they have changed plans until their balance runs out.
To get your MRR right, you’ll need to include Charges paid from balance.
Here’s how you recognize Charge paid from balance:
"paid": true, "amount_due": 0, "starting_balance": -9000, "ending_balance": -8001
The Charge paid from balance is marked as paid, but no amount was due.
Prorations happen when customers change plans, and you haven’t disabled the prorate option in Stripe. Stripe creates an imaginary refund to the customer for the part of the subscription that the customer didn’t use, and places a new subscription to the new plan.
There are 2 types of prorations, ones that happen between plans having similar billing period and ones that happen when billing period changes.
Unfortunately both types mess up MRR calculations.
Handling Prorations when billing period changes
This type of proration, if not handled, will cause you to show MRR to wrong plans, but does not affect the total MRR.
As we are calculating just the total MRR here, I will not go into details, but the trick is to properly assign the refunds to the right plans, and create a correction entry so that the amount lost/refunded matches the whole subscription amount that the customer actually paid.
You’ll have extra trouble finding out the plan that was refunded, as the plan id is not returned in the Stripe proration invoiceitem. Plan name, however is, so unless plans have been renamed, that can be used.
Handling Prorations when billing period does not change
This type of prorations, if not handled, may cause you to show extra MRR for a single customer. The proration collects up changes that may have happened in previous month, and then charges them in the next.
For these prorations, you’ll need to check if the MRR to the new plan belongs to the previous month, and if it does, you’ll need to ignore it.
For example, you had a customer who subscribed to $9.99/mo plan in 27th of Nov. In 2nd of Dec, he decided to move to $14.99 plan.
Stripe will give you this proration at 27th of Dec:
$8.47 was refunded to the old plan, $12.70 was charged from the old plan’s remaining period (using new price), and $14.99 gets charged from the new subscription.
In this case, extra $12.70 would be added to second plan’s MRR and extra $4.23 would be added to total MRR, if you handled this wrong. The right MRR for Dec would be $14.99.
If, instead, this customer would have made the change in 28th of Nov, you would have needed a different handling. In that case you could make the move right there and then and account $14.99 as November’s MRR for that customer.
Should you really do all this?
Now that you know how to calculate MRR from Stripe entries, should you do it?
Well, the question to ask is: “Will doing this give me the best return of investment to the time spent?”
If you are just starting out, it may be better to just keep count on customers and calculate MRR with theoretical plan prices. Or to implement just the part handling charges and refunds and to keep count on the customers who change plans. Those are the ones messing up the figures, and if you don’t have plenty of them, the difference isn’t that big.
What I went through here is just calculating the total MRR, and getting the MRR by plan right is a lot more work still.
To be frank, I probably would not have done all this if I wouldn’t be implementing an analytics app.