MCC SCRIPT: HOURLY SCRIPT CHECKING IF YOUR ACCOUNTS ARE ALIVE

How about an early Holidays gift – a free script that notifies you about an epic fuck up right away? Ta-daaaaah! This is it.

This script will send you an email WHEN the impressions or conversion value drop to zero. No need to wait for the next day to find out.

WHY USE IT

Impressions

Impressions dropping to zero can signal:

  • Billing is not working
  • Bad auto-rule paused your campaigns
  • A specialist made some horrible mistake
  • Auto-bidding script went crazy and now all your bids are zero etc.

Conversion value

Revenue or whatever you report in conversion value flatlining can mean:

  • Website is down and you should pause campaigns
  • Tracking is screwed up
  • Developers updated checkout and now it doesn’t work etc.

HOW SCRIPT WORKS

grumpy ppc - screenshot of dashboard of the hourly script that checks if your accounts are alive
  1. Every hour it downloads data for each account with an hour segment.
  2. It checks what time is NOW and compares it with the downloaded data.
  3. It looks at the delay you set up. Let’s say you put 1 hour delay.
  4. If now it is 4:20pm and the script ran, an hour delay setting will check if 4-1=3 >> from 3pm to 4pm there was missing data.
  5. If yes – you get an email.

CUSTOMIZATION

You can customize this script in the “SETUP” part of the spreadsheet.

  1. Hours of delay.
    Choose how many hours back you want to check the data.
    “1” means previous hour, ex. at 3:40pm script with check data from 2:00pm to 3:00pm
  2. During hours between.
    Put the hours in the 24 hour format as they are reported in google ads interface. 6 to 20 would mean from 6am to 8pm.

    * If checking conversion value is not applicable for you business/clients, just delete the numbers in C21 and D21 and the script will only report impressions problems.
  3. Counting w/ time zones
    Sometimes accounts are in different time zones cuz your company’s exporting or you country is huge. You can setup a country adjustment:
    “1” would mean an account’s time setup is 1 hour ahead of you, “-3” would mean the account’s time is 3 hours behind you.

    * If all you accounts are in the same time zone as you are, just delete these sample accounts.

    ** Yes, I’m aware with huge time zone differences it might not make sense, but hey, it’s a free script. If you have a business with accounts over 12 different time zones I imagine you have your own developer to add this.

FALSE POSITIVES ?

Seeing some false-positives in YOUR ALERT EMAIL section?
Don’t worry. You won’t get the email.
When the script runs on schedule it will download fresh report and send only up-to-date information.

The report you see here (in columns G and I) might not be the actual email you’re going to receive.

Example when it can happen.

  1. last time script ran at 15:40 and there wasn’t an account with 0 impressions from 14 to 15. all good.
  2. the script correctly didn’t send you the email.
  3. however cell M8 is dynamic, so when it switched to 16, “an hour ago” would mean “from 15 to 16” and that data is not yet downloaded.
  4. hence in between the script runs you can see here false-positives

GOOGLE’S FALSE POSITIVES

You know like sometimes Google has a reporting delay because of their own server issues? Well this will cause this script to send you a false-positive. If it useful?

Well, I prefer to get a notification and then check on twitter that there’s an outage than regret not receiving an actual fuck up update.

I’ve been using this script now for a year and got only 3 google false positives. 3 false-positives in a year – I’m fine with that margin of error.

HOW TO SETUP

spreadsheet to copy

  1. Copy the spreadsheet above.
    Click File > Make a copy
  2. Change the settings in the “SETUP” how you want it to be. No need to delete my sample data. Your report will overwrite it.
  3. Paste the URL of the new spreadsheet in the code to my_sheet in the beginning of the code.
  4. Update the mailto on the second line with your actual email.
  5. Add the label in label_for_accounts for account you want to be checking.
  6. Authorize and launch the script.
  7. Set it up to run hourly.

MEGA IMPORTANT TIP

Did you know when you launch a script and schedule it to run hourly it will actually run around the same minutes as the original launch in the future?

What I mean is. If you first launch script at 16:01 and then schedule it hourly, it will run in future around XX:01.

The problem! When you want data from 15:00 to 16:00 at 16:01 there’s a 99% chance there’ll be no data. However around 16:40-16:50 it will be reported.

So I strooooongly advise to first launch the script at XX:40 ish and then set it run hourly.

SO COPY THE CODE
AND TEST IT

spreadsheet to copy

