Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys
Last active January 14, 2020 03:10
Show Gist options
  • Save siliconvallaeys/42803070fc8b957f367483ab97065caf to your computer and use it in GitHub Desktop.
Save siliconvallaeys/42803070fc8b957f367483ab97065caf to your computer and use it in GitHub Desktop.
Add poorly performing mobile app placements on GDN as excluded targets
/****************************
* Exclude Mobile App ad placements that perform poorly
* Version 1.0 (Aug 08, 2018)
*
* Created By: Optmyzr
* A supported version of this script is available at www.optmyzr.com
****************************/
function main() {
// User Inputs
var LAST_N_DAYS = 30,
MAX_CPA = 25,
MAX_COST_NO_CONVERSIONS = 50,
EMAIL = 'example@example.com, example2@example.com'; //Comma separated list of emails
// ---------------------------------------------------- //
// Script Code Begins Here. Do not edit below this line //
// ---------------------------------------------------- //
var DATE_RANGE = getAdWordsFormattedDate(LAST_N_DAYS, 'yyyyMMdd') + ',' +getAdWordsFormattedDate(0, 'yyyyMMdd');
var toExclude = {}, alreadyManagedOrExcluded = {};
var query = [
'SELECT CampaignName, AdGroupName, AdGroupId, Id, Criteria, Impressions, Clicks, Conversions, Cost',
'FROM PLACEMENT_PERFORMANCE_REPORT',
'WHERE IsNegative IN [TRUE, FALSE] and Criteria CONTAINS_IGNORE_CASE "mobileapp"',
'DURING ' + DATE_RANGE
].join(' ');
var rows = AdWordsApp.report(query, { 'includeZeroImpressions': true }).rows();
while(rows.hasNext()) {
var row = rows.next();
// Skip If the placement is managed or already excluded
if((row.IsNegative == 'false' && row.Id != '--') || row.IsNegative == 'true') {
if(!alreadyManagedOrExcluded[row.AdGroupId]) {
alreadyManagedOrExcluded[row.AdGroupId] = {};
}
alreadyManagedOrExcluded[row.AdGroupId][row.Criteria] = 1;
continue;
}
row.Cost = parseFloat(row.Cost.toString().replace(/,/g, ''));
row.Conversions = parseFloat(row.Conversions.toString().replace(/,/g, ''));
row.Cpa = row.Conversions == 0 ? row.Cost : row.Cost / row.Conversions;
Logger.log(row.Criteria);
// iTunes apps
if(row.Criteria.indexOf("mobileapp::1-") != -1) {
row.Url = row.Criteria.replace('mobileapp::1-', 'adsenseformobileapps.com/1-');
}
// Android apps
else if(row.Criteria.indexOf("mobileapp::2-") != -1) {
row.Url = row.Criteria.replace('mobileapp::2-', 'adsenseformobileapps.com/');
} else {
row.Url= row.Criteria;
}
//Logger.log(row.Criteria + ': ' + row.Cpa);
if((row.Conversions == 0 && row.Cost >= MAX_COST_NO_CONVERSIONS) || row.Cpa >= MAX_CPA) {
if(!toExclude[row.AdGroupId]) {
toExclude[row.AdGroupId] = {};
}
toExclude[row.AdGroupId][row.Criteria] = row;
}
}
// Delete any exclusions which are already excluded or managed
for(var id in toExclude) {
if(!alreadyManagedOrExcluded[id]) { continue; }
for(var criteria in toExclude[id]) {
if(alreadyManagedOrExcluded[id][criteria]) {
delete toExclude[id][criteria];
}
}
if(!Object.keys(toExclude[id]).length) {
delete toExclude[id];
}
}
var ids = Object.keys(toExclude);
if(!ids.length) {
log('No Automatic Mobile Placements found matching the criteria');
return;
}
var out = [['Campaign', 'AdGroup', 'Matched Placement Url', 'Negative Placement Added', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'CPA']];
var iter = AdWordsApp.adGroups().withIds(ids).get()
while(iter.hasNext()) {
var ag = iter.next();
var id = ag.getId();
for(var criteria in toExclude[id]) {
var row = toExclude[id][criteria];
ag.display().newPlacementBuilder().withUrl(row.Url).exclude();
out.push([row.CampaignName, row.AdGroupName, row.Criteria, row.Url, row.Impressions, row.Clicks, row.Cost, row.Conversions, row.Cpa]);
}
}
var ss = SpreadsheetApp.create(AdWordsApp.currentAccount().getName() + ' - Automatic Mobile Placements Exclusions');
log('Report Url: ' + ss.getUrl());
var tab = ss.getSheets()[0];
tab.setFrozenRows(1);
tab.getRange(1,1,out.length, out[0].length).setValues(out).setFontFamily('Calibri').setVerticalAlignment('middle');
tab.getRange(1,1,1, out[0].length).setBackground('#efefef').setFontWeight('bold').setHorizontalAlignment('center');
tab.getRange(1,4,out.length,5).setHorizontalAlignment('center');
log('Sending Email');
var SUB = AdWordsApp.currentAccount().getName() + ' - Automatic Mobile Placements Exclusions'
var MSG = 'Hi\n\nPlease find below the report of Automatic Mobile Placements which were excluded from the AdGroups:\n'+ss.getUrl()+'\n\nThanks';
MailApp.sendEmail(EMAIL, SUB, MSG);
ss.addEditors(EMAIL.split(','));
}
function getAdWordsFormattedDate(d, format){
var date = new Date();
date.setDate(date.getDate() - d);
return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format);
}
function log(msg) {
Logger.log(AdWordsApp.currentAccount().getName() + ' - ' + msg);
}
@thkouk
Copy link

thkouk commented Apr 9, 2019

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