Written byTom MeyrickSenior Technical Analytics Executive
Most non-essential gathering places are closed under the UK’s national lockdown. If your business relies on people being out and about, you may end up paying for ads in regions where buyers can't use your services - especially as the country begins to open up again over the next few months.
Our Innovation Team is here for you. They’ve designed a solution to this problem, in the form of a Google Ads script. You’ll find the script at the bottom of this article. But first - let’s take a look at how it works.
If none of the below makes sense, don’t worry - it’s complicated stuff. And we’re more than happy to help. If you need assistance with setting up scripts, just get in touch...
When we’re not experiencing a national lockdown the UK government reviews the tier system on a regular basis. You'll find the info here, on the .gov site. We’ve converted the cities and regions in the higher tiers using Google Ads’ location code system.
This script works by enabling and disabling locations across the entire account. We’ll use two sheets, one to store the tier location data and another for any locations we want to ignore. If your account never advertises in Northern Ireland, for example, you can make a copy of the sheet and add this location and its matching code.
If there are no such concerns, simply leave the link as is.
Query the Sheets
To query each sheet, we initialize our pullSheetData() function.
Ad Scripts has some useful methods for working with Sheets. We’ll store our sheet URLs in variables and use SpreadsheetApp.openByUrl() to gain access to each sheet.
Using getRange(), we can tell Ad Scripts which part of the sheet we want to import. We could hard code a range here. But then we’d need to update this value every time we add or remove a location. The getLastRow() method is a neater solution. We can concatenate the returned integer to our range string.
The sheet data will arrive in Ad Scripts in the form of a nested array.
Arrays are used to store multiple values under one heading. A nested array is an array of arrays. But they can be tricky to work with, particularly within Ad Scripts where we don’t have access to shiny new ES6 methods like flat(). Here we’re going to make use of a recursive function to convert this nested array into a singularity.
We’re then going to wrap the query in an if statement and use some conditional logic to check that we’re getting data back from the sheets. We’ll then call the function twice, passing in each of our sheets and starting row numbers, and save the returned arrays to two variables: tierData and exclusionData.
Check for Existing Exclusions
We want to remove all restrictions every time this script runs.
Enter our resetCampaigns() function. We’ll use it to loop over all the enabled campaigns in the account.
We’ll use the targeting method to check for excluded locations. If we find one, we’ll save the id to a variable called resetLocationId.
The exclusionData array contains our excluded locations. We’ll use it to ensure they aren’t reset with the rest of the locations in the account. To do this we pass resetLocationId and exclusionData into our notInArray function. If a match is found, this location will remain off-limits.
We can then log a message announcing campaigns have been re-enabled.
Exclude Tier 3 Locations
Let’s think about how we can ensure all locations in tierData are temporarily removed from the account. As before, we need to select all our newly enabled campaigns, loop over them and assign each to a campaign variable.
Then we loop over our tierData array and use the built-in excludeLocation() method to remove each location from our campaigns.
We access the values stored in an array using square bracket notation. Here the number within the bracket refers to the placement of each value in the array. The “i” in the for loop goes up by one each time the loop runs.
The loop will continue to execute for the length of the tierData array. The excludeLocation() method receives all the elements in the array and removes all of them from each of the campaigns.
Again, we’ll log a message just to keep track of what’s happening. You can check your logs in the “All Bulk Actions” section tab in the Scripts window. This will provide a breakdown of changes over time.
Now we want to run each of these functions.
We want resetCampaigns() to execute first, followed by removeLocations(). But we have one last piece of conditional logic to take care of.
By setting removeLocations() to run only if there is data in the Tier sheet, we’re able to reset all the campaigns and exit with a final message. This will (hopefully) indicate the return of something approaching normality.
Simply log in to your Google Ads account and head to the Tools and Settings tag at the top. In the dropdown menu, you’ll see the Scripts option. Click this, then click the blue and white cross on the left of the screen to create a new script.
Remove the default function main() and paste in the script below. You’ll then need to authorise it. Once that’s out the way, navigate back to the main Scripts window and click the pencil icon next to “Frequency”. To make sure you have the most up to date dataset, we recommend you select “daily”.
As mentioned previously, if you have a list of locations you want to stay excluded you can copy the existing exclusions sheet and add them here – along with the matching Google location code. You can then paste your new Sheets URL in place of the original.