Last active
December 17, 2018 20:36
-
-
Save kazdegroot/4c1b6bad6d31d495c3078af08d3b8f7a to your computer and use it in GitHub Desktop.
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
//------------------------------------------------ | |
// Auto add negative keywords to all campaigns | |
// Created by: Remko van der Zwaag & PDDS | |
// remkovanderzwaag.nl & pdds.nl | |
// More info: http://goo.gl/64PrMT | |
// 05-04-2016: New and improved! | |
//------------------------------------------------ | |
// This is a hybrid script, and will work on both account and MCC level | |
var spreadsheetId = '171OUhavrxfEv7xZg4jFr5ywQSZUKo_IS4nwM3m14aoU';// Google Spreadsheet with account info | |
// Can be shared with the 'normal' CCKE sheet | |
// if using our standard sheet and fieldmapping | |
var prefillSpreadsheet = false; // When set to true, gets all accounts from the MCC account | |
// and automagically adds their name and id to the spreadsheet | |
// Use once, doesn't check for existing records | |
// switch back to false after use | |
// PREFERABLY RUN USING PREVIEW (true), CHANGE TO false AND SAVE | |
// The prefix of the negative keyword lists | |
// These lists will be created if necessary, and with the exception of adding them | |
// to the campaign, are the only objects to be modified. | |
// Because of the limit of 20 lists, we set a list limit | |
// and break if there are more campaigns than the number of lists allowed. | |
// (Might still break if you do not take the amount of existing lists on the account into account) | |
// * Default - overwritable from spreadsheet | |
var negative_list = 'RvdZ - CCKE'; | |
var max_list_num = 10; | |
// Should we add the negative keyword with the same match type? | |
// Otherwise adds as EXACT | |
var keep_type = true; | |
// Special cases etc. | |
// DO NOT TOUCH below this line unless you know what you're doing. | |
// These are set to safe defaults that work for most cases. | |
// Clear the negative keyword lists before we fill them. | |
// If you switch this off, weird things might happen. | |
var clear_list = true; | |
// Minimum number of impressions to consider keywords | |
// Should be tweaked in large accounts (x0.000+ keywords) to function correctly | |
var MIN_IMPRESSIONS = 1; | |
// The label on the source campaigns, leave empty to use all campaigns | |
// * Default - overwritable from spreadsheet | |
// * This value will only be overwritten by the spreadsheet value, | |
// if the spreadsheet has a value, so this unless you _always_ want | |
// to filter based on label, this should remail empty! | |
var campaign_label = ''; | |
// Mapping from spreadsheet rows to script settings. | |
// Empty spreadsheet fields are filled with the defaults | |
// If skip is set, and true, the script will skip that row | |
// (Used here for interaction with the general CCKE script) | |
// You can modify this function to make this script work | |
// with a custom formatted spreadsheet. | |
function mapRowToInfo(row) { | |
return { | |
custId: row[1].trim(), | |
cust: row[0], | |
include_label: row[2], | |
list_prefix: row[4], | |
list_amount: row[5], | |
skip: (row[6] != 'Yes') | |
}; | |
} | |
// Some facts about the world | |
// please don't touch unless google changes this | |
var MAX_NEG_LISTS = 20; | |
var MAX_ITEMS_PER_LIST = 5000; | |
function main() { | |
try { | |
// Uses parallel execution. Is limited to 50 accounts by Google. | |
if (prefillSpreadsheet) { | |
MccApp.accounts() | |
.withLimit(50) | |
.executeInParallel("getSSAccountInfo","saveSSAccountInfo"); | |
} else { | |
var ids = getSpreadsheetIds(); | |
if (ids.length > 0) { | |
MccApp.accounts() | |
.withIds(ids) | |
.withLimit(50) | |
.executeInParallel("processAccount"); | |
} | |
} | |
} catch (e) { | |
processAccount(); | |
} | |
beacon(); | |
} | |
// Get account name and id | |
function getSSAccountInfo() { | |
var result = { | |
custId: AdWordsApp.currentAccount().getCustomerId(), | |
cust: AdWordsApp.currentAccount().getName() | |
}; | |
Logger.log(result); | |
return JSON.stringify(result); | |
} | |
// Save account info to the spreadsheet | |
function saveSSAccountInfo(response) { | |
var ss; | |
try { | |
ss = SpreadsheetApp.openById(spreadsheetId); | |
} catch (e) { | |
} | |
ss = ss.getSheets()[0]; | |
ss.appendRow(["Account Name", "Account ID", "Source Label", "-", "List Prefix", "List Amount", 'Run Auto']); | |
for (var i in response) { | |
if(!response[i].getReturnValue()) { continue; } | |
var rep = JSON.parse(response[i].getReturnValue()); | |
Logger.log(rep); | |
ss.appendRow([rep.cust, rep.custId]); | |
} | |
} | |
function getSpreadsheetIds() { | |
var ids = [], | |
ss, | |
reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/; | |
try { | |
ss = SpreadsheetApp.openById(spreadsheetId); | |
} catch (e) { | |
return ids; | |
} | |
ss = ss.getSheets()[0]; | |
var rows = parseInt(ss.getLastRow()); | |
var range = ss.getRange("A1:Z" + rows).getValues(); | |
for (var i = 0; i < rows; i++) { | |
var account = mapRowToInfo(range[i]); | |
if (!reAWId.test(account.custId) || account.skip) { | |
continue; | |
} | |
ids.push(account.custId); | |
} | |
return ids; | |
} | |
// Fetch info for current account from the spreadsheet | |
// MCC scripts don't seem to support shared state between | |
// Parallel executions, so we need to do this fresh for every account | |
// Uses default info from 'defaults' set in script, and replaces with | |
// values from spreadsheet where possible | |
function getAccountInfo() { | |
var ss; | |
var reAWId = /^([0-9]{3})-([0-9]{3})-([0-9]{4})$/; | |
var protoAccount = { | |
custId: AdWordsApp.currentAccount().getCustomerId(), | |
cust: AdWordsApp.currentAccount().getName(), | |
include_label: campaign_label, | |
list_prefix: negative_list, | |
list_amount: max_list_num | |
}; | |
try { | |
ss = SpreadsheetApp.openById(spreadsheetId); | |
} catch (e) { | |
return protoAccount; | |
} | |
ss = ss.getSheets()[0]; | |
var rows = parseInt(ss.getLastRow()); | |
var range = ss.getRange("A1:Z" + rows).getValues(); | |
var ret_account; | |
for (var i = 0; i < rows; i++) { | |
var account = mapRowToInfo(range[i]); | |
if (account.skip) { | |
continue; | |
} | |
if (!reAWId.test(account.custId) || account.custId !== protoAccount.custId) { | |
continue; | |
} | |
for(var key in account) { | |
if (account[key] === '' || account[key] === '_ALL_') { | |
account[key] = protoAccount[key]; | |
} | |
} | |
return account; | |
} | |
return protoAccount; | |
} | |
function getIteratorAsString(it) { | |
var list = it.get(); | |
var text = []; | |
while (list.hasNext()) { | |
text.push(list.next().getName()); | |
} | |
return text.join(', '); | |
} | |
function getNegListByName(name) { | |
var negativeKeywordListIterator = AdWordsApp.negativeKeywordLists() | |
.withCondition("Name = '" + name + "'") | |
.get(); | |
if (negativeKeywordListIterator.totalNumEntities() != 0) { | |
while (negativeKeywordListIterator.hasNext()) { | |
var negativeKeywordList = negativeKeywordListIterator.next(); | |
if (clear_list) { | |
clearNegativeKeywordList(negativeKeywordList); | |
} | |
return negativeKeywordList; | |
} | |
} | |
var builder = AdWordsApp.newNegativeKeywordListBuilder() | |
.withName(name) | |
.build(); | |
return builder.getResult(); | |
} | |
function buildCampaignList(account, campaign) { | |
var name = account.list_prefix + ' - ' + campaign.getName(); | |
var list = getNegListByName(name); | |
var keywords = getAllKeywords([campaign.getId()]); | |
addKeywordsToLists(keywords, [list]); | |
return list; | |
} | |
function processAccount() { | |
var account = getAccountInfo(); | |
Logger.log(account); | |
var campaigns = AdWordsApp.campaigns().withCondition("Status = ENABLED"); | |
if (account.include_label !== '') { | |
Logger.log('Filter on label: ' + account.include_label); | |
campaigns = campaigns.withCondition("LabelNames CONTAINS_ANY ['" + account.include_label + "']"); | |
} | |
var campaignIterator = campaigns.get(); | |
var negatives = {}; | |
if (account.list_amount < campaignIterator.totalNumEntities()) { | |
Logger.log('More campaigns than allowed number of lists :( : ' + campaignIterator.totalNumEntities()); | |
return; | |
} | |
while (campaignIterator.hasNext()) { | |
var campaign = campaignIterator.next(); | |
var name = campaign.getName(); | |
var locations = getIteratorAsString(campaign.targeting().targetedLocations()); | |
negatives[name] = { | |
location: locations, | |
name: name, | |
list: buildCampaignList(account, campaign) | |
}; | |
} | |
campaignIterator = AdWordsApp.campaigns().withCondition("Status = ENABLED").get(); | |
while (campaignIterator.hasNext()) { | |
var campaign = campaignIterator.next(); | |
var name = campaign.getName(); | |
var locations = getIteratorAsString(campaign.targeting().targetedLocations()); | |
for (var other in negatives) { | |
if (other === name) { | |
continue; | |
} | |
var otherList = negatives[other]; | |
if (locations === otherList.location) { | |
Logger.log([name, other]); | |
campaign.addNegativeKeywordList(otherList.list); | |
} | |
} | |
} | |
} | |
function addKeywordsToLists(keywords, lists) { | |
var numLists = Math.min(lists.length, Math.ceil(keywords.length/MAX_ITEMS_PER_LIST)); | |
for (var i = 0; i < numLists; i++) { | |
var keywordSlice = keywords.slice(i * MAX_ITEMS_PER_LIST, (i + 1) * MAX_ITEMS_PER_LIST); | |
lists[i].addNegativeKeywords(keywordSlice); | |
} | |
} | |
function makeOrUpdateListForCampaign() { | |
} | |
function addListsToCampaign(lists, campaigns) { | |
for (var j in campaigns) { | |
for (var i in lists) { | |
campaigns[j].addNegativeKeywordList(lists[i]); | |
} | |
} | |
} | |
function getNegativeKeywordLists(prefix, clear, minimum) { | |
if (minimum == undefined) { | |
minimum = 1; | |
} else if (minimum > MAX_NEG_LISTS) { | |
minimum = MAX_NEG_LISTS; | |
} | |
var lists = []; | |
var negativeKeywordListIterator = | |
AdWordsApp.negativeKeywordLists() | |
.withCondition("Name STARTS_WITH '" + prefix + " - '") | |
.get(); | |
if (negativeKeywordListIterator.totalNumEntities() != 0) { | |
while (negativeKeywordListIterator.hasNext()) { | |
var negativeKeywordList = negativeKeywordListIterator.next(); | |
if (clear) { | |
clearNegativeKeywordList(negativeKeywordList); | |
} | |
lists.push(negativeKeywordList); | |
} | |
} | |
while (lists.length < minimum) { | |
var builder = AdWordsApp.newNegativeKeywordListBuilder() | |
.withName(prefix + ' - ' + (lists.length + 1)) | |
.build(); | |
lists.push(builder.getResult()); | |
} | |
return lists; | |
} | |
function clearNegativeKeywordList(negativeKeywordList) { | |
var sharedNegativeKeywordIterator = | |
negativeKeywordList.negativeKeywords().get(); | |
while (sharedNegativeKeywordIterator.hasNext()) { | |
sharedNegativeKeywordIterator.next().remove(); | |
} | |
} | |
function getAllKeywords(campaignIds) { | |
return getAllKeywordsReport(campaignIds).slice(0,MAX_NEG_LISTS * MAX_ITEMS_PER_LIST); | |
} | |
// Use reports: moar speed | |
function getAllKeywordsReport(campaignIds) { | |
var result = {}; | |
var options = { includeZeroImpressions : true }; // Include keywords that aren't used | |
// AWQL query to find all keywords in the account | |
var query = "SELECT Criteria, KeywordMatchType " + | |
"FROM KEYWORDS_PERFORMANCE_REPORT " + | |
"WHERE " + | |
" Impressions >= " + MIN_IMPRESSIONS + " AND " + | |
" IsNegative = FALSE AND " + | |
" Status = ENABLED AND " + | |
" CampaignStatus = ENABLED AND " + | |
" AdGroupStatus = ENABLED AND " + | |
" AdNetworkType1 != 'CONTENT' AND " + | |
" AdNetworkType2 != 'CONTENT'"; | |
if (campaignIds.length > 0) { | |
query += " AND CampaignId IN [" + campaignIds.join(", ") + "]"; | |
} | |
query += " DURING LAST_7_DAYS"; | |
var keywords = AdWordsApp.report(query, options).rows(); | |
var i = 0; | |
while (keywords.hasNext()) { | |
i++; | |
var keyword = keywords.next(); | |
keyword = resetKeyword(keyword.Criteria, keyword.KeywordMatchType); | |
result[keyword] = true; | |
// Check every 10000 rows if the result is going to be over the maxiumum number of keywords we can use | |
if (i % 10000 == 0) { | |
if (Object.keys(result).length > MAX_NEG_LISTS * MAX_ITEMS_PER_LIST ) { | |
break; | |
} | |
} | |
} | |
result = Object.keys(result); | |
return result; | |
} | |
function resetKeyword(keyword, type) { | |
keyword = keyword.replace(/\+/g, ''); | |
if (!keep_type) { | |
if (keyword.substr(0,1) == '"' || keyword.substr(0,1) == '[') { | |
keyword = keyword.substr(1,-1); | |
} | |
keyword = '[' + keyword + ']'; | |
} else if (type) { | |
if (type == 'Exact') { | |
keyword = '[' + keyword + ']'; | |
} else if (type == 'Phrase') { | |
keyword = '"' + keyword + '"'; | |
} | |
} | |
return keyword; | |
} | |
function getCampaignWithLabel(label) { | |
var campaignIterator = getCampaignsWithLabel(label); | |
if (campaignIterator.hasNext()) { | |
return campaignIterator.next(); | |
} | |
return null; | |
} | |
function getCampaignsWithLabel(label) { | |
return AdWordsApp.campaigns() | |
.withCondition("LabelNames CONTAINS_ANY ['" + label + "']") | |
.get(); | |
} | |
function getCampaignsWithLabelArray(label) { | |
var iterator = getCampaignsWithLabel(label); | |
var campaigns = []; | |
while (iterator.hasNext()) { | |
campaigns.push(iterator.next()); | |
} | |
return campaigns; | |
} | |
/******************************** | |
* Track Script Runs in Google Analytics | |
* Created By: Russ Savage | |
* FreeAdWordsScripts.com | |
********************************/ | |
function beacon() { | |
var TAG_ID = 'UA-31686665-7'; | |
var CAMPAIGN_SOURCE = 'adwords'; | |
var CAMPAIGN_MEDIUM = 'scripts'; | |
var CAMPAIGN_NAME = 'script - negative keywords for DSA'; | |
var HOSTNAME = 'remkovanderzwaag.nl'; | |
var PAGE = '/scripts/dsa-negatives'; | |
var DOMAIN_LINK = 'http://'+HOSTNAME+PAGE; | |
//Pulled from: http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript | |
var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, | |
function(c) {var r = Math.random()*16|0,v=c=='x'?r:r&0x3|0x8;return v.toString(16);}); | |
var url = 'http://www.google-analytics.com/collect?'; | |
var payload = { | |
'v':1,'tid':TAG_ID,'cid':uuid, | |
't':'pageview','cs':CAMPAIGN_SOURCE,'cm':CAMPAIGN_MEDIUM,'cn':CAMPAIGN_NAME, | |
'dl':DOMAIN_LINK | |
}; | |
var qs = ''; | |
for(var key in payload) { | |
qs += key + '=' + encodeURIComponent(payload[key]) + '&'; | |
} | |
url += qs.substring(0,qs.length-1); | |
UrlFetchApp.fetch(url); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment