zorinatesc

nGram Google Ads Script

Jun 10th, 2021
578
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**
  2. *
  3. * Search Query Mining Tool
  4. *
  5. * This script calculates the contribution of each word or phrase found in the
  6. * search query report and outputs a report into a Google Doc spreadsheet.
  7. *
  8. * Version: 2.2
  9. * Updated 2015-09-17: replacing 'KeywordText' with 'Criteria'
  10. * Updated 2016-10-11: replacing 'ConvertedClicks' with 'Conversions'
  11. *
  12. **/
  13.  
  14. function main() {
  15.   //////////////////////////////////////////////////////////////////////////////
  16.   // Options
  17.  
  18.   var startDate = "2021-04-01";
  19.   var endDate = "2021-05-30";
  20.   // The start and end date of the date range for your search query data
  21.   // Format is yyyy-mm-dd
  22.  
  23.   var currencySymbol = "HRK";
  24.   // The currency symbol used for formatting. For example "£", "$" or "€".
  25.  
  26.   var campaignNameContains = "key";
  27.   // Use this if you only want to look at some campaigns
  28.   // such as campaigns with names containing 'Brand' or 'Shopping'.
  29.   // Leave as "" if not wanted.
  30.  
  31.   var campaignNameDoesNotContain = "Brand";
  32.   // Use this if you want to exclude some campaigns
  33.   // such as campaigns with names containing 'Brand' or 'Shopping'.
  34.   // Leave as "" if not wanted.
  35.  
  36.   var ignorePausedCampaigns = true;
  37.   // Set this to true to only look at currently active campaigns.
  38.   // Set to false to include campaigns that had impressions but are currently paused.
  39.  
  40.   var ignorePausedAdGroups = true;
  41.   // Set this to true to only look at currently active ad groups.
  42.   // Set to false to include ad groups that had impressions but are currently paused.
  43.  
  44.   var checkNegatives = true;
  45.   // Set this to true to remove queries that would be excluded by your negative keywords.
  46.  
  47.   var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/1RMrU_WQ5qVRd3PeL8AKeBstWK5ZWAWPzKlseECD3yJc/edit#gid=0";
  48.   // The URL of the Google Doc the results will be put into.
  49.  
  50.   var minNGramLength = 1;
  51.   var maxNGramLength = 5;
  52.   // The word length of phrases to be checked.
  53.   // For example if minNGramLength is 1 and maxNGramLength is 3,
  54.   // phrases made of 1, 2 and 3 words will be checked.
  55.   // Change both min and max to 1 to just look at single words.
  56.  
  57.   var clearSpreadsheet = true;
  58.  
  59.  
  60.   //////////////////////////////////////////////////////////////////////////////
  61.   // Thresholds
  62.  
  63.   var queryCountThreshold = 0;
  64.   var impressionThreshold = 10;
  65.   var clickThreshold = 0;
  66.   var costThreshold = 0;
  67.   var conversionThreshold = 0;
  68.   // Words will be ignored if their statistics are lower than any of these thresholds
  69.  
  70.  
  71.   //////////////////////////////////////////////////////////////////////////////
  72.   // Check the spreadsheet has been entered, and that it works
  73.   if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
  74.     Logger.log("Problem with the spreadsheet URL: make sure you've replaces the default with a valid spreadsheet URL.");
  75.     return;
  76.   }
  77.   try {
  78.     var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  79.   } catch (e) {
  80.     Logger.log("Problem with the spreadsheet URL: '" + e + "'");
  81.     return;
  82.   }
  83.  
  84.   // Get the IDs of the campaigns to look at
  85.   var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
  86.   var activeCampaignIds = [];
  87.   var whereStatements = "";
  88.  
  89.   if (campaignNameDoesNotContain != "") {
  90.     whereStatements += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain + "' ";
  91.   }
  92.   if (ignorePausedCampaigns) {
  93.     whereStatements += "AND CampaignStatus = ENABLED ";
  94.   } else {
  95.     whereStatements += "AND CampaignStatus IN ['ENABLED','PAUSED'] ";
  96.   }
  97.  
  98.   var campaignReport = AdWordsApp.report(
  99.     "SELECT CampaignName, CampaignId " +
  100.     "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
  101.     "WHERE CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
  102.     "AND Impressions > 0 " + whereStatements +
  103.     "DURING " + dateRange
  104.   );
  105.   var campaignRows = campaignReport.rows();
  106.   while (campaignRows.hasNext()) {
  107.     var campaignRow = campaignRows.next();
  108.     activeCampaignIds.push(campaignRow["CampaignId"]);
  109.   }//end while
  110.  
  111.   if (activeCampaignIds.length == 0) {
  112.     Logger.log("Could not find any campaigns with impressions and the specified options.");
  113.     return;
  114.   }
  115.  
  116.   var whereAdGroupStatus = "";
  117.   if (ignorePausedAdGroups) {
  118.     var whereAdGroupStatus = "AND AdGroupStatus = ENABLED ";
  119.   } else {
  120.     whereAdGroupStatus += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
  121.   }
  122.  
  123.  
  124.   //////////////////////////////////////////////////////////////////////////////
  125.   // Find the negative keywords
  126.   var negativesByGroup = [];
  127.   var negativesByCampaign = [];
  128.   var sharedSetData = [];
  129.   var sharedSetNames = [];
  130.   var sharedSetCampaigns = [];
  131.  
  132.   if (checkNegatives) {
  133.     // Gather ad group level negative keywords
  134.     var keywordReport = AdWordsApp.report(
  135.       "SELECT CampaignId, AdGroupId, Criteria, KeywordMatchType " +
  136.       "FROM   KEYWORDS_PERFORMANCE_REPORT " +
  137.       "WHERE Status = ENABLED AND IsNegative = TRUE " + whereAdGroupStatus +
  138.       "AND CampaignId IN [" + activeCampaignIds.join(",") + "] " +
  139.       "DURING " + dateRange
  140.       );
  141.    
  142.     var keywordRows = keywordReport.rows();
  143.     while (keywordRows.hasNext()) {
  144.       var keywordRow = keywordRows.next();
  145.      
  146.       if (negativesByGroup[keywordRow["AdGroupId"]] == undefined) {
  147.         negativesByGroup[keywordRow["AdGroupId"]] =
  148.           [[keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]];
  149.       } else {
  150.         negativesByGroup[keywordRow["AdGroupId"]].push([keywordRow["Criteria"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]);
  151.       }
  152.     }
  153.    
  154.     // Gather campaign level negative keywords
  155.     var campaignNegReport = AdWordsApp.report(
  156.       "SELECT CampaignId, Criteria, KeywordMatchType " +
  157.       "FROM   CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT " +
  158.       "WHERE  IsNegative = TRUE " +
  159.       "AND CampaignId IN [" + activeCampaignIds.join(",") + "]"
  160.     );
  161.     var campaignNegativeRows = campaignNegReport.rows();
  162.     while (campaignNegativeRows.hasNext()) {
  163.       var campaignNegativeRow = campaignNegativeRows.next();
  164.       if (negativesByCampaign[campaignNegativeRow["CampaignId"]] == undefined) {
  165.         negativesByCampaign[campaignNegativeRow["CampaignId"]] = [[campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]];
  166.       } else {
  167.         negativesByCampaign[campaignNegativeRow["CampaignId"]].push([campaignNegativeRow["Criteria"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]);
  168.       }
  169.     }
  170.    
  171.     // Find which campaigns use shared negative keyword sets
  172.     var campaignSharedReport = AdWordsApp.report(
  173.       "SELECT CampaignName, CampaignId, SharedSetName, SharedSetType, Status " +
  174.       "FROM   CAMPAIGN_SHARED_SET_REPORT " +
  175.       "WHERE SharedSetType = NEGATIVE_KEYWORDS " +
  176.       "AND CampaignId IN [" + activeCampaignIds.join(",") + "]");
  177.     var campaignSharedRows = campaignSharedReport.rows();
  178.     while (campaignSharedRows.hasNext()) {
  179.       var campaignSharedRow = campaignSharedRows.next();
  180.       if (sharedSetCampaigns[campaignSharedRow["SharedSetName"]] == undefined) {
  181.         sharedSetCampaigns[campaignSharedRow["SharedSetName"]] = [campaignSharedRow["CampaignId"]];
  182.       } else {
  183.         sharedSetCampaigns[campaignSharedRow["SharedSetName"]].push(campaignSharedRow["CampaignId"]);
  184.       }
  185.     }
  186.    
  187.     // Map the shared sets' IDs (used in the criteria report below)
  188.     // to their names (used in the campaign report above)
  189.     var sharedSetReport = AdWordsApp.report(
  190.       "SELECT Name, SharedSetId, MemberCount, ReferenceCount, Type " +
  191.       "FROM   SHARED_SET_REPORT " +
  192.       "WHERE ReferenceCount > 0 AND Type = NEGATIVE_KEYWORDS ");
  193.     var sharedSetRows = sharedSetReport.rows();
  194.     while (sharedSetRows.hasNext()) {
  195.       var sharedSetRow = sharedSetRows.next();
  196.       sharedSetNames[sharedSetRow["SharedSetId"]] = sharedSetRow["Name"];
  197.     }
  198.    
  199.     // Collect the negative keyword text from the sets,
  200.     // and record it as a campaign level negative in the campaigns that use the set
  201.     var sharedSetReport = AdWordsApp.report(
  202.       "SELECT SharedSetId, KeywordMatchType, Criteria " +
  203.       "FROM   SHARED_SET_CRITERIA_REPORT ");
  204.     var sharedSetRows = sharedSetReport.rows();
  205.     while (sharedSetRows.hasNext()) {
  206.       var sharedSetRow = sharedSetRows.next();
  207.       var setName = sharedSetNames[sharedSetRow["SharedSetId"]];
  208.       if (sharedSetCampaigns[setName] !== undefined) {
  209.         for (var i=0; i<sharedSetCampaigns[setName].length; i++) {
  210.           var campaignId = sharedSetCampaigns[setName][i];
  211.           if (negativesByCampaign[campaignId] == undefined) {
  212.             negativesByCampaign[campaignId] =
  213.               [[sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]];
  214.           } else {
  215.             negativesByCampaign[campaignId].push([sharedSetRow["Criteria"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]);
  216.           }
  217.         }
  218.       }
  219.     }
  220.    
  221.     Logger.log("Finished finding negative keywords");
  222.   }// end if
  223.  
  224.  
  225.   //////////////////////////////////////////////////////////////////////////////
  226.   // Define the statistics to download or calculate, and their formatting
  227.  
  228.   var statColumns = ["Clicks", "Impressions", "Cost", "Conversions", "ConversionValue"];
  229.   var calculatedStats = [["CTR","Clicks","Impressions"],
  230.                          ["CPC","Cost","Clicks"],
  231.                          ["Conv. Rate","Conversions","Clicks"],
  232.                          ["Cost / conv.","Cost","Conversions"],
  233.                          ["Conv. value/cost","ConversionValue","Cost"]]
  234.   var currencyFormat = currencySymbol + "#,##0.00";
  235.   var formatting = ["#,##0", "#,##0", "#,##0", currencyFormat, "#,##0", currencyFormat,"0.00%",currencyFormat,"0.00%",currencyFormat,"0.00%"];
  236.  
  237.  
  238.   //////////////////////////////////////////////////////////////////////////////
  239.   // Go through the search query report, remove searches already excluded by negatives
  240.   // record the performance of each word in each remaining query
  241.  
  242.   var queryReport = AdWordsApp.report(
  243.     "SELECT CampaignName, CampaignId, AdGroupId, AdGroupName, Query, " + statColumns.join(", ") + " " +
  244.     "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
  245.       "WHERE CampaignId IN [" + activeCampaignIds.join(",") + "] " + whereAdGroupStatus +
  246.         "DURING " + dateRange);
  247.  
  248.   var numberOfWords = [];
  249.   var campaignNGrams = {};
  250.   var adGroupNGrams = {};
  251.   var totalNGrams = [];
  252.  
  253.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  254.     totalNGrams[n] = {};
  255.   }
  256.  
  257.   var queryRows = queryReport.rows();
  258.   while (queryRows.hasNext()) {
  259.     var queryRow = queryRows.next();
  260.    
  261.     if (checkNegatives) {
  262.       var searchIsExcluded = false;
  263.      
  264.       // Checks if the query is excluded by an ad group level negative
  265.       if (negativesByGroup[queryRow["AdGroupId"]] !== undefined) {
  266.         for (var i=0; i<negativesByGroup[queryRow["AdGroupId"]].length; i++) {
  267.           if ( (negativesByGroup[queryRow["AdGroupId"]][i][1] == "exact" &&
  268.                 queryRow["Query"] == negativesByGroup[queryRow["AdGroupId"]][i][0]) ||
  269.               (negativesByGroup[queryRow["AdGroupId"]][i][1] != "exact" &&
  270.               (" "+queryRow["Query"]+" ").indexOf(" "+negativesByGroup[queryRow["AdGroupId"]][i][0]+" ") > -1 )){
  271.             searchIsExcluded = true;
  272.             break;
  273.           }
  274.         }
  275.       }
  276.      
  277.       // Checks if the query is excluded by a campaign level negative
  278.       if (!searchIsExcluded && negativesByCampaign[queryRow["CampaignId"]] !== undefined) {
  279.         for (var i=0; i<negativesByCampaign[queryRow["CampaignId"]].length; i++) {
  280.           if ( (negativesByCampaign[queryRow["CampaignId"]][i][1] == "exact" &&
  281.                 queryRow["Query"] == negativesByCampaign[queryRow["CampaignId"]][i][0]) ||
  282.               (negativesByCampaign[queryRow["CampaignId"]][i][1]!= "exact" &&
  283.               (" "+queryRow["Query"]+" ").indexOf(" "+negativesByCampaign[queryRow["CampaignId"]][i][0]+" ") > -1 )){
  284.             searchIsExcluded = true;
  285.             break;
  286.           }
  287.         }
  288.       }
  289.      
  290.       if (searchIsExcluded) {continue;}
  291.     }
  292.    
  293.     var currentWords = queryRow["Query"].split(" ");
  294.    
  295.     if (campaignNGrams[queryRow["CampaignName"]] == undefined) {
  296.       campaignNGrams[queryRow["CampaignName"]] = [];
  297.       adGroupNGrams[queryRow["CampaignName"]] = {};
  298.      
  299.       for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  300.         campaignNGrams[queryRow["CampaignName"]][n] = {};
  301.       }
  302.     }
  303.    
  304.     if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] == undefined) {
  305.       adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]] = [];
  306.       for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  307.         adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n] = {};
  308.       }
  309.     }
  310.    
  311.     var stats = [];
  312.     for (var i=0; i<statColumns.length; i++) {
  313.       stats[i] = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
  314.     }
  315.    
  316.     var wordLength = currentWords.length;
  317.     if (wordLength > 6) {
  318.       wordLength = "7+";
  319.     }
  320.     if (numberOfWords[wordLength] == undefined) {
  321.       numberOfWords[wordLength] = [];
  322.     }
  323.     for (var i=0; i<statColumns.length; i++) {
  324.       if (numberOfWords[wordLength][statColumns[i]] > 0) {
  325.         numberOfWords[wordLength][statColumns[i]] += stats[i];
  326.       } else {
  327.         numberOfWords[wordLength][statColumns[i]] = stats[i];
  328.       }
  329.     }
  330.    
  331.     // Splits the query into n-grams and records the stats for each
  332.     for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  333.       if (n > currentWords.length) {
  334.         break;
  335.       }
  336.      
  337.       var doneNGrams = [];
  338.      
  339.       for (var w=0; w < currentWords.length - n + 1; w++) {
  340.         var currentNGram = '="' + currentWords.slice(w,w+n).join(" ") + '"';
  341.        
  342.         if (doneNGrams.indexOf(currentNGram) < 0) {
  343.          
  344.           if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram] == undefined) {
  345.             campaignNGrams[queryRow["CampaignName"]][n][currentNGram] = {};
  346.             campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"] = 0;
  347.           }
  348.           if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] == undefined) {
  349.             adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] = {};
  350.             adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"] = 0;
  351.           }
  352.           if (totalNGrams[n][currentNGram] == undefined) {
  353.             totalNGrams[n][currentNGram] = {};
  354.             totalNGrams[n][currentNGram]["Query Count"] = 0;
  355.           }
  356.          
  357.           campaignNGrams[queryRow["CampaignName"]][n][currentNGram]["Query Count"]++;
  358.           adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"]++;
  359.           totalNGrams[n][currentNGram]["Query Count"]++;
  360.          
  361.           for (var i=0; i<statColumns.length; i++) {
  362.             if (campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] > 0) {
  363.               campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] += stats[i];
  364.             } else {
  365.               campaignNGrams[queryRow["CampaignName"]][n][currentNGram][statColumns[i]] = stats[i];
  366.             }
  367.            
  368.             if (adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] > 0) {
  369.               adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] += stats[i];
  370.             } else {
  371.               adGroupNGrams[queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] = stats[i];
  372.             }
  373.            
  374.             if (totalNGrams[n][currentNGram][statColumns[i]] > 0) {
  375.               totalNGrams[n][currentNGram][statColumns[i]] += stats[i];
  376.             } else {
  377.               totalNGrams[n][currentNGram][statColumns[i]] = stats[i];
  378.             }
  379.           }
  380.          
  381.           doneNGrams.push(currentNGram);
  382.         }
  383.       }
  384.     }
  385.   }
  386.  
  387.   Logger.log("Finished analysing queries.");
  388.  
  389.  
  390.   //////////////////////////////////////////////////////////////////////////////
  391.   // Output the data into the spreadsheet
  392.  
  393.   var wordLengthOutput = [];
  394.   var wordLengthFormat = [];
  395.   var outputs = [];
  396.   var formats = [];
  397.  
  398.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  399.     outputs[n] = {};
  400.     outputs[n]['campaign'] = [];
  401.     outputs[n]['adgroup'] = [];
  402.     outputs[n]['total'] = [];
  403.     formats[n] = {};
  404.     formats[n]['campaign'] = [];
  405.     formats[n]['adgroup'] = [];
  406.     formats[n]['total'] = [];
  407.   }
  408.  
  409.   // Create headers
  410.   var calcStatNames = [];
  411.   for (var s=0; s<calculatedStats.length; s++) {
  412.     calcStatNames.push(calculatedStats[s][0]);
  413.   }
  414.   var statNames = statColumns.concat(calcStatNames);
  415.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  416.     outputs[n]['campaign'].push(["Campaign","Phrase","Query Count"].concat(statNames));
  417.     outputs[n]['adgroup'].push(["Campaign","Ad Group","Phrase","Query Count"].concat(statNames));
  418.     outputs[n]['total'].push(["Phrase","Query Count"].concat(statNames));
  419.   }
  420.   wordLengthOutput.push(["Word count"].concat(statNames));
  421.  
  422.   // Organise the ad group level stats into an array for output
  423.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  424.     for (var campaign in adGroupNGrams) {
  425.       for (var adGroup in adGroupNGrams[campaign]) {
  426.         for (var nGram in adGroupNGrams[campaign][adGroup][n]) {
  427.          
  428.           // skips nGrams under the thresholds
  429.           if (adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
  430.           if (adGroupNGrams[campaign][adGroup][n][nGram]["Impressions"] < impressionThreshold) {continue;}
  431.           if (adGroupNGrams[campaign][adGroup][n][nGram]["Clicks"] < clickThreshold) {continue;}
  432.           if (adGroupNGrams[campaign][adGroup][n][nGram]["Cost"] < costThreshold) {continue;}
  433.           if (adGroupNGrams[campaign][adGroup][n][nGram]["Conversions"] < conversionThreshold) {continue;}
  434.          
  435.           var printline = [campaign, adGroup, nGram, adGroupNGrams[campaign][adGroup][n][nGram]["Query Count"]];
  436.          
  437.           for (var s=0; s<statColumns.length; s++) {
  438.             printline.push(adGroupNGrams[campaign][adGroup][n][nGram][statColumns[s]]);
  439.           }
  440.          
  441.           for (var s=0; s<calculatedStats.length; s++) {
  442.             var multiplier = calculatedStats[s][1];
  443.             var divisor = calculatedStats[s][2];
  444.             if (adGroupNGrams[campaign][adGroup][n][nGram][divisor] > 0) {
  445.               printline.push(adGroupNGrams[campaign][adGroup][n][nGram][multiplier] / adGroupNGrams[campaign][adGroup][n][nGram][divisor]);
  446.             } else {
  447.               printline.push("-");
  448.             }
  449.           }
  450.           outputs[n]['adgroup'].push(printline);
  451.           formats[n]['adgroup'].push(["0","0","0"].concat(formatting));
  452.         }
  453.       }
  454.     }
  455.   }
  456.  
  457.   // Organise the campaign level stats into an array for output
  458.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  459.     for (var campaign in campaignNGrams) {
  460.       for (var nGram in campaignNGrams[campaign][n]) {
  461.        
  462.         // skips nGrams under the thresholds
  463.         if (campaignNGrams[campaign][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
  464.         if (campaignNGrams[campaign][n][nGram]["Impressions"] < impressionThreshold) {continue;}
  465.         if (campaignNGrams[campaign][n][nGram]["Clicks"] < clickThreshold) {continue;}
  466.         if (campaignNGrams[campaign][n][nGram]["Cost"] < costThreshold) {continue;}
  467.         if (campaignNGrams[campaign][n][nGram]["Conversions"] < conversionThreshold) {continue;}
  468.        
  469.         var printline = [campaign, nGram, campaignNGrams[campaign][n][nGram]["Query Count"]];
  470.        
  471.         for (var s=0; s<statColumns.length; s++) {
  472.           printline.push(campaignNGrams[campaign][n][nGram][statColumns[s]]);
  473.         }
  474.        
  475.         for (var s=0; s<calculatedStats.length; s++) {
  476.           var multiplier = calculatedStats[s][1];
  477.           var divisor = calculatedStats[s][2];
  478.           if (campaignNGrams[campaign][n][nGram][divisor] > 0) {
  479.             printline.push(campaignNGrams[campaign][n][nGram][multiplier] / campaignNGrams[campaign][n][nGram][divisor]);
  480.           } else {
  481.             printline.push("-");
  482.           }
  483.         }
  484.         outputs[n]['campaign'].push(printline);
  485.         formats[n]['campaign'].push(["0","0"].concat(formatting));
  486.       }
  487.     }
  488.   }
  489.  
  490.   // Organise the account level stats into an array for output
  491.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  492.     for (var nGram in totalNGrams[n]) {
  493.      
  494.       // skips n-grams under the thresholds
  495.       if (totalNGrams[n][nGram]["Query Count"] < queryCountThreshold) {continue;}
  496.       if (totalNGrams[n][nGram]["Impressions"] < impressionThreshold) {continue;}
  497.       if (totalNGrams[n][nGram]["Clicks"] < clickThreshold) {continue;}
  498.       if (totalNGrams[n][nGram]["Cost"] < costThreshold) {continue;}
  499.       if (totalNGrams[n][nGram]["Conversions"] < conversionThreshold) {continue;}
  500.      
  501.       var printline = [nGram, totalNGrams[n][nGram]["Query Count"]];
  502.      
  503.       for (var s=0; s<statColumns.length; s++) {
  504.         printline.push(totalNGrams[n][nGram][statColumns[s]]);
  505.       }
  506.      
  507.       for (var s=0; s<calculatedStats.length; s++) {
  508.         var multiplier = calculatedStats[s][1];
  509.         var divisor = calculatedStats[s][2];
  510.         if (totalNGrams[n][nGram][divisor] > 0) {
  511.           printline.push(totalNGrams[n][nGram][multiplier] / totalNGrams[n][nGram][divisor]);
  512.         } else {
  513.           printline.push("-");
  514.         }
  515.       }
  516.       outputs[n]['total'].push(printline);
  517.       formats[n]['total'].push(["0"].concat(formatting));
  518.     }
  519.   }
  520.  
  521.   // Organise the word count analysis into an array for output
  522.   for (var i = 1; i<8; i++) {
  523.     if (i < 7) {
  524.       var wordLength = i;
  525.     } else {
  526.       var wordLength = "7+";
  527.     }
  528.    
  529.     var printline = [wordLength];
  530.    
  531.     if (numberOfWords[wordLength] == undefined) {
  532.       printline.push([0,0,0,0,"-","-","-","-"]);
  533.     } else {
  534.       for (var s=0; s<statColumns.length; s++) {
  535.         printline.push(numberOfWords[wordLength][statColumns[s]]);
  536.       }
  537.      
  538.       for (var s=0; s<calculatedStats.length; s++) {
  539.         var multiplier = calculatedStats[s][1];
  540.         var divisor = calculatedStats[s][2];
  541.         if (numberOfWords[wordLength][divisor] > 0) {
  542.           printline.push(numberOfWords[wordLength][multiplier] / numberOfWords[wordLength][divisor]);
  543.         } else {
  544.           printline.push("-");
  545.         }
  546.       }
  547.     }
  548.     wordLengthOutput.push(printline);
  549.     wordLengthFormat.push(formatting);
  550.   }
  551.  
  552.   var filterText = "";
  553.   if (ignorePausedAdGroups) {
  554.     filterText = "Active ad groups";
  555.   } else {
  556.     filterText = "All ad groups";
  557.   }
  558.  
  559.   if (ignorePausedCampaigns) {
  560.     filterText += " in active campaigns";
  561.   } else {
  562.     filterText += " in all campaigns";
  563.   }
  564.  
  565.   if (campaignNameContains != "") {
  566.     filterText += " containing '" + campaignNameContains + "'";
  567.     if (campaignNameDoesNotContain != "") {
  568.       filterText += " and not containing '" + campaignNameDoesNotContain + "'";
  569.     }
  570.   } else if (campaignNameDoesNotContain != "") {
  571.     filterText += " not containing '" + campaignNameDoesNotContain + "'";
  572.   }
  573.  
  574.   // Find or create the required sheets
  575.   var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  576.   var campaignNGramName = [];
  577.   var adGroupNGramName = [];
  578.   var totalNGramName = [];
  579.   var campaignNGramSheet = [];
  580.   var adGroupNGramSheet = [];
  581.   var totalNGramSheet = [];
  582.  
  583.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  584.     if (n==1) {
  585.       campaignNGramName[n] = "Campaign Word Analysis";
  586.       adGroupNGramName[n] = "Ad Group Word Analysis";
  587.       totalNGramName[n] = "Account Word Analysis";    
  588.     } else {
  589.       campaignNGramName[n] = "Campaign " + n + "-Gram Analysis";
  590.       adGroupNGramName[n] = "Ad Group " + n + "-Gram Analysis";
  591.       totalNGramName[n] = "Account " + n + "-Gram Analysis";
  592.     }
  593.    
  594.     campaignNGramSheet[n] = spreadsheet.getSheetByName(campaignNGramName[n]);
  595.     if (campaignNGramSheet[n] == null) {
  596.       campaignNGramSheet[n] = spreadsheet.insertSheet(campaignNGramName[n]);
  597.     }
  598.    
  599.     adGroupNGramSheet[n] = spreadsheet.getSheetByName(adGroupNGramName[n]);
  600.     if (adGroupNGramSheet[n] == null) {
  601.       adGroupNGramSheet[n] = spreadsheet.insertSheet(adGroupNGramName[n]);
  602.     }
  603.    
  604.     totalNGramSheet[n] = spreadsheet.getSheetByName(totalNGramName[n]);
  605.     if (totalNGramSheet[n] == null) {
  606.       totalNGramSheet[n] = spreadsheet.insertSheet(totalNGramName[n]);
  607.     }
  608.   }
  609.  
  610.   var wordCountSheet = spreadsheet.getSheetByName("Word Count Analysis");
  611.   if (wordCountSheet == null) {
  612.     wordCountSheet = spreadsheet.insertSheet("Word Count Analysis");
  613.   }
  614.  
  615.   // Write the output arrays to the spreadsheet
  616.   for (var n=minNGramLength; n<maxNGramLength+1; n++) {
  617.     var nGramName = n + "-Grams";
  618.     if (n == 1) {
  619.       nGramName = "Words";
  620.     }
  621.    
  622.     writeOutput(outputs[n]['campaign'], formats[n]['campaign'], campaignNGramSheet[n], nGramName, "Campaign", filterText, clearSpreadsheet);
  623.     writeOutput(outputs[n]['adgroup'], formats[n]['adgroup'], adGroupNGramSheet[n], nGramName, "Ad Group", filterText, clearSpreadsheet);
  624.     writeOutput(outputs[n]['total'], formats[n]['total'], totalNGramSheet[n], nGramName, "Account", filterText, clearSpreadsheet);
  625.   }
  626.  
  627.   writeOutput(wordLengthOutput, wordLengthFormat, wordCountSheet, "Word Count", "Account", filterText, clearSpreadsheet);
  628.  
  629.   Logger.log("Finished writing to spreadsheet.");
  630. } // end main function
  631.  
  632.  
  633. function writeOutput(outputArray, formatArray, sheet, nGramName, levelName, filterText, clearSpreadsheet) {
  634.   for (var i=0;i<5;i++) {
  635.     try {
  636.       if (clearSpreadsheet) {
  637.         sheet.clear();
  638.       }
  639.      
  640.       if (nGramName == "Word Count") {
  641.         sheet.getRange("R1C1").setValue("Analysis of Search Query Performance by Word Count");
  642.       } else {
  643.         sheet.getRange("R1C1").setValue("Analysis of " + nGramName + " in Search Query Report, By " + levelName);
  644.       }
  645.      
  646.       sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue(filterText);
  647.      
  648.       var lastRow = sheet.getLastRow();
  649.      
  650.       if (formatArray.length == 0) {
  651.         sheet.getRange("R" + (lastRow + 1) + "C1").setValue("No " + nGramName.toLowerCase() + " found within the thresholds.");
  652.       } else {
  653.         sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).setValues(outputArray);
  654.         sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + formatArray[0].length).setNumberFormats(formatArray);
  655.        
  656.         var sortByColumns = [];
  657.         if (outputArray[0][0] == "Campaign" || outputArray[0][0] == "Word count") {
  658.           sortByColumns.push({column: 1, ascending: true});
  659.         }
  660.         if (outputArray[0][1] == "Ad Group") {
  661.           sortByColumns.push({column: 2, ascending: true});
  662.         }
  663.         sortByColumns.push({column: outputArray[0].indexOf("Cost") + 1, ascending: false});
  664.         sortByColumns.push({column: outputArray[0].indexOf("Impressions") + 1, ascending: false});
  665.         sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).sort(sortByColumns);
  666.       }
  667.      
  668.       break;
  669.      
  670.     } catch (e) {
  671.       if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
  672.         Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
  673.         try {
  674.           sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue("Not enough space to write the data - try again in an empty spreadsheet");
  675.         } catch (e2) {
  676.           Logger.log("Error writing 'not enough space' message: " + e2);
  677.         }
  678.         break;
  679.       }
  680.      
  681.       if (i == 4) {
  682.         Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
  683.       }
  684.     }
  685.   }
  686. }
RAW Paste Data