Budget pacing monitor

// Budget pacing monitor
// Compares month-to-date spend against a monthly budget and projects the month end.
// Reads through GAQL FROM campaign, so Performance Max and Demand Gen are included.
// Optionally weights the expected line by weekday, so a weekday-heavy account does not
// look alarmingly behind every Monday.
// Read-only: it reports, it changes no budget.

var MONTHLY_BUDGET = 0;      // required, in the account currency
var ALERT_ABOVE    = 1.15;   // pace above this triggers an alert
var ALERT_BELOW    = 0.85;   // pace below this triggers an alert
var SKIP_FIRST_DAYS = 4;     // early in the month one day distorts the ratio
var WEEKDAY_WEIGHTS = true;  // false uses a flat expected line
var EMAIL = '';              // leave empty to log only

function main() {
  if (!MONTHLY_BUDGET) {
    Logger.log('Set MONTHLY_BUDGET at the top of the script.');
    return;
  }

  var tz    = AdsApp.currentAccount().getTimeZone();
  var now   = new Date();
  var year  = Number(Utilities.formatDate(now, tz, 'yyyy'));
  var month = Number(Utilities.formatDate(now, tz, 'MM'));
  var dayOfMonth = Number(Utilities.formatDate(now, tz, 'dd'));

  var daysInMonth = new Date(year, month, 0).getDate();
  var daysDone    = dayOfMonth - 1;   // today is still spending, do not count it

  if (daysDone < SKIP_FIRST_DAYS) {
    Logger.log('Day ' + dayOfMonth + '. Too early in the month to pace.');
    return;
  }

  var from = year + '-' + pad(month) + '-01';
  var to   = Utilities.formatDate(new Date(now.getTime() - 86400000), tz, 'yyyy-MM-dd');

  var spend = spendByCampaign(from, to);
  var total = 0;
  for (var name in spend) { total += spend[name]; }

  var doneShare = WEEKDAY_WEIGHTS
    ? weightedShare(year, month, daysDone, daysInMonth, tz)
    : (daysDone / daysInMonth);

  var expected   = MONTHLY_BUDGET * doneShare;
  var pace       = expected > 0 ? (total / expected) : 0;
  var projected  = doneShare > 0 ? (total / doneShare) : 0;
  var remaining  = MONTHLY_BUDGET - total;
  var daysLeft   = daysInMonth - daysDone;
  var dailyToFit = daysLeft > 0 ? (remaining / daysLeft) : 0;

  var lines = [];
  lines.push(AdsApp.currentAccount().getName());
  lines.push(from + ' to ' + to + '  (' + daysDone + ' of ' + daysInMonth + ' days)');
  lines.push('');
  lines.push('Budget      ' + MONTHLY_BUDGET.toFixed(2));
  lines.push('Spent       ' + total.toFixed(2));
  lines.push('Expected    ' + expected.toFixed(2) + (WEEKDAY_WEIGHTS ? '  (weekday weighted)' : ''));
  lines.push('Pace        ' + pace.toFixed(2));
  lines.push('Projected   ' + projected.toFixed(2));
  lines.push('To finish on budget, spend ' + dailyToFit.toFixed(2) + ' per day for the remaining ' + daysLeft + ' days.');
  lines.push('');

  var names = Object.keys(spend).sort(function (a, b) { return spend[b] - spend[a]; });
  lines.push('campaign\tspend\tshare');
  for (var i = 0; i < names.length; i++) {
    var share = total > 0 ? (spend[names[i]] / total * 100) : 0;
    lines.push(names[i] + '\t' + spend[names[i]].toFixed(2) + '\t' + share.toFixed(1) + '%');
  }

  var body = lines.join('\n');
  Logger.log(body);

  var breached = (pace >= ALERT_ABOVE || pace <= ALERT_BELOW);
  if (EMAIL && breached) {
    var word = pace >= ALERT_ABOVE ? 'ahead' : 'behind';
    MailApp.sendEmail(
      EMAIL,
      'Pacing ' + word + ' (' + pace.toFixed(2) + ') - ' + AdsApp.currentAccount().getName(),
      body
    );
  }
}

function spendByCampaign(from, to) {
  var out  = {};
  var rows = AdsApp.search(
    'SELECT campaign.name, metrics.cost_micros ' +
    'FROM campaign ' +
    'WHERE segments.date BETWEEN "' + from + '" AND "' + to + '" ' +
    '  AND campaign.status != "REMOVED"'
  );
  while (rows.hasNext()) {
    var r = rows.next();
    var n = String(r.campaign.name);
    out[n] = (out[n] || 0) + Number(r.metrics.costMicros) / 1000000;
  }
  return out;
}

// Derives the share of a normal month that the elapsed days represent,
// using the account's own weekday spend pattern over the last eight weeks.
function weightedShare(year, month, daysDone, daysInMonth, tz) {
  var weights = weekdayWeights(tz);
  if (!weights) { return daysDone / daysInMonth; }

  var done = 0, all = 0;
  for (var d = 1; d <= daysInMonth; d++) {
    var w = weights[new Date(year, month - 1, d).getDay()];
    all += w;
    if (d <= daysDone) { done += w; }
  }
  return all > 0 ? (done / all) : (daysDone / daysInMonth);
}

function weekdayWeights(tz) {
  var from = Utilities.formatDate(new Date(Date.now() - 57 * 86400000), tz, 'yyyy-MM-dd');
  var to   = Utilities.formatDate(new Date(Date.now() - 86400000), tz, 'yyyy-MM-dd');

  var sums   = [0, 0, 0, 0, 0, 0, 0];
  var counts = [0, 0, 0, 0, 0, 0, 0];

  var rows = AdsApp.search(
    'SELECT segments.date, metrics.cost_micros ' +
    'FROM customer ' +
    'WHERE segments.date BETWEEN "' + from + '" AND "' + to + '"'
  );
  while (rows.hasNext()) {
    var r = rows.next();
    var p = String(r.segments.date).split('-');
    var d = new Date(Number(p[0]), Number(p[1]) - 1, Number(p[2])).getDay();
    sums[d]   += Number(r.metrics.costMicros) / 1000000;
    counts[d] += 1;
  }

  var total = 0;
  for (var i = 0; i < 7; i++) { total += sums[i]; }
  if (total <= 0) { return null; }   // no history, fall back to a flat line

  var w = [];
  for (var j = 0; j < 7; j++) {
    w.push(counts[j] > 0 ? (sums[j] / counts[j]) : 0);
  }
  return w;
}

function pad(n) { return (n < 10 ? '0' : '') + n; }