/**
*
* Google Ads Script - Negate Low Performing Search Terms
*
* This script creates negative keywords for search terms that had little to none conversions.
* Negatives are added at the ad group level.
* If new negatives are added the negatives are reported via email. 
* The email contains a link to Google Doc spreadsheet documenting all the negatives that have been added.
*
* INSTRUCTIONS:
* (If you've never installed a script before, be sure to read my instructions for installing scripts first: 
* https://nilsrooijmans.com/how-to-set-up-and-run-google-ads-scripts/ )
*
* 1. Create a new Google Sheet and copy the url
* 2. Look for the variable 'SPREADSHEET_URL' in the Options section below, and insert the spreadsheet url
* 3. Look for the variable 'EMAIL_ADDRESS' in the Options section below, and insert your email address
* 4. Look for the Thresholds in the Optios section below and configure your thresholds 
* 5. Preview the script, don't worry about messages that indicate failures for adding negatives during preview (these will go away once you actually Run the script)
* 6. Look at the output in the sheet, reconfigure thresholds if unhappy with the results 
* 7. Run the script
* 8. Schedule script to run daily/weekly
*
* Version: 1.2 - Added support for Shopping campaigns
* Version: 1.1 - Fixed bugs with PERIOD and fixed check to see if neg KW has successfully been added
* Version: 1.0
* (C) Nils Rooijmans , https://nilsrooijmans.com
*
* For an MCC version and more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com
*
* ---------------------------------------------------
**/

  //////////////////////////////////////////////////////////////////////////////
  // Options

  var SPREADSHEET_URL = ""; //insert url of new Google spreadsheet
  
  var PERIOD = "THIS_YEAR"; // the date range for your search term data, can be either "LAST_30_DAYS", "LAST_60_DAYS", "LAST_90_DAYS", or "THIS_YEAR"
  
  
  /*** THRESHOLDS ***/
  
  var MIN_CONVERSIONS = 0.5; // negate search terms that had less than this amount of conversions during the PERIOD you defined above
  
  var MIN_CLICKS = 100; // Only look at search terms that have had over this amount of clicks during the PERIOD you defined above
  
  var MIN_COST = 10; // Only look at search terms that accrued over this amount of cost during the PERIOD you defined above
  
  
  /*** CAMPAIGN FILTERS ***/
  
  var CAMPAIGN_NAME_CONTAINS = "";
    // Use this if you only want to process some campaigns
    // such as campaigns with names containing 'Brand' or 'Shopping'.
    // Leave as "" if you want to process all campaigns.
  
  var CAMPAIGN_NAME_DOES_NOT_CONTAIN = "";
    // Use this if you want to exclude some campaigns
    // such as campaigns with names containing 'Brand' or 'Shopping'.
    // Leave as "" if not wanted.
  
  
  /*** EMAIL CONFIGURATION SETTINGS ***/
  var SEND_EMAIL = true;
  
  var EMAIL_ADDRESS = ""; // insert your email address
  var EMAIL_SUBJECT = "New Negative Keywords added for Low Performing Search Terms ";
  var EMAIL_BODY = "This script looks at all enabled ad groups in all enabled campaigns, and"+
                    " creates negative keywords at the ad group level for search terms that had over "+MIN_CLICKS+" clicks"+
                    " AND over "+MIN_COST+"cost AND less than "+MIN_CONVERSIONS+" conversions during "+PERIOD+".\n\n" +
                    "You can find all negative keywords that have been added here: "+SPREADSHEET_URL+"\n\n"+ 
                    "---\n" + 
                    "This email is generated by the Google Ads Script - Negate Low Performance Search Terms, (C) Nils Rooijmans \n" +
                    "For an MCC version and more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" + 
                    "---\n";

  //////////////////////////////////////////////////////////////////////////////

/*** DON'T CHANGE ANYTHING BELOW THIS LINE ***/

var DEBUG = false;

function main() {
  
  var outputSheet = prepareOutputSheets();

  var result = getResults();
  var output = result.output;
  
  if (output.length > 0) {
    addOutputToSheet(output, outputSheet);
  }

  if(SEND_EMAIL && output.length > 0) {
    var emailSubject = "[Gads Script] "+AdsApp.currentAccount().getName()+" | "+EMAIL_SUBJECT;     
    var emailBody = EMAIL_BODY+"\nNumber of negative keywords added in total: "+output.length+"\n\n";
    MailApp.sendEmail(EMAIL_ADDRESS, emailSubject, emailBody) ;
  }
  
  if (result.issues) {
    var emailSubject = "### ERROR ### [Gads Script] "+AdsApp.currentAccount().getName()+" | "+EMAIL_SUBJECT; 
    var emailBody = EMAIL_BODY+"\nISSUE: Adding one or more negative keywords failed. Please check change log in script history.\n\n";
    MailApp.sendEmail(EMAIL_ADDRESS, emailSubject, emailBody) ;    
  }
  
}


function getResults() {

  var results = [];
  var date = getTodaysDate();
  var startDate = getStartDate(PERIOD);
  var endDate = getEndDate(PERIOD);
  
  var whereClause =       
      " WHERE metrics.clicks > " + MIN_CLICKS +
      " AND metrics.cost_micros > " + toMoney(MIN_COST) +
      " AND metrics.conversions < " + MIN_CONVERSIONS;
  
  if (CAMPAIGN_NAME_CONTAINS != "" ) {
    whereClause = whereClause +
      " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS + "%'";
  }
  
  if (CAMPAIGN_NAME_DOES_NOT_CONTAIN  != "" ) {
    whereClause = whereClause +
      " AND campaign.name NOT LIKE '%" + CAMPAIGN_NAME_DOES_NOT_CONTAIN  + "%'";
  }
  
  whereClause = whereClause +
      " AND campaign.status = ENABLED AND ad_group.status = ENABLED" +
      " AND segments.date BETWEEN '" + startDate + "' AND '" + endDate +"'";
      " ORDER BY metrics.clicks DESC" ;
      // + " LIMIT 10";

  var query = 
      "SELECT search_term_view.search_term, campaign.name, campaign.advertising_channel_type, ad_group.name, ad_group.id, metrics.impressions, metrics.clicks, metrics.average_cpc, metrics.cost_micros, metrics.conversions" +
      " FROM search_term_view" + whereClause;
  
  debug(query);
  
  var result = AdsApp.search(query, {apiVersion: 'v5'});
  
  var issueAddingNegativeKeyword = false; // no issues so far
  
  while (result.hasNext()) {
    
    var row = result.next();
    //debug(row);
    
    var searchTerm = row.searchTermView.searchTerm;
    var campaignName = row.campaign.name;
    var campaignType = row.campaign.advertisingChannelType;
    var adGroupName = row.adGroup.name;
    var adGroupId = row.adGroup.id;
    var impressions = row.metrics.impressions;
    var clicks = row.metrics.clicks;
    var ctr = clicks/impressions;
    var avgCPC = convertMoney(row.metrics.averageCpc);
    var cost = convertMoney(row.metrics.costMicros);
    var conversions = row.metrics.conversions;
    var conversionRate = conversions/clicks;
    var costPerConversion = cost/conversions;

    Logger.log("\nPotential Negative keyword candidate: "+searchTerm+" for ad group: "+adGroupName+" (adGroupId:"+adGroupId+") in campaign: '"+campaignName+"' of campaign type: "+campaignType);
    
    var isShoppingCampaign = (campaignType == 'SHOPPING');
    debug ("isShoppingCampaign: "+isShoppingCampaign);
    
    if(!isNegated(searchTerm, adGroupId, isShoppingCampaign)) {
      
      var status = addNegativeKeyword(searchTerm, adGroupId, isShoppingCampaign);
      debug("$addNegativeKeyword status="+status);
      
      if (status=="ok") {
        results.push([date, searchTerm, campaignName, adGroupName, impressions, clicks, ctr, avgCPC, cost, conversions, conversionRate, costPerConversion, "Negative keyword succesfully added"]);
      } else {
        Logger.log("### Failed to create negative keyword  "+searchTerm+" in adgroup '"+adGroupName+"' in campaign '"+campaignName);
        results.push([date, searchTerm, campaignName, adGroupName, impressions, clicks, ctr, avgCPC, cost, conversions, conversionRate, costPerConversion, "ISSUE: Adding Negative keyword failed. Please check change log in script history."]);
        issueAddingNegativeKeyword = true;
      }
    }   
  }  

  Logger.log("\nFinished analyses\nNumber of new negative keywords: "+results.length);
  
  return { 
    output: results, 
    issues: issueAddingNegativeKeyword
  };
}


function addNegativeKeyword(searchTerm, adGroupId, isShoppingCampaign) {

  var negativeKeyword = "["+searchTerm+"]";

  var ids = [adGroupId];
  
  if (!isShoppingCampaign) {
    var adGroupIterator = adGroupIterator = AdsApp.adGroups().withIds(ids).get();
  } else {
    var adGroupIterator = adGroupIterator = AdsApp.shoppingAdGroups().withIds(ids).get();
  }

  while (adGroupIterator.hasNext()) {
    adGroup = adGroupIterator.next();
    try {
      adGroup.createNegativeKeyword(negativeKeyword);
    } catch (e) {
      debug("### Failed to create negative keyword  "+negativeKeyword+" in adgroup '"+adGroup.getName()+"' in campaign '"+adGroup.getCampaign().getName()+"' \n ### ERROR: "+e);
      return "error";
    }
  }
  
  if ( negativeKeywordAddedToAdgroup(negativeKeyword, adGroupId, isShoppingCampaign) ) {
    Logger.log("Added negative keyword "+negativeKeyword+" to adgroup '"+adGroup.getName()+"' in campaign '"+adGroup.getCampaign().getName()+"'");
    return "ok";
  }
  
  return "issue";
}

