Google Ads N-Gram Skript
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
- Neues leeres Google Sheet erstellen
- 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)
- Skript hier kopieren (weiter untern)
- In Google Ads unter Skripte ein neues Skript erstellen
- Zuvor erstelltes Google Sheets in Zeile 49 einfügen
- 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) - 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 - Wenn das Skript entsprechend ausgefüllt wurde, im nächsten Schritt im Skripteditor auf "Vorschau" klicken und dann anschließend auf "authorisieren"
- 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
- 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}
691Unverbindliches 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
Kontakt
anocus GmbH
Hamburg Business Center
Poststraße 33
20354 Hamburg
📞 01522 7749017
✉️ info@anocus.de
Hamburg Business Center
Poststraße 33
20354 Hamburg
📞 01522 7749017
✉️ info@anocus.de
