Label Low Performers

1/**
2 * Label Low Performers
3 *
4 * Labelt zoekwoorden en advertenties die aan één of meer "low performer"-regels voldoen,
5 * bijvoorbeeld een lage CTR bij voldoende impressies of hoge kosten zonder conversies.
6 * Optioneel worden gelabelde items gepauzeerd. Items die niet meer aan de regels voldoen
7 * krijgen het label weer verwijderd (en worden optioneel weer ingeschakeld als het script
8 * ze zelf pauzeerde). Standaard staat DRY_RUN aan: dan wordt alleen gelogd en naar de
9 * Sheet geschreven.
10 *
11 * Versie:     1.4.0
12 * Frequentie: Wekelijks
13 * Niveau:     Account
14 * Licentie:   MIT
15 */
16
17// ===== CONFIGURATIE =====
18
19// Zolang DRY_RUN true is worden er geen labels toegevoegd en niets gepauzeerd.
20var DRY_RUN = true;
21
22// Gelabelde items ook pauzeren? (alleen als DRY_RUN false is)
23var PAUSE_LOW_PERFORMERS = false;
24
25// Items die het script eerder pauzeerde weer inschakelen als ze niet meer aan de regels voldoen?
26var REENABLE_RECOVERED = false;
27
28// Periode waarover de statistieken berekend worden.
29var DATE_RANGE = 'LAST_30_DAYS';
30
31// Wat controleren?
32var CHECK_KEYWORDS = true;
33var CHECK_ADS = true;
34
35// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle campagnes.
36var CAMPAIGN_NAME_CONTAINS = '';
37
38// Labels (worden aangemaakt als ze nog niet bestaan).
39var LABEL_NAMES = {
40  lowPerformer: 'Script: Low performer',
41  pausedByScript: 'Script: Gepauzeerd'
42};
43
44// Regels: een item is low performer als het aan MINIMAAL ÉÉN regel voldoet.
45var RULES = [
46  { name: 'Lage CTR', minImpressions: 1000, maxCtr: 0.01 },                      // CTR < 1% bij >= 1000 impressies
47  { name: 'Kosten zonder conversie', minCost: 100, maxConversions: 0 },          // >= 100 kosten en 0 conversies
48  { name: 'CPA te hoog', minConversions: 2, minCpa: 250 }                         // >= 2 conversies maar CPA > 250
49];
50
51// Google Sheet met logboek (optioneel, leeg = geen Sheet).
52var SPREADSHEET_URL = '';
53var SHEET_NAME = 'Low performers log';
54
55// E-mail met samenvatting (optioneel).
56var EMAIL_RECIPIENTS = '';
57
58// ===== EINDE CONFIGURATIE =====
59
60function main() {
61  try {
62    var account = AdsApp.currentAccount();
63    var preview = AdsApp.getExecutionInfo().isPreview();
64    var effectiveDryRun = DRY_RUN || preview;
65    Logger.log('Label low performers voor ' + account.getName() + ' | DRY_RUN=' + effectiveDryRun + ' | pauzeren=' + (PAUSE_LOW_PERFORMERS && !effectiveDryRun));
66
67    if (!effectiveDryRun) { ensureLabels(); }
68
69    var actions = [];
70    if (CHECK_KEYWORDS) { actions = actions.concat(processKeywords(effectiveDryRun)); }
71    if (CHECK_ADS) { actions = actions.concat(processAds(effectiveDryRun)); }
72
73    var summary = summarize(actions);
74    Logger.log('Samenvatting: ' + JSON.stringify(summary));
75    writeSheet(actions, effectiveDryRun);
76    sendEmail(account, actions, summary, effectiveDryRun);
77  } catch (e) {
78    Logger.log('FOUT in label-low-performers: ' + e + (e.stack ? '\n' + e.stack : ''));
79    throw e;
80  }
81}
82
83function ensureLabels() {
84  Object.keys(LABEL_NAMES).forEach(function (key) {
85    var name = LABEL_NAMES[key];
86    var exists = AdsApp.labels().withCondition("label.name = '" + name.replace(/'/g, "\\'") + "'").get().hasNext();
87    if (!exists) {
88      AdsApp.createLabel(name, 'Automatisch aangemaakt door label-low-performers.js', key === 'pausedByScript' ? '#E53935' : '#FB8C00');
89      Logger.log('Label aangemaakt: ' + name);
90    }
91  });
92}
93
94/**
95 * Bepaalt welke regel(s) van toepassing zijn op de statistieken. Retourneert een lijst regelnamen.
96 */
97function matchRules(stats) {
98  var matched = [];
99  RULES.forEach(function (rule) {
100    var ok = true;
101    if (rule.minImpressions !== undefined && stats.impressions < rule.minImpressions) { ok = false; }
102    if (rule.maxCtr !== undefined && !(stats.ctr < rule.maxCtr)) { ok = false; }
103    if (rule.minCost !== undefined && stats.cost < rule.minCost) { ok = false; }
104    if (rule.maxConversions !== undefined && stats.conversions > rule.maxConversions) { ok = false; }
105    if (rule.minConversions !== undefined && stats.conversions < rule.minConversions) { ok = false; }
106    if (rule.minCpa !== undefined && !(stats.conversions > 0 && stats.cost / stats.conversions > rule.minCpa)) { ok = false; }
107    if (ok) { matched.push(rule.name); }
108  });
109  return matched;
110}
111
112function statsFromRow(row) {
113  var s = {
114    impressions: Number(row['metrics.impressions']),
115    clicks: Number(row['metrics.clicks']),
116    cost: Number(row['metrics.cost_micros']) / 1000000,
117    conversions: Number(row['metrics.conversions'])
118  };
119  s.ctr = s.impressions > 0 ? s.clicks / s.impressions : 0;
120  return s;
121}
122
123function campaignFilterClause() {
124  return CAMPAIGN_NAME_CONTAINS ? " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'" : '';
125}
126
127/**
128 * Zoekwoorden: statistieken ophalen via GAQL, daarna entiteiten via AdsApp.keywords() muteren.
129 */
130function processKeywords(dryRun) {
131  var query =
132    'SELECT campaign.name, ad_group.name, ad_group.id, ad_group_criterion.criterion_id, ad_group_criterion.keyword.text, ' +
133    'ad_group_criterion.keyword.match_type, ad_group_criterion.status, ad_group_criterion.labels, ' +
134    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions ' +
135    'FROM keyword_view ' +
136    'WHERE segments.date DURING ' + DATE_RANGE + ' ' +
137    "AND campaign.status = 'ENABLED' AND ad_group.status = 'ENABLED' AND ad_group_criterion.status IN ('ENABLED', 'PAUSED') " +
138    'AND metrics.impressions > 0' + campaignFilterClause();
139
140  var candidates = {};
141  var rows = AdsApp.report(query).rows();
142  while (rows.hasNext()) {
143    var row = rows.next();
144    var stats = statsFromRow(row);
145    var matched = matchRules(stats);
146    var key = row['ad_group.id'] + '##' + row['ad_group_criterion.criterion_id'];
147    candidates[key] = {
148      type: 'Zoekwoord',
149      name: row['ad_group_criterion.keyword.text'] + ' [' + row['ad_group_criterion.keyword.match_type'] + ']',
150      campaign: row['campaign.name'],
151      adGroup: row['ad_group.name'],
152      status: row['ad_group_criterion.status'],
153      stats: stats,
154      matched: matched,
155      ids: [Number(row['ad_group.id']), Number(row['ad_group_criterion.criterion_id'])]
156    };
157  }
158
159  var actions = [];
160  var keys = Object.keys(candidates);
161  Logger.log(keys.length + ' zoekwoorden beoordeeld.');
162  // In batches ophalen om de iterator-limieten te respecteren.
163  for (var i = 0; i < keys.length; i += 500) {
164    var batch = keys.slice(i, i + 500).map(function (k) { return candidates[k].ids; });
165    var iterator = AdsApp.keywords().withIds(batch).get();
166    while (iterator.hasNext()) {
167      var keyword = iterator.next();
168      var c = candidates[keyword.getAdGroup().getId() + '##' + keyword.getId()];
169      if (!c) { continue; }
170      var action = decideAndApply(keyword, c, dryRun);
171      if (action) { actions.push(action); }
172    }
173  }
174  return actions;
175}
176
177/**
178 * Advertenties: dezelfde aanpak via ad_group_ad en AdsApp.ads().
179 */
180function processAds(dryRun) {
181  var query =
182    'SELECT campaign.name, ad_group.name, ad_group.id, ad_group_ad.ad.id, ad_group_ad.ad.type, ad_group_ad.status, ad_group_ad.ad.name, ' +
183    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions ' +
184    'FROM ad_group_ad ' +
185    'WHERE segments.date DURING ' + DATE_RANGE + ' ' +
186    "AND campaign.status = 'ENABLED' AND ad_group.status = 'ENABLED' AND ad_group_ad.status IN ('ENABLED', 'PAUSED') " +
187    'AND metrics.impressions > 0' + campaignFilterClause();
188
189  var candidates = {};
190  var rows = AdsApp.report(query).rows();
191  while (rows.hasNext()) {
192    var row = rows.next();
193    var stats = statsFromRow(row);
194    var key = row['ad_group.id'] + '##' + row['ad_group_ad.ad.id'];
195    candidates[key] = {
196      type: 'Advertentie',
197      name: (row['ad_group_ad.ad.name'] || row['ad_group_ad.ad.type']) + ' #' + row['ad_group_ad.ad.id'],
198      campaign: row['campaign.name'],
199      adGroup: row['ad_group.name'],
200      status: row['ad_group_ad.status'],
201      stats: stats,
202      matched: matchRules(stats),
203      ids: [Number(row['ad_group.id']), Number(row['ad_group_ad.ad.id'])]
204    };
205  }
206
207  var actions = [];
208  var keys = Object.keys(candidates);
209  Logger.log(keys.length + ' advertenties beoordeeld.');
210  for (var i = 0; i < keys.length; i += 500) {
211    var batch = keys.slice(i, i + 500).map(function (k) { return candidates[k].ids; });
212    var iterator = AdsApp.ads().withIds(batch).get();
213    while (iterator.hasNext()) {
214      var ad = iterator.next();
215      var c = candidates[ad.getAdGroup().getId() + '##' + ad.getId()];
216      if (!c) { continue; }
217      var action = decideAndApply(ad, c, dryRun);
218      if (action) { actions.push(action); }
219    }
220  }
221  return actions;
222}
223
224/**
225 * Bepaalt de actie voor een entiteit (label toevoegen/verwijderen, pauzeren/inschakelen) en voert die uit
226 * tenzij dryRun. Retourneert een actie-object voor het logboek, of null als er niets te doen is.
227 */
228function decideAndApply(entity, candidate, dryRun) {
229  var labels = {};
230  var labelIterator = entity.labels().get();
231  while (labelIterator.hasNext()) { labels[labelIterator.next().getName()] = true; }
232  var hasLowLabel = !!labels[LABEL_NAMES.lowPerformer];
233  var hasPausedLabel = !!labels[LABEL_NAMES.pausedByScript];
234  var isLow = candidate.matched.length > 0;
235  var steps = [];
236
237  if (isLow && !hasLowLabel) {
238    steps.push('label toevoegen');
239    if (!dryRun) { entity.applyLabel(LABEL_NAMES.lowPerformer); }
240  }
241  if (isLow && PAUSE_LOW_PERFORMERS && entity.isEnabled()) {
242    steps.push('pauzeren');
243    if (!dryRun) { entity.pause(); entity.applyLabel(LABEL_NAMES.pausedByScript); }
244  }
245  if (!isLow && hasLowLabel) {
246    steps.push('label verwijderen (hersteld)');
247    if (!dryRun) { entity.removeLabel(LABEL_NAMES.lowPerformer); }
248  }
249  if (!isLow && hasPausedLabel && REENABLE_RECOVERED && entity.isPaused()) {
250    steps.push('weer inschakelen');
251    if (!dryRun) { entity.enable(); entity.removeLabel(LABEL_NAMES.pausedByScript); }
252  }
253  if (!steps.length) { return null; }
254
255  Logger.log((dryRun ? '[DRY RUN] ' : '') + candidate.type + ' "' + candidate.name + '" (' + candidate.campaign + ' > ' + candidate.adGroup + '): ' +
256    steps.join(', ') + (isLow ? ' | regels: ' + candidate.matched.join('; ') : ''));
257  return { candidate: candidate, steps: steps, isLow: isLow };
258}
259
260function summarize(actions) {
261  var s = { labeled: 0, paused: 0, unlabeled: 0, reenabled: 0 };
262  actions.forEach(function (a) {
263    a.steps.forEach(function (st) {
264      if (st === 'label toevoegen') { s.labeled++; }
265      if (st === 'pauzeren') { s.paused++; }
266      if (st.indexOf('label verwijderen') === 0) { s.unlabeled++; }
267      if (st === 'weer inschakelen') { s.reenabled++; }
268    });
269  });
270  return s;
271}
272
273function writeSheet(actions, dryRun) {
274  if (!SPREADSHEET_URL || !actions.length) { return; }
275  try {
276    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
277    var sheet = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);
278    var header = ['Datum', 'Modus', 'Type', 'Item', 'Campagne', 'Advertentiegroep', 'Impressies', 'Klikken', 'CTR', 'Kosten', 'Conversies', 'Regels', 'Acties'];
279    if (sheet.getLastRow() === 0) {
280      sheet.appendRow(header);
281      sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
282      sheet.setFrozenRows(1);
283    }
284    var now = new Date();
285    var values = actions.map(function (a) {
286      var c = a.candidate;
287      return [now, dryRun ? 'DRY RUN' : 'LIVE', c.type, c.name, c.campaign, c.adGroup, c.stats.impressions, c.stats.clicks,
288        Math.round(c.stats.ctr * 10000) / 100 + '%', Math.round(c.stats.cost * 100) / 100, Math.round(c.stats.conversions * 100) / 100,
289        c.matched.join('; '), a.steps.join(', ')];
290    });
291    sheet.getRange(sheet.getLastRow() + 1, 1, values.length, header.length).setValues(values);
292    Logger.log(values.length + ' regels naar logboek geschreven.');
293  } catch (e) {
294    Logger.log('Sheet schrijven mislukt: ' + e);
295  }
296}
297
298function sendEmail(account, actions, summary, dryRun) {
299  if (!EMAIL_RECIPIENTS || !actions.length) { return; }
300  var subject = '[Google Ads] Low performers ' + account.getName() + ': ' + summary.labeled + ' gelabeld, ' + summary.paused + ' gepauzeerd' + (dryRun ? ' (dry run)' : '');
301  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><p>Periode ' + DATE_RANGE + '. ' + (dryRun ? '<b>Dry run: er is niets gewijzigd.</b>' : '') +
302    '</p><table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd"><tr style="background:#f5f5f5"><th align="left">Type</th><th align="left">Item</th><th align="left">Campagne</th><th>Kosten</th><th>Conv.</th><th align="left">Regels</th><th align="left">Actie</th></tr>';
303  actions.slice(0, 100).forEach(function (a) {
304    var c = a.candidate;
305    html += '<tr><td>' + c.type + '</td><td>' + escapeHtml(c.name) + '</td><td>' + escapeHtml(c.campaign) + '</td><td align="right">' + c.stats.cost.toFixed(2) +
306      '</td><td align="right">' + c.stats.conversions.toFixed(1) + '</td><td>' + escapeHtml(c.matched.join('; ')) + '</td><td>' + a.steps.join(', ') + '</td></tr>';
307  });
308  html += '</table></div>';
309  try {
310    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
311  } catch (e) {
312    Logger.log('E-mail versturen mislukt: ' + e);
313  }
314}
315
316function escapeHtml(text) {
317  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
318}
319