Google Ads MCC Spend Check

Overview

This script will drop spend from all accounts in an MCC (with a 60 minute runtime limit on the script, so your MCC can't have too many accounts, or you'll have to apply some withCondition filters to the script) into a sheet at a customizable frequency (every X hours, with the current default set to 6 hours). If no spend has occurred in any of the accounts within that window, you can set this Sheet up to send you an email alert (again, every X hours per your settings) with a list of the accounts without spend. You can also just unhide the Spend Import and Spend Check tabs to just use the sheet as a raw data source.

Getting Set Up

1. To get started, click the button above to make a copy of the Google Sheet template

2. Go to the Settings tab and enter your Email in C3 and your desired frequency in cell C4

   • • Note: The frequency will dictate how often you want to check for spend. In this case, the sheet is set to 6 by default. So the Google Ads script (below) will check spend for all accounts within the MCC every 6 hours. If any accounts have not spend within "the last 6 hours," then they will be filtered into the Spend Report tab for your reference. Adjust this as you see fit – options range from every 1 to every 24 hours).

3. Once you've added your email and set your desired frequency, you can set up the scheduled email alerts if you want them. Simply do this by going to Sheet Controls > Schedule Function To Run Hourly.

   • • If you ever want to stop receiving emails entirely, you can select Sheet Controls > Clear Email Triggers

     • You can test out the function (to see how it works) by selecting Sheet Controls > Run Spend Check Test. A note will appear in the bottom right corner with the results from the function. You will only every receive an email if (1) you're one hour after the correct hour of the day (i.e. if your frequency is 6, you'll only be eligible to receive emails at 1am, 7am, 1pm, and 7pm) AND if there are accounts that have not spend in the last 6 hour window being reported. The note at the bottom right will tell you which of those conditions is not being met if you don't receive an email.

4. If there are accounts that you don't want monitored here, you can simply go to the Account Mute List tab to "mute" alerts for them. Just remember to check this occasionally so you don't forget that they're there!

   • • This result can also be achieved by adding withCondition filters onto the script below. See the link in the overview section for details on how to set this up so that it will check for Account Labels in the MCC or other conditions.

5. If you want to just use the imports for something else, you can unhide the Spend Import and Spend Check tabs to just use the sheet as a raw data source.

6. That'll do it for the Google Sheet – now we just need to set up the script in your Google Ads MCC. Simply create a new Script in your MCC account. Paste in the script below, change MY_GOOGLE_SHEET_URL to the link from the Google Sheet that you created above, authorize & run the script. Then just schedule the script to run hourly (again, it will only deposit data at the frequency you specified in your Google Sheet Settings tab but you need it to run hourly in order to do so), and you're all set!

function main() {

  //***START CUSTOM VARIABLES***//

    //to adjust the frequency of the spend check (i.e. every hour, 6 hours, 12 hours, etc.) go to the Settings tab of your Google Sheet template and edit cell C4

    //URL of Google Sheet to push data to

      var spreadsheetUrl = 'MY_GOOGLE_SHEET_URL';

  //***END CUSTOM VARIABLES***//

  //name of tab on sheet where data will be delivered

    var sheetTabName = 'Spend Import';

  //report type – see documentation here: https://developers.google.com/adwords/api/docs/appendix/reports/account-performance-report

    var reportType = 'ACCOUNT_PERFORMANCE_REPORT';

  //select metrics from report above – see link from documentation – make sure all terms are comma separated with a space afterwards

   var metrics = 'ExternalCustomerId, CustomerDescriptiveName, Date, HourOfDay, Cost';

  //if you want to add a filter to your data using one of the metrics from the selected report, simply un-comment line 49 below and customize the filter..

  //..selector using a metric from the report you selected

  //get script spend check frequency

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var settingsSheet = spreadsheet.getSheetByName('Settings');

    var frequency = settingsSheet.getRange('C4').getValue();

  //end date (default is today's date – if other date is desired, simply change the to variable to a specific date formatted like the startDate variable above.)

    var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

    var now = new Date();

    var currentHour = Utilities.formatDate(now, "America/New_York", "H");

    //set frequency check an hour ahead to ensure that data is loaded before sheet script runs

    var frequencyCheck = (currentHour % frequency) + 1;

    var startDate = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

    var from = Utilities.formatDate(startDate, "America/New_York", "yyyyMMdd");

    var to = Utilities.formatDate(now, "America/New_York", "yyyyMMdd");

    var dateRange = from+','+to;

  if (frequencyCheck == 0) {

    //account iterator – currently gets all accounts, but can be adjusted to filter for specifics using this documentation:

    //https://developers.google.com/google-ads/scripts/docs/examples/ads-manager-scripts

    var accountIterator = MccApp.accounts().get();

    var mccAccount = AdWordsApp.currentAccount();

    var sheet = spreadsheet.getSheetByName(sheetTabName);

    var headers = metrics.split(', ');

    var dataArray = [];

    //pull report and append rows of data

    while (accountIterator.hasNext()) {

      var account = accountIterator.next();

      MccApp.select(account);

      var report = AdWordsApp.report(

        'SELECT ' + metrics + ' ' +

        'FROM ' + reportType + ' ' +

        //'WHERE CampaignStatus = "ENABLED" ' +

        'DURING ' + dateRange);

      var rows = report.rows();

      while (rows.hasNext()) {

        var row = rows.next();

        var rowArray = [];

        for (var i = 0; i < headers.length; i++) {

          rowArray.push(row[headers[i]]);

        }

        dataArray.push(rowArray);

      }

    }

    //clear sheet & add header using metrics variable

    sheet.clearContents();

    sheet.appendRow(headers);

    sheet.getRange(2, 1, dataArray.length, headers.length).setValues(dataArray);

  }

  else {

    Logger.log('No updates to spend data for another '+(frequency - frequencyCheck)+' hour(s).');

  }

}