Register for one of our upcoming Google Ads Scripts workshops.

Relatório de grupos de anúncios rebaixados: 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 grupos de anúncios rebaixados das contas de administrador estende o funcionamento do script de Relatório de grupos de anúncios rebaixados de uma única conta a várias contas em uma conta de administrador.

A definição de um grupo de anúncios rebaixados é a mesma na versão para uma única conta:

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

Naturalmente, é possível usar critérios mais sofisticados para selecionar grupos de anúncios rebaixados.

O relatório gera uma planilha com várias guias. Para cada conta, é criada uma guia diferente que mostra o relatório de grupos de anúncios rebaixados correspondentes.

Há também uma guia de resumo que fornece alguns dados agregados de todas as contas. Ela mostra o número de grupos de anúncios rebaixados, a queda média na taxa de cliques, a alteração média no custo e a alteração total nos custos de cada conta.

Se desejar, um endereço de e-mail pode ser inserido na guia de resumo do relatório, e um e-mail será enviado sempre que esse relatório for gerado, resumindo dados das contas que tiverem as maiores quantidades de grupos de anúncios rebaixados.

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 da MCC.
  • Atualize CONFIG.SPREADSHEET_URL no código de forma a refletir sua planilha.
  • Atualize CONFIG.TIMEZONE no código de forma a refletir seu fuso horário.
  • [Opcional] Atualize CONFIG.SUMMARY_TAB_NAME no código se você optar por mudar o nome da guia "Resumo".
  • Agende o script para ser executado Semanalmente.

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 MCC Declining AdGroups
 *
 * @overview The MCC Declining AdGroups script fetches ad groups in advertiser
 *     accounts under an MCC 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/mccapp-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 CONFIG = {
  SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL',
  TIMEZONE: 'EST', // Timezone code, such as 'PST', 'EST', 'UTC'
  ACCOUNT_LABEL: 'High Spend Accounts', // For selecting accounts to process.
                                        // Leave blank if choosing not to filter
  SUMMARY_TAB_NAME: 'Report Summary',
  FIRST_DATA_ROW: 7,
  FIRST_DATA_COLUMN: 2
};

function main() {
  debug('Begin processing of MCC declining Ad Groups');
  var account;

  // Iterate over accounts.
  while (account = mccManager.next()) {
    debug('Switching to account:' + account.getCustomerId());

    // For each account, compute the declining ad groups.
    var rows = getDegradingAccountsFor(account);
    debug('' + rows.length + ' declining accounts found');

    // Insert rows into the sheet.
    spreadsheetManager.insertRowsForTab(account.getCustomerId(), rows);

    // Send the data to the MccManager to use for summary tab.
    mccManager.save(rows);
  }

  debug('All accounts computed. Generating summary ...');
  // Numbers for the summary.
  var sumTabRows = mccManager.summarize();

  // Push summary numbers into summary tab.
  spreadsheetManager.insertSummaryRows(sumTabRows);
  debug('Summary complete.');

  // Send email.
  spreadsheetManager.sendEmail(sumTabRows);

  debug('MCC declining Ad Groups complete');
}

/**
 * Pulls out the declining ad groups for the account specified, and formats the
 * information into rows for adding to the spreadsheet later.
 *
 * @param {AdWordsApp.Account} account The account whose information is desired
 * @return {Array.<Array>} A 2D matrix of data that has been extracted to fit
 * the format of the Spreadsheet it will be inserted into.
 */
