Отчет по эффективности ключевых слов – один аккаунт

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

С помощью отчета по эффективности ключевых слов рекламодатели AdWords могут анализировать такие дополнительные характеристики, как показатель качества и средняя позиция объявлений в результатах поиска Google. Полученные данные представляются в виде таблицы Google с несколькими диаграммами распределения.

При каждом запуске скрипта создается новый отчет, который сохраняется на Google Диске. Кроме того, вы можете настроить отправку отчета по электронной почте определенным получателям.

Планирование

Поскольку для создания отчета скрипт использует данные за предыдущую неделю, планируйте его выполнение еженедельно по понедельникам.

Принцип работы

Скрипт создает копию шаблона таблицы со всеми стандартными диаграммами. Далее на лист Отчет вводятся значения данных, на основе которых автоматически строятся соответствующие диаграммы.

Настройка

  • Создайте новый скрипт с приведенным ниже кодом.
  • Настройте адрес электронной почты с помощью переменной 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 average position of ads, as well as several distribution charts for
 *     an advertiser account. See
 *     https://developers.google.com/adwords/scripts/docs/solutions/keyword-performance
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.0.4
 *
 * @changelog
 * - 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.
 */

/**
 * Comma-separated list of recipients. Comment out to not send any emails.
 */
var RECIPIENT_EMAIL = 'email@example.com';

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

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

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

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

  spreadsheet.getRangeByName('date_label').setValue('Date');
  spreadsheet.getRangeByName('date_value').setValue(new Date());
  spreadsheet.getRangeByName('account_id')
      .setValue(AdWordsApp.currentAccount().getCustomerId());
  outputQualityScoreData(spreadsheet);
  outputPositionData(spreadsheet);
  Logger.log(
      'Keyword performance report available at\n' + spreadsheet.getUrl());
  if (RECIPIENT_EMAIL) {
    MailApp.sendEmail(
        RECIPIENT_EMAIL, '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) {
  var 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(AdWordsApp.currentAccount().getTimeZone());
  return spreadsheet;
}

/**
 * Gets an iterator for keywords that had impressions last week.
 * @return {Iterator} an iterator of the keywords
 */
function getLastWeekKeywordsWithPositiveImpressions() {
  return AdWordsApp.keywords()
      .forDateRange('LAST_WEEK')
      .withCondition('Impressions > 0')
      .get();
}

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

  // Initialize
  var qualityScoreMap = getEmptyStatMap(QUALITY_SCORE_MAP_SIZE);

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

  // Output data to spreadsheet
  var rows = [];
  for (var key in qualityScoreMap) {
    var ctr = calculateCtr(qualityScoreMap[key]);
    var 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 average position related data to the spreadsheet.
 * @param {Spreadsheet} spreadsheet The spreadsheet to output to.
 */
function outputPositionData(spreadsheet) {
  // Output header row
  headerRow = [];
  var header = [
    'Avg Position', 'Num Keywords', 'Impressions', 'Clicks', 'CTR (%)', 'Cost'
  ];
  headerRow.push(header);
  spreadsheet.getRangeByName('position_headings').setValues(headerRow);

  // Initialize
  var positionMap = getEmptyStatMap(POSITION_MAP_SIZE);

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

  // Output data to spreadsheet
  var rows = [];
  for (var key in positionMap) {
    var ctr = calculateCtr(positionMap[key]);
    var mapSizeLessOne = POSITION_MAP_SIZE - 1;
    var row = [
      key <= mapSizeLessOne ? key - 1 + ' to ' + key : '>' + mapSizeLessOne,
      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) {
  var 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) {
  var 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) {
  while (keywordIterator.hasNext()) {
    var keyword = keywordIterator.next();
    var stats = keyword.getStatsFor('LAST_WEEK');
    var data = qualityScoreMap[keyword.getQualityScore()];
    if (data) {
      data.numKeywords++;
      data.totalImpressions += stats.getImpressions();
      data.totalClicks += stats.getClicks();
      data.totalCost += stats.getCost();
    }
  }
}

/**
 * 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 computePositionData(keywordIterator, positionMap) {
  while (keywordIterator.hasNext()) {
    var keyword = keywordIterator.next();
    var stats = keyword.getStatsFor('LAST_WEEK');
    var index =
        Math.min(Math.ceil(stats.getAveragePosition()), POSITION_MAP_SIZE);
    var data = positionMap[index];
    data.numKeywords++;
    data.totalImpressions += stats.getImpressions();
    data.totalClicks += stats.getClicks();
    data.totalCost += stats.getCost();
  }
}

/**
 * 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 || AdWordsApp.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} email The email address.
 * @throws {Error} If the email is the default fake address.
 */
function validateEmail(email) {
  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);
}

Looking for the Manager Account (MCC) version? Click here

Оставить отзыв о...

Текущей странице
Скрипты AdWords
Скрипты AdWords