Click here to see your recently viewed pages and most viewed pages.
Hide
AdWords scripts

Account Anomaly Detector

AdWordsApp

This script is for a single account. For operating on multiple accounts of an MCC, use the MccApp version of the script.

Account Anomaly Detector alerts the advertiser whenever an AdWords account is suddenly behaving too differently from what's historically observed. When an issue is encountered, the script will send the user an alerting email. Only a single email for an alert is sent per day.

The script is comparing stats observed so far today with historical stats for the same day of week. For instance, stats for a Tuesday, 13:00 are compared with stats for 26 previous Tuesdays. Adjust the number of weeks to look back depending on the age and stability of your account.

Scheduling

Schedule the script to run Hourly in order to get the most out of alerting. If the alert is too noisy, scheduling it Daily around mid-day might also make sense.

How it works

Suppose the script runs at 7pm on a Tuesday. Since AdWords statistics may be up to 3 hours delayed, the script will only consider stats up to 4pm.

The script will then fetch stats for 26 preceding Tuesdays, average them, and compare with today's stats.

No subsequent alerts of the same type will be triggered for the day. If you'd like to reset the alert, delete the Alerting cell value.

Setup

  • Setup a spreadsheet-based script with the source code below. Use Account Anomaly Detector template spreadsheet.
  • Don't forget to update SPREADSHEET_URL in code.
  • Schedule the script Hourly.

Source code

var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

var DAYS = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
            'Saturday'];

function main() {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  spreadsheet.getRangeByName('date').setValue(new Date());
  spreadsheet.getRangeByName('account_id').setValue(
      AdWordsApp.currentAccount().getCustomerId());
  var impressionsThreshold = parseField(spreadsheet.
      getRangeByName('impressions').getValue());
  var clicksThreshold = parseField(spreadsheet.getRangeByName('clicks').
      getValue());
  var costThreshold = parseField(spreadsheet.getRangeByName('cost').getValue());
  var weeksStr = spreadsheet.getRangeByName('weeks').getValue();
  var weeks = parseInt(weeksStr.substring(0, weeksStr.indexOf(' ')));
  var email = spreadsheet.getRangeByName('email').getValue();

  var now = new Date(Utilities.formatDate(new Date(),
      AdWordsApp.currentAccount().getTimeZone(), 'MMM dd,yyyy HH:mm:ss'));

  var currentDate = now.getDate();
  now.setTime(now.getTime() - 3 * 3600 * 1000);
  var adjustedDate = now.getDate();

  var hours = now.getHours();
  if (hours == 0) {
    hours = 24;
  }
  if (hours == 1) {
    // first run of the day, kill existing alerts
    spreadsheet.getRangeByName('clicks_alert').clearContent();
    spreadsheet.getRangeByName('impressions_alert').clearContent();
    spreadsheet.getRangeByName('cost_alert').clearContent();
  }
  var dayToCheck;
  if (currentDate != adjustedDate) {
    dayToCheck = 1;
  } else {
    dayToCheck = 0;
  }
  var dateRangeToCheck = getDateInThePast(dayToCheck);
  var dateRangeToEnd = getDateInThePast(dayToCheck + 1);
  var dateRangeToStart = getDateInThePast(dayToCheck + 1 + weeks * 7);
  var fields = 'HourOfDay,DayOfWeek,Clicks,Impressions,Cost';

  var today = AdWordsApp.report('SELECT ' + fields +
      ' FROM ACCOUNT_PERFORMANCE_REPORT DURING ' + dateRangeToCheck + ',' +
      dateRangeToCheck);
  var past = AdWordsApp.report('SELECT ' + fields +
      ' FROM ACCOUNT_PERFORMANCE_REPORT WHERE DayOfWeek=' +
      DAYS[now.getDay()].toUpperCase() +
      ' DURING ' + dateRangeToStart + ',' + dateRangeToEnd);

  var todayStats = accumulateRows(today.rows(), hours, 1);
  var pastStats = accumulateRows(past.rows(), hours, weeks);

  var alertText = [];
  if (impressionsThreshold &&
      todayStats.impressions < pastStats.impressions * impressionsThreshold) {
    var range = spreadsheet.getRangeByName('impressions_alert');
    if (!range.getValue() || range.getValue().length == 0) {
      alertText.push('    Impressions are too low: ' + todayStats.impressions +
          ' impressions by ' + hours + ':00, expecting at least ' +
          parseInt(pastStats.impressions * impressionsThreshold));
      range.setValue('Alerting ' + hours + ':00');
    }
  }
  if (clicksThreshold &&
      todayStats.clicks < pastStats.clicks * clicksThreshold) {
    var range = spreadsheet.getRangeByName('clicks_alert');
    if (!range.getValue() || range.getValue().length == 0) {
      alertText.push('    Clicks are too low: ' + todayStats.clicks +
          ' clicks by ' + hours + ':00, expecting at least ' +
          (pastStats.clicks * clicksThreshold).toFixed(1));
      range.setValue('Alerting ' + hours + ':00');
    }
  }
  if (costThreshold && todayStats.cost > pastStats.cost * costThreshold) {
    var range = spreadsheet.getRangeByName('cost_alert');
    if (!range.getValue() || range.getValue().length == 0) {
      alertText.push('    Cost is too high: ' + todayStats.cost + ' ' +
          AdWordsApp.currentAccount().getCurrencyCode() + ' by ' + hours +
          ':00, expecting at most ' +
          (pastStats.clicks * costThreshold).toFixed(2));
      range.setValue('Alerting ' + hours + ':00');
    }
  }
  if (alertText.length > 0 && email && email.length > 0) {
    MailApp.sendEmail(email,
        'AdWords Account ' + AdWordsApp.currentAccount().getCustomerId() +
        ' misbehaved.',
        'Your account ' + AdWordsApp.currentAccount().getCustomerId() +
        ' is not performing as expected today: \n\n' + alertText.join('\n') +
        '\n\nLog into AdWords and take a look.\n\nAlerts dashboard: ' +
        SPREADSHEET_URL);
  }
  spreadsheet.getRangeByName('date').setValue(new Date());
  spreadsheet.getRangeByName('account_id').setValue(
      AdWordsApp.currentAccount().getCustomerId());
  spreadsheet.getRangeByName('timestamp').setValue(
      DAYS[now.getDay()] + ', ' + hours + ':00');

  var dataRows = [
    [todayStats.impressions, pastStats.impressions.toFixed(0)],
    [todayStats.clicks, pastStats.clicks.toFixed(1)],
    [todayStats.cost, pastStats.cost.toFixed(2)]
  ];
  spreadsheet.getRangeByName('data').setValues(dataRows);
}

function toFloat(value) {
  value = value.toString().replace(/,/g, '');
  return parseFloat(value);
}

function parseField(value) {
  if (value == 'No alert') {
    return null;
  } else {
    return toFloat(value);
  }
}

function accumulateRows(rows, hours, weeks) {
  var row;
  var result;

  while (rows.hasNext()) {
    var row = rows.next();
    var hour = row['HourOfDay'];

    if (hour < hours) {
      result = addRow(row, result, 1 / weeks);
    }
  }

  return result;
}

function addRow(row, previous, coefficient) {
  if (!coefficient) {
    coefficient = 1;
  }
  if (row == null) {
    row = {Clicks: 0, Impressions: 0, Cost: 0};
  }
  if (!previous) {
    return {
      clicks: parseInt(row['Clicks']) * coefficient,
      impressions: parseInt(row['Impressions']) * coefficient,
      cost: toFloat(row['Cost']) * coefficient
    };
  } else {
    return {
      clicks: parseInt(row['Clicks']) * coefficient + previous.clicks,
      impressions: parseInt(row['Impressions']) * coefficient +
          previous.impressions,
      cost: toFloat(row['Cost']) * coefficient + previous.cost
    };
  }
}

