Campaign Performance Report

1/**
2 * Campaign Performance Report (week-over-week)
3 *
4 * Maakt wekelijks een rapport per campagne met de kernmetrics van de afgelopen 7 dagen
5 * versus de 7 dagen daarvoor (WoW). Schrijft het overzicht naar een Google Sheet (één
6 * tabblad per week plus een historietabblad) en stuurt een HTML-e-mail met een gekleurde
7 * tabel. Het script wijzigt niets in het account.
8 *
9 * Versie:     1.4.0
10 * Frequentie: Wekelijks (bv. maandagochtend)
11 * Niveau:     Account
12 * Licentie:   MIT
13 */
14
15// ===== CONFIGURATIE =====
16
17// Google Sheet voor het rapport.
18var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
19var HISTORY_SHEET_NAME = 'Historie';
20
21// Ontvangers van de HTML-e-mail (komma-gescheiden). Leeg = geen e-mail.
22var EMAIL_RECIPIENTS = 'ads@voorbeeld.nl';
23
24// Alleen campagnes waarvan de naam deze tekst bevat. Leeg = alle campagnes.
25var CAMPAIGN_NAME_CONTAINS = '';
26
27// Ook gepauzeerde campagnes met kosten in de periode meenemen?
28var INCLUDE_PAUSED_WITH_SPEND = true;
29
30// Kleurdrempels voor de WoW-verandering (fractie). Boven "good" groen, onder "bad" rood.
31var THRESHOLDS = {
32  good: 0.10,
33  bad: -0.10,
34  minCostToShow: 1   // campagnes onder dit bedrag (beide weken) worden weggelaten
35};
36
37// Metrics waarbij een stijging POSITIEF is (groen). Voor de overige (kosten, CPA) is een daling positief.
38var HIGHER_IS_BETTER = { impressions: true, clicks: true, ctr: true, conversions: true, convValue: true, roas: true, convRate: true };
39
40// ===== EINDE CONFIGURATIE =====
41
42function main() {
43  try {
44    var account = AdsApp.currentAccount();
45    var timezone = account.getTimeZone();
46    var currency = account.getCurrencyCode();
47    var ranges = computeRanges(timezone);
48    Logger.log('WoW-rapport voor ' + account.getName() + ': ' + ranges.current.label + ' vs ' + ranges.previous.label);
49
50    var current = fetchCampaignMetrics(ranges.current);
51    var previous = fetchCampaignMetrics(ranges.previous);
52    var report = mergeAndCompare(current, previous);
53    Logger.log(report.rows.length + ' campagnes in rapport.');
54
55    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
56    writeWeekSheet(ss, report, ranges, currency);
57    appendHistory(ss, report, ranges);
58    sendEmail(account, report, ranges, currency);
59    Logger.log('Klaar: ' + SPREADSHEET_URL);
60  } catch (e) {
61    Logger.log('FOUT in campaign-performance-report: ' + e + (e.stack ? '\n' + e.stack : ''));
62    throw e;
63  }
64}
65
66/**
67 * Bepaalt de twee periodes: de laatste 7 volledige dagen en de 7 dagen daarvoor.
68 */
69function computeRanges(timezone) {
70  var today = new Date();
71  var end = addDays(today, -1);
72  var start = addDays(today, -7);
73  var prevEnd = addDays(today, -8);
74  var prevStart = addDays(today, -14);
75  return {
76    current: { start: fmt(start, timezone), end: fmt(end, timezone), label: fmt(start, timezone) + ' t/m ' + fmt(end, timezone) },
77    previous: { start: fmt(prevStart, timezone), end: fmt(prevEnd, timezone), label: fmt(prevStart, timezone) + ' t/m ' + fmt(prevEnd, timezone) }
78  };
79}
80
81function fetchCampaignMetrics(range) {
82  var query =
83    'SELECT campaign.id, campaign.name, campaign.status, campaign.advertising_channel_type, ' +
84    'metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value, ' +
85    'metrics.search_impression_share ' +
86    'FROM campaign ' +
87    "WHERE segments.date BETWEEN '" + range.start + "' AND '" + range.end + "' " +
88    (INCLUDE_PAUSED_WITH_SPEND ? "AND campaign.status IN ('ENABLED', 'PAUSED')" : "AND campaign.status = 'ENABLED'");
89  if (CAMPAIGN_NAME_CONTAINS) {
90    query += " AND campaign.name LIKE '%" + CAMPAIGN_NAME_CONTAINS.replace(/'/g, "\\'") + "%'";
91  }
92
93  var result = {};
94  var rows = AdsApp.report(query).rows();
95  while (rows.hasNext()) {
96    var row = rows.next();
97    var id = row['campaign.id'];
98    var isRaw = row['metrics.search_impression_share'];
99    result[id] = {
100      id: id,
101      name: row['campaign.name'],
102      status: row['campaign.status'],
103      channel: row['campaign.advertising_channel_type'],
104      impressions: Number(row['metrics.impressions']),
105      clicks: Number(row['metrics.clicks']),
106      cost: Number(row['metrics.cost_micros']) / 1000000,
107      conversions: Number(row['metrics.conversions']),
108      convValue: Number(row['metrics.conversions_value']),
109      impressionShare: isRaw === undefined || isRaw === null || isRaw === '' ? null : Number(isRaw)
110    };
111  }
112  return result;
113}
114
115function derive(m) {
116  m.ctr = m.impressions > 0 ? m.clicks / m.impressions : 0;
117  m.cpc = m.clicks > 0 ? m.cost / m.clicks : 0;
118  m.cpa = m.conversions > 0 ? m.cost / m.conversions : 0;
119  m.convRate = m.clicks > 0 ? m.conversions / m.clicks : 0;
120  m.roas = m.cost > 0 ? m.convValue / m.cost : 0;
121  return m;
122}
123
124function emptyMetrics(base) {
125  return { id: base.id, name: base.name, status: base.status, channel: base.channel, impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0, impressionShare: null };
126}
127
128/**
129 * Combineert beide periodes per campagne en berekent de relatieve verandering per metric.
130 */
131function mergeAndCompare(current, previous) {
132  var ids = {};
133  Object.keys(current).forEach(function (id) { ids[id] = true; });
134  Object.keys(previous).forEach(function (id) { ids[id] = true; });
135
136  var metricKeys = ['impressions', 'clicks', 'ctr', 'cpc', 'cost', 'conversions', 'cpa', 'convRate', 'convValue', 'roas'];
137  var rows = [];
138  var totalCur = derive({ impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0 });
139  var totalPrev = derive({ impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0 });
140
141  Object.keys(ids).forEach(function (id) {
142    var cur = derive(current[id] || emptyMetrics(previous[id]));
143    var prev = derive(previous[id] || emptyMetrics(current[id]));
144    if (cur.cost < THRESHOLDS.minCostToShow && prev.cost < THRESHOLDS.minCostToShow) { return; }
145    ['impressions', 'clicks', 'cost', 'conversions', 'convValue'].forEach(function (k) { totalCur[k] += cur[k]; totalPrev[k] += prev[k]; });
146    var changes = {};
147    metricKeys.forEach(function (k) { changes[k] = relChange(cur[k], prev[k]); });
148    rows.push({ name: cur.name, status: cur.status, channel: cur.channel, cur: cur, prev: prev, changes: changes });
149  });
150  rows.sort(function (a, b) { return b.cur.cost - a.cur.cost; });
151
152  derive(totalCur);
153  derive(totalPrev);
154  var totalChanges = {};
155  metricKeys.forEach(function (k) { totalChanges[k] = relChange(totalCur[k], totalPrev[k]); });
156
157  return { rows: rows, total: { cur: totalCur, prev: totalPrev, changes: totalChanges }, metricKeys: metricKeys };
158}
159
160function relChange(cur, prev) {
161  if (prev === 0 && cur === 0) { return 0; }
162  if (prev === 0) { return null; } // nieuw, geen basis
163  return (cur - prev) / prev;
164}
165
166function writeWeekSheet(ss, report, ranges, currency) {
167  var sheetName = 'Week ' + ranges.current.end;
168  var sheet = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName, 0);
169  sheet.clearContents();
170  sheet.clearFormats();
171
172  var header = ['Campagne', 'Status', 'Type',
173    'Impressies', 'Impr. WoW', 'Klikken', 'Klikken WoW', 'CTR', 'CTR WoW', 'CPC', 'CPC WoW',
174    'Kosten (' + currency + ')', 'Kosten WoW', 'Conversies', 'Conv. WoW', 'CPA', 'CPA WoW', 'Conv. ratio', 'Conv. ratio WoW',
175    'Conv. waarde', 'Waarde WoW', 'ROAS', 'ROAS WoW', 'Impression share'];
176  var values = [header];
177  var rowsToColor = [];
178
179  function pushRow(label, status, channel, entry) {
180    var c = entry.cur;
181    var ch = entry.changes;
182    values.push([label, status, channel,
183      c.impressions, pctOrNew(ch.impressions), c.clicks, pctOrNew(ch.clicks), c.ctr, pctOrNew(ch.ctr), round(c.cpc), pctOrNew(ch.cpc),
184      round(c.cost), pctOrNew(ch.cost), round(c.conversions), pctOrNew(ch.conversions), round(c.cpa), pctOrNew(ch.cpa), c.convRate, pctOrNew(ch.convRate),
185      round(c.convValue), pctOrNew(ch.convValue), round(c.roas), pctOrNew(ch.roas), c.impressionShare === null || c.impressionShare === undefined ? '' : c.impressionShare]);
186    rowsToColor.push(ch);
187  }
188
189  pushRow('TOTAAL', '', '', report.total);
190  report.rows.forEach(function (r) { pushRow(r.name, r.status, r.channel, r); });
191
192  sheet.getRange(1, 1, values.length, header.length).setValues(values);
193  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
194  sheet.getRange(2, 1, 1, header.length).setFontWeight('bold').setBackground('#f5f5f5');
195  sheet.setFrozenRows(1);
196  sheet.setFrozenColumns(1);
197  sheet.getRange(1, header.length + 2).setValue('Periode: ' + ranges.current.label + ' vs ' + ranges.previous.label);
198
199  // Kleur de WoW-kolommen.
200  var wowColumns = { 5: 'impressions', 7: 'clicks', 9: 'ctr', 11: 'cpc', 13: 'cost', 15: 'conversions', 17: 'cpa', 19: 'convRate', 21: 'convValue', 23: 'roas' };
201  var percentColumns = [8, 18, 24];
202  percentColumns.forEach(function (col) { if (values.length > 1) { sheet.getRange(2, col, values.length - 1, 1).setNumberFormat('0.00%'); } });
203  Object.keys(wowColumns).forEach(function (colStr) {
204    var col = Number(colStr);
205    for (var i = 0; i < rowsToColor.length; i++) {
206      var change = rowsToColor[i][wowColumns[col]];
207      sheet.getRange(i + 2, col).setBackground(colorFor(wowColumns[col], change));
208    }
209  });
210  sheet.autoResizeColumns(1, 3);
211}
212
213function appendHistory(ss, report, ranges) {
214  var sheet = ss.getSheetByName(HISTORY_SHEET_NAME) || ss.insertSheet(HISTORY_SHEET_NAME);
215  var header = ['Week eindigend op', 'Campagne', 'Impressies', 'Klikken', 'Kosten', 'Conversies', 'Conv. waarde', 'CTR', 'CPC', 'CPA', 'ROAS'];
216  if (sheet.getLastRow() === 0) {
217    sheet.appendRow(header);
218    sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
219    sheet.setFrozenRows(1);
220  }
221  var values = report.rows.map(function (r) {
222    var c = r.cur;
223    return [ranges.current.end, r.name, c.impressions, c.clicks, round(c.cost), round(c.conversions), round(c.convValue), round(c.ctr, 4), round(c.cpc), round(c.cpa), round(c.roas)];
224  });
225  var t = report.total.cur;
226  values.push([ranges.current.end, 'TOTAAL', t.impressions, t.clicks, round(t.cost), round(t.conversions), round(t.convValue), round(t.ctr, 4), round(t.cpc), round(t.cpa), round(t.roas)]);
227  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, header.length).setValues(values);
228}
229
230function sendEmail(account, report, ranges, currency) {
231  if (!EMAIL_RECIPIENTS) { return; }
232  var t = report.total;
233  var subject = '[Google Ads] Weekrapport ' + account.getName() + ' (' + ranges.current.label + ')';
234  var html = '<div style="font-family:Arial,sans-serif;font-size:13px;color:#222">' +
235    '<h2 style="margin-bottom:4px">Weekrapport ' + escapeHtml(account.getName()) + '</h2>' +
236    '<p style="margin-top:0;color:#666">' + ranges.current.label + ' vergeleken met ' + ranges.previous.label + '</p>';
237
238  html += '<table cellpadding="8" style="border-collapse:collapse;margin-bottom:16px">' +
239    kpiCell('Kosten', currency + ' ' + t.cur.cost.toFixed(2), t.changes.cost, 'cost') +
240    kpiCell('Conversies', t.cur.conversions.toFixed(1), t.changes.conversions, 'conversions') +
241    kpiCell('CPA', currency + ' ' + t.cur.cpa.toFixed(2), t.changes.cpa, 'cpa') +
242    kpiCell('ROAS', t.cur.roas.toFixed(2), t.changes.roas, 'roas') +
243    kpiCell('Klikken', String(t.cur.clicks), t.changes.clicks, 'clicks') +
244    kpiCell('CTR', (t.cur.ctr * 100).toFixed(2) + '%', t.changes.ctr, 'ctr') + '</table>';
245
246  html += '<table cellpadding="6" style="border-collapse:collapse;border:1px solid #ddd;font-size:12px">' +
247    '<tr style="background:#f5f5f5"><th align="left">Campagne</th><th>Kosten</th><th>WoW</th><th>Klikken</th><th>WoW</th>' +
248    '<th>Conv.</th><th>WoW</th><th>CPA</th><th>WoW</th><th>ROAS</th><th>WoW</th></tr>';
249  report.rows.slice(0, 40).forEach(function (r) {
250    var c = r.cur;
251    var ch = r.changes;
252    html += '<tr><td>' + escapeHtml(r.name) + (r.status === 'PAUSED' ? ' <span style="color:#999">(gepauzeerd)</span>' : '') + '</td>' +
253      '<td align="right">' + c.cost.toFixed(2) + '</td>' + changeCell(ch.cost, 'cost') +
254      '<td align="right">' + c.clicks + '</td>' + changeCell(ch.clicks, 'clicks') +
255      '<td align="right">' + c.conversions.toFixed(1) + '</td>' + changeCell(ch.conversions, 'conversions') +
256      '<td align="right">' + c.cpa.toFixed(2) + '</td>' + changeCell(ch.cpa, 'cpa') +
257      '<td align="right">' + c.roas.toFixed(2) + '</td>' + changeCell(ch.roas, 'roas') + '</tr>';
258  });
259  html += '</table>';
260  if (report.rows.length > 40) { html += '<p>... en ' + (report.rows.length - 40) + ' campagnes meer in de Sheet.</p>'; }
261  html += '<p><a href="' + SPREADSHEET_URL + '">Open het volledige rapport in Google Sheets</a></p></div>';
262
263  try {
264    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
265    Logger.log('E-mail verstuurd naar ' + EMAIL_RECIPIENTS);
266  } catch (e) {
267    Logger.log('E-mail versturen mislukt: ' + e);
268  }
269}
270
271function kpiCell(label, value, change, metric) {
272  return '<td style="border:1px solid #e0e0e0;border-radius:4px;background:#fafafa"><div style="color:#666;font-size:11px">' + label +
273    '</div><div style="font-size:18px;font-weight:bold">' + value + '</div><div style="color:' + textColorFor(metric, change) + '">' + pctOrNew(change) + '</div></td>';
274}
275
276function changeCell(change, metric) {
277  return '<td align="right" style="background:' + colorFor(metric, change) + '">' + pctOrNew(change) + '</td>';
278}
279
280function colorFor(metric, change) {
281  if (change === null || change === undefined) { return '#ffffff'; }
282  var positive = HIGHER_IS_BETTER[metric] ? change : -change;
283  if (positive >= THRESHOLDS.good) { return '#e6f4ea'; }
284  if (positive <= THRESHOLDS.bad) { return '#fdecea'; }
285  return '#ffffff';
286}
287
288function textColorFor(metric, change) {
289  if (change === null || change === undefined) { return '#666'; }
290  var positive = HIGHER_IS_BETTER[metric] ? change : -change;
291  if (positive >= THRESHOLDS.good) { return '#1e8e3e'; }
292  if (positive <= THRESHOLDS.bad) { return '#c5221f'; }
293  return '#666';
294}
295
296function pctOrNew(change) {
297  if (change === null || change === undefined) { return 'nieuw'; }
298  return (change > 0 ? '+' : '') + (change * 100).toFixed(1) + '%';
299}
300
301function addDays(date, days) {
302  var d = new Date(date.getTime());
303  d.setDate(d.getDate() + days);
304  return d;
305}
306
307function fmt(date, timezone) {
308  return Utilities.formatDate(date, timezone, 'yyyy-MM-dd');
309}
310
311function round(value, decimals) {
312  var f = Math.pow(10, decimals === undefined ? 2 : decimals);
313  return Math.round(Number(value) * f) / f;
314}
315
316function escapeHtml(text) {
317  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
318}
319