Declining Ad Groups Report - Single Account

This script is for a single account. For operating on multiple accounts in a Manager Account, use the Manager Account version of the script.

AdWords scripts can easily access statistics over multiple date ranges, and can therefore compare performance of campaigns through time.

Declining Ad Groups Report fetches ad groups whose performance we consider to be worsening:

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

Obviously, a more sophisticated measure of "worsening" may be developed.

The script outputs the data into a spreadsheet that looks like this:

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 Declining Ad Groups Report template spreadsheet
  • Don't forget to update SPREADSHEET_URL in code.
  • Schedule the script Weekly.

Source code

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

Send feedback about...

AdWords Scripts
AdWords Scripts
Need help? Visit our support page.