Skip 0 Impression Account For Anomoly Checker

[Nicole Mennicke]

I've implemented the following script to identify account anamoly through the day comparing day performance compared the last 52 days of the same day of week.

https://developers.google.com/adwords/scripts/docs/solutions/account-anomaly-detector

I uncommented the var to pull only accounts with specific labels, I applied a label to each account I wanted included in the tracker.

var CONFIG = {

  // Uncomment below to include an account label filter

  ACCOUNT_LABEL: 'labelsfortracking',

  TIMEZONE: 'EST' // Timezone code, such as 'PST', 'EST', 'UTC'

The issue is, if at any time there is an account with 0 impressions for the day, it stops the script and does not send the alert. In the log I get an error of "TypeError: Cannot read property "impressions from undefined." 

The line it refers to is where (I believe) it calls the impressions. I've been investigating which account it is sending this error for and it is whenever the account had 0 impressions. 

 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));

Is there an addition to add an "if "0" skip to next account" to the script?

[Anthony Madrigal]

Hello,

You can add a condition Impressions > 0 during your selector for both variables today and past which will exclude accounts with zero impressions

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

Cheers,

Anthony

AdWords Scripts Team

[Nicole Mennicke]

I tried adding this and i get an error:  ReferenceError: "fields" is not defined.

Did I implement incorrectly?

[Anthony Madrigal]

Hello,

The fields variable is already defined in the Account Anomaly Detector script. The variables today and past are also defined. You just need to modify it with the changes I made above.

If you are still running to any issues, Reply privately to author your CID and script name.

Regards,

Anthony

AdWords Scripts Team

[Anthony Madrigal]

Hey Nicole,

In the Account Anomaly Detector script, the fields variable is defined as 

var fields = 'HourOfDay,DayOfWeek,Clicks,Impressions,Cost';

I noticed in your script that you do not define fields anywhere, which is why you are getting that error. Also, I see you have a variable field throughout your code but it is not defined anywhere, which may cause another error. 

I suggest adding fields before defining today and past, along with the changes I suggested to get rid of zero impressions.

Hope this helps!

Anthony

AdWords Scripts Team

[rush...@gmail.com]

Hi i am having the same issue. I added the changes that Antony recommended but I get an error: ReferenceError: "fields" is not defined. (line 68)

[Anthony Madrigal]

Hello,

Could you please Reply privately to author your CID and script name so I could take a look and see what is wrong?

Thanks,

Anthony

AdWords Scripts Team

[Anthony Madrigal]

Hello,

The variable fields is not in scope in the function processAccount. Change the variables today and past back to their original values. Then insert Impressions > 0 into the variables todayQuery and pastQuery appropriately.

[RUSH MCC]

Hi Anthony I still get an error msg. I copied the exact code from the Developers site. Anything else I need to change? I am not sure your instructions were very clear. 

Many thanks

Stelios

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "AdWords Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/Z3py5KvHrQ8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/7dfc13bf-1773-45c6-81cd-8d0c5400ff8a%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Anthony Madrigal]

Hello Stellos,

In the function processAccount, change today and past as follows

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

And in SheetUtil, change todayQuery and pastQuery to  

    todayQuery = 'SELECT ' + fields +

        ' FROM ACCOUNT_PERFORMANCE_REPORT WHERE Impressions >0 DURING ' + dateRangeToCheck + ',' +

        dateRangeToCheck;
    pastQuery = 'SELECT ' + fields +

        ' FROM ACCOUNT_PERFORMANCE_REPORT WHERE Impressions >0 AND DayOfWeek=' +
        DAYS[now.getDay()].toUpperCase() +

        ' DURING ' + dateRangeToStart + ',' + dateRangeToEnd;

[RUSH MCC]

Many thanks!

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "AdWords Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/Z3py5KvHrQ8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/2f209e7b-8a8a-4b38-bacf-80863f200209%40googlegroups.com.

[RUSH MCC]

Hi Anthony I am now getting another error msg: 

17:48:35.170	ReferenceError: "fields" is not defined. (line 152)
17:48:35.913	ReferenceError: "fields" is not defined. (line 152

This come under SheetUtil, change todayQuery and pastQuery!

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "AdWords Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/Z3py5KvHrQ8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/2f209e7b-8a8a-4b38-bacf-80863f200209%40googlegroups.com.

[Anthony Madrigal]

Hi Stelios,

The variables todayQuery and pastQuery are defined twice in the variable SheetUtil

• At the beginning of the definition
  
• Inside of setupData, towards the end of the definition
  

Change the initial todayQuery and pastQuery to the empty string ' ' as it is defined in the original code.

Change the todayQuery and pastQuery to the values I gave previously. The variables are located after the variable fields, which is the one that was not defined. 

Once these changes are made, your script should run fine.

[RUSH MCC]

Hi Anthony still no luck, any chance you could email  me the code? I tried several times but still the same or different issues ( you can check under the MCC)

Many thanks

Stelios

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "AdWords Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/Z3py5KvHrQ8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/3a147474-7b8d-4a88-85b9-41552d4ce8cc%40googlegroups.com.

[Anthony Madrigal]

Hi,

In your script Anomaly Report runs hourly, change CONFIG to the following

var CONFIG = {
  // Uncomment below to include an account label filter

   ACCOUNT_LABEL: 'Active',

  TIMEZONE: 'EST' // Timezone code, such as 'PST', 'EST', 'UTC'

};

This will check all your accounts with the label 'Active' and run the report accordingly.

[Jeffrey Chang]

Hi Anthony,

Was there a solution to this? Would it be possible for you to post the full script as I am having the same issue. 

Thanks, 

[Anthony Madrigal]

Hi Jeffrey, 

I have posted the solution below. If you are still running into issues please Reply privately to author your CID and I will take a look at your script.

var SPREADSHEET_URL = '[YOUR_URL]';

var CONFIG = {
  // Uncomment below to include an account label filter

  ACCOUNT_LABEL: 'Active', //or whatever label you want

  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 = [];


  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  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 WHERE Impressions > 0 DURING ' + dateRangeToCheck + ',' +
        dateRangeToCheck;
    pastQuery = 'SELECT ' + fields +
        ' FROM ACCOUNT_PERFORMANCE_REPORT WHERE Impressions > 0 AND 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 != 'f...@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
  };


})();