Account Anomaly Pulse: compare yesterday with the last four of the same weekday

/**
 * Account Anomaly Pulse
 * The SEM Dispatch Vault, semdispatch.com/vault
 *
 * Compares yesterday's cost, clicks and conversions with the average of
 * the same weekday over the previous four weeks, and flags any metric
 * outside the tolerance. Read-only: this script never changes the account.
 */

var CONFIG = {
  // Flag a metric this far from its four-week weekday average.
  // 0.35 = plus or minus 35%.
  TOLERANCE: 0.35,

  // Optional. An email address for anomaly alerts, e.g. "you@example.com".
  // Leave empty to log only.
  EMAIL: ""
};

function main() {
  var account = AdsApp.currentAccount();
  var tz = account.getTimeZone();
  var currency = account.getCurrencyCode();

  // Build the dates carefully. We read today's calendar date in the account
  // timezone, then rebuild it at UTC noon. Reading and shifting UTC parts of
  // a UTC-noon date never rolls into the wrong day at a daylight-saving edge,
  // which a naive "now minus 24h" subtraction can do.
  var todayParts = Utilities.formatDate(new Date(), tz, "yyyy-MM-dd").split("-");
  var anchor = new Date(
    Date.UTC(
      Number(todayParts[0]),
      Number(todayParts[1]) - 1,
      Number(todayParts[2]),
      12
    )
  );

  var yesterday = shiftDays(anchor, -1);
  var baselineDates = [
    shiftDays(yesterday, -7),
    shiftDays(yesterday, -14),
    shiftDays(yesterday, -21),
    shiftDays(yesterday, -28)
  ];

  var yesterdayKey = dateKey(yesterday);
  var earliest = baselineDates[baselineDates.length - 1];

  // One report over the whole span, then we pick out the exact days we need.
  var query =
    "SELECT segments.date, metrics.cost_micros, metrics.clicks, " +
    "metrics.conversions " +
    "FROM customer " +
    "WHERE segments.date BETWEEN '" + dateKey(earliest) + "' " +
    "AND '" + yesterdayKey + "'";

  var rows = AdsApp.report(query).rows();
  var byDate = {};
  while (rows.hasNext()) {
    var row = rows.next();
    byDate[row["segments.date"]] = {
      cost: Number(row["metrics.cost_micros"] || 0) / 1000000,
      clicks: Number(row["metrics.clicks"] || 0),
      conversions: Number(row["metrics.conversions"] || 0)
    };
  }

  var zero = { cost: 0, clicks: 0, conversions: 0 };
  var actual = byDate[yesterdayKey] || zero;

  var baselineKeys = baselineDates.map(dateKey);
  var metrics = ["cost", "clicks", "conversions"];
  var results = [];

  for (var m = 0; m < metrics.length; m++) {
    var metric = metrics[m];
    var sum = 0;
    var weeks = 0;
    for (var b = 0; b < baselineKeys.length; b++) {
      var day = byDate[baselineKeys[b]];
      if (day) {
        sum += day[metric];
        weeks += 1;
      }
    }
    var average = weeks > 0 ? sum / weeks : 0;
    var value = actual[metric];
    // Deviation against the baseline. With no baseline (a brand new account)
    // we cannot judge, so we mark it as such rather than dividing by zero.
    var deviation = average > 0 ? (value - average) / average : null;
    results.push({
      metric: metric,
      value: value,
      average: average,
      weeks: weeks,
      deviation: deviation,
      flagged: deviation !== null && Math.abs(deviation) > CONFIG.TOLERANCE
    });
  }

  var lines = [
    "Account Anomaly Pulse, " + account.getName(),
    "Yesterday (" + yesterdayKey + ") vs the previous 4 same-weekdays",
    ""
  ];
  var anyFlag = false;

  for (var r = 0; r < results.length; r++) {
    var res = results[r];
    var shown =
      res.metric === "cost"
        ? currency + " " + res.value.toFixed(2)
        : res.value.toFixed(res.metric === "conversions" ? 1 : 0);
    var avgShown =
      res.metric === "cost"
        ? currency + " " + res.average.toFixed(2)
        : res.average.toFixed(res.metric === "conversions" ? 1 : 0);

    var verdict;
    if (res.deviation === null) {
      verdict = "no baseline yet";
    } else {
      var pct = (res.deviation * 100).toFixed(0);
      verdict =
        (res.deviation >= 0 ? "+" : "") + pct + "% vs avg" +
        (res.flagged ? "  <-- FLAGGED" : "");
    }
    if (res.flagged) anyFlag = true;

    lines.push(
      res.metric.toUpperCase() + ": " + shown +
        " (avg " + avgShown + " over " + res.weeks + " wk): " + verdict
    );
  }

  var report = lines.join("\n");
  Logger.log(report);

  if (CONFIG.EMAIL !== "" && anyFlag) {
    MailApp.sendEmail(
      CONFIG.EMAIL,
      "[Anomaly] " + account.getName() + ": yesterday is off pattern",
      report
    );
  }
}

function shiftDays(date, days) {
  var next = new Date(date.getTime());
  next.setUTCDate(next.getUTCDate() + days);
  return next;
}

function dateKey(date) {
  // The dates are constructed in UTC, so format them in UTC to read back the
  // same calendar day we built. GAQL's segments.date wants YYYY-MM-DD.
  return Utilities.formatDate(date, "UTC", "yyyy-MM-dd");
}