A zero impressions emergency Google Adwords script for assisting in account management and emergency response.

zero-impressions-emergency-alarm.js

// This script gets the number of impressions on the account. If the number of impressions < 0, it 
// will send emergency email to Roger, Steve, Melissa & Jordan.

// --------------------------START----------------------------//

// Add your email address to be notified in an account emergency.
var EMAIL_ADDRESSES_TO_NOTIFY = ["test@email.com"];

// Corresponds with Google Sheet "Impressions Emergency Script Global Variables"
var SHEETID = "yourcustomsheetidhere";
var ACCOUNTID = AdWordsApp.currentAccount().getCustomerId();
var ACCOUNTNAME = AdWordsApp.currentAccount().getName();

function sendWarningEmail( emailAddresses, subject, body ) {
	var finalSubject = "WARNING - " + subject + " - " + ACCOUNTNAME +
	" (" + ACCOUNTID + ")";
	
	for ( var i = 0; i < emailAddresses.length; i++ ) {
		MailApp.sendEmail({to: emailAddresses[i], subject: finalSubject, htmlBody: body });
		Logger.log( "Sent email to " + emailAddresses[i] );
	}
}

// Resets the Sent Email? column to false at the start of a new day
function resetEmailSent(sheetID) {
  
}

// 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;
    }
  }
}

// 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 main() {
	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);
	
	var subject = "Warning on Adwords Account";
	var body = "<h3>One of your accounts has gotten 0 impressions.</h3><strong>Account: </strong>"+ACCOUNTNAME+
	"<br><strong>ID: </strong>"+ACCOUNTID+"<br><strong>Impressions from hour "+previousHour+" to hour "+
	currentHour+":</strong> "+impressionsByHour[getCurrentHour()-1] + "<br><strong>Impressions in the last 24 hours: </strong>"+
	impressionsIn24Hours+"<br><strong>Impressions from hour "+previousHour+" to hour "+currentHour+" 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 Digital Position representatives to find out the cause.<br><br>"+
	"Sincerely,<br><i>Digital Position</i>";
	
	Logger.log("Impressions from hour " + previousHour + " to hour " + currentHour + ": " + impressionsByHour[getCurrentHour()-1]);
	Logger.log("Impressions in the last 24 hours: " + impressionsIn24Hours);
	Logger.log("Impressions from hour " + previousHour + " to hour " + currentHour + " last week: " + impressionsByHourLastWeek[getCurrentHour()-1]);
	Logger.log("Impressions in the last 24 hours last week: " + impressionsIn24HoursLastWeek);
	
    // If an email hasn't been sent already, and if the account had impressions during the last hour last week, but shows no impressions this week, send warning email!
	if (getEmailSent(SHEETID, ACCOUNTID) == false && impressionsByHourLastWeek[getCurrentHour()-1] > 0 && impressionsByHour[getCurrentHour()-1] < 1) {
		sendWarningEmail( EMAIL_ADDRESSES_TO_NOTIFY, subject, body );
        Logger.log("Warning!");
        setEmailSent(SHEETID, ACCOUNTID, 'true');
	}
	else if (getEmailSent(SHEETID, ACCOUNTID) == true && impressionsByHourLastWeek[getCurrentHour()-1] > 0 && impressionsByHour[getCurrentHour()-1] < 1) {
		Logger.log("Warning! - Email already sent today.");

	} 
	else {
		Logger.log("OK!");

	}
	
	Logger.log("Script finished");
}

// ---------------------------END-----------------------------//