Scripting Strategies: Identifying Inappropriate Ad Placements in Google AdWords

The Problem with GDN

Our team explored streamlining GDN placement cleaning through JavaScript scripts within Google AdWords, inspired by Alexey Jaroshenko and Derek Martin's approaches, integrating their ideas and components.

The Google Display Network (GDN) is a key component of Google AdWords, enabling businesses to showcase display ads on numerous websites worldwide. Despite its passive nature in contrast to the Search Network, GDN reaches over 90% of internet users, offering immense potential for targeted advertising.

The Problem with GDN

The challenge with online display ad placements is the time-consuming task of filtering through numerous placements to avoid unfavorable associations for your company, product, or service. Marketers, SEO experts, writers, web developers, and other team members often spend hours organizing this process, even with filters in place.

Google AdWords offers the capability to manage ad campaigns using JavaScript directly within its interface, making the prospect of using scripts to streamline GDN placement cleaning promising. Our team embarked on researching this area, examining previous attempts to address this issue with scripts.

Two options stood out: one developed by Alexey Jaroshenko and the other by Derek Martin. Drawing inspiration from their work, our team developed a solution integrating some of their ideas and script components.

General Solution

For a comprehensive understanding of our step-by-step solution and the available options, it’s crucial to consider the following key details.

Main Conditions

The script utilizes the automatic placement report in Google AdWords for a specified timeframe. It generates samples based on selected placements that meet specific criteria:

  1. Expenses exceed a certain threshold (X USD) with no conversions.
  2. Conversion cost surpasses a designated amount (X USD).
  3. Placements have more than a specified number of impressions, with a CTR below a certain percentage (Y%) and no conversions.
  4. Placements have more than a set number of impressions, with a CTR exceeding a certain percentage (Y%) and no conversions.

These criteria are customizable within the Google Spreadsheet’s “config” list, from which the script imports data.

Unwanted Domains

Similarly, the script conducts an analysis to identify instances of unwanted “bad” words in domain names without conversions. These undesirable markers are specified within the “exclude_domain” list.

Lists of Exceptions

The chosen placements are appended to the list of exceptions within the shared library of the account.

For each sample, a distinct list is created within the shared library (these must be created initially), with customizable names. However, they must contain – list1, list2, list3, list4, and list5. Detailed instructions are provided within the document itself (refer to images 2 and 3).

Mobile applications, anonymous.google, and YouTube can be added to the “except_domain” list.

Notification Email with Results

Once the script is executed, an email is automatically sent to the contact address specified on the “config” list. This email includes statistics for the first two samples and the count of excluded placements for the remaining selections, along with a link to the Google Spreadsheet. It’s important to note that you must configure the link to the Google Spreadsheet and the project title within the corresponding lines of the script.

Step-by-Step Solution

  1. Provide the Google Spreadsheet link and project name:

var config = {
SPREADSHEET_URL: ‘Link to the Google Spreadsheet’,
PROJECT_NAME: ‘Name of the project’
2.Specify the title of the list containing markers for “bad” domains in the fifth placement selection:


function getConfigData(spreadsheet) {
var excludeDomainSheet = spreadsheet.getSheetByName(‘exclude_domain’),
values = excludeDomainSheet.getSheetValues(1, 1, excludeDomainSheet.getLastRow(), 1);
config.exclude = [];
if (typeof values == “object”) {
for (i = 0; i < values.length; i++) {
config.exclude.push(values[i][0]);
}
}
Logger.log(config.exclude)
}
3.Indicate the title of the list for domains not included in the statistical analysis:


var exceptDomainSheet = spreadsheet.getSheetByName(‘except_domain’),
values = exceptDomainSheet.getSheetValues(1, 1, exceptDomainSheet.getLastRow(), 1);
config.except = [];
if (typeof values == “object”) {
for (i = 0; i < values.length; i++) {
config.except.push(values[i][0]);
}
}
Logger.log(config.except)
4.Specify selection criteria:


var configSheet = spreadsheet.getSheetByName(‘config’);
config.email = configSheet.getRange(1, 2, 1, 1).getValues();
config.timeperiod = configSheet.getRange(2, 2, 1, 1).getValue();
config.listCost = configSheet.getRange(3, 2, 1, 1).getValue();
config.list2ConversionCost = configSheet.getRange(4, 2, 1, 1).getValue();
config.list3Impressions = configSheet.getRange(5, 2, 1, 1).getValue();
config.list3Ctr = configSheet.getRange(6, 2, 1, 1).getValue();
config.list4Impressions = configSheet.getRange(7, 2, 1, 1).getValue();
config.list4Ctr = configSheet.getRange(8, 2, 1, 1).getValue();
5.Update the document title based on the script execution date:


function main() {
var spreadsheet = SpreadsheetApp.openByUrl(config.SPREADSHEET_URL);
var curDate = Utilities.formatDate(new Date(), “GMT+3”, “yyyy-MMMM-dd”);
getConfigData(spreadsheet);
spreadsheet.setName(“GDN Report ” + config.PROJECT_NAME + ” ” + curDate)
}
6.Generate the reports:


var body = “<h2>Google Display Network – Report on underperforming placements</h2>”;
body += “<h3>Placements that spent more than ” + config.listCost + ” USD and did not bring conversions:</h3> “;
body += “<ul>”;
var list = runHightCostAndNoConvertingReport();
var rows = [];
for (i = 0; i < list.length; i++) {
body += “<li>” + list[i].placement + ‘ – ‘ + list[i].cost + ‘ USD ‘ + “</li>”;
}
addPlacementList(‘list1’, list, spreadsheet);
body += “</ul>”;
body += “<h3>Placements with the conversion cost more than ” + config.list2ConversionCost + ” USD:</h3> “;
body += “<ul>”;
var list2 = runHighCostOfConversionsReport();
for (i = 0; i < list2.length; i++) {
body += “<li>” + list2[i].placement + ‘ – ‘ + list2[i].cost + ‘ USD ‘ + ‘ – The cost of conversion – ‘ + list2[i].costperconversion + ‘ USD ‘ + “</li>”;
}
addPlacementList(‘list2’, list2, spreadsheet);
body += “</ul>”;
body += “<h3>Placements with more than ” + config.list3Impressions + ” impressions and CTR less than ” + config.list3Ctr + “%:</h3> “;
body += “<ul>”;
var list3 = runBadCtrNoConversionsReport();
body += “<li>The number of exceptions – ” + list3.length + “</li>”;
addPlacementList(‘list3’, list3, spreadsheet);
body += “</ul>”;
body += “<h3>Placements with more than ” + config.list4Impressions + ” impressions and CTR more than ” + config.list4Ctr + “%:</h3> “;
body += “<ul>”;
var list4 = runHighCtrReport();
body += “<li>The number of exceptions – ” + list4.length + “</li>”;
addPlacementList(‘list4’, list4, spreadsheet);
body += “</ul>”;
body += “<h3>The placements, which domain name contains the unwanted word:</h3> “;
body += “<ul>”;
var list5 = gamePlacements();
body += “<li> The number of exceptions – ” + list5.length + “</li>”;
addPlacementList(‘list5’, list5, spreadsheet);
body += “</ul>”;
body += “<a href='” + spreadsheet.getUrl() + “‘>Link to the report Google Spreadsheet</a>”;
7.Send an email with the selected placements:


if (config.email.length) {
MailApp.sendEmail(config.email, ‘Display Network Alerts – ‘ + config.PROJECT_NAME + ” – ” + curDate, body, { htmlBody: body });
}
8.Use the following function to add the placements to the exclusions:


function addPlacementList(nameList, list, spreadsheet) {
var rows = [],
sheet,
range,
listSharedExcludedPlacementIterator;
sheet = spreadsheet.getSheetByName(nameList);
sheet.clear();
range = sheet.getRange(1, 1, 1, 7).setValues([[‘Exclusion URL’, ‘Impressions’, ‘Clicks’, ‘CTR’, ‘Cost’, ‘Conversions’, ‘Cost Per Conversion’]]);
range.setBackground(“yellow”);
listSharedExcludedPlacementIterator = AdWordsApp.excludedPlacementLists()
.withCondition(“Name CONTAINS ‘” + nameList + “‘”).get();
while (listSharedExcludedPlacementIterator.hasNext()) {
listSharedExcludedPlacement = listSharedExcludedPlacementIterator.next();
}
for (i = 0; i < list.length; i++) {
listSharedExcludedPlacement.addExcludedPlacement(list[i].placement);
rows.push([list[i].placement, list[i].impressions, list[i].clicks, list[i].clicks / list[i].impressions * 100 + “%”, list[i].cost, list[i].conversions, list[i].costperconversion])
}
if (rows.length)
sheet.getRange(2, 1, rows.length, 7).setValues(rows).sort({ column: 2, ascending: false });
}
9.Determine the placements that spent more than X USD and did not bring conversions:


function runHightCostAndNoConvertingReport() {
list = [];
var periodString = ”;
if (config.timeperiod) {
periodString = ‘DURING ‘ + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log(‘DURING ALL TIME’);
}
var report = AdWordsApp.report(
‘SELECT Domain, Clicks, Impressions, CostPerConversion, Conversions, Cost ‘ +
‘FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ‘ +
‘WHERE Cost > ‘ + config.listCost * 1000000 + ” ” +
‘AND Conversions < 1 ‘ +
periodString);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var anonymous = row[‘Domain’].match(new RegExp(config.except.join(‘|’).replace(/\./g, ‘\\.’), ‘g’));
if (anonymous == null) {
var placementDetail = new placementObject(row[‘Domain’], row[‘Clicks’], row[‘Impressions’], row[‘CostPerConversion’], row[‘Conversions’], row[‘Cost’]);
list.push(placementDetail);
}
}
return list;
}
10.Identify placements with conversion rates surpassing X USD.


