Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A zero impressions emergency Google Adwords script for assisting in account management and emergency response.
// 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-----------------------------//
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.