function getDegradingAccountsFor(account) {
    var reportRows = [];

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

    // Find the declining ad groups using criteria specified,
    // then return some useful information about them.
    while (adGroupsIter.hasNext()) {
      var adGroup = adGroupsIter.next();
      var statsThreeWeeksAgo = adGroup.getStatsFor(
                                Util.threeWeeksAgo, Util.twoWeeksAgo);
      var statsTwoWeeksAgo = adGroup.getStatsFor(
                                Util.twoWeeksAgo, Util.oneWeekAgo);
      var statsLastWeek = adGroup.getStatsFor(
                                Util.oneWeekAgo, Util.today);

      // Week over week, the ad group is declining.
      // You may change this criteria to suit your needs.
      // Set this condition to always true to show all adgroups.
      if (statsLastWeek.getCtr() < statsTwoWeeksAgo.getCtr() &&
        statsTwoWeeksAgo.getCtr() < statsThreeWeeksAgo.getCtr()) {
        // Gather data to return. Note that the data matches the fields in
        // the spreadsheet.
        reportRows.push([adGroup.getCampaign().getName(), adGroup.getName(),
            statsLastWeek.getCtr(), statsLastWeek.getCost(),
            statsTwoWeeksAgo.getCtr(), statsTwoWeeksAgo.getCost(),
            statsThreeWeeksAgo.getCtr(), statsThreeWeeksAgo.getCost()]);
      }

    }

    return reportRows;
}

/**
 * Module that deals with fetching and iterating through multiple accounts.
 * Also keeps track of info across accounts for summary at the end.
 *
 * @return {object} callable functions corresponding to the available
 * actions. Specifically, it currently supports next, current, mccAccount,
 * save, and summarize.
 */
var mccManager = (function() {
  var accountIterator;
  var mccAccount;
  var currentAccount;
  var summary = [];

  // Private one-time init function.
  var init = function() {
    var accountSelector = MccApp.accounts();

    // Use this to limit the accounts that are being selected in the report.
    if (CONFIG.ACCOUNT_LABEL) {
        accountSelector.withCondition("LabelNames CONTAINS '" +
            CONFIG.ACCOUNT_LABEL + "'");
    }

    accountSelector.withLimit(50);
    accountIterator = accountSelector.get();

    mccAccount = AdWordsApp.currentAccount(); // save the mccAccount
    currentAccount = AdWordsApp.currentAccount();
  };

 /**
  * After calling this, AdWordsApp will have the next account selected.
  * If there are no more accounts to process, re-selects the original
  * MCC account.
  *
  * @return {AdWordsApp.Account} The account that has been selected.
  */
  var getNextAccount = function() {
    // Make sure we have an iterator to call against.
    if (accountIterator.hasNext()) {
      currentAccount = accountIterator.next();
      MccApp.select(currentAccount);
      return currentAccount;
    }
    else {
      MccApp.select(mccAccount);
      return null;
    }

  };

 /**
  * Returns the currently selected account. This is cached for performance.
  *
  * @return {AdWords.Account} The currently selected account
  */
  var getCurrentAccount = function() {
    return currentAccount;
  };

 /**
  * Returns the original MCC account.
  *
  * @return {AdWords.Account} The original account that was selected.
  */
  var getMccAccount = function() {
    return mccAccount;
  };

 /**
  * Computes and saves the info for the account's adgroups,
  * in preparation for the summary. The data is unsorted at this stage.
  *
  * @param {Array.<Array>} rows The data to be added to the spreadsheet
  */
  var saveAdgroupInfo = function(rows) {
    if (rows.length == 0) return;

    /**
     * Computes the sum of the difference between 2 columns, for all rows.
     *
     * @param {Array.<Array>} rows
     * @param {number} colA The 0-based index of the first column
     * @param {number} colB The 0-based index of the second column
     * @return {number} The sum of the differences across all the rows computed
     */
    var computeTotalChange = function(rows, colA, colB) {
      var sum = 0;
      rows.forEach(function(r) {
        sum += r[colA] - r[colB];
      });
      return sum;
    };

    summaryRow = [
      getCurrentAccount().getCustomerId(),
      rows.length,  // The number of decreasing adgroups.
      computeTotalChange(rows, 2, 6) / (rows.length),
        // avg(CTR last week  - CTR last week 3 weeks ago)
      computeTotalChange(rows, 3, 7) / (rows.length),
        // avg(Cost last week - Cost 3 weeks ago)
      computeTotalChange(rows, 3, 7)
        // Total cost change per account.
    ];
    summary.push(summaryRow);
  };

 /**
  * Sorts and returns the summary rows for use on the summary tab.
  *
  * @return {Array.<Array>} The sorted order of the summary rows, by the column
  * at index 1, where the number of declining accounts per account is recorded
  */
  var getSummaryRows = function() {
    // Sort the rows.
    summary.sort(function(a, b) {
      return b[1] - a[1]; // Sort descending order is desired.
    });
    return summary;
  };

  // Set up internal variables; called only once, here.
  init();

  // Expose the external interface.
  return {
    next: getNextAccount,
    current: getCurrentAccount,
    mccAccount: getMccAccount,
    save: saveAdgroupInfo,
    summarize: getSummaryRows
  };

})();


