Monthly Trending Forecast Budget Script
It can be difficult to get an accurate idea of how your Google or Bing SEM campaigns are trending, budget-wise. The total account budget totals you see in the console are to be taken with a grain of salt, since Google gives themselves the leeway to spend 2x or more of your stated budget. Forecasting becomes even more difficult if your campaigns aren’t hitting their daily maximums, or if you use budget scripts to override individual campaign spends.
Your total daily cost might be up to 2 times your average daily budget, unless you’re paying for conversions. If you pay for conversions, your daily costs may be more than 2 times your daily budget.
Google Ads Help
There is a bit of a safety brake, however. Google Ads policy is for your overall campaigns to not exceed a “monthly” total spend, calculated as your average daily budget multiplied by 30.4. We are going to take a similar, but more accurate approach, using our existing data to project monthly spends in Google Sheets, Excel, or other compatible spreadsheet tool.
In fact, this budget report has unlimited potential use-cases, and is not limited to search engine marketing data. You can calculate budget trends for anything this way.
Create a Live-Updating Google and Bing Ads Monthly Budget Forecast and Trend
Our goal here is to create a dynamic and daily updating display that shows our current, forecast and trending Google/Bing Ads budgets (total and percentage). The end result will look something like this, but of course can be customized in myriad ways:
Step One: Import Your Data
To create our budget trend script, we’ll first need to import our budget data. Luckily, this is only a single data point (budget spend month-to-date), so if you are unable or unwilling to set up automation, you can just enter the current spend manually.
We use Google Sheets to create live dashboards that are customized for each client’s request, as everyone has different KPIs and metrics they want to monitor. To set up an automatic sheet, you’ll need to install an add-on that will help you do that. We recently wrote a guide on setting up a converting search query report, which can help you get this set up. The Google Analytics add-on for Sheets is free, and all you need here. Advanced users and those with multi-channel or otherwise more complex clients can use paid options. Supermetrics is the popular, though expensive choice, and Adveronix looks promising. Options in Excel include Supermetrics, AnalyticsEdge and more.
Step Two: Create the Template
There are four cells that we will need to pay attention to when it comes to our data:
- Monthly Budget
- Current Monthly Spend
- Forecast Monthly Spend
- Forecast Monthly Spend Trend (%)
You are free to position and format these as you wish, and the only limit is your creativity. Alternatively, you can use the budget template we provide.
Step Three: Set Up Your Forecast and Trend Formulas
We will be implementing three formulas that will manipulate our current spend figure, alongside identifying the current day of the month, and how many days the current month has.
To calculate the current day of the month, use this formula:
=day(today())
To calculate the number of days in the month, use this formula:
=day(eomonth(today(),0))
We are now ready to calculate our forecast budget spend and trends. The way we do that is to take the current spend and divide it by the number of days elapsed, to get a cost per day. We then take that, and multiply it by the number of days remaining, and add the current spend. This is particularly accurate in calculating forecasts with fairly stable daily spends.
We use either manually entered data, or from add-ons/plug-ins as discussed above to generate current monthly spend. This is the only cell you should ever be manipulating, and if you have scheduled refreshes set up, you won’t even need to touch this one. The cells references are based on our template example that you’re free to use:
- B1: Day of month
- B2: Days in month
- B3: Monthly budget
- B4: Current monthly spend
To calculate forecast monthly spend, use this formula:
=(B4/B1)*(B2–B1)+B4
To create a trending percentage for the month, use the forecast monthly spend result in B5, and use this formula:
=B5/B3
Finally, make sure your formatting is set up to make your report easier to read. B1 and B2 are whole numbers, B3, B4 and B5 should be set as currency, and B6 as a percentage.
Using Your Trending Forecast Budget Report
Creating a trending budget report is an easy and effective way of generating meaningful and actionable data. This is an invaluable metric to share with clients, as well as to use yourself for monitoring budget performance trends. This is data that suits itself well to forward-facing dashboards in the office. Google Sheets also lets you easily share with others, so simply supplying a client with a link lets them view performance at any time.
Further Improving Budget Forecast Accuracy
As this report uses a simple calculation that looks at the entire month’s spend and divides that by the number of days passed, large changes in spend may not be as visible as you would like, especially towards the end of the month. If you do find your daily budgets fluctuate quite a bit, or you want added peace of mind to be able to catch sudden, unexpected spends, we’ll simply need to add a metric that shows us our last 3 days spend. This can be done via your chosen add-on/plugin.
We are using cell B8 in our example template for last 3 days spend. To calculate forecast monthly spend using this additional date, use this formula:
=(B8/3)*(B2–B1)+B4
This may potentially be significantly different than the forecast monthly spend you calculated using the total monthly spend. Large spikes will be immediately visible, and let you take action, but spikes going back more days than your window will have deprecated value in this calculation.
Which Option Should I Choose?
Unless you know you experience significant fluctuations in daily spend, you should start with the estimate using your full-month spend, Of course, with it so easy to set up, you could easily use both. Campaigns with stable spends should see these values track similarly, and that itself can be used to validate the data and make sure everything is set up correctly.
Where’s the Template?
We linked to it a few times above, but if you want to get started with a fresh sheet that has all of the formulas already in place (but without your budget data, obviously), feel free to use our template. This is of course for Google Sheets, and if you use a different spreadsheet you can copy/paste the formulas directly from there. This is compatible with Microsoft Excel, but not tested on other spreadsheets.
This article was originally posted at the digital marketing knowledge base from Husaria.