Instantly share code, notes, and snippets.

Embed
What would you like to do?
Find Underperforming Placements & Opportunities On Google Display Network
// This script reviews your GDN placements for the following conditions:
// 1) Placements that are converting at less than $40
// 2) Placements that have cost more than $50 but haven't converted
// 3) Placements that have more than 5K impressions and less than .10 CTR
function main() {
var body = "<h2>Google Display Network Alert</h2>";
body += "<h3>Placements that are converting at less than $40:</h3> " ;
body += "<ul>";
var list = runLowCostAndConvertingReport();
for (i=0; i < list.length; i++) {
body += "<li><strong>" + list[i].placement + "</strong> - " + list[i].adgroup + ' - $' + list2[i].cost + "</li>";
}
body += "</ul>";
body += "<h3>Placements that have cost more than $50 but haven't converted:</h3> " ;
body += "<ul>";
var list2 = runHighCostNoConversionsReport();
for (i=0; i < list2.length; i++) {
body += "<li><strong>" + list2[i].placement + "</strong> - " + list2[i].adgroup + ' - $' + list2[i].cost + "</li>";
}
body += "</ul>";
body += "<h3>Placements that have more than 5K impressions and less than .10 CTR:</h3> " ;
body += "<ul>";
var list3 = runLowCtrAndHighImpressionsReport();
for (i=0; i < list3.length; i++) {
body += "<li><strong>" + list3[i].placement + "</strong> - " + list3[i].adgroup +" - " + parseFloat(list3[i].clicks/list3[i].impressions).toFixed(4) + "% - " + list3[i].clicks + " clicks - " + list3[i].impressions + ' impressions ' + "</li>";
}
body += "</ul>";
MailApp.sendEmail('your-email@here.com','Display Network Alerts ', body,{htmlBody: body});
}
function runLowCostAndConvertingReport()
{
list = [];
// Any placement detail (individual page) that has converted 2 or more times with CPA below $40
var report = AdWordsApp.report(
'SELECT Url, CampaignName, AdGroupName, Clicks, Impressions, Conversions, Cost ' +
'FROM URL_PERFORMANCE_REPORT ' +
'WHERE Cost < 40000000 ' +
'AND Conversions >= 2 ' +
'DURING LAST_30_DAYS');
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var anonymous = row['Url'].match(/anonymous\.google/g);
if (anonymous == null) {
var placement = row['Url'];
var campaign = row['CampaignName'];
var adgroup = row['AdGroupName'];
var clicks = row['Clicks'];
var impressions = row['Impressions'];
var conversions = row['Conversions'];
var cost = row['Cost'];
var placementDetail = new placementObject(placement, campaign, adgroup, clicks, impressions, conversions, cost);
list.push(placementDetail);
}
}
return list;
}
function runLowCtrAndHighImpressionsReport()
{
list = [];
// Any placement detail (individual page) that has converted 2 or more times with CPA below $40
var report = AdWordsApp.report(
'SELECT Url, CampaignName, AdGroupName, Clicks, Impressions, Conversions, Cost ' +
'FROM URL_PERFORMANCE_REPORT ' +
'WHERE Impressions > 5000 ' +
'AND Ctr < 0.1 ' +
'DURING LAST_30_DAYS');
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var anonymous = row['Url'].match(/anonymous\.google/g);
if (anonymous == null) {
var placement = row['Url'];
var campaign = row['CampaignName'];
var adgroup = row['AdGroupName'];
var clicks = row['Clicks'];
var impressions = row['Impressions'];
var conversions = row['Conversions'];
var cost = row['Cost'];
var placementDetail = new placementObject(placement, campaign, adgroup, clicks, impressions, conversions, cost);
list.push(placementDetail);
}
}
return list;
}
function runHighCostNoConversionsReport()
{
list = [];
// Any placement detail (individual page) that has converted 2 or more times with CPA below $40
var report = AdWordsApp.report(
'SELECT Url, CampaignName, AdGroupName, Clicks, Impressions, Conversions, Cost ' +
'FROM URL_PERFORMANCE_REPORT ' +
'WHERE Cost > 50000000 ' +
'AND Conversions = 0 ' +
'DURING LAST_30_DAYS');
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var anonymous = row['Url'].match(/anonymous\.google/g);
if (anonymous == null) {
var placement = row['Url'];
var campaign = row['CampaignName'];
var adgroup = row['AdGroupName'];
var clicks = row['Clicks'];
var impressions = row['Impressions'];
var conversions = row['Conversions'];
var cost = row['Cost'];
var placementDetail = new placementObject(placement, campaign, adgroup, clicks, impressions, conversions, cost);
list.push(placementDetail);
}
}
return list;
}
function placementObject(placement, campaign, adgroup, clicks, impressions, conversions, cost) {
this.placement = placement;
this.campaign = campaign;
this.adgroup = adgroup;
this.clicks = clicks;
this.impressions = impressions;
this.conversions = conversions;
this.cost = cost;
}
// Helpers
function warn(msg) {
Logger.log('WARNING: '+msg);
}
function info(msg) {
Logger.log(msg);
}
@honzafelt

This comment has been minimized.

Copy link

honzafelt commented Aug 9, 2015

Hey Derek,

Thank you very much for releasing this script in the world!

Maybe it's just me, but whenever I tried to implement the script, I got this error.

Column 'Conversions' is not valid for report type URL_PERFORMANCE_REPORT. Double-check your SELECT clause. (line 50)

Any tweaks or ideas? :-)

Thanks a lot!

@greyscalecom

This comment has been minimized.

Copy link

greyscalecom commented Oct 20, 2017

Really old script/topic. However for anyone that wants the correction to conversion error listed above. The SQL operator does not allow >=, you must change it to > instead.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment