Google Ads N-Gram Analyzer

Setting Up The Sheet

1. Make a copy of the template sheet using the button above.

2. Rename and organize in your Drive as needed :)

3. Go through all settings and edit all relevant settings using the light blue cells

   • Start Date/End Date: The date range you want to use to analyze your Search Queries. Try to set the start date to the day after the most recent analysis you've done. Be mindful of the size of the dataset, as the Sheet will begin to buckle under more than a year's worth of data with 1-6 levels of analysis.

   • Min N Gram / Max N Gram: The range of phrase lengths you want to analyze. From 1 word chunks of Search Queries to whatever you want.  Recommended setting the max as no more than 4-6 as it'll start to get overwhelming/meaningless to do this exercise. Also the Sheet will just get too bogged down.

   • Campaign Name Does/Does Not Contain: Enter in any parameters you want here. They will be compounded, so be aware of that. If you don't want to exclude any campaigns or evaluate a specific set, just skip this. This is case sensitive, as it will be entered into a CONTAINS AWQL statement in the script.

   • Ignore Paused Campaigns/Ad Groups: Self explanatory :)

4. All set! Script & instructions below.

Setting Up The Script

1. Go to the specific Google Ads account you want to analyze (not our MCC!), and go to Tools > Bulk Actions > Scripts.

2. Click the Blue + button to create a new script. Usually takes a few seconds.

   1. 1. • Note: If you already have run this script before, feel free to just edit the existing Script and send the data to the new Sheet you've created.

3. When the new Script page has opened, name your script "N-Gram Analyzer" ...or something easily identifiable.

4. Copy and paste the entire script below into the script box (be sure to overwrite the preset "function main()" contents when you do).

5. Just underneath the "function main()" replace the ENTER_YOUR_SPREADSHEET_URL_HERE with the full URL of your spreadsheet. Be sure to leave the quotation marks in place!