var report = AdWordsApp.report(
‘SELECT Domain, Clicks, Impressions, CostPerConversion, Conversions, Cost ‘ +
‘FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ‘ +
‘WHERE CostPerConversion > ‘ + config.list2ConversionCost * 1000000 + ” ” +
‘AND Conversions > 1 ‘ +
periodString);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var anonymous = row[‘Domain’].match(new RegExp(config.except.join(‘|’).replace(/\./g,’\\.’),’g’));
if (anonymous == null) {
var placementDetail = new placementObject(row[‘Domain’], row[‘Clicks’], row[‘Impressions’], row[‘CostPerConversion’], row[‘Conversions’], row[‘Cost’]);
list.push(placementDetail);
}
}
return list;
}

function runBadCtrNoConversionsReport() {
var list = [];
var periodString = ”;
if(config.timeperiod) {
periodString = ‘DURING ‘ + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log(‘DURING ALL TIME’);
}
11.Identify placements with no conversions, more than X impressions, and CTR less than Y%.


var report = AdWordsApp.report(
‘SELECT Domain, Clicks, Impressions, CostPerConversion, Conversions, Cost ‘ +
‘FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ‘ +
‘WHERE Impressions > ‘ + config.list3Impressions + ” ” +
‘AND Ctr < ‘ + config.list3Ctr * 0.01 + ” ” +
‘AND Conversions < 1 ‘ +
periodString);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var anonymous = row[‘Domain’].match(new RegExp(config.except.join(‘|’).replace(/\./g,’\\.’),’g’));
if (anonymous == null) {
var placementDetail = new placementObject(row[‘Domain’], row[‘Clicks’], row[‘Impressions’], row[‘CostPerConversion’], row[‘Conversions’], row[‘Cost’]);
list.push(placementDetail);
}
}
return list;
}

function runHighCtrReport() {
var list = [];
var periodString = ”;
if(config.timeperiod) {
periodString = ‘DURING ‘ + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log(‘DURING ALL TIME’);
}
12.Identify placements with no conversions, more than X impressions, and CTR greater than Y%.


var report = AdWordsApp.report(
‘SELECT Domain, Clicks, Impressions, CostPerConversion, Conversions, Cost ‘ +
‘FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ‘ +
‘WHERE Impressions > ‘ + config.list4Impressions + ” ” +
‘AND Ctr > ‘ + config.list4Ctr * 0.01 + ” ” +
‘AND Conversions < 1 ‘ +
periodString);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var anonymous = row[‘Domain’].match(new RegExp(config.except.join(‘|’).replace(/\./g,’\\.’),’g’));
if (anonymous == null) {
var placementDetail = new placementObject(row[‘Domain’], row[‘Clicks’], row[‘Impressions’], row[‘CostPerConversion’], row[‘Conversions’], row[‘Cost’]);
list.push(placementDetail);
}
}
return list;
}

function gamePlacements() {
var list = [];
var periodString = ”;
if(config.timeperiod) {
periodString = ‘DURING ‘ + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log(‘DURING ALL TIME’);
}
13.Include any placements without conversions whose domains contain the unwanted word.
var report = AdWordsApp.report(
‘SELECT Domain, Clicks, Impressions, CostPerConversion, Conversions, Cost ‘ +
‘FROM AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT ‘ +
‘WHERE Conversions < 1 ‘ +
periodString);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var domain = row[‘Domain’];
var clicks = row[‘Clicks’];
var impressions = row[‘Impressions’];
var costPerConversion = row[‘CostPerConversion’];
var conversions = row[‘Conversions’];
var cost = row[‘Cost’];

var isUnwantedDomain = containsAny(domain, config.exclude);

if (!isUnwantedDomain) {
var placementDetail = new placementObject(domain, clicks, impressions, costPerConversion, conversions, cost);
list.push(placementDetail);
}
}
return list;

function containsAny(str, substrings) {
for (var i = 0; i < substrings.length; i++) {
var substring = substrings[i];
if (str.indexOf(substring) !== -1) {
return true;
}
}
return false;
}

function placementObject(placement, clicks, impressions, costperconversion, conversions, cost) {
this.placement = placement;
this.clicks = clicks;
this.impressions = impressions;
this.costperconversion = costperconversion;
this.conversions = conversions;
this.cost = cost;
}

Results

We evaluated the script based on the specified criteria outlined in the accompanying image.

The email we received outlined the following discoveries:

  1. Google Display Network – Report on underperforming placements:
  • Placements that spent more than 300 USD without bringing conversions.
  • Placements with a conversion cost exceeding 500 USD.
  • Placements with more than 100 impressions and a CTR below 0.05%. Exceptions found: 3
  1. Placements with more than 50 impressions and a CTR above 8%: Exceptions found: 7
  2. Placements whose domain names contain the unwanted word: Exceptions found: 382