The Google Display Network (GDN) is one of two Google AdWords networks which allow businesses to place display advertisements on a vast amount of sites across the web. Although this is a passive form of advertising, compared to the Google’s Search Network, t he company estimates that GDN involves more than 90% of the internet users.
The Problem with GDN
The dark side of display ad placements online is that you will need to spend plenty of time filtering hundreds of placements where you don’t wish the information about your company, product, or service appears. The problem that marketers, SEO experts, writers, web developers, and other responsible staff face is that even using filters they can spend hours dealing with this type of organizing.
Google AdWords provides the option to manage ad campaigns using javascript directly within the interface of the system, so the idea of cleaning GDN placements using scripts that can automatically do this job is promising. Our team started researching this topic area some time ago and we studied the attempts that were made to solve this problem by using scripts.
We found these two following options particularly useful: the first one was developed by Alexey Jaroshenko and the second – by Derek Martin. Some of their ideas and parts of their script were used to develop a solution.
General Solution
To fully understand how our step-by-step solution works and what options you have, kindly consider the following vital details.
Main Conditions
The script refers to the automatic placement report in Google AdWords for a specified period of time. It makes a few samples, in which the selected placements satisfy the following conditions:
- The expenses are more than X USD and have no conversions;
- The conversion cost is more than X USD;
- There are more than X impressions, CTR is less than Y % and there is no conversions;
- There are more than X impressions, CTR is greater than Y % and there is no conversions.
All the aforementioned criteria can be set manually in the Google Spreadsheet on the “config” list since the script imports data from it.
Unwanted Domains
The same procedure is performed to analyze the entry of the unwanted, “bad” words in the domain name without conversions. The list of such markers is specified on the “exclude_domain” list.
Image 1: An example of the Google Spreadsheet“exclude_domain” list.
Lists of Exceptions
The selected placements are added to the list of exceptions at the level of the shared library of the account.
For each sample, there is a separate list within the shared library (they must be created first), you can name them anyway you want, but it must contain - list1, list2, list3, list4, and list5. In addition, the information is available in the document itself (see images 2 and 3).
Image 2: An example of the shared library with the lists.
Image 3: An example of the Google Spreadsheet report.
You can add mobile applications, anonymous.google, and YouTube at the “except_domain” list.
Image 4: An example of the “except_domain” list.
Notification Email with Results
After the script is executed, an email is sent to the contact address, specified on the “config” list. The email contains the statistics for the first two samples and the number of excluded placements for the remaining selections and the link to the Google Spreadsheet. Please note that you are required to set the link to the Google Spreadsheet and the title of the project in the respective lines of the script.
Step-by-Step Solution
1. Indicate the Google Spreadsheet link and the name of the project:
var config = {
SPREADSHEET_URL:Link to the Google Spreadsheet',
PROJECT_NAME:'Name of the project',
}2. Specify the title of the list, in which the markers for the “bad”, underperforming domains for the fifth placement selection are indicated in the first column:
function getConfigData(spreadsheet) {
var excludeDomainSheet = spreadsheet.getSheetByName('exclude_domain'),
values =exclude DomainSheet.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, in which the markers for the domains that are not included in the statistical analysis:
var except DomainSheet = spreadsheet.getSheetByName('except_domain'),
values = except DomainSheet.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 your selection criteria:
- the list title,
- contact email,
- report time period,
- maximum cost for the first selection of placements,
- maximum cost of conversion for the second selection of placements,
- minimum number of impressions for the third selection,
- maximum CTR for the third selection,
- minimum number of impressions for the fourth selection, and
- minimum CTR for the fourth selection.
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 title of the document considering the latest date of the script execution:
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();
varrows = [];
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 in 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});
}
function runHightCostAndNoConvertingReport() {
list = [];
var periodString = '';
if(config.timeperiod) {
periodString = 'DURING ' + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log('DURING ALL TIME');
}9. Determine the placements that spent more than X USD and did not bring conversions:
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;
}
function runHighCostOfConversionsReport() {
list = [];
var periodString = '';
if(config.timeperiod) {
periodString = 'DURING ' + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log('DURING ALL TIME');
}10. Determine the placements with the conversion rate higher than 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() {
list = [];
var periodString = '';
if(config.timeperiod) {
periodString = 'DURING ' + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log('DURING ALL TIME');
}11. Determine the placements without conversions and with 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() {
list = [];
var periodString = '';
if(config.timeperiod) {
periodString = 'DURING ' + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log('DURING ALL TIME');
}12. Identify the placements without conversions with more than X impressions and CTR more 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() {
list = [];
var periodString = '';
if(config.timeperiod) {
periodString = 'DURING ' + config.timeperiod;
Logger.log(periodString);
} else {
Logger.log('DURING ALL TIME');
}13. Add any placements without conversions, which domain contains 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 anonymous = row['Domain'].match(new RegExp(config.except.join('|').replace(/\./g,'\\.'),'g'));
if (anonymous == null) {
var placement = row['Domain'];
var clicks = row['Clicks'];
var impressions = row['Impressions'];
var costperconversion = row['CostPerConversion']
var conversions = row['Conversions'];
var cost = row['Cost'];
var placementDetail = new placementObject(placement, clicks, impressions, costperconversion, conversions, cost);
if (containsAny(placement.toString(), config.exclude)) {
var placementDetail = new placementObject(placement, 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 substring;
}
}
return null;
}
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 tested the script using the following criteria (see the image below).
Image 5: The criteria used to test the script and find the “bad” placements.
The email that we received contained the following findings:
Google Display Network – Report on underperforming placements
Placements that spent more than 300 USD and did not bring conversions:
Placements with the conversion cost more than 500 USD:
Placements with more than 100 impressions and CTR less than 0.05%:
- The number of exceptions - 3
Placements with more than 50 impressions and CTR more than 8%:
- The number of exceptions - 7
The placements, which domain name contains the unwanted word:
- The number of exceptions - 382
Link to the report Google Spreadsheet.
Final Comments
It is fair to point out some important details to keep in mind:
- The aforementioned parameters can be specified according to the needs of your project.
- You can add new placements to the list of exceptions in the shared library.
- Remember that you can adjust the automatic launch of the script every week or whenever you find it necessary.
- If your account has a long history, it is recommended to check the whole time period to get all the underperforming placements. To do so, simply leave the time period field blank. Then, you can change the period for “LAST_WEEK” or “LAST_14_DAYS.”
Good luck finding your “bad” placements and improving your performance!
Did it work for you? Let us know in the comments.