0 points

Report Trending Search Terms


dashboard

Thu Aug 27 2020 18:21:39 GMT+0000 (UTC)

Posted by @moakdesigns #javascript

/**
*
* Report Trending Search Terms
*
* Creates a Google Sheets report containing two sheets:
*
* 1. Sheet with trending search terms for Last Week versus Week Before last Week
* 2. Sheet with trending search terms for Last Week versus Same Week Last Year
*
* @author: Nils Rooijmans
*
* contact nils@nilsrooijmans.com for questions and a High Performance MCC version of the script	
*/


/*** ADD YOUR CONFIGURATION HERE ***/

var SPREADSHEET_URL = "";  //insert a new blank spreadsheet url between quotes
var EMAIL_ADDRESSES = ""; //insert your email between quotes
var EMAIL_SUBJECT = "Search Terms Trends for Account - XYZ"; // replace XYZ with the account/client name


/*** NO NEED TO CHANGE ANYTHING BELOW THIS LINE ***/

var IMPRESSIONS_MIN = 10; //igore search terms with less impresssions last week 
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.2; // ignore search terms with relative difference that is smaller then the value you set here

var DEBUG = true;
var SEND_EMAIL = true; // set to false if you do not want to send alert email
var EMAIL_BODY = "";


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

function main() {
    
  var searchtermsLastWeek = {};
  var searchtermsWeekBeforeLastWeek = {};
  var searchtermsLastWeekLastYear = {};
  
  var trendingSearchterms_LastWeek_vs_WeekBefore = {};
  var trendingSearchterms_LastWeek_vs_LastYear = {};
  
  searchtermsLastWeek = getSearchterms("lastWeek"); 
  searchtermsWeekBeforeLastWeek = getSearchterms("weekBeforeLastWeek"); 
  searchtermsLastWeekLastYear = getSearchterms("lastWeekLastYear"); 
    
  
  debug("Nr of searchterms last week: "+Object.keys(searchtermsLastWeek).length);
  debug("Nr of searchterms week before last week: "+Object.keys(searchtermsWeekBeforeLastWeek).length);
  debug("Nr of searchterms week last week last year: "+Object.keys(searchtermsLastWeekLastYear).length);
  
  trendingSearchterms_LastWeek_vs_WeekBefore = getTrendingSearchterms(searchtermsLastWeek, searchtermsWeekBeforeLastWeek);
  trendingSearchterms_LastWeek_vs_LastYear = getTrendingSearchterms(searchtermsLastWeek, searchtermsLastWeekLastYear);
  
  debug("Nr of trending searchterms LastWeek_vs_WeekBefore: "+Object.keys(trendingSearchterms_LastWeek_vs_WeekBefore).length);
  debug("Nr of trending searchterms LastWeek_vs_LastYear: "+Object.keys(trendingSearchterms_LastWeek_vs_LastYear).length);
  
  generateReport(trendingSearchterms_LastWeek_vs_WeekBefore, trendingSearchterms_LastWeek_vs_LastYear);

  // send email
  if (SEND_EMAIL) {
   
    var emailBody = 
        "\nNumber of trending search terms: " + ( Object.keys(trendingSearchterms_LastWeek_vs_WeekBefore).length + Object.keys(trendingSearchterms_LastWeek_vs_LastYear).length ) + "\n" + 
        "See details: "+ SPREADSHEET_URL+ "\n---\n\n" + EMAIL_BODY + "\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 - Trending Search Terms, (C) Nils Rooijmans \n" +
      	"---\n";

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


function generateReport(trendingSearchterms_LastWeek_vs_WeekBefore, trendingSearchterms_LastWeek_vs_LastYear) {
    
  var rowsThisYear  = getReportRows(trendingSearchterms_LastWeek_vs_WeekBefore);
  var rowsLastYear  = getReportRows(trendingSearchterms_LastWeek_vs_LastYear);
    
  
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL) ;
  if (!spreadsheet) {
    Logger.log("Cannot open new reporting spreadsheet") ;
    return ;
  }

  // First, generate the sheet for this year's data
  var sheet=spreadsheet.getSheetByName('LastWeek_vs_WeekBefore');
    
  if (!sheet) {
    Logger.log("Cannot open exisiting reporting sheet 'LastWeek_vs_WeekBefore', creating new one") ;
    spreadsheet.insertSheet('LastWeek_vs_WeekBefore');
    var sheet=spreadsheet.getSheetByName('LastWeek_vs_WeekBefore');
  }
  
  generateReportSheet(sheet, rowsThisYear); 
  debug("-> Generated report sheet: "+sheet.getName());
 
  
  // Second, generate the sheet for this year's data
  var sheet=spreadsheet.getSheetByName('LastWeek_vs_LastYear');
    
  if (!sheet) {
    Logger.log("Cannot open exisiting reporting sheet 'LastWeek_vs_LastYear' , creating new one") ;
    spreadsheet.insertSheet('LastWeek_vs_LastYear');
    var sheet=spreadsheet.getSheetByName('LastWeek_vs_LastYear');
  }
  
  generateReportSheet(sheet, rowsLastYear);
  debug("-> Generated report sheet: "+sheet.getName());
}


function generateReportSheet(sheet, rows) {
  
  debug("--> Generating report sheet: "+sheet.getName());

  if (sheet.getName()=='LastWeek_vs_WeekBefore') {
    var header = [
      "Searchterm",
      "Impressions Last Week",
      "Impressions week Before Last Week",
      "Diff",
      "Relative Diff (%)"
    ];
  } else if (sheet.getName()=='LastWeek_vs_LastYear') {
    var header = [  
      "Searchterm",
      "Impressions Last Week",
      "Impressions Last Week Last Year",
      "Diff",
      "Relative Diff (%)"
    ];  
  } else {
    Logger.log("### ERROR: name of sheet not recognized");
  }
  
  
  sheet.clear();
  sheet.clearConditionalFormatRules();
  sheet.appendRow(header);
  var range = sheet.getRange(1,1,1,header.length);
  range.setFontWeight("bold");
  
  var rules = sheet.getConditionalFormatRules();
  
  if ( !(rows.length < 1) ) { range = sheet.getRange(2,1,rows.length,5); 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}]); } else { debug("ZERO trending search terms"); } } 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 "lastWeek" :
      periodString = dates(7) + "," + dates(1);
      break;
    case "weekBeforeLastWeek" :
      periodString = dates(14) + "," + dates(8);
      break; 
    case "lastWeekLastYear" :
      periodString = dates(372) + "," + dates(366);
      break;      
    default :
      Logger.log("### ERROR: Could not recognize the period for which to add search terms");
  }
  
  var awqlQuery="SELECT Query, Impressions FROM SEARCH_QUERY_PERFORMANCE_REPORT WHERE Impressions > "+IMPRESSIONS_MIN+" 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;
}
content_copy Copy

https://nilsrooijmans.com/google-ads-script-trending-search-terms/