Track Stripe MRR in Google Sheets

1. Active Subscriptions

2. 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

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:

• 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 Pagination in the accordion and set it up

• We’re all set - hit update and we’ll now have our subscriptions loaded into Google Sheets

 

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
2. 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
3. Finally because Stripe provides prices in 100ths of a dollar or cents we divide by 100

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

Lets Clean Things Up

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

• 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()

• 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

 

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.

• 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

• 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

Try the Free 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