Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save siliconvallaeys/f5e9e30d879d6e35ff6c4340e38663cb to your computer and use it in GitHub Desktop.
Save siliconvallaeys/f5e9e30d879d6e35ff6c4340e38663cb to your computer and use it in GitHub Desktop.
Add placement exclusions if a Google Ads placement name contains a character in a disallowed Unicode script
/*************************************************
* Placement Exclusion
* @version: 1.0
* @author: Naman Jindal (Optmyzr)
* -------------------------------
* Visit Optmyzr.com for PPC management tools and scripts
* including Rule-based automations, Reports, Audits, Team workflows,
* and optimization suggestions.
* -------------------------------
* Note Google limits placement (content) exclusions that may
* cause errors in the script. Read about allowed exclusions by campaign type at
* https://support.google.com/google-ads/answer/3306596
*
* IMPORTANT:
* In order to support the exclusion of YT placements, this script uses a workaround and
* creates the exclusions via bulk edits. You will need to review your bulk edits
* in the Google Ads interface after running the script.
***************************************************/
/*
Array of Scripts that are supported.
Placement will be excluded if a character which does not belong to any of the scripts in the array is found in its name
Scripts Supported:
'Latin', 'Greek', 'Coptic', 'Cyrillic', 'Armenian', 'Hebrew', 'Arabic', 'Syriac',
'Thaana', 'Devanagari', 'Bengali', 'Gurmukhi', 'Gujarati', 'Oriya', 'Tamil', 'Telugu',
'Kannada', 'Malayalam', 'Sinhala', 'Thai', 'Lao', 'Tibetan', 'Myanmar', 'Georgian',
'Hangul_Jamo', 'Ethiopic', 'Cherokee', 'Ogham', 'Runic', 'Tagalog', 'Hanunoo', 'Buhid',
'Tagbanwa', 'Khmer', 'Mongolian', 'Limbu', 'Tai_Le'
*/
var ALLOWED_SCRIPTS = [
'Latin'
];
//Name of the exclusion list. If the list does not exist, script will create it in the account
var EXCLUSION_LIST_NAME = 'Test 2';
// Maximum number of placements to be added to the exclusion list
var MAX_PLACEMENTS = 700;
// Lookback period for Clicks
var LAST_N_DAYS = 90;
// Do not edit below this line
function main() {
var listIter = AdsApp.excludedPlacementLists().withCondition("shared_set.name = '"+EXCLUSION_LIST_NAME+"'").get();
if(!listIter.hasNext()) {
AdsApp.newExcludedPlacementListBuilder().withName(EXCLUSION_LIST_NAME).build();
}
var list = AdsApp.excludedPlacementLists().withCondition("shared_set.name = '"+EXCLUSION_LIST_NAME+"'").get().next();
//Logger.log(list.getName());
var existingPlacements = {};
var placements = list.excludedPlacements().get();
while(placements.hasNext()) {
var pl = placements.next();
existingPlacements[pl.getUrl()] = 1;
}
var query = [
'SELECT shared_set.id, shared_criterion.keyword.text, shared_criterion.youtube_video.video_id,',
'shared_criterion.youtube_channel.channel_id, shared_criterion.placement.url,',
'shared_criterion.mobile_application.name',
'FROM shared_criterion WHERE shared_set.id='+list.getId()
].join(' ');
var rows = AdsApp.report(query).rows();
while(rows.hasNext()) {
var row = rows.next();
if(row['shared_criterion.mobile_application.name']) {
existingPlacements[row['shared_criterion.mobile_application.name']] = 1;
}
if(row['shared_criterion.youtube_video.video_id']) {
existingPlacements['youtube.com/video/' + row['shared_criterion.youtube_video.video_id']] = 1;
}
if(row['shared_criterion.youtube_channel.channel_id']) {
existingPlacements['youtube.com/channel/' + row['shared_criterion.youtube_channel.channel_id']] = 1;
}
if(row['shared_criterion.placement.url']) {
existingPlacements[row['shared_criterion.placement.url']] = 1;
}
}
var existingPlacementsCount = Object.keys(existingPlacements).length;
var PLACEMENTS_LEFT = MAX_PLACEMENTS - existingPlacementsCount;
if(PLACEMENTS_LEFT < 1) {
Logger.log('Maximum # placements already added. Exiting!');
return;
}
var START = getGoogleAdsFormattedDate(LAST_N_DAYS, 'yyyy-MM-dd'),
END = getGoogleAdsFormattedDate(0, 'yyyy-MM-dd');
var videoList = [], toAdd = [];
var query = [
'SELECT detail_placement_view.display_name, detail_placement_view.placement_type,',
'detail_placement_view.resource_name, detail_placement_view.placement,',
'detail_placement_view.group_placement_target_url, detail_placement_view.target_url,',
'metrics.clicks FROM detail_placement_view',
'WHERE segments.date between "'+START+'" AND "'+END+'"',
//'and detail_placement_view.placement_type != YOUTUBE_VIDEO',
'ORDER BY metrics.clicks DESC'
].join(' ');
//Logger.log(query);
var rows = AdsApp.report(query).rows();
while(rows.hasNext()) {
var row = rows.next();
var placementName = row['detail_placement_view.display_name'];
var placementUrl = row['detail_placement_view.target_url'];
if(!placementUrl) { continue; }
var placementUrlCheck = '';
if(placementUrl.indexOf('https://itunes.apple.com/') > -1) {
placementUrlCheck = 'mobileapp::1-' + placementUrl.split('app/id')[1];
} else if(placementUrl.indexOf('https://play.google.com') > -1) {
placementUrlCheck = 'mobileapp::2-' + placementUrl.split('id=')[1];
} else {
placementUrlCheck = placementUrl;
}
if(existingPlacements[placementUrlCheck]) { continue; }
var isAllowed = checkForAllowedScripts(placementName);
if(!isAllowed) {
Logger.log(placementUrl);
toAdd.push(placementUrl);
existingPlacements[placementUrl] = 1;
PLACEMENTS_LEFT--;
}
if(PLACEMENTS_LEFT < 1) { break; }
}
var columnHeads = [
'Action', 'Placement Exclusion List Name', 'Placement url'
];
var upload = AdWordsApp.bulkUploads().newCsvUpload(columnHeads);
for(var z in toAdd) {
upload.append({
'Action': 'Add',
'Placement Exclusion List Name': EXCLUSION_LIST_NAME,
'Placement url': toAdd[z]
});
}
Logger.log('Number of new placements to add: ' + toAdd.length);
upload.apply();
Logger.log('Placements were added via Bulk upload. Please check Uploads under Scripts section for logs.');
}
function checkForAllowedScripts(str) {
var unicodeMap = {
'Latin': /[\u0000-\u007F]/,
'Greek': /[\u0370-\u03FF]/,
'Coptic': /[\u0370-\u03FF]/,
'Cyrillic': /[\u0400-\u04FF]/,
'Armenian': /[\u0530-\u058F]/,
'Hebrew': /[\u0590-\u05FF]/,
'Arabic': /[\u0600-\u06FF]/,
'Syriac': /[\u0700-\u074F]/,
'Thaana': /[\u0780-\u07BF]/,
'Devanagari': /[\u0900-\u097F]/,
'Bengali': /[\u0980-\u09FF]/,
'Gurmukhi': /[\u0A00-\u0A7F]/,
'Gujarati': /[\u0A80-\u0AFF]/,
'Oriya': /[\u0B00-\u0B7F]/,
'Tamil': /[\u0B80-\u0BFF]/,
'Telugu': /[\u0C00-\u0C7F]/,
'Kannada': /[\u0C80-\u0CFF]/,
'Malayalam': /[\u0D00-\u0D7F]/,
'Sinhala': /[\u0D80-\u0DFF]/,
'Thai': /[\u0E00-\u0E7F]/,
'Lao': /[\u0E80-\u0EFF]/,
'Tibetan': /[\u0F00-\u0FFF]/,
'Myanmar': /[\u1000-\u109F]/,
'Georgian': /[\u10A0-\u10FF]/,
'Hangul_Jamo': /[\u1100-\u11FF]/,
'Ethiopic': /[\u1200-\u137F]/,
'Cherokee': /[\u13A0-\u13FF]/,
'Ogham': /[\u1680-\u169F]/,
'Runic': /[\u16A0-\u16FF]/,
'Tagalog': /[\u1700-\u171F]/,
'Hanunoo': /[\u1720-\u173F]/,
'Buhid': /[\u1740-\u175F]/,
'Tagbanwa': /[\u1760-\u177F]/,
'Khmer': /[\u1780-\u17FF]/,
'Mongolian': /[\u1800-\u18AF]/,
'Limbu': /[\u1900-\u194F]/,
'Tai_Le': /[\u1950-\u197F]/
}
for(var language in unicodeMap) {
if(ALLOWED_SCRIPTS.indexOf(language) > -1) {
continue;
}
var regex = unicodeMap[language];
if(regex.test(str)) {
return false;
}
}
return true;
}
function getGoogleAdsFormattedDate(d, format){
var date = new Date();
date.setDate(date.getDate() - d);
return Utilities.formatDate(date,AdsApp.currentAccount().getTimeZone(),format);
}
@blastofff
Copy link

Hi! Just the script I was looking for, but it doesn't seem to work with Youtube channels? It gives an error "This type of criterion cannot target YouTube websites". Is this possible to fix? Cheers!

@SjoerdWeConnect
Copy link

Thank you for this! works well

@arshammm
Copy link

I modified this to work for ONLY Youtube Channels. Thank you for the base/template -- Optmyzr rules!

@arillera
Copy link

arillera commented Nov 6, 2023

I modified this to work for ONLY Youtube Channels. Thank you for the base/template -- Optmyzr rules!

How did you modify it to work for YouTube channels? Would love to do the same.

@dimatakoy
Copy link

Hello, @arillera, did you find a solution?

@dimatakoy
Copy link

Hello, @blastofff, did you find a solution?

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