Create your own ad spend calculator with Google Data Studio
Analytics
Google Ads
Facebook
Data Science

Create your own ad spend calculator with Google Data Studio

Michał BurzyńskiMichał Burzyński

July 15, 2021

Keeping ad spend under control is essential if you have a limited budget. Even if you don’t, you probably want to appropriately manage it and have a full picture of your paid activity. At Ladder, we wanted both. So we created our very own ad spend calculator in Google Data Studio. Here, we’re going to show you how we came up with the idea and implemented it. It’s totally free and gives you much more insight than the tools offered by the main ad platforms.

But before we dive deeper, let’s explain the reason behind it.

Why you need to closely monitor ad spend

Have you ever launched an ad campaign with a dedicated budget and daily spend and then, after several weeks, completely lost track of how much money you’ve actually spent? At first glance, it seems this would be a piece of cake to figure out. If you have $1,000 to spend in a given month, you just set around $33 as your daily budget, right? That way, you’ll know exactly how much money will vanish from your credit card.

But this may quickly become more confusing once you conduct ad optimizations or attach further campaigns to your paid activity. Say for example that you limit the budget of a poor-performing campaign and transfer it to a more promising one. Then, once you collect enough data to launch a retargeting campaign, you do it alongside the one that’s currently running. The more changes and the more campaigns you have, the easier it is to overspend. 

Think that only a really disorganized individual could find themselves in such a pickle? Trust us – it happens to the best of us too! It’s not difficult to overspend when you have a really busy month and the owner of an ad account (your client) asks you to implement many changes within that time. The first adjustments are quite easy to monitor, but the more requests you have, the easier it is to lose control of how much money you’ve truly spent.

The good news is that there are some methods you can use to avoid pulling out your hair over something like this. We’ve made our life easier here at Ladder using Google Data Studio, for example. And you don’t need advanced skills to implement it on a daily basis. 

But there are plenty of other ways to keep a tighter grip on your budget. So, let’s take a look at the features that each key ad platform offers in terms of managing ad spend.

How to use Facebook automated rules

If you’ve never fully explored Facebook Ads Manager before, we’d definitely recommend taking the time to look at what it can offer. Their ad spend management tool is called ‘Rules’ and can be found on the main bar in the top right.

creating a new automated rule in facebook ads

Facebook implemented this solution to free you up from having to manually check the performance of your campaigns. It allows you to set up some desired actions that will be triggered when certain conditions happen. 

Some of these actions include:

  1. Pausing an ad, ad set or campaign

You can easily tell Facebook to turn off any activity when the current performance is below your expectations. That could be after a cost per result exceeds the amount that you want to pay for, or when an ad frequency surpassed a safe border. 

Your possibilities here are almost infinite.

  1. Relaunching any activity 

Just as you can pause an ad, ad set or campaign, you can also automatically reactivate any assets when precise conditions are met. 

Imagine that you operate in a high-competition market which influences the cost per thousand impressions (CPM) of your ads. When you’re charged for impressions (which is the most commonly used metric on Facebook), you’d of course want to avoid running your paid activity when your rivals are in the game. Facebook enables you to ‘hunt’ for a time when there’s almost no competition for your target audience. In this way, you can take advantage of a lower CPM and reach out to more prospects.

  1. Increasing your budget 

When the current performance of your ads is better than you expected, Facebook can raise your budget to maximize the chance of collecting more results at lower costs. 

This is also worth considering in order to improve your overall outcomes. Just determine the metrics that are crucial for you and let Facebook adjust your spend automatically.

  1. Sending a notification for any of the above circumstances 

You don’t have to perform any action when your ad performance meets set conditions. You can be notified about it by Facebook and then decide what next steps to take. 

This feature is worth using to keep an eye on comments posted on your ads – you can then turn off an ad that’s encouraging negative engagement. This is also a good way to verify that your automated rules work.

Facebook automated rules in practice

Another major benefit of this tool is its ability to create really advanced rules that align with your business goals. 

Say that you’re running one campaign with five ad sets – all operating under the same budget. If you’re worried that not all ad sets will perform well, set a rule to turn off a poor-performing ad set. This optimizes your campaign budget, giving more room for the better ad sets to keep generating great results.

facebook automated rule to turn off all ads

Now that you know what Facebook’s automated rules can offer you, let’s get back to our main topic: How can we use them to manage an entire budget? 

There are several ways to do it, of course, and you can adjust such rules to your specific business. But the simplest way to set it up would be as follows:

facebook automated rule to send notification

In the above example, Facebook will send the ‘subscriber’ a message once the spend of all active campaigns exceeds $990 within 30 days. If your monthly budget is $1,000, you’ll have enough time to decide whether you want to pause your ads or do something else. 

We’d recommend thinking twice before deciding to pause all your currently running ads at once. Bear in mind that doing so can reset the learnings that Facebook managed to collect while delivering your ads. Sometimes it’s much better to limit your spending (ideally by no more than 20% at once) rather than turning all your activity off.

What’s also great about this feature is the frequency with which Facebook will verify the conditions of your rules. This is especially useful to those who like to very precisely keep their spend under control. 

Finally, to make sure you won’t exceed your budget, you can also add multiple subscribers to notify any relevant members of your team.

How to use Google Ads automated rules 

Google Ads offers similar capabilities to Facebook. You can use it to both save yourself from unplanned overspend and improve your ad performance. Their ad spend management feature can be found under Tools & Settings > Bulk Actions > Rules.

facebook ads menu automation rules

Google has quite a decent guide about this tool, which you can check out here.

Facebook and Google’s automated rules are very much alike in some respects. However, when you come to create a new rule, you may be amazed at how many other options Google offers. 

If, for instance, you’re running a search campaign, you’ll find a number of rules that you can apply to your keywords – such as increasing bids for a keyword that led to a conversion. Also, by linking Google Ads with Google Analytics, you can automatically pause any given ad that causes a bounce rate on the landing page above a certain value. This enables you to easily remove irrelevant ads from your campaign.

To test out the features, you can additionally set and apply any of the available rules with an email notification. This won’t influence your current activity.

Google Ads automated rules in practice

When it comes to keeping your budget under control, we can use nearly identical settings that we picked for Facebook – namely, sending an alert once our monthly spend exceeds a set value. The settings on Google would look like this:

google ads automated rule

The outcome should be exactly the same too – once you spend over $990, Google will notify you by email. 

However, there’s one important difference here: the minimal frequency with which you can check your rule’s condition is once a day. As you can imagine, this is far from ideal. You could potentially meet your rule’s conditions just five minutes after a daily verification, and the system won’t check your rule again until almost 24 hours later. 

Fortunately, you can protect yourself against such an eventuality by setting another rule(s) with a different verification time. This forces Google to check your ad spend at more intervals throughout the day.

How to use Google Data Studio as ad spend calculator

The way that we keep our clients’ ad spend under control here at Ladder might initially look more complicated than simply using the solutions offered by Facebook and Google Ads. But you’ll quickly realize that it really isn’t rocket science!

Google Data Studio is one of our key tools. The more we use it, the more opportunities we can see in it. Once you set up all the performance metrics you care about, all your reports are automatically updated. This makes it a crucial data source, which we then use to inform our decision making.

Technically, then, you don’t have to worry about anything – you only need to keep an eye on ad performance and draw any possible insights from the presented data. Google is constantly evolving this tool too, and new improvements are added each month. 

One aspect of our reporting in Google Data Studio is budget and goal pacing. Each month, we set out our client’s media budget and the new results we need to achieve. This takes the form of widgets that display real-time results, and graphs that show our progress towards monthly goals. Both act as an alert when you’re progressing too slowly and a great motivator when you’ve far surpassed your goal.

Google Data Studio reporting in practice

Recently, a member of our Search Team exceeded their goal in the very first week of running an initial app install campaign for a new client. 

Just take a look at the results below. Note that there were seven days left to go until the end of the month – and the number of app installs were already over 844% above the goal!

app installs analytics dashboard

By building a report like this, nothing escapes your attention. You can observe the progress of all the goals you’ve set. 

But, as the topic here is about keeping spend under control, let’s take you through what we did to keep an eye on our budgets. 

Creating widgets for your ad spend calculator in Google Data Studio

Once you’ve picked your data source (Google Ads in our case), you need to create a new field to set your monthly budget. 

google budget pacing

As you can’t set a fixed value like ‘$4,000’, you have to create a formula. Here’s what $4,000 would look like:

budget field in google data studio

Note: if you have a problem with creating custom metric the presented way (it happens multiple times with GDS), try to use the following solution:

impressions formula google data studio

(0 * any metric (say Impressions)) + 4000

And that’s our first widget – the monthly budget. 

Next to it, you can choose to display the amount of money you’ve used to date (this is usually the ‘Cost’ metric in Google Ads). The two below images show you how to include how much money you have left to spend and what percentage of the planned budget we’ve already used. 

In the first case, you’d create a new calculated field with a formula like this:

amount left formula google data studio

To see the percentage of the budget already used, you’d create this calculated field (choosing ‘Percent’ for the outcome):

percentage of budget used formula google data studio

And that’s it! By following these simple steps, you can create your own ad spend calculator to monitor your ad campaigns. If you’re in charge of more than one ad account at once, you can include all their results on one page. Pretty convenient, right? 

Setting a monthly budget and a current spend is just scratching the surface. Once you’ve familiarised yourself with the process, you’ll start trying to create your own. It really is addictive!

We went further and created two more widgets to even better manage our budgets. 

manage ad budget analytical dashboard

As you can see, it now tells us much more about where we stand with our budget. We incorporated some other metrics to see how far we are from our planned daily spend. If we’re above, orange text highlights that. Otherwise, it shows green. It works like an ad spend calculator which simultaneously tells you the recommended daily budget you should set to avoid overspending.

These outcomes show our current spend and suggested daily budgets depending on how many days of a month are left. This was perhaps the most technically tricky thing to do.

We couldn’t find an appropriate formula in Google Data Studio that shows the number of days already past. So, instead, we created a new data source in the form of a Google Sheet document. You can do this sort of thing in Excel, after all, so why not make the calculations here and just link it to our report? 

The formulas we used were:

  • Today: TODAY()
  • Start of month: date(year(A2),month(A2),1)
  • End of month: EOMONTH(A2,0)
  • Past days: A2-B2
  • Days left: C2-A2+1
  • Days total: D2+E2

And here’s what it looked like:

budgeting formula in excel

You don’t have to change anything here. It doesn’t matter what month it is, or whether it’s a leap year. Our formula knows it all. And the best part? Those calculation fields are automatically updated once you open your report in Google Data Studio and refresh your data!

Most important, however, is the fact that this enables you to use a metric of days left in a given month and link it with your current spend.

All you have to do after creating a sheet-like this is to select it as a new data source for your report.

google sheets connection

Blending data sources in Google Data Studio

Now we need to match our metrics from Google Sheet with the ones from Google Ads. This will allow us to create metrics about suggested daily spend in relation to days left in a given month. 

To do it, just start building a chart based on multiple data sources, otherwise known as a blended data source. Choose the Google Sheet source first, and then select the source that shows your current ad spend.

google data studio sources

Notice that we don’t use any join keys here. We only picked relevant metrics from each data source and put them together in our blended data source. If necessary, you can limit your data by using a relevant filter to apply to each source.

Building your ad spend calculator

This blended data source is now ready to use in creating our ad spend calculator. Here’s how we went about it, with a short description of each formula.

  • Planned Daily Spend tells us what daily budget we should set up in order to spend the whole monthly budget evenly. It’s also useful at the beginning of a new month when you’re wondering how to plan your media spend:
planner daily budget formula google data studio
  • Actual Daily Spend represents the current daily budget being spent – in other words, what an average daily spend is bearing in mind the money already spent. It takes our actual spend and divides it by the number of days that have passed this month:
actual daily spend formula google data studio
  • Pacing +/- refers to the difference between the planned budget and the current spend. A positive value informs us that we’re spending more than we should, and a negative one shows the opposite:
spend pacing formula google data studio
  • To see this more easily at a glance, we used conditional format rules so that the text would show in either an orange or green color. This is a recent feature that Google have introduced and it’s really simple to use. These are our two rules for this metric:
conditional format rules google data studio
  • Pacing % shows us a percent value of our over- or underspending of daily budgets. As you saw earlier, we’re above the planned spend – but the Pacing % informs us that we’re only about 4.5% above the goal. Nothing serious, but something to keep an eye on.
spend pacing formula google data studio
  • Suggested Daily Spend is a crucial metric. It calculates what daily budget we should set now to meet our budget goal and avoid overspend:
suggested daily spend formula google data studio
  • Spend Change +/- tells us exactly how much we should increase or decrease our budget by in order to meet our goal. It seems like a no-brainer, but it saves you from doing the calculations yourself:
spend change formula google data studio
  • Spend Change %, the last metric, shows us the suggested change in budget as a percent value:
spend pacing formula google data studio

Simple as that. This is how we built our ad spend calculator in Google Data Studio to keep our spending under control. If you want to manage your ad budget better, it’s worth preparing a similar report. It may take a little while to set up, but will save you so much time in the long run.

We also use graphs in our report to even better reflect the current state of a client’s budget. Here’s what it looks like:

google data studio dashboard budget spending pacing

The two graphs below our widgets show us how close we are to our budget limit in two different ways. Together, all the various metrics give us a clear picture of our situation for any given client and enable us to maintain a tighter control on ad spend. 

Takeaways

Google Data Studio provides many solutions that you can apply to your business. An ad spend calculator acting as a dashboard is just one that’s worth considering if you’re concerned you might miss something during a month. 

Automated rules offered by well-known ad channels are great to use, but they still don’t show you the full picture. If you really want to be one step ahead of possible problems, use the tools that can help you do so.

Ladder powers strategy and performance solutions for fast-growing brands

Talk to a strategist →

Growth is a high velocity game.

Sign up to our newsletter to stay up to date with all the latest movements in the field.

More from

796+ open sourced growth tactics used by the best marketers in the world.
Explore our playbooks →