Skip to content

Instantly share code, notes, and snippets.

@norisk-marketing

norisk-marketing/CLIENT_nrNegativeKeywordListSync.js Secret

Last active July 31, 2017 14:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save norisk-marketing/d993e623435df0dfe303ce644d29a9a1 to your computer and use it in GitHub Desktop.
Save norisk-marketing/d993e623435df0dfe303ce644d29a9a1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
readme.md

AdWords Negative Keyword List Automation (by norisk)

General Information

This script uses a csv-source to generate a list of negative Keywords to create in an AdWords Account. In case the limit of 5000 entities per negative keyword list is exceeded a new list will be created automatically. The new lists must be added to specific campaigns manually. Duplicate keywords won't be added.

Installation

To install the programm copy the file CLIENT_nrNegativeKeywordListSync.js into the AdWords Scripts Editor of your Account and fill in all required configuration variables. Before the script runs for the first time allow the script to

  • manage your AdWords campaigns
  • connect to an external service

Please make sure that your database doesn't contain any non-ASCII characters like é, à, etc. as negative keywords containing characters like those won't work.

Configuration variables

NEGATIVE_LIST_IDENTIFIER

var NEGATIVE_LIST_IDENTIFIER = "EXAMPLE_LIST";

@String: This is the identifier of your negative keyword list cluster this script works with. Make sure that no other cluster has the same identifier.

FEEDURL

var FEEDURL = "http://example.de/EXAMPLE_FEED.csv";

@String: URL of your feed.

SEPARATOR

var SEPARATOR = "~";

@char: CSV seperator of your feed.

COLUMN_INDEX

var COLUMN_INDEX = 0;

@int: column in your feed you want to extract data from. Start counting from 0. E.g column A has the index 0.

OWN_BRAND

var OWN_BRAND = ["Example brand","examplebrand","example-brand"];

@Array: all variations of your brand name, which won't be included in the negative list.

First run

After you run the script for the first time, please make sure to check the created negative lists for problems.

Contact

In case you have any questions, please contact us at norisk: Alexander Groß or Christopher Gutknecht

