Relatório de desempenho por palavra-chave: conta única

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

O Relatório de desempenho por palavra-chave é um exemplo de funcionalidade de relatório avançada fornecida por scripts do Google AdWords. Os anunciantes gostam de conhecer o desempenho das palavras-chave nas campanhas. Duas métricas importantes para o desempenho de palavras-chave são o Índice de qualidade e a posição média dos anúncios acionados com base na Pesquisa do Google. O Relatório de desempenho por palavra-chave gera uma Planilha do Google com vários gráficos de distribuição interessantes.

Um novo Relatório de desempenho por palavra-chave é criado sempre que o script é executado. Você pode acessar todos esses relatórios no Google Drive. Opcionalmente, o script também pode enviar o relatório por e-mail para um ou mais destinatários.

Agendamento

O script usa as estatísticas da semana anterior para gerar o relatório. Agende-o para ser executado Semanalmente, às segundas-feiras.

Como funciona

O script começa criando uma cópia de uma planilha modelo, com todos os gráficos pré-configurados. Em seguida, o script preenche os valores de dados na página Relatório, e os gráficos nas outras páginas são gerados automaticamente.

Configuração

  • Crie um novo script com o código-fonte abaixo.
  • Não se esqueça de atualizar RECIPIENT_EMAIL para especificar sua preferência de e-mail.
  • Agende-o para ser executado Semanalmente, às segundas-feiras.

Código-fonte

// 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

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.