Keyword Performance Report

“报告”图标

此脚本通过在 Google 搜索上触发的广告的质量得分和展示次数份额分布图表来总结关键字效果。

脚本每次执行时,都会生成新的关键字效果报告。这些报告会以电子表格的形式保存在 Google 云端硬盘中。您还可以配置脚本以通过电子邮件发送报告。

正在安排

脚本使用前一周的统计信息来生成报告。请将其设为在每周星期一运行。

运作方式

首先,脚本创建模板电子表格的副本,并预先配置所有图表。

然后,该脚本在 Report 工作表中填充数据值。其他工作表中的图表会自动构建。

初始设置

  • 使用下面的源代码创建新脚本。
  • 更新 RECIPIENT_EMAIL 以指定您的电子邮件偏好设置。
  • 设置在每周星期一运行。
// 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 Keyword Performance Report
 *
 * @overview The Keyword Performance Report script generates a Google
 *     Spreadsheet that contains keyword performance stats like quality score
 *     and impression share of ads, as well as several distribution charts for
 *     an advertiser account. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/keyword-performance
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.0
 *
 * @changelog
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.0.4
 *   - Fixed issue of calling getRangeByName on spreadsheet vs sheet.
 * - version 1.0.3
 *   - Refactored to improve readability. Added documentation.
 * - version 1.0.2
 *   - Added validation for spreadsheet url and email address.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * A list of recipients. Comment out the list entries to not send any emails.
 */
const RECIPIENT_EMAILS = [
  'email@example.com'
  ];

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

/**
 * The size of the quality score map to output.
 * DO NOT change this value.
 */
const QUALITY_SCORE_MAP_SIZE = 10;

/**
 * The size of the position map to output.
 * DO NOT change this value.
 */
const POSITION_MAP_SIZE = 10;

const FIELDS = ['ad_group_criterion.keyword.text',
                'ad_group_criterion.quality_info.quality_score',
                'metrics.video_views',
                'metrics.ctr',
                'metrics.conversions',
                'metrics.average_cpc',
                'metrics.clicks',
                'metrics.cost_micros',
                'metrics.search_absolute_top_impression_share',
                'metrics.search_top_impression_share',
                'metrics.average_cpm',
                'metrics.impressions'];

/**
 * This script computes a keyword performance report
 * and outputs it to a Google spreadsheet. The spreadsheet
 * url is logged and emailed.
 */
function main() {
  validateEmail(RECIPIENT_EMAILS);
  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.`);
  spreadsheet.getRangeByName('date_label').setValue('Date');
  spreadsheet.getRangeByName('date_value').setValue(new Date());
  spreadsheet.getRangeByName('account_id')
      .setValue(AdsApp.currentAccount().getCustomerId());
  outputQualityScoreData(spreadsheet);
  outputImpressionShareData(spreadsheet);
  console.log(
      `Keyword performance report available at\n${spreadsheet.getUrl()}`);
  if (RECIPIENT_EMAILS.length) {
    MailApp.sendEmail(
        RECIPIENT_EMAILS.join(','), 'Keyword 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(
                            'Keyword 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;
}

/**
 * Gets an iterator for keywords that had impressions last week.
 * @return {Iterator} an iterator of the keywords
 */
function getLastWeekKeywordsWithPositiveImpressions() {
  const fields = FIELDS.join(',');
  return AdsApp.search(`SELECT ${fields} FROM keyword_view ` +
                       `WHERE metrics.impressions > 0 AND ` +
                       `segments.date DURING LAST_7_DAYS`);
}

/**
 * Outputs Quality score related data to the spreadsheet
 *
 * @param {Spreadsheet} spreadsheet The sheet to output to.
 */
function outputQualityScoreData(spreadsheet) {
  // Output header row
  const header = [
    'Quality Score', 'Num Keywords', 'Impressions', 'Clicks', 'CTR (%)', 'Cost'
  ];
  spreadsheet.getRangeByName('quality_score_headings').setValues([header]);

  // Initialize
  const qualityScoreMap = getEmptyStatMap(QUALITY_SCORE_MAP_SIZE);

  // Compute data
  computeQualityData(
      getLastWeekKeywordsWithPositiveImpressions(), qualityScoreMap);

  // Output data to spreadsheet
  const rows = [];
  for (const key in qualityScoreMap) {
    const ctr = calculateCtr(qualityScoreMap[key]);
    const row = [
      key, qualityScoreMap[key].numKeywords,
      qualityScoreMap[key].totalImpressions, qualityScoreMap[key].totalClicks,
      ctr.toFixed(2), qualityScoreMap[key].totalCost
    ];
    rows.push(row);
  }
  spreadsheet.getRangeByName('quality_score_body').setValues(rows);
}

/**
 * Outputs impression share related data to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet to output to.
 */
function outputImpressionShareData(spreadsheet) {
  // Output header row
  headerRow = [];
  const header = [
    'Impression Share', 'Num Keywords', 'Impressions', 'Clicks', 'CTR (%)',
    'Cost'];
  headerRow.push(header);
  spreadsheet.getRangeByName('position_headings').setValues(headerRow);

  // Initialize
  const positionMap = getEmptyStatMap(POSITION_MAP_SIZE);

  // Compute data
  computeImpressionShareData(
      getLastWeekKeywordsWithPositiveImpressions(), positionMap);

  // Output data to spreadsheet
  const rows = [];
  for (let key in positionMap) {
    const ctr = calculateCtr(positionMap[key]);
    const row = [
      // Divide impression share into slabs of 10%
      '(' + (key-1) * 10 + '-' + key * 10 + ')%',
      positionMap[key].numKeywords, positionMap[key].totalImpressions,
      positionMap[key].totalClicks, ctr.toFixed(2), positionMap[key].totalCost
    ];
    rows.push(row);
  }
  spreadsheet.getRangeByName('position_body').setValues(rows);
}

/**
 * Calculates the click through rate given an entry from a map.
 *
 * @param {object} mapEntry - an entry from the  map
 * @return {number} the click through rate
 */
function calculateCtr(mapEntry) {
  let ctr = 0;
  if (mapEntry.numKeywords > 0) {
    ctr = (mapEntry.totalClicks / mapEntry.totalImpressions) * 100;
  }
  return ctr;
}

/**
 * Gets an empty stat map.
 *
 * @param {number} size - the number of entries in the stat map.
 * @return {array} the empty quality stat map.
 */
function getEmptyStatMap(size) {
  const qualityScoreMap = [];
  for (i = 1; i <= size; i++) {
    qualityScoreMap[i] =
        {numKeywords: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
  }
  return qualityScoreMap;
}

/**
 * Uses the given keyword iterator and populates the given quality score map.
 *
 * @param {Iterator} keywordIterator - the keywords to use for getting scores.
 * @param {array} qualityScoreMap - the score map to fill with keyword data.
 */
function computeQualityData(keywordIterator, qualityScoreMap) {
  for (const keyword of keywordIterator) {
    if (keyword.adGroupCriterion.qualityInfo) {
      const data =
            qualityScoreMap[keyword.adGroupCriterion.qualityInfo.qualityScore];
      if (data) {
        data.numKeywords++;
        data.totalImpressions += parseFloat(keyword.metrics.impressions);
        data.totalClicks += parseFloat(keyword.metrics.clicks);
        data.totalCost += parseFloat(keyword.metrics.costMicros)/1000000;
      }
    }
  }
}

/**
 * Uses the given keyword iterator and populates the given position map.
 *
 * @param {Iterator} keywordIterator - the keywords to use for getting scores.
 * @param {array} positionMap - the map to fill with keyword data.
 */
function computeImpressionShareData(keywordIterator, positionMap) {
  for (const keyword of keywordIterator) {
    let index =
        Math.ceil(keyword.metrics.searchAbsoluteTopImpressionShare * 10);
    const data = positionMap[index];
    if (data) {
      data.numKeywords++;
      data.totalImpressions += parseFloat(keyword.metrics.impressions);
      data.totalClicks += parseFloat(keyword.metrics.clicks);
      data.totalCost += parseFloat(keyword.metrics.costMicros)/1000000;
    }
  }
}

/**
 * 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 and throws a descriptive error if the user
 * has not changed the email from the default fake address.
 *
 * @param {string} emails The email address.
 * @throws {Error} If the email is the default fake address.
 */
function validateEmail(emails) {
  for (const email of emails) {
    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);
}