Register for one of our upcoming Google Ads Scripts workshops.

Relatório de grupos de anúncios rebaixados: 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.

Os scripts do Google AdWords podem acessar facilmente estatísticas em vários períodos e, assim, comparar o desempenho das campanhas ao longo do tempo.

O Relatório de grupos de anúncios rebaixados busca grupos de anúncios cujo desempenho consideramos estar em declínio:

  • O grupo de anúncios está ATIVO e pertence a uma campanha ATIVA. Isso significa que ele está sendo veiculado.
  • A taxa de cliques dos grupos de anúncios está diminuindo há três semanas consecutivas.

Obviamente, uma medida mais sofisticada de "piora" pode ser desenvolvida.

A saída dos dados do script acontece em uma planilha semelhante a e:

Agendamento

O script usa três semanas válidas de estatísticas para gerar o relatório. Faria sentido agendá-lo semanalmente.

Configuração

  • Configure um script com base em planilha usando o código-fonte abaixo. Use a planilha modelo do Relatório de grupos de anúncios rebaixados.
  • Não se esqueça de atualizar SPREADSHEET_URL no código.
  • Agende o script para ser executado Semanalmente.

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 Declining AdGroups
 *
 * @overview The Declining AdGroups script fetches ad groups in an advertiser
 *     account, whose performance is considered to be worsening. By default, ad
 *     groups whose Click Through Rate has been decreasing for three consecutive
 *     weeks is considered worsening. A more sophisticated measure of
 *     "worsening" may be developed if required. See
 *     https://developers.google.com/adwords/scripts/docs/solutions/declining-adgroups
 *     for more details.
 *
 * @author AdWords Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 1.0.2
 *
 * @changelog
 * - version 1.0.2
 *   - Added validation for spreadsheet URL.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

function main() {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  var spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());

  var sheet = spreadsheet.getSheets()[0];
  spreadsheet.getRangeByName('account_id').setValue(
      AdWordsApp.currentAccount().getCustomerId());
  sheet.getRange(1, 2, 1, 1).setValue('Date');
  sheet.getRange(1, 3, 1, 1).setValue(new Date());
  sheet.getRange(7, 1, sheet.getMaxRows() - 7, sheet.getMaxColumns()).clear();

  var adGroupsIterator = AdWordsApp.adGroups()
      .withCondition("Status = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .forDateRange('LAST_7_DAYS')
      .orderBy('Ctr ASC')
      .withLimit(100)
      .get();

  var today = getDateStringInPast(0);
  var oneWeekAgo = getDateStringInPast(7);
  var twoWeeksAgo = getDateStringInPast(14);
  var threeWeeksAgo = getDateStringInPast(21);

  var reportRows = [];

  while (adGroupsIterator.hasNext()) {
    var adGroup = adGroupsIterator.next();
    // Let's look at the trend of the ad group's CTR.
    var statsThreeWeeksAgo = adGroup.getStatsFor(threeWeeksAgo, twoWeeksAgo);
    var statsTwoWeeksAgo = adGroup.getStatsFor(twoWeeksAgo, oneWeekAgo);
    var statsLastWeek = adGroup.getStatsFor(oneWeekAgo, today);

    // Week over week, the ad group is declining - record that!
    if (statsLastWeek.getCtr() < statsTwoWeeksAgo.getCtr() &&
        statsTwoWeeksAgo.getCtr() < statsThreeWeeksAgo.getCtr()) {
      reportRows.push([adGroup.getCampaign().getName(), adGroup.getName(),
          statsLastWeek.getCtr(), statsLastWeek.getCost(),
          statsTwoWeeksAgo.getCtr(), statsTwoWeeksAgo.getCost(),
          statsThreeWeeksAgo.getCtr(), statsThreeWeeksAgo.getCost()]);
    }
  }
  if (reportRows.length > 0) {
    sheet.getRange(7, 2, reportRows.length, 8).setValues(reportRows);
    sheet.getRange(7, 4, reportRows.length, 1).setNumberFormat('#0.00%');
    sheet.getRange(7, 6, reportRows.length, 1).setNumberFormat('#0.00%');
    sheet.getRange(7, 8, reportRows.length, 1).setNumberFormat('#0.00%');

    sheet.getRange(7, 5, reportRows.length, 1).setNumberFormat('#,##0.00');
    sheet.getRange(7, 7, reportRows.length, 1).setNumberFormat('#,##0.00');
    sheet.getRange(7, 9, reportRows.length, 1).setNumberFormat('#,##0.00');
  }

  var email = spreadsheet.getRangeByName('email').getValue();
  if (email) {
    var body = [];
    body.push('The Ctr of the following ad groups is declining over the ' +
        'last three weeks.\n');
    body.push('Full report at ' + SPREADSHEET_URL + '\n\n');
    for (var i = 0; i < reportRows.length; i++) {
      body.push(reportRows[i][0] + ' > ' + reportRows[i][1]);
      body.push('  ' + ctr(reportRows[i][6]) + ' > ' + ctr(reportRows[i][4]) +
          ' > ' + ctr(reportRows[i][2]) + '\n');
    }
    MailApp.sendEmail(email, '' +
        reportRows.length + ' ad groups are declining in AdWords account ' +
        AdWordsApp.currentAccount().getCustomerId(), body.join('\n'));
  }
}

function ctr(number) {
  return parseInt(number * 10000) / 10000 + '%';
}

// Returns YYYYMMDD-formatted date.
function getDateStringInPast(numDays, date) {
  date = date || new Date();
  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  var past = new Date(date.getTime() - numDays * MILLIS_PER_DAY);
  return getDateStringInTimeZone('yyyyMMdd', past);
}

function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdWordsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

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