MCC Account Health Dashboard

1/**
2 * MCC Account Health Dashboard
3 *
4 * MCC-script (manageraccount). Loopt parallel door alle (of gefilterde) klantaccounts en
5 * verzamelt per account: kosten, klikken, conversies, conversiewaarde, CPA, ROAS,
6 * verloren vertoningsaandeel door budget, aantal afgekeurde advertenties, aantal actieve
7 * campagnes en de laatste dag met uitgaven. Per run wordt één nieuw overzichtstabblad
8 * in de Google Sheet geschreven (plus een historietabblad), met kleurcodering op basis
9 * van drempels. Het script wijzigt niets in de accounts.
10 *
11 * Versie:     1.3.0
12 * Frequentie: Dagelijks
13 * Niveau:     MCC (AdsManagerApp)
14 * Licentie:   MIT
15 */
16
17// ===== CONFIGURATIE =====
18
19// Google Sheet voor het dashboard.
20var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
21var HISTORY_SHEET_NAME = 'Historie';
22
23// Periode voor de prestaties.
24var DATE_RANGE = 'LAST_7_DAYS';
25
26// Filter op accounts (optioneel). Leeg = alle accounts onder het MCC.
27// Voorbeeld label: 'Actieve klanten'. Voorbeeld ID's: ['123-456-7890', '234-567-8901'].
28var ACCOUNT_LABEL = '';
29var ACCOUNT_IDS = [];
30
31// executeInParallel verwerkt maximaal 50 accounts per run. Bij meer accounts: gebruik ACCOUNT_LABEL
32// om te splitsen in groepen, of plan meerdere kopieën van dit script met verschillende labels.
33var MAX_ACCOUNTS = 50;
34
35// Drempels voor kleurcodering en "aandacht"-markering.
36var THRESHOLDS = {
37  minRoas: 3,                 // ROAS onder deze waarde = rood (alleen als er conversiewaarde is)
38  maxCpa: 100,                // CPA boven deze waarde = rood
39  budgetLostIs: 0.20,         // > 20% IS verloren door budget = oranje
40  maxDisapprovedAds: 0,       // meer afgekeurde ads dan dit = rood
41  noSpendDays: 2              // geen uitgaven in de laatste N dagen = rood (account staat mogelijk stil)
42};
43
44// E-mail met samenvatting van accounts die aandacht nodig hebben. Leeg = geen e-mail.
45var EMAIL_RECIPIENTS = 'ads@voorbeeld.nl';
46var SLACK_WEBHOOK_URL = '';
47
48// ===== EINDE CONFIGURATIE =====
49
50function main() {
51  try {
52    var selector = AdsManagerApp.accounts();
53    if (ACCOUNT_IDS.length) { selector = selector.withIds(ACCOUNT_IDS); }
54    if (ACCOUNT_LABEL) { selector = selector.withCondition("customer_client.applied_labels CONTAINS ANY ('" + ACCOUNT_LABEL.replace(/'/g, "\\'") + "')"); }
55    selector = selector.withLimit(MAX_ACCOUNTS);
56
57    var count = selector.get().totalNumEntities();
58    Logger.log(count + ' accounts geselecteerd (max ' + MAX_ACCOUNTS + ').');
59    if (!count) { return; }
60
61    selector.executeInParallel('collectAccountHealth', 'afterAllAccounts', JSON.stringify({ dateRange: DATE_RANGE, noSpendDays: THRESHOLDS.noSpendDays }));
62  } catch (e) {
63    Logger.log('FOUT in mcc-account-health-dashboard (main): ' + e + (e.stack ? '\n' + e.stack : ''));
64    throw e;
65  }
66}
67
68/**
69 * Wordt per klantaccount uitgevoerd (parallel). Moet een string teruggeven.
70 */
71function collectAccountHealth(input) {
72  var params = JSON.parse(input);
73  var account = AdsApp.currentAccount();
74  var result = {
75    customerId: account.getCustomerId(),
76    name: account.getName(),
77    currency: account.getCurrencyCode(),
78    impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0,
79    budgetLostIs: null,
80    disapprovedAds: 0,
81    enabledCampaigns: 0,
82    lastSpendDate: '',
83    error: ''
84  };
85
86  try {
87    // Prestaties + gewogen verloren IS door budget over Search-campagnes.
88    var perf = AdsApp.report(
89      'SELECT campaign.advertising_channel_type, metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value, ' +
90      'metrics.search_budget_lost_impression_share ' +
91      'FROM campaign WHERE segments.date DURING ' + params.dateRange
92    ).rows();
93    var lostWeighted = 0;
94    var searchImpressions = 0;
95    while (perf.hasNext()) {
96      var r = perf.next();
97      var impr = Number(r['metrics.impressions']);
98      result.impressions += impr;
99      result.clicks += Number(r['metrics.clicks']);
100      result.cost += Number(r['metrics.cost_micros']) / 1000000;
101      result.conversions += Number(r['metrics.conversions']);
102      result.convValue += Number(r['metrics.conversions_value']);
103      var lost = r['metrics.search_budget_lost_impression_share'];
104      if (r['campaign.advertising_channel_type'] === 'SEARCH' && lost !== '' && lost !== null && lost !== undefined && impr > 0) {
105        lostWeighted += Number(lost) * impr;
106        searchImpressions += impr;
107      }
108    }
109    result.budgetLostIs = searchImpressions > 0 ? lostWeighted / searchImpressions : null;
110
111    // Actieve campagnes.
112    var camp = AdsApp.report("SELECT campaign.id FROM campaign WHERE campaign.status = 'ENABLED'").rows();
113    while (camp.hasNext()) { camp.next(); result.enabledCampaigns++; }
114
115    // Afgekeurde advertenties in actieve campagnes/adgroepen.
116    var dis = AdsApp.report(
117      "SELECT ad_group_ad.ad.id FROM ad_group_ad WHERE ad_group_ad.status = 'ENABLED' AND ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED' " +
118      "AND ad_group_ad.policy_summary.approval_status = 'DISAPPROVED'"
119    ).rows();
120    while (dis.hasNext()) { dis.next(); result.disapprovedAds++; }
121
122    // Laatste dag met uitgaven (afgelopen 14 dagen).
123    var daily = AdsApp.report(
124      'SELECT segments.date, metrics.cost_micros FROM customer WHERE segments.date DURING LAST_14_DAYS AND metrics.cost_micros > 0 ORDER BY segments.date DESC'
125    ).rows();
126    if (daily.hasNext()) { result.lastSpendDate = daily.next()['segments.date']; }
127  } catch (e) {
128    result.error = String(e);
129    Logger.log('Fout in account ' + result.customerId + ': ' + e);
130  }
131  return JSON.stringify(result);
132}
133
134/**
135 * Wordt éénmaal uitgevoerd na alle accounts (in MCC-context).
136 */
137function afterAllAccounts(results) {
138  try {
139    var accounts = [];
140    results.forEach(function (res) {
141      if (res.getStatus() !== 'OK') {
142        accounts.push({ customerId: res.getCustomerId(), name: '(niet verwerkt)', error: 'Status ' + res.getStatus() + ': ' + res.getError(),
143          impressions: 0, clicks: 0, cost: 0, conversions: 0, convValue: 0, budgetLostIs: null, disapprovedAds: 0, enabledCampaigns: 0, lastSpendDate: '', currency: '' });
144        return;
145      }
146      accounts.push(JSON.parse(res.getReturnValue()));
147    });
148
149    var timezone = AdsApp.currentAccount().getTimeZone();
150    var today = new Date();
151    var todayStr = Utilities.formatDate(today, timezone, 'yyyy-MM-dd');
152    accounts.forEach(function (a) { enrich(a, today, timezone); });
153    accounts.sort(function (a, b) { return b.cost - a.cost; });
154
155    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
156    writeRunSheet(ss, accounts, todayStr);
157    appendHistory(ss, accounts, todayStr);
158
159    var attention = accounts.filter(function (a) { return a.flags.length > 0; });
160    Logger.log(accounts.length + ' accounts verwerkt, ' + attention.length + ' met aandachtspunten.');
161    attention.forEach(function (a) { Logger.log(a.name + ' (' + a.customerId + '): ' + a.flags.join(' | ')); });
162    sendEmail(attention, accounts.length, todayStr);
163    sendSlack(attention, todayStr);
164  } catch (e) {
165    Logger.log('FOUT in afterAllAccounts: ' + e + (e.stack ? '\n' + e.stack : ''));
166    throw e;
167  }
168}
169
170/**
171 * Berekent afgeleide metrics en aandachtspunten per account.
172 */
173function enrich(a, today, timezone) {
174  a.cpa = a.conversions > 0 ? a.cost / a.conversions : null;
175  a.roas = a.cost > 0 ? a.convValue / a.cost : null;
176  a.ctr = a.impressions > 0 ? a.clicks / a.impressions : 0;
177  a.flags = [];
178  if (a.error) { a.flags.push('Fout: ' + a.error); }
179  if (a.cost > 0 && a.cpa !== null && a.cpa > THRESHOLDS.maxCpa) { a.flags.push('CPA ' + a.cpa.toFixed(0) + ' > ' + THRESHOLDS.maxCpa); }
180  if (a.cost > 0 && a.convValue > 0 && a.roas !== null && a.roas < THRESHOLDS.minRoas) { a.flags.push('ROAS ' + a.roas.toFixed(1) + ' < ' + THRESHOLDS.minRoas); }
181  if (a.budgetLostIs !== null && a.budgetLostIs > THRESHOLDS.budgetLostIs) { a.flags.push('Budget lost IS ' + (a.budgetLostIs * 100).toFixed(0) + '%'); }
182  if (a.disapprovedAds > THRESHOLDS.maxDisapprovedAds) { a.flags.push(a.disapprovedAds + ' afgekeurde ads'); }
183  if (a.enabledCampaigns > 0) {
184    var daysSinceSpend = a.lastSpendDate ? Math.round((today.getTime() - new Date(a.lastSpendDate + 'T00:00:00').getTime()) / 86400000) : 99;
185    if (daysSinceSpend > THRESHOLDS.noSpendDays) { a.flags.push('Geen uitgaven sinds ' + (a.lastSpendDate || '> 14 dagen')); }
186  }
187}
188
189function writeRunSheet(ss, accounts, todayStr) {
190  var name = 'Run ' + todayStr;
191  var sheet = ss.getSheetByName(name) || ss.insertSheet(name, 0);
192  sheet.clearContents();
193  sheet.clearFormats();
194  var header = ['Account', 'Customer ID', 'Valuta', 'Actieve campagnes', 'Impressies', 'Klikken', 'CTR', 'Kosten', 'Conversies', 'CPA', 'Conv. waarde', 'ROAS',
195    'Budget lost IS', 'Afgekeurde ads', 'Laatste uitgave', 'Aandachtspunten'];
196  var values = [header].concat(accounts.map(function (a) {
197    return [a.name, a.customerId, a.currency, a.enabledCampaigns, a.impressions, a.clicks, a.ctr, round(a.cost), round(a.conversions),
198      a.cpa === null ? '' : round(a.cpa), round(a.convValue), a.roas === null ? '' : round(a.roas), a.budgetLostIs === null ? '' : a.budgetLostIs,
199      a.disapprovedAds, a.lastSpendDate, a.flags.join(' | ')];
200  }));
201  sheet.getRange(1, 1, values.length, header.length).setValues(values);
202  sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
203  sheet.setFrozenRows(1);
204  sheet.setFrozenColumns(1);
205  if (values.length > 1) {
206    sheet.getRange(2, 7, values.length - 1, 1).setNumberFormat('0.00%');
207    sheet.getRange(2, 13, values.length - 1, 1).setNumberFormat('0%');
208  }
209  for (var i = 0; i < accounts.length; i++) {
210    var a = accounts[i];
211    var row = i + 2;
212    if (a.cpa !== null && a.cpa > THRESHOLDS.maxCpa) { sheet.getRange(row, 10).setBackground('#fdecea'); }
213    if (a.roas !== null && a.convValue > 0 && a.roas < THRESHOLDS.minRoas) { sheet.getRange(row, 12).setBackground('#fdecea'); }
214    if (a.budgetLostIs !== null && a.budgetLostIs > THRESHOLDS.budgetLostIs) { sheet.getRange(row, 13).setBackground('#fff3e0'); }
215    if (a.disapprovedAds > THRESHOLDS.maxDisapprovedAds) { sheet.getRange(row, 14).setBackground('#fdecea'); }
216    if (a.flags.length) { sheet.getRange(row, 16).setBackground('#fff8e1'); }
217  }
218  sheet.getRange(1, header.length + 2).setValue('Periode ' + DATE_RANGE + ' | gegenereerd ' + new Date());
219}
220
221function appendHistory(ss, accounts, todayStr) {
222  var sheet = ss.getSheetByName(HISTORY_SHEET_NAME) || ss.insertSheet(HISTORY_SHEET_NAME);
223  var header = ['Datum', 'Periode', 'Account', 'Customer ID', 'Kosten', 'Conversies', 'CPA', 'Conv. waarde', 'ROAS', 'Budget lost IS', 'Afgekeurde ads', 'Aandachtspunten'];
224  if (sheet.getLastRow() === 0) {
225    sheet.appendRow(header);
226    sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
227    sheet.setFrozenRows(1);
228  }
229  var values = accounts.map(function (a) {
230    return [todayStr, DATE_RANGE, a.name, a.customerId, round(a.cost), round(a.conversions), a.cpa === null ? '' : round(a.cpa), round(a.convValue),
231      a.roas === null ? '' : round(a.roas), a.budgetLostIs === null ? '' : round(a.budgetLostIs), a.disapprovedAds, a.flags.join(' | ')];
232  });
233  if (values.length) { sheet.getRange(sheet.getLastRow() + 1, 1, values.length, header.length).setValues(values); }
234}
235
236function sendEmail(attention, total, todayStr) {
237  if (!EMAIL_RECIPIENTS) { return; }
238  var subject = '[MCC] Account health ' + todayStr + ': ' + attention.length + ' van ' + total + ' accounts met aandachtspunten';
239  var html = '<div style="font-family:Arial,sans-serif;font-size:13px"><p>' + attention.length + ' van ' + total + ' accounts hebben aandachtspunten (periode ' + DATE_RANGE + ').</p>';
240  if (attention.length) {
241    html += '<table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd"><tr style="background:#f5f5f5"><th align="left">Account</th><th>Kosten</th><th>Conv.</th><th>CPA</th><th>ROAS</th><th align="left">Aandachtspunten</th></tr>';
242    attention.forEach(function (a) {
243      html += '<tr><td>' + escapeHtml(a.name) + '<br><span style="color:#888">' + a.customerId + '</span></td><td align="right">' + a.cost.toFixed(2) + '</td><td align="right">' +
244        a.conversions.toFixed(1) + '</td><td align="right">' + (a.cpa === null ? '-' : a.cpa.toFixed(2)) + '</td><td align="right">' + (a.roas === null ? '-' : a.roas.toFixed(2)) +
245        '</td><td style="background:#fff8e1">' + escapeHtml(a.flags.join(' | ')) + '</td></tr>';
246    });
247    html += '</table>';
248  }
249  html += '<p><a href="' + SPREADSHEET_URL + '">Open het dashboard</a></p></div>';
250  try {
251    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
252    Logger.log('E-mail verstuurd naar ' + EMAIL_RECIPIENTS);
253  } catch (e) {
254    Logger.log('E-mail versturen mislukt: ' + e);
255  }
256}
257
258function sendSlack(attention, todayStr) {
259  if (!SLACK_WEBHOOK_URL || !attention.length) { return; }
260  var text = ':hospital: *MCC account health ' + todayStr + '*\n' + attention.slice(0, 20).map(function (a) { return '- ' + a.name + ': ' + a.flags.join(' | '); }).join('\n');
261  try {
262    UrlFetchApp.fetch(SLACK_WEBHOOK_URL, { method: 'post', contentType: 'application/json', payload: JSON.stringify({ text: text }), muteHttpExceptions: true });
263  } catch (e) {
264    Logger.log('Slack versturen mislukt: ' + e);
265  }
266}
267
268function round(value) {
269  return Math.round(Number(value) * 100) / 100;
270}
271
272function escapeHtml(text) {
273  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
274}
275