키워드 실적 보고서

보고서 아이콘

이 스크립트는 Google 검색에서 트리거된 광고의 품질평가점수 및 노출 점유율 분포도를 통해 키워드 실적을 요약합니다.

스크립트를 실행할 때마다 새 키워드 실적 보고서가 생성됩니다. 이 보고서는 Google Drive에 스프레드시트로 저장됩니다. 보고서를 이메일로 보내도록 스크립트를 구성할 수도 있습니다.

예약

스크립트는 지난 주의 통계를 사용하여 보고서를 생성합니다. 매주, 월요일로 예약합니다.

사용 방법

스크립트는 모든 그래프가 미리 구성된 템플릿 스프레드시트의 사본을 만들기 시작합니다.

그러면 스크립트가 보고서 시트에 데이터 값을 채웁니다. 다른 시트의 그래프는 자동으로 구성됩니다.

설정

  • 아래의 소스 코드로 새로운 스크립트를 만듭니다.
  • 이메일 환경설정을 지정하려면 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);
}