Informe de grupos de anuncios rechazado: una sola cuenta

Ícono de informes

Las secuencias de comandos de Google Ads pueden acceder a las estadísticas de varios períodos, lo que permite realizar comparaciones del rendimiento de las campañas a lo largo del tiempo.

El informe de grupos de anuncios en descenso recupera los grupos de anuncios cuyo rendimiento se considera que está deteriorando si cumplen estas condiciones:

  • El grupo de anuncios está HABILITADO y pertenece a una campaña HABILITADA, lo que significa que sus anuncios se están publicando.
  • La tasa de clics del grupo de anuncios disminuyó durante tres semanas consecutivas.

La secuencia de comandos envía los datos a una hoja de cálculo como la siguiente:

Captura de pantalla de la hoja de cálculo de grupos de anuncios rechazados

Programación

La secuencia de comandos utiliza estadísticas de tres semanas para generar el informe, por lo que la programación más adecuada es Semanalmente.

Configuración

  • Configura una secuencia de comandos basada en hojas de cálculo con el código fuente que aparece a continuación. Usar la hoja de cálculo de la plantilla del informe de grupos de anuncios en declive
  • Actualiza SPREADSHEET_URL en el código.
  • Programa la secuencia de comandos para Semanalmente.

Código fuente

// 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/google-ads/scripts/docs/solutions/declining-adgroups
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.0
 *
 * @changelog
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.0.2
 *   - Added validation for spreadsheet URL.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

/**
 * Fetch the ad groups whose performance we consider to be worsening
 * for three consecutive weeks and updates to spreadsheet.
 */
function main() {
  console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
  const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());

  const sheet = spreadsheet.getSheets()[0];
  spreadsheet.getRangeByName('account_id').setValue(
      AdsApp.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();

  const adGroupsIterator = AdsApp.adGroups()
      .withCondition("ad_group.status = 'ENABLED'")
      .withCondition("campaign.status = 'ENABLED'")
      .forDateRange('LAST_7_DAYS')
      .orderBy('metrics.ctr ASC')
      .withLimit(100)
      .get();

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

  const reportRows = [];

  for (const adGroup of adGroupsIterator) {
    // Retrieve the trend of the ad group's CTR.
    const statsThreeWeeksAgo = adGroup.getStatsFor(threeWeeksAgo, twoWeeksAgo);
    const statsTwoWeeksAgo = adGroup.getStatsFor(twoWeeksAgo, oneWeekAgo);
    const statsLastWeek = adGroup.getStatsFor(oneWeekAgo, today);

    // If the ad group is declining week over week,
    // record it into the spreadsheet.
    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');
  }

  const email = spreadsheet.getRangeByName('email').getValue();
  if (email) {
    const 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(const row of reportRows){
      body.push(row[0] + ' > ' + row[1]);
      body.push(`  ${ctr(row[6])} > ${ctr(row[4])} > ${ctr(row[2])}\n`);
    }
    MailApp.sendEmail(email,
      `${reportRows.length} ad groups are declining in Google Ads account ` +
      `${AdsApp.currentAccount().getCustomerId()}`,
      body.join('\n'));
  }
}

/**
 * Formats the Ctr value.
 *
 * @param {string} number The Ctr value.
 * @return {string} The formatted Ctr value.
 */
function ctr(number) {
  return parseInt(number * 10000) / 10000 + '%';
}

/**
 * Calculates date ranges of last week, two weeks ago and three weeks ago.
 *
 * @param {number} numDays The Number of Days.
 * @param {!Date} date The current date.
 * @return {string} Returns YYYYMMDD-formatted date.
 */
function getDateStringInPast(numDays, date) {
  date = date || new Date();
  const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
  const past = new Date(date.getTime() - numDays * MILLIS_PER_DAY);
  return getDateStringInTimeZone('yyyyMMdd', past);
}

/**
 * Converts date in YYYYMMDD format.
 *
 * @param {string} format YYYYMMDD format.
 * @param {!Date} date The current date.
 * @param {string} timeZone The Time Zone e.g.,America/New_York.
 * @return {string} Returns YYYYMMDD-formatted date.
 */
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.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);
}