AdWords Script to Exclude Locations Automatically

Recently,  we had an interesting case with one of our clients where he always needed to exclude different locations from his AdWords 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 of locations actually takes time and mistakes can happen, so we had to think of a way to automate this process. We developed a simple AdWords 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());
 }
}

mm
Ahmed launched his first digital campaign in 2013 through participating in Google Online Marketing Challenge. He competed with over 12,000 students from 80 countries to win the first place in Africa & MENA region and one of the best five campaigns in the world. Today, he works as an Associate Digital Director at Keyade Middle East.

6 Comments

    • Hello,

      Are you targeting Argentina as a country in your campaigns? If no, you should target the entire country and will be excluding the cities separately.

      I tried to run the script again and it’s working for me. Please give me edit access to your spreadsheet and share with me the script after your modification and will try to run it on my accounts.

Leave a Reply

Your email address will not be published. Required fields are marked *