Raport dotyczący spadku liczby grup reklam – pojedyncze konto

Ikona raportów

Skrypty Google Ads mają dostęp do statystyk z wielu zakresów dat, co umożliwia porównywanie skuteczności kampanii na przestrzeni czasu.

Raport „Spadek grup reklam” pobiera grupy reklam, których skuteczność uznaje się za pogarszającą się, jeśli spełniają te warunki:

  • Grupa reklam jest ENABLED i należy do kampanii ENABLED, co oznacza, że jej reklamy się wyświetlają.
  • Współczynnik klikalności tej grupy reklam maleje od trzech kolejnych tygodni.

Skrypt zapisuje dane w arkuszu kalkulacyjnym podobnym do tego:

Zrzut ekranu arkusza kalkulacyjnego malejących grup reklam

Planuję

Skrypt do wygenerowania raportu wykorzystuje 3-tygodniowe statystyki, więc najbardziej odpowiednim harmonogramem jest Co tydzień.

Konfiguracja

  • Skonfiguruj skrypt oparty na arkuszu kalkulacyjnym za pomocą poniższego kodu źródłowego. Skorzystaj z szablonu raportu „Spadające grupy reklam”
  • Zaktualizuj kod SPREADSHEET_URL w kodzie.
  • Zaplanuj skrypt na Co tydzień.

Kod źródłowy

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