Optimization Up

Recently,  we had an interesting case with one of our clients where he always needed to exclude different locations from his Google Ads campaigns based on the business availability. This used to happen very often and the client used to come back to us every couple of days asking to exclude and include different certain locations.

Excluding dozen locations actually takes time and mistakes can happen, so we had to think of a way to automate this process. We developed a simple Google Ads script linked to a Google spreadsheet where the client can just change the status of his locations to “Excluded” or ” Targeted” and changes will be applied to the campaigns.

How’s the script work?

The script fetches its data from a spreadsheet which consists of two sheets:

#1 The Locations Sheet where we list all of the available locations supported by the client business along with their IDs.

#2 The Master Sheet where we only pull the excluded locations from the location sheet (using an array formula) and feed them into the script.

The source code to link the spreadsheet to your AdWords script as follows:

function main() {

 var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1xxxxxhtN8ub6mS8Nn7CktFcajBw8iGfTI0Ns7KGLulNgcMY/edit#gid=0";
 var SHEET_NAME = 'Master';
 var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
 var sheet = spreadsheet.getSheetByName(SHEET_NAME);
 var data = sheet.getRange("A:C").getValues();

The first thing the AdWords script does, it removes all the excluded locations from the specified campaigns. So, if the client does change the status of any of the locations from “Excluded” to “Targeted”. We make sure this is also applied through our script and we target the location back again.

// REMOVING EXCLUDED LOCATIONS
 
 var campaignIterator = AdWordsApp.campaigns()
 .withCondition("Status = ENABLED")
 .get();
 
 while (campaignIterator.hasNext()) {
 var campaign = campaignIterator.next();
 var excludedLocations = campaign.targeting().excludedLocations().get();
 while(excludedLocations.hasNext()){
 var excludedLocation = excludedLocations.next();
 excludedLocation.remove();
 }
 }

The script then starts iterating over the data in your spreadsheet and if the location column is empty it stops executing at this point.  Otherwise, the script will finish its job and exclude all the locations added in your sheet from your AdWords campaigns.

for (i in data) {
 // SKIP HEADER ROW
 if (i == 0) {
 continue;
 }
 var [location, locationId] = data[i];

 // BREAK IF LOCATION IS EMPTY
 if (location == "") {
 break;
 } else {
 
 // ADDING EXCLUDED LOCATIONS

 var campaignIterator = AdWordsApp.campaigns()
 .get();
 while (campaignIterator.hasNext()) {
 var campaign = campaignIterator.next();

 campaign.excludeLocation(parseInt(locationId));
   }
  }
 }

Finally, the AdWords script gives you a report of all the excluded locations in your campaigns or across your account. You can find the report in your sheet in columns “E” and “F” in the master sheet.

Also, don’t forget to schedule your script to run every 24 hours or the time period that suits you. So, whenever a change happens in the spreadsheet, it applies to your AdWords campaigns.

// GETTING EXCLUDED LOCATIONS
 var locationIterator = campaign.targeting().excludedLocations().get(); 
 sheet.getRange("E:F").clearContent();
 sheet.getRange("E1").setValue("Current Excluded Location ID");
 sheet.getRange("F1").setValue("Current Excluded Location Name");
 for (var row = 2; locationIterator.hasNext(); row ++) {
 var excludedLocation = locationIterator.next();
 sheet.getRange("E" + row).setValue(excludedLocation.getId());
 sheet.getRange("F" + row).setValue(excludedLocation.getName());
 }
}

Here’s the source code of the full AdWords script. You can make a copy of the spreadsheet and start testing it out on your account and let us know how it goes.

/* Copy Right 2016, Ahmed Ali, https://optimizationup.com */
function main() {
 var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1pB22uvAZICUIWpJ3ulP3LPffVd5B2mreKXc9g14SXP0/edit#gid=2136453441";
 var SHEET_NAME = 'Master';
 var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
 var sheet = spreadsheet.getSheetByName(SHEET_NAME);
 var data = sheet.getRange("A:C").getValues();

 
 // REMOVING EXCLUDED LOCATIONS
 
 var campaignIterator = AdWordsApp.campaigns()
 .withCondition("Status = ENABLED")
 .get();
 
 while (campaignIterator.hasNext()) {
 var campaign = campaignIterator.next();
 var excludedLocations = campaign.targeting().excludedLocations().get();
 while(excludedLocations.hasNext()){
 var excludedLocation = excludedLocations.next();
 excludedLocation.remove();
 }
 }

 for (i in data) {

 // SKIP HEADER ROW
 if (i == 0) {
 continue;
 }
 var [location, locationId] = data[i];

 // BREAK IF LOCATION IS EMPTY
 if (location == "") {
 break;
 } else {
 // ADDING EXCLUDED LOCATIONS

 var campaignIterator = AdWordsApp.campaigns()
 .get();
 while (campaignIterator.hasNext()) {
 var campaign = campaignIterator.next();

 campaign.excludeLocation(parseInt(locationId));
 }
 }
 }
 
 // GETTING EXCLUDED LOCATIONS
 var locationIterator = campaign.targeting().excludedLocations().get(); 
 sheet.getRange("E:F").clearContent();
 sheet.getRange("E1").setValue("Current Excluded Location ID");
 sheet.getRange("F1").setValue("Current Excluded Location Name");
 for (var row = 2; locationIterator.hasNext(); row ++) {
 var excludedLocation = locationIterator.next();
 sheet.getRange("E" + row).setValue(excludedLocation.getId());
 sheet.getRange("F" + row).setValue(excludedLocation.getName());
 }
}

Ahmed Ali

Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest product GA4 Auditor and discover all my products here.