Budget pacing alert

/**
 * Budget pacing alert
 * By Pierre Bordier — https://pierrebordier.com
 *
 * Every day, this script compares month-to-date spend with the monthly budget you set,
 * projects end-of-month spend at the current pace, and flags spend spikes from the previous
 * day. It emails you only when a threshold is exceeded (or every day, if you prefer a daily
 * check-in). Read-only: it never modifies the account.
 *
 * INSTALLATION
 * 1. Google Ads > Tools and settings > Bulk actions > Scripts > "+".
 * 2. Paste this whole file, adjust the CONFIG block below.
 * 3. "Preview" for a first test (the log shows the summary), then "Save".
 * 4. Schedule it (clock icon): daily, early in the morning (07:00 for example), so that
 *    yesterday's data is complete.
 *
 * NOTES
 * - Costs are in the account currency. On a manager account (MCC), install the script in
 *   each client account, or adapt it with AdsManagerApp.accounts().
 * - Performance Max campaigns are included (they have their own iterator in Google Ads Scripts).
 */

const CONFIG = {
  // Monthly media budget of the account, in the account currency.
  MONTHLY_BUDGET: 3000,

  // Alert when spend exceeds this percentage of the budget expected to date.
  // 110 = 10% ahead of the normal pace.
  PACE_ALERT_PCT: 110,

  // Alert when yesterday's spend exceeds this percentage of the average of the previous
  // 7 days. 150 = a 50% spike.
  DAILY_SPIKE_PCT: 150,

  // Alert when the end-of-month projection exceeds this percentage of the budget.
  PROJECTION_ALERT_PCT: 105,

  // Recipients, comma-separated.
  EMAIL_RECIPIENTS: 'you@company.com',

  // true: email every day even without an alert (daily check-in).
  // false: email only when a threshold is exceeded.
  ALWAYS_SEND: false,

  // Number of campaigns detailed in the email (highest spend first).
  TOP_CAMPAIGNS: 10,
};

function main() {
  const account = AdsApp.currentAccount();
  const tz = account.getTimeZone();
  const currency = account.getCurrencyCode();
  const now = new Date();

  // Current month boundaries, in the format expected by getStatsFor (yyyyMMdd).
  const monthStart = new Date(now.getFullYear(), now.getMonth(), 1);
  const from = Utilities.formatDate(monthStart, tz, 'yyyyMMdd');
  const to = Utilities.formatDate(now, tz, 'yyyyMMdd');
  const dayOfMonth = parseInt(Utilities.formatDate(now, tz, 'd'), 10);
  const daysInMonth = new Date(now.getFullYear(), now.getMonth() + 1, 0).getDate();

  const costMonth = account.getStatsFor(from, to).getCost();
  const costYesterday = account.getStatsFor('YESTERDAY').getCost();
  const costLast7 = account.getStatsFor('LAST_7_DAYS').getCost();
  const avg7 = costLast7 / 7;

  const expectedToDate = CONFIG.MONTHLY_BUDGET * dayOfMonth / daysInMonth;
  const pacePct = expectedToDate > 0 ? (costMonth / expectedToDate) * 100 : 0;
  const projection = dayOfMonth > 0 ? (costMonth / dayOfMonth) * daysInMonth : 0;
  const projectionPct = CONFIG.MONTHLY_BUDGET > 0 ? (projection / CONFIG.MONTHLY_BUDGET) * 100 : 0;
  const spikePct = avg7 > 0 ? (costYesterday / avg7) * 100 : 0;

  const alerts = [];
  if (pacePct > CONFIG.PACE_ALERT_PCT) {
    alerts.push('Spend pace: ' + pacePct.toFixed(0) + '% of the budget expected to date (threshold ' + CONFIG.PACE_ALERT_PCT + '%).');
  }
  if (projectionPct > CONFIG.PROJECTION_ALERT_PCT) {
    alerts.push('End-of-month projection: ' + fmt(projection) + ' ' + currency + ', i.e. ' + projectionPct.toFixed(0) + '% of the budget (threshold ' + CONFIG.PROJECTION_ALERT_PCT + '%).');
  }
  if (avg7 > 0 && spikePct > CONFIG.DAILY_SPIKE_PCT) {
    alerts.push('Spike yesterday: ' + fmt(costYesterday) + ' ' + currency + ', i.e. ' + spikePct.toFixed(0) + '% of the 7-day average (threshold ' + CONFIG.DAILY_SPIKE_PCT + '%).');
  }

  const campaigns = topCampaigns(from, to, CONFIG.TOP_CAMPAIGNS);

  const summary = [
    'Account ' + account.getName() + ' (' + account.getCustomerId() + ')',
    'Month-to-date spend: ' + fmt(costMonth) + ' ' + currency + ' of ' + fmt(CONFIG.MONTHLY_BUDGET) + ' (day ' + dayOfMonth + '/' + daysInMonth + ')',
    'Expected to date: ' + fmt(expectedToDate) + ' ' + currency + ' -> pace ' + pacePct.toFixed(0) + '%',
    'End-of-month projection: ' + fmt(projection) + ' ' + currency + ' (' + projectionPct.toFixed(0) + '%)',
    'Yesterday: ' + fmt(costYesterday) + ' ' + currency + ' (7-day average: ' + fmt(avg7) + ')',
  ];
  summary.forEach(function (line) { Logger.log(line); });
  alerts.forEach(function (line) { Logger.log('ALERT: ' + line); });

  if (alerts.length === 0 && !CONFIG.ALWAYS_SEND) {
    Logger.log('No alert, no email.');
    return;
  }

  const subject = (alerts.length ? '[Google Ads] Budget alert: ' : '[Google Ads] Budget check-in: ')
    + account.getName() + ' - ' + pacePct.toFixed(0) + '% of pace';
  MailApp.sendEmail({
    to: CONFIG.EMAIL_RECIPIENTS,
    subject: subject,
    htmlBody: buildHtml(summary, alerts, campaigns, currency),
  });
  Logger.log('Email sent to ' + CONFIG.EMAIL_RECIPIENTS);
}