function prepareOutputSheets() {
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  if (!spreadsheet) {
    Logger.log("Cannot open new reporting spreadsheet") ;
    return ;
  }

  var sheet = spreadsheet.getActiveSheet();
  if (!sheet) {
    Logger.log("Cannot open new reporting sheet") ;
    return ;
  }  
  
  var numberOfRows=sheet.getLastRow() ;
  debug("NR of rows in output sheet: "+numberOfRows);
  
  if (numberOfRows == 0) { // the sheet has no header
    addHeaderToOutputSheet(sheet);
  }
  
  return sheet;
}


function addHeaderToOutputSheet(sheet) {
  
  var header = [
    "date",
    "searchTerm", 
    "campaignName",
    "adGroupName",
    "impressions",
    "clicks",
    "ctr",
    "avg CPC",
    "cost",
    "conversions",
    "conversionRate",
    "cost/conv",
    "status"
    ];
  
  sheet.appendRow(header);
  var range=sheet.getRange(1,1,1,header.length);
  range.setFontWeight("bold");  
}


function addOutputToSheet(output, sheet) {

  var numberOfRows=sheet.getLastRow() ;
  
  sheet.insertRowsBefore(2, output.length);

  var startRow = 2;
  
  var range=sheet.getRange(startRow, 1, output.length, output[0].length) ;
  range.setValues(output) ;
  range.sort([{column: 1, ascending: false}, {column: 5, ascending: false}]);    

}


function isNegated(keyword, adGroupId, isShoppingCampaign) {
  
  var negativeKWs = getNegativeKeywords(adGroupId, isShoppingCampaign);
  
  for(var i=0;i<negativeKWs.length;i++) {
    if(negativeBlocksPositive(negativeKWs[i], keyword)) {
      Logger.log("Keyword already negated: "+keyword);
      return true;
    }
  }
    
  return false;
}


function negativeKeywordAddedToAdgroup(negativeKeyword, adGroupId, isShoppingCampaign) {
  var ids = [adGroupId];
  var negKWs = [];
  
  if (!isShoppingCampaign) {
    var adGroupIterator = adGroupIterator = AdsApp.adGroups().withIds(ids).get();
  } else {
    var adGroupIterator = adGroupIterator = AdsApp.shoppingAdGroups().withIds(ids).get();
  }
  
  while (adGroupIterator.hasNext()) {
    var adGroup = adGroupIterator.next();
    var negKWIterator = adGroup.negativeKeywords().get();
    
    while (negKWIterator.hasNext()) {
      var negativeKW = negKWIterator.next();
      negKWs.push(negativeKW.getText());
    }  
  }
   
  if ( isInArray(negativeKeyword, negKWs) ) {
    return true;
  } 
  
  return false;
}


function getNegativeKeywords(adGroupId, isShoppingCampaign) {

  var ids = [adGroupId];
  var negKWs = [];
  var campaignId;
  
  // first: let's get all the negative keywords for the ad group
  if (!isShoppingCampaign) {
    var adGroupIterator = adGroupIterator = AdsApp.adGroups().withIds(ids).get();
  } else {
    var adGroupIterator = adGroupIterator = AdsApp.shoppingAdGroups().withIds(ids).get();
  }
  
  while (adGroupIterator.hasNext()) {
    var adGroup = adGroupIterator.next();
    var negKWIterator = adGroup.negativeKeywords().get();
    
    while (negKWIterator.hasNext()) {
      var negativeKW = negKWIterator.next();
      negKWs.push(negativeKW);
    }
    
    campaignId = adGroup.getCampaign().getId();
  }
  
  // second: let's get all the negative keywords for the campaign
  var campaignIds = [campaignId];
  
  if (!isShoppingCampaign) {
    var campaignIterator = AdsApp.campaigns().withIds(campaignIds).get();
  } else {
    var campaignIterator = AdsApp.shoppingCampaigns().withIds(campaignIds).get();
  }
  
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    
    // let's get all the negative keywords at the campaign level
    var negKWIterator = campaign.negativeKeywords().get();
    
    while (negKWIterator.hasNext()) {
      var negativeKW = negKWIterator.next();
      negKWs.push(negativeKW);
    }
    
    // let's get all the negative keywords from negative keyword lists associated to this campaign
    var negativeKeywordListIterator = campaign.negativeKeywordLists().withCondition('Status = ACTIVE').get();
    
    while (negativeKeywordListIterator.hasNext()) {
      var negativeKeywordList = negativeKeywordListIterator.next();
      var negativeKeywordIterator = negativeKeywordList.negativeKeywords().get();
      
      while (negativeKeywordIterator.hasNext()) {
        var negativeKW = negativeKeywordIterator.next();
        negKWs.push(negativeKW);
      }
    }
  }
  
  return negKWs;
}


