-
Notifications
You must be signed in to change notification settings - Fork 0
/
code.js
171 lines (162 loc) · 7.41 KB
/
code.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
/* _ _ __ __ _
* __ _ __| | |_ _ __ __ _ / _|/ _(_) ___
* / _` |/ _` | __| '__/ _` | |_| |_| |/ __|
* | (_| | (_| | |_| | | (_| | _| _| | (__
* \__,_|\__,_|\__|_| \__,_|_| |_| |_|\___|
*
* E: [email protected] | W: www.adtraffic.de
*
* YouTube Channel Excluder 23.1
*
* @desc: Use this Google Ads Script to autpmatically
* exclude YouTube channels in irrelevant countries.
*
* @author: @ric_sti (Twitter)
* @version: 1.0
* @github-slug: google-ads-script-youtube-channel-excluder
*
*/
var SPREADSHEET_URL = "INSERT_YOUR_SPREADSHEET_URL";
var SHEET_NAME = "INSERT_YOUR_SPREAD_NAME";
var EMAIL_ADRESSES = "INSERT_YOUR_E-MAIL_ADRESSES"; // comma separated
var allowedCountries = /^DE$/; // Provide RegEx, e.g. /^DE$/ or /^DE|AT|CH$/
var impressionsTimeRange = "LAST_7_DAYS";
var impressionsThreshold = 10;
function main() {
// Prepare an array for logging purposes
var allExclusions = [];
// Fetch all active video campaigns and start iterating
var videoCampaignIterator = AdsApp.videoCampaigns().withCondition("Status = 'ENABLED'").get();
while (videoCampaignIterator.hasNext()) {
var alreadyExcludedYouTubeChannels = [];
var videoCampaign = videoCampaignIterator.next();
// Fetch all already excluded YouTube Channels from the processed campaign and store them in an array
var exludedYoutubeChannelIterator = videoCampaign.videoTargeting().excludedYouTubeChannels().get();
while (exludedYoutubeChannelIterator.hasNext()) {
var excludedYouTubeChannel = exludedYoutubeChannelIterator.next();
alreadyExcludedYouTubeChannels.push(excludedYouTubeChannel.getChannelId());
}
// Build a batch with to be excluded YouTube channels
const toBeExcludedYouTubeChannels = checkYouTubeChannels(videoCampaign.getId(),alreadyExcludedYouTubeChannels);
// Exclude all YouTube channels in batch from campaign
for (i=0;i<toBeExcludedYouTubeChannels.length;i++){
videoCampaign.videoTargeting().newYouTubeChannelBuilder().withChannelId(toBeExcludedYouTubeChannels[i].id).exclude();
}
// Add logging information to log array
allExclusions.push({ campaign: videoCampaign.getName(), excludedYouTubeChannels: toBeExcludedYouTubeChannels })
}
// Prepare and send a notification email with logging information
sendConfirmationEmail(allExclusions);
}
function checkYouTubeChannels(campaignId,alreadyExcludedYouTubeChannels){
// Prepare an array for the return values
var toBeExcludedYouTubeChannels = [];
// Load currently known allowed YouTube Channels from Google Sheet
var knownAllowedYouTubeChannels = getAllowedYouTubeChannels();
// Prepare an array for newly identified allowed YouTube Channels to be added to the Google Sheet
var newAllowedYouTubeChannels = [];
// Query all YouTube placements of the processed campaign with at least ${impressionsThreshold} impressions within the ${impressionsTimeRange}
var query =
"SELECT detail_placement_view.display_name, detail_placement_view.group_placement_target_url FROM detail_placement_view " +
"WHERE " +
" campaign.id = " + campaignId +
" AND segments.date DURING " + impressionsTimeRange +
" AND metrics.impressions > " + impressionsThreshold +
" AND detail_placement_view.placement_type IN ('YOUTUBE_VIDEO','YOUTUBE_CHANNEL') " +
" AND detail_placement_view.group_placement_target_url IS NOT NULL " +
" AND detail_placement_view.group_placement_target_url NOT REGEXP_MATCH '" + alreadyExcludedYouTubeChannels.join("|") + "'" +
" AND detail_placement_view.group_placement_target_url NOT REGEXP_MATCH '" + knownAllowedYouTubeChannels.join("|") + "'" +
""
;
var result = AdsApp.search(query);
console.log(result.totalNumEntities() + " YouTube Placements found.");
while (result.hasNext()) {
var row = result.next();
const channelId = row['detailPlacementView']['groupPlacementTargetUrl'].match(/^.*\/([^\/]*)$/)[1];
const channelName = row['detailPlacementView']['displayName'];
const channelInfo = getChannelInfo(channelId); // [ Country, Default Language ]
if ( channelInfo && channelInfo[0]){
if (channelInfo[0].match(allowedCountries) && knownAllowedYouTubeChannels.indexOf(channelId) == -1 && newAllowedYouTubeChannels.indexOf(channelId) == -1){
// Store channel id of newly identified German YouTube Channel in array to be added to Google Sheet
newAllowedYouTubeChannels.push(channelId);
}
else if (!channelInfo[0].match(allowedCountries)){
if ( alreadyExcludedYouTubeChannels.indexOf(channelId) == -1){
toBeExcludedYouTubeChannels.push(
{
id: channelId,
name: channelName,
country: channelInfo[0],
language: channelInfo[1]
}
);
}
}
}
}
if (newAllowedYouTubeChannels.length > 0){
saveAllowedYouTubeChannels(newAllowedYouTubeChannels);
newAllowedYouTubeChannels.length.toLocaleString("de") + " new allowed Channel(s) found." + "\n\n";
}
return toBeExcludedYouTubeChannels.sort();
}
function getChannelInfo(channelId){
try {
const results = YouTube.Channels.list('snippet,localizations', {
id: channelId,
maxResults: 1
});
if (results === null) {
console.log(`ID ${channelId}: Unable to search videos`);
return;
}
return [ results.items[0].snippet.country, results.items[0].snippet.defaultLanguage ];
} catch (err) {
// TODO (developer) - Handle exceptions from Youtube API
console.log(`ID ${channelId}: Failed with an error: %s`, err.message);
}
}
function getAllowedYouTubeChannels(){
var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName(SHEET_NAME);
var lastRow = sheet.getLastRow();
var range = sheet.getRange(1, 1, lastRow);
var returnValues = [];
var values = range.getValues();
for (var row in values) {
for (var col in values[row]) {
returnValues.push(values[row][col]);
}
}
return returnValues;
}
function saveAllowedYouTubeChannels(newAllowedYouTubeChannels){
var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName(SHEET_NAME);
var lastRow = sheet.getLastRow();
var range = sheet.getRange(lastRow+1,1,newAllowedYouTubeChannels.length);
var outputValues = [];
while(newAllowedYouTubeChannels.length) outputValues.push(newAllowedYouTubeChannels.splice(0,1));
range.setValues(outputValues);
sheet.sort(1);
}
function sendConfirmationEmail(allExclusions){
var output = "";
for (i=0;i<allExclusions.length;i++){
output += allExclusions[i].excludedYouTubeChannels.length.toLocaleString("de") + " Channel(s) excluded from Campaign " + allExclusions[i].campaign + "\n\n";
var outputChannels = "";
for (j=0;j<allExclusions[i].excludedYouTubeChannels.length;j++){
outputChannels += allExclusions[i].excludedYouTubeChannels[j].name + " (" + allExclusions[i].excludedYouTubeChannels[j].country + "|" + allExclusions[i].excludedYouTubeChannels[j].language + ")\n";
outputChannels += "https://youtube.com/channel/" + allExclusions[i].excludedYouTubeChannels[j].id + "\n\n";
}
output += outputChannels;
}
var eMailAddress = EMAIL_ADRESSES;
var eMailSubject = "YouTube Channel Excluder";
var eMailContent = output;
console.log(output);
sendSimpleTextEmail(eMailAddress,eMailSubject,eMailContent)
}
// HELPERS
function sendSimpleTextEmail(eMailAddress,eMailSubject,eMailContent) {
MailApp.sendEmail(eMailAddress,eMailSubject,eMailContent);
Logger.log("Mail sent.");
}