function checkInRange(today, yesterday, coefficient, field) {
  var yesterdayValue = yesterday[field] * coefficient;
  if (today[field] > yesterdayValue * 2) {
    Logger.log('' + field + ' too much');
  } else if (today[field] < yesterdayValue / 2) {
    Logger.log('' + field + ' too little');
  }
}

// Returns YYYYMMDD-formatted date.
function getDateInThePast(numDays) {
  var today = new Date();
  today.setDate(today.getDate() - numDays);
  return Utilities.formatDate(today, 'PST', 'yyyyMMdd');
}

MccApp

This is an MCC script. For operating on a single account, use the AdWordsApp version of the script.

The MCC Account Anomaly Detector extends the single account Account Anomaly Detector to run for multiple accounts under a single MCC. An Account Anomaly Detector alerts the advertiser whenever an AdWords account's behavior differs significantly from how it performed in the past. When an issue is encountered, the script will send the user an alerting email. Only one alert is sent per day.

The script compares today’s stats to historical averages for the same day of week. For instance, stats for 1pm on a Tuesday are compared with stats for the previous 26 Tuesdays. You can adjust the number of weeks to look back depending on the age and stability of your account.

The view provided by the MCC Account Anomaly Detector shows a summary of all accounts under a managing MCC account. This allows for an easy, at-a-glance view of the performance of the accounts.

Scheduling

Schedule the script to run Hourly in order to get the most out of alerting. This way, you will receive an email within an hour of an anomaly taking place.

How it works

Suppose the script runs at 7pm on a Tuesday. Since AdWords statistics may be up to 3 hours delayed, the script will only consider stats up to 4pm.

The script fetches stats for the previous 26 Tuesdays, averages them, and compares the average with today's stats.

No subsequent alerts of the same type for the same account will be triggered for the day. If you'd like to reset the alert, clear the background color of the row of the account(s) you would like to reset.

Setup

  • Setup a spreadsheet-based script with the source code below. Use the MCC Account Anomaly Detector template spreadsheet.
  • Update the SPREADSHEET_URL in the code to reflect your spreadsheet's URL.
  • Schedule the script Hourly.

Source code

var SPREADSHEET_URL = '[YOUR_URL]';

var CONFIG = {
  // Uncomment below to include an account label filter
  // ACCOUNT_LABEL: 'High Spend Accounts',
  TIMEZONE: 'EST' // Timezone code, such as 'PST', 'EST', 'UTC'
};

var CONST = {
  FIRST_DATA_ROW: 11,
  FIRST_DATA_COLUMN: 2,
  MCC_CHILD_ACCOUNT_LIMIT: 50,
  TOTAL_DATA_COLUMNS: 7
};

var STATS = {
  'numOfColumns': 3,
  'impressions': {
    'column': 3,
    'color': 'red',
    'alert_range': 'impressions_alert'
  },
  'clicks': {
    'column': 4,
    'color': 'orange',
    'alert_range': 'clicks_alert'
  },
  'cost': {
    'column': 5,
    'color': 'yellow',
    'alert_range': 'cost_alert'
  }
};

var DAYS = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
            'Saturday'];

function main() {
  var account;
  var alertText = [];
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var dataRow = CONST.FIRST_DATA_ROW;

  SheetUtil.setupData(spreadsheet);

  Logger.log('MCC account: ' + mccManager.mccAccount().getCustomerId());
  while (account = mccManager.next()) {
    Logger.log('Processing account ' + account.getCustomerId());
    alertText.push(processAccount(account, spreadsheet, dataRow));
    dataRow++;
  }

  sendEmail(mccManager.mccAccount(), alertText, spreadsheet);
}

/**
 * For each of impressions, clicks, and cost, check to see if the values are
 * out of range. If they are, and no alert has been set in the spreadsheet,
 * then 1) Add text to the email, and 2) Add coloring to the cells
 * corresponding to the statistic.
 *
 * @return {string} the next piece of the alert text to include in the email.
 */