function negativeBlocksPositive(negativeKeyword, positiveKeywordText) {
  
  switch (negativeKeyword.getMatchType()) {
    case 'BROAD':
      return hasAllTokens(negativeKeyword.getText(), positiveKeywordText);
      break;

    case 'PHRASE':
      return isSubsequence(negativeKeyword.getText(), positiveKeywordText);
      break;

    case 'EXACT':
      return positiveKeywordText === negativeKeyword.getText().replace('[', '').replace(']', '');
      break;
  }
}


/**
 * Tests whether all of the tokens in one keyword's raw text appear in
 * the tokens of a second keyword's text.
 *
 * @param {string} keywordText1 the raw keyword text whose tokens may
 *     appear in the other keyword text.
 * @param {string} keywordText2 the raw keyword text which may contain
 *     the tokens of the other keyword.
 * @return {boolean} Whether all tokens in keywordText1 appear among
 *     the tokens of keywordText2.
 */
function hasAllTokens(keywordText1, keywordText2) {
  var keywordTokens1 = keywordText1.split(' ');
  var keywordTokens2 = keywordText2.split(' ');

  for (var i = 0; i < keywordTokens1.length; i++) {
    if (keywordTokens2.indexOf(keywordTokens1[i]) == -1) {
      return false;
    }
  }

  return true;
}


/**
 * Tests whether all of the tokens in one keyword's raw text appear in
 * order in the tokens of a second keyword's text.
 *
 * @param {string} keywordText1 the raw keyword text whose tokens may
 *     appear in the other keyword text.
 * @param {string} keywordText2 the raw keyword text which may contain
 *     the tokens of the other keyword in order.
 * @return {boolean} Whether all tokens in keywordText1 appear in order
 *     among the tokens of keywordText2.
 */
function isSubsequence(keywordText1, keywordText2) {
  return (' ' + keywordText2 + ' ').indexOf(' ' + keywordText1 + ' ') >= 0;
}


function debug(string) {
  if (DEBUG == true) {
    Logger.log(string);
  }
}


// convert cost value to money data type
function toMoney(m) {
  return m * 1000000;
}

// convert money data type to cost value
function convertMoney(m) {
  return m / 1000000;
}

function isInArray(value, array) { 
  return array.indexOf(value) > -1;
}

function getTodaysDate() {
  return new Date();
}




function getStartDate(period) {
  
  var startDate;
  var today = new Date();
  
  switch(period) {
      
    case "THIS_YEAR" : 
      startDate = today.getFullYear()+"-01-01";
      break;
      
    case "LAST_30_DAYS" :
      startDate =  dateToISOString(new Date(Date.now() - 30*864e5)); // 864e5 == 86400000 == 24*60*60*1000 
      break;

    case "LAST_60_DAYS" :
      startDate =  dateToISOString(new Date(Date.now() - 60*864e5)); 
      break;

    case "LAST_90_DAYS" :
      startDate =  dateToISOString(new Date(Date.now() - 90*864e5)); 
      break;
      
    default:
      startDate ="2021-01-01";
  }
  
  debug("startDate: "+startDate);
  
  return startDate;
}


function getEndDate(period) {

  var endDate;
  
  var yesterday = new Date(Date.now() - 864e5); // 864e5 == 86400000 == 24*60*60*1000 
 
  switch(period) {
      
    case "LAST_YEAR" : 
      endDate = "lastDayLastYear"; //TODO
      break;
      
    case "LAST_MONTH" :
      endDate = "lastDayLastMonth" //TODO
      break;
      
    default:
      endDate = dateToISOString(yesterday);
  }

  debug("endDate: "+endDate);
  
  return endDate;
}


function dateToISOString(date) {
   
  var year = date.getUTCFullYear();
  var month = date.getUTCMonth()+1;
  if (month < 10) {
    month = "0"+month;
  }
    
  var day = date.getUTCDate();
  if (day < 10) {
    day = "0"+day;
  }  
  var ISOString = year+"-"+month+"-"+day;
  
  return ISOString;
}