Report Trending Search Terms

PHOTO

Thu Aug 27 2020 18:21:39 GMT+0000 (Coordinated Universal Time)

/**
*
* 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;
}

COPY

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

Save snippets that work from anywhere online with our extensions

Comments

Google Ads Scripts

@moakdesigns

Report Disapproved Ads Disapproved Ads Alerts Report Trending Search Terms ETA vs RSA Query Report Disapproved Extensions Report Script

#html #javascript #nodejs

How to Save HTML Form Data in JSON - Express

<form action="/new" method="post">
 
  <input name="title" type="text">
  <input name="description" type="text">
  <button type="submit">Submit Form</button>
 
</form>

> More steps

#javascript

Get URL and URL Parts in JavaScript

var newURL = window.location.protocol + "//" + window.location.host + "/" + window.location.pathname + window.location.search

#javascript

Add event listener to multiple buttons with the same class

    btns = document.getElementsByClassName("saveBtn");
    for (var i = 0; i < btns.length; i++) {
        btns[i].addEventListener("click", function () {
			//Add function here
        });
    }

#javascript

Replace Textarea with Codemirror editor

<link rel="stylesheet" type="text/css" href="plugin/codemirror/lib/codemirror.css">

<body>
	<textarea class="codemirror-textarea"></textarea>
</body>

<script>

$(document).ready(function(){
    var codeText = $(".codemirror-textarea")[0];
    var editor = CodeMirror.fromTextArea(codeText, {
        lineNumbers : true
    });
});

</script>

<script type="text/javascript" src="plugin/codemirror/lib/codemirror.js"></script>

#javascript #nodejs #commandline

HTTPS Redirect in Nodejs

npm install heroku-ssl-redirect

> More steps

#javascript #nodejs

Save to MongoDB without Duplicates Using Mongoose

var mongoose = require('mongoose');
var Schema = mongoose.Schema;

const exampleSchema = new Schema({
    title: { type: String , required: true},
    content: [{type: String}]
});


var Example = mongoose.model('Example', exampleSchema);
module.exports = Example;

> More steps

#html #javascript #jsfunctions #dom #dommanipulation

getAttribute() - how to get the value of any attribute of HTML element

<html>

<input id="contact" name="address">

<script>

    var x = document.getElementById("contact").getAttribute('name');

</script>

</html>

#javascript

Full Stack Developer

function full_stack_developer() {
    full_stack_developer();
}

#javascript

3 Ways To Detect Selenium Bots Using Javascript - CodingTutz

if (navigator.webdriver) {
    document.body.innerHTML = "This is a Bot";
}

#javascript #promises #howto

How to Get the Domain From A URL Using Javascript

var url = "http://scratch99.com/web-development/javascript/";
var urlParts = url.replace('http://','').replace('https://','').split(/[/?#]/);
var domain = urlParts[0];

#javascript #javascript #functions #parameters

Required Parameters for Functions in JavaScript

 const isRequired = () => { throw new Error('param is required'); };

const hello = (name = isRequired()) => { console.log(`hello ${name}`) };

// These will throw errors
hello();
hello(undefined);

// These will not
hello(null);
hello('David');
The idea here is that it uses default parameters, like how the b parameter here has a default if you don’t send it anything:
function multiply(a, b = 1) {
  return a * b;
}

#javascript

Mapping an Object

const object1 = {
  a: 'somestring',
  b: 42
};

for (let [key, value] of Object.entries(object1)) {
  console.log(`${key}: ${value}`);
}

#javascript

Copy Text to Clipboard with Line Breaks

function copyToClipboard(){

    var codeToBeCopied = document.getElementById('code-snippet').innerText;
    var emptyArea = document.createElement('TEXTAREA');
    emptyArea.innerHTML = codeToBeCopied;
    const parentElement = document.getElementById('post-title');
    parentElement.appendChild(emptyArea);

    emptyArea.select();
    document.execCommand('copy');

    parentElement.removeChild(emptyArea);
    M.toast({html: 'Code copied to clipboard'})

    }

#javascript

How to store fetch response in javascript variable - Stack Overflow

async function fun() {
  return fetch('https://jsonplaceholder.typicode.com/todos/1').then(res => res.json());
}

const data  = await fun();

#javascript #jquery

jQuery.post() - AJAX for post requests

$.ajax({
  type: "POST",
  url: url,
  data: data,
  success: success,
  dataType: dataType
});

Report Trending Search Terms

Save snippets that work from anywhere online with our extensions

Comments

More like this

Google Ads Scripts

Browse more snippets >>

Report Trending Search Terms

Save snippets that work from anywhere online with our extensions

Comments

More like this

Google Ads Scripts

Browse more snippets >>

Embed code snippet