This is the legacy documentation for Google Ads scripts. Go to the current docs.

Manager Account Ad Performance Report

Stay organized with collections Save and categorize content based on your preferences.

Manager Account Ad Performance Report extends Ad Performance Report to work for multiple accounts.

Manager Account Ad Performance Report is an example of advanced reporting functionality provided by Google Ads scripts. Advertisers like to analyze how their ads are performing in their campaigns. Sometimes, comparing how a given headline or final URL performs against others will provide insight in creating new ads. Manager Account Ad Performance Report generates a Google Spreadsheet with a number of interesting distribution charts.

A new Manager Account Ad Performance report gets created whenever the script executes. You can access all of these reports in Google Drive. Optionally, the script can also email the report to one or more recipients.

Scheduling

The script uses last week's statistics to generate the report. Schedule it Weekly, on Mondays.

How it works

The script starts off creating a copy of a template spreadsheet, with all graphs pre-configured. The script then populates the data values in the Report sheet, and graphs in the other sheets get constructed automatically.

Setup

  • Create a new script with the source code below.
  • Update the ACCOUNTS in case you need to run report only on a subset of accounts under your manager account.
  • Don't forget to update RECIPIENT_EMAIL in order to specify your email preference.
  • Schedule to run Weekly, on Mondays.

Source code

// Copyright 2016, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Manager Account Ad Performance Report
 *
 * @overview The Ad Performance Report generates a Google Spreadsheet that
 *     contains ad performance stats like Impressions, Cost, Click Through Rate,
 *     etc. as several distribution charts for an advertiser account. Visit
 *     https://developers.google.com/google-ads/scripts/docs/solutions/adsmanagerapp-ad-performance
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.0.1
 *
 * @changelog
 * - version 1.0.1
 *   - Added validation for spreadsheet URL and email address.
 * - version 1.0
 *   - Released initial version.
 */

// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = 'email@example.com';

// If specific accounts should be used, add them here, for example:
// var ACCOUNTS = ['123-456-7890', '234-567-8901', '345-678-9012'];
var ACCOUNTS = [];

// URL of the default spreadsheet template. This should be a copy of:
//
// https://docs.google.com/spreadsheets/d/1qWDfOhWBZpsWWDuMJ5W4Zm-zIY8z0wls56ngp8azM6o
//
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

// The maximum number of accounts that Google Ads Scripts can process in parallel.
var MAX_ACCOUNTS = 50;

/**
 * Entry-point for execution.
 */
function main() {
  validateEmail(RECIPIENT_EMAIL);
  var accountSelector = AdsManagerApp.accounts();
  if (ACCOUNTS.length) {
    accountSelector = accountSelector.withIds(ACCOUNTS);
  }
  accountSelector.withLimit(MAX_ACCOUNTS)
      .executeInParallel('processAccount', 'processResults');
}

/**
 * Defines a row created from the results of the AD_PERFORMANCE_REPORT query.
 * @typedef {Object} ResultRow
 * @property {number} impressions The number of impressions in the time period.
 * @property {number} clicks The number of clicks in the time period.
 * @property {number} cost The associated cost in the given period.
 * @property {string} finalUrl The associated URL.
 * @property {string} headline The headline of the Ad.
 */

/**
 * Retrieves performance data for each enabled Ad in the account that has had
 * impressions in the last week.
 * @return {string} A stringified-set of results of form Array.<ResultRow>
 */
function processAccount() {
  var AD_PERFORMANCE_REPORT_QUERY = [
    'SELECT Impressions, Clicks, Cost, CreativeFinalUrls, Headline,',
    'HeadlinePart1, HeadlinePart2, LongHeadline',
    'FROM AD_PERFORMANCE_REPORT',
    'WHERE Status = "ENABLED" AND AdGroupStatus = "ENABLED" AND',
    'CampaignStatus = "ENABLED" AND Impressions > 0 DURING LAST_WEEK'
  ].join(' ');

  var ads = [];
  var report = AdsApp.report(AD_PERFORMANCE_REPORT_QUERY);
  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var headline = '';
    if (row.Headline) {
      headline = row.Headline;
    } else if (row.HeadlinePart1) {
      // Expanded text ads have a headline in two parts.
      headline = row.HeadlinePart2 ?
          row.HeadlinePart1 + ' - ' + row.HeadlinePart2 :
          row.HeadlinePart1;
    } else if (row.LongHeadline) {
      // Responsive display ads have a long and short headline.
      headline = row.LongHeadline;
    }
    ads.push({
      impressions: formatNumber(row.Impressions),
      clicks: formatNumber(row.Clicks),
      cost: formatNumber(row.Cost),
      finalUrl: row.CreativeFinalUrls,
      headline: headline
    });
  }
  return JSON.stringify(ads);
}

