This post is part of 3Q’s Brand Protection series, in which we tackle a range of issues to help companies safeguard their most important asset: their brand’s reputation. For a full list of posts, click here

If I had to pick one hot topic in marketing from 2017, it would have to be brand safety. Specifically, the controversies behind AT&T and Johnson & Johnson pulling ads from YouTube and the rise of Breitbart on the Google Display Network have changed the landscape. More than ever, clients demand assurances from SEM practitioners and agencies that their ads do not serve on distasteful websites.

brand protection

Even as advertisers flee controversial sites by the thousands, how confident are you that your clients aren’t showing up on Daily Stormer or other related websites as you read this? In this article, I’ll share how we solved this problem at 3Q Digital using Google AdWords scripts. I’ll also share the source code so that you can go the extra mile in protecting your clients on GDN.

Note: this script was created before the introduction of Account Level Exclusions. And while ALE is a great feature, you may still find value in the solution outlined here.

Outlining the Problem

The problem that we are attempting to solve is as follows:

Even the best-intentioned / thorough SEM manager can make the mistake of missing one GDN campaign / ad group in blocking questionable placements.

And one mistake by omission — e.g. forgetting to exclude placements like Breitbart — can jeopardize client relationships.

Because no individual should be allowed to introduce that level of uncertainty into the client relationship, we decided to craft a solution that would take the decision out of the hands of the SEM manager and place the responsiblity within the confines of automation.

(Instead of hoping that hundreds of people simulateously get a routine task correct, we’ll offload that task to computers.)

Essentially, we need a Google AdWords script that can keep track of an unlimited amount of accounts and ensure that our ads are far away from Breitbart and other questionable sites.

Anticipating the Edge Cases

Before getting to the code, it’s important to sketch out the possible edge cases that we might encounter.

  • How do we account for users who don’t want to exclude Breitbart? Some clients might actually want their ads to show on the site.
  • How do we account for incremental changes within an account? It is not enough to post changes on a daily basis – new GDN campaigns might be created at the start of the day and run for up to 24 hours.
  • How do we account for new accounts that join the agency MCC? We need a solution that accounts for the newest client, as well as the oldest.
  • Finally, how do we create a system that can scale? Even though our current use case concerns one domain (i.e. breitbart.com), there is a strong possibility that other domains will eventually become cause for concern/action.

Breaking Ground on a Solution

Now that we have our problem defined and at least the most obvious edge cases considered, lets get to the implementation. Here’s the approach that we used to manage GDN exclusions at scale:

By leveraging Google Sheets and leveraging AdWords MCC labels, we created a robust solution for ensuring that odious placements are restricted from serving from client GDN campaigns.

The Breitbart Script in a Nutshell

  • Create a Google Sheet that serves as a repository for unwanted GDN placements. Leveraging a Sheet will allow the scope to scale over time while also allowing non-developers to have a say in what gets restricted.
  • Leverage AdWords My Client Center (MCC) labels to serve as a way of delineating which accounts fall under the GDN exclusion script and which don’t.
    • All accounts are opted in by default to have a ‘Exclude Breitbart‘ label
    • Accounts that do not want to exclude Breitbart simply need to tag themselves with a ‘Breitbart Opt-Out‘ label
  • Create the MCC-level labels just outlined.
  • Create a Google AdWords script that manages the auto-labeling of all accounts within the MCC.
    • This logic is so specific and time-sensitive that we shouldn’t risk this task timing out
  • Create another Google AdWords script that accomplishes the following:
    • Monitors all appropriately labeled accounts
    • Checks for the existence of a shared GDN Excluded Placements list (e.g. ‘3Q GDN Shared Exclusions List’)
      • If this list does not exist, create the list and add the list of excluded GDN placements.
      • If the list does exist, repopulate it with the latest entries in the respective Google Sheet.
    • Iterate over all enabled GDN campaigns and test whether each campaign has the global shared exclusions list from step 2.
      • If the campaign does not have the list, then we opt it in.

