Ad Performance Report

This script is for a single account. For operating on multiple accounts in a manager account, use the Ads Manager version of the script.

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. Ad Performance Report generates a Google Spreadsheet with a number of interesting distribution charts.

A new 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.
• Don't forget to update RECIPIENT_EMAIL in order to specify your email preference.
• Schedule to run Weekly, on Mondays.

Creating a Google Ads script

Creating a Google Ads script

1. In Google Ads, navigate to Scripts
2. Create a new script and give it a name; authorize the script
3. Copy-paste source code into the script (see below)
4. Save the script, click "Close".
5. If necessary, schedule the script

Source code

// Copyright 2015, 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 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. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/ad-performance
 *     for more details.
 *
 * @author Google Ads Scripts Team [[e-mail]]
 *
 * @version 1.0.1
 *
 * @changelog
 * - version 1.1
 *   - Updated to use expanded text ads.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */
// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = '[e-mail]';

// URL of the default spreadsheet template. This should be a copy of
// https://goo.gl/aN49Nk
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

/**
 * This script computes an Ad performance report
 * and outputs it to a Google spreadsheet.
 */
function main() {
  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());

  var headlineSheet = spreadsheet.getSheetByName('Headline');
  headlineSheet.getRange(1, 2, 1, 1).setValue('Date');
  headlineSheet.getRange(1, 3, 1, 1).setValue(new Date());
  var finalUrlSheet = spreadsheet.getSheetByName('Final Url');
  finalUrlSheet.getRange(1, 2, 1, 1).setValue('Date');
  finalUrlSheet.getRange(1, 3, 1, 1).setValue(new Date());
  spreadsheet.getRangeByName('account_id_headline').setValue(
      AdsApp.currentAccount().getCustomerId());
  spreadsheet.getRangeByName('account_id_final_url').setValue(
      AdsApp.currentAccount().getCustomerId());

  // Only include ad types on the headline sheet for which the concept of a
  // headline makes sense.
  outputSegmentation(headlineSheet, 'Headline', function(ad) {
    var headline;
    // There is no AdTypeSpace method for textAd
    if (ad.getType() === 'TEXT_AD') {
      headline = ad.getHeadline();
    } else if (ad.isType().expandedTextAd()) {
      var eta = ad.asType().expandedTextAd();
      headline = eta.getHeadlinePart1() + ' - ' + eta.getHeadlinePart2();
    } else if (ad.isType().gmailMultiProductAd()) {
      var gmailMpa = ad.asType().gmailMultiProductAd();
      headline = gmailMpa.getHeadline();
    } else if (ad.isType().gmailSinglePromotionAd()) {
      var gmailSpa = ad.asType().gmailSinglePromotionAd();
      headline = gmailSpa.getHeadline();
    } else if (ad.isType().responsiveDisplayAd()) {
      var responsiveDisplayAd = ad.asType().responsiveDisplayAd();
      headline = responsiveDisplayAd.getLongHeadline();
    }
    return headline;
  });
  outputSegmentation(finalUrlSheet, 'Final Url', function(ad) {
    return ad.urls().getFinalUrl();
  });
  Logger.log('Ad performance report available at\n' + spreadsheet.getUrl());
  if (validateEmailAddress(RECIPIENT_EMAIL)) {
    MailApp.sendEmail(RECIPIENT_EMAIL,
      'Ad Performance Report is ready',
      spreadsheet.getUrl());
  }
}

/**
 * Retrieves the spreadsheet identified by the URL.
 * @param {string} spreadsheetUrl The URL of the spreadsheet.
 * @return {SpreadSheet} The spreadsheet.
 */
function copySpreadsheet(spreadsheetUrl) {
  var spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl).copy(
      'Ad Performance Report - ' +
      getDateStringInTimeZone('MMM dd, yyyy HH:mm:ss z'));

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

/**
 * Generates statistical data for this segment.
 * @param {Sheet} sheet Sheet to write to.
 * @param {string} segmentName The Name of this segment for the header row.
 * @param {function(AdsApp.Ad): string} segmentFunc Function that returns
 *        a string used to segment the results by.
 */
function outputSegmentation(sheet, segmentName, segmentFunc) {
  // Output header row.
  var rows = [];
  var header = [
    segmentName,
    'Num Ads',
    'Impressions',
    'Clicks',
    'CTR (%)',
    'Cost'
  ];
  rows.push(header);

  var segmentMap = {};

  // Compute data.
  var adIterator = AdsApp.ads()
      .forDateRange('LAST_WEEK')
      .withCondition('Impressions > 0').get();
  while (adIterator.hasNext()) {
    var ad = adIterator.next();
    var stats = ad.getStatsFor('LAST_WEEK');
    var segment = segmentFunc(ad);
    // In the case of the headline segmentation segmentFunc will return null
    // where there is no headline e.g. an HTML5 ad or other non-text ad, for
    // which metrics are therefore not aggregated.
    if (segment) {
      if (!segmentMap[segment]) {
        segmentMap[segment] =
            {numAds: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
      }
      var data = segmentMap[segment];
      data.numAds++;
      data.totalImpressions += stats.getImpressions();
      data.totalClicks += stats.getClicks();
      data.totalCost += stats.getCost();
    }
  }

  // Write data to our rows.
  for (var key in segmentMap) {
    if (segmentMap.hasOwnProperty(key)) {
      var ctr = 0;
      if (segmentMap[key].numAds > 0) {
        ctr = (segmentMap[key].totalClicks /
          segmentMap[key].totalImpressions) * 100;
      }
      var row = [
        key,
        segmentMap[key].numAds,
        segmentMap[key].totalImpressions,
        segmentMap[key].totalClicks,
        ctr.toFixed(2),
        segmentMap[key].totalCost];
      rows.push(row);
    }
  }
  sheet.getRange(3, 2, rows.length, 6).setValues(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} date A date object. Defaults to the current date.
 * @param {string} timeZone A time zone. Defaults to the account's time zone.
 * @return {string} A formatted string of the given date in the given time zone.
 */
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * Validates the provided email address
 * to make sure that it's set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} emailAddress The email address to send the results.
 * @return {Spreadsheet} The email address, if it is not the default fake one.
 * @throws {Error} If the email address has not been changed from the default.
 */
function validateEmailAddress(emailAddress) {
  if (emailAddress == '[e-mail]') {
    throw new Error('Please specify a valid email or leave empty to not' +
    ' send any email.');
  }
  return emailAddress;
}

/**
 * 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 spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
}