/**
*
* Report Trending Search Terms - DAILY
*
* Creates a Google Sheets report containing yesterday's trending search terms
* 
*
* @author: Nils Rooijmans
*
* contact nils@nilsrooijmans.com for questions and a High Performance MCC version of the script 
*/
 
 
/*** [REQUIRED] ADD YOUR SETTINGS HERE ***/

var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url
var EMAIL_ADDRESSES = ""; //insert your email

/*** [OPTIONAL] YOU MIGHT WANT TO CHANGE SOME CONFIGURATIONS HERE ***/

var CAMPAIGN_NAME_CONTAINS = "";
// Use this if you only want to look at some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if not wanted.

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.

var IMPRESSIONS_MIN_ABS_DIFFERENCE = 50; // ignore search terms with absolute difference that is smaller then the value you set here
var IMPRESSIONS_MIN_REL_DIFFERENCE = 0.25; // ignore search terms with relative difference that is smaller then the value you set here

var SEND_EMAIL = true; // set to false if you do not want to send alert email
var EMAIL_SUBJECT = "[GAds Script] - Trending Search Terms - DAILY"; // subject of emails, you might want to include your account name here


/*** DO NOT CHANGE ANYTHING BELOW THIS LINE ***/

var DEBUG = true; 

function main() {
    
  var searchtermsYesterday = {};
  var searchterms8DaysAgo = {};
  
  var trendingSearchterms_Yesterday_vs_8DaysAgo = {};
  
  searchtermsYesterday = getSearchterms("yesterday"); 
  debug("Nr of searchterms yesterday: "+Object.keys(searchtermsYesterday).length);


  searchterms8DaysAgo = getSearchterms("8DaysAgo"); 
  debug("Nr of searchterms 8DaysAgo: "+Object.keys(searchterms8DaysAgo).length);
  trendingSearchterms_Yesterday_vs_8DaysAgo = getTrendingSearchterms(searchtermsYesterday, searchterms8DaysAgo);  
  debug("Nr of trending searchterms Yesterday_vs_8DaysAgo: "+Object.keys(trendingSearchterms_Yesterday_vs_8DaysAgo).length);
  
  generateReport(trendingSearchterms_Yesterday_vs_8DaysAgo);

  // send email
  if (SEND_EMAIL) {
   
    var emailBody = 
        "\nNumber of trending search terms: " + Object.keys(trendingSearchterms_Yesterday_vs_8DaysAgo).length + "\n" + 
        "See details: "+ SPREADSHEET_URL+ "\n---\n\n"+
        "For more FREE Google Ads Scripts to improve your results and make your working day feel like a breeze, visit https://nilsrooijmans.com \n" + 
        "---\n" + 
        "This email is generated by a copy of the free Google Ads Script - Report Trending Search Terms (DAILY version), (C) Nils Rooijmans \n" +
        "---\n";

    MailApp.sendEmail(EMAIL_ADDRESSES, EMAIL_SUBJECT, emailBody);
    debug("Report email sent");
  }
}


function generateReport(trendingSearchterms_Yesterday_vs_8DaysAgo) {
    
  var rows  = getReportRows(trendingSearchterms_Yesterday_vs_8DaysAgo);
  
  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 exisiting reporting sheet, creating new one") ;
    spreadsheet.insertSheet('Yesterday versus 8 Days Ago');
    var sheet=spreadsheet.getSheetByName('Yesterday versus 8 Days Ago');
  }

  generateReportSheet(sheet, rows); 
  debug("-> Generated report sheet: "+sheet.getName());
      
}


function generateReportSheet(sheet, rows) { 

  var header = [
    "Searchterm",
    "Impressions Yesterday",
    "Impressions 8 Days Ago",
    "Diff",
    "Relative Diff (%)"
    ];  
  
  sheet.clear();
  sheet.clearConditionalFormatRules();
  sheet.appendRow(header);
  var range = sheet.getRange(1,1,1,header.length);
  range.setFontWeight("bold");
  
  if (rows.length > 0) {
    var rules = sheet.getConditionalFormatRules();

    range = sheet.getRange(2,1,rows.length,header.length);

    var diffRange = [sheet.getRange("E2:E"+ (2+rows.length))]; 

    var rule = SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThan(100).setFontColor("#32CD32").setRanges(diffRange).build();  
    rules.push(rule); 
    var rule = SpreadsheetApp.newConditionalFormatRule().whenNumberLessThan(-50).setFontColor("#FF8C00").setRanges(diffRange).build();  
    rules.push(rule); 

    sheet.setConditionalFormatRules(rules);

    range.setValues(rows);
    range.sort([{column: 4, ascending: false}]);
  }
}


function getReportRows(trendingSearchTermObject) {
  
  var rows  = [];
  
  for (var searchterm in trendingSearchTermObject) {
    var impressions_period1 = trendingSearchTermObject[searchterm].impressions_1;
    var impressions_period2 = trendingSearchTermObject[searchterm].impressions_2;
    var absoluteDiff = impressions_period1-impressions_period2;
    var relativeDiff = (((impressions_period1-impressions_period2)/impressions_period2)*100).toFixed(0);
    rows.push([searchterm, impressions_period1, impressions_period2, absoluteDiff, relativeDiff]);
  }
  
  return rows;
}



function getTrendingSearchterms(searchtermsPeriod_1,searchtermsPeriod_2) {
  
  var trendingSearchTerms = {};
  
  for (var searchterm in searchtermsPeriod_1) {
    
    if (searchtermsPeriod_1.hasOwnProperty(searchterm)) {
      
      var impressions1 = searchtermsPeriod_1[searchterm];
      
      if (searchtermsPeriod_2.hasOwnProperty(searchterm)) {
        var impressions2 = searchtermsPeriod_2[searchterm];
      } else {
        var impressions2 = 0;
      }
      
      if (Math.abs(impressions1-impressions2) > IMPRESSIONS_MIN_ABS_DIFFERENCE && 
          Math.abs((impressions1-impressions2)/impressions2) > IMPRESSIONS_MIN_REL_DIFFERENCE ) {
        
         var searchTermObject = {};
        
         searchTermObject.impressions_1 = impressions1;     
         searchTermObject.impressions_2 = impressions2;
        
         trendingSearchTerms[searchterm] = searchTermObject;
      }
    }
  }
  
 
  // now add searchterms that were not present in data from period_1
  for (var searchterm in searchtermsPeriod_2) {
    
    if (searchtermsPeriod_2.hasOwnProperty(searchterm)) {
      
      var impressions2 = searchtermsPeriod_2[searchterm];
      
      if (!searchtermsPeriod_1.hasOwnProperty(searchterm)) { // searchterm not present in data from period_1
        var impressions1 = 0;
 
        if (Math.abs(impressions1-impressions2) > IMPRESSIONS_MIN_ABS_DIFFERENCE && 
            Math.abs((impressions1-impressions2)/impressions2) > IMPRESSIONS_MIN_REL_DIFFERENCE ) {

           var searchTermObject = {};

           searchTermObject.impressions_1 = impressions1;     
           searchTermObject.impressions_2 = impressions2;

           trendingSearchTerms[searchterm] = searchTermObject;
        }
      }
    }
  }   
  
  return trendingSearchTerms;
}



function getSearchterms(period) {
  
  var searchtermObject = {};
  var periodString;
  
  switch(period) {    
    case "today" :
      periodString = "TODAY"; 
      break;
    case "yesterday" :
      periodString = "YESTERDAY";
      break;
    case "dayBeforeYesterday" :
      periodString = dates(2) + "," + dates(2);
      break;
    case "8DaysAgo" :
      periodString = dates(8) + "," + dates(8);
      break;
    case "lastWeek" :
      periodString = dates(7) + "," + dates(1);
      break;
    case "weekBeforeLastWeek" :
      periodString = dates(14) + "," + dates(8);
      break; 
    case "lastWeekLastYear" :
      periodString = dates(372) + "," + dates(366);
      break;      
    case "lastWeek2019" :
      periodString = dates2019(7) + "," + dates2019(1);
      break;
    default :
      Logger.log("### ERROR: Could not recognize the period for which to add search terms");
  }

  var whereStatements = "Impressions > 0 ";
  
  if (CAMPAIGN_NAME_CONTAINS != "") {
    whereStatements += "AND CampaignName CONTAINS_IGNORE_CASE '" + CAMPAIGN_NAME_CONTAINS + "' ";
  }
  if (CAMPAIGN_NAME_DOES_NOT_CONTAIN != "") {
    whereStatements += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + CAMPAIGN_NAME_DOES_NOT_CONTAIN + "' ";
  }
  
  
  var awqlQuery=
      "SELECT Query, Impressions FROM SEARCH_QUERY_PERFORMANCE_REPORT "+
      "WHERE "+whereStatements+ 
      " DURING "+periodString;   
  
  debug("awqlQuery: "+awqlQuery);
  
  var rows=AdWordsApp.report(awqlQuery).rows(); 
  
  while (rows.hasNext()) {
    var row = rows.next();
    var searchterm = row['Query'];
    var impressions = numericalize(row['Impressions']);
    
    if (searchtermObject.hasOwnProperty(searchterm)) {
      searchtermObject[searchterm] += impressions;
    } else {
      searchtermObject[searchterm] = impressions;      
    }    
  }
  
  return searchtermObject;
}


function numericalize(string){
  return parseFloat(string.toString().replace(/\,/g, ''));
}


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


// return date x days before today
function dates(x){ 
 var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
 var now = new Date();
 var date = new Date(now.getTime() - x * MILLIS_PER_DAY);
 var timeZone = AdWordsApp.currentAccount().getTimeZone();
 var output = Utilities.formatDate(date, timeZone, 'yyyyMMdd');
 return output;
}