Declining Ad Groups Report - Single Account

Reports icon

Google Ads scripts can access statistics over multiple date ranges, enabling campaign performance comparisons through time.

Declining Ad Groups Report fetches ad groups whose performance are considered to be worsening if they meet these conditions:

  • The ad group is ENABLED and belongs to an ENABLED campaign, which means its ads are serving.
  • The ad group's Click-Through Rate has been decreasing for three consecutive weeks.

The script outputs the data into a spreadsheet like the following:

Screenshot of declining ad groups spreadsheet

Scheduling

The script uses three weeks worth of statistics to generate the report, so the most appropriate schedule is Weekly.

Setup

  • Setup a spreadsheet-based script with the source code below. Use the Declining Ad Groups Report template spreadsheet
  • Update SPREADSHEET_URL in the code.
  • Schedule the script for 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/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);
}