function processAccount(account, spreadsheet, startingRow) {
  var sheet = spreadsheet.getSheets()[0];

  var thresholds = SheetUtil.thresholds();
  var today = AdWordsApp.report(SheetUtil.getTodayQuery());
  var past = AdWordsApp.report(SheetUtil.getPastQuery());

  var hours = SheetUtil.hourOfDay();
  var todayStats = accumulateRows(today.rows(), hours, 1); // just one week
  var pastStats = accumulateRows(past.rows(), hours, SheetUtil.weeksToAvg());

  var alertText = ['Account ' + account.getCustomerId()];
  var validWhite = ['', 'white', '#ffffff']; // these all count as white

  // Colors cells that need alerting, and adds text to the alert email body.
  function generateAlert(field, emailAlertText) {
    // There are 2 cells to check, for Today's value and Past value
    var bgRange = [
      sheet.getRange(startingRow, STATS[field].column, 1, 1),
      sheet.getRange(startingRow, STATS[field].column + STATS.numOfColumns,
        1, 1)
    ];
    var bg = [bgRange[0].getBackground(), bgRange[1].getBackground()];

    // If both backgrounds are white, change background colors
    // and update most recent alert time.
    if ((-1 != validWhite.indexOf(bg[0])) &&
        (-1 != validWhite.indexOf(bg[1]))) {
      bgRange[0].setBackground([[STATS[field]['color']]]);
      bgRange[1].setBackground([[STATS[field]['color']]]);

      spreadsheet.getRangeByName(field + '_alert').
        setValue('Alert at ' + hours + ':00');
      alertText.push(emailAlertText);
    }
  }

  if (thresholds.impressions &&
      todayStats.impressions < pastStats.impressions * thresholds.impressions) {
    generateAlert('impressions',
                  '    Impressions are too low: ' + todayStats.impressions +
                  ' impressions by ' + hours + ':00, expecting at least ' +
                  parseInt(pastStats.impressions * thresholds.impressions));
  }

  if (thresholds.clicks &&
      todayStats.clicks < (pastStats.clicks * thresholds.clicks).toFixed(1)) {
    generateAlert('clicks',
                  '    Clicks are too low: ' + todayStats.clicks +
                  ' clicks by ' + hours + ':00, expecting at least ' +
                  (pastStats.clicks * thresholds.clicks).toFixed(1));
  }

  if (thresholds.cost &&
      todayStats.cost > (pastStats.cost * thresholds.cost).toFixed(2)) {
    generateAlert('cost',
                  '    Cost is too high: ' + todayStats.cost + ' ' +
                  account.getCurrencyCode() + ' by ' + hours +
                  ':00, expecting at most ' +
                  (pastStats.cost * thresholds.cost).toFixed(2));
  }

  // If no alerts were triggered, we will have only the heading text. Remove it.
  if (alertText.length == 1) {
    alertText = [];
  }

  var dataRows = [[
    account.getCustomerId(),
    todayStats.impressions,
    todayStats.clicks,
    todayStats.cost,
    pastStats.impressions.toFixed(0),
    pastStats.clicks.toFixed(1),
    pastStats.cost.toFixed(2)
  ]];

  sheet.getRange(startingRow, CONST.FIRST_DATA_COLUMN,
    1, CONST.TOTAL_DATA_COLUMNS).setValues(dataRows);

  return alertText;
}

var SheetUtil = (function() {
  var thresholds = {};
  var hours = 1; // default
  var weeks = 26; // default

  var todayQuery = '';
  var pastQuery = '';

  var setupData = function(spreadsheet) {
    Logger.log('Running setupData');
    spreadsheet.getRangeByName('date').setValue(new Date());
    spreadsheet.getRangeByName('account_id').setValue(
        mccManager.mccAccount().getCustomerId());

    var getThresholdFor = function(field) {
      thresholds[field] = parseField(spreadsheet.
          getRangeByName(field).getValue());
    };
    getThresholdFor('impressions');
    getThresholdFor('clicks');
    getThresholdFor('cost');

    var now = new Date(Utilities.formatDate(new Date(),
              mccManager.mccAccount().getTimeZone(), 'MMM dd,yyyy HH:mm:ss'));
    var currentDate = now.getDate();
    now.setTime(now.getTime() - 3 * 3600 * 1000);
    var adjustedDate = now.getDate();

    spreadsheet.getRangeByName('timestamp').setValue(
        DAYS[now.getDay()] + ', ' + hours + ':00');

    hours = now.getHours();
    // Uncomment the following line to clear alerts; the script will think
    // it's the first execution for the day:
    // hours = 1;

    if (hours == 0) {
      hours = 24;
    }
    if (hours == 1) {
      // First run of the day, clear existing alerts.
      spreadsheet.getRangeByName('clicks_alert').clearContent();
      spreadsheet.getRangeByName('impressions_alert').clearContent();
      spreadsheet.getRangeByName('cost_alert').clearContent();

      // Reset background and font colors for all data rows.
      var bg = [];
      var ft = [];
      var bg_single = ['white', 'white', 'white', 'white',
          'white', 'white', 'white'];
      var ft_single = ['black', 'black', 'black', 'black',
          'black', 'black', 'black'];

      // Construct a 50-row array of colors to set.
      for (var a = 0; a < CONST.MCC_CHILD_ACCOUNT_LIMIT; ++a) {
        bg.push(bg_single);
        ft.push(ft_single);
      }

      var dataRegion = spreadsheet.getSheets()[0].getRange(
        CONST.FIRST_DATA_ROW, CONST.FIRST_DATA_COLUMN,
        CONST.MCC_CHILD_ACCOUNT_LIMIT, CONST.TOTAL_DATA_COLUMNS);

      dataRegion.setBackgrounds(bg);
      dataRegion.setFontColors(ft);
    }

    var dayToCheck;
    if (currentDate != adjustedDate) {
      dayToCheck = 1;
    } else {
      dayToCheck = 0;
    }

    var weeksStr = spreadsheet.getRangeByName('weeks').getValue();
    weeks = parseInt(weeksStr.substring(0, weeksStr.indexOf(' ')));

    var dateRangeToCheck = getDateInThePast(dayToCheck);
    var dateRangeToEnd = getDateInThePast(dayToCheck + 1);
    var dateRangeToStart = getDateInThePast(dayToCheck + 1 + weeks * 7);
    var fields = 'HourOfDay, DayOfWeek, Clicks, Impressions, Cost';

    todayQuery = 'SELECT ' + fields +
        ' FROM ACCOUNT_PERFORMANCE_REPORT DURING ' + dateRangeToCheck + ',' +
        dateRangeToCheck;
    pastQuery = 'SELECT ' + fields +
        ' FROM ACCOUNT_PERFORMANCE_REPORT WHERE DayOfWeek=' +
        DAYS[now.getDay()].toUpperCase() +
        ' DURING ' + dateRangeToStart + ',' + dateRangeToEnd;
  };

  var getThresholds = function() { return thresholds; };
  var getHourOfDay = function() { return hours; };
  var getWeeksToAvg = function() { return weeks; };
  var getPastQuery = function() { return pastQuery; };
  var getTodayQuery = function() { return todayQuery; };

  // The SheetUtil public interface.
  return {
    setupData: setupData,
    thresholds: getThresholds,
    hourOfDay: getHourOfDay,
    weeksToAvg: getWeeksToAvg,
    getPastQuery: getPastQuery,
    getTodayQuery: getTodayQuery
  };
})();

function sendEmail(account, alertTextArray, spreadsheet) {
  var bodyText = '';
  alertTextArray.forEach(function(alertText) {
    // When zero alerts, this is an empty array, which we don't want to add.
    if (alertText.length == 0) { return }
    bodyText += alertText.join('\n') + '\n\n';
  });
  bodyText = bodyText.trim();

  var email = spreadsheet.getRangeByName('email').getValue();
  if (bodyText.length > 0 && email && email.length > 0 &&
      email != 'foo@example.com') {
    Logger.log('Sending Email');
    MailApp.sendEmail(email,
        'AdWords Account ' + account.getCustomerId() + ' misbehaved.',
        'Your account ' + account.getCustomerId() +
        ' is not performing as expected today: \n\n' +
        bodyText + '\n\n' +
        'Log into AdWords and take a look: ' +
        'adwords.google.com\n\nAlerts dashboard: ' +
        SPREADSHEET_URL);
  }
  else if (bodyText.length == 0) {
    Logger.log('No alerts triggered. No email being sent.');
  }
}

function toFloat(value) {
  value = value.toString().replace(/,/g, '');
  return parseFloat(value);
}

function parseField(value) {
  if (value == 'No alert') {
    return null;
  } else {
    return toFloat(value);
  }
}

function accumulateRows(rows, hours, weeks) {
  var row;
  var result;

  while (rows.hasNext()) {
    var row = rows.next();
    var hour = row['HourOfDay'];

    if (hour < hours) {
      result = addRow(row, result, 1 / weeks);
    }
  }

  return result;
}

function addRow(row, previous, coefficient) {
  if (!coefficient) {
    coefficient = 1;
  }
  if (row == null) {
    row = {Clicks: 0, Impressions: 0, Cost: 0};
  }
  if (!previous) {
    return {
      clicks: parseInt(row['Clicks']) * coefficient,
      impressions: parseInt(row['Impressions']) * coefficient,
      cost: toFloat(row['Cost']) * coefficient
    };
  } else {
    return {
      clicks: parseInt(row['Clicks']) * coefficient + previous.clicks,
      impressions: parseInt(row['Impressions']) * coefficient +
          previous.impressions,
      cost: toFloat(row['Cost']) * coefficient + previous.cost
    };
  }
}

function checkInRange(today, yesterday, coefficient, field) {
  var yesterdayValue = yesterday[field] * coefficient;
  if (today[field] > yesterdayValue * 2) {
    Logger.log('' + field + ' too much');
  } else if (today[field] < yesterdayValue / 2) {
    Logger.log('' + field + ' too little');
  }
}

// Returns yyyyMMdd-formatted date.
function getDateInThePast(numDays) {
  var today = new Date();
  today.setDate(today.getDate() - numDays);
  return Utilities.formatDate(today, CONFIG.TIMEZONE, 'yyyyMMdd');
}

/**
 * Module that deals with fetching and iterating through multiple accounts.
 *
 * @return {object} callable functions corresponding to the available
 * actions. Specifically, it currently supports next, current, mccAccount.
 */
var mccManager = (function() {
  var accountIterator;
  var mccAccount;
  var currentAccount;

  // Private one-time init function.
  var init = function() {
    var accountSelector = MccApp.accounts();

    // Use this to limit the accounts that are being selected in the report.
    if (CONFIG.ACCOUNT_LABEL) {
        accountSelector.withCondition("LabelNames CONTAINS '" +
            CONFIG.ACCOUNT_LABEL + "'");
    }

    accountSelector.withLimit(CONST.MCC_CHILD_ACCOUNT_LIMIT);
    accountIterator = accountSelector.get();

    mccAccount = AdWordsApp.currentAccount(); // save the mccAccount
    currentAccount = AdWordsApp.currentAccount();
  };

  /**
   * After calling this, AdWordsApp will have the next account selected.
   * If there are no more accounts to process, re-selects the original
   * MCC account.
   *
   * @return {AdWordsApp.Account} The account that has been selected.
   */
  var getNextAccount = function() {
    if (accountIterator.hasNext()) {
      currentAccount = accountIterator.next();
      MccApp.select(currentAccount);
      return currentAccount;
    }
    else {
      MccApp.select(mccAccount);
      return null;
    }

  };

  /**
   * Returns the currently selected account. This is cached for performance.
   *
   * @return {AdWords.Account} The currently selected account.
   */
  var getCurrentAccount = function() {
    return currentAccount;
  };

 /**
  * Returns the original MCC account.
  *
  * @return {AdWords.Account} The original account that was selected.
  */
  var getMccAccount = function() {
    return mccAccount;
  };

  // Set up internal variables; called only once, here.
  init();

  // Expose the external interface.
  return {
    next: getNextAccount,
    current: getCurrentAccount,
    mccAccount: getMccAccount
  };

})();