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:
- Active Subscriptions
- Past Due Subscriptions
- 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.
First thing you need to do is head over to https://dashboard.stripe.com/apikeys and get your
- Search for
stripein the add on
- Select the subscriptions endpoint
- Add a
statusquery parameter and set it to active (this get’s active subscriptions)
- Add a
limitquery 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:
- 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.
Pagenationin the accordion and set it up
- We’re all set - hit update and we’ll now have our subscriptions loaded into Google Sheets
- 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
- 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
- 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
- Finally because Stripe provides prices in 100ths of a dollar or cents we divide by 100
We can now sum all the normalized MRR to give us our current active MRR normalized to USD
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
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:
Now we can add two sums and show our total MRR:
- 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
- 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
- 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
- Now we’ll
- 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.dateas the field used to determine if we add or update.
- 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.
- It’s helpful to track MRR on a rolling basis to get rid of the noise, here’s our tutorial on how to use an Array Formula to calculate a 7DMA and 30DMA of your MRR
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