Keeping an eye on the health of your Adwords accounts can be an overwhelming task if you’re managing many of them. Thankfully, using Google Adwords Scripts can help take away some of the hassle and automate many tasks and processes. One essential task that many skilled developers have attempted to automate is making sure your accounts are running as expected. The central idea is, if the account’s ads are getting impressions, that’s good; but if not, there’s most likely a problem. Catching these problems early can potentially save your clients thousands of dollars, therefore it’s essential to have the best Adwords script out there running on your accounts. Here are some of the most popular free Adwords scripts on the internet that periodically check to make sure your accounts are getting impressions:
- Google Anomaly Detector – https://developers.google.com/adwords/scripts/docs/solutions/account-anomaly-detector
- Google Anomaly Detector MCC – https://developers.google.com/adwords/scripts/docs/solutions/mccapp-account-anomaly-detector
- Granular Anomaly Detector – https://searchengineland.com/script-getting-granular-adwords-account-anomaly-alerts-249139
- Zero Impressions Email Alarm – http://catalyst.ca/blog/set-a-fire-alarm-on-your-accounts-with-adwords-scripts/
- Check If Account Is Offline – https://bitbucket.org/snippets/fvallaeys/nLEoj/check-if-account-is-offline
When I investigated the scripts listed above, I noticed that every one of them is subject to the fact that data released by Google often has a delay of 3-4 hours. Each of these scripts are using the Impressions metric to determine if their accounts are functioning as expected. Run hourly, this can be a great indication of whether an account is functioning properly; but Google releases impressions (along with clicks and conversion) only periodically.
A general rule of thumb for top level metrics is that the data won’t be available for at least 3 hours after the time in question (although it can vary depending on the size of the account and many other factors).
In the case of an emergency however, this is no good. For accounts that make any significant amount of their income through Adwords, a 3-4 hour delay could translate into thousands of dollars being lost.
Another problem with these other scripts is that many things can trigger a warning email to be sent out, but it may not necessarily translate to a real emergency with your accounts. Imagine a situation where you’re managing a smaller account. It might be perfectly normal for this account to not get any impressions at 1 AM, but this could trigger and alarm with these other scripts. An ideal script for Adwords would only detect REAL emergencies and notify you IMMEDIATELY.
In order to create the perfect impressions emergency alarm script, one would have to provide a better solution to the problem of stale data (we want to detect emergencies as quickly as possible!), and only send an email in the event of an emergency. In the script below, I accomplished the first goal by making use of an account’s total impressions (instead of the hourly impressions). This is one of the few metrics that Google Adwords updates in real time, so it will always be current and data freshness will never be an issue. By using this data and storing its value in a Google Sheet every time the script runs (should be hourly), we can easily subtract the total number of impressions on the previous run from the total number of impressions on the most current run, and get an accurate portrayal of how many impressions the account demonstrated during the previous hour.
Note: The only time the script would generate a false value would be on the first run of the day when the total impressions metric refreshes to 0, so it would calculate the total number of impressions on the last run (a period of 23 hours, and presumably more impressions than 0) subtracting from 0, resulting in a negative number of impressions. This was unfortunately an outstanding problem, and so the first run of the day is always ignored by the script.
The second issue was that the script was throwing too many false triggers. Accounts that were cancelled or paused that might be on an MCC account would all be getting 0 impressions.
Small accounts also might get 0 impressions on off peak hours. With no regulation, you might easily see your inbox full with 10 emergency emails from the same account.
No problem! First we need to specify that each account can only send one email per day. Since we’re already using a Google Spreadsheet to store variables, it was easy enough to add a separate column that stores either a true or false value for the question of – has an account already sent an email (this value refreshes to false at the end of the day).
The other issue here is that we might expect some accounts to get 0 impressions (cancelled accounts or accounts with paused campaigns). This was addressed by adding a separate IF statement to the emergency detection logic, checking whether the account demonstrated greater than 200 impressions during the same hour in question, but on the previous week. If the statement returns true, we can reasonably expect that the account is supposed to be getting impressions during that hour, and 0 impressions during this time is a real emergency. If it returns false however, we know that that either the account doesn’t usually get impressions during that time, or the account isn’t enabled currently.
What was the end result?
The following Adwords script uses about 250 lines of code to do a relatively simple task in regards to account management, but compared to other PPC scripts that attempt to accomplish the same thing, I believe this script is taking the most concise route, given the extra functionality I wanted to add for real time anomaly detection. Feel free to use and edit this code to match your specific needs.
Set up:
- Make a copy of this Google Spreadsheet: https://docs.google.com/spreadsheets/d/1PRcYbIYQ0Y0-Lwvcez8eHx6ukopFjV2aDMQKwRdSj3w/edit#gid=0
- Open your Google Adwords MCC Account > Bulk Operations > Scripts and add the following script
- Replace the email addresses in the EMAIL_ADDRESSES_TO_NOTIFY variable
- Replace the SHEETID variable with the id of the Google Sheet you just created
- Set the script to run hourly
// This script gets the number of impressions on the account. If the number of impressions < // 0, it will send an emergency email to the email addresses in the EMAIL_ADDRESSES_TO_NOTIFY // variable.
// --------------------------START----------------------------//
var accountIterator = MccApp.accounts().get();
var mccAccount = AdWordsApp.currentAccount();
var EMAIL_ADDRESSES_TO_NOTIFY = ["email1@example"," email2@example "," email3@example "];
var SHEETID = "Replace me";
// Resets the Sent Email? and Impressions columns to false at the start of a new day
function resetEmailSentAndImpressions(sheetID) {
var ss = SpreadsheetApp.openById(sheetID).getSheetByName('Sheet1');
var val = ss.getRange('A:D').getValues();
var length = 0;
for (var i = 0; i < val.length; i++) {
if (!val[i][0]) {
length = i;
break;
}
}
ss.getRange('B2:B'+length.toString()).setValue('false');
ss.getRange('D2:D'+length.toString()).setValue(0);
return "Reset all values in 'Sent Email?' and 'Impressions on last run' columns to false and 0";
}
// Opens the corresponding Google Sheet to set the 'Sent Email?' column to true or false for the AccountID
function setEmailSent(sheetID, accountID, value) {
var ss = SpreadsheetApp.openById(sheetID).getSheetByName('Sheet1');
var val = ss.getRange('A:B').getValues();
var length = val.length;
for (var i = 1; i < length; i++) {
if (val[i][0]) {
if (val[i][0] == accountID) {
var val = ss.getRange('B'+(i+1).toString());
val.setValue(value);
return 'set value to '+value;
}
}
else {
return 'could not find account ID '+accountID;
}
}
}
// Opens the corresponding Google Sheet to read whether an email has been sent or not from the account in question
function getEmailSent(sheetID, accountID) {
var ss = SpreadsheetApp.openById(sheetID).getSheetByName('Sheet1');
var val = ss.getRange('A:B').getValues();
var length = val.length;
for (var i = 1; i < length; i++) {
if (val[i][0]) {
if (val[i][0] == accountID) {
return val[i][1]
}
}
else {
var val = ss.getRange('A'+(i+1).toString());
val.setValue(accountID);
val = ss.getRange('B'+(i+1).toString());
val.setValue('false');
return false;
}
}
}
// Opens the corresponding Google Sheet to set the 'Impressions on last run' column to equal impressionsIn24Hours
function setImpressions(sheetID, accountID, value) {
var ss = SpreadsheetApp.openById(sheetID).getSheetByName('Sheet1');
var val = ss.getRange('C:D').getValues();
var length = val.length;
for (var i = 1; i < length; i++) {
if (val[i][0]) {
if (val[i][0] == accountID) {
var val = ss.getRange('D'+(i+1).toString());
val.setValue(value);
return 'set value to '+value;
}
}
else {
return 'could not find account ID '+accountID;
}
}
}
// Opens the corresponding Google Sheet to read number of impressions in the last 24 hours
function getImpressions(sheetID, accountID) {
var ss = SpreadsheetApp.openById(sheetID).getSheetByName('Sheet1');
var val = ss.getRange('C:D').getValues();
var length = val.length;
for (var i = 1; i < length; i++) {
if (val[i][0]) {
if (val[i][0] == accountID) {
return val[i][1]
}
}
else {
var val = ss.getRange('C'+(i+1).toString());
val.setValue(accountID);
val = ss.getRange('D'+(i+1).toString());
val.setValue(0);
return 0;
}
}
}
// Dates must be formatted for properly querying a specific date from AWQL
Date.prototype.yyyymmdd = function() {
var yyyy = this.getFullYear().toString();
var mm = (this.getMonth()+1).toString();
var dd = this.getDate().toString();
return yyyy + (mm[1]?mm:"0"+mm[0]) + (dd[1]?dd:"0"+dd[0]);
}
function getDateOneWeekBeforeToday() {
var tempDate = new Date();
tempDate.setDate(tempDate.getDate()-7)
var lastWeekDate = tempDate;
return lastWeekDate.yyyymmdd();
}
function getCurrentHour() {
today = new Date();
// Reporting on data using Eastern Standard Time
return today.getUTCHours()-5;
}
function setTimestamp(sheetID) {
var today = new Date();
var ss = SpreadsheetApp.openById(sheetID).getSheetByName('Sheet1');
var val = ss.getRange('F2');
val.setValue(today.toString());
}
function main() {
// Check if it is the first hour of the day. If it is, reset Sent Email? and Impressions on last run columns
if (getCurrentHour() == 0) {
Logger.log(resetEmailSentAndImpressions(SHEETID));
} else {
Logger.log("It is now hour " + getCurrentHour() + ". Starting hourly processing.");
Logger.log("\n");
}
setTimestamp(SHEETID);
while (accountIterator.hasNext()) {
var account = accountIterator.next();
// Switch to the account you want to process.
MccApp.select(account);
var ACCOUNTID = AdWordsApp.currentAccount().getCustomerId();
var ACCOUNTNAME = AdWordsApp.currentAccount().getName();
Logger.log("Started processing " + ACCOUNTNAME + " | " + ACCOUNTID);
function sendWarningEmail(emailAddresses, subject, body) {
var finalSubject = "WARNING - " + subject + " - " + ACCOUNTNAME + " (" + ACCOUNTID + ")";
var emailAddressesString = "";
for (var i = 0; i < emailAddresses.length; i++) {
if (i < emailAddresses.length-1) {
emailAddressesString += emailAddresses[i] + ",";
} else {
emailAddressesString += emailAddresses[i];
}
}
MailApp.sendEmail({to: emailAddressesString, subject: finalSubject, htmlBody: body });
Logger.log("Sent email to " + emailAddressesString);
}
function runQuery() {
var impressionsByHour = {};
var impressionsIn24Hours = 0;
var impressionsByHourLastWeek = {};
var impressionsIn24HoursLastWeek = 0;
var currentHour = getCurrentHour().toString();
var previousHour = (getCurrentHour()-1).toString();
var query = function(date, impressionsVar, impressionsIn24HoursVar) {
var query = AdWordsApp.report("SELECT Impressions, HourOfDay FROM ACCOUNT_PERFORMANCE_REPORT DURING " + date);
var rows = query.rows();
while (rows.hasNext()) {
var data = rows.next();
var hour = parseFloat(data["HourOfDay"]);
impressionsVar[hour] = data["Impressions"]
}
for (k in impressionsVar) {
impressionsIn24HoursVar += parseInt(impressionsVar[k]);
}
// We return this so we can assign it outside of the function
return impressionsIn24HoursVar;
}
impressionsIn24Hours = query("TODAY", impressionsByHour, impressionsIn24Hours);
// We pass in two dates separated by a comma and a space because of the nature of specific date requesting within AWQL
impressionsIn24HoursLastWeek = query(getDateOneWeekBeforeToday() + ", " + getDateOneWeekBeforeToday(), impressionsByHourLastWeek, impressionsIn24HoursLastWeek);
// Get the total impressions on last run before we overwrite it with the current total impressions in last 24 hours
var totalImpressionsOnLastRun = getImpressions(SHEETID, ACCOUNTID);
getEmailSent(SHEETID, ACCOUNTID);
Logger.log(setImpressions(SHEETID, ACCOUNTID, impressionsIn24Hours));
var subject = "Warning on Adwords Account";
var body = "<h3>Whoa there cowboy! One of your accounts has gotten 0 impressions.</h3><strong>Account: </strong>"+ACCOUNTNAME+
"<br><strong>ID: </strong>"+ACCOUNTID+"<br><strong>Impressions during hour "+
previousHour+":</strong> "+(impressionsIn24Hours-totalImpressionsOnLastRun)+"<br><strong>Impressions in the last 24 hours: </strong>"+
impressionsIn24Hours+"<br><strong>Impressions during hour "+previousHour+" last week:</strong> "+impressionsByHourLastWeek[getCurrentHour()-1] +
"<br><strong>Impressions in the last 24 hours last week: </strong>"+impressionsIn24HoursLastWeek+"<br><br>Since this account has previously demonstrated " +
" impressions during this period, it is highly recommended that you contact your account administrators to find out the cause.<br><br>"+
"Sincerely,<br><i>Your Management Team</i>";
// Check to make sure total impressions on most recent run is GREATER THAN total impressions on last run. (That would mean we are getting impressions) (Unless it's the first hour of the day)
if (impressionsIn24Hours > totalImpressionsOnLastRun) {
Logger.log("Everything looks okay! You've gotten impressions since the last time I ran!");
}
// If total impressions on most recent run is LESS THAN total impression on last run, it must mean that this is the first run of the day, because it's impossible to lose impression that we had previously. Reset data in the sheet.
else if (impressionsIn24Hours < totalImpressionsOnLastRun) {
Logger.log("First run of the day.");
}
// If the two are EQUAL, it means that since the last time the script ran, no new impressions have been recorded
else if (impressionsIn24Hours == totalImpressionsOnLastRun) {
// We have to go back a week and check if it is normal for there to be no impressions during the previous hour
// (We check to make sure there are more than 200 impressions last week instead of zero to filter out smaller accounts who have inconsistent impression trends at off peak hours.)
if (impressionsByHourLastWeek[getCurrentHour()-1] > 200) {
// There is a problem, but let's see if we've already sent an email
if (getEmailSent(SHEETID, ACCOUNTID) == false) {
// We really have a problem.
Logger.log("Warning! You have no new impressions on this account but you had impressions during this hour last week!");
sendWarningEmail(EMAIL_ADDRESSES_TO_NOTIFY, subject, body);
setEmailSent(SHEETID, ACCOUNTID, 'true');
}
else {
Logger.log("Email already sent today");
}
}
else {
Logger.log("You have no new impressions on this account during the last hour, but this is consistent with last week's data. (Or the number of impressions falls below the cut off point of 200.");
}
}
Logger.log("Impressions last hour: " + (impressionsIn24Hours - totalImpressionsOnLastRun) + ".");
Logger.log("Impressions last hour (last week): " + impressionsByHourLastWeek[getCurrentHour()-1] + ".");
Logger.log("\n")
}
runQuery();
}
Logger.log("Script finished");
}
// ---------------------------END-----------------------------//
no replies