Anomaly Detector

1/**
2 * Anomaly Detector
3 *
4 * Vergelijkt de prestaties van vandaag (tot het huidige uur) en/of gisteren met het
5 * gemiddelde van dezelfde weekdag in de afgelopen 8 weken. Voor impressies, klikken,
6 * kosten en conversies wordt een z-score berekend; boven de drempel volgt een alert per
7 * e-mail en/of Slack. Werkt op accountniveau en optioneel per campagne. Het script
8 * wijzigt niets in het account.
9 *
10 * Versie:     1.3.0
11 * Frequentie: Elk uur (voor de "vandaag"-check) of dagelijks (voor de "gisteren"-check)
12 * Niveau:     Account
13 * Licentie:   MIT
14 */
15
16// ===== CONFIGURATIE =====
17
18// Welke vergelijking(en) uitvoeren: 'TODAY' vergelijkt vandaag t/m het vorige volledige uur
19// met dezelfde uren op dezelfde weekdag; 'YESTERDAY' vergelijkt de volledige dag van gisteren.
20var COMPARE_MODES = ['TODAY', 'YESTERDAY'];
21
22// Aantal weken historie voor het weekdag-gemiddelde.
23var WEEKS_OF_HISTORY = 8;
24
25// Drempels: alert als |z-score| groter is dan deze waarde en de absolute afwijking groot genoeg is.
26var THRESHOLDS = {
27  zScore: 2.0,
28  minImpressionsBaseline: 100,  // negeer metrics met een te klein gemiddelde (ruis)
29  minCostBaseline: 10,
30  minRelativeChange: 0.25       // minimaal 25% afwijking van het gemiddelde
31};
32
33// Metrics die gecontroleerd worden.
34var METRICS = ['impressions', 'clicks', 'cost', 'conversions'];
35
36// Ook per campagne controleren (alleen campagnes met voldoende volume)?
37var CHECK_PER_CAMPAIGN = true;
38var CAMPAIGN_NAME_CONTAINS = '';
39
40// Vandaag-check pas uitvoeren vanaf dit uur (te vroeg op de dag is er te weinig data).
41var TODAY_CHECK_MIN_HOUR = 9;
42
43// Alerts.
44var EMAIL_RECIPIENTS = 'ads@voorbeeld.nl';
45var SLACK_WEBHOOK_URL = '';
46
47// ===== EINDE CONFIGURATIE =====
48
49function main() {
50  try {
51    var account = AdsApp.currentAccount();
52    var timezone = account.getTimeZone();
53    var now = new Date();
54    var currentHour = Number(Utilities.formatDate(now, timezone, 'H'));
55    Logger.log('Anomaly detector voor ' + account.getName() + ' | uur ' + currentHour + ' (' + timezone + ')');
56
57    var anomalies = [];
58    COMPARE_MODES.forEach(function (mode) {
59      if (mode === 'TODAY' && currentHour < TODAY_CHECK_MIN_HOUR) {
60        Logger.log('TODAY-check overgeslagen: het is nog voor ' + TODAY_CHECK_MIN_HOUR + ':00.');
61        return;
62      }
63      var targetDate = mode === 'TODAY' ? now : addDays(now, -1);
64      var hourLimit = mode === 'TODAY' ? currentHour : 24; // uren [0, hourLimit) tellen mee
65      var found = detectForMode(mode, targetDate, hourLimit, timezone);
66      anomalies = anomalies.concat(found);
67    });
68
69    if (!anomalies.length) {
70      Logger.log('Geen afwijkingen gevonden.');
71      return;
72    }
73    anomalies.forEach(function (a) {
74      Logger.log('ANOMALIE [' + a.mode + '] ' + a.entity + ' ' + a.metric + ': ' + a.actual.toFixed(2) + ' vs gem. ' +
75        a.mean.toFixed(2) + ' (z=' + a.z.toFixed(2) + ', ' + formatPct(a.relative) + ')');
76    });
77    sendEmail(account, anomalies);
78    sendSlack(account, anomalies);
79  } catch (e) {
80    Logger.log('FOUT in anomaly-detector: ' + e + (e.stack ? '\n' + e.stack : ''));
81    throw e;
82  }
83}
84
85/**
86 * Voert de detectie uit voor één modus. Haalt per uur de metrics op voor de doeldag en de
87 * zelfde weekdag in de afgelopen N weken, aggregeert tot hourLimit en berekent z-scores.
88 */
89function detectForMode(mode, targetDate, hourLimit, timezone) {
90  var targetStr = formatDate(targetDate, timezone);
91  var historyDates = [];
92  for (var w = 1; w <= WEEKS_OF_HISTORY; w++) {
93    historyDates.push(formatDate(addDays(targetDate, -7 * w), timezone));
94  }
95  var earliest = historyDates[historyDates.length - 1];
96
97  var query =
98    'SELECT campaign.name, segments.date, segments.hour, ' +
99    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions ' +
100    'FROM campaign ' +
101    "WHERE segments.date BETWEEN '" + earliest + "' AND '" + targetStr + "' " +
102    'AND segments.hour < ' + hourLimit;
103  if (CAMPAIGN_NAME_CONTAINS) {
104    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
105  }
106
107  var relevantDates = {};
108  relevantDates[targetStr] = true;
109  historyDates.forEach(function (d) { relevantDates[d] = true; });
110
111  // data[entity][date] = {impressions, clicks, cost, conversions}
112  var data = {};
113  var rows = AdsApp.report(query).rows();
114  while (rows.hasNext()) {
115    var row = rows.next();
116    var date = row['segments.date'];
117    if (!relevantDates[date]) { continue; }
118    var metrics = {
119      impressions: Number(row['metrics.impressions']),
120      clicks: Number(row['metrics.clicks']),
121      cost: Number(row['metrics.cost_micros']) / 1000000,
122      conversions: Number(row['metrics.conversions'])
123    };
124    accumulate(data, 'ACCOUNT', date, metrics);
125    if (CHECK_PER_CAMPAIGN) { accumulate(data, row['campaign.name'], date, metrics); }
126  }
127
128  var anomalies = [];
129  Object.keys(data).forEach(function (entity) {
130    var actualDay = data[entity][targetStr] || emptyMetrics();
131    METRICS.forEach(function (metric) {
132      var samples = historyDates.map(function (d) { return (data[entity][d] || emptyMetrics())[metric]; });
133      var stats = meanAndStd(samples);
134      if (!passesBaseline(metric, stats.mean)) { return; }
135      var actual = actualDay[metric];
136      var std = Math.max(stats.std, stats.mean * 0.05); // ondergrens tegen deling door bijna nul
137      var z = (actual - stats.mean) / std;
138      var relative = stats.mean > 0 ? (actual - stats.mean) / stats.mean : 0;
139      if (Math.abs(z) >= THRESHOLDS.zScore && Math.abs(relative) >= THRESHOLDS.minRelativeChange) {
140        anomalies.push({ mode: mode, entity: entity, metric: metric, actual: actual, mean: stats.mean, std: stats.std, z: z, relative: relative, hourLimit: hourLimit });
141      }
142    });
143  });
144  Logger.log(mode + ' (' + targetStr + ', uren < ' + hourLimit + '): ' + anomalies.length + ' afwijking(en).');
145  return anomalies;
146}
147
148function passesBaseline(metric, mean) {
149  if (metric === 'impressions') { return mean >= THRESHOLDS.minImpressionsBaseline; }
150  if (metric === 'cost') { return mean >= THRESHOLDS.minCostBaseline; }
151  if (metric === 'clicks') { return mean >= THRESHOLDS.minImpressionsBaseline / 20; }
152  if (metric === 'conversions') { return mean >= 1; }
153  return mean > 0;
154}
155
156function accumulate(data, entity, date, metrics) {
157  if (!data[entity]) { data[entity] = {}; }
158  if (!data[entity][date]) { data[entity][date] = emptyMetrics(); }
159  var target = data[entity][date];
160  METRICS.forEach(function (m) { target[m] += metrics[m]; });
161}
162
163function emptyMetrics() {
164  return { impressions: 0, clicks: 0, cost: 0, conversions: 0 };
165}
166
167function meanAndStd(values) {
168  if (!values.length) { return { mean: 0, std: 0 }; }
169  var sum = values.reduce(function (a, b) { return a + b; }, 0);
170  var mean = sum / values.length;
171  var variance = values.reduce(function (a, b) { return a + Math.pow(b - mean, 2); }, 0) / values.length;
172  return { mean: mean, std: Math.sqrt(variance) };
173}
174
175function addDays(date, days) {
176  var d = new Date(date.getTime());
177  d.setDate(d.getDate() + days);
178  return d;
179}
180
181function formatDate(date, timezone) {
182  return Utilities.formatDate(date, timezone, 'yyyy-MM-dd');
183}
184
185function formatPct(fraction) {
186  return (fraction > 0 ? '+' : '') + (fraction * 100).toFixed(0) + '%';
187}
188
189function metricLabel(metric) {
190  return { impressions: 'Impressies', clicks: 'Klikken', cost: 'Kosten', conversions: 'Conversies' }[metric] || metric;
191}
192
193function sendEmail(account, anomalies) {
194  if (!EMAIL_RECIPIENTS) { return; }
195  var subject = '[Google Ads] ' + anomalies.length + ' afwijking(en) gedetecteerd - ' + account.getName();
196  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><p>Vergelijking met het gemiddelde van dezelfde weekdag over de afgelopen ' +
197    WEEKS_OF_HISTORY + ' weken.</p><table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd">' +
198    '<tr style="background:#f5f5f5"><th>Periode</th><th align="left">Entiteit</th><th>Metric</th><th>Actueel</th><th>Gemiddelde</th><th>Afwijking</th><th>Z-score</th></tr>';
199  anomalies.forEach(function (a) {
200    var color = a.z > 0 ? '#e6f4ea' : '#fdecea';
201    if (a.metric === 'cost' && a.z > 0) { color = '#fdecea'; }
202    html += '<tr style="background:' + color + '"><td>' + (a.mode === 'TODAY' ? 'Vandaag t/m ' + a.hourLimit + ':00' : 'Gisteren') + '</td><td>' +
203      escapeHtml(a.entity) + '</td><td>' + metricLabel(a.metric) + '</td><td align="right">' + a.actual.toFixed(a.metric === 'cost' ? 2 : 0) +
204      '</td><td align="right">' + a.mean.toFixed(a.metric === 'cost' ? 2 : 0) + '</td><td align="right">' + formatPct(a.relative) +
205      '</td><td align="right">' + a.z.toFixed(2) + '</td></tr>';
206  });
207  html += '</table></div>';
208  try {
209    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
210    Logger.log('E-mail verstuurd naar ' + EMAIL_RECIPIENTS);
211  } catch (e) {
212    Logger.log('E-mail versturen mislukt: ' + e);
213  }
214}
215
216function sendSlack(account, anomalies) {
217  if (!SLACK_WEBHOOK_URL) { return; }
218  var lines = [':warning: *' + anomalies.length + ' afwijking(en) in ' + account.getName() + '*'];
219  anomalies.slice(0, 15).forEach(function (a) {
220    lines.push('- [' + (a.mode === 'TODAY' ? 'vandaag' : 'gisteren') + '] ' + a.entity + ' / ' + metricLabel(a.metric) + ': ' +
221      a.actual.toFixed(a.metric === 'cost' ? 2 : 0) + ' vs ' + a.mean.toFixed(a.metric === 'cost' ? 2 : 0) + ' (' + formatPct(a.relative) + ', z=' + a.z.toFixed(1) + ')');
222  });
223  try {
224    UrlFetchApp.fetch(SLACK_WEBHOOK_URL, { method: 'post', contentType: 'application/json', payload: JSON.stringify({ text: lines.join('\n') }), muteHttpExceptions: true });
225  } catch (e) {
226    Logger.log('Slack versturen mislukt: ' + e);
227  }
228}
229
230function escapeHtml(text) {
231  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
232}
233