Google Ads N-Gram Skript

Was ist ein N-Gram?

Ein N-Gram ist eine Sequenz von N aufeinanderfolgenden Wörtern in einem Text. Je höher das N, desto länger wird die Wortfolge und desto spezifischer wird die Bedeutung.In dem gezeigten Beispiel wird der Suchbegriff „Golf Hose für Männer“ in N-Gram unterteilt.

2-Gram: Hier werden alle möglichen Kombinationen von 2 Wörtern aus dem Suchbegriff gebildet
- „Golf Hose“
- „für Männer“
- „Hose für“
3-Gram: Alle möglichen Kombinationen von 3 Wörtern:
- „Golf Hose für“
- „Hose für Männer“
4-Gram: Alle 4 Wörter zusammen bilden die einzige Kombination:
- „Golf Hose für Männer“

Vorteile N-Gram Skript bei Google Ads

Mit einem N-Gram-Skript kannst du in Google Ads eine Google-Sheet-Tabelle erstellen, die N-Grams nach ihrer Performance aufschlüsselt. Dadurch stößt du auf Wörter oder Phrasen, die im normalen Keyword-Check untergehen, zum Beispiel nutzlose Füllwörter ohne Conversions. Das kann darauf hinweisen, dass es viele Nischen-Suchanfragen gibt, die einzeln kaum Klicks liefern, in der Summe aber teure Streuverluste verursachen.Im Screenshot sind bspw. zwei 1-Gram zu erkennen, die höhere Kosten als Umsatz generiert haben.Es funktioniert nur für Suchanzeigen und Shopping Anzeigen, da nur hier auf Suchbegriffe angezeigt werden.

Anleitung N-Gram Skript installieren

  1. Neues leeres Google Sheet erstellen
  2. Für Google freigeben (Rechts oben auf "Freigeben" ➝ unter Allgemeiner Zugriff von "Eingeschränkt" auf "Jeder, der über den Link verfügt" stellen und "Mitbearbeiter" auswählen)
  3. Skript hier kopieren (weiter untern)
  4. In Google Ads unter Skripte ein neues Skript erstellen
  5. Zuvor erstelltes Google Sheets in Zeile 49 einfügen
  6. Zeitraum eingeben (einen Zeitraum wählen, der genug Daten zulässt, bspw. 180 Tage. Darauf achten, dass ein Zeitraum gewählt wird, in dem es keine Trackingprobleme gab)
    - Zeile 20 ➝ Startdatum hinzufügen, muss weiter zurück liegen als Enddatum
    - Zeile 21 ➝ Enddatum hinzufügen, bspw. Gestern (heute ist wenig sinnvoll, da noch nicht alle Conversion Daten in Google Ads eingelaufen sind)
  7. Weitere Einstellmöglichkeiten
    - currencySymbol: Hier wird das Währungssymbol für die Formatierung definiert, z. B. "€" für Euro.
    - campaignNameContains: Dieser Filter erlaubt es, nur bestimmte Kampagnen basierend auf einem enthaltenen Namensteil zu betrachten. Beispielsweise könnten nur Kampagnen mit „Brand“ oder „Shopping“ im Namen ausgewählt werden. Bleibt leer, wenn keine Filterung gewünscht ist.
    - campaignNameDoesNotContain: Dieser Filter schließt bestimmte Kampagnen basierend auf einem Namensteil aus. Auch hier könnte beispielsweise „Brand“ oder „Shopping“ ausgeschlossen werden.
    - ignorePausedCampaigns: Wenn true, werden nur aktive Kampagnen berücksichtigt. Wenn false, werden auch pausierte Kampagnen mit einbezogen, solange sie Impressionen hatten.
    - ignorePausedAdGroups: Ähnlich wie bei den Kampagnen; wenn true, werden nur aktive Anzeigengruppen betrachtet, ansonsten auch pausierte.
    - checkNegatives: Wenn true, werden Suchanfragen, die durch negative Keywords ausgeschlossen würden, entfernt.
    - minNGramLength und maxNGramLength: Diese Variablen legen die minimale und maximale Wortanzahl für die N-Gramme fest, die untersucht werden sollen. Wenn z. B. minNGramLength auf 1 und maxNGramLength auf 2 gesetzt ist, werden N-Gramme bestehend aus 1 und 2 Wörtern betrachtet.
    - clearSpreadsheet: Wenn true, wird die Google Sheet Tabelle vor dem Schreiben der neuen Daten geleert.
    - Die Thresholds geben an, wie viele Impr. etc. hineinbezogen werden sollen. Hier müssen ggf. die Werte angepasst werden, wenn das Skript zu lange lädt, weil zu viele Daten analysiert werden
  8. Wenn das Skript entsprechend ausgefüllt wurde, im nächsten Schritt im Skripteditor auf "Vorschau" klicken und dann anschließend auf "authorisieren"
  9. Nach dem Authorisieren erneut auf "Vorschau" klicken und unter "Protokolle" abwarten, ob Fehler einlaufen. Wenn alles richtig eingestellt wurde, schreibt das Skript beim Durchlauf die Daten in das Google Sheet und man muss kurz warten (je größer der Account auch mal etwas länger), bis das Skript durchgelaufen ist und die Meldung "Finished writing to spreadsheet." im Protokoll zu sehen ist
  10. Jetzt kann analysiert werden

Bekannte Fehler

  • Im Protokoll steht "Problem with the spreadsheet URL: make sure you've replaced the default with a valid spreadsheet URL." ➝ URL von dem selbst erstellen Google Sheet wurde nicht in Zeile 49 eingefügt
  • Es passiert nichts, Google Sheet bleibt leer ➝ häufig Startdatum und Enddatum vertauscht
  • Skript braucht zu lange ➝ Datumsspanne kürzer machen oder Thresholds nach oben anpassen. Es werden zu viele Daten analysiert und Google Ads Skripte kommen an ihre Grenzen

N-Gram kopieren

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

Lass uns sprechen

Ich lade Dich zu einem unverbindlichen Erstgespräch ein, um Dich und Euer Unternehmen kennenzulernen. Ich nehme mir die Zeit, Eure Herausforderungen zu verstehen und ehrlich zu bewerten, ob wir der passende Wachstumspartner für Euch sind.

Zusätzlich zeige ich Dir auf, warum unsere Spezialisierung auf umfangreiche Produktportfolios mit einem individuellen Betreuungsansatz den Unterschied macht.

Weil ich im E-Commerce mittlerweile seit über 7 Jahre auf Agentur- und Inhouse-Seite tätig bin, kenne ich die Herausforderungen aus beiden Blickwinkeln.

Sergej Saschenin
Gründer & Geschäftsführer

© 2025 - All Rights Reserved
Impressum | Datenschutzerklörung