/**
 * Module for the creation and selection of tabs in the spreadsheet,
 * as well as the formatted insertion of data, and the sending of email.
 *
 * @return {object} callable functions of the module, including
 * insertRowsForTab, insertSummaryRows, setValueForCell, sendEmail
 */
var spreadsheetManager = (function() {
  var spreadsheetTabs = []; // List of sheet names.
  var spreadsheet = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());
  Logger.log('Using spreadsheet - %s.', CONFIG.SPREADSHEET_URL);
  var currentTab;

  var templateSpreadsheet = spreadsheet.getSheetByName('Report Template');
  var sheetOptions = { template: templateSpreadsheet};

  /**
   * Does basic set up for a given tab, to prepare for data insertion.
   *
   * @param {Sheet} the tab to setup
   */
  var setupTab = function(tab) {
    currentTab = tab;
    // Set the date, clear the rows of any data.
    currentTab.getRange(1, 2, 1, 1).setValue('Date');
    currentTab.getRange(1, 3, 1, 1).setValue(new Date());
    currentTab.getRange(7, 1, currentTab.getMaxRows() - 7,
      currentTab.getMaxColumns()).clear();

    // Set account_id cell.
    var cellName = currentTab.getSheetName() + '!account_id';
    setValueForCell(cellName, mccManager.current().getCustomerId());

    return tab;
  };

  /**
   * Sets the value for a given cellname with the value provided.
   *
   * @param {string} cellName The name of the cell to set
   * @param {string} value The value to place into the cell
   */
  var setValueForCell = function(cellName, value) {
    var cell = spreadsheet.getRangeByName(cellName);
    if (cell) {
      cell.setValue(value);
    }
    else {
      debug('WARNING: cell not found: ' + cellName);
    }
  };

  /**
   * Applies format, starting at row CONFIG.FIRST_DATA_ROW,
   * for the next rowCount rows, in the columns marked by columnArray.
   *
   * @param {number} rowCount number of rows to format
   * @param {Array.<number} columnArray Array of columns to apply formatting
   * @param {string} format Format to apply to those cells
   */
  var setRowFormating = function(rowCount, columnArray, format) {
    columnArray.forEach(function(col) {
      currentTab.
        getRange(CONFIG.FIRST_DATA_ROW, col, rowCount, 1).
        setNumberFormat(format);
    });
  };


  /**
   * Always returns the tab requested by name.
   * If it does not exist, it will create one.
   *
   * @param {string} tabName : name of tab
   * @return {Sheet} currentTab: the tab requested.
   */
  var getTabByName = function(tabName) {
    if (currentTab && currentTab.getName() == tabName) {
      return currentTab;
    }

    // Return a reference to the tab.
    currentTab = spreadsheet.getSheetByName(tabName);

    // If it doesn't exist, create it.
    if (currentTab == null) {
      currentTab = spreadsheet.insertSheet(tabName, sheetOptions);
    }

    return currentTab;
  };

  /**
   * Inserts the rows of data into the appropriate tab, and formats them.
   *
   * @param {string} tabName : name of the tab to add rows
   * @param {Array.<Array>} rows : array of arrays for spreadsheet
   */
  var insertRowsForTab = function(tabName, rows) {

    // Get the tab and clean it up. Do this regardless of data size.
    setupTab(getTabByName(tabName));

    if (rows.length == 0)
      return;

    // Add data, and formatting.
    setRowFormating(rows.length, [4, 6, 8], '#0.00%');
    setRowFormating(rows.length, [5, 7, 9], '#,##0.00');
    currentTab.getRange(CONFIG.FIRST_DATA_ROW, CONFIG.FIRST_DATA_COLUMN,
      rows.length, rows[0].length).setValues(rows);

    return;
  };

  /**
   * Inserts the summary data into the summary tab, and formats them.
   *
   * @param {Array.<Array>} rows : array of arrays for spreadsheet
   */
  var insertSummaryRows = function(rows) {
    getTabByName(CONFIG.SUMMARY_TAB_NAME);
    // Set the date, clear the rows of any data.
    currentTab.getRange(1, 2, 1, 1).setValue('Summary Date');
    currentTab.getRange(1, 3, 1, 1).setValue(new Date());
    currentTab.getRange(7, 1, currentTab.getMaxRows() - 7,
      currentTab.getMaxColumns()).clear();

    // Put the mcc account # in the summary tab.
    var cellName = currentTab.getSheetName() + '!account_id';
    setValueForCell(cellName, mccManager.mccAccount().getCustomerId());

    if (rows.length == 0)
      return;

    // Load the data in, and format it.
    setRowFormating(rows.length, [4], '#0.00%');
    setRowFormating(rows.length, [5, 6], '#,##0.00');
    currentTab.getRange(CONFIG.FIRST_DATA_ROW, CONFIG.FIRST_DATA_COLUMN,
      rows.length, rows[0].length).setValues(rows);

    return;

  };

  /**
   * Sends email if an email was provided on the summary tab.
   * Otherwise does nothing.
   *
   * @param {Array.<Array>} all the rows to be sent in the email
   *
   */
  var sendEmail = function(reportRows) {
    getTabByName(CONFIG.SUMMARY_TAB_NAME);

    var rangeName = currentTab.getSheetName() + '!email';
    var email = spreadsheet.getRangeByName(rangeName).getValue();
    if (!email || email == 'foo@example.com') {
      debug('no email sent');
      return; // No address, do nothing.
    }

    debug('sending email to ' + email);
    var body = [];
    body.push('The Ctr of some of the adgroups in the following accounts is' +
      ' declining over the last three weeks.\nThe number of declining ' +
      'adgroups is shown below.\n');
    body.push('Full report at ' + CONFIG.SPREADSHEET_URL + '\n\n');
    body.push('Account Id => Number of declining ad groups');
    for (var i = 0; i < reportRows.length; i++) {
      body.push(reportRows[i][0] + ' => ' + reportRows[i][1]);
    }
    MailApp.sendEmail(email, '' +
      reportRows.length +
      ' AdWords accounts have ad groups that are declining, ' +
      'in AdWords MCC account ' + mccManager.mccAccount().getCustomerId(),
      body.join('\n'));

  };

  // Return the external interface.
  return {
    insertRowsForTab: insertRowsForTab,
    insertSummaryRows: insertSummaryRows,
    setValueForCell: setValueForCell,
    sendEmail: sendEmail
  };

})();


/**
 * Utilities that are useful for dealing with dates.
 *
 * @return {Object} Properties returned include today, oneWeekAgo, twoWeeksAgo,
 * and threeWeeksAgo.
 */
var Util = (function() {
  // 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);
  }

  // Compute once, ahead of time, and just return the value directly.
  var today = getDateStringInPast(0);
  var oneWeekAgo = getDateStringInPast(7);
  var twoWeeksAgo = getDateStringInPast(14);
  var threeWeeksAgo = getDateStringInPast(21);

  return {
    today: today,
    oneWeekAgo: oneWeekAgo,
    twoWeeksAgo: twoWeeksAgo,
    threeWeeksAgo: threeWeeksAgo
  };

})();

/**
 * Wrapper for Logger.log.
 *
 * @param {string} t The text to log
 */
function debug(t) {
    Logger.log(t);
}

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