MCC Budget Overspend Guard

1/**
2 * MCC Budget Overspend Guard
3 *
4 * MCC-script (manageraccount). Leest per klantaccount een maandlimiet uit een Google Sheet
5 * en vergelijkt die met de uitgaven van deze maand. Komt een account boven zijn limiet,
6 * dan worden alle actieve campagnes gepauzeerd en voorzien van een label. Aan het begin van
7 * een nieuwe maand (of zodra de limiet in de Sheet verhoogd is) worden campagnes met dat
8 * label automatisch weer ingeschakeld en wordt het label verwijderd. Standaard staat
9 * DRY_RUN aan: er wordt dan niets gewijzigd, alleen gelogd en gerapporteerd.
10 *
11 * Sheet-indeling (tabblad LIMITS_SHEET_NAME), eerste regel is kopregel:
12 *   A = Customer ID (123-456-7890), B = Maandlimiet (getal), C = Actief (JA/NEE), D = Notitie (optioneel)
13 *
14 * Versie:     1.4.0
15 * Frequentie: Elk uur
16 * Niveau:     MCC (AdsManagerApp)
17 * Licentie:   MIT
18 */
19
20// ===== CONFIGURATIE =====
21
22// Zolang DRY_RUN true is worden campagnes NIET gepauzeerd of ingeschakeld.
23var DRY_RUN = true;
24
25// Google Sheet met limieten per account en het logboek.
26var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/JOUW_SHEET_ID/edit';
27var LIMITS_SHEET_NAME = 'Limieten';
28var LOG_SHEET_NAME = 'Logboek';
29
30// Label dat op gepauzeerde campagnes wordt gezet (wordt per account aangemaakt indien nodig).
31var LABEL_NAMES = {
32  pausedByGuard: 'Overspend Guard - gepauzeerd'
33};
34
35// Waarschuwingsdrempel: e-mail/Slack als uitgaven boven dit percentage van de limiet komen (nog geen pauze).
36var THRESHOLDS = {
37  warnAtFraction: 0.90,
38  pauseAtFraction: 1.00
39};
40
41// Herinschakelen aan het begin van de maand: alleen op dag <= REENABLE_UNTIL_DAY, of altijd als de uitgaven
42// weer onder de limiet liggen (bv. na verhoging van de limiet in de Sheet).
43var REENABLE_UNTIL_DAY = 3;
44var REENABLE_WHEN_UNDER_LIMIT = true;
45
46// executeInParallel verwerkt maximaal 50 accounts per run. Gebruik meerdere Sheets/scripts bij meer accounts.
47var MAX_ACCOUNTS = 50;
48
49// Meldingen.
50var EMAIL_RECIPIENTS = 'ads@voorbeeld.nl';
51var SLACK_WEBHOOK_URL = '';
52
53// ===== EINDE CONFIGURATIE =====
54
55function main() {
56  try {
57    var preview = AdsApp.getExecutionInfo().isPreview();
58    var effectiveDryRun = DRY_RUN || preview;
59    Logger.log('Overspend guard | DRY_RUN=' + effectiveDryRun + (preview ? ' (preview)' : ''));
60
61    var limits = readLimits();
62    var ids = Object.keys(limits);
63    if (!ids.length) {
64      Logger.log('Geen actieve limieten gevonden in de Sheet.');
65      return;
66    }
67    Logger.log(ids.length + ' accounts met limiet gelezen.');
68    if (ids.length > MAX_ACCOUNTS) {
69      Logger.log('Waarschuwing: meer dan ' + MAX_ACCOUNTS + ' accounts; alleen de eerste ' + MAX_ACCOUNTS + ' worden verwerkt.');
70      ids = ids.slice(0, MAX_ACCOUNTS);
71    }
72
73    var selector = AdsManagerApp.accounts().withIds(ids).withLimit(MAX_ACCOUNTS);
74    var found = selector.get().totalNumEntities();
75    if (found !== ids.length) {
76      Logger.log('Let op: ' + (ids.length - found) + ' Customer ID(\'s) uit de Sheet niet gevonden onder dit MCC.');
77    }
78    selector.executeInParallel('guardAccount', 'afterAllAccounts', JSON.stringify({ limits: limits, dryRun: effectiveDryRun }));
79  } catch (e) {
80    Logger.log('FOUT in mcc-budget-overspend-guard (main): ' + e + (e.stack ? '\n' + e.stack : ''));
81    throw e;
82  }
83}
84
85/**
86 * Leest de limieten uit de Sheet. Retourneert { '123-456-7890': { limit: 1000, note: '' } }.
87 */
88function readLimits() {
89  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
90  var sheet = ss.getSheetByName(LIMITS_SHEET_NAME);
91  if (!sheet) { throw new Error('Tabblad "' + LIMITS_SHEET_NAME + '" niet gevonden in de Sheet.'); }
92  var values = sheet.getDataRange().getValues();
93  var limits = {};
94  for (var i = 1; i < values.length; i++) {
95    var id = normalizeCustomerId(values[i][0]);
96    var limit = Number(values[i][1]);
97    var active = String(values[i][2] || 'JA').trim().toUpperCase();
98    if (!id || isNaN(limit) || limit <= 0) { continue; }
99    if (active === 'NEE' || active === 'NO' || active === 'FALSE') { continue; }
100    limits[id] = { limit: limit, note: String(values[i][3] || '') };
101  }
102  return limits;
103}
104
105function normalizeCustomerId(value) {
106  var digits = String(value || '').replace(/\D/g, '');
107  if (digits.length !== 10) { return null; }
108  return digits.substr(0, 3) + '-' + digits.substr(3, 3) + '-' + digits.substr(6, 4);
109}
110
111/**
112 * Wordt per klantaccount uitgevoerd. Bepaalt uitgaven deze maand en pauzeert/herstelt campagnes.
113 */
114function guardAccount(input) {
115  var params = JSON.parse(input);
116  var account = AdsApp.currentAccount();
117  var customerId = account.getCustomerId();
118  var config = params.limits[customerId];
119  var dryRun = params.dryRun;
120  var timezone = account.getTimeZone();
121  var dayOfMonth = Number(Utilities.formatDate(new Date(), timezone, 'd'));
122
123  var result = {
124    customerId: customerId,
125    name: account.getName(),
126    currency: account.getCurrencyCode(),
127    limit: config ? config.limit : 0,
128    spend: 0,
129    fraction: 0,
130    action: 'geen',
131    pausedCount: 0,
132    reenabledCount: 0,
133    labeledActive: 0,
134    error: ''
135  };
136  if (!config) {
137    result.error = 'Geen limiet gevonden voor dit account.';
138    return JSON.stringify(result);
139  }
140
141  try {
142    result.spend = fetchMonthSpend();
143    result.fraction = result.limit > 0 ? result.spend / result.limit : 0;
144
145    var labeledCampaigns = getCampaignsWithLabel(LABEL_NAMES.pausedByGuard);
146    result.labeledActive = labeledCampaigns.length;
147
148    if (result.fraction >= THRESHOLDS.pauseAtFraction) {
149      // Boven limiet: pauzeer alle actieve campagnes.
150      result.action = 'pauzeren';
151      result.pausedCount = pauseEnabledCampaigns(dryRun);
152    } else if (labeledCampaigns.length > 0 && (dayOfMonth <= REENABLE_UNTIL_DAY || REENABLE_WHEN_UNDER_LIMIT)) {
153      // Nieuwe maand of limiet verhoogd: herstel de eerder gepauzeerde campagnes.
154      result.action = 'herstellen';
155      result.reenabledCount = reenableCampaigns(labeledCampaigns, dryRun);
156    } else if (result.fraction >= THRESHOLDS.warnAtFraction) {
157      result.action = 'waarschuwing';
158    }
159  } catch (e) {
160    result.error = String(e);
161    Logger.log('Fout in account ' + customerId + ': ' + e);
162  }
163  return JSON.stringify(result);
164}
165
166function fetchMonthSpend() {
167  var rows = AdsApp.report('SELECT metrics.cost_micros FROM customer WHERE segments.date DURING THIS_MONTH').rows();
168  var total = 0;
169  while (rows.hasNext()) { total += Number(rows.next()['metrics.cost_micros']) / 1000000; }
170  return total;
171}
172
173function ensureLabel(name) {
174  var exists = AdsApp.labels().withCondition("label.name = '" + name.replace(/'/g, "\\'") + "'").get().hasNext();
175  if (!exists) {
176    AdsApp.createLabel(name, 'Automatisch aangemaakt door mcc-budget-overspend-guard.js', '#E53935');
177  }
178}
179
180function getCampaignsWithLabel(labelName) {
181  var list = [];
182  var labels = AdsApp.labels().withCondition("label.name = '" + labelName.replace(/'/g, "\\'") + "'").get();
183  if (!labels.hasNext()) { return list; }
184  var campaigns = labels.next().campaigns().get();
185  while (campaigns.hasNext()) { list.push(campaigns.next()); }
186  return list;
187}
188
189/**
190 * Pauzeert alle actieve campagnes (Search, Shopping, PMax, Display, Video) en labelt ze.
191 */
192function pauseEnabledCampaigns(dryRun) {
193  var count = 0;
194  if (!dryRun) { ensureLabel(LABEL_NAMES.pausedByGuard); }
195  var iterators = [
196    AdsApp.campaigns().withCondition("campaign.status = 'ENABLED'").get(),
197    AdsApp.performanceMaxCampaigns().withCondition("campaign.status = 'ENABLED'").get(),
198    AdsApp.shoppingCampaigns().withCondition("campaign.status = 'ENABLED'").get(),
199    AdsApp.videoCampaigns().withCondition("campaign.status = 'ENABLED'").get()
200  ];
201  var seen = {};
202  iterators.forEach(function (it) {
203    while (it.hasNext()) {
204      var campaign = it.next();
205      if (seen[campaign.getId()]) { continue; }
206      seen[campaign.getId()] = true;
207      Logger.log((dryRun ? '[DRY RUN] ' : '') + 'Pauzeren: ' + campaign.getName());
208      if (!dryRun) {
209        campaign.pause();
210        campaign.applyLabel(LABEL_NAMES.pausedByGuard);
211      }
212      count++;
213    }
214  });
215  return count;
216}
217
218function reenableCampaigns(campaigns, dryRun) {
219  var count = 0;
220  campaigns.forEach(function (campaign) {
221    Logger.log((dryRun ? '[DRY RUN] ' : '') + 'Herstellen: ' + campaign.getName());
222    if (!dryRun) {
223      campaign.enable();
224      campaign.removeLabel(LABEL_NAMES.pausedByGuard);
225    }
226    count++;
227  });
228  return count;
229}
230
231/**
232 * Na alle accounts: logboek bijwerken en meldingen versturen.
233 */
234function afterAllAccounts(results) {
235  try {
236    var accounts = [];
237    results.forEach(function (res) {
238      if (res.getStatus() !== 'OK') {
239        accounts.push({ customerId: res.getCustomerId(), name: '(niet verwerkt)', error: 'Status ' + res.getStatus() + ': ' + res.getError(), limit: 0, spend: 0, fraction: 0, action: 'fout', pausedCount: 0, reenabledCount: 0, currency: '' });
240        return;
241      }
242      accounts.push(JSON.parse(res.getReturnValue()));
243    });
244    accounts.sort(function (a, b) { return b.fraction - a.fraction; });
245
246    var dryRun = DRY_RUN || AdsApp.getExecutionInfo().isPreview();
247    accounts.forEach(function (a) {
248      Logger.log(a.name + ' (' + a.customerId + '): ' + a.spend.toFixed(2) + ' / ' + a.limit + ' (' + (a.fraction * 100).toFixed(0) + '%) -> ' + a.action +
249        (a.pausedCount ? ', ' + a.pausedCount + ' gepauzeerd' : '') + (a.reenabledCount ? ', ' + a.reenabledCount + ' hersteld' : '') + (a.error ? ' | FOUT: ' + a.error : ''));
250    });
251
252    writeLog(accounts, dryRun);
253    var notable = accounts.filter(function (a) { return a.action !== 'geen' || a.error; });
254    if (notable.length) {
255      sendEmail(notable, dryRun);
256      sendSlack(notable, dryRun);
257    } else {
258      Logger.log('Alle accounts binnen budget; geen meldingen.');
259    }
260  } catch (e) {
261    Logger.log('FOUT in afterAllAccounts: ' + e + (e.stack ? '\n' + e.stack : ''));
262    throw e;
263  }
264}
265
266function writeLog(accounts, dryRun) {
267  try {
268    var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
269    var sheet = ss.getSheetByName(LOG_SHEET_NAME) || ss.insertSheet(LOG_SHEET_NAME);
270    var header = ['Tijdstip', 'Modus', 'Account', 'Customer ID', 'Uitgaven deze maand', 'Limiet', 'Percentage', 'Actie', 'Gepauzeerd', 'Hersteld', 'Fout'];
271    if (sheet.getLastRow() === 0) {
272      sheet.appendRow(header);
273      sheet.getRange(1, 1, 1, header.length).setFontWeight('bold').setBackground('#e8eaf6');
274      sheet.setFrozenRows(1);
275    }
276    var now = new Date();
277    var values = accounts.map(function (a) {
278      return [now, dryRun ? 'DRY RUN' : 'LIVE', a.name, a.customerId, round(a.spend), a.limit, a.fraction, a.action, a.pausedCount || 0, a.reenabledCount || 0, a.error || ''];
279    });
280    var start = sheet.getLastRow() + 1;
281    sheet.getRange(start, 1, values.length, header.length).setValues(values);
282    sheet.getRange(start, 7, values.length, 1).setNumberFormat('0%');
283  } catch (e) {
284    Logger.log('Logboek schrijven mislukt: ' + e);
285  }
286}
287
288function sendEmail(notable, dryRun) {
289  if (!EMAIL_RECIPIENTS) { return; }
290  var paused = notable.filter(function (a) { return a.action === 'pauzeren'; }).length;
291  var subject = '[MCC] Overspend guard: ' + paused + ' account(s) boven limiet' + (dryRun ? ' (dry run)' : '');
292  var html = '<div style="font-family:Arial,sans-serif;font-size:13px">' + (dryRun ? '<p><b>Dry run: er is niets gewijzigd.</b></p>' : '') +
293    '<table cellpadding="5" style="border-collapse:collapse;border:1px solid #ddd"><tr style="background:#f5f5f5"><th align="left">Account</th><th>Uitgaven</th><th>Limiet</th><th>%</th><th align="left">Actie</th></tr>';
294  notable.forEach(function (a) {
295    var color = a.action === 'pauzeren' ? '#fdecea' : (a.action === 'waarschuwing' ? '#fff3e0' : (a.action === 'herstellen' ? '#e6f4ea' : '#ffffff'));
296    html += '<tr style="background:' + color + '"><td>' + escapeHtml(a.name) + '<br><span style="color:#888">' + a.customerId + '</span></td><td align="right">' + a.spend.toFixed(2) +
297      '</td><td align="right">' + a.limit + '</td><td align="right">' + (a.fraction * 100).toFixed(0) + '%</td><td>' + escapeHtml(a.action) +
298      (a.pausedCount ? ' (' + a.pausedCount + ' campagnes)' : '') + (a.reenabledCount ? ' (' + a.reenabledCount + ' campagnes)' : '') + (a.error ? '<br>' + escapeHtml(a.error) : '') + '</td></tr>';
299  });
300  html += '</table><p><a href="' + SPREADSHEET_URL + '">Limieten en logboek</a></p></div>';
301  try {
302    MailApp.sendEmail({ to: EMAIL_RECIPIENTS, subject: subject, htmlBody: html });
303    Logger.log('E-mail verstuurd naar ' + EMAIL_RECIPIENTS);
304  } catch (e) {
305    Logger.log('E-mail versturen mislukt: ' + e);
306  }
307}
308
309function sendSlack(notable, dryRun) {
310  if (!SLACK_WEBHOOK_URL) { return; }
311  var text = ':moneybag: *Overspend guard' + (dryRun ? ' (dry run)' : '') + '*\n' + notable.map(function (a) {
312    return '- ' + a.name + ': ' + a.spend.toFixed(0) + ' / ' + a.limit + ' (' + (a.fraction * 100).toFixed(0) + '%) -> ' + a.action;
313  }).join('\n');
314  try {
315    UrlFetchApp.fetch(SLACK_WEBHOOK_URL, { method: 'post', contentType: 'application/json', payload: JSON.stringify({ text: text }), muteHttpExceptions: true });
316  } catch (e) {
317    Logger.log('Slack versturen mislukt: ' + e);
318  }
319}
320
321function round(value) {
322  return Math.round(Number(value) * 100) / 100;
323}
324
325function escapeHtml(text) {
326  return String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
327}
328