Last active
July 25, 2024 11:14
-
-
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
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
/************************************************* | |
* 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); | |
} |
Thank you for this! works well
I modified this to work for ONLY Youtube Channels. Thank you for the base/template -- Optmyzr rules!
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.
Hello, @arillera, did you find a solution?
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
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!