Search Query Mining Tool script to calculate the performance of n-grams

Search Query Mining Tool.js

/**
*
* 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 = "2015-07-01";
  var endDate = "2015-07-30";
  // 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 = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
  // 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/YXXR-SPRXXDSHXXT-XRL-HXRX") {
    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 + "'");
      }
    }
  }
}