Script: PMax Placement Exclusion Suggestions
What it does:
This script suggests placement exclusions for bad placements where your PMax ads showed.
If new placement exclusions are suggested the placement exclusions are reported via email.
The email contains a link to a Google Doc spreadsheet documenting all the placement exclusions suggestions.
Why you care:
PMax is notorious for showing your ads on placements that are not safe for your brand and/or only deliver fraudulent clicks and leads.
Now go ahead, and install the script (it only takes 5 mins!).
Run the script weekly (I prefer Monday mornings)
And sleep comfortably knowing that all money-wasting and brand-hurting PMax-placements will pop up in your inbox when they appear.
Happy scripting!
NB: If you like this script be sure to also check out my Google Ads Script for Non-Converting Performance Max Search Terms Alerts
INSTRUCTIONS:
– Create a copy of this spreadheet -> https://docs.google.com/spreadsheets/d/1Ehsm9zOQ6ETrZgxZKDdflOUol2AVxE96BhOElncU7fw/copy
– Add the complete URL of the copy of the spreadsheet to the SPREADSHEET_URL below
– Decide on the date range / PERIOD
– Decide on the TLDs that you consider safe and add them to SAFE_TLDS
= Add your keywords that identify spammy domain names to SPAMMY_KEYWORDS
– Add the keywords that identify political content for you to POLITICAL_KEYWORDS
– Set thresholds for MIN_IMPRESSIONS
– Add the name of your Google Ads account to ACCOUNT_NAME
– Add your email address to the script to EMAIL_ADDRESSES
– Authorize and Preview
– Schedule to run weekly (I prefer Mondays at 6AM)
Subscribe to my mailing list to receive updates on future versions of the script.
/**
*
* Google Ads Script - PMax Placement Exclusion Suggestions (FREE version)
*
* (C) Nils Rooijmans , https://nilsrooijmans.com
*
* This script suggests placement exclusions for bad placements where your ads show in PMax campaigns.
* If new placement exclusions are suggested the placement exclusions are reported via email.
* The email containts a link to Google Doc spreadsheet documenting all the placement exclusion suggestions.
*
* Current Version: 1.2.2
*
* Version 1.2.2 -> added placement exclusions for videos with clickbait titles
* Version 1.2.1 -> added exlusions for Second Level Domains that are questionable
* Version 1.2 -> added placement exclusions for Mobile App placements.
* Version 1.1 -> added placement exclusions for videos with political content.
* Version: 1.0
* The 1.0 version only checks website placements to see if they are hosted on trusted Top Level Domains (TLDs).
*
* Future versions will include more intelligent checks to discriminate between good and bad placements.
*
* INSTRUCTIONS:
*
* If you are new to scripts -> follow these instructions on how to copy-paste and install Google Ads Scripts:
* https://nilsrooijmans.com/how-to-set-up-and-run-google-ads-scripts/
*
* Next:
* - Create a copy of this spreadheet -> https://docs.google.com/spreadsheets/d/1Ehsm9zOQ6ETrZgxZKDdflOUol2AVxE96BhOElncU7fw/copy
* - Add the complete URL of the copy of the spreadsheet to the SPREADSHEET_URL below
* - Decide on the date range / PERIOD
* - Decide on the TLDs that you consider safe and add them to SAFE_TLDS
* - Add the keywords that identify political content for you to POLITICAL_KEYWORDS
* - Set thresholds for MIN_IMPRESSIONS
* - Add the name of your Google Ads account to ACCOUNT_NAME
* - Add your email address to the script to EMAIL_ADDRESSES
* - Authorize and Preview
* - Schedule to run weekly (I prefer Mondays at 6AM)
*
* ---------------------------------------------------
**/
/// START OF SCRIPT CONFIGURATION ///
var SPREADSHEET_URL = ""; //insert url of YOUR COPY of https://docs.google.com/spreadsheets/d/1Ehsm9zOQ6ETrZgxZKDdflOUol2AVxE96BhOElncU7fw/copy
var PERIOD = "LAST_30_DAYS"; // the date range for your placement data, can be either "LAST_7_DAYS", "LAST_30_DAYS", "LAST_60_DAYS", "LAST_90_DAYS", "LAST_12_MONTHS" or "THIS_YEAR"
var MIN_IMPRESSIONS = 5; // Only look at PMax placements that accrued over this amount of impressions during the PERIOD you defined above
/*** BAD PLACEMENT FILTER CONFIGURATIONS ***/
var SAFE_TLDS = [".com", ".edu", ".org", ".net", ".nl", ".be"]; // list of TLDs you consider to be safe. All placements of other TLDs will be added to the list of exclusion suggestions. Add TLDs like ".co.uk", ".de", ".nl" etc when you feel thay are safe.
var SPAMMY_KEYWORDS = [
'free', 'cheap', 'earn', 'win', 'get', 'hot', 'cash', 'offer', 'deal', 'bonus', 'winbig', 'prizes', 'deals', // general list of spammy keywords
'girls', 'xxx', 'naked', 'adult', 'sex', 'porn', 'cam', 'escort', 'chat', // list of adult keywords
'support', 'help', 'login', 'recover', 'fix', 'verify', 'account', 'reset', // list of potential phishing keywords
'now', 'today', 'fast', 'instant', 'top', 'amazing', 'exclusive', 'shocking', 'breaking', 'viral', 'secret', // clickbait
'bitcoin', 'crypto', 'forex', 'loan', 'payday', 'profit', 'money', 'invest', 'trading', 'wealth', 'gold' // potential scam
];
var POLITICAL_KEYWORDS = [
'Trump', 'Biden', 'Harris', 'Pence', 'Obama', 'Clinton', 'Sanders', 'Pelosi', 'Putin', 'Jinping', 'Vance',
'election', 'government', 'politics', 'president', 'congress',
'senate', 'democracy', 'republic', 'voting', 'votes',
'political', 'policy', 'referendum', 'protest', 'activism',
'lawmakers', 'liberal', 'conservative', 'reform',
'racism', 'racist'
];
var CLICKBAIT_PHRASES = [
'shocking', 'bombshell', 'unexpected', "you won't believe", 'must see',
'secret', 'revealed', 'exposed', 'caught on camera', 'nightmare', 'mystery', 'scandal',
'jaw-dropping', 'insane', 'epic', 'unbelievable', 'must-watch', 'massive', 'stunning', 'gone wrong', 'wtf', 'omg', "don't miss"
];
/*** EMAIL CONFIGURATION SETTINGS ***/
var ACCOUNT_NAME = ""; // insert short name of your account to recoginze the account in the subject of your emails
var SEND_EMAIL = true; // set to false if you do not want to recieve any emails (not recommended)
var EMAIL_ADDRESSES = ""; // insert email addresses of people that want to get the placements exclusions. Insert email addresses between the quotes, seperate multipe email addresses by a comma. Ie: "jane@doe.com, john@doe.com"
/// END OF SCRIPT CONFIGURATION ///
/*** DON'T CHANGE ANYTHING BELOW THIS LINE ***/
var DEBUG = false;
var VERSION = "1.2.2";
var VERSION_OUTDATED_MESSAGE = `WAKE UP CALL: I've got some good news -> there's a new and improved version of this script waiting for you!\nFind you way to https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ to get the latest version of the script.`;
var VERSION_UP2DATE_MESSAGE = `Congrats, you're all up-to-date with the lastest version of the script! I hope you enoy the suggestions :)`;
var EMAIL_SUBJECT = `${ACCOUNT_NAME} - New PMax Placement Exclusion Suggestions for bad placements`;
var EMAIL_BODY = `This script looks at all placements that had over ${MIN_IMPRESSIONS} impressions during ${PERIOD} for all PMax cmpaigns in your account, and suggests bad placement exclusions to add to your account.\n`;
function main() {
checkVersion();
var result = getResults();
reportResults(result);
}
function reportResults(result) {
var outputSheet = prepareOutputSheets();
var nrOfResultsReported = 0;
var output = result.output;
if (output.length > 0) {
addOutputToSheet(output, outputSheet);
nrOfResultsReported += output.length;
}
if(SEND_EMAIL && nrOfResultsReported > 0) {
var emailSubject = "[Gads Script] "+EMAIL_SUBJECT;
var emailHeader = `Hey PPC Friend, this email is generated by version ${VERSION} of the 'PMax Placement Exclusion Suggestions Script' (c) Nils Rooijmans.\n`
// check to see if the email needs a message that updates the user about the latest version of the script
var latestVersion = getLatestScriptVersion();
var versionMessage = "";
if (latestVersion != VERSION) {
versionMessage = VERSION_OUTDATED_MESSAGE+` --> Latest version: ${latestVersion}`;
} else {
versionMessage = VERSION_UP2DATE_MESSAGE;
}
emailHeader += `\n${versionMessage}\n\n`;
var emailFooter = getEmailFooter();
var emailBody = emailHeader+EMAIL_BODY+"\nNumber of PMax placement exclusion suggestions added in total: "+nrOfResultsReported+"\n\n"+
"You can find all PMax placement exclusion suggestions here: "+SPREADSHEET_URL+"\n\n"+
"---\n"+emailFooter;
var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
debug("Remaining email quota: " + emailQuotaRemaining);
MailApp.sendEmail(EMAIL_ADDRESSES, emailSubject, emailBody) ;
}
}
function getResults() {
var accountName = AdsApp.currentAccount().getName();
var results = [];
var date = getTodaysDate();
var startDate = getStartDate(PERIOD);
var endDate = getEndDate(PERIOD);
var whereClause =
" WHERE metrics.impressions > " + MIN_IMPRESSIONS;
whereClause = whereClause +
" AND segments.date BETWEEN '" + startDate + "' AND '" + endDate +"'";
" ORDER BY metrics.impressions DESC" ;
// + " LIMIT 10";
var query =
"SELECT performance_max_placement_view.display_name, performance_max_placement_view.placement, performance_max_placement_view.placement_type, performance_max_placement_view.resource_name, performance_max_placement_view.target_url, metrics.impressions" +
" FROM performance_max_placement_view" + whereClause;
// Let's report all placements in a seperate sheet.
var allPlacementsSheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName('all_placements');
gaqlToReportSheet(query, allPlacementsSheet);
//debug(query);
try {
var result = AdsApp.search(query);
while (result.hasNext()) {
var row = result.next();
//debug(row);
var performanceMaxPlacement = {
displayName: row.performanceMaxPlacementView.displayName,
placement: row.performanceMaxPlacementView.placement,
placementType: row.performanceMaxPlacementView.placementType,
resourceName: row.performanceMaxPlacementView.resourceName,
targetUrl: row.performanceMaxPlacementView.targetUrl,
impressions: row.metrics.impressions
};
var checkedPlacement = checkPlacement(performanceMaxPlacement);
if(checkedPlacement.exclude == true) {
//console.log(`PMax placement exclusion suggestion: ${performanceMaxPlacement.targetUrl} | impressions ${performanceMaxPlacement.impressions}`);
results.push([performanceMaxPlacement.targetUrl, performanceMaxPlacement.displayName , performanceMaxPlacement.impressions, checkedPlacement.reason]);
}
}
} catch(e) {
alert("ERROR: "+e);
MailApp.sendEmail(EMAIL_ADDRESSES, "### ERROR: "+EMAIL_SUBJECT, "Could not suggest placement exclusions: "+e);
}
console.log(`\nFinished analyses\nNumber of new placement exclusion suggestions: ${results.length}\n--> Report available at: ${SPREADSHEET_URL}`);
return {
output: results
};
}
function checkPlacement(performanceMaxPlacement) {
// check website placement
if (performanceMaxPlacement.placementType == 'WEBSITE') {
if (questionableTLD(performanceMaxPlacement)) return {exclude:true, reason:`Questionable TLD`};
var checkedSLD = questionableSLD(performanceMaxPlacement);
if(checkedSLD.isQuestionable == true) return {exclude:true, reason:checkedSLD.reason};
}
// check YouTube placement
if (performanceMaxPlacement.placementType == 'YOUTUBE_VIDEO') {
var checkedVideoTitle = questionableVideoTitle(performanceMaxPlacement);
if (checkedVideoTitle.isQuestionable == true) return {exclude:true, reason:checkedVideoTitle.reason};
}
// check Mobile App placement
if (performanceMaxPlacement.placementType == 'MOBILE_APPLICATION') {
return {exclude:true, reason:`Mobile App placement. Almost certainly low conversion value.`};
}
return {exclude:false};
}
function questionableVideoTitle(performanceMaxPlacement) {
var exclude = false;
var reasons = ["Questionable video title:"];
var videoTitle = performanceMaxPlacement.displayName;
// check if title is political
for (var i = 0; i < POLITICAL_KEYWORDS.length; i++) {
if (videoTitle.toLowerCase().includes(POLITICAL_KEYWORDS[i].toLowerCase())) {
exclude = true;
reasons.push(`Video Title contains political keyword: ${POLITICAL_KEYWORDS[i]}`);
break;
}
}
// check if title is clickbait
for (var i = 0; i < CLICKBAIT_PHRASES.length; i++) {
if (videoTitle.toLowerCase().includes(CLICKBAIT_PHRASES[i].toLowerCase())) {
exclude = true;
reasons.push(`Video Title contains clickbait phrase: ${CLICKBAIT_PHRASES[i]}`);
break;
}
}
// check for excessive punctuation like "!!" or "??"
if (/[!?]{2,}/.test(videoTitle)) {
exclude = true;
reasons.push(`Video Title contains excessive punctuation like "!!" or "??"`)
}
// check for overly capitalized titles
if (/[A-Z\s]{10,}/.test(videoTitle)) {
exclude = true;
reasons.push(`Video Title is overly capitalized`)
}
// check for monetary amounts
if (/[\$\€]\d+/.test(videoTitle)) {
exclude = true;
reasons.push(`Video Title contains monetary amounts`)
}
// return result
if (exclude) {
var reason = reasons.join(`\n`);
return {isQuestionable:true, reason:reason};
} else return false;
}
function questionableSLD(performanceMaxPlacement) {
var exclude = false;
var reasons = ["Questionable domain name:"];
var targetURL = performanceMaxPlacement.targetUrl;
var sld = getSLD(targetURL);
// check length
if(sld.length < 3 || sld.length >25) {
exclude = true;
reasons.push(`Second Level Domain length is suspicious: ${sld.length} chars`);
}
// check for spammy keywords
for (var i = 0; i < SPAMMY_KEYWORDS.length; i++) {
if(sld.toLowerCase().includes(SPAMMY_KEYWORDS[i].toLowerCase())) {
exclude = true;
reasons.push(`Domain name contains spammy keyword(s), including: ${SPAMMY_KEYWORDS[i]}`);
break;
}
}
// check for hyphens
if (sld.includes(`-`)) {
exclude = true;
reasons.push(`Domain name contains hyphen`);
}
// check for more than 3 numbers in a row
if (/[0-9]{3,}/.test(sld)) {
exclude = true;
reasons.push(`Domain name contains more than 3 numbers in a row`)
}
// check for repeated chars
if (/(.)\1{2,}/.test(sld)) { // same char repeated 3 times?
exclude = true;
reasons.push(`Domain name contains same char repeated more than 2 times in a row`)
}
// Check for presence of "xn--" (punycode for internationalized domain names)
if (sld.includes("xn--")) {
exclude = true;
reasons.push(`Domain name contains punycode, which can be abused`)
}
// return result
if (exclude) {
var reason = reasons.join(`\n`);
return {isQuestionable:true, reason:reason};
} else return false;
function getSLD(domain) {
// Remove any subdomains (if present)
const parts = domain.split(".");
if (parts.length > 2) {
parts.shift(); // Remove the first part (subdomain)
}
// Extract the SLD
return parts[0];
}
}
function questionableTLD(performanceMaxPlacement) {
var safeTLDs = SAFE_TLDS;
var targetURL = performanceMaxPlacement.targetUrl;
var tld = targetURL.substring(targetURL.lastIndexOf("."));
return !safeTLDs.includes(tld);
}
/*** REPORT FUNCTIONS ***/
function prepareOutputSheets() {
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
if (!spreadsheet) {
Logger.log("Cannot open new reporting spreadsheet") ;
return ;
}
spreadsheet.rename(ACCOUNT_NAME+" - PMax Placement Exclusion Suggestions");
var sheet = spreadsheet.getSheetByName('suggested_exclusions');
if (!sheet) {
Logger.log("Cannot open new reporting sheet") ;
return ;
}
sheet.clear();
// set width of columns
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 400);
sheet.setColumnWidth(3, 100);
sheet.setColumnWidth(4, 500);
addHeaderToOutputSheet(sheet);
return sheet;
}
function addHeaderToOutputSheet(sheet) {
try {
var headerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zWtNFCXbq4yPSz3G0am0HxW6NHZO3VqJzH-K12jAVZw/").getSheetByName('header_sheet');
} catch(e) {
console.log(`### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`);
throw `### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`;
}
var headerRange = headerSheet.getRange(1, 1, 2, headerSheet.getLastColumn());
var headerData = headerRange.getValues();
var range=sheet.getRange(1,1,2,headerData[1].length);
range.clear();
range.clearFormat();
range.setValues(headerData)
range.setFontWeight("bold");
range = sheet.getRange(1,1,1,headerData[1].length);
range.setFontColor('#007BFF')
sheet.setFrozenRows(2);
var latestVersion = getLatestScriptVersion();
var versionMessage = "";
if (latestVersion != VERSION) {
versionMessage = VERSION_OUTDATED_MESSAGE+` --> Latest version: ${latestVersion}`;
} else {
versionMessage = VERSION_UP2DATE_MESSAGE;
}
range = sheet.getRange("A1");
var titleCell = range.getValue();
titleCell = titleCell+`\nYou are running version ${VERSION}`+`\n${versionMessage}`;
range.setValue(titleCell);
}
function addOutputToSheet(output, sheet) {
var numberOfRows=sheet.getLastRow() ;
sheet.insertRowsBefore(3, output.length);
var startRow = 3;
var range=sheet.getRange(startRow, 1, output.length, output[0].length) ;
range.setValues(output) ;
range.sort([{column: 3, ascending: false}]);
}
function getEmailFooter() {
try {
var footerSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zWtNFCXbq4yPSz3G0am0HxW6NHZO3VqJzH-K12jAVZw/").getSheetByName('email_footer');
} catch(e) {
console.log(`### There was an issue opening the email_footer sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`);
throw `### There was an issue opening the email_footer sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`;
}
var footerRange = footerSheet.getRange(1, 1, footerSheet.getLastRow(), 1);
var footerData = footerRange.getValues();
var footer = footerData.join('\n');
return footer;
}
/*** HELPER FUNCTIONS ***/
function checkVersion() {
console.log(`Hey PPC Friend, you're running verion ${VERSION} of the 'PMax Placement Exclusion Suggestions Script' (c) Nils Rooijmans.`);
var latestVersion = getLatestScriptVersion();
if (latestVersion != VERSION) {
console.log(VERSION_OUTDATED_MESSAGE+` --> Latest version: ${latestVersion}`);
} else {
console.log(VERSION_UP2DATE_MESSAGE);
}
}
function getLatestScriptVersion() {
try {
var latestVersionSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zWtNFCXbq4yPSz3G0am0HxW6NHZO3VqJzH-K12jAVZw/").getSheetByName('latest_version');
} catch(e) {
console.log(`### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`);
throw `### There was an issue opening the header sheet. Please download the latest version of this script at https://nilsrooijmans.com/google-ads-script-pmax-placement-exclusion-suggestions/ \n${e}`;
}
var latestVersionRange = latestVersionSheet.getRange(1, 2, 1, 1);
var latestVersion = latestVersionRange.getValues();
return latestVersion;
}
function debug(string) {
if (DEBUG == true) {
Logger.log("** "+string);
}
}
function alert(string) {
Logger.log("### "+string);
}
function getTodaysDate() {
return new Date();
}
function getStartDate(period) {
var startDate;
var today = new Date();
switch(period) {
case "THIS_YEAR" :
startDate = today.getFullYear()+"-01-01";
break;
case "LAST_7_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 7*864e5)); // 864e5 == 86400000 == 24*60*60*1000
break;
case "LAST_30_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 30*864e5)); // 864e5 == 86400000 == 24*60*60*1000
break;
case "LAST_60_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 60*864e5));
break;
case "LAST_90_DAYS" :
startDate = dateToISOString(new Date(Date.now() - 90*864e5));
break;
case "LAST_12_MONTHS" :
startDate = dateToISOString(new Date(Date.now() - 365*864e5));
break;
default:
startDate ="2021-01-01";
}
debug("startDate: "+startDate);
return startDate;
}
function getEndDate(period) {
var endDate;
var yesterday = new Date(Date.now() - 864e5); // 864e5 == 86400000 == 24*60*60*1000
switch(period) {
case "LAST_YEAR" :
endDate = "lastDayLastYear"; //TODO
break;
case "LAST_MONTH" :
endDate = "lastDayLastMonth" //TODO
break;
default:
endDate = dateToISOString(yesterday);
}
debug("endDate: "+endDate);
return endDate;
}
function dateToISOString(date) {
var year = date.getUTCFullYear();
var month = date.getUTCMonth()+1;
if (month < 10) {
month = "0"+month;
}
var day = date.getUTCDate();
if (day < 10) {
day = "0"+day;
}
var ISOString = year+"-"+month+"-"+day;
return ISOString;
}
// this function sends the output of the GAQL query to a spreadsheet
function gaqlToReportSheet(gaqlQuery, sheet) {
var report = AdsApp.report(gaqlQuery);
report.exportToSheet(sheet);
}
Join thousands of PPC geeks who already have access: