Google Ads Script – Trending Search Terms

Opportunity comes from changes in the world.

The bigger the changes, the bigger the opportunity.

Search behavior is no exception.

Here’s a script to monitor how Google is matching new search terms to your keywords and what is changing in the search behavior of your audience.

The script creates a report in Google Sheets.
The report lists the search terms that show a significant increase or drop in impressions.
It compares last week’s data to the week before AND last week to the same week last year.

The output will look something like this:

Rising search terms present new opportunities for growth. Maybe even opportunities for completely new products/service offerings 🙂
Or, if Google decided to match your keywords to new irrelevant search terms you might want to add some negatives in the mix.

Declining search terms might require a reallocation of budget.

NB: If you like this script and run PMax campaigns, be sure to also check out my Google Ads Script – Performance Max Trending Search Categories.

"This script has been extremely timely and useful during the COVID-19 crisis. We’re finding that trending search terms aren’t the ones we expected and we needed to negate some keywords and shift budget to avoid overspending on irrelevant terms or blowing budgets. Our clients really appreciate the insights that the script offers. We’re able to easily share trends with them weekly. I think everyone should add it to their accounts! We’ve added it to most of ours and it’s been extremely helpful."

---

Melissa Mackey, Search Supervisor at gyro

Nils Rooijmans

2020-05-23T09:41:17+00:00

Melissa Mackey, Search Supervisor at gyro

"This script has been extremely timely and useful during the COVID-19 crisis. We’re finding that trending search terms aren’t the ones we expected and we needed to negate some keywords and shift budget to avoid overspending on irrelevant terms or blowing budgets. Our clients really appreciate the insights that the script offers. We’re able to easily share trends with them weekly. I think everyone should add it to their accounts! We’ve added it to most of ours and it’s been extremely helpful."

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

0

0

Nils Rooijmans

INSTRUCTIONS:

1. See the script code below. Install the script in your account.
   Don’t worry if you have never done this before. You do not need any coding skills. It is as simple as copy-paste. Simply follow these instructions on how to set up and schedule google ads scripts.
2. Create a new Google Sheet
   (tip for chrome users: simply type ‘sheets.new’ in the address bar)
3. Add the complete URL of the spreadsheet to the script (line 23)
4. Add your email address to the script (line 24)
5. Add the name of your Google Ads account to the subject of emails (line 46)
6. Authorize and Preview 
7. Schedule to run weekly (I prefer Mondays at 6AM)

NOTE: the output spreadsheet contains up to three sheets.

"Super handy script! Great way to get new insights into trending keywords, also compared to last year. The script is very easy to use (even for someone without javascript knowledge;)). Highly recommend!"

---

Marloes Klaver, Owner and Marketing Specialist at AltVijf

Nils Rooijmans

2020-05-23T09:37:49+00:00

Marloes Klaver, Owner and Marketing Specialist at AltVijf

"Super handy script! Great way to get new insights into trending keywords, also compared to last year. The script is very easy to use (even for someone without javascript knowledge;)). Highly recommend!"

https://nilsrooijmans.com/testimonials/great-way-to-get-new-insights-into-trending-keywords/

0

0

Nils Rooijmans

Subscribe to my mailing list to receive more scripts and updates on how to start learning to write your own Google Ads Scripts.

/**
*
* Report Trending Search Terms
*
* Creates a Google Sheets report containing up to three optional 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
* 2. Sheet with trending search terms for Last Week versus Same Week in 2019
* 
*
* @author: Nils Rooijmans
*
* Version 1.2 : Added support for comparison between this year and the pre-corona year 2019
* Version 1.1 : Added support for filtering based on campaign names
*
* 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 COMPARE_LAST_WEEK_TO_SAME_WEEK_2019 = true; // set to true if you want to compare last weeks search terms to the search terms from the same week in the pre-corona year 2019, set to false otherwise
var COMPARE_LAST_WEEK_TO_SAME_WEEK_LAST_YEAR = true; // set to true if you want to compare last weeks search terms to the search terms from the same week last year, set to false otherwise
var COMPARE_LAST_WEEK_TO_WEEK_BEFORE = true; // set to true if you want to compare last weeks search terms to the search terms from the week before last week, set to false otherwise

var SEND_EMAIL = true; // set to false if you do not want to send alert email
var EMAIL_SUBJECT = "[GAds Script] - Trending Search Terms"; // 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 searchtermsLastWeek = {};
  var searchtermsWeekBeforeLastWeek = {};
  var searchtermsLastWeekLastYear = {};
  var searchtermsLastWeek2019 = {};
  
  var trendingSearchterms_LastWeek_vs_WeekBefore = {};
  var trendingSearchterms_LastWeek_vs_LastYear = {};
  var trendingSearchterms_LastWeek_vs_2019 = {};
  
  searchtermsLastWeek = getSearchterms("lastWeek"); 
  debug("Nr of searchterms last week: "+Object.keys(searchtermsLastWeek).length);

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

  if (COMPARE_LAST_WEEK_TO_SAME_WEEK_2019) {  
    searchtermsLastWeek2019 = getSearchterms("lastWeek2019"); 
    debug("Nr of searchterms week last week in 2019: "+Object.keys(searchtermsLastWeek2019).length);
    trendingSearchterms_LastWeek_vs_2019 = getTrendingSearchterms(searchtermsLastWeek, searchtermsLastWeek2019);    
    debug("Nr of trending searchterms LastWeek_vs_2019: "+Object.keys(trendingSearchterms_LastWeek_vs_2019).length);
  }
  
  
  generateReport(trendingSearchterms_LastWeek_vs_WeekBefore, trendingSearchterms_LastWeek_vs_LastYear, trendingSearchterms_LastWeek_vs_2019);

  // 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 + Object.keys(trendingSearchterms_LastWeek_vs_2019).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, (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, trendingSearchterms_LastWeek_vs_2019) {
    
  var rowsThisYear  = getReportRows(trendingSearchterms_LastWeek_vs_WeekBefore);
  var rowsLastYear  = getReportRows(trendingSearchterms_LastWeek_vs_LastYear);
  var rows2019  = getReportRows(trendingSearchterms_LastWeek_vs_2019);  
  
  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
  if (COMPARE_LAST_WEEK_TO_WEEK_BEFORE) {

    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 last year's data
  if (COMPARE_LAST_WEEK_TO_SAME_WEEK_LAST_YEAR) {
  
    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()); 
  }
  
  // Third, generate the sheet for last year's data
  if (COMPARE_LAST_WEEK_TO_SAME_WEEK_2019) {
  
    var sheet=spreadsheet.getSheetByName('LastWeek_vs_2019');

    if (!sheet) {
      Logger.log("Cannot open exisiting reporting sheet 'LastWeek_vs_2019' , creating new one") ;
      spreadsheet.insertSheet('LastWeek_vs_2019');
      var sheet=spreadsheet.getSheetByName('LastWeek_vs_2019');
    }

    generateReportSheet(sheet, rows2019);
    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 if (sheet.getName()=='LastWeek_vs_2019') {
    var header = [  
      "Searchterm",
      "Impressions Last Week",
      "Impressions Last Week 2019",
      "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");
  
  if (rows.length > 0) {
    var rules = sheet.getConditionalFormatRules();

    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}]);
  }
}


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


// return date x days before today, for the year 2019
function dates2019(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');
  
 // hack to make it a 2019 date string
 var output2 = "2019"+output.slice(4); 
  
 return output2;
}

Join thousands of PPC geeks who already have access:

TIP: Get Access to my Ultimate List of 400+ Google Ads Scripts >>