![Reports icon](https://developers.google.com/static/google-ads/scripts/images/reports.png)

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](https://drive.google.com). Optionally,
the script can also email the report to one or more recipients.

![Ad performance report](https://developers.google.com/static/google-ads/scripts/images/ad-performance-report.png)

## 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](https://docs.google.com/spreadsheets/d/14QDdEWU9EUC19EmWNBhxmpijQMf3eDWWFfmFdIL0TXY/copy),
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

- Click the button to create the spreadsheet-based script in your Google
  Ads account.

  [Install the script template](https://ads.google.com/aw/bulk/scripts/new?solutionId=2)
- Click the button to make a copy of the template spreadsheet.

  [Copy the template spreadsheet](https://docs.google.com/spreadsheets/d/14QDdEWU9EUC19EmWNBhxmpijQMf3eDWWFfmFdIL0TXY/copy)
- Update `spreadsheet_url` and `recipient_emails` in your script.

- Schedule your script to run **Weekly, on Mondays**.

## 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 [adwords-scripts@googlegroups.com]
     *
     * @version 2.3
     *
     * @changelog
     * - version 2.3
     *   - Added discovery_carousel_ad and discovery_multi_asset_ad support
     * - version 2.2
     *   - Removed deprecated ad_group_ad.ad.gmail_ad.marketing_image_headline field.
     * - version 2.1
     *   - Split into info, config, and code.
     * - version 2.0
     *   - Updated to use new Google Ads scripts features.
     * - version 1.1
     *   - Updated to use expanded text ads.
     * - version 1.0.1
     *   - Improvements to time zone handling.
     * - version 1.0
     *   - Released initial version.
     */
    /**
     * Configuration to be used for the Ad Performance Report.
     */

    CONFIG = {
      // Array of recipient emails. Comment out to not send any emails.
      'recipient_emails': ['YOUR_EMAIL_HERE'],
      // URL of the default spreadsheet template. This should be a copy of
      // https://goo.gl/aN49Nk
      'spreadsheet_url': 'YOUR_SPREADSHEET_URL',

      'advanced_options': {
        /**
         * Adding new metrics to the list will not get them automatically included
         * unless corresponding changes are made in the spreadsheet and the code
         * section.
         * Removing fields in the list will result in the corresponding
         * field not being rendered in the report.
         */
        'fields': [
          'ad_group_ad.ad.id',
          'ad_group_ad.ad.type',
          'ad_group_ad.ad.text_ad.headline',
          'ad_group_ad.ad.expanded_text_ad.headline_part1',
          'ad_group_ad.ad.expanded_text_ad.headline_part2',
          'ad_group_ad.ad.responsive_display_ad.long_headline',
          'ad_group_ad.ad.video_responsive_ad.long_headlines',
          'ad_group_ad.ad.responsive_search_ad.headlines',
          'ad_group_ad.ad.app_engagement_ad.headlines',
          'ad_group_ad.ad.app_ad.headlines',
          'ad_group_ad.ad.call_ad.headline1',
          'ad_group_ad.ad.call_ad.headline2',
          'ad_group_ad.ad.local_ad.headlines',
          'ad_group_ad.ad.legacy_responsive_display_ad.long_headline',
          'ad_group_ad.ad.shopping_comparison_listing_ad.headline',
          'ad_group_ad.ad.smart_campaign_ad.headlines',
          'ad_group_ad.ad.video_ad.in_feed.headline',
          'ad_group_ad.ad.final_urls',
          'ad_group_ad.ad.discovery_multi_asset_ad.headlines',
          'ad_group_ad.ad.discovery_carousel_ad.headline',
          'metrics.clicks',
          'metrics.cost_micros',
          'metrics.impressions',
        ]
      }
    };
    const RECIPIENT_EMAILS = CONFIG.recipient_emails;
    const SPREADSHEET_URL = CONFIG.spreadsheet_url;
    const FIELDS = CONFIG.advanced_options.fields;

    /**
     * This script computes an Ad performance report
     * and outputs it to a Google spreadsheet.
     */
    function main() {
      console.log(`Using template spreadsheet - ${SPREADSHEET_URL}.`);
      const spreadsheet = copySpreadsheet(SPREADSHEET_URL);
      console.log(`Generated new reporting spreadsheet ${spreadsheet.getUrl()} ` +
          `based on the template spreadsheet. ` +
          `The reporting data will be populated here.`);

      const headlineSheet = spreadsheet.getSheetByName('Headline');
      headlineSheet.getRange(1, 2, 1, 1).setValue('Date');
      headlineSheet.getRange(1, 3, 1, 1).setValue(new Date());
      const 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', (adGroupAd) => {
        switch (adGroupAd.ad.type) {
          case 'TEXT_AD':
            return adGroupAd.ad.textAd.headline;
          case 'EXPANDED_TEXT_AD':
            return adGroupAd.ad.expandedTextAd.headlinePart1 + ' - ' +
                adGroupAd.ad.expandedTextAd.headlinePart2;
          case 'RESPONSIVE_DISPLAY_AD':
            return adGroupAd.ad.responsiveDisplayAd.longHeadline.text;
          case 'VIDEO_RESPONSIVE_AD':
            return adGroupAd.ad.videoResponsiveAd.longHeadlines.map(
                asset => asset.text);
          case 'RESPONSIVE_SEARCH_AD':
            return adGroupAd.ad.responsiveSearchAd.headlines.map(
                asset => asset.text);
          case 'APP_ENGAGEMENT_AD':
            return adGroupAd.ad.appEngagementAd.headlines.map(asset => asset.text);
          case 'APP_AD':
            return adGroupAd.ad.appAd.headlines.map(asset => asset.text);
          case 'CALL_AD':
            return adGroupAd.ad.callAd.headline1 + ' - ' +
                adGroupAd.ad.callAd.headline2;
          case 'LEGACY_RESPONSIVE_DISPLAY_AD':
            return adGroupAd.ad.legacyResponsiveDisplayAd.longHeadline;
          case 'LOCAL_AD':
            return adGroupAd.ad.localAd.headlines.map(asset => asset.text);
          case 'SHOPPING_COMPARISON_LISTING_AD':
            return adGroupAd.ad.shoppingComparisonListingAd.headline;
          case 'SMART_CAMPAIGN_AD':
            return adGroupAd.ad.smartCampaignAd.headlines.map(asset => asset.text);
          case 'VIDEO_AD':
            return adGroupAd.ad.videoAd.inFeed.headline;
          case 'DISCOVERY_CAROUSEL_AD':
            return adGroupAd.ad.discoveryCarouselAd.headline.text;
          case 'DISCOVERY_MULTI_ASSET_AD':
            return adGroupAd.ad.discoveryMultiAssetAd.headlines.map(asset => asset.text);
          default:
            return;
        }
      });
      outputSegmentation(
          finalUrlSheet, 'Final Url', (adGroupAd) => adGroupAd.ad.finalUrls);
      console.log(`Ad performance report available at\n${spreadsheet.getUrl()}`);
      validateEmailAddresses(RECIPIENT_EMAILS);
      MailApp.sendEmail(
          RECIPIENT_EMAILS.join(','), '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) {
      const 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.
      const rows = [];
      const header = [
        segmentName,
        'Num Ads',
        'Impressions',
        'Clicks',
        'CTR (%)',
        'Cost'
      ];
      rows.push(header);

      const segmentMap = {};

      // Compute data.
      const fields = FIELDS.join(",");
      const results = AdsApp.search(`SELECT ${fields} FROM ad_group_ad ` +
            `WHERE metrics.impressions > 0 AND ` +
            `segments.date DURING LAST_7_DAYS`);
      let skipped = 0;
      for (const row of results) {
        let rawSegments = segmentFunc(row.adGroupAd);
        // 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 (!rawSegments) {
          skipped += 1;
          continue;
        }

        let segments = [];
        if (typeof (rawSegments) == 'string') {
          segments[0] = rawSegments;
        }
        else {
          segments = rawSegments;
        }

        for (const segment of segments) {
          if (!segmentMap[segment]) {
            segmentMap[segment] =
                {numAds: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
          }
          const data = segmentMap[segment];
          data.numAds++;
          data.totalImpressions += parseFloat(row.metrics.impressions);
          data.totalClicks += parseFloat(row.metrics.clicks);
          data.totalCost += parseFloat((row.metrics.costMicros)/1000000);
        }
      }

      // Write data to our rows.
      for (const key in segmentMap) {
        if (segmentMap.hasOwnProperty(key)) {
          let ctr = 0;
          if (segmentMap[key].numAds > 0) {
            ctr = (segmentMap[key].totalClicks /
              segmentMap[key].totalImpressions) * 100;
          }
          const row = [
            key,
            segmentMap[key].numAds,
            segmentMap[key].totalImpressions,
            segmentMap[key].totalClicks,
            ctr.toFixed(2),
            segmentMap[key].totalCost];
          rows.push(row);
        }
      }

      // Write a warning if we skipped ads that were missing segmentation info
      if (skipped) {
        rows.push(['SKIPPED', skipped, '', '', '', '']);
      }
      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 addresses to make sure it's not the default.
     * Throws a descriptive error message if validation fails.
     *
     * @param {Array.<string>} recipientEmails The list of email addresses.
     * @throws {Error} If the list of email addresses is still the default
     */
    function validateEmailAddresses(recipientEmails) {
      if (recipientEmails && recipientEmails[0] == 'YOUR_EMAIL_HERE') {
        throw new Error(
            'Please either specify a valid email address or clear' +
            ' the recipient_emails field in Config.');
      }
    }

    /**
     * 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);
    }