CreateNegativeKWList-Shopping.js

// 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());
    }  
  
  
}