Ben Brown’s Post

Transcript

Hey guys, in this video I want to run through how you can create a detailed Google Ads budget pacer which will give you full visibility over how much you're spending versus your allocated budget. Ty, I would like you to crunch those numbers again. It's programmed. There's no such thing as crunch. Just crunch them, please. Better still, this pacer can be used across any PVC activity like LinkedIn ads, Facebook ads, Bing Ads, whatever ad you're running. So let's jump straight into it. OK, so this is the tab with all the raw data in it, and this may not make much sense yet, but let's go through it column by column so I can give you a bit of an oversight of what each of the columns mean and how it all works together. And then how it then can go into this sheet here, which gives us an overview of the August 2023 Google Ads month per day. And how we're pacing against our current allocated budget. I do apologise for the fact that this isn't styled in any way, shape or form, but it's very much just to give you a Google Sheets mechanism of how you can apply this budget as tracker or pacer to your work as well. So I can give you the template. So first and foremost, let's just say, for example, we have a budget for our Google Ads of ��6000 per month. I'll get into what the other ones mean in a minute, what this graph means. But let's go over to the pacing formula. And at the moment we're currently on the 15th of August, so we haven't got four months worth of data this month, but it's good to show you how this works part way through the month as well. And that's meant we've got around ��6000 per month for budget. And everything in this column down here is pulled in from this monthly thing here. So if we changed it to 5000, then automatically that's all changed to 5000. Take that back a SEC. So first and foremost is we need to make sure we start putting this stuff in dynamically because the last thing we want to do is start going in here and pulling in how much we spent each day by looking into our different accounts of pulling that through. And this is where API extraction tools come in handy. You can use API extractor tools such as Supermetrics, funnel dot IO, Agency Analytics, or you can build a custom API extraction. But ultimately just making sure that you have access to one of these tools allows you to automate this whole process. You can do it manually, but something you have to manually. Day, every day. So what you can do is if we go into extensions and take one of our API extract until we can launch a sidebar which integrates here with Google Sheets. And what we can do once we open one of our API integration tools is look to create a new query. So we just call this Google Ads cost we can say we want to in cell one. We can use a campaign for one of our test sites. We use integration which is Google ads in this case. What we can do is look at the dimensions which will be date. With date, we also want to look at cost, so we can look at every single date and the associated cost. It's then important to remember to go ascending and rather than doing the last 30 days. What we want to make sure that we keep updating so it renews every month and starts from the month that we're now on would be to go on to. This month and then apply and then. We can then increase the row limit too. Let's just put all just to make sure that we have, it's only going to be 31 days maximum and let's just put all anyway. And then what we can do is insert data and this data will then update these two rows. Here, if you didn't do this month and you put this year, rather than doing daily costs, it would start doing monthly cost. So if you wanted to have a, a master sheet which looked at all of your previous monthly costs and then look at the associated conversions and you know, other potential metrics, then you can have a really nice oversight of that as well. In this case, we do it this month just so we can see day by day how much we're spending, so we can see if we're over pacing, under pacing and what sort of the the pitch of the month and how we can adjust it in the short term. So you can press insert data and that will then pull through this data here. And depending on the API extractor tool that you use, some of them automatically do it every 24 hours. Some of them don't. You need to look into the tool and provider that you're using. We're pretty agnostic here when it comes to it. Just work out which one works best for you. But yeah, the three best ones we found are. Supermetrics Funnel and Agency Analytics. But this is just a Sheetz Sheets API plugin O let's get rid of this now. So now that we pulled in the date and the cost here, obviously every day we populate the new one. So tomorrow the 16th will come through and the associated cost 17th. And as it goes down, it will then start changing these associated formulas, which at the moment are just set default with the formula behind the scenes. So we had the date, we have the cost, we have the cumulated spend. So how much we spent every day? So every day, how much is this these two ones added together. So three days in would be the sum of all of these. Get it together. The budget for the month, how many days of the month there are so we can see here that day compared to a 2. So it's self referencing. So we can see here this is the first. So it has the day of the month first here and also how many days in the month also self referencing. It knows it's August, it knows it's 31 days is how much the expected spend should be. So this one here is actually how much the spend was and this is one how how much the expected spend should be if we were hitting the exact number every day of our budget. And meeting it completely and what we can see here is the budget pacing. So for example, we were ��59 short. So we had actually how much we spent minus C2, which was accumulative, accumulative spend. So we were ��59 shorts, we had ��59 more to play with. Same with this day, you can see the 3rd of the month we actually spent ��55 more than we were supposed to do, so starting to level out. We can then see here the remaining budget, so ultimately how much budget we have left after each day and how many days we have remaining. This then comes in handy to look at this really important metric which is revised budget. So say for example what we should have spent on the first day, we didn't quite meet it. So we were saying that for the rest of the 30 days we need to spend 195 lbs to make up the difference in the budget. And as we go further and further down, you can see that we've been under pacing. It's now gone up to On this date here, 257 lbs. Because we fallen behind with how much rebidding. So if we want to spend that whole 6000 lbs rather than only spending ��193.00 a day, we need to spend around ��257 per day. This is really handy to have an overview of whether you need to start maybe upping some daily budgets or whether you need to start lowering daily budgets depending on if you're overpricing or underpricing in itself. This becomes mega useful if you go to this next tab. This just gives you the overview of what's going on in a nutshell. So what we can see here is we have the monthly budget of 6000. We have the date here which again is self referencing and what's cool about this self referencing date is we can do AV look up against this date so because this date here exactly matches. The eighth of the 15th here we can do AV look and say what is today's revised budget that we should going after. So you can see here the formula. Which therefore goes and pretty much says for this day, what is the new revised spend that we should be spending, So ��257.67, which is ��257.67 and how much the current spend is for this month. Now with this graph here, we literally just have the cumulative spend and the expected cumulative spend is so in red this is how much we should be spending and then in blue this is how much actually we have been spending. What we can do now is obviously create Zapier integrations. Example which can pull into Slack e-mail to show each day and give an update whether we're overpricing or underpricing. But also helps to say if we're having a good month and we're hitting targets and say for example, we want to go up to ��8000 per month. What is the revised? Daily budget which we need to go up to and start spending and keep an eye on. Or say for example, we're cutting back, we only want to spend ��3000 in one month. We know that we need to now change and pivot towards ��70 per month because we're massively over pacing and every day for example, that we add in. More so say for example, we had 116 lbs. Or let's go just pull this down to the 31st and let's just say that we spent ��116 every day. This now has the target 4000. Sorry, 3000, we spent way over that. Let's go back to the 6000 mark. Hmm, they expected spend 6000, we still wouldn't hit every day. So as the days go on, obviously we can start seeing the days appear in this graph. If you guys want to have access to this, what I'll do is I'll link to this template with all the formulas within it along with the test data in it if you guys want to play around with it. Ultimately, it's a really nice way of looking behind the scenes and making sure you have your finger on the pulse and making sure that you're not overpricing, not underpricing. And if you have got any changes, it allows you then to go in and make those changes before any issues happen towards the end of the month when you haven't got time to spend more. Or send less. Also, what can happen is every month we'd like to create a legacy year so we can see every month how much we've been spending, looking at the conversion data of the back of that spending. Look at the average cost per lead. Obviously all that stuff is in your CRM as well, but it gives you a really top level overview of the different channels, what the top line Cpl. is, and ultimately allows you to start saying, OK, maybe we should look at reallocating some of this budget to a different channel which is performing better from a performance marketing standpoint. Hope you found that useful. I hope it's made your life slightly easier with a bit of automation. And any questions, feel free to ask them in the comments below. And until next time guys, have a great week.