Skip to content

Instantly share code, notes, and snippets.

@derekmartinla
Last active July 12, 2023 09:08
Show Gist options
  • Save derekmartinla/06e51b8a4298b8bbb8ff to your computer and use it in GitHub Desktop.
Save derekmartinla/06e51b8a4298b8bbb8ff to your computer and use it in GitHub Desktop.
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
Copy link

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!

@WiseCat-Git
Copy link

Hi, is this error solved already? salute

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