広告の掲載結果レポート

レポート アイコン

広告の掲載結果レポートは、Google 広告 スクリプトで利用できる高度なレポート機能の一例です。広告主様にとって、キャンペーンでの広告の掲載結果を分析することは重要です。広告見出しや最終ページ URL の成果を他のユーザーと比較すると、新しい広告の作成に役立つ情報が得られます。広告の掲載結果レポートでは、さまざまな分布グラフを含む Google スプレッドシートが生成されます。

新しい広告の掲載結果レポートは、スクリプトの実行を通じていつでも作成できます。これらのレポートはすべて、Google ドライブからアクセスできます。必要に応じて、スクリプトから 1 人以上の宛先にレポートをメールで送信することもできます。

広告の掲載結果レポート

スケジュール設定中

スクリプトは、先週分の掲載結果に基づいてレポートを生成します。毎週月曜日に実行されるようスケジュールを設定します。

仕組み

このスクリプトは、すべてのグラフが事前構成されたテンプレート スプレッドシートのコピーを作成することから始めます。その後、このスクリプトによって [Report] シートにデータ値が入力され、他のシートのグラフは自動的に作成されます。

設定

ソースコード

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