In this tutorial we’ll look at how to use array formulas to calculate your daily change in MRR and a moving average of the daily change to smooth out the noise. This applies equally to other daily numbers you may wish to analyze such as Sales, traffic, etc. Moving averages are a great way to smooth out daily noise to give you a better look at your trend lines.
Quick reminder that SyncWith is laser focused on helping business users solve their data problems in Google Sheets by making data from any API instantly available and sync’d. Give us a try if you’re not already
Analyzing the moving average of the daily change helps you understand whether you’re growing linearly or accelerating your growth. Because you can see how much new MRR you’re adding. Ideally the daily MRR you add each day has an increasing slope. Again equally applicable to analyzing website traffic, revenue, retention, etc.
If you’re running a SaaS business you’re likely tracking your MRR, a long with a tonne of other metrics. Likely you use Stripe to handle payment and they have an excellent api to pull in all the information you need to create a stunning dashboard tracking retention, revenue, etc.
Let’s suppose we are pulling our Daily MRR from Stripe API into Google Sheets and we want to understand how much progress we make each day in terms of adding new MRR and what are trends are for adding incremental MRR each day. The first thing we need to do is add a column showing us the detla of new revenue added each day.
To do this we can use an arrayformula
arrayformula(if(B4:B="",,B4:B-{B4;B4:B})), what this does is subtract the current day from the previous day, for the first entry it subtracts it by itself since we don’t know what the previous MRR was. Breaking it down- The if statement ensures we only do the calculation on items that are not blank
=””
B4:Bis all of our daily deltas which we must subtract with an offset
- We add the B4 to the beginning of the array
{b4:b4:b}such that when we subtract the arrays they are now offset by 1 and thus we subtract the current day from the previous day to get the delta
Calculating Daily Moving Average
The daily moving average is the set of simple moving averages for all the days in the set of data.
To calculate the simple moving average for a given day we add up all the numbers over the time period and divide by the number of days (arithmetic mean over the time period).
You do this for every day and then you get a time series where the number on a given day is the average of the previous x days which smooths out the value on a given day because it’s just the average of the previous period.
Common periods you might use:
- 7 day to smooth out variances based on the day of the week - eg as a SaaS business focused on business users weekends are slower for us
- 30 day to smooth out monthly aberrations
- 24 hour to smooth out time of day aberrations
So how to do this with an ARRAY FORMULA in Google Sheets? Using SumIFs. One of the annoying things about Sheets is many of the formulas are not available as array operations, so you have to get creative with the formulas that are available. But the concept is this:
A single formula to calculate the moving average for every row in a columnHow to Do it
- For every row get and the date it represents get the sum of all detla mrr greater than 14 days old:
- Only for rows that are blank
IF(C4:C="",, - Make the SUMIF statement evaluate multiple times by making the condition statement have a range (array) in it
">"&(row(C4:C)-14) - So look at all rows nunbers in column C
SUMIF(ROW(C4:C)to see if their row number is greater than the current row number - 14">"&(row(C4:C)-14) C4:Cis the detla MRR that gets summed- So this means for row 16 we look at rows 3→∞ and we sum all their values, then for row 17 we sum all rows from 4→∞, and so on and so forth
- It’s certainly a mind bender thinking about how SUMIF works with arrays, so you might want to read it through again, just remember
- SUMIF( rows to compare that never change, condition that runs multiple times, rows to be summed)
- So a condition of row(c4:c)>14 and rows to compare of row(c4:c), and values to sum of c4:c
- so now for every row c4:c we have the MRR deltas that are > 14 days ago for the given row, but we don’t want that we only want >14 days up until the date (row) in question
- So if for every row (date) we subtract the sum of all the rows that are later in date (later rows) then all we’ll be left with is the last 13 days plus the row in question.
- Get every row sum all the delta mrr that occur after that row. Eg for all delta mrr after a given date sum it up.
- For every row number
ROW(c4:C)
- Filter only the rows numbers that are greater than the current row number
">"&(row(C4:C))
- The third parameter of sumif lets us set a seperate column to sum, so we sum the actual values of
C4:C(which are the delta MRRs)
- We now have an array where every row in it is the sum of that dates mrr delta as well as the proceeding 13 days - 14 days of MRR Delta. We then divide every row by 14 and we now have an array where every row is a day and the value in it is the mean of the 14 days of MRR delta
Now we can graph our MRR over time with the MRR delta and the MRR Delta 14 DMA:
- We can see our MRR rising
- We can see that our MRR change per day is quite noise
- We can see that our 14DMA of the MRR delta is pretty flat meaning we’re not accelerating our growth in MRR, we’re only growing linearly
Want to try the spreadsheet in action. Use our Stripe MRR Tracker Template
- Automatically pull your MRR from Stripe
- Aggregate your daily MRR
- Auto calculate your new daily MRR and 14DMA of your daily new MRR
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, including pulling all their data from
- 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.