/**
 * @name Detect Duplicate Keywords
 * 
 * @overview Identify duplicate keywords in the AdWords account, including close variants.
 * 
 * Please send in your comment, feedback, criticism or occasional Thank You 
 * to support@karooya.com
 * 
 * @author Karooya Team [support@karooya.com]
 *
 * All AdWords scripts published by Karooya are available here 
 * https://www.karooya.com/blog/category/karooya-adwords-script/ 
 *
 * Copyright (c) 2018 - Karooya Technologies Pvt. Ltd.  All Rights Reserved.
 * https://www.karooya.com
 * 
 * The script was last modified on: 22 October, 2018
 * 
 *Get the demonstrartion HERE - https://www.wao.co.il/google-ads-script-duplicate-keywords
 *
 */

// URL of the sheet to which the data is to be copied
var SPREADSHEET_URL = 'https://docs.google.com/YOUR-SPREADSHEET-URL-HERE';

// Specify a date range for the report 
var DATE_RANGE = "LAST_90_DAYS"; // Other allowed values are: LAST_<NUM>_DAYS (Ex: LAST_90_DAYS) or TODAY, YESTERDAY, THIS_WEEK_SUN_TODAY, THIS_WEEK_MON_TODAY, LAST_WEEK, LAST_WEEK, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH 

// Or specify a custom date range: 
// Format is yyyy-mm-dd
var USE_CUSTOM_DATE_RANGE = false;
var START_DATE = "2016-02-01"; // Example "2016-02-01"
var END_DATE = "2016-03-01"; // Example "2016-02-29"

// Set this to true to only look at currently active campaigns. 
// Set to false to include campaigns that had impressions but are currently paused.
var IGNORE_PAUSED_CAMPAIGNS = true;
 
// Set this to true to only look at currently active ad groups.
// Set to false to include ad groups that had impressions but are currently paused.  
var IGNORE_PAUSED_ADGROUPS = true;

var IGNORE_PAUSED_KEYWORDS = true;

/*
 * 
 * Please update the language setting ACCOUNT_LANGUAGE_CODE below if 
 * account's primary language is other than English. No need to change anything for English.
 * The language codes are as follows. 
 *  
 *
 * ar: Arabic
 * bg: Bulgarian
 * cz: Czech
 * da: Danish
 * nl: Dutch
 * en: English
 * fi: Finnish
 * fr: French
 * de: German
 * hu: Hungarian
 * no: Norwegian
 * pt: Portuguese
 * ro: Romanian
 * ru: Russian
 * es: Spanish
 * sv: Swedish
 * tr: Turkish
 *  
 */

var ACCOUNT_LANGUAGE_CODE = 'en';   // Laguage of the account. Default is English.

// The script is expected to work with following API version
var API_VERSION = {
  apiVersion: 'v201806'
}

/*-- More filter for MCC account --*/
//Is your account a MCC account
var IS_MCC_ACCOUNT = false;
var FILTER_ACCOUNTS_BY_LABEL = false;
var ACCOUNT_LABEL_TO_SELECT = "INSERT_LABEL_NAME_HERE";
/*---------------------------------*/

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

function main() {
  var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
  if (spreadsheet == null) {
    return;
  }
  
  if (!IS_MCC_ACCOUNT) {
    processCurrentAccount(spreadsheet);
  } else {
    var childAccounts  = getManagedAccounts();
    while(childAccounts .hasNext()) {
      var childAccount  = childAccounts .next()
      MccApp.select(childAccount);
      processCurrentAccount(spreadsheet);
    }
  }
  trackEventInAnalytics();
  Logger.log("Done!");
}

function getManagedAccounts() {
  var accountSelector = MccApp.accounts();
  if (FILTER_ACCOUNTS_BY_LABEL) {
    accountSelector = accountSelector.withCondition("LabelNames CONTAINS '" + ACCOUNT_LABEL_TO_SELECT + "'")
  }
  return accountSelector.get();  
}