function main() {
  
var mailto = "YOUR_EMAIL";  // add you email to get notifications
  
var my_sheet = "https://docs.google.com/spreadsheets/blablabla";  // add here url of the spreadsheet copy you made  

var label_for_accounts = "YOUR_LABEL" // add here the label you've added to accounts that you want to monitor. If you want to use script on all account find the delete ".withCondition('LabelNames CONTAINS ' + label_for_accounts)"

Logger.log("script is starting");
  
var sheet_input = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('input');
    sheet_input.clearContents();
  
var columns = [
 'Account Name',
 'Impressions',
 'ConversionValue',
 'HourOfDay'
 ];  
  
  
sheet_input.appendRow(columns);


Logger.log("----------------------");  
Logger.log("getting hourly data for accounts");
Logger.log("go get some coffee meanwhile...");
  
var accounts = AdsManagerApp.accounts().withCondition('LabelNames CONTAINS ' + label_for_accounts).get();

  
while (accounts.hasNext()) {
var account = accounts.next();
AdsManagerApp.select(account) 
var account_name = account.getName();
  
  
var data = [];
var columnsStr = columns.join(',') + " ";  
 
  
var report = AdsApp.report("SELECT Impressions, ConversionValue, HourOfDay " +
                               "FROM ACCOUNT_PERFORMANCE_REPORT " +
                               "DURING TODAY");
  
 var rows = report.rows(); 
 while (rows.hasNext()) {
 var row = rows.next(); 
 
 data.push(row[account_name]); 



 
 sheet_input.appendRow([account_name, row['Impressions'], row['ConversionValue'], row['HourOfDay']]);  
  
  
}
}
  
Logger.log("----------------------");  
Logger.log("data reporting is finished"); 
Logger.log("----------------------");  
Logger.log("verifying email sending"); 
  
var sheet_with_data = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');  
  
  
  
/*************** EMAIL IMPRESSIONS ****************************/  
  

 var ImpressionsEmaildata = sheet_with_data.getRange("G10:G100").getValues();

Logger.log("----------------------");  
Logger.log("CHECKING IMPRESSIONS");
  
var  ImpressionsEmailrow = 0;
  var ImpressionsEmailcol = 0;
      if (ImpressionsEmaildata[ImpressionsEmailrow][ImpressionsEmailcol] === "" || 0) {
        Logger.log("Nothing to send, all good");
      }
  else
       sendEmailImpressions();
  
  
/*************** EMAIL CONVERSIONS ****************************/  

var sheet_with_data = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');  

Logger.log("----------------------");  
Logger.log("CHECKING REVENUE");  

 var ConversionsEmaildata = sheet_with_data.getRange("I10:I100").getValues();
  
var  ConversionsEmailrow = 0;
  var ConversionsEmailcol = 0;
      if (ConversionsEmaildata[ConversionsEmailrow][ConversionsEmailcol] === "" || 0) {
        Logger.log("Nothing to send, all good");
      }
  else
       sendEmailConversions();
  

  



function sendEmailImpressions(){
 var ImpressionsSpeadseet = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');
 var impressions_data_input = ImpressionsSpeadseet.getRange("G10:G100").getValues();
 var impressions_data = [i for each (i in impressions_data_input)if (isNaN(i))];

var TABLEFORMAT = 'cellspacing="10" cellpadding="0" dir="ltr" border="1" style="width:60%;table-layout:fixed;font-size:10pt;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:0.5px solid #fff;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';

for (row = 0; row<impressions_data.length; row++){
htmltable += '<tr>';  
for (col = 0 ;col<impressions_data[row].length; col++){
if (impressions_data[row][col] === "") {
htmltable += '</table>';
  } 
else
if (row === 0)  {
htmltable += '<th>' + impressions_data[row][col] + '</th>';
  }
else {
htmltable += '<td>' + impressions_data[row][col] + '</td>';
             }
} 
htmltable += '</tr>';
}
 htmltable += '</table>';

Logger.log(impressions_data);
   
MailApp.sendEmail(mailto, '💀 Critical alert - Some of your accounts stopped getting impressions','' ,{htmlBody: htmltable})
}



  
function sendEmailConversions(){
 var ConversionsSpeadseet = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');
 var conversions_data_input = ConversionsSpeadseet.getRange("I10:I100").getValues();
 var conversions_data = [i for each (i in conversions_data_input)if (isNaN(i))];


var TABLEFORMAT = 'cellspacing="10" cellpadding="0" dir="ltr" border="1" style="width:60%;table-layout:fixed;font-size:10pt;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:0.5px solid #fff;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';
for (row = 0; row<conversions_data.length; row++){
     htmltable += '<tr>';  
for (col = 0 ;col<conversions_data[row].length; col++){
 if (conversions_data[row][col] === "") {
    htmltable += '</table>';
  } 
 
  else
    if (row === 0)  {
htmltable += '<th>' + conversions_data[row][col] + '</th>';
    }
 
  else {
htmltable += '<td>' + conversions_data[row][col] + '</td>';
             }
} 
htmltable += '</tr>';

}
   
   htmltable += '</table>';

Logger.log(conversions_data);
     
  MailApp.sendEmail(mailto, '💀 Critical alert - Some of your accounts get no reported conversion value','' ,{htmlBody: htmltable})
}
  
Logger.log("----------------------");  
Logger.log("THE END");
}
9 replies on “ MCC SCRIPT: HOURLY SCRIPT CHECKING IF YOUR ACCOUNTS ARE ALIVE ”
  1. Hi Olena,

    thank you for sharing the script. I try to set up but there is an error:

    “ReferenceError: “MccApp” is not defined. (file Code.gs, line 27)”

    (I added the label “main” to the account). Could you please help me what is wrong.

  2. Hi , thank you for giving this script a try!
    1) Try to replace MccApp. with AdsManagerApp. – AdsManagerApp.accounts() should be the newer formula since Google changed Adwords to Ads and MCC to AdsManager. Let me know if it helps!
    2) Thank you for the feedback – I also updated the label entering in the beginning of the script, AdsManagerApp instead of MCC and AdsApp. instead of AdwordsApp.

  3. Thank you for your answer. The error only changed name of the function: ReferenceError: “AdsManagerApp” is not defined. (file Code.gs, line 27)

  4. Olena, I´m sorry, everything is all right, it was my dummy mistake. It must be implemented at MCC 😀

  5. Hello I am able to change this script to just account level and not MCC level? It errors as ReferenceError: “AdsManagerApp” is not defined. (file Code.gs, line 28)

  6. Hi Olena,

    Can I kindly ask you to update your script? 🙂 It works correctly but send an email without a list of the 0 impr. accounts, so I always need to open a spreadsheet.

Comments are closed.