Introducing the PMax Placement Cleaner: Your Solution to Streamline Ad Placements
Managing Google Ads can often feel like navigating through a dense forest, especially when it comes to maintaining an efficient and effective placement list. Undesirable placements can diminish your ad performance, affecting your campaign's return on investment. That's where the PMax Placement Cleaner script comes into play, cutting through the clutter and automating the cleanup process to ensure your ads reach their intended audience.
Features
- Automated Cleanup: Say goodbye to manual audits. Our script simplifies the process, saving you time and effort.
- Improved Ad Performance: Focus your budget on placements that work, enhancing your overall campaign effectiveness.
- Ease of Use: With detailed instructions, the script is easy to install and integrate into your Google Ads account, regardless of your technical expertise.
Instructions
- Prepare Your Spreadsheet with PMax Placements on MCC level: Go to Report Editor → Predefined Reports → Other → Performance Max Campaigns Placements → Download as Google Spreadsheet. More information can be find on Google Ads support page here.
- Set Up the Script: In your MCC Google Ads account, navigate to Tools & Settings > Scripts, then create a new script and paste the provided code.
- Customize Your Script:
- Set the
REPORT_URL
within the script to link to your Google Spreadsheet for placement data analysis from step 1. - Adjust the
placementListName
to correspond with your existing placement list name in Google Ads. - Authorize and Run: Authorize the script to run under your account and set a trigger for regular checks.
Step by step guide
Prepare PMax Placement report on MCC level
Go to "Insights & reports"
Then go to "Report editor"
Choose "Performance Max campains placement" from report gallery
Filter report
Filter by "Performance Max placement type"
Choose "Webpage"
Download report as "Google Sheet"
You'll need Google Sheet URL to setup the script.
Create Google Ads script on MCC level
In your Manager Google Ads account. Go to "Scripts"
Create "New script"
Paste script code and update "REPORT_URL" with Google Sheet URL.
Set hourly frequency
Now your script is ready.
Apply new or existing MCC-level placement exclusion list to accounts
In Manager account "Overview” go to "Settings”
Choose "Sub-account settings”
Choose accounts
Click on "Edit" in the new menu.
Add placement exclusion lists
Choose your MCC level list and "Apple" changes.
All sorted!
Code
/*
License Note for PMax Placement Cleaner Script
Author: Dmytro Tonkikh, TrueClicks & chiliad
Version: 2.0
Date: 20.01.2024
License:
This PMax Placement Cleaner script is made available under the GPL licensing. By using this script, you agree to the terms and conditions of this license.
Description:
The PMax Placement Cleaner is designed for Google Ads users to manage and clean up their placement lists efficiently. It automates the process of identifying and excluding undesirable placements, enhancing ad performance.
Disclaimer:
This script is provided 'as is' and without any warranty. The author or company will not be liable for any damages or losses arising from its use. Users are responsible for adhering to Google Ads policies and applicable laws.
Contact Information:
dmytro@trueclicks.com
dmytro@chiliad.agency
*/
var REPORT_URL = "";
var placementListName = "TrueClicks | common negative";
function main() {
if (typeof AdsManagerApp !== "undefined") {
pMaxPlacementlCleaner();
} else {
throw "This is a MCC-level script.";
}
}
function pMaxPlacementlCleaner() {
var customerId = AdsApp.currentAccount().getCustomerId();
var scriptName = "Placement Cleaner";
var summaryFileName = "TrueClicks | Placement Cleaner";
var mainScriptFolder = getOrCreateFolder("🤖 TrueClicks scripts");
var accountFolder = getOrCreateClientFolder(mainScriptFolder);
var folder = getOrCreateScriptFolder(accountFolder, scriptName);
var mainFile;
var fileIterator = folder.getFiles();
while (fileIterator.hasNext()) {
var spreadsheet = fileIterator.next();
if (spreadsheet.getName() == summaryFileName) {
mainFile = spreadsheet;
}
}
try {
if (!mainFile) mainFile = addSummaryFile(folder, summaryFileName);
} catch (e) {
Logger.log(e);
}
var ss = SpreadsheetApp.open(mainFile);
Logger.log("Spreadsheet url: " + ss.getUrl());
checkPlacementList(placementListName);
var bulkUpload = AdsApp.bulkUploads().newCsvUpload([
"Action",
"Customer ID",
"Placement Exclusion List Name",
"Placement url",
]);
var placementData = [];
var checked = [];
var exclusion = [];
var sheet = ss.getSheetByName("results");
var regExp = createRegExp(ss, "keywords");
var domain_regExp = createRegExp(ss, "domains");
var union = unionArrays(
getExludedPlacements(placementListName),
columnData(sheet)
);
var placements = Object.keys(urlReport(union)).reduce(function (s, c) {
var match = c.match(domain_regExp || []);
if (match) {
exclusion.push(c);
checked.push([c, "domain"]);
} else {
s.push(c);
}
return s;
}, []);
if (placements.length > 700) {
placements = placements.slice(0, 700);
}
var requests = chunkArray(placements, 10);
for (var i in requests) {
try {
var reqs = UrlFetchApp.fetchAll(requests[i]);
reqs.forEach(function (x, n) {
placementData.push([requests[i][n], request(requests[i][n], x)]);
});
} catch (e) {
requests[i].forEach(function (x, n) {
placementData.push([requests[i][n], getTitle(requests[i][n])]);
});
}
}
for (var y in placementData) {
try {
var p = placementData[y];
var match = p[1].match(regExp || []);
var match_domain = p[0].match(domain_regExp || []);
if (match || match_domain) {
exclusion.push(p[0]);
checked.push(p);
bulkUpload.append({
Action: "Add",
"Customer ID": customerId,
"Placement Exclusion List Name": placementListName,
"Placement url": pl,
});
} else {
checked.push(p);
}
} catch (e) {
checked.push(p);
Logger.log(p);
}
}
if (exclusion[0]) {
bulkUpload.setFileName("TrueClicks - Placement Cleaner").apply();
}
if (checked[0]) {
sheet
.getRange(sheet.getLastRow() + 1, 1, checked.length, checked[0].length)
.setValues(checked);
}
Logger.log("Excluded placements: " + exclusion.length);
Logger.log("Checked placements: " + checked.length);
}
function checkPlacementList(name) {
var epl = AdsApp.excludedPlacementLists()
.withCondition("shared_set.name = '" + name + "'")
.get();
if (!epl.hasNext()) {
epl = AdsApp.newExcludedPlacementListBuilder()
.withName(name)
.build()
.getResult();
}
}
function urlReport(union) {
var reportSS = SpreadsheetApp.openByUrl(REPORT_URL);
var values = reportSS.getActiveSheet().getDataRange().getValues();
var headers = values.filter(function (p) {
if (p.indexOf("Performance Max placement URL") !== -1) {
return p;
}
});
var index = headers[0].indexOf("Performance Max placement URL");
var regex = new RegExp(
"^(?:https?://)?(?:[^@/\n]+@)?(?:www.)?([^:/?\n]+)",
"igm"
);
var domains = {};
var urls = columnData(reportSS.getActiveSheet(), index).forEach(function (x) {
if (
!domains[x] &&
union.indexOf(x) == -1 &&
x.indexOf("mobileapp::") == -1 &&
x.indexOf("youtube") == -1
) {
domains[x] = true;
}
});
return domains;
}
// Get placement title
function getTitle(url) {
try {
var title = request(url);
} catch (e) {
Logger.log(e.message + " " + url);
}
return title;
}
function request(url, resp) {
try {
if (!resp) {
var resp = UrlFetchApp.fetch(url, {
muteHttpExceptions: true,
followRedirects: true,
});
}
var headers = resp.getAllHeaders();
if (headers["Content-Type"]) {
try {
var charset = headers["Content-Type"]
.match(/charset=.*/g || [])[0]
.replace("charset=", "")
.toUpperCase();
} catch (e) {
var charset = "";
}
} else {
var charset = "";
}
var content = resp.getContentText(charset).toLowerCase();
var start = content.indexOf("<title");
var end = content.indexOf("</title>");
var title = content.substring(start + 7, end);
return title;
} catch (e) {
Logger.log(e + " " + url);
}
}
//Creating new RegExp
function createRegExp(ss, name) {
var string = "(" + columnData(ss.getSheetByName(name)).join("|") + ")";
if (string.indexOf(".") !== -1) {
string = string.replace(/\./g, "\\.");
}
var regexp = new RegExp(string, "g");
return regexp;
}
//Get checked placement
function columnData(sheet, index) {
if (index == undefined) {
index = 0;
}
return sheet
.getDataRange()
.getValues()
.filter(function (i) {
return i[index] !== "";
})
.map(function (x) {
return x[index];
});
}
function unionArrays(x, y) {
var obj = {};
for (var i = x.length - 1; i >= 0; --i) obj[x[i]] = x[i];
for (var i = y.length - 1; i >= 0; --i) obj[y[i]] = y[i];
var res = [];
for (var k in obj) {
if (obj.hasOwnProperty(k))
// <-- optional
res.push(obj[k]);
}
return res;
}
/*
* Creates a spreadsheet for the summary and stores it in the folder.
* Populates header rows and columns.
*/
function addSummaryFile(folder, name, currentSS) {
if (currentSS) {
DriveApp.getFileById(currentSS.getId()).makeCopy(name, folder);
} else {
DriveApp.getFileById(
"1iVdtOQt0WsOyA5rtdSkZHhaiel9Pdqd2K2vIujLsrrM"
).makeCopy(name, folder);
}
return folder.getFilesByName(name).next();
}
/*
* Checks if there is a folder with the given name in the Google Drive root folder. If not, the folder is created.
* The folderName can be in the form of a complete path with subfolders, like "QS Reports/123/whatever".
* Returns the folder.
*/
function getOrCreateFolder(folderName) {
if (!DriveApp.getFoldersByName(folderName).hasNext()) {
var currentFolder = DriveApp.createFolder(folderName);
} else {
var currentFolder = DriveApp.getFoldersByName(folderName).next();
}
return currentFolder;
}
/*
* Checks if there is a folder for the current client account in the base folder. If not, the folder is created.
* Existing client folders are recognized by the client id in parentheses. This way, folders can be found again, even if an account has been renamed.
*/
function getOrCreateClientFolder(baseFolder) {
var folderIterator = baseFolder.getFolders();
var regExp = new RegExp(AdsApp.currentAccount().getCustomerId());
while (folderIterator.hasNext()) {
var folder = folderIterator.next();
if (folder.getName().match(regExp)) return folder;
}
// Since no folder has been found: Create one.
var newFolderName =
AdsApp.currentAccount().getName() +
" (" +
AdsApp.currentAccount().getCustomerId() +
")";
Logger.log("Creating folder '" + newFolderName + "'");
return baseFolder.createFolder(newFolderName);
}
function getOrCreateScriptFolder(baseFolder, scriptName) {
var folderIterator = baseFolder.getFolders();
var regExp = new RegExp(scriptName);
while (folderIterator.hasNext()) {
var folder = folderIterator.next();
if (folder.getName().match(regExp)) return folder;
}
// Since no folder has been found: Create one.
var newFolderName = scriptName;
Logger.log("Creating script folder '" + newFolderName + "'");
return baseFolder.createFolder(newFolderName);
}
function getExludedPlacements(name) {
var result = [];
let query =
"SELECT shared_criterion.placement.url FROM shared_criterion WHERE shared_set.name = '" +
name +
"'";
let report = AdsApp.search(query);
while (report.hasNext()) {
let r = report.next();
if (r.sharedCriterion.placement) {
let url = r.sharedCriterion.placement.url;
result.push(url);
}
}
return result;
}
function chunkArray(myArray, chunk_size) {
var index = 0;
var arrayLength = myArray.length;
var tempArray = [];
for (index = 0; index < arrayLength; index += chunk_size) {
myChunk = myArray.slice(index, index + chunk_size);
// Do something if you want with the group
tempArray.push(myChunk);
}
return tempArray;
}
Final Thoughts
The PMax Placement Cleaner script is more than a tool; it's a strategic asset for Google Ads users aiming to optimize their advertising efforts. By automating the cleanup of your placement list, you can focus on what truly matters - crafting impactful ads that resonate with your target audience.