Declining Ad Groups Report - Manager Account

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

Declining Ad Groups Report for Manager Accounts extends the single account Declining Ad Groups Report script to run for multiple accounts under a manager account.

The definition of a declining ad group is the same as in the single account version:

• The ad group is ENABLED and belongs to an ENABLED campaign, which means it’s serving.
• The ad group’s ‘Click Through Rate’ has been decreasing for three consecutive weeks.

Naturally, more sophisticated criteria may be used to select declining ad groups.

The report produces a multi-tabbed spreadsheet. For each account, a separate tab is created, which shows the declining account report for that account.

There is also a summary tab that gives some aggregate data across accounts. It shows the number of declining ad groups, the average drop in click through rate, the average change in cost, and the total change in cost for each account.

If desired, an email address can be entered into the report summary tab, and an email will be sent each time this report is run, summarizing the accounts which have the highest number of declining ad groups.

Scheduling

The script uses three weeks worth of statistics to generate the report. It would make sense to schedule it Weekly.

Setup

• Setup a spreadsheet-based script with the source code below. Use MCC Declining Ad Groups Report template spreadsheet
• Update CONFIG.SPREADSHEET_URL in the code to reflect your spreadsheet.
• Update CONFIG.TIMEZONE in code to reflect your timezone.
• [Optional] Update CONFIG.SUMMARY_TAB_NAME in the code if you choose to alter the name of the report summary tab
• Schedule the script Weekly.

Setting up a spreadsheet-based script

1. Open the template spreadsheet
2. Make a copy of the spreadsheet and save it:
   • In the spreadsheet, menu, select "File -> Make a copy".
   • Enter the new spreadsheet name, click "OK".
3. In your Google Ads manager account, navigate to Scripts
4. Create a new script by clicking the "+ SCRIPT" button and give it a name; authorize the script.
5. Copy-paste source code into the script (see below)
6. Replace
   

   var SPREADSHEET_URL = "[YOUR_URL]";

   with

   var SPREADSHEET_URL = "https://docs.google.com/...";

   Use the URL of the spreadsheet created in step 2.
7. Save the script, click "Close".
8. If necessary, schedule the script
   

Source code

// 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/google-ads/scripts/docs/solutions/adsmanagerapp-declining-adgroups
 *     for more details.
 *
 * @author Google Ads Scripts Team [[e-mail]]
 *
 * @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 {AdsApp.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 = AdsApp.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 = AdsManagerApp.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 = AdsApp.currentAccount(); // save the mccAccount
    currentAccount = AdsApp.currentAccount();
  };

 /**
  * After calling this, AdsApp will have the next account selected.
  * If there are no more accounts to process, re-selects the original
  * MCC account.
  *
  * @return {AdsApp.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();
      AdsManagerApp.select(currentAccount);
      return currentAccount;
    }
    else {
      AdsManagerApp.select(mccAccount);
      return null;
    }

  };

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

 /**
  * Returns the original MCC account.
  *
  * @return {AdsApp.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(AdsApp.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 == '[e-mail]') {
      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 +
      ' Google Ads accounts have ad groups that are declining, ' +
      'in Google Ads 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 || AdsApp.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);
}