拒絕廣告群組報表 - 單一帳戶

報表圖示

Google Ads 指令碼可以存取多個日期範圍的統計資料,方便您比較廣告活動成效。

「拒絕廣告群組報表」會擷取符合下列條件的廣告群組:成效較差的廣告群組:

  • 廣告群組已啟用且屬於「已啟用」的廣告活動,表示廣告正在放送。
  • 廣告群組連續三週的點閱率一直下滑。

指令碼會將資料輸出至試算表,如下所示:

拒絕廣告群組試算表的螢幕截圖

排程

指令碼會使用三週的統計資料產生報表,因此最合適的排程為「每週」

設定

  • 使用以下原始碼,設定適合試算表的指令碼。使用「拒絕廣告群組報表」範本試算表
  • 更新程式碼中的 SPREADSHEET_URL
  • 安排每週執行指令碼。

原始碼

// 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);
}