Avoid campaign overspend with an automated budget monitor

In digital marketing, managing your budgets is unsexy but critically important work. Say you’re overseeing dozens of campaigns for your brand or clients.

Or you just operate with a healthy level of caution for the campaigns you’re running. And you like the idea of keeping your job. The specifics aside, budget management is key.

The ad platforms try to help with this in various ways. Daily budgets, campaign budgets, lifetime spend limits, and account insertion orders are a few examples.

But at the end of the day, until the robots take over, budget management is a manual process involving people typing values directly into the platforms. And as we all know, manual processes are breeding grounds for miscalculations and typos.

Therefore, the question is not: Will budget mistakes occur? There are in fact two more important questions: How long will it take you to discover each mistake? And how much over- or underspend will occur before you catch them?

Activating the promise of data-driven marketing, and helping in both of these areas, I created an automated budget monitor for Google Ads and Facebook spend, our two most prominent channels.

It helps our team quickly identify and highlight daily campaign spend anomalies. It’s proven to be immensely valuable in our budget management workflow, and I think others may find it useful too.

What is an automated budget monitor?

At its core, the automated budget monitor is a tool that pulls your daily advertising spend (actual spend, not your budget amount) for each campaign over a given timeframe and highlights any spikes that are worth your attention.

To get more specific, in my example I am first pulling spend from Google Ads using Supermetrics and dropping it into a Google Sheet.

The data is broken out by ad account and campaign, with the spend separated by day for the last seven days. Then, for each campaign, yesterday’s spend is compared to the average spend across the previous six days.

If yesterday’s spend exceeds 150% of the six-day average, that particular campaign is highlighted.

Finally, once the data is fully refreshed, if there are any highlighted campaigns in the report, an automated email is sent to my team with a PDF copy of the report and a link to the live document.

That’s it! Simple, yet important.

Note: For tips on how to combine Google Ads, Facebook Ad Manager, and other channels all into one budget monitor, scroll down to the section: How to combine all channels into one report.

Why should you build an automated budget monitor?

There are many ways this tool will help you. To name a few, you’ll be able to:

• See significant mistakes (e.g., $1,000 instead of $100) the very next day.
• Identify significant mistakes made to lifetime budgets, as the daily spend will spike in response to the revised lifetime budget.
• Save time. The tool allows you to visualize spend from all of your campaigns in one place. The manual alternative, where a real person has to go into each platform and pull the same data across several campaigns, takes an enormous amount of time and is inherently error prone.
• Save time. Yes, this is an intentional repeat. You’ll only receive notifications for those campaigns with budget spikes. All the others are in order.
• Customize the parameters of the tool to meet your level of comfort. For instance, is a six-day average for spend not enough for your blood? No problem, just pull in 14 or 30 days’ worth of data. Or is 150% as a threshold for the “overage” too high or low? You can easily adjust the threshold percentage directly in the cell.
• Observe when new campaigns begin or when planned budget increases for existing campaigns go into effect. For instance, if you set a campaign to start in two weeks, you will eventually see the new campaign appear in your budget monitor. It will be highlighted (as any spend is a spike above zero spend), and you can ensure that it began as expected. This is of course not a substitute for going into the platform and checking on your new campaign to ensure that all other aspects of it are set up as planned, but nevertheless it is a nice gut check to see it in the budget monitor.

How to create your own automated budget monitor

These steps describe how to build an automated budget monitor for Google Ads using Supermetrics for Google Sheets.

You can create additional budget monitors on separate tabs of your Google Sheet for any other platforms to which Supermetrics connects, such as Facebook Ads, Twitter Ads, Pinterest Ads, and Microsoft Advertising (Bing).

To note, you will need to perform steps 2-7 below for each additional tab of your budget monitor spreadsheet. For guidance on how to create a single budget monitor that combines all channels into one report, scroll down to the next section.

Here are the steps for creating your own automated budget monitor. See the screenshot earlier in this article for a visual aid:

1) Create a Google Sheet

Then create a tab for your Google Ads data.

2) Create your Supermetrics query to pull in your campaign spending

Connect to your Google Ads data source. Select the accounts to include. Select the last seven days of data, or however many days you prefer. Your only metric will be Cost. Split by account name and campaign name in rows, and then split by date in columns. You can also add any filters if you like. Then run the query to ensure that the data pulls in as expected.

3) Add a cost threshold cell

This cell contains the value of the threshold that you want to use for highlighting campaign spend anomalies. In my example, this is 150%, meaning if yesterday’s spend was greater than 150% of the previous six-day average, the campaign is highlighted. Having this value in its own cell means that you can easily change this parameter whenever you like.

4) Add columns for your previous six-day average and yesterday’s overage

Add calculations into these columns. The six-day average is simply the average of the previous six days. The overage column takes yesterday’s spend and subtracts the product of the six-day average times the cost threshold. If there is no overage, the column is filled with empty quotes.

It is important to note that these formulas should extend far beyond the bottom of your data, because you want them to work no matter how many campaigns are pulled in with the query. In my example, the formulas extend down to row 500.

5) Add conditional formatting to highlight the campaign rows where there are overages

This helps you to easily find the anomalies when scanning the report. In my example, the conditional formatting rule applies to the range “A7:N500” and the format rule uses the following custom formula: =$N7<>””. The range “N7:N500” refers to the “Yesterday’s Overage” column, excluding the header.

6) Add a notification cell to detect if there are any overages

This cell is used for the Supermetrics email notification. The cell detects if any of the campaigns had an overspend. The presence of a value in this cell triggers the Supermetrics email notification. As an example, below is the formula from my notification cell:

=IF(COUNTBLANK(N7:N500)=ROWS(N7:N500), “”, “Overage detected”)

7) Schedule the query refresh and email notification

In your Supermetrics menu, go to “Schedule refresh & emailing.” Schedule the query to refresh and email daily, and to start processing at 23:00 (11 PM). Select the type as PDF attachment, enter the email addresses for the recipients of the report, and edit the details of the email as you see fit. The last important step is to check the box for “Conditional emailing” and enter the cell address for your notification cell.

As Supermetrics states, this option dictates that “[a]fter refresh, the email will only be sent if this cell is not empty,” meaning you won’t be bothered with an email if there are no overages detected. It’s also a good practice to enter your email in the box for sending email alerts if the queries fail on refresh.

How to combine all channels into one report

You’re probably thinking, “Sure, this is great, but I don’t want to receive five emails every morning with one coming from each of my marketing channels.”

The limitation is that Supermetrics only allows you to connect to one data source per query, so you must build separate tabs for each channel that you want to bring in.

However, it is possible to combine all of your digital marketing channels into one report with a data connector, like Funnel. I have built a similar budget monitor including all channels using Funnel.

It also exports the data into a Google Sheet, from which a pivot table pulls the data and formats it similarly to the Supermetrics query output shown above. From that point on, the process is essentially the same.

Go forth and manage your budget

I hope you found this guide helpful in leveraging data to manage your digital marketing budgets and catching any potentially catastrophic mistakes. It’s a worthy practice, especially when you’re monitoring many brands and campaigns at once.

Nick Harper oversees digital marketing campaigns at Vladimir Jones, an independent advertising agency celebrating its 50th anniversary in 2020.