Estimate Month End Run Rate Results in Google Data Studio

What is a Run Rate?

A run rate is utilised to estimate future performance based on current / historic data. It is key to note that to remain accurate a run rate assumes current conditions will continue with no significant fluctuations.

For the purposes of this post I’m going to explain how to create a month end run rate estimate utilising data sourced directly from Google Search Ads 360. While the steps detailed focus on Search Ads 360, the method can be applied to other data sources for example Google Analytics, Google Search Console etc with minimal adjustments.

To calculate a month end run rate the following calculation should be utilised:

Run rate = (Value of X to Date / Days of Month) * Days in Month

Step 1 – Days of Month

First we must determine how many days are contained within the current month. This figure will be stored as a numerical field within our data source.

Field should be added as an additional field

The below formula will efficiently output the current number of days which have passed during the current month. For reference I like to call this field “Days of Month”:

CAST(SUBSTR(CAST(TODAY() AS TEXT),9,2) AS NUMBER)-1

To break down the formula, first todays date is loaded via the TODAY() function, example output “30 Jul 2021”. This is then converted from a date value to a text string via a CAST function. Next the SUBSTR function is utilised to select only the first two characters – the date of the month. A second CAST function is then used to convert the text string to a numerical value. Finally a day is subtracted from the total number of days passed to ensure only whole days are utilised within our run rate calculation.

Step 2 – Days in Month

Once we have calculated the number of days which have passed, we need to determine the total number of days within the current month.

Due to the fact custom fields within Google Data Studio cannot combine dimensions & metrics within a CASE function we need to create an additional field before we can create the final “Day in Month” field.

In order to work around the above limitation we need to capture the current month of the year and format it as text rather than as a date. To achieve this create a field and name it “Month of the Year” and insert the following formula:

SUBSTR(today(), 1, 7)

This formula similar to the previous one loads todays date via the TODAY() function, but this time the month / year are selected.

Now we have the current month / year stored within a text field we can proceed to create our “Days in Month” field. For this we simply require a formula which contains a CASE statement that matched the relevant month / year to the number of days within that particular month. The below formula contains all months for 2021.

CASE

WHEN Month of the Year = "2021/01" THEN 31

WHEN Month of the Year = "2021/02" THEN 28

WHEN Month of the Year = "2021/03" THEN 31

WHEN Month of the Year = "2021/04" THEN 30

WHEN Month of the Year = "2021/05" THEN 31

WHEN Month of the Year = "2021/06" THEN 30

WHEN Month of the Year = "2021/07" THEN 31

WHEN Month of the Year = "2021/08" THEN 31

WHEN Month of the Year = "2021/09" THEN 30

WHEN Month of the Year = "2021/10" THEN 31

WHEN Month of the Year = "2021/11" THEN 30

WHEN Month of the Year = "2021/12" THEN 31

END

Step 3 – Calculate Run Rate Figures

Utilising our newly created “Days of Month” and “Days in Month” fields we can quickly calculate month end run rates for key metrics. To calculate estimated total monthly clicks we would use the formula:

(Clicks / Days of Month)  * Days in Month

Similarly to estimate the total monthly cost we would simply substitute the “Clicks” field for “Cost”:

(Cost / Days of Month)  * Days in Month

The default date range for the table should be set to “This month to date” with the include today option not selected.

Step 4 – Run Rate vs. Forecast

A further step which I regularly take to add value to run rate calculations is to directly compare them in Google Data Studio with previously forecasted figures. This can be achieved by pulling in static monthly forecast projections from an external source such as a Google Sheet and then combining via a simple data blend with actual / run rate figures. This allows the % difference between previous forecast figures and actual projected to figures to be viewed up to the month end.

Similarly if you wish to compare monthly performance to date vs previously forecasted performance, but you only have month end figures then the above run rate calculations can also be applied to the imported forecast data allowing for further usage.

Do you use run rates as part of your regular reporting, do you find them useful? Also let me know in the comments any further methods you’ve utilised to improve further the accuracy of your run rate calculations.