// Standard campaigns + Performance Max, sorted by month-to-date spend.
function topCampaigns(from, to, limit) {
  const rows = [];
  const iterators = [
    AdsApp.campaigns().withCondition('Status = ENABLED').get(),
    AdsApp.performanceMaxCampaigns().withCondition('Status = ENABLED').get(),
  ];
  iterators.forEach(function (it) {
    while (it.hasNext()) {
      const c = it.next();
      const cost = c.getStatsFor(from, to).getCost();
      let daily = null;
      try { daily = c.getBudget().getAmount(); } catch (e) { daily = null; }
      rows.push({ name: c.getName(), cost: cost, daily: daily });
    }
  });
  rows.sort(function (a, b) { return b.cost - a.cost; });
  return rows.slice(0, limit);
}

function buildHtml(summary, alerts, campaigns, currency) {
  let html = '<div style="font-family:Arial,sans-serif;font-size:14px">';
  if (alerts.length) {
    html += '<h2 style="color:#b32d2e;font-size:16px">Alerts</h2><ul>'
      + alerts.map(function (a) { return '<li>' + escapeHtml(a) + '</li>'; }).join('') + '</ul>';
  }
  html += '<h2 style="font-size:16px">Summary</h2><ul>'
    + summary.map(function (s) { return '<li>' + escapeHtml(s) + '</li>'; }).join('') + '</ul>';
  if (campaigns.length) {
    html += '<h2 style="font-size:16px">Highest-spending campaigns this month</h2>'
      + '<table border="1" cellpadding="5" style="border-collapse:collapse"><tr><th align="left">Campaign</th><th align="right">Spend</th><th align="right">Daily budget</th></tr>';
    campaigns.forEach(function (c) {
      html += '<tr><td>' + escapeHtml(c.name) + '</td><td align="right">' + fmt(c.cost) + ' ' + currency + '</td>'
        + '<td align="right">' + (c.daily === null ? '-' : fmt(c.daily) + ' ' + currency) + '</td></tr>';
    });
    html += '</table>';
  }
  html += '<p style="color:#888;font-size:12px">"Budget pacing alert" script by Pierre Bordier, pierrebordier.com. Read-only.</p></div>';
  return html;
}

function fmt(n) {
  return Number(n || 0).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ',');
}

function escapeHtml(s) {
  return String(s).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
}