Google Ads Script For Cleaning GDN Placements By Placement`s Titles

An easy but very routine solution could be weekly manual cleaning of all placements. It could be in the next flow:

1. Get placement report for last week or day (depends on how much traffic you get from GDN)
2. Click in each placement with clicks or more than 3 impressions to check placement
3. If placement is about one of the common “bad” fields that often waste money (sites about games, recipes, films, funny videos, etc), copy those placements to the Negative placement list in the Shared library.

This is not rocket science, but it`s hard, needs time and we have to do it if we do not want to waste money.
That’s why we decided to develop a Google Ads script that makes the same process. There are ready to use AdWords scripts below.

The Main Idea

The main idea is that bad placements get the most traffic from organic searches. And for SEO is highly important to set the right words in the Title HTML tag.

For example:

If the webmaster wants to promote a site for search terms about games or fun, he (or she) has to include those words in the site`s title tag.

So all we need is to compare placement`s title tags with a predefined list of bad word templates. It`s enough to remove placement if the title tag consists of the word “game” or “GTA”, etc in any place.

That`s why our script parses placements titles, compares them with bad words templates and adds bad placements to the Negative list in the Shared library

Script Mechanism

For collecting data about placements and for cleaning we use separate scripts (because we need time delay for parsing placement`s titles). Titles are parsed via Google SpreadSheet.

The parsing process takes time (depends on placement count), that`s why we decided to make 2 versions of the script

1. The impression script version analyzes placements with more than 3 impressions but without clicks and conversions (for yesterday’s placement report) and works on a daily basis
2. Click script version analyzes placements with clicks but without conversions (for 7 placement reports) and works weekly

Actually, we could analyze only placements with clicks. But in this way, we remove bad placement only when it already spend our money. So we make an additional script version which analyses and cleans placements with impressions. And we remove bad placements before it spends money.

Final Script Process:

Run Report script (send data to Google SpreadSheet) → Google SpreadSheet parses placement titles → Run Cleaning script (compare parsed titles and bad words template, set also in Google SpreadSheet).

Script Schedule

As I mentioned for title parsing we use Google SpreadSheet. And parsing takes time (usually 50-100 placement titles per hour). If you have a lot of GDN traffic we recommend scheduling script in a next way:

For Impression version

• First Run – Start report script manually (after 3 a.m., also recommended before 3 p.m.)
• Set for cleaning script schedule at 2 a.m. daily
• Set for report script schedule at 3 a.m. daily

In this way, time delay between sending data to Google SpreadSheet = time on parsing process and cleaning is 23 hours.

For Click version

• Set for report script schedule at 3 a.m. on Monday weekly
• Set for cleaning script schedule at 3 a.m. Tuesday weekly

Here an example for scripts schedule:

Google SpreadSheet For Parsing And Store Bad Words Templates

SpreadSheet consists of 3 sheets:

• Source
• BadWords
• RawDataReport

“Source” sheet

Column A — placement`s domain names

Column B — parsed titles (by Google Spreadsheet formulas)

“BadWords” sheet

We added common templates for bad words. You can add additional templates in list. Just add new templates in column A. You also can remove some templates (for example if you work with woman audience and want to show ads on recipes sites).

“RawDataReport” sheet

Here first report script sends data from placement report in AdWords (filtered by impressions or clicks as set).

Warning. Do not change “Source” and “RawDataReport” sheets.

Impressions Report Script

// Copyright 2017, Lira ltd. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the “License”);
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an “AS IS” BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
* @name AdWords GDN Placement Cleaner For Impressions (Report) script
*
* @overview The script allows you to manage Google Display Network
* GDN placements and remove common non effective placements about
* games, recipes, films, etc
* See
* https://lira.agency/
* for more details.
*
* @author Artem Akulov [help@lira.agency]
*
* @version 1.0
*
* @changelog
* – version 1.0
* – Released initial version.
*/

// The spreadsheet for parse GDN titles. This should be a copy of
// https://docs.google.com/spreadsheets/d/1IawBXlLeBsYBi9A45HQadTlACEgtMhY4jG7nw773hII/edit?usp=sharing

//Set link to your copy of Google Spreadsheet above
var SPREADSHEET_URL = ‘https://docs.google.com/spreadsheets/d/1r0WMGA-vJaa8iH7gEd9IjPiKOwovepyQ7QI5fSItNM4/edit?usp=sharing’;

var RawDataReport_Name = “RawDataReport”; //Don’t change the name of the sheet if you use standart docs template
var RawDataReportSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName(RawDataReport_Name);

function main() {
createReports();
}
//Get placements with 4 and more impressions without clicks for yesterday.
//For Clicks it`s recommended to set a copy of current script.
//Second script works on weekly basis
function createReports() {
var RawDataReport = AdWordsApp.report(“SELECT Domain, Clicks, Impressions ” + “FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ” + “WHERE Impressions > 3 ” + “AND Clicks < 1 ” +”AND Conversions < 1 ” + “DURING YESTERDAY”);
RawDataReport.exportToSheet(RawDataReportSheet);
}

Impression report script on GitHub

Clicks Report Script

// Copyright 2017, Lira ltd. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the “License”);
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an “AS IS” BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
* @name AdWords GDN Placement Cleaner For Impressions (Report) script
*
* @overview The script allows you to manage Google Display Network
* GDN placements and remove common non effective placements about
* games, recipes, films, etc
* See
* https://lira.agency/
* for more details.
*
* @author Artem Akulov [help@lira.agency]
*
* @version 1.0
*
* @changelog
* – version 1.0
* – Released initial version.
*/

// The spreadsheet for parse GDN titles. This should be a copy of
// https://docs.google.com/spreadsheets/d/1gECAF4iatFWDEnwKaZdnLLBCoW2-O6uOuZMOpQ94JaI/edit?usp=sharing

//Set link to your copy of Google Spreadsheet above
var SPREADSHEET_URL = ‘https://docs.google.com/spreadsheets/d/14zZoMrMV1K41zMqRjla9jfIBOOUX7IRawYGIqlp3mwY/edit?usp=sharing’;

var RawDataReport_Name = “RawDataReport”; //Don’t change the name of the sheet if you use standart docs template
var RawDataReportSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName(RawDataReport_Name);

function main() {
createReports();
}
//Get placements with 1 and more clicks over last 7 days.
//It`s recommended to schedule script weekly
function createReports() {
var RawDataReport = AdWordsApp.report(“SELECT Domain, Clicks ” + “FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ” + “WHERE Clicks > 0 ” + “AND Conversions < 1 ” + “DURING LAST_7_DAYS”);
RawDataReport.exportToSheet(RawDataReportSheet);
}

Clicks report script on GitHub

Cleaning Script

// Copyright 2017, Lira ltd. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the “License”);
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an “AS IS” BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
* @name AdWords GDN Placement Cleaner For Impressions (Report) script
*
* @overview The script allows you to manage Google Display Network
* GDN placements and remove common non effective placements about
* games, recipes, films, etc
* See
* https://lira.agency/
* for more details.
*
* @author Artem Akulov [help@lira.agency]
*
* @version 1.0
*
* @changelog
* – version 1.0
* – Released initial version.
*/

//Set Negative Placement list in Shared library for script`s results.
//If list in not exist script create a list with that name
var EXCLUDED_PLACEMENT_LIST_NAME = ‘PlacementCleanerByTitleList’;

// The spreadsheet for parse GDN titles. This should be a copy of
// https://docs.google.com/spreadsheets/d/1IawBXlLeBsYBi9A45HQadTlACEgtMhY4jG7nw773hII/edit?usp=sharing

var SPREADSHEET_URL = ‘https://docs.google.com/spreadsheets/d/1r0WMGA-vJaa8iH7gEd9IjPiKOwovepyQ7QI5fSItNM4/edit?usp=sharing’;
//Get Data with Placement`s Titles
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var urlTitles = getSheetData(ss, 0);
//Massive on negative templates which will be compared with parsed GDN placement`s Titles.
var badWords = getSheetData(ss, 1);

function main() {
var excludePlacementArray = [];
for (var i = 0; i < urlTitles.length; i++) {
if (containsAny(urlTitles[i][1].toLowerCase(), badWords)) {
excludePlacementArray[excludePlacementArray.length] = urlTitles[i][0].toString();
}
}
Logger.log(excludePlacementArray);//In Log you will see a result list of new excluded placements
addNegativeKeywordToList(excludePlacementArray);
}
function getSheetData(ss, sheetIndex) {
var sheet = ss.getSheets()[sheetIndex];
var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
return range.getValues();
}
function containsAny(str, substrings) {
for (var i = 0; i != substrings.length; i++) {
var substring = substrings[i];
if (str.indexOf(substring) != – 1 && str.indexOf(‘mobileapp::’) == -1) {
return substring;
}
}
return null;
}
function addNegativeKeywordToList(negativePlacements) {
var excludedPlacementListIterator =
AdWordsApp.excludedPlacementLists().withCondition(“Name = ‘” + EXCLUDED_PLACEMENT_LIST_NAME + “‘”).get();

if (excludedPlacementListIterator.totalNumEntities() == 1) {
var excludedPlacementList = excludedPlacementListIterator.next().addExcludedPlacements(negativePlacements);
} else {
AdWordsApp.newExcludedPlacementListBuilder()
.withName(EXCLUDED_PLACEMENT_LIST_NAME)
.build().getResult().addExcludedPlacements(negativePlacements);
}
}

Cleaning script on GitHub

Script creates new negative placements list (if it does not exist) and adds new bad placements to it. You need to implement this result negative placements list to all GDN campaigns you need.

Script adds excluded placements to log.

Additional links

• Google SpreadSheet for Impressions report (ready to use) [Eng version]
• Google SpreadSheet for Clicks report (ready to use) [Eng version]
• GitHub with AdWords Scripts

Conclusion

Those scripts are not full substitution of human work and human brain, but it make placement cleaning work much more easier. So qualified spec may spend more time on experiments and analytics.