function processCurrentAccount(spreadsheet) {
  var accountName = AdWordsApp.currentAccount().getName();
  
  Logger.log("Accesing AdWord account: " + accountName);
  
  var campaignIDs = getSearchOnlyCampaignIDs();
  if (campaignIDs.length > 0) {
    Logger.log("Found " + campaignIDs.length + " SearchOnly campaigns");
  } else {
    Logger.log("Did not find any SearchOnly campaigns! Skiping account.");
    return;
  }
  
  Logger.log("Fetching keyword performance data from AdWords..");
  var keywordReport = getKeywordReport(campaignIDs);

  Logger.log("Computing..");
  var duplicateKeywordGroupsArr = compute(keywordReport);

  Logger.log("Exporting results to spreadsheet..");
  // Export keyword level stats to a spreadsheet
  var newSheetName = accountName;
  var sheet = spreadsheet.getSheetByName(newSheetName);
  if (sheet != null) {
    sheet.clear();
  } else {
    sheet = spreadsheet.insertSheet(newSheetName, 0);
  }

  exportToSpreadsheet(duplicateKeywordGroupsArr, sheet, accountName);
}


function getSearchOnlyCampaignIDs() {
  var campaignIDs = new Array();
  var campaignSelector = AdWordsApp.campaigns().withCondition("TargetContentNetwork = FALSE");
  if (IGNORE_PAUSED_CAMPAIGNS) {
    campaignSelector = campaignSelector.withCondition("Status = ENABLED");
  } else {
    campaignSelector = campaignSelector.withCondition("Status IN ['ENABLED','PAUSED']");
  }
  var campaignIterator = campaignSelector.get();
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    campaignIDs.push(campaign.getId());
  }
  return campaignIDs;
}

function compute(keywordReport) {
  var reportIterator = keywordReport.rows();
  var keywordArray = new Array();
  while (reportIterator.hasNext()) {
    var kw = reportIterator.next();
    keywordArray.push(kw);
  }
  Logger.log("Total keywords found: " + keywordArray.length);
  var singleWordTerms = getUniqueTerms(keywordArray);
  Logger.log("Found: " + singleWordTerms.length + " single word terms.");
  var termToStemmedWordMap = fetchStemmedWords(singleWordTerms);
  Logger.log("Grouping keywords..");
  var dupeKwGroupsArr = findDuplicateKeywords(termToStemmedWordMap, keywordArray);
  if (dupeKwGroupsArr.length > 0) {
    Logger.log("Found " + dupeKwGroupsArr.length + " duplicate keyword groups.");
  } else {
    Logger.log("There are no duplicate keywords in the current account.");
  }
  return dupeKwGroupsArr;
}

function findDuplicateKeywords(termToStemmedWordMap, keywordArray) {
  var uniqueKwGroupsMap = {};
  for (var i=0; i<keywordArray.length; i++) {
    var kw = keywordArray[i];
    var kwKey = getUniqieKeyForKeyword(kw, termToStemmedWordMap);
    var kwGroup = uniqueKwGroupsMap[kwKey];
    if (kwGroup == undefined) {
      kwGroup = new Object();
      kwGroup.kwArr = new Array();
      uniqueKwGroupsMap[kwKey] = kwGroup;
    }
    kwGroup.kwArr.push(kw);
  }
  
  var dupeKwGroupsArr = new Array();
  for(kwKey in uniqueKwGroupsMap) {
    var kwGroup = uniqueKwGroupsMap[kwKey];
    if (kwGroup.kwArr.length > 1) {
      var totalImpressions = 0;
      for(var kwIdx=0; kwIdx<kwGroup.kwArr.length; kwIdx++) {
        totalImpressions += kwGroup.kwArr[kwIdx]["Impressions"];
      }
      kwGroup.totalImpressions = totalImpressions;
      kwGroup.kwArr.sort(getComparator("Impressions", true));
      dupeKwGroupsArr.push(kwGroup);
    }
  }
  dupeKwGroupsArr.sort(getComparator("totalImpressions", true));
  return dupeKwGroupsArr;
}

function cleanAndSplitString(str) {
  var strArr = str.replace(/\+/g, " + ")
          .replace(/\-/g, " - ")
          .replace(/\_/g, " _ ")
          .trim().split(/\s+/);
  return strArr;
}

function getUniqieKeyForKeyword(kw, termToStemmedWordMap) {
  var matchType = kw['KeywordMatchType'];
  var kwStr = kw["Criteria"];
  var kwTermsArr = cleanAndSplitString(kwStr);
  var stemmedWordsArr = new Array();
  for(var j=0; j < kwTermsArr.length; j++) {
    var stemmedWord;
    if (kwTermsArr[j] == "+") {
      stemmedWord = "+";
    } else {
      stemmedWord = termToStemmedWordMap[kwTermsArr[j]];
      if (stemmedWord == undefined) {
        Logger.log("WARNING: Did not find stemmed word for:" + kwTermsArr[j] + ": in " + kw["Criteria"]); // Shouldn't happen
      }
    }
    stemmedWordsArr.push(stemmedWord);
  }
  if (matchType === "BROAD") {
    stemmedWordsArr.sort();
  }
  var uniqueKeywordKey = matchType + "_" + stemmedWordsArr.join('_');
  return uniqueKeywordKey;
}

function fetchStemmedWords(singleWordTerms) {
  //Logger.log(JSON.stringify(singleWordTerms));
  var options = {
    'method' : 'post',
    'contentType' : 'application/json',
    'payload' :JSON.stringify(singleWordTerms)
  };
  var response = UrlFetchApp.fetch('https://www.karooya.com/service/free/getStemmedWords/language/' + ACCOUNT_LANGUAGE_CODE, options);
  var resText = response.getContentText();
//  Logger.log(resText);
  var termToStemmedWordMap = JSON.parse(resText);
  var count = 0;
  for(t in termToStemmedWordMap) {
    count++;
  }
  Logger.log("Received stemmed words for " + count + " terms");
  return termToStemmedWordMap;
}

function getUniqueTerms(keywordArray) {
  var uniqueTerms = new Array();
  var tempMap = {};
  
  for (var i=0; i<keywordArray.length; i++) {
    var kw = keywordArray[i];
    var kwStr = kw["Criteria"];
    var kwTermsArr = cleanAndSplitString(kwStr);
    for(var j=0; j<kwTermsArr.length; j++) {
      if (tempMap[kwTermsArr[j]] === 1) {
        continue;
      }
      tempMap[kwTermsArr[j]] = 1;
      uniqueTerms.push(kwTermsArr[j]);
    }
  }
  return uniqueTerms;
}

function getKeywordReport(campaignIDs) {
  var dateRange = getDateRange(",");

  var whereStatements = "AND CampaignId IN [" + campaignIDs.join(",") + "] ";
  
  if (IGNORE_PAUSED_ADGROUPS) {
    whereStatements += "AND AdGroupStatus = ENABLED ";
  } else {
    whereStatements += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
  }

  if (IGNORE_PAUSED_KEYWORDS) {
    whereStatements += "AND Status = ENABLED "; 
  } else {
    whereStatements += "AND Status IN ['ENABLED','PAUSED'] ";
  }
  
  var query = "SELECT CampaignId, AdGroupId, Id, CampaignName, AdGroupName, Criteria, KeywordMatchType, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, CostPerConversion, AveragePosition " +
    "FROM  KEYWORDS_PERFORMANCE_REPORT " +
    "WHERE IsNegative = FALSE " + whereStatements + 
    "DURING " + dateRange;

  return AdWordsApp.report(query, API_VERSION);
}

function getDateRange(seperator) {
  var dateRange = DATE_RANGE;
  if (USE_CUSTOM_DATE_RANGE) {
    dateRange = START_DATE.replace(/-/g, "") + seperator + END_DATE.replace(/-/g, "");
  } else if (dateRange.match(/LAST_(.*)_DAYS/)) {
    var adWordsAccount = AdWordsApp.currentAccount();
    var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
    var numDaysBack = parseInt(dateRange.match(/LAST_(.*)_DAYS/)[1]);
    var today = new Date();
    var endDate = Utilities.formatDate(new Date(today.getTime() - MILLIS_PER_DAY), adWordsAccount.getTimeZone(), "yyyyMMdd");// Yesterday
    var startDate = Utilities.formatDate(new Date(today.getTime() - (MILLIS_PER_DAY * numDaysBack)), adWordsAccount.getTimeZone(), "yyyyMMdd");
    dateRange = startDate + seperator + endDate;
  }
  return dateRange;
}

function getSpreadsheet(spreadsheetUrl) {
  // Check if the spreadsheet works
  if (spreadsheetUrl == "https://docs.google.com/" + "YOUR-SPREADSHEET-URL-HERE") {
    Logger.log("Problem with the spreadsheet URL: replace 'https://docs.google.com/" + "YOUR-SPREADSHEET-URL-HERE' with a valid spreadsheet URL.");
    return null;
  }

  var spreadsheet = null;
  try {
    spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  } catch (e) {
    Logger.log("Problem with the spreadsheet URL: '" + e + "'");
  }
  return spreadsheet;
}

function exportToSpreadsheet(duplicateKeywordGroupsArr, sheet, accountName) {
  var colTitleColor = "#03cfcc"; // Aqua
  var headers = ["Campaign Name", "Ad Group Name", "Keyword", "Match Type", "Clicks", "Impressions", "Ctr", "Avg CPC", "Conversions", "Cost", "Cost Per Conv.", "Avg Position"];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setBackground(colTitleColor).setFontWeight("BOLD");
  
  var LIGHT_GREY = "#DDDDDD"; // Grey
  var WHITE = "#FFFFFF"; // White
  
  var rowIdx = 2;
  for(var kwGroupIdx=0; kwGroupIdx < duplicateKeywordGroupsArr.length; kwGroupIdx++) {
    var dupeKeywordGroup = duplicateKeywordGroupsArr[kwGroupIdx];
    var rowsArray = new Array();
    var kwArr = dupeKeywordGroup.kwArr;
    for (var i=0; i<kwArr.length; i++) {
      var kw = kwArr[i];
      rowsArray.push([ kw["CampaignName"], kw["AdGroupName"], kw["Criteria"], kw["KeywordMatchType"], 
         kw["Clicks"], kw["Impressions"], kw["Ctr"], kw["AverageCpc"], kw["Conversions"], 
         kw["Cost"], kw["CostPerConversion"], kw["AveragePosition"]] );
    }
    var bgColor = (kwGroupIdx % 2)?LIGHT_GREY:WHITE;
    sheet.getRange(rowIdx, 1, rowsArray.length, headers.length).setValues(rowsArray).setBackground(bgColor);
    rowIdx += rowsArray.length;
  }
}

function getComparator(sortFieldName, reverse) {
  return function(obj1, obj2) {
    var retVal = 0;
    var val1 = parseInt(obj1[sortFieldName], 10);
    var val2 = parseInt(obj2[sortFieldName], 10);
    if (val1 < val2)
      retVal = -1;
    else if (val1 > val2)
      retVal = 1;
    else 
      retVal = 0;
    
    if (reverse) {
      retVal = -1 * retVal;
    }
    return retVal;
  }
}

/* ******************************************* */
/* 
* Tracks the execution of the script as an event in Google Analytics.
* Sends the script name and a random UUID (it is just a random number, required by Analytics).
* The event information just tells us that somewhere someone ran this script. 
* Credit for the idea goes to Russel Savage, who posted his version at http://www.freeadwordsscripts.com/2013/11/track-adwords-script-runs-with-google.html.
* and to Martin Roettgerding, as we learnt the trick from his script at http://www.ppc-epiphany.com/2016/03/11/introducing-the-quality-score-tracker-v3-0
*/
function trackEventInAnalytics() {
  // Create the random UUID from 30 random hex numbers gets them into the format xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (with y being 8, 9, a, or b).
  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?v=1&t=event&tid=UA-46662882-1&cid=" + uuid 
  + "&ec=AdWords%20Scripts&ea=Script%20Execution&el=Detect%20Duplicate%20Keywords";
  UrlFetchApp.fetch(url);
}