AdWords Script For Cleaning GDN Placements By Placement`s Titles

Artem Akulov

There is a huge amount of bad placements in Google Display Network (GDN). But in same time GDN is good source for ppc lead generation. If you are not use it, you do not get all leads and conversions you could. But if you use GDN but do not clean you placement`s list, you waste your money for sites about games, recipes, films, fun, etc.

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

Get placement report for last week or day (depends on how many traffic you get from GDN)
Click in each placement with clicks or more than 3 impression to check placement
If placement is about one of common “bad” fields that often waste money (sites about games, recipes, films, funny videos, etc), copy those placements to Negative placement`s list in 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 AdWords script makes same proces. There are ready to use AdWords scripts below.

The Main Idea

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

For example:

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

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

That`s why our script parses placements titles, compares it with bad words templates and adds bad placements to Negative list in 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.

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

Impression script version analyzes placements with more than 3 impressions but without clicks and conversions (for yesterday placement report) and works on daily basis
Click script version analyzes placements with clicks but without conversions (for 7 placement report) 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 additional script version witch 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`s titles per hour). If you have a lot GDN traffic we recommend to schedule script in 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:

Schedule Example For Adwords Scripts

Google SpreadSheet For Parsing And Store Bad Words Templates

SpreadSheet consists of 3 sheets:

Source
BadWords
RawDataReport

Google SpreadSheet for parsing placement titles

“Source” sheet

Column A — placement`s domain names

Column B — parsed titles (by Google Spreadsheet formulas)

“BadWords” sheet

Bad Words Templates

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

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.

« Guide: Call Reporting and Call Conversions for Google Adwords

Manual For Analyzing Of Advertising Campaigns In Which Conversions Are Configured Wrong »

adwords script Google AdWords