Raw
CLIENT_nrNegativeKeywordListSync.js
/**
* AdWords Titel/Brand To Negative List Sync
* @version: 3.0.1
* @author: Alexander Groß
* norisk GmbH
* cgutknecht@noriskshop.de
* agross@noriskshop.de
*
***********
*
* Script that synchronizes your Google Merchant Center product feed with a specific set of shared negative keyword lists.
*
***********
*
* THIS SOFTWARE IS PROVIDED BY norisk GMBH ''AS IS'' AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL norisk GMBH BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*
*/
/****** CONFIG BLOCK ******/
var NEGATIVE_LIST_IDENTIFIER = "Test_Liste"; // @String: This is the identifier of your negative keyword list cluster this script works with. Make sure that no other cluster has the same identifier.
var FEEDURL = "FEEDURL"; //@String: URL of your feed.
var SEPARATOR = '~'; //@char: CSV seperator of your feed.
var COLUMN_INDEX = 0; //@int: column in your feed you want to extract data from. Start counting from 0. E.g column A has the index 0.
var OWN_BRAND = []; //@Array<String>: all variations of your brand name, which won't be included in the negative list.
/****** CONFIG BLOCK ******/
/************************************************/
/****** DO NOT CHANGE CODE BELOW THIS LINE ******/
/************************************************/
function main(){
var scriptfile_name = "https://gist.githubusercontent.com/norisk-marketing/d993e623435df0dfe303ce644d29a9a1/raw/724b8a5ddc78041e12933f65e450cd220436ebc4/nrNegativeKeywordListSync.js";
var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();
eval(scriptFile_raw);
Logger.log("Script loaded.");
var script = new nrNegativeKeywordlistSync();
script.main();
}
Raw
nrNegativeKeywordListSync.js
/**
* AdWords Titel/Brand To Negative List Sync
* @version: 3.0.1
* @author: Alexander Groß
* norisk GmbH
* cgutknecht@noriskshop.de
* agross@noriskshop.de
*
************
* READ ME!!!!
* @changeLog:
* v1.0: Scalability added
* v1.0.1: Keyword matchtype changed to phrase.
* v1.0.2: Bugfix: Error while comparing lists due to new match type.
* v1.0.3: Bugfix: Error due to special character replace/whitespacing.
* v1.0.4: Bugfix: Error due to special character replace/whitespacing. Only core must be updated.
* v2.0.0: Script has been refactored.
* EVERYTHING MUST BE UPDATED
* v3.0.0: Client-/Server Implementation
* v3.0.1: Minor changes
************
*
* Script that synchronizes your Google Merchant Center product feed with a specific set of shared negative keyword lists.
*
***********
*
* THIS SOFTWARE IS PROVIDED BY norisk GMBH ''AS IS'' AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL norisk GMBH BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
function nrNegativeKeywordlistSync(){
this.main = function() {
//Fetch all the necessary data to process
init();
var spreadsheet = UrlFetchApp.fetch(FEEDURL);
spreadsheet = Utilities.parseCsv(spreadsheet, SEPARATOR);
var entityNames = getVendorNames(spreadsheet);
var sharedNegativeKeywords = getSharedLists();
var newSharedNegativeKeywords = compareLists(sharedNegativeKeywords,entityNames);
while(newSharedNegativeKeywords.length > 0){
Logger.log("List update in progress...");
var negativeListIterator = AdWordsApp.negativeKeywordLists().withCondition("Name CONTAINS '"+NEGATIVE_LIST_IDENTIFIER+"'").get();
var numberOfCurrentLists = negativeListIterator.totalNumEntities();
if(numberOfCurrentLists!=0){
for(var i = 1; (i < numberOfCurrentLists+1)&&(newSharedNegativeKeywords.length>0); i++){
try{
var list = AdWordsApp.negativeKeywordLists().withCondition("Name CONTAINS '"+NEGATIVE_LIST_IDENTIFIER+"_"+i+"'").get().next();
}
catch(e){
throw new Error("Please make sure that there is a preceding list for every list except for '_1' (i.e. there must be a '_1' list if there is a '_2' list.");
}
var negativeKeywordIterator = list.negativeKeywords().get();
var keywordAmount = negativeKeywordIterator.totalNumEntities();
var remainingSlots = 5000 - keywordAmount;
if(remainingSlots > 0){
var keywordArray = newSharedNegativeKeywords.slice(0, remainingSlots);
newSharedNegativeKeywords = newSharedNegativeKeywords.slice(remainingSlots+1);
listPush(list, keywordArray);
}
else if((i==(numberOfCurrentLists))&&(remainingSlots==0)){
createNewListByName(NEGATIVE_LIST_IDENTIFIER+"_"+(i+1));
break;
}
}
}
else{
createNewListByName(NEGATIVE_LIST_IDENTIFIER+"_1");
}
}
Logger.log("Lists updated successfully.");
}
function getVendorNames(spreadsheet){ // Does not only refer to vendor names, can be any type of object.
Logger.log("Initializing database...");
var vendorNames = [];
for(i=1;i<spreadsheet.length;i++){
var vendorName = spreadsheet[i][COLUMN_INDEX];
vendorName = vendorName.replace(/@/g,'').replace(/\+/g,'').replace(/\|-/g,'').replace(/\®/g,'').replace(/!/g,'').replace(/\%/g,'').replace(/\./g,'').replace(/\(/g,'').replace(/\)/g,'').replace(/\,/g,'').replace(/\`/g,'').replace(/\´/g,'').replace(/\…/g,'').replace(/\+/g,'').replace(/\&/g,'').replace(/\;/g,'').replace(/\"/g,'').replace(/\//g,'').replace(/\-/g,'').replace(/\?/g,'').replace(/\°/g,'').replace(/\'/g,'').replace(/repl-/g,'').replace(/\*/g,'').replace(/™/g,'').replace(/©/g,'').replace(/(^[\s]+|[\s]+$)/g,'').replace(/®/g,'').replace(/®/g,'').replace(/°/g,'').replace(/~/g,'').replace(/\s\s+/g,' ');
if(checkIfOwnBrand(vendorName))
continue;
else{
vendorNames.push(vendorName);
}
}
Logger.log('The number of unique titles is: ' + vendorNames.length+". @getVendorNames()");
Logger.log("Database initialized.");
return vendorNames;
}
function checkIfOwnBrand(vendorName){
if(OWN_BRAND.indexOf(vendorName)==-1)
return false;
return true;
}
function getSharedLists(){
Logger.log("Initializing shared lists...");
var sharedNegativeKeywords = [];
var negativeListIterator = AdWordsApp.negativeKeywordLists().withCondition("Name CONTAINS '" + NEGATIVE_LIST_IDENTIFIER + "'").get();
Logger.log('Number of Lists found: ' + negativeListIterator.totalNumEntities()+". @getSharedLists()");
while (negativeListIterator.hasNext()) {
var negativeList = negativeListIterator.next();
var sharedNegativeKeywordIterator = negativeList.negativeKeywords().get();
while (sharedNegativeKeywordIterator.hasNext()) {
sharedNegativeKeywords.push(sharedNegativeKeywordIterator.next().getText().replace(/\"/g, '').replace(/(^[\s]+|[\s]+$)/g, ''));
}
}
Logger.log("Merged number of negatives found: " + sharedNegativeKeywords.length+". @getSharedLists()");
Logger.log("Shared lists initialized.");
return sharedNegativeKeywords;
}
function compareLists(sharedNegativeKeywords,vendorNames){
Logger.log("Comparing database and shared lists...");
var newSharedNegativeKeywords = [];
for(i=0;i<vendorNames.length;i++){
var vendor = vendorNames[i].toString();
if(sharedNegativeKeywords.indexOf(vendor)==-1)
newSharedNegativeKeywords.push(vendor.replace(/(^[\s]+|[\s]+$)/g, ''));
}
Logger.log("New Keywords: "+newSharedNegativeKeywords.length+". @compareLists()");
Logger.log("Database and shared lists compared.");
return newSharedNegativeKeywords;
}
function listPush(negativeList, newNegativeKeywordArray) {
try {
for(var i=0;i<newNegativeKeywordArray.length;i++) {
var negativeKeyword = newNegativeKeywordArray[i];
if(negativeKeyword)
negativeList.addNegativeKeyword('"'+negativeKeyword+'"');
}
Logger.log("The list '" + negativeList.getName() + "' received this update amount: " + newNegativeKeywordArray.length+". @listPush()");
}
catch(e) {
Logger.log("List "+negativeList.getName()+" could not be updated. @listPush()");
}
}
function createNewListByName(newNegativeList) {
Logger.log("New list being created: "+newNegativeList+"... @createNewListByName()");
Logger.log("New list created.");
return AdWordsApp.newNegativeKeywordListBuilder()
.withName(newNegativeList)
.build()
.getResult();
}
function init(){
var scriptfile_name = "https://scripts.adserver.cc/getScript.php?package=nrUtils&version=latest&script=index.js&aid=000-111-222-333&key=oWbnQ45R2pSMWx1dhZNhVApTT3O8tTRP";
var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();
eval(scriptFile_raw);
var script = new nrTr();
script.main("nrNegativeKeywordListSync");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment