April 16, 2020

Remove Wasted PPC Spend with N-gram Analysis

by Collin Slattery

Now more than ever, every dollar matters. Businesses cannot afford waste, whether that is in unutilized software, fancy office perks, or wasted advertising spend. And yet, most businesses are wasting a large percentage of their PPC budgets on keywords that don’t convert. 

Across the hundreds of search campaigns we have analyzed and managed over the years, we have never analyzed an account that didn’t have at least 5% waste. Most were over 15%. 

We have a process that we use to quickly and easily identify and remove wasted ad spend, and I’m going to show you can do it too. 

It requires no paid tools and takes less than an hour. 

The process is called N-gram analysis.

What is N-Gram Analysis?

An n-gram is a contiguous sequence of n items from a given sample of text. For our purposes, the items we are referring to are words. So a 1-gram would be any individual word, a 2-gram would be two word phrases, a 3-gram would be three word phrases, etc. 

Let’s take a look at the search term “affordable Brooklyn movers” as an example. 

  • We have 3 1-grams with “affordable” “Brooklyn” and “movers”
  • We have 2 2-grams with “affordable Brooklyn” and “Brooklyn movers”
  • We have 1 3-gram with “affordable Brooklyn movers”

How does this help you find waste in your Google Ads account?

By running a script that collects all the data on your search terms and converts that data into a spreadsheet of n-grams, you are able to identify n-grams that drive clicks but not conversions and turn them into negative keywords easily. This will make your Google Ads campaigns more efficient and profitable. 

Before we start you will need a few things:

  1. An empty Google Sheet
  2. A Google Ads account with 3+ months of search campaign data
  3. The n-gram Analysis script

Setting up the Script in your Ads Account

The first thing we need to do is set up the n-gram analysis script. I unfortunately cannot take credit for the creation of the script, that credit goes to the great team over at Brainlabs. It is important to note that you do not need any programming experience to run this n-gram script! 

All you need is some copy and paste skills.

/**
*
* Search Query Mining Tool
*
* This script calculates the contribution of each word or phrase found in the 
* search query report and outputs a report into a Google Doc spreadsheet.
*
* Version: 2.2
* Updated 2015-09-17: replacing 'KeywordText' with 'Criteria'
* Updated 2016-10-11: replacing 'ConvertedClicks' with 'Conversions'
* Google AdWords Script maintained on brainlabsdigital.com
*
**/

function main() {
  //////////////////////////////////////////////////////////////////////////////
  // Options
  
  var startDate = "YYYY-MM-DD";
  var endDate = "YYYY-MM-DD";
  // The start and end date of the date range for your search query data
  // Format is yyyy-mm-dd
  
  var currencySymbol = "$";
  // The currency symbol used for formatting. For example "£", "$" or "€".
  
  var campaignNameContains = "";
  // 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 campaignNameDoesNotContain = "";
  // Use this if you want to exclude some campaigns
  // such as campaigns with names containing 'Brand' or 'Shopping'.
  // Leave as "" if not wanted.
  
  var ignorePausedCampaigns = true;
  // Set this to true to only look at currently active campaigns.
  // Set to false to include campaigns that had impressions but are currently paused.
  
  var ignorePausedAdGroups = true;
  // Set this to true to only look at currently active ad groups.
  // Set to false to include ad groups that had impressions but are currently paused.
  
  var checkNegatives = true;
  // Set this to true to remove queries that would be excluded by your negative keywords.
  
  var spreadsheetUrl = "GOOGLE_SHEET_URL";
  // The URL of the Google Doc the results will be put into.
  
  var minNGramLength = 1;
  var maxNGramLength = 2;
  // The word length of phrases to be checked.
  // For example if minNGramLength is 1 and maxNGramLength is 3, 
  // phrases made of 1, 2 and 3 words will be checked.
  // Change both min and max to 1 to just look at single words.
  
  var clearSpreadsheet = true;
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Thresholds
  
  var queryCountThreshold = 0;
  var impressionThreshold = 10;
  var clickThreshold = 0;
  var costThreshold = 0;
  var conversionThreshold = 0;
  // Words will be ignored if their statistics are lower than any of these thresholds
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Check the spreadsheet has been entered, and that it works
  if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/spreadsheets/d/18lAQzc6pk5wNt9Vw7evKzlJ8u9R6jF-647lxK3r1BS8/edit#gid=0") {
    Logger.log("Problem with the spreadsheet URL: make sure you've replaces the default with a valid spreadsheet URL.");
    return;
  }
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  } catch (e) {
    Logger.log("Problem with the spreadsheet URL: '" + e + "'");
    return;
  }
  
  // Get the IDs of the campaigns to look at
  var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
  var activeCampaignIds = [];
  var whereStatements = "";
  
  if (campaignNameDoesNotContain != "") {
    whereStatements += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain + "' ";
  }
  if (ignorePausedCampaigns) {
    whereStatements += "AND CampaignStatus = ENABLED ";
  } else {
    whereStatements += "AND CampaignStatus IN ['ENABLED','PAUSED'] ";
  }
  
  var campaignReport = AdWordsApp.report(
    "SELECT CampaignName, CampaignId " +
    "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
    "WHERE CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
    "AND Impressions > 0 " + whereStatements +
    "DURING " + dateRange
  );
  var campaignRows = campaignReport.rows();
  while (campaignRows.hasNext()) {
    var campaignRow = campaignRows.next();
    activeCampaignIds.push(campaignRow["CampaignId"]);
  }//end while
  
  if (activeCampaignIds.length == 0) {
    Logger.log("Could not find any campaigns with impressions and the specified options.");
    return;
  }
  
  var whereAdGroupStatus = "";
  if (ignorePausedAdGroups) {
    var whereAdGroupStatus = "AND AdGroupStatus = ENABLED ";
  } else {
    whereAdGroupStatus += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
  }
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Find the negative keywords
  var negativesByGroup = [];
  var negativesByCampaign = [];
  var sharedSetData = [];
  var sharedSetNames = [];
  var sharedSetCampaigns = [];
  
  if (checkNegatives) {
    // Gather ad group level negative keywords
    var keywordReport = AdWordsApp.report(
      "SELECT CampaignId, AdGroupId, Criteria, KeywordMatchType " +
      "FROM   KEYWORDS_PERFORMANCE_REPORT " +
      "WHERE Status = ENABLED AND IsNegative = TRUE " + whereAdGroupStatus +
      "AND CampaignId IN [" + activeCampaignIds.join(",") + "] " +
      "DURING " + dateRange
      );
    
    var keywordRows = keywordReport.rows();
    while (keywordRows.hasNext()) {
      var keywordRow = keywordRows.next();
      
      if (negativesByGroup[keywordRow["AdGroupId"]] == undefined) {
        negativesByGroup[keywordRow["AdGroupId"]] = 
          [[keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]];
      } else {
        negativesByGroup[keywordRow["AdGroupId"]].push([keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]);
      }
    }
    
    // Gather campaign level negative keywords
    var campaignNegReport = AdWordsApp.report(
      "SELECT CampaignId, Criteria, KeywordMatchType " +
      "FROM   CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT " +
      "WHERE  IsNegative = TRUE " +
      "AND CampaignId IN [" + activeCampaignIds.join(",") + "]"
    );
    var campaignNegativeRows = campaignNegReport.rows();
    while (campaignNegativeRows.hasNext()) {
      var campaignNegativeRow = campaignNegativeRows.next();
      if (negativesByCampaign[campaignNegativeRow["CampaignId"]] == undefined) {
        negativesByCampaign[campaignNegativeRow["CampaignId"]] = [[campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]];
      } else {
        negativesByCampaign[campaignNegativeRow["CampaignId"]].push([campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]);
      }
    }
    
    // Find which campaigns use shared negative keyword sets
    var campaignSharedReport = AdWordsApp.report(
      "SELECT CampaignName, CampaignId, SharedSetName, SharedSetType, Status " +
      "FROM   CAMPAIGN_SHARED_SET_REPORT " +
      "WHERE SharedSetType = NEGATIVE_KEYWORDS " +
      "AND CampaignId IN [" + activeCampaignIds.join(",") + "]");
    var campaignSharedRows = campaignSharedReport.rows();
    while (campaignSharedRows.hasNext()) {
      var campaignSharedRow = campaignSharedRows.next();
      if (sharedSetCampaigns[campaignSharedRow["SharedSetName"]] == undefined) {
        sharedSetCampaigns[campaignSharedRow["SharedSetName"]] = [campaignSharedRow["CampaignId"]];
      } else {
        sharedSetCampaigns[campaignSharedRow["SharedSetName"]].push(campaignSharedRow["CampaignId"]);
      }
    }
    
    // Map the shared sets' IDs (used in the criteria report below)
    // to their names (used in the campaign report above)
    var sharedSetReport = AdWordsApp.report(
      "SELECT Name, SharedSetId, MemberCount, ReferenceCount, Type " +
      "FROM   SHARED_SET_REPORT " +
      "WHERE ReferenceCount > 0 AND Type = NEGATIVE_KEYWORDS ");
    var sharedSetRows = sharedSetReport.rows();
    while (sharedSetRows.hasNext()) {
      var sharedSetRow = sharedSetRows.next();
      sharedSetNames[sharedSetRow["SharedSetId"]] = sharedSetRow["Name"];
    }
    
    // Collect the negative keyword text from the sets,
    // and record it as a campaign level negative in the campaigns that use the set
    var sharedSetReport = AdWordsApp.report(
      "SELECT SharedSetId, KeywordMatchType, Criteria " +
      "FROM   SHARED_SET_CRITERIA_REPORT ");
    var sharedSetRows = sharedSetReport.rows();
    while (sharedSetRows.hasNext()) {
      var sharedSetRow = sharedSetRows.next();
      var setName = sharedSetNames[sharedSetRow["SharedSetId"]];
      if (sharedSetCampaigns[setName] !== undefined) {
        for (var i=0; i<sharedSetCampaigns[setName].length; i++) {
          var campaignId = sharedSetCampaigns[setName][i];
          if (negativesByCampaign[campaignId] == undefined) {
            negativesByCampaign[campaignId] = 
              [[sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]];
          } else {
            negativesByCampaign[campaignId].push([sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]);
          }
        }
      }
    }
    
    Logger.log("Finished finding negative keywords");
  }// end if
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Define the statistics to download or calculate, and their formatting
  
  var statColumns = ["Clicks", "Impressions", "Cost", "Conversions", "ConversionValue"];
  var calculatedStats = [["CTR","Clicks","Impressions"],
                         ["CPC","Cost","Clicks"],
                         ["Conv. Rate","Conversions","Clicks"],
                         ["Cost / conv.","Cost","Conversions"],
                         ["Conv. value/cost","ConversionValue","Cost"]]
  var currencyFormat = currencySymbol + "#,##0.00";
  var formatting = ["#,##0", "#,##0", "#,##0", currencyFormat, "#,##0", currencyFormat,"0.00%",currencyFormat,"0.00%",currencyFormat,"0.00%"];
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Go through the search query report, remove searches already excluded by negatives
  // record the performance of each word in each remaining query
  
  var queryReport = AdWordsApp.report(
    "SELECT CampaignName, CampaignId, AdGroupId, AdGroupName, Query, " + statColumns.join(", ") + " " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
      "WHERE CampaignId IN [" + activeCampaignIds.join(",") + "] " + whereAdGroupStatus +
        "DURING " + dateRange);
  
  var numberOfWords = [];
  var campaignNGrams = {};
  var adGroupNGrams = {};
  var totalNGrams = [];
  
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    totalNGrams[n] = {};
  }
  
  var queryRows = queryReport.rows();
  while (queryRows.hasNext()) {
    var queryRow = queryRows.next();
    
    if (checkNegatives) {
      var searchIsExcluded = false;
      
      // Checks if the query is excluded by an ad group level negative
      if (negativesByGroup[queryRow["AdGroupId"]] !== undefined) {
        for (var i=0; i<negativesByGroup[queryRow["AdGroupId"]].length; i++) {
          if ( (negativesByGroup[queryRow["AdGroupId"]][i][1] == "exact" &&
                queryRow["Query"] == negativesByGroup[queryRow["AdGroupId"]][i][0]) ||
              (negativesByGroup[queryRow["AdGroupId"]][i][1] != "exact" &&
              (" "+queryRow["Query"]+" ").indexOf(" "+negativesByGroup[queryRow["AdGroupId"]][i][0]+" ") > -1 )){
            searchIsExcluded = true;
            break;
          }
        }
      }
      
      // Checks if the query is excluded by a campaign level negative
      if (!searchIsExcluded && negativesByCampaign[queryRow["CampaignId"]] !== undefined) {
        for (var i=0; i<negativesByCampaign[queryRow["CampaignId"]].length; i++) {
          if ( (negativesByCampaign[queryRow["CampaignId"]][i][1] == "exact" &&
                queryRow["Query"] == negativesByCampaign[queryRow["CampaignId"]][i][0]) ||
              (negativesByCampaign[queryRow["CampaignId"]][i][1]!= "exact" &&
              (" "+queryRow["Query"]+" ").indexOf(" "+negativesByCampaign[queryRow["CampaignId"]][i][0]+" ") > -1 )){
            searchIsExcluded = true;
            break;
          }
        }
      }
      
      if (searchIsExcluded) {continue;}
    }
    
    var currentWords = queryRow["Query"].split(" ");
    
    if (campaignNGrams[queryRow["CampaignName"]] == undefined) {
      campaignNGrams[queryRow["CampaignName"]] = [];
      adGroupNGrams[queryRow["CampaignName"]] = {};
      
      for (var n=minNGramLength; n<maxNGramLength+1; n++) {
        campaignNGrams[queryRow["CampaignName"]][n] = {};
      }
    }
    
    if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] == undefined) {
      adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] = [];
      for (var n=minNGramLength; n<maxNGramLength+1; n++) {
        adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n] = {};
      }
    }
    
    var stats = [];
    for (var i=0; i<statColumns.length; i++) {
      stats[i] = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
    }
    
    var wordLength = currentWords.length;
    if (wordLength > 6) {
      wordLength = "7+";
    }
    if (numberOfWords[wordLength] == undefined) {
      numberOfWords[wordLength] = [];
    }
    for (var i=0; i<statColumns.length; i++) {
      if (numberOfWords[wordLength][statColumns[i]] > 0) {
        numberOfWords[wordLength][statColumns[i]] += stats[i];
      } else {
        numberOfWords[wordLength][statColumns[i]] = stats[i];
      }
    }
    
    // Splits the query into n-grams and records the stats for each
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      if (n > currentWords.length) {
        break;
      }
      
      var doneNGrams = [];
      
      for (var w=0; w < currentWords.length - n + 1; w++) {
        var currentNGram = '="' + currentWords.slice(w,w+n).join(" ") + '"';
        
        if (doneNGrams.indexOf(currentNGram) < 0) {
          
          if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram] == undefined) {
            campaignNGrams[queryRow["CampaignName"]][n][currentNGram] = {};
            campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"] = 0;
          }
          if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] == undefined) {
            adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] = {};
            adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"] = 0;
          }
          if (totalNGrams[n][currentNGram] == undefined) {
            totalNGrams[n][currentNGram] = {};
            totalNGrams[n][currentNGram]["Query Count"] = 0;
          }
          
          campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"]++;
          adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"]++;
          totalNGrams[n][currentNGram]["Query Count"]++;
          
          for (var i=0; i<statColumns.length; i++) {
            if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] > 0) {
              campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] += stats[i];
            } else {
              campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] = stats[i];
            }
            
            if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] > 0) {
              adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] += stats[i];
            } else {
              adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] = stats[i];
            }
            
            if (totalNGrams[n][currentNGram][statColumns[i]] > 0) {
              totalNGrams[n][currentNGram][statColumns[i]] += stats[i];
            } else {
              totalNGrams[n][currentNGram][statColumns[i]] = stats[i];
            }
          }
          
          doneNGrams.push(currentNGram);
        }
      }
    }
  }
  
  Logger.log("Finished analysing queries.");
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Output the data into the spreadsheet
  
  var wordLengthOutput = [];
  var wordLengthFormat = [];
  var outputs = [];
  var formats = [];
  
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    outputs[n] = {};
    outputs[n]['campaign'] = [];
    outputs[n]['adgroup'] = [];
    outputs[n]['total'] = [];
    formats[n] = {};
    formats[n]['campaign'] = [];
    formats[n]['adgroup'] = [];
    formats[n]['total'] = [];
  }
  
  // Create headers
  var calcStatNames = [];
  for (var s=0; s<calculatedStats.length; s++) {
    calcStatNames.push(calculatedStats[s][0]);
  }
  var statNames = statColumns.concat(calcStatNames);
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    outputs[n]['campaign'].push(["Campaign","Phrase","Query Count"].concat(statNames));
    outputs[n]['adgroup'].push(["Campaign","Ad Group","Phrase","Query Count"].concat(statNames));
    outputs[n]['total'].push(["Phrase","Query Count"].concat(statNames));
  }
  wordLengthOutput.push(["Word count"].concat(statNames));
  
  // Organise the ad group level stats into an array for output
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    for (var campaign in adGroupNGrams) {
      for (var adGroup in adGroupNGrams[campaign]) {
        for (var nGram in adGroupNGrams[campaign][adGroup][n]) {
          
          // skips nGrams under the thresholds
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Impressions"] < impressionThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Clicks"] < clickThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Cost"] < costThreshold) {continue;}
          if (adGroupNGrams[campaign][adGroup][n][nGram]["Conversions"] < conversionThreshold) {continue;}
          
          var printline = [campaign, adGroup, nGram, adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"]];
          
          for (var s=0; s<statColumns.length; s++) {
            printline.push(adGroupNGrams[campaign][adGroup][n][nGram][statColumns[s]]);
          }
          
          for (var s=0; s<calculatedStats.length; s++) {
            var multiplier = calculatedStats[s][1];
            var divisor = calculatedStats[s][2];
            if (adGroupNGrams[campaign][adGroup][n][nGram][divisor] > 0) {
              printline.push(adGroupNGrams[campaign][adGroup][n][nGram][multiplier] / adGroupNGrams[campaign][adGroup][n][nGram][divisor]);
            } else {
              printline.push("-");
            }
          }
          outputs[n]['adgroup'].push(printline);
          formats[n]['adgroup'].push(["0","0","0"].concat(formatting));
        }
      }
    }
  }
  
  // Organise the campaign level stats into an array for output
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    for (var campaign in campaignNGrams) {
      for (var nGram in campaignNGrams[campaign][n]) {
        
        // skips nGrams under the thresholds
        if (campaignNGrams[campaign][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Impressions"] < impressionThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Clicks"] < clickThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Cost"] < costThreshold) {continue;}
        if (campaignNGrams[campaign][n][nGram]["Conversions"] < conversionThreshold) {continue;}
        
        var printline = [campaign, nGram, campaignNGrams[campaign][n][nGram]["Query Count"]];
        
        for (var s=0; s<statColumns.length; s++) {
          printline.push(campaignNGrams[campaign][n][nGram][statColumns[s]]);
        }
        
        for (var s=0; s<calculatedStats.length; s++) {
          var multiplier = calculatedStats[s][1];
          var divisor = calculatedStats[s][2];
          if (campaignNGrams[campaign][n][nGram][divisor] > 0) {
            printline.push(campaignNGrams[campaign][n][nGram][multiplier] / campaignNGrams[campaign][n][nGram][divisor]);
          } else {
            printline.push("-");
          }
        }
        outputs[n]['campaign'].push(printline);
        formats[n]['campaign'].push(["0","0"].concat(formatting));
      }
    }
  }
  
  // Organise the account level stats into an array for output
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    for (var nGram in totalNGrams[n]) {
      
      // skips n-grams under the thresholds
      if (totalNGrams[n][nGram]["Query Count"] < queryCountThreshold) {continue;}
      if (totalNGrams[n][nGram]["Impressions"] < impressionThreshold) {continue;}
      if (totalNGrams[n][nGram]["Clicks"] < clickThreshold) {continue;}
      if (totalNGrams[n][nGram]["Cost"] < costThreshold) {continue;}
      if (totalNGrams[n][nGram]["Conversions"] < conversionThreshold) {continue;}
      
      var printline = [nGram, totalNGrams[n][nGram]["Query Count"]];
      
      for (var s=0; s<statColumns.length; s++) {
        printline.push(totalNGrams[n][nGram][statColumns[s]]);
      }
      
      for (var s=0; s<calculatedStats.length; s++) {
        var multiplier = calculatedStats[s][1];
        var divisor = calculatedStats[s][2];
        if (totalNGrams[n][nGram][divisor] > 0) {
          printline.push(totalNGrams[n][nGram][multiplier] / totalNGrams[n][nGram][divisor]);
        } else {
          printline.push("-");
        }
      }
      outputs[n]['total'].push(printline);
      formats[n]['total'].push(["0"].concat(formatting));
    }
  }
  
  // Organise the word count analysis into an array for output
  for (var i = 1; i<8; i++) {
    if (i < 7) {
      var wordLength = i;
    } else {
      var wordLength = "7+";
    }
    
    var printline = [wordLength];
    
    if (numberOfWords[wordLength] == undefined) {
      printline.push([0,0,0,0,"-","-","-","-"]);
    } else {
      for (var s=0; s<statColumns.length; s++) {
        printline.push(numberOfWords[wordLength][statColumns[s]]);
      }
      
      for (var s=0; s<calculatedStats.length; s++) {
        var multiplier = calculatedStats[s][1];
        var divisor = calculatedStats[s][2];
        if (numberOfWords[wordLength][divisor] > 0) {
          printline.push(numberOfWords[wordLength][multiplier] / numberOfWords[wordLength][divisor]);
        } else {
          printline.push("-");
        }
      }
    }
    wordLengthOutput.push(printline);
    wordLengthFormat.push(formatting);
  }
  
  var filterText = "";
  if (ignorePausedAdGroups) {
    filterText = "Active ad groups";
  } else {
    filterText = "All ad groups";
  }
  
  if (ignorePausedCampaigns) {
    filterText += " in active campaigns";
  } else {
    filterText += " in all campaigns";
  }
  
  if (campaignNameContains != "") {
    filterText += " containing '" + campaignNameContains + "'";
    if (campaignNameDoesNotContain != "") {
      filterText += " and not containing '" + campaignNameDoesNotContain + "'";
    }
  } else if (campaignNameDoesNotContain != "") {
    filterText += " not containing '" + campaignNameDoesNotContain + "'";
  }
  
  // Find or create the required sheets
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var campaignNGramName = [];
  var adGroupNGramName = [];
  var totalNGramName = [];
  var campaignNGramSheet = [];
  var adGroupNGramSheet = [];
  var totalNGramSheet = [];
  
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    if (n==1) {
      campaignNGramName[n] = "Campaign Word Analysis";
      adGroupNGramName[n] = "Ad Group Word Analysis";
      totalNGramName[n] = "Account Word Analysis";	  
    } else {
      campaignNGramName[n] = "Campaign " + n + "-Gram Analysis";
      adGroupNGramName[n] = "Ad Group " + n + "-Gram Analysis";
      totalNGramName[n] = "Account " + n + "-Gram Analysis";
    }
    
    campaignNGramSheet[n] = spreadsheet.getSheetByName(campaignNGramName[n]);
    if (campaignNGramSheet[n] == null) {
      campaignNGramSheet[n] = spreadsheet.insertSheet(campaignNGramName[n]);
    }
    
    adGroupNGramSheet[n] = spreadsheet.getSheetByName(adGroupNGramName[n]);
    if (adGroupNGramSheet[n] == null) {
      adGroupNGramSheet[n] = spreadsheet.insertSheet(adGroupNGramName[n]);
    }
    
    totalNGramSheet[n] = spreadsheet.getSheetByName(totalNGramName[n]);
    if (totalNGramSheet[n] == null) {
      totalNGramSheet[n] = spreadsheet.insertSheet(totalNGramName[n]);
    }
  }
  
  var wordCountSheet = spreadsheet.getSheetByName("Word Count Analysis");
  if (wordCountSheet == null) {
    wordCountSheet = spreadsheet.insertSheet("Word Count Analysis");
  }
  
  // Write the output arrays to the spreadsheet
  for (var n=minNGramLength; n<maxNGramLength+1; n++) {
    var nGramName = n + "-Grams";
    if (n == 1) {
      nGramName = "Words";
    }
    
    writeOutput(outputs[n]['campaign'], formats[n]['campaign'], campaignNGramSheet[n], nGramName, "Campaign", filterText, clearSpreadsheet);
    writeOutput(outputs[n]['adgroup'], formats[n]['adgroup'], adGroupNGramSheet[n], nGramName, "Ad Group", filterText, clearSpreadsheet);
    writeOutput(outputs[n]['total'], formats[n]['total'], totalNGramSheet[n], nGramName, "Account", filterText, clearSpreadsheet);
  }
  
  writeOutput(wordLengthOutput, wordLengthFormat, wordCountSheet, "Word Count", "Account", filterText, clearSpreadsheet);
  
  Logger.log("Finished writing to spreadsheet.");
} // end main function


function writeOutput(outputArray, formatArray, sheet, nGramName, levelName, filterText, clearSpreadsheet) {
  for (var i=0;i<5;i++) {
    try {
      if (clearSpreadsheet) {
        sheet.clear();
      }
      
      if (nGramName == "Word Count") {
        sheet.getRange("R1C1").setValue("Analysis of Search Query Performance by Word Count");
      } else {
        sheet.getRange("R1C1").setValue("Analysis of " + nGramName + " in Search Query Report, By " + levelName);
      }
      
      sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue(filterText);
      
      var lastRow = sheet.getLastRow();
      
      if (formatArray.length == 0) {
        sheet.getRange("R" + (lastRow + 1) + "C1").setValue("No " + nGramName.toLowerCase() + " found within the thresholds.");
      } else {
        sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).setValues(outputArray);
        sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + formatArray[0].length).setNumberFormats(formatArray);
        
        var sortByColumns = [];
        if (outputArray[0][0] == "Campaign" || outputArray[0][0] == "Word count") {
          sortByColumns.push({column: 1, ascending: true});
        }
        if (outputArray[0][1] == "Ad Group") {
          sortByColumns.push({column: 2, ascending: true});
        }
        sortByColumns.push({column: outputArray[0].indexOf("Cost") + 1, ascending: false});
        sortByColumns.push({column: outputArray[0].indexOf("Impressions") + 1, ascending: false});
        sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).sort(sortByColumns);
      }
      
      break;
      
    } catch (e) {
      if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
        Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
        try {
          sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue("Not enough space to write the data - try again in an empty spreadsheet");
        } catch (e2) {
          Logger.log("Error writing 'not enough space' message: " + e2);
        }
        break;
      }
      
      if (i == 4) {
        Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
      }
    }
  }
}

Pasting the Script

The first step we need to do is take the code above and paste it into the script section of our Google Ads account. 

So go to tools & settings and under bulk actions choose the scripts option.

Next we need to click on the big plus sign, copy and paste the script into the script area and replace what is there, and then give it a name like “n-gram analysis.” 

Modifying the Script

In order to run the n-gram script, we will need to make a few small changes to some variables. This may sound daunting, but it isn’t. There are really only 2 changes that we need to make to get our script to run. 

  1. Change the start & end date.

The first thing we want to do is change the start date and the end date for our data analysis. The script does not have a default time frame, so you will need to input your own.

Generally, the longer the time frame you input the better it will be because you will have a larger data set. I recommend at least 3 months, but 6 months or 12 months are even better. I don’t think it’s necessary to go longer than that. 

  1. Add your Google Sheets URL

The only other change you need to make is to add your Google sheet URL so your data is sent to a URL. Let’s scroll down to line 48 in the code--you will see the code line to the left--and we’ll see the input for the variable. 

Now all we have to do is replace the placeholder with the editable Google Sheet URL. It is important to ensure that the sharing permissions are set to “anyone with the link can edit.”

Another important thing to remember is to only update the data within the quotation marks. If you remove the quotation marks from either the dates or the spreadsheet URL, the script will not work.

Click the save button at the bottom to update the information in the script. We’re almost ready to run!

Authorizing the Script

The final step before we are able to run the script is to authorize it. At the top of the screen you will see a yellow bar telling you that you need to authorize the script in order for it to run.

Click authorize.

You will be presented with the popup screen below, asking you to choose an account. Choose the account you’re using and continue.

On the next screen you will see Google telling you what the script is trying to access like in the screenshot below.

If you do not see both spreadsheets in Google Drive AND manage Google Adwords campaigns, that means you will need to do one more step. In either case, click allow.

If you only saw one bullet point for manage Google Adwords campaigns, you will get a prompt saying the authorization failed. 

If this happens, close the script, open it again, and try the authorization process again. I don’t know why this situation happens, but it is common and is almost always resolved after closing the script and then opening it back up and trying again.

Once you have successfully authorized the script, we’re ready to run!

Running The Script

The next step is running the script. This is easy. Just go to actions and choose run. 

Depending on the size of your account the script should take anywhere from 3 to 30 minutes to run. If you have a really big account with lots of data, go ahead and wait. 

You can see the status of your script by setting your date range to today in the script history

When it says complete, that means you’re ready to go through your data and start analyzing!

Analyzing the Data

Now that our script has run, You need to clean up your data and analyze it. While you can do your analysis from within Google Sheets, I prefer to work in Excel. So the first thing you will want to do is download the Google Sheet as an .xlsx file.

Cleaning up the data

There’s a little bit of data cleanup that you need to do to easily find n-grams that you will add to your negative keywords list.

First, you should delete the first sheet. This sheet contains no data. 

Next, you should go through each sheet and delete the first three rows of data. This data is unimportant.

Lastly, after deleting those rows,you need to turn each sheet of data into a table with headers. This allows you to sort through the data and find wasted ad spend easily. 

Zero Conversion Words

The first thing that I look at when clearing out waste is 1-grams that had zero conversions in the last year. Depending on the complexity of your account, you will want to do this either on a campaign basis or an account basis. So you can choose either the account word analysis tab or the campaign word analysis tab. 

So let’s go to the Cost / Conv. header and sort from largest to smallest. This will default to showing null at the top.

Every entry that has no cost per conversion are words that generated zero conversions at all. 

This is all waste. 

Go through these words and create a list of them. These are keywords that you will add to your negative keyword list. 

If you find keywords that are relevant to your business but have a low volume (10 clicks or less) you can leave them off your list. Sometimes a small sample size will result in zero conversions. 

High Cost per Conversion Words

Once you get past all your never-converting words, you’ll reach your very poorly converting words.

These are keywords that you should also be adding to your negative keyword list. If the Cost per Conversion is much higher than your target CPA, these are good options.