Let’s tackle the account-labeling script first since it’s relatively straightforward.

Script #1: MCC Account Labeler

Purpose: Iterate over all MCC accounts and apply an opt-in label if said account does not already have an ‘opt-out’ label.

Frequency: Hourly

function main() {
var labelIter = MccApp.accountLabels().get();

var accountIter = MccApp.accounts()
.withCondition(“LabelNames does_not_contain ‘3Q Breitbart Opt-Out'”)
.get();

while (accountIter.hasNext()) {
var account = accountIter.next();

MccApp.select(account);

try {
account.applyLabel(“3Q Global Breitbart Placements”);
} catch (err) {
// Do something with the error
}
}
}

Script #2: GDN Shared Placements Exclusion Manager

Purpose: Iterate over all MCC accounts, enforce the existence of a ‘Breitbart Opt-Out‘ Shared Placements exclusion list, and opt all enabled GDN campaigns into having this list.

Frequency: Hourly

We’ll unpack this in steps because there is a lot going on! Let’s start with outlining our constant values — namely our Google Sheet URL and AdWords MCC label criteria.

Note: You don’t necessarily need the label IDs as I have outlined below — names work just fine.

var PLACEMENT_SHEET = “ENTER_YOUR_URL”;
var excluded_placement_lists = [
{label: “3Q Global Breitbart Placements”, name: “3Q Global Breitbart Placements”, id: 1232323 }];

Note: This AdWords Script makes use of the underscore.js library — which is free and available at my Github. Just copy and paste the code at the end of the script.

What the spreadsheet looks like

Deciphering the main function

Essentially, the core aspect of our script is doing the following:

1) Selecting the actual AdWords Labels (e.g. ‘Breitbart Exclusion’)

2) Iterating over all accounts that have said label that reside in the MCC

3) Passing a reference of the label down to the actual AdWords account in question. This allows us to continue processing the account in a clean way,

function main() {
var labelSelector = MccApp
.accountLabels()
.withIds(_.pluck(excluded_placement_lists, ‘id’))
.get();

while (labelSelector.hasNext()) {
var label = labelSelector.next();

var accountIter = label
.accounts()
.get();

while (accountIter.hasNext()) {
var account = accountIter.next();
MccApp.select(account);

var theList = _.filter(excluded_placement_lists, function(item) {
return item.id === label.getId();
});

if (theList.length > 0) {
processClientAccount(theList[0]);
}
}
}
afterProcessAllClientAccounts();
}

Dealing with Actual Client Accounts

The code below does the core work that keeps clients from freaking out.

Essentially, it follows the following process:

  • Search for the existence of the GDN Shared Placement Exclusion List — in our case, the ‘Breitbart Exclusion
    • If the list exists, update its contents with what is in the Google Sheet
    • If the list does not exist, then create the list and populate it with what is in the Google Sheet
  • Iterate over all active campaigns — GDN or not — and add the exclusion list. In reality, we could have made these conditions tighter but opted for the easy solution to save time
  • The function below loadPlacements() loads the list of exclusions from the Google Sheet (hopefully efficiently)

function processClientAccount(list) {
var clientAccount = AdWordsApp.currentAccount();

// check if account has the negative exclusion list
var placementIter = AdWordsApp
.excludedPlacementLists()
.withCondition(‘Name = “‘ + list.name + ‘”‘)
.get();

if (placementIter.totalNumEntities() === 0.0) {
// no? – create the new exclusion list
var exclusionList = AdWordsApp
.newExcludedPlacementListBuilder()
.withName(list.name)
.build();

var placements = loadPlacements(list.name);
operationResult = exclusionList.isSuccessful();

if (operationResult) {
var actualList = exclusionList.getResult();

if (placements && placements.length > 0) {
actualList.addExcludedPlacements(_.pluck(placements, ‘url’));
}

var campaignIter = AdWordsApp
.campaigns()
.withCondition(“Status = ENABLED”)
.get();

while (campaignIter.hasNext()) {
var campaign = campaignIter.next();

campaign.addExcludedPlacementList(actualList);
}
}
} else {
while (placementIter.hasNext()) {
var placements = loadPlacements(list.name);

var sharedList = placementIter.next();

var urlIter = sharedList
.excludedPlacements()
.get();

// remove current placements
while (urlIter.hasNext()) {
var placement = urlIter.next();
placement.remove();
}

// add new placements
if (placements && placements.length > 0) {
sharedList
.addExcludedPlacements(_.pluck(placements, ‘url’));
}

// opt in all current campaigns
var campaignIter = AdWordsApp
.campaigns()
.withCondition(“Status = ENABLED”)
.get();

while (campaignIter.hasNext()) {
var campaign = campaignIter.next();

campaign.addExcludedPlacementList(sharedList);
}
}
}
}

function loadPlacements(sheetName) {
var spreadSheet = SpreadsheetApp.openByUrl(PLACEMENT_SHEET);
var sheet = spreadSheet.getSheetByName(sheetName);

try {
var placementDataRange = sheet.getDataRange();
var placements = [];
var values = placementDataRange.getValues();

for (var i = 0; i < values.length; i++) {
placements.push({
url: values[i][0],
});
}

placements.shift(); // Get rid of the header row
return placements;
} catch(err) {
Logger.log(“Error! ” + err);
}
}

Cleaning up

Finally, we end the script with a possibly counter-intuitive task — we need to remove the GDN exclusion list from those accounts that have opted-out.

We do this by iterating over all MCC accounts that do not have the original label we selected. Since we already have a script running hourly that flags unidentified accounts and opts them in, we can safely assume that accounts that do not have our label have opted out.

Additionally, because it’s unclear exactly when an account opted out from our label, we can’t simply filter on that label. We need to effectively reverse the work of the last time our exclusion script completed.

function afterProcessAllClientAccounts() {
// this is where we do the reverse process of removing shared lists from accounts that do NOT have the account label

_.each(excluded_placement_lists, function(list) {
var accountSelector = MccApp
.accounts()
.withCondition(“LabelNames DOES_NOT_CONTAIN ‘” + list.label + “‘”)
.get();

while(accountSelector.hasNext()) {
var account = accountSelector.next();
MccApp.select(account);

var placementIter = AdWordsApp
.excludedPlacementLists()
.withCondition(‘Name = “‘ + list.name + ‘”‘)
.get();

while (placementIter.hasNext()) {
var placementList = placementIter.next();
var placementIter = placementList
.excludedPlacements()
.get();

while (placementIter.hasNext()) {
var placement = placementIter.next();
placement.remove();
}
}
}
});
}

Next Steps

Believe it or not, that’s all there is to it! If you follow the steps outlined above, you’ll have a state-of-the-art GDN exclusions placement system.

Now you might be thinking, what is the point of this now that Account Shared Exclusions are a thing? Sadly, we still need to rely on humans to do the opting in, which rarely happens 100% of the time.

Run this script to ensure that both your big and small clients enjoy the same level of protection needed in this challenging time.

1 Comment

  1. Jane November 29th, 2017

    Hahaha.. This is great. I got nailed on this today through a twitter name & shame of a client via a new campaign I had forgotten to run my exclusion list on. Thanks for the perspective and the script. I’ll give it a try.

Leave a Comment

Derek Martin
Derek joined the online marketing industry in 2013 and 3Q Digital in February of 2017. He has directly managed paid search accounts across many industries, including education, retail, and professional services. Prior to his entry into paid search, he worked as a certified public account for a retail investment trust company in Los Angeles. A 2006 graduate of Villanova University, he is a devout Wildcat and also enjoys MMA.