Search Terms Negative Suggester

1/**
2 * Search Terms Negative Suggester
3 *
4 * Verzamelt zoektermen die geld kosten zonder te converteren (of met een te hoge CPA)
5 * en schrijft ze als voorstel naar een Google Sheet. Optioneel worden de voorstellen
6 * direct als exact-match uitsluitingszoekwoord toegevoegd aan een gedeelde negatieve
7 * zoekwoordenlijst. Standaard staat DRY_RUN aan: er wordt dan niets gewijzigd.
8 *
9 * Versie:     1.4.0
10 * Frequentie: Wekelijks
11 * Niveau:     Account
12 * Licentie:   MIT
13 */
14
15// ===== CONFIGURATIE =====
16
17// Zolang DRY_RUN true is worden voorstellen alleen gelogd en naar de Sheet geschreven.
18var DRY_RUN = true;
19
20// Google Sheet voor het overzicht van voorstellen.
21var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
22var SHEET_NAME = 'Negatieve voorstellen';
23
24// Periode waarover zoektermen geanalyseerd worden.
25var DATE_RANGE = 'LAST_30_DAYS';
26
27// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle campagnes.
28var CAMPAIGN_NAME_CONTAINS = '';
29
30// Drempels om een zoekterm als negatief voor te stellen.
31var THRESHOLDS = {
32  minCost: 30,          // kosten in de periode minimaal dit bedrag ...
33  maxConversions: 0,    // ... met hooguit dit aantal conversies
34  maxCpa: 200,          // OF een CPA boven dit bedrag (als er wel conversies zijn)
35  minClicks: 5,         // en minimaal dit aantal klikken (tegen ruis)
36  maxSuggestions: 200   // maximum aantal toe te voegen negatieven per run
37};
38
39// Naam van de gedeelde negatieve zoekwoordenlijst. Wordt aangemaakt als hij niet bestaat.
40var NEGATIVE_LIST_NAME = 'Script - Auto negatieven';
41
42// Koppel de lijst automatisch aan alle campagnes waaruit de zoekterm komt.
43var ATTACH_LIST_TO_CAMPAIGNS = true;
44
45// Zoektermen die deze woorden bevatten worden NOOIT voorgesteld (bv. merknaam).
46var PROTECTED_WORDS = ['jouwmerk'];
47
48// Optioneel e-mailoverzicht na afloop. Leeg = geen e-mail.
49var EMAIL_RECIPIENTS = '';
50
51// ===== EINDE CONFIGURATIE =====
52
53function main() {
54  try {
55    var account = AdsApp.currentAccount();
56    var preview = AdsApp.getExecutionInfo().isPreview();
57    var effectiveDryRun = DRY_RUN || preview;
58    Logger.log('Negative suggester voor ' + account.getName() + ' | DRY_RUN=' + effectiveDryRun +
59      (preview ? ' (preview-modus)' : ''));
60
61    var candidates = fetchCandidates();
62    Logger.log(candidates.length + ' kandidaten gevonden.');
63
64    var existing = loadExistingNegatives();
65    var suggestions = candidates.filter(function (c) {
66      return !existing[c.term] && !isProtected(c.term);
67    }).slice(0, THRESHOLDS.maxSuggestions);
68    Logger.log(suggestions.length + ' nieuwe voorstellen na filteren op bestaande negatieven en beschermde woorden.');
69
70    writeSheet(suggestions, effectiveDryRun);
71
72    if (suggestions.length > 0 && !effectiveDryRun) {
73      addToNegativeList(suggestions);
74    } else if (suggestions.length > 0) {
75      Logger.log('DRY_RUN: er zijn geen negatieven toegevoegd. Zet DRY_RUN op false om toe te voegen.');
76    }
77
78    sendEmail(account, suggestions, effectiveDryRun);
79  } catch (e) {
80    Logger.log('FOUT in search-terms-negative-suggester: ' + e + (e.stack ? '\n' + e.stack : ''));
81    throw e;
82  }
83}
84
85/**
86 * Haalt zoektermen op die aan de kostendrempel voldoen en bepaalt of ze kandidaat zijn.
87 */
88function fetchCandidates() {
89  var query =
90    'SELECT search_term_view.search_term, search_term_view.status, campaign.id, campaign.name, ad_group.name, ' +
91    'metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.impressions ' +
92    'FROM search_term_view ' +
93    'WHERE segments.date DURING ' + DATE_RANGE + ' ' +
94    'AND metrics.cost_micros >= ' + Math.round(THRESHOLDS.minCost * 1000000) + ' ' +
95    'AND metrics.clicks >= ' + THRESHOLDS.minClicks;
96  if (CAMPAIGN_NAME_CONTAINS) {
97    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
98  }
99  query += ' ORDER BY metrics.cost_micros DESC';
100
101  var byTerm = {};
102  var rows = AdsApp.report(query).rows();
103  while (rows.hasNext()) {
104    var row = rows.next();
105    var term = String(row['search_term_view.search_term'] || '').toLowerCase().trim();
106    if (!term) { continue; }
107    // Reeds uitgesloten zoektermen (status EXCLUDED) overslaan.
108    if (row['search_term_view.status'] === 'EXCLUDED') { continue; }
109
110    if (!byTerm[term]) {
111      byTerm[term] = { term: term, cost: 0, clicks: 0, conversions: 0, impressions: 0, campaigns: {}, campaignIds: {} };
112    }
113    var t = byTerm[term];
114    t.cost += Number(row['metrics.cost_micros']) / 1000000;
115    t.clicks += Number(row['metrics.clicks']);
116    t.conversions += Number(row['metrics.conversions']);
117    t.impressions += Number(row['metrics.impressions']);
118    t.campaigns[row['campaign.name']] = true;
119    t.campaignIds[row['campaign.id']] = true;
120  }
121
122  var candidates = [];
123  Object.keys(byTerm).forEach(function (key) {
124    var t = byTerm[key];
125    t.cpa = t.conversions > 0 ? t.cost / t.conversions : null;
126    var noConversions = t.conversions <= THRESHOLDS.maxConversions;
127    var tooExpensive = t.cpa !== null && t.cpa > THRESHOLDS.maxCpa;
128    if (t.cost >= THRESHOLDS.minCost && (noConversions || tooExpensive)) {
129      t.reason = noConversions ? 'Geen conversies' : 'CPA te hoog (' + t.cpa.toFixed(2) + ')';
130      candidates.push(t);
131    }
132  });
133  candidates.sort(function (a, b) { return b.cost - a.cost; });
134  return candidates;
135}
136
137/**
138 * Laadt de bestaande negatieven uit de gedeelde lijst zodat we geen dubbelen toevoegen.
139 */
140function loadExistingNegatives() {
141  var existing = {};
142  var lists = AdsApp.negativeKeywordLists()
143    .withCondition("shared_set.name = '" + NEGATIVE_LIST_NAME.replace(/'/g, "\\'") + "'")
144    .get();
145  if (!lists.hasNext()) { return existing; }
146  var list = lists.next();
147  var negatives = list.negativeKeywords().get();
148  while (negatives.hasNext()) {
149    var neg = negatives.next();
150    existing[normalizeKeyword(neg.getText())] = true;
151  }
152  Logger.log(Object.keys(existing).length + ' bestaande negatieven in lijst "' + NEGATIVE_LIST_NAME + '".');
153  return existing;
154}
155
156function normalizeKeyword(text) {
157  return String(text).toLowerCase().replace(/^\[|\]$/g, '').replace(/^"|"$/g, '').trim();
158}
159
160function isProtected(term) {
161  for (var i = 0; i < PROTECTED_WORDS.length; i++) {
162    var w = String(PROTECTED_WORDS[i]).toLowerCase().trim();
163    if (w && term.indexOf(w) !== -1) { return true; }
164  }
165  return false;
166}
167
168/**
169 * Voegt de voorstellen als exact-match negatieven toe aan de gedeelde lijst.
170 */
171function addToNegativeList(suggestions) {
172  var list = getOrCreateNegativeList();
173  var added = 0;
174  var campaignIds = {};
175  suggestions.forEach(function (s) {
176    try {
177      list.addNegativeKeyword('[' + s.term + ']');
178      added++;
179      Object.keys(s.campaignIds).forEach(function (id) { campaignIds[id] = true; });
180    } catch (e) {
181      Logger.log('Kon "' + s.term + '" niet toevoegen: ' + e);
182    }
183  });
184  Logger.log(added + ' negatieven toegevoegd aan "' + NEGATIVE_LIST_NAME + '".');
185
186  if (ATTACH_LIST_TO_CAMPAIGNS) {
187    attachListToCampaigns(list, Object.keys(campaignIds));
188  }
189}
190
191function getOrCreateNegativeList() {
192  var lists = AdsApp.negativeKeywordLists()
193    .withCondition("shared_set.name = '" + NEGATIVE_LIST_NAME.replace(/'/g, "\\'") + "'")
194    .get();
195  if (lists.hasNext()) { return lists.next(); }
196  var operation = AdsApp.newNegativeKeywordListBuilder().withName(NEGATIVE_LIST_NAME).build();
197  if (!operation.isSuccessful()) {
198    throw new Error('Kon negatieve lijst niet aanmaken: ' + operation.getErrors().join(', '));
199  }
200  Logger.log('Negatieve lijst "' + NEGATIVE_LIST_NAME + '" aangemaakt.');
201  return operation.getResult();
202}
203
204function attachListToCampaigns(list, campaignIds) {
205  if (!campaignIds.length) { return; }
206  var alreadyAttached = {};
207  var attached = list.campaigns().get();
208  while (attached.hasNext()) { alreadyAttached[attached.next().getId()] = true; }
209
210  var campaigns = AdsApp.campaigns().withIds(campaignIds.map(function (id) { return Number(id); })).get();
211  var count = 0;
212  while (campaigns.hasNext()) {
213    var campaign = campaigns.next();
214    if (alreadyAttached[campaign.getId()]) { continue; }
215    campaign.addNegativeKeywordList(list);
216    count++;
217  }
218  Logger.log('Lijst gekoppeld aan ' + count + ' extra campagne(s).');
219}
220
221/**
222 * Schrijft voorstellen naar de Sheet (nieuwe regels bovenaan, met datum).
223 */
224function writeSheet(suggestions, dryRun) {
225  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
226  var sheet = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);
227  var header = ['Datum', 'Zoekterm', 'Kosten', 'Klikken', 'Impressies', 'Conversies', 'CPA', 'Reden', 'Campagnes', 'Status'];
228  if (sheet.getLastRow() === 0) {
229    sheet.appendRow(header);
230    sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
231    sheet.setFrozenRows(1);
232  }
233  if (!suggestions.length) { return; }
234
235  var today = Utilities.formatDate(new Date(), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
236  var values = suggestions.map(function (s) {
237    return [today, s.term, Math.round(s.cost * 100) / 100, s.clicks, s.impressions, Math.round(s.conversions * 100) / 100,
238      s.cpa === null ? '' : Math.round(s.cpa * 100) / 100, s.reason, Object.keys(s.campaigns).join(', '),
239      dryRun ? 'VOORSTEL (dry run)' : 'TOEGEVOEGD'];
240  });
241  sheet.insertRowsAfter(1, values.length);
242  sheet.getRange(2, 1, values.length, header.length).setValues(values);
243  Logger.log(values.length + ' regels naar Sheet geschreven.');
244}
245
246function sendEmail(account, suggestions, dryRun) {
247  if (!EMAIL_RECIPIENTS || !suggestions.length) { return; }
248  var subject = '[Google Ads] ' + suggestions.length + ' negatieve zoekwoordvoorstellen - ' + account.getName() +
249    (dryRun ? ' (dry run)' : '');
250  var html = '<p>Top voorstellen op basis van ' + DATE_RANGE + ':</p><table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd">' +
251    '<tr style="background:#f5f5f5"><th align="left">Zoekterm</th><th>Kosten</th><th>Klikken</th><th>Conversies</th><th>Reden</th></tr>';
252  suggestions.slice(0, 50).forEach(function (s) {
253    html += '<tr><td>' + escapeHtml(s.term) + '</td><td align="right">' + s.cost.toFixed(2) + '</td><td align="right">' +
254      s.clicks + '</td><td align="right">' + s.conversions.toFixed(1) + '</td><td>' + escapeHtml(s.reason) + '</td></tr>';
255  });
256  html += '</table><p>Volledig overzicht: <a href="' + SPREADSHEET_URL + '">Google Sheet</a></p>';
257  try {
258    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
259    Logger.log('E-mail verstuurd naar ' + EMAIL_RECIPIENTS);
260  } catch (e) {
261    Logger.log('E-mail versturen mislukt: ' + e);
262  }
263}
264
265function escapeHtml(text) {
266  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
267}
268