Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
/**
* @name Create Negative KW List
*
* @overview The Create Negative KW List scripts uses Search Query Performance
* Report to find undesired search terms and add them as negative (or
* positive) exact keywords. See
* https://developers.google.com/google-ads/scripts/docs/solutions/search-query
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 1.0.1
*
* @changelog
* - version 1.0.1
* - Upgrade to API version v201609.
* - version 1.0
* - Released initial version.
*/
// Minimum number of impressions to consider "enough data"
var IMPRESSIONS_THRESHOLD = 300;
// Name of Campaign
var CAMPAIGN_NAME = "Shopping";
// Name of Negative KW List
var NEGATIVE_KW_LIST = "Automated-NegativeKWL-Shopping";
// Cost-per-click (in account currency) we consider an expensive keyword.
var AVERAGE_CPC_THRESHOLD = 1; // $1
// Threshold we use to decide if a keyword is a good performer or bad.
var CTR_THRESHOLD = 8; // 0.5%
// Cost Threshold
var COST_THRESHOLD = 1; // $4
// Conversion Threshold
var CONVERSIONS_THRESHOLD = 1; // 1
// One currency unit is one million micro amount.
var MICRO_AMOUNT_MULTIPLIER = 1;
// Spreadsheet URL
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1rLuHlBlc5IHo_hT8TZWCFUTz8fLO1CGHfcdSvXyiYwI/';
// Name of the spreadsheet sheet/tab
var SHEET_NAME = 'NegativeKWListShopping';
function main() {
var DATE_START = getStartDate();
var DATE_END = getTodaysDate();
DATE_RANGE = getStartDate()+','+getTodaysDate();
Logger.log('Running Query for Date Range : '+DATE_RANGE);
var report = AdsApp.report(
'SELECT CampaignName, AdGroupName, Query, Clicks, Cost, Ctr, ConversionRate,' +
' CostPerConversion, Impressions, Conversions, CampaignId, AdGroupId ' +
' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
' WHERE ' +
' Conversions < ' + CONVERSIONS_THRESHOLD +
' AND Impressions > ' + IMPRESSIONS_THRESHOLD +
' AND Cost > ' + COST_THRESHOLD*1000000 +
' AND CampaignName CONTAINS_IGNORE_CASE "'+CAMPAIGN_NAME+'" ' +
' DURING '+DATE_RANGE);
var rows = report.rows();
Logger.log('Impressions Threshold %s CTR %s Cost Threshold %s', IMPRESSIONS_THRESHOLD, CTR_THRESHOLD, COST_THRESHOLD);
// Retrieve the spreadsheet
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
// Retrieve the tab/sheet within the spreadsheet
var sheet = spreadsheet.getSheetByName(SHEET_NAME);
// Get the Negative KW List
var nkwList = getNegativeKeywordList();
// Array that contains negative KWs
var negKwsToAdd = [];
// Clear the list first (you can modify this to append instead of clear)
clearNegativeKeywordList(nkwList);
// Header row for spreadsheet
sheet.appendRow(['Search Term', 'Impressions', 'Ctr', 'Cost', 'Conversions']);
// Iterate through search query and decide whether to
// add them as negative keywords (or ignore).
while (rows.hasNext()) {
var row = rows.next();
//Logger.log('Processing Campaign Name %s Ad Group Name %s Search Query %s Impressions %s CTR %s Cost %s Conversions %s', row['CampaignName'], row['AdGroupName'], row['Query'], row['Impressions'], row['Ctr'], row['Cost'], row['Conversions']);
sheet.appendRow([row['Query'], row['Impressions'], row['Ctr'], row['Cost'], row['Conversions']]);
// Add this keyword to the array
negKwsToAdd.push('['+row["Query"]+']');
}
// Print # of Keywords to add
Logger.log(" Number of Negative KWs to add: [%s]", negKwsToAdd.length);
//Logger.log("NegativeKWSToAdd [%s] : %s", negKwsToAdd.length , negKwsToAdd.toString());
// Add the array of negative keywords to the list
nkwList.addNegativeKeywords(negKwsToAdd);
//listKeywordsInList(nkwList);
}
// gets the date of January 01 of the current year in YYYYMMDD format
function getStartDate() {
var today = new Date();
var yyyy = today.getFullYear();
var startDate = yyyy+'01'+'01';
return startDate;
}
// gets Todays Date in YYYYMMDD format
function getTodaysDate() {
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1; //January is 0!
var yyyy = today.getFullYear();
if(dd<10) {
dd = '0'+dd
}
if(mm<10) {
mm = '0'+mm
}
today = yyyy+mm+dd; /*+mm + '/' + dd + '/' + yyyy;*/
return today;
}
// Returns the negative keyword list (if it exists)
function getNegativeKeywordList() {
var negativeKeywordListSelector = AdsApp.negativeKeywordLists()
.withCondition("Name CONTAINS '"+NEGATIVE_KW_LIST+"'")
.withLimit(1);
var negativeKeywordListIterator = negativeKeywordListSelector.get();
var negativeKeywordList;
//Logger.log("This iterator has %s lists", negativeKeywordListIterator.totalNumEntities());
while (negativeKeywordListIterator.hasNext()) {
negativeKeywordList = negativeKeywordListIterator.next();
Logger.log("Name of the list is %s", negativeKeywordList.getName());
}
return negativeKeywordList;
}
// This function clears the negative keyword lists
// Removes/Empty the Negative KW List
function clearNegativeKeywordList(list) {
//Logger.log("clearNegativeKeywordList ");
var sharedNegativeKeywordIterator =
list.negativeKeywords().get();
while (sharedNegativeKeywordIterator.hasNext()) {
sharedNegativeKeywordIterator.next().remove();
}
}
// Displays all the keywords in the list
function listKeywordsInList(list) {
var sharedNegativeKeywordIterator = list.negativeKeywords().get();
var sharedNegativeKeywords = [];
while (sharedNegativeKeywordIterator.hasNext()) {
sharedNegativeKeywords.push(sharedNegativeKeywordIterator.next());
}
for (var i = 0; i < sharedNegativeKeywords.length; i++) {
Logger.log("Negative Keyword : %s", sharedNegativeKeywords[i].toString());
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment