Last active
July 12, 2023 09:08
-
-
Save derekmartinla/06e51b8a4298b8bbb8ff to your computer and use it in GitHub Desktop.
Find Underperforming Placements & Opportunities On Google Display Network
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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); | |
} |
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
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!