Track Stripe MRR in Google Sheets

Stripe does not offer an easy way to pull down your MRR by day in order to graph it and analyze trends. In order to get our MRR we need to do a couple things.
The first thing to understand is your MRR on a given point of time is (using Stripe’s definition):
Active Subscriptions + Past Due Subscriptions
So the we’re going to create two connections to the Stripe API:
  1. Active Subscriptions
  1. Past Due Subscriptions

Not Sure How to Pull API Data into Google Sheets?

  • SyncWith is a Google Sheets Add On
  • SyncWith allows you to move data from any API into Google Sheets
  • Learn more about why data lovers use SyncWith
  • Check out our Google Workspace Listing and see why we’re the highest rated API add on with over 100,000 installs. Install today and get your data into Sheets in minutes.

Getting an API key from Stripe

First thing you need to do is head over to https://dashboard.stripe.com/apikeys and get your publishable key.

Getting Active Subscriptions

  • Search for stripe in the add on
  • Select the subscriptions endpoint
  • Add a status query parameter and set it to active (this get’s active subscriptions)
  • Add a limit query parameter and set it to 100 (because we’ll need to page results we want to get the most per request where 100 is the maximum)
  • Hit Preview
  • Be warned the subscription API sends back a lot of data, use the expand button to expand the json
  • Expand and delete the response until all you have left are 4 columns:
    • notion image
  • Because the Stripe API requires pagination to return a large number of rows we need to setup pagenation. Stripe uses a cursor to allow you to move through the results.
  • Expand Pagenation in the accordion and set it up
    • notion image
  • We’re all set - hit update and we’ll now have our subscriptions loaded into Google Sheets
notion image
 

Normalizing Active Subscriptions Data to MRR in USD

  • First thing you’ll notice is we’re getting back the interval and the currency, to calculate MRR we need to normalize all plans to a monthly revenue amount and all plans to a common currency
  • Do do this we’ll create a helper column called Normalized to MRR in USD
  • In this example we only have monthly and annual plans (which in our experience are the most common options), if you offer other plans you’ll need to normalize against those too.
  • Unfortunately GoogleFinance function is not supported with Array Formulas so we need to put a formula in and copy it across
  • Here’s the formula:
    • We need to check c3 to see if it’s blank - we only want to normalize rows with data, so that our sheet remains clean
    • A3 is the subscription amount unnormalized which we need to multiply by 3 factors
      1. The next if statement looks up the exchange rate for non usd currencies, if the price is already in usd we just multiply by 1
      1. The last if statement ensures that any yearly subscriptions are divided by 12 and any other subscriptions (which in this case are monthly) are left the same
      1. Finally because Stripe provides prices in 100ths of a dollar or cents we divide by 100
notion image
notion image
We can now sum all the normalized MRR to give us our current active MRR normalized to USD

Getting Subscriptions Past Due

It’s important to note that active subscription and overdue subscriptions are mutually exclusive and Stripe’s definition of MRR is active + over due. So for our purposes we’re going to add them together. You could choose to only included active subscriptions or you could discount your overdue based on their historic likelyhood to not pay.
To get Subscriptions Past Due we’re going to repeat exactly what we did above but with a new connection.
To save some time we can simply duplicate the connections.
After duplicating we can change query so that connector only pulls subscriptions where the the status is past due.
Everything else should work the same:
  • Click preview
  • Click update
  • Copy and paste the helper formula to normalize to MRR in USD
notion image
notion image

Lets Clean Things Up

By default SyncWith puts data on a new sheet per connector - but you can have multiple connectors dump onto the same sheet. Easiest way to do that:
Cut and Past all the data from the existing sheet to where want it. All future updates will detect the new location and update accordingly.
Add a little formatting and we now have a sheet that looks like this:
notion image
 
Now we can add two sums and show our total MRR:
notion image

Recording MRR over time

  • Now that we’re able to get the current MRR we probably want to pull it every day
    • For both the Connectors that pull Active and Over Due Subscriptions
    • Open the Connection
    • Go to the Scheduling setting
    • Set it to Automatically refresh daily
notion image
notion image
  • Great now the MRR will be updated every day. The problem is it will only ever show our current MRR. What we want to do is append the new MRR we get every day so we have a time series of MRR.
  • Do do this we need to append the latest days MRR to a list
  • We’ll start by ensuring out Total MRR has a time stamp, since we’re pulling daily now we can assume that the number showing there is the current day, so lets show the date below using =today()
notion image
  • Now we’re going to use a little trick, we just need any endpoint that will send back a valid json response with us sending it two fake parameters. Those parameters will be the date and the MRR, which SyncWith always includes in the response. To do this we’ll create a new connection called Echo MRR.
    • This connection will take the Total MRR and the Date as Inputs
    • It will echo back the the current date and current date’s MRR
 
notion image

Preview and Finish the Connector

  • Now we’ll preview
  • Delete all the columns we don’t need leaving Data and MRR
  • Set the update mode to Update or add rows as needed
  • We’ll need to refresh the preview again
  • You’ll see the note: Add new rows to the bottom of the sheet unless these columns match
  • So we want to add new rows unless the date matches - so we’ll pick params.date as the field used to determine if we add or update.
notion image
  • Finally make sure you set the Echo MRR connector to run every day such that we continually append the new MRR
  • If you want you can move the MRR to the existing sheet to keep consolidating data into a single sheet. This is helpful if you have a spreadsheet acting as a dashboard. Having a single Sheet representing your dashboard and other Sheets driving the data for the graphs / insights.

Other Things to Consider

Try the Free Template

You can also use the following free Google Sheet Template to Track your Stripe MRR, if you copy the template
  • You can auto install the syncwith addon
  • Have the connectors outlined above already setup
  • 5 easy steps to have the whole thing up and running