Relatório de desempenho por palavra-chave: conta de administrador

This is a Manager Account script. For operating on a single account, use the Single Account version of the script.

O Relatório de desempenho por palavra-chave da MCC estende o funcionamento do Relatório de desempenho por palavra-chave para várias contas. O Relatório de desempenho por palavra-chave da MCC gera um conjunto de Planilhas do Google com uma série de gráficos de distribuição interessantes relacionados ao Índice de qualidade e à posição média dos anúncios acionados na Pesquisa Google.

Um novo conjunto de Relatórios 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 pasta com a data do dia em uma pasta raiz definida pelo usuário no Google Drive. Em seguida, ele cria uma cópia de uma planilha modelo, com todos os gráficos pré-configurados para cada conta incluída. O script preenche os valores de dados na planilha do relatório e gera os gráficos nas outras planilhas automaticamente.

Você pode definir a lista de contas a serem processadas, a pasta raiz em que o script cria os relatórios e a lista de endereços de e-mail para envio de uma notificação depois da execução do script.

Configuração

  • Crie um novo script com o código-fonte abaixo.
  • Atualize as ACCOUNTS caso seja necessário gerar o relatório somente para um subconjunto de contas na sua conta da MCC.
  • Atualize ROOT_FOLDER_NAME com o nome da pasta do Google Drive em que os relatórios devem ser salvos. Verifique também se essa pasta existe no Google Drive e se o script tem permissão de gravação nela.
  • 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 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 MCC Keyword Performance Report
 *
 * @overview The MCC 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.
 *     An indvidual report is generated for each Advertiser account under the
 *     MCC account. See
 * https://developers.google.com/adwords/scripts/docs/solutions/mccapp-keyword-performance
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.1.4
 *
 * @changelog
 *  - version 1.1.4
 *   - Fixed issue of calling getRangeByName on spreadsheet vs sheet.
 * - version 1.1.3
 *   - Refactored to improve readability. Added documentation.
 * - version 1.1.2
 *   - Added validation of user settings.
 * - version 1.1.1
 *   - Improvements to time zone handling.
 * - version 1.1
 *   - Make the file cloning process more robust.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * Name of the Google Drive folder under which the generated reports are stored.
 */
var ROOT_FOLDER_NAME = 'ACCOUNT_PERFORMANCE';

/**
 * List of accounts for which keyword performance report is run. To run for all
 * accounts under the MCC, leave the array empty. E.g.
 *
 * var ACCOUNTS = ['925-591-3280', '918-501-8835'];
 */
var ACCOUNTS = [];

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

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

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

function main() {
  validateEmail(RECIPIENT_EMAIL);
  Logger.log('Opening Google Drive folder %s.', ROOT_FOLDER_NAME);
  var rootFolder = DriveApp.getFoldersByName(ROOT_FOLDER_NAME).next();
  var formattedDate = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');

  var todayFolder = rootFolder.createFolder(formattedDate);
  Logger.log(
      'Created a new folder named \'%s\' under root folder \'%s\'. ' +
          'All reports will be generated under this folder.',
      todayFolder.getName(), rootFolder.getName());

  var accountSelector = MccApp.accounts();
  if (ACCOUNTS.length > 0) {
    accountSelector = accountSelector.withIds(ACCOUNTS);
  }

  var accountIterator = accountSelector.get();

  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    processAccount(account, todayFolder);
    Logger.log('Done processing %s', account.getCustomerId());
  }
  if (RECIPIENT_EMAIL) {
    MailApp.sendEmail(
        RECIPIENT_EMAIL, 'Keyword Performance Reports are ready',
        todayFolder.getUrl());
  }
}

/**
 * Process an account.
 *
 * @param {Object} account The acount to be processed.
 * @param {string} rootFolder The root folder under which reports are saved.
 */
function processAccount(account, rootFolder) {
  MccApp.select(account);

  var spreadsheet =
      copySpreadsheet(SPREADSHEET_URL, rootFolder, account.getCustomerId());
  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());
}

/**
 * Makes a copy of the template spreadsheet for a customer.
 *
 * @param {string} spreadsheetUrl The URL of the spreadsheet.
 * @param {string} rootFolder The root folder under which reports are saved.
 * @param {string} customerId The customer id.
 * @return {SpreadSheet} The spreadsheet.
 */
function copySpreadsheet(spreadsheetUrl, rootFolder, customerId) {
  var fileName = 'Keyword Performance Report - ' + customerId;

  var copy = validateAndGetSpreadsheet(spreadsheetUrl).copy(fileName);

  // Make sure the spreadsheet is using the account's timezone.
  copy.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
  var newFile = DriveApp.getFileById(copy.getId());

  var oldParent = newFile.getParents().next();
  oldParent.removeFile(newFile);
  rootFolder.addFile(newFile);

  Logger.log(
      'Generated spreadsheet - %s for customer ID: %s.', newFile.getUrl(),
      customerId);
  return SpreadsheetApp.openByUrl(newFile.getUrl());
}

/**
 * Outputs Quality score related data to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The spreadsheet 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);
}

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

/**
 * Gets an empty stat map.
 * @param {number} size - the number of entries in the stat map.
 * @return {array} the empty 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;
}

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

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

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

Enviar comentários sobre…

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