For this campaign the target CPA is $40. For the three words shown the CPAs are $310, $207, and $170. I will add these to the negative keyword list. 

Additional Removal

After adding the individual words that are wasted spend to your list, you need to repeat this process with 2-grams as well. 

Make sure that when you add these 2-grams to your list, you put them in quotation marks so that they are phrase matched. 

Updating Your Account

With your list of negative keywords compiled, you need to create a new negative keyword list in your Google Ads account and copy and paste all of these keywords over. 

I typically give the list a name like “N-gram Analysis Negatives” or something like that. If you already have negative keyword lists and would rather sort these terms into separate lists, that is also a good strategy. 

Add your negative keyword list to the campaigns you want to improve, and you’re done! 

After completing this process, you will likely see a big improvement in your campaign efficiency. Depending on your use of negative keywords historically, I have found that the improvement can range anywhere from 5% to 30%!

Ongoing N-gram Analysis

I also strongly recommend continuing to periodically run an n-gram analysis on your account. For our clients, we perform an n-gram analysis every quarter, which we do on top of our standard negative keyword optimization. 

If this is something that sounds too complex for you, get in touch with us, and we’ll be happy to perform an n-gram analysis for you!

Is your business making the most of PPC?

Online advertising is the largest and most important advertising channel in the world. It is also the most competitive. Professional advertising management will ensure that you maximize your ROI from online advertising.

© 2020 Taikun, Inc. All Rights Reserved.

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram