N-gram Search Terms Analyse

1/**
2 * N-gram Search Terms Analyse
3 *
4 * Splitst alle zoektermen van de afgelopen 30 en 90 dagen op in 1-, 2- en 3-grams en
5 * aggregeert per n-gram de impressies, klikken, kosten, conversies, CPA en
6 * conversieratio. Resultaten worden per n-gram-lengte naar een Google Sheet geschreven.
7 * N-grams met veel kosten en geen (of te dure) conversies worden gemarkeerd als
8 * kandidaat voor uitsluitingszoekwoorden. Het script wijzigt niets in het account.
9 *
10 * Versie:     1.3.0
11 * Frequentie: Wekelijks
12 * Niveau:     Account
13 * Licentie:   MIT
14 */
15
16// ===== CONFIGURATIE =====
17
18// URL van de Google Sheet waarin de resultaten komen (script moet schrijfrechten hebben).
19var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
20
21// Perioden die geanalyseerd worden. Per periode komen er drie tabbladen (1-, 2-, 3-gram).
22var DATE_RANGES = ['LAST_30_DAYS', 'LAST_90_DAYS'];
23
24// Alleen zoektermen uit campagnes waarvan de naam deze tekst bevat. Leeg = alle campagnes.
25var CAMPAIGN_NAME_CONTAINS = '';
26
27// Drempels voor het markeren van negatieve-kandidaten.
28var THRESHOLDS = {
29  minCostForNegative: 25,     // n-gram moet minimaal dit bedrag gekost hebben
30  maxConversions: 0,          // ... en hooguit dit aantal conversies hebben (0 = geen conversies)
31  maxCpa: 150,                // OF een CPA boven dit bedrag (alleen als er conversies zijn)
32  minImpressions: 20,         // negeer n-grams met minder impressies (te weinig data)
33  maxRowsPerSheet: 2000       // maximum aantal regels per tabblad (gesorteerd op kosten)
34};
35
36// Woorden die genegeerd worden bij het bouwen van n-grams (stopwoorden, NL + EN).
37var STOP_WORDS = ['de', 'het', 'een', 'en', 'of', 'in', 'op', 'voor', 'van', 'met', 'te', 'bij', 'naar',
38  'the', 'a', 'an', 'and', 'or', 'for', 'to', 'of', 'in', 'on', 'with'];
39
40// Zet op true om stopwoorden te verwijderen voordat n-grams gemaakt worden.
41var REMOVE_STOP_WORDS = false;
42
43// ===== EINDE CONFIGURATIE =====
44
45function main() {
46  try {
47    var account = AdsApp.currentAccount();
48    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
49    Logger.log('N-gram analyse voor ' + account.getName() + ' (' + account.getCustomerId() + ')');
50
51    DATE_RANGES.forEach(function (range) {
52      var terms = fetchSearchTerms(range);
53      Logger.log(range + ': ' + terms.length + ' zoektermen opgehaald.');
54
55      for (var n = 1; n <= 3; n++) {
56        var grams = buildNgrams(terms, n);
57        var rows = toSortedRows(grams);
58        var sheetName = range.replace('LAST_', '').replace('_DAYS', 'd') + ' - ' + n + '-gram';
59        writeSheet(ss, sheetName, rows, range, n);
60        Logger.log(sheetName + ': ' + rows.length + ' n-grams, waarvan ' +
61          rows.filter(function (r) { return r.negativeCandidate; }).length + ' negatieve kandidaten.');
62      }
63    });
64
65    writeReadme(ss);
66    Logger.log('Klaar. Resultaten: ' + SPREADSHEET_URL);
67  } catch (e) {
68    Logger.log('FOUT in ngram-search-terms: ' + e + (e.stack ? '\n' + e.stack : ''));
69    throw e;
70  }
71}
72
73/**
74 * Haalt zoektermen met statistieken op via search_term_view.
75 */
76function fetchSearchTerms(dateRange) {
77  var query =
78    'SELECT search_term_view.search_term, campaign.name, ' +
79    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value ' +
80    'FROM search_term_view ' +
81    'WHERE segments.date DURING ' + dateRange + ' ' +
82    'AND metrics.impressions > 0';
83
84  if (CAMPAIGN_NAME_CONTAINS) {
85    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
86  }
87
88  var rows = AdsApp.report(query).rows();
89  var terms = [];
90  while (rows.hasNext()) {
91    var row = rows.next();
92    terms.push({
93      term: String(row['search_term_view.search_term'] || '').toLowerCase(),
94      impressions: Number(row['metrics.impressions']),
95      clicks: Number(row['metrics.clicks']),
96      cost: Number(row['metrics.cost_micros']) / 1000000,
97      conversions: Number(row['metrics.conversions']),
98      conversionsValue: Number(row['metrics.conversions_value'])
99    });
100  }
101  return terms;
102}
103
104/**
105 * Bouwt n-grams van lengte n en aggregeert de statistieken per n-gram.
106 * Eén zoekterm telt per uniek n-gram maar één keer mee (dubbele n-grams binnen dezelfde term worden ontdubbeld).
107 */
108function buildNgrams(terms, n) {
109  var grams = {};
110  terms.forEach(function (t) {
111    var words = tokenize(t.term);
112    if (words.length < n) { return; }
113    var seen = {};
114    for (var i = 0; i <= words.length - n; i++) {
115      var gram = words.slice(i, i + n).join(' ');
116      if (seen[gram]) { continue; }
117      seen[gram] = true;
118      if (!grams[gram]) {
119        grams[gram] = { gram: gram, terms: 0, impressions: 0, clicks: 0, cost: 0, conversions: 0, conversionsValue: 0 };
120      }
121      var g = grams[gram];
122      g.terms += 1;
123      g.impressions += t.impressions;
124      g.clicks += t.clicks;
125      g.cost += t.cost;
126      g.conversions += t.conversions;
127      g.conversionsValue += t.conversionsValue;
128    }
129  });
130  return grams;
131}
132
133function tokenize(text) {
134  var cleaned = text.replace(/[^a-z0-9àáâäèéêëìíîïòóôöùúûüçñß\s\-]/g, ' ');
135  var words = cleaned.split(/\s+/).filter(function (w) { return w.length > 0; });
136  if (REMOVE_STOP_WORDS) {
137    words = words.filter(function (w) { return STOP_WORDS.indexOf(w) === -1; });
138  }
139  return words;
140}
141
142/**
143 * Zet de n-gram-map om in gesorteerde regels met afgeleide metrics en markering.
144 */
145function toSortedRows(grams) {
146  var rows = [];
147  Object.keys(grams).forEach(function (key) {
148    var g = grams[key];
149    if (g.impressions < THRESHOLDS.minImpressions) { return; }
150    g.ctr = g.impressions > 0 ? g.clicks / g.impressions : 0;
151    g.cpc = g.clicks > 0 ? g.cost / g.clicks : 0;
152    g.cpa = g.conversions > 0 ? g.cost / g.conversions : null;
153    g.convRate = g.clicks > 0 ? g.conversions / g.clicks : 0;
154    g.roas = g.cost > 0 ? g.conversionsValue / g.cost : 0;
155    g.negativeCandidate = isNegativeCandidate(g);
156    rows.push(g);
157  });
158  rows.sort(function (a, b) { return b.cost - a.cost; });
159  return rows.slice(0, THRESHOLDS.maxRowsPerSheet);
160}
161
162function isNegativeCandidate(g) {
163  if (g.cost < THRESHOLDS.minCostForNegative) { return false; }
164  if (g.conversions <= THRESHOLDS.maxConversions) { return true; }
165  return g.cpa !== null && g.cpa > THRESHOLDS.maxCpa;
166}
167
168/**
169 * Schrijft de regels naar een tabblad (bestaand tabblad wordt leeggemaakt).
170 */
171function writeSheet(ss, sheetName, rows, range, n) {
172  var sheet = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName);
173  sheet.clearContents();
174  sheet.clearFormats();
175
176  var header = ['N-gram', 'Aantal zoektermen', 'Impressies', 'Klikken', 'CTR', 'Kosten', 'Gem. CPC',
177    'Conversies', 'Conv. ratio', 'CPA', 'Conv. waarde', 'ROAS', 'Negatieve kandidaat'];
178  var values = [header];
179  rows.forEach(function (g) {
180    values.push([g.gram, g.terms, g.impressions, g.clicks, g.ctr, round(g.cost), round(g.cpc), round(g.conversions, 2),
181      g.convRate, g.cpa === null ? '' : round(g.cpa), round(g.conversionsValue), round(g.roas, 2),
182      g.negativeCandidate ? 'JA' : '']);
183  });
184
185  sheet.getRange(1, 1, values.length, header.length).setValues(values);
186  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
187  sheet.setFrozenRows(1);
188  if (values.length > 1) {
189    sheet.getRange(2, 5, values.length - 1, 1).setNumberFormat('0.00%');
190    sheet.getRange(2, 9, values.length - 1, 1).setNumberFormat('0.00%');
191    sheet.getRange(2, 6, values.length - 1, 2).setNumberFormat('#,##0.00');
192    sheet.getRange(2, 10, values.length - 1, 2).setNumberFormat('#,##0.00');
193    // Markeer negatieve kandidaten rood.
194    for (var i = 1; i < values.length; i++) {
195      if (values[i][12] === 'JA') {
196        sheet.getRange(i + 1, 1, 1, header.length).setBackground('#fdecea');
197      }
198    }
199  }
200  sheet.autoResizeColumns(1, header.length);
201  var note = 'Periode: ' + range + ' | ' + n + '-grams | bijgewerkt: ' + new Date();
202  sheet.getRange(1, header.length + 2).setValue(note);
203}
204
205function writeReadme(ss) {
206  var sheet = ss.getSheetByName('Uitleg') || ss.insertSheet('Uitleg', 0);
207  sheet.clearContents();
208  var lines = [
209    ['N-gram analyse van zoektermen'],
210    ['Laatste run', String(new Date())],
211    ['Account', AdsApp.currentAccount().getName() + ' (' + AdsApp.currentAccount().getCustomerId() + ')'],
212    [''],
213    ['Negatieve kandidaat', 'kosten >= ' + THRESHOLDS.minCostForNegative + ' en conversies <= ' + THRESHOLDS.maxConversions +
214      ', OF CPA > ' + THRESHOLDS.maxCpa],
215    ['Min. impressies', THRESHOLDS.minImpressions],
216    ['Tip', 'Bekijk vooral 1- en 2-grams met veel kosten en 0 conversies; controleer altijd handmatig voordat je een negatief zoekwoord toevoegt.']
217  ];
218  sheet.getRange(1, 1, lines.length, 2).setValues(lines.map(function (l) { return l.length === 1 ? [l[0], ''] : l; }));
219  sheet.getRange(1, 1).setFontWeight('bold');
220}
221
222function round(value, decimals) {
223  var d = decimals === undefined ? 2 : decimals;
224  var f = Math.pow(10, d);
225  return Math.round(Number(value) * f) / f;
226}
227