/**
 * Combines the results of querying AD_PERFORMANCE_REPORT on each account,
 * and writes the results to a newly-created spreadsheet which is emailed to
 * the user.
 * @param {!Array.<!AdsManagerApp.ExecutionResult>} executionResults
 */
function processResults(executionResults) {
  var error = false;
  var results = [];
  for (var i = 0; i < executionResults.length; i++) {
    var result = executionResults[i];
    if (result.getError()) {
      error = true;
      break;
    }
    var data = JSON.parse(result.getReturnValue());
    Array.prototype.push.apply(results, data);
  }

  if (!error) {
    var spreadsheet = createReport(results);
    sendSuccessEmail(spreadsheet.getUrl());
  } else {
    sendFailureEmail(AdsApp.currentAccount().getCustomerId());
  }
}

/**
 * Creates a spreadsheet from the combined results from all accounts.
 * @param {!Array.<!ResultRow>} results
 * @return {!Spreadsheet}
 */
function createReport(results) {
  var rowsByHeadline = groupArray(results, 'headline');
  var rowsByFinalUrl = groupArray(results, 'finalUrl');

  Logger.log('Using template spreadsheet - %s.', SPREADSHEET_URL);
  var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
  Logger.log(
      'Generated new reporting spreadsheet %s based on the template ' +
      'spreadsheet. The reporting data will be populated here.',
      spreadsheet.getUrl());

  writeToNamedRange(spreadsheet, 'headline_top_left', rowsByHeadline);
  writeToNamedRange(spreadsheet, 'final_url_top_left', rowsByFinalUrl);

  var customerId = AdsApp.currentAccount().getCustomerId();
  writeToNamedRange(spreadsheet, 'account_id_headline', customerId);
  writeToNamedRange(spreadsheet, 'account_id_final_url', customerId);

  var today = getDateStringInTimeZone('MMM dd, yyyy');
  writeToNamedRange(spreadsheet, 'headline_date', today);
  writeToNamedRange(spreadsheet, 'final_url_date', today);
  return spreadsheet;
}

/**
 * Creates a copy of a specified spreadsheet.
 * @param {string} spreadsheetUrl The URL of the spreadsheet to copy.
 * @return {!Spreadsheet} The newly-created spreadsheet.
 */
function copySpreadsheet(spreadsheetUrl) {
  var today = getDateStringInTimeZone('MMM dd, yyyy HH:mm:ss z');
  var spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl)
      .copy('Ad Performance Report - ' + today);

  // Make sure the spreadsheet is using the account's timezone.
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  return spreadsheet;
}

/**
 * Converts a string representation of a number to a number, removing commas.
 * @param {string} numStr The number to convert.
 * @return {number} The resulting number.
 */
function formatNumber(numStr) {
  return parseFloat(numStr.replace(',', ''));
}

/**
 * Extends a Sheet to meet the number of required rows, where necessary
 * @param {!Sheet} sheet The Sheet object
 * @param {number} requiredRows The number of rows that are required in total.
 */
function extendSheet(sheet, requiredRows) {
  if (requiredRows > sheet.getMaxRows()) {
    sheet.insertRowsAfter(
        sheet.getMaxRows(), requiredRows - sheet.getMaxRows());
  }
}

/**
 * Writes either a value or a 2D array to a spreadsheet, starting at the cell
 * specified top-left by a NamedRange.
 * @param {!Spreadsheet} spreadsheet The spreadsheet to write to.
 * @param {string} rangeName The name of the NamedRange to start at.
 * @param {string|number|!Date|!Array.<!Array.<string|number|!Date>>} data The
 *     data to write, either:
 * <ul>
 * <li>A single value, which is written to the cell.</li>
 * <li>A two-dimensional array, which is written starting at the cell.</li>
 * </li>
 */
function writeToNamedRange(spreadsheet, rangeName, data) {
  var namedRange = spreadsheet.getRangeByName(rangeName);
  var sheet = namedRange.getSheet();
  var col = namedRange.getColumn();
  var row = namedRange.getRow();

  if (Array.isArray(data)) {
    // Write two-dimensional data
    if (data.length && data[0].length) {
      extendSheet(sheet, row + data.length - 1);
      sheet.getRange(row, col, data.length, data[0].length).setValues(data);
    }
  } else if (data) {
    // Write single value to the named range.
    sheet.getRange(row, col).setValue(data);
  }
}

/**
 * Defines an aggregated row of data, for writing to the final spreadsheet.
 * @typedef {Array} GroupedRow
 * @property {string} 0 The value grouped by
 * @property {number} 1 The total number of Ads.
 * @property {number} 2 The total number of impressions.
 * @property {number} 3 The total number of clicks.
 * @property {number} 4 The click-through-rate (CTR).
 * @property {number} 5 The total cost.
 */

/**
 * Aggregates a 2D array of data around a given property.
 * @param {!Array.<!ReportRow>} reportRows The data to aggregate
 * @param {string} groupingKey The property name about which to aggregate.
 * @return {!Array.<!GroupedRow>} The aggregated data
 */
function groupArray(reportRows, groupingKey) {
  var rows = [];
  var group = {};
  for (var i = 0; i < reportRows.length; i++) {
    var reportRow = reportRows[i];
    if (!group[reportRow[groupingKey]]) {
      group[reportRow[groupingKey]] =
          {numAds: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
    }
    var data = group[reportRow[groupingKey]];
    data.numAds++;
    data.totalImpressions += reportRow.impressions;
    data.totalClicks += reportRow.clicks;
    data.totalCost += reportRow.cost;
  }

  var groupedKeys = Object.keys(group);
  for (var j = 0; j < groupedKeys.length; j++) {
    var groupedRow = group[groupedKeys[j]];
    var ctr = (groupedRow.totalClicks * 100) / groupedRow.totalImpressions;
    rows.push([
      groupedKeys[j], groupedRow.numAds, groupedRow.totalImpressions,
      groupedRow.totalClicks, ctr, groupedRow.totalCost
    ]);
  }
  return rows;
}

/**
 * Produces a formatted string representing a given date in a given time zone.
 *
 * @param {string} format A format specifier for the string to be produced.
 * @param {?Date=} opt_date A date object. Defaults to the current date.
 * @param {string=} opt_timeZone A time zone. Defaults to the account time zone.
 * @return {string} A formatted string of the given date in the given time zone.
 */
function getDateStringInTimeZone(format, opt_date, opt_timeZone) {
  var date = opt_date || new Date();
  var timeZone = opt_timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * Sends an email to the user with the link to the spreadsheet.
 *
 * @param {string} url URL of the spreadsheet.
 */
function sendSuccessEmail(url) {
  var footerStyle = 'color: #aaaaaa; font-style: italic;';
  var scriptsLink = 'https://developers.google.com/google-ads/scripts/';
  var subject = 'Manager Account Ad Performance Report - ' +
      getDateStringInTimeZone('MMM dd, yyyy');
  var htmlBody = '<html><body>' +
      '<p>Hello,</p>' +
      '<p>A Google Ads Script has run successfully and the output is' +
      'available here:' +
      '<ul><li><a href="' + url +
      '">Manager Account Ad Performance Report</a></li></ul></p>' +
      '<p>Regards,</p>' +
      '<span style="' + footerStyle + '">This email was automatically ' +
      'generated by <a href="' + scriptsLink + '">Google Ads Scripts</a>.' +
      '</span></body></html>';
  var body = 'Please enable HTML to view this report.';
  var options = {htmlBody: htmlBody};
  MailApp.sendEmail(RECIPIENT_EMAIL, subject, body, options);
}

/**
 * Sends an email to the user notifying them of a failed execution.
 *
 * @param {string} mccId The ID of the Manager Account.
 */
function sendFailureEmail(mccId) {
  var footerStyle = 'color: #aaaaaa; font-style: italic;';
  var scriptsLink = 'https://developers.google.com/google-ads/scripts/';
  var subject = '[Failure] Manager Account Ad Performance Report - ' +
      getDateStringInTimeZone('MMM dd, yyyy');
  var htmlBody = '<html><body>' +
      '<p>Hello,</p>' +
      '<p>A Google Ads Script has run unsuccessfully for Manager Account: ' +
      mccId + '.</p>' +
      '<p>For further details on this error, please log into the account and ' +
      'examine the execution logs</p>' +
      '<span style="' + footerStyle + '">This email was automatically ' +
      'generated by <a href="' + scriptsLink + '">Google Ads Scripts</a>.' +
      '</span></body></html>';
  var body = 'Please enable HTML to view this email.';
  var options = {htmlBody: htmlBody};
  MailApp.sendEmail(RECIPIENT_EMAIL, subject, body, options);
}

/**
 * Validates the provided email and throws a descriptive error if the user
 * has not changed the email from the default fake address.
 *
 * @param {string} email The email address.
 * @throws {Error} If the email is the default fake address.
 */
function validateEmail(email){
  if (email == "email@example.com") {
    throw new Error('Please use a valid email address.');
  }
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}