Auction Insights Tracker

1/**
2 * Auction Insights Tracker
3 *
4 * Legt per Search-campagne dagelijks/wekelijks de concurrentie-metrics vast:
5 * vertoningsaandeel (search_impression_share), top- en absoluut-top-vertoningsaandeel,
6 * verloren vertoningsaandeel door budget en door rangschikking. De waarden gaan naar een
7 * historietabblad in een Google Sheet en er wordt een trendtabblad opgebouwd met de
8 * verandering t.o.v. de vorige periode. Optioneel alert bij een sterke daling van het
9 * vertoningsaandeel. Het script wijzigt niets in het account.
10 *
11 * Versie:     1.2.0
12 * Frequentie: Dagelijks (voor dagelijkse historie) of wekelijks
13 * Niveau:     Account
14 * Licentie:   MIT
15 */
16
17// ===== CONFIGURATIE =====
18
19// Google Sheet voor historie en trend.
20var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
21var SHEET_NAMES = { history: 'IS historie', trend: 'IS trend' };
22
23// Periode per meting. Bij dagelijkse runs: 'LAST_7_DAYS' geeft een voortschrijdend weekgemiddelde.
24var DATE_RANGE = 'LAST_7_DAYS';
25
26// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle Search-campagnes.
27var CAMPAIGN_NAME_CONTAINS = '';
28
29// Alleen campagnes met minimaal dit aantal impressies in de periode.
30var MIN_IMPRESSIONS = 100;
31
32// Alert bij daling van het vertoningsaandeel (in procentpunten) t.o.v. de vorige meting. 0 = uit.
33var THRESHOLDS = {
34  impressionShareDropPoints: 10,
35  budgetLostAlert: 0.30   // alert als > 30% verloren door budget
36};
37var EMAIL_RECIPIENTS = '';
38var SLACK_WEBHOOK_URL = '';
39
40// ===== EINDE CONFIGURATIE =====
41
42var METRIC_FIELDS = [
43  { key: 'is', field: 'metrics.search_impression_share', label: 'Vertoningsaandeel' },
44  { key: 'topIs', field: 'metrics.search_top_impression_share', label: 'Top IS' },
45  { key: 'absTopIs', field: 'metrics.search_absolute_top_impression_share', label: 'Abs. top IS' },
46  { key: 'lostBudget', field: 'metrics.search_budget_lost_impression_share', label: 'Verloren IS (budget)' },
47  { key: 'lostRank', field: 'metrics.search_rank_lost_impression_share', label: 'Verloren IS (rank)' },
48  { key: 'clickShare', field: 'metrics.search_click_share', label: 'Klikaandeel' }
49];
50
51function main() {
52  try {
53    var account = AdsApp.currentAccount();
54    var timezone = account.getTimeZone();
55    var today = Utilities.formatDate(new Date(), timezone, 'yyyy-MM-dd');
56    Logger.log('Auction insights tracker voor ' + account.getName() + ' | ' + DATE_RANGE);
57
58    var current = fetchImpressionShare();
59    Logger.log(current.length + ' campagnes met vertoningsaandeel-data.');
60
61    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
62    var previous = readPreviousMeasurement(ss, today);
63    appendHistory(ss, current, today);
64    var trend = writeTrend(ss, current, previous, today);
65    sendAlerts(account, trend);
66    Logger.log('Klaar: ' + SPREADSHEET_URL);
67  } catch (e) {
68    Logger.log('FOUT in auction-insights-tracker: ' + e + (e.stack ? '\n' + e.stack : ''));
69    throw e;
70  }
71}
72
73/**
74 * Haalt vertoningsaandeel-metrics per Search-campagne op. Waarden zijn fracties (0.0 - 1.0).
75 * "< 10%" wordt door de API als 0.0999 teruggegeven; "> 90%" als 0.9001.
76 */
77function fetchImpressionShare() {
78  var query =
79    'SELECT campaign.id, campaign.name, campaign.bidding_strategy_type, metrics.impressions, metrics.clicks, metrics.cost_micros, ' +
80    METRIC_FIELDS.map(function (m) { return m.field; }).join(', ') + ' ' +
81    'FROM campaign ' +
82    "WHERE campaign.advertising_channel_type = 'SEARCH' AND campaign.status = 'ENABLED' " +
83    'AND segments.date DURING ' + DATE_RANGE + ' ' +
84    'AND metrics.impressions >= ' + MIN_IMPRESSIONS;
85  if (CAMPAIGN_NAME_CONTAINS) {
86    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
87  }
88  var list = [];
89  var rows = AdsApp.report(query).rows();
90  while (rows.hasNext()) {
91    var row = rows.next();
92    var entry = {
93      id: row['campaign.id'],
94      name: row['campaign.name'],
95      bidding: row['campaign.bidding_strategy_type'],
96      impressions: Number(row['metrics.impressions']),
97      clicks: Number(row['metrics.clicks']),
98      cost: Number(row['metrics.cost_micros']) / 1000000
99    };
100    METRIC_FIELDS.forEach(function (m) { entry[m.key] = toFraction(row[m.field]); });
101    list.push(entry);
102  }
103  list.sort(function (a, b) { return b.cost - a.cost; });
104  return list;
105}
106
107function toFraction(value) {
108  if (value === undefined || value === null || value === '' || value === '--') { return null; }
109  var n = Number(value);
110  return isNaN(n) ? null : n;
111}
112
113/**
114 * Leest de meest recente eerdere meting per campagne uit de historie (voor de trend).
115 */
116function readPreviousMeasurement(ss, today) {
117  var previous = {};
118  var sheet = ss.getSheetByName(SHEET_NAMES.history);
119  if (!sheet || sheet.getLastRow() < 2) { return previous; }
120  var data = sheet.getDataRange().getValues();
121  var header = data[0];
122  var idx = {};
123  header.forEach(function (h, i) { idx[h] = i; });
124  for (var i = data.length - 1; i >= 1; i--) {
125    var row = data[i];
126    var date = row[idx['Datum']] instanceof Date ? Utilities.formatDate(row[idx['Datum']], AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd') : String(row[idx['Datum']]);
127    if (date === today) { continue; }
128    var id = String(row[idx['Campagne ID']]);
129    if (previous[id]) { continue; } // we willen alleen de meest recente
130    var entry = { date: date };
131    METRIC_FIELDS.forEach(function (m) { entry[m.key] = row[idx[m.label]] === '' ? null : Number(row[idx[m.label]]); });
132    previous[id] = entry;
133  }
134  return previous;
135}
136
137function appendHistory(ss, current, today) {
138  var sheet = ss.getSheetByName(SHEET_NAMES.history) || ss.insertSheet(SHEET_NAMES.history);
139  var header = ['Datum', 'Periode', 'Campagne ID', 'Campagne', 'Biedstrategie', 'Impressies', 'Klikken', 'Kosten'].concat(METRIC_FIELDS.map(function (m) { return m.label; }));
140  if (sheet.getLastRow() === 0) {
141    sheet.appendRow(header);
142    sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
143    sheet.setFrozenRows(1);
144  }
145  // Verwijder eventuele regels van vandaag (bij dubbele run).
146  var lastRow = sheet.getLastRow();
147  if (lastRow > 1) {
148    var data = sheet.getRange(2, 1, lastRow - 1, header.length).getValues();
149    var keep = data.filter(function (r) {
150      var d = r[0] instanceof Date ? Utilities.formatDate(r[0], AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd') : String(r[0]);
151      return d !== today;
152    });
153    if (keep.length !== data.length) {
154      sheet.getRange(2, 1, lastRow - 1, header.length).clearContent();
155      if (keep.length) { sheet.getRange(2, 1, keep.length, header.length).setValues(keep); }
156    }
157  }
158  var values = current.map(function (c) {
159    return [today, DATE_RANGE, c.id, c.name, c.bidding, c.impressions, c.clicks, round(c.cost)].concat(METRIC_FIELDS.map(function (m) { return c[m.key] === null ? '' : c[m.key]; }));
160  });
161  if (values.length) {
162    var start = sheet.getLastRow() + 1;
163    sheet.getRange(start, 1, values.length, header.length).setValues(values);
164    sheet.getRange(start, 9, values.length, METRIC_FIELDS.length).setNumberFormat('0.0%');
165  }
166  Logger.log(values.length + ' historieregels geschreven.');
167}
168
169/**
170 * Schrijft het trendtabblad: huidige waarde, vorige waarde en verschil in procentpunten per metric.
171 */
172function writeTrend(ss, current, previous, today) {
173  var sheet = ss.getSheetByName(SHEET_NAMES.trend) || ss.insertSheet(SHEET_NAMES.trend);
174  sheet.clearContents();
175  sheet.clearFormats();
176  var header = ['Campagne', 'Biedstrategie', 'Kosten'];
177  METRIC_FIELDS.forEach(function (m) { header.push(m.label, m.label + ' vorige', 'Verschil (pp)'); });
178  header.push('Signaal');
179
180  var trend = [];
181  var values = [header];
182  current.forEach(function (c) {
183    var prev = previous[c.id] || {};
184    var row = [c.name, c.bidding, round(c.cost)];
185    var signals = [];
186    var deltas = {};
187    METRIC_FIELDS.forEach(function (m) {
188      var cur = c[m.key];
189      var old = prev[m.key] === undefined ? null : prev[m.key];
190      var delta = cur !== null && old !== null ? (cur - old) * 100 : null;
191      deltas[m.key] = delta;
192      row.push(cur === null ? '' : cur, old === null ? '' : old, delta === null ? '' : Math.round(delta * 10) / 10);
193    });
194    if (deltas.is !== null && THRESHOLDS.impressionShareDropPoints > 0 && deltas.is <= -THRESHOLDS.impressionShareDropPoints) {
195      signals.push('IS gedaald met ' + Math.abs(deltas.is).toFixed(1) + ' pp');
196    }
197    if (c.lostBudget !== null && c.lostBudget >= THRESHOLDS.budgetLostAlert) {
198      signals.push('Budget beperkt: ' + (c.lostBudget * 100).toFixed(0) + '% verloren');
199    }
200    if (c.lostRank !== null && c.lostRank >= 0.5) {
201      signals.push('Rank beperkt: ' + (c.lostRank * 100).toFixed(0) + '% verloren');
202    }
203    row.push(signals.join(' | '));
204    values.push(row);
205    trend.push({ campaign: c, previous: prev, deltas: deltas, signals: signals });
206  });
207
208  sheet.getRange(1, 1, values.length, header.length).setValues(values);
209  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
210  sheet.setFrozenRows(1);
211  sheet.setFrozenColumns(1);
212  if (values.length > 1) {
213    for (var i = 0; i < METRIC_FIELDS.length; i++) {
214      sheet.getRange(2, 4 + i * 3, values.length - 1, 2).setNumberFormat('0.0%');
215    }
216    for (var r = 1; r < values.length; r++) {
217      if (values[r][header.length - 1]) { sheet.getRange(r + 1, header.length).setBackground('#fff3e0'); }
218      var isDelta = values[r][6];
219      if (isDelta !== '' && isDelta <= -THRESHOLDS.impressionShareDropPoints) { sheet.getRange(r + 1, 6).setBackground('#fdecea'); }
220      if (isDelta !== '' && isDelta >= THRESHOLDS.impressionShareDropPoints) { sheet.getRange(r + 1, 6).setBackground('#e6f4ea'); }
221    }
222  }
223  sheet.getRange(1, header.length + 2).setValue('Meting ' + today + ' (' + DATE_RANGE + ') vs vorige meting');
224  return trend;
225}
226
227function sendAlerts(account, trend) {
228  var flagged = trend.filter(function (t) { return t.signals.length > 0; });
229  if (!flagged.length) { Logger.log('Geen signalen.'); return; }
230  flagged.forEach(function (t) { Logger.log('SIGNAAL ' + t.campaign.name + ': ' + t.signals.join(' | ')); });
231
232  if (EMAIL_RECIPIENTS) {
233    var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><h3>Auction insights signalen - ' + escapeHtml(account.getName()) + '</h3>' +
234      '<table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd"><tr style="background:#f5f5f5"><th align="left">Campagne</th><th>IS</th><th>Verloren (budget)</th><th>Verloren (rank)</th><th align="left">Signaal</th></tr>';
235    flagged.forEach(function (t) {
236      var c = t.campaign;
237      html += '<tr><td>' + escapeHtml(c.name) + '</td><td align="right">' + pct(c.is) + '</td><td align="right">' + pct(c.lostBudget) + '</td><td align="right">' + pct(c.lostRank) + '</td><td>' + escapeHtml(t.signals.join(' | ')) + '</td></tr>';
238    });
239    html += '</table><p><a href="' + SPREADSHEET_URL + '">Open de Sheet</a></p></div>';
240    try {
241      MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: '[Google Ads] ' + flagged.length + ' auction insights signaal/signalen - ' + account.getName(), htmlBody: html });
242    } catch (e) {
243      Logger.log('E-mail versturen mislukt: ' + e);
244    }
245  }
246  if (SLACK_WEBHOOK_URL) {
247    var text = ':bar_chart: *Auction insights ' + account.getName() + '*\n' + flagged.slice(0, 15).map(function (t) { return '- ' + t.campaign.name + ': ' + t.signals.join(' | '); }).join('\n');
248    try {
249      UrlFetchApp.fetch(SLACK_WEBHOOK_URL, { method: 'post', contentType: 'application/json', payload: JSON.stringify({ text: text }), muteHttpExceptions: true });
250    } catch (e) {
251      Logger.log('Slack versturen mislukt: ' + e);
252    }
253  }
254}
255
256function pct(fraction) {
257  return fraction === null || fraction === undefined ? '-' : (fraction * 100).toFixed(1) + '%';
258}
259
260function round(value) {
261  return Math.round(Number(value) * 100) / 100;
262}
263
264function escapeHtml(text) {
265  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
266}
267