6. That's it. Click Save, then Run. You'll be prompted at the bottom to Authorize the script – go ahead and Authorize that – and then just Click Run again and Run Without Preview and you should be good to go! The script should take around anywhere from 2-10 minutes to run fully, depending on the size of your account. You'll see it listed as "Finished Successfully" in green when it's fully run.

   1. 1. • Pro Tip: Since this is just a reporting script, you can also just hit Preview and the script will run in full (this applies to any Script that doesn't make changes directly to the account). The benefit of this is that if the Script fails for any reason, it won't show up in the Script logs.

7. I wouldn't recommend scheduling this script to run routinely. Saving the data in the original Sheet and just updating the Spreadsheet URL to a new Sheet will help maintain the historical data that you were referencing when you made changes. Can be helpful for justifying/understanding past decisions, etc.

//CUSTOM PARAMETERS - EDIT BELOW

var SheetURL = "ENTER_YOUR_SPREADSHEET_URL_HERE";

//END CUSTOM PARAMETERS - DO NOT EDIT BELOW

// Reference: https://developers.google.com/adwords/api/docs/guides/awql

//settings data

var QueryTabName = "Search Query Data";

var KeywordTabName = "Keyword Data";

var ss = SpreadsheetApp.openByUrl(SheetURL);

var settingsSheet = ss.getSheetByName('Settings');

//establish date range

var from = Utilities.formatDate(settingsSheet.getRange('C3').getValue(), 'EST', 'yyyyMMdd');

var to = Utilities.formatDate(settingsSheet.getRange('C4').getValue(), 'EST', 'yyyyMMdd');

var DateRange = from+','+to;

//establish campaign name filters

var campaignNameInclude = settingsSheet.getRange('B10').getValue();

var campaignNameExclude = settingsSheet.getRange('B12').getValue();

var campaignNameFilterText = '';

if (campaignNameInclude) {

  var campaignNameIncludeText = ' AND CampaignName CONTAINS "'+campaignNameInclude+'"';

  campaignNameFilterText += campaignNameIncludeText;

}

if (campaignNameExclude) {

  var campaignNameExcludeText = ' AND CampaignName DOES_NOT_CONTAIN "'+campaignNameExclude+'"';

  campaignNameFilterText += campaignNameExcludeText;

}

console.log('Campaign Name Filter Statement: '+campaignNameFilterText);

//establish campaign and ad group status filters

var campaignStatus = settingsSheet.getRange('D14').getValue();

var adGroupStatus = settingsSheet.getRange('D15').getValue();

var statusFilterText = '';

if (campaignStatus === true) {

  statusFilterText += ' AND CampaignStatus = "ENABLED"'

}

if (adGroupStatus === true) {

  statusFilterText += ' AND AdGroupStatus = "ENABLED"'

}

console.log('Campaign/Ad Group Status Filter Statement: '+statusFilterText);

//queries

var QUERIES = [

                {'query' : 'SELECT CampaignName, AdGroupName, KeywordId, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, QueryTargetingStatus, Impressions, Clicks, Cost, Conversions, ConversionValue ' +

                   'FROM   SEARCH_QUERY_PERFORMANCE_REPORT ' +

                   'WHERE  Impressions > 1' + statusFilterText + campaignNameFilterText + ' ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : QueryTabName,

                   'reportVersion' : 'v201809'

                }, 

                {'query' : 'SELECT CampaignName, AdGroupName, Id, Criteria, KeywordMatchType, Impressions, Clicks, Cost, Conversions, ConversionValue ' +

                   'FROM   KEYWORDS_PERFORMANCE_REPORT ' +

                   'WHERE  Impressions > 1' + statusFilterText + campaignNameFilterText + ' ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : KeywordTabName,

                   'reportVersion' : 'v201809'

                }

              ];

function main() {

  for(var i in QUERIES) {

    var queryObject = QUERIES[i];

    var query = queryObject.query;

    var spreadsheetUrl = queryObject.spreadsheetUrl;

    var tabName = queryObject.tabName;

    var reportVersion = queryObject.reportVersion;

    //Logger.log(spreadsheetUrl + " " + query);

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var sheet = spreadsheet.getSheetByName(tabName);

    var report = AdWordsApp.report(query, {apiVersion: reportVersion});

    report.exportToSheet(sheet);

  }

  //run nGram analysis

  var searchQuerySheet = ss.getSheetByName('Search Query Data');

  var keywordDataSheet = ss.getSheetByName('Keyword Data');

  var settingsSheet = ss.getSheetByName('Settings');

  //freeze all formulas

  settingsSheet.getRange('E1').setValue(0);

  SpreadsheetApp.flush();

  var nGramMin = settingsSheet.getRange('D6').getValue();

  var nGramMax = settingsSheet.getRange('D7').getValue();

  // Create or get the N-Gram data sheets

  var searchQueryNGramSheet = ss.getSheetByName('Search Query N-Gram Data') || ss.insertSheet('Search Query N-Gram Data');

  var keywordNGramSheet = ss.getSheetByName('Keyword N-Gram Data') || ss.insertSheet('Keyword N-Gram Data');

  // Clear only columns A and B on the N-Gram sheets

  searchQueryNGramSheet.getRange('A:B').clearContent();

  keywordNGramSheet.getRange('A:B').clearContent();

  // Function to get terms from a given column range

  function getTerms(sheet, range) {

    return sheet.getRange(range).getValues().flat().filter(String);

  }

  // Function to generate N-Grams from a term

  function generateNGramsFromTerm(term, n) {

    var nGrams = [];

    var words = term.split(/\s+/);

    for (var i = 0; i <= words.length - n; i++) {

      nGrams.push(words.slice(i, i + n).join(' '));

    }

    return nGrams;

  }

  // Function to generate and add N-Grams to a sheet

  function addNGramsToSheet(sheet, terms) {

    var nGramData = [];

    var uniqueNGrams = new Set();

    terms.forEach(function(term) {

      for (var n = nGramMin; n <= nGramMax; n++) {

        var nGrams = generateNGramsFromTerm(term, n);

        nGrams.forEach(function(nGram) {

          if (!uniqueNGrams.has(nGram)) {

            uniqueNGrams.add(nGram);

            nGramData.push([nGram, n]);

          }

        });

      }

    });

    // Sort the N-Gram data

    nGramData.sort(function(a, b) {

      if (a[0] < b[0]) return -1;

      if (a[0] > b[0]) return 1;

      return 0;

    });

    // Add the N-Gram data to the sheet

    if (nGramData.length > 0) {

      sheet.getRange(1, 1, nGramData.length, 2).setValues(nGramData);

    }

  }

  // Get the terms from the specified columns

  var searchQueryTerms = getTerms(searchQuerySheet, 'E2:E' + searchQuerySheet.getLastRow());

  var keywordDataTerms = getTerms(keywordDataSheet, 'D2:D' + keywordDataSheet.getLastRow());

  // Add N-Grams to the respective sheets

  addNGramsToSheet(searchQueryNGramSheet, searchQueryTerms);

  addNGramsToSheet(keywordNGramSheet, keywordDataTerms);

  SpreadsheetApp.flush();

  //unfreeze all formulas

  settingsSheet.getRange('E1').setValue(1);

  SpreadsheetApp.flush();

}

Analyzing The Sheet

• So after you've done all the above, the Sheet may need a few minutes for all of the formulas to catch up and finish loading.

• Once that's done, you can reference the two Green tabs at the back of the Sheet (Keyword N-Gram Analysis and Search Query N-Gram Analysis) to identify potential Keywords to pause and Negative Keywords to add, respectively.

  • • Note: The Keyword N-Gram Analysis will run an N-Gram analysis on the targeted Keywords and the Search Query N-Gram Analysis will run an N-Gram analysis on the resulting Search Queries.

    • Another note: The Campaign, Ad Group, and Keyword columns in the two analyses contain a comma-separated list of all of the places that that N-Gram occurs. The list of N-Grams, themselves, is fully unique.

    • One more: You can use the filters/sort options in Row 3 to adjust the data displayed in the table below.

      1. 1. The CPL Flag will automatically hide everything below the average Keyword CPL.