Bericht "Kontozusammenfassung"

Symbol: Berichte

Der Bericht "Kontozusammenfassung" ist ein Übersichtsbericht mit Daten zur Leistung eines gesamten Google Ads-Kontos. Außerdem sendet das Skript täglich eine E-Mail im HTML-Format mit aktuellen Kontostatistiken. Account Manager können mit einem vergleichbaren Verfahren professionell formatierte Berichte direkt an ihre Kunden senden.

Screenshot der Tabelle mit der Kontoübersicht

Wird geplant

Das Skript arbeitet mit den Daten des Vortags. Planen Sie es täglich um 3 Uhr nachts oder später. Da die Aktualisierung der Google Ads-Statistiken bis zu 3 Stunden verzögert ist, vermeiden Sie so Ungenauigkeiten.

Funktionsweise

Das Skript ruft die Statistiken des Vortags ab und aktualisiert die Tabelle. Die Grafiken sind zur Anzeige der Statistiken vorkonfiguriert. Wenn die E-Mail-Adresse angegeben ist, sendet das Skript eine E-Mail im HTML-Format.

Einrichtung

  • Klicken Sie auf die Schaltfläche unten, um das tabellenbasierte Skript in Ihrem Google Ads-Konto zu erstellen.

    Skriptvorlage installieren

  • Klicken Sie auf die Schaltfläche unten, um eine Kopie der Tabellenvorlage zu erstellen.

    Tabellenvorlage kopieren

  • Aktualisieren Sie spreadsheet_url im Script.

  • Planen Sie das Skript so, dass es täglich um 3 Uhr ausgeführt wird.

Bericht erweitern (optional)

Der Bericht kann erweitert werden, indem weitere Messwerte aus der Ressource customer hinzugefügt werden:

  1. Fügen Sie Ihrer Tabelle in der nächsten verfügbaren Spalte eine Überschrift (z. B. Anteil an möglichen Impressionen in der Suche) hinzu. In der Grundvorlage steht diese Spalte neben Klicks.

  2. Bearbeiten Sie die REPORT_FIELDS-Definition oben im Script. Beispiel:

      var REPORT_FIELDS = [
        {columnName: 'metrics.cost_micros', displayName: 'Cost'},
        {columnName: 'metrics.average_cpc', displayName: 'Average CPC'},
        {columnName: 'metrics.ctr', displayName: 'CTR'},
        {columnName: 'metrics.impression', displayName: 'Impressions'},
        {columnName: 'metrics.clicks', displayName: 'Clicks'},
        {columnName: 'metrics.search_impression-share', displayName: 'Search Imp. Share'}
      ];
    

    Dadurch wird dem Bericht der Anteil an möglichen Impressionen in der Suche hinzugefügt. Wählen Sie einen beliebigen Wert für displayName aus. Dieser wird in der E-Mail-Überschrift verwendet.

  3. So erstellen Sie ein Diagramm für das neue Feld:

    1. Klicken Sie auf eines der vorhandenen Diagramme in Ihrer Tabelle und wählen Sie unter dem Dreipunkt-Menü oben rechts im Diagramm Diagramm kopieren aus. Klicken Sie dann mit der rechten Maustaste und wählen Sie Einfügen aus.
    2. In der Tabelle wird ein neues Diagramm erstellt. Wählen Sie im Menü für das neue Diagramm die Option Diagramm bearbeiten aus.
    3. Ändern Sie den Spaltenverweis Series auf die Spalte mit den neuen Daten. Ändern Sie beispielsweise G in H.
    4. Doppelklicken Sie auf den Diagrammtitel und ändern Sie ihn in den neuen Messwertnamen.

Quellcode

// 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 Account Summary Report
 *
 * @overview The Account Summary Report script generates an at-a-glance report
 *     showing the performance of an entire Google Ads account. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/account-summary
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.1
 *
 * @changelog
 * - version 2.1
 *   - Split into info, config, and code.
 * - version 2.0
 *   - Updated to use new Google Ads Scripts features.
 * - version 1.1
 *   - Add user-updateable fields, and ensure report row ordering.
 * - version 1.0.4
 *   - Improved code readability and comments.
 * - version 1.0.3
 *   - Added validation for external spreadsheet setup.
 * - version 1.0.2
 *   - Fixes date formatting bug in certain timezones.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */
/**
 * Configuration to be used for the Account Summary Report.
 */

CONFIG = {
  // URL of the report spreadsheet. This should be a copy of
  // https://docs.google.com/spreadsheets/d/1gYLXtDK93lWoTe3OBKvTlfcc7L_qHJFgWU9N6HwhZtU/copy
  'spreadsheet_url': 'YOUR_SPREADSHEET_URL',
  // More reporting options can be found at
  // https://developers.google.com/google-ads/scripts/docs/reference/adsapp/adsapp#report_2
  'reporting_options': {
    // Comment out the following line to default to the latest reporting
    // version.
    'apiVersion': 'v10'
  },
  /**
   * To add additional fields to the report, follow the instructions at
   * https://developers.google.com/google-ads/scripts/docs/solutions/account-summary#extending_the_report_optional
   */
  'report_fields': [
    {'columnName': 'metrics.cost_micros', 'displayName': 'Cost'},
    {'columnName': 'metrics.average_cpc', 'displayName': 'Avg. CPC'},
    {'columnName': 'metrics.ctr', 'displayName': 'CTR'},
    {'columnName': 'metrics.search_impression_share', 'displayName': 'Search Impr. share'},
    {'columnName': 'metrics.impressions', 'displayName': 'Impressions'},
    {'columnName': 'metrics.clicks', 'displayName': 'Clicks'}
  ]
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const REPORTING_OPTIONS = CONFIG.reporting_options;
const REPORT_FIELDS = CONFIG.report_fields;

/** The spreadsheet is updated to showcase the performance of the account.
 *  An email is sent to the email ID mentioned in the spreadsheet
 */
function main() {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  const spreadsheet = validateAndGetSpreadsheet();
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  spreadsheet.getRangeByName('account_id_report').setValue(AdsApp.currentAccount().getCustomerId());
  const yesterday = getYesterday();
  const date = getFirstDayToCheck(spreadsheet, yesterday);
  const rows = [];
  const existingDates = getExistingDates();
  while (date.getTime() <= yesterday.getTime()) {
    if (!existingDates[date]) {
      let row = getReportRowForDate(date);
      rows.push([new Date(date)].concat(REPORT_FIELDS.map(function(field) {
        row[field.columnName] = format(field.columnName, row[field.columnName]);
        return row[field.columnName];
 })));
    spreadsheet.getRangeByName('last_check').setValue(date);
    }
    date.setDate(date.getDate() + 1);
  }
  if (rows.length > 0) {
    writeToSpreadsheet(rows);
    const email = spreadsheet.getRangeByName('email').getValue();
    if (email) {
      sendEmail(email);
    }
  }
}

/**Converts the metrics.cost_micros by dividing it by a million to match the
 * output with version v1.1.1 of the file.
 *
 * @param {string} value that needs to be converted.
 * @return {string} A value that is of type float.
 */
function formatMicros(value) {
  const micros = parseFloat(value / 1000000).toFixed(2);
  return `${micros}`;
}

/**
 * Formats decimal number into a percentage.
 *
 * @param {string} value The decimal number to format.
 * @return {string} The decimal number formatted as a percentage.
 */
function formatPercentage(value) {
 value=parseFloat(value*100).toFixed(2)+'%';
 return value;
}

/**
 * Formats Impression Share values.
 *
 * @param {string} value The Impression Share in Google Ads API format.
 * @return {string} The Impression Share formatted for the spreadsheet.
 */
function formatImpressionShare(value) {
   if (value <= 0.0999) {
       value='<10%';
    }
    else if (value>0.0999) {
     value=parseFloat(value*100)+'%';
    }
    else {
      value='--';
    }
  return value;
}

/**
 *  Formats clicks, impressions, ctr, average_cpc, cost_micros field values.
 *
 * @param {string} column The name of the field.
 * @param {string} value The value of the field.
 * @return {string} The formatted value of the field.
 */
function format(column, value) {
  switch (column) {
    case 'metrics.clicks':
    case 'metrics.impressions':
      return value;
    case 'metrics.ctr':
      return formatPercentage(value);
    case 'metrics.average_cpc':
    case 'metrics.cost_micros':
      return formatMicros(value);
    case 'metrics.search_impression_share':
      return formatImpressionShare(value);
    default:
      throw new Error(`Unknown field ${column}`);
  }
}

/**
 * Retrieves a lookup of dates for which rows already exist in the spreadsheet.
 *
 * @return {!Object} A lookup of existing dates.
 */
function getExistingDates() {
  const spreadsheet = validateAndGetSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Report');
  const data = sheet.getDataRange().getValues();
  const existingDates = {};
  data.slice(5).forEach(function(row) {
    existingDates[row[1]] = true;
  });
  return existingDates;
}

/**
 * Sorts the data in the spreadsheet into ascending date order.
 */
function sortReportRows() {
  const spreadsheet = validateAndGetSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Report');
  const data = sheet.getDataRange().getValues();
  const reportRows = data.slice(5);
  if (reportRows.length) {
    reportRows.sort(function(rowA, rowB) {
      if (!rowA || !rowA.length) {
        return -1;
      } else if (!rowB || !rowB.length) {
        return 1;
      } else if (rowA[1] < rowB[1]) {
        return -1;
      } else if (rowA[1] > rowB[1]) {
        return 1;
      }
      return 0;
    });
    sheet.getRange(6, 1, reportRows.length, reportRows[0].length)
        .setValues(reportRows);
  }
}

/**
 * Append the data rows to the spreadsheet.
 *
 * @param {!Array<!Array<string>>} rows The data rows.
 */
function writeToSpreadsheet(rows) {
  const access = new SpreadsheetAccess(SPREADSHEET_URL, 'Report');
  let emptyRow = access.findEmptyRow(6, 2);
  if (emptyRow < 0) {
    access.addRows(rows.length);
    emptyRow = access.findEmptyRow(6, 2);
  }
  access.writeRows(rows, emptyRow, 2);
  sortReportRows();
}
/**
 * Sends mail to specified email address in spreadsheet
 *
 * @param {string} email address
 */
function sendEmail(email) {
  const day = getYesterday();
  const yesterdayRow = getReportRowForDate(day);
  day.setDate(day.getDate() - 1);
  const twoDaysAgoRow = getReportRowForDate(day);
  day.setDate(day.getDate() - 5);
  const weekAgoRow = getReportRowForDate(day);
  const html = [];
  html.push(
    '<html>',
      '<body>',
        '<table width=800 cellpadding=0 border=0 cellspacing=0>',
          '<tr>',
            '<td colspan=2 align=right>',
              "<div style='font: italic normal 10pt Times New Roman, serif; " +
                  "margin: 0; color: #666; padding-right: 5px;'>" +
                  'Powered by Google Ads Scripts</div>',
            '</td>',
          '</tr>',
          "<tr bgcolor='#3c78d8'>",
            '<td width=500>',
              "<div style='font: normal 18pt verdana, sans-serif; " +
              "padding: 3px 10px; color: white'>Account Summary report</div>",
            '</td>',
            '<td align=right>',
              "<div style='font: normal 18pt verdana, sans-serif; " +
              "padding: 3px 10px; color: white'>",
               AdsApp.currentAccount().getCustomerId(), '</h1>',
            '</td>',
            '</tr>',
          '</table>',
          '<table width=800 cellpadding=0 border=0 cellspacing=0>',
            "<tr bgcolor='#ddd'>",
              '<td></td>',
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
                  "text-align: left'>Yesterday</td>",
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
                  "text-align: left'>Two Days Ago</td>",
              "<td style='font: 12pt verdana, sans-serif; " +
                  'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
                  "text-align: left'>A week ago</td>",
            '</tr>');
  REPORT_FIELDS.forEach(function(field) {
    html.push(emailRow(
        field.displayName,field.columnName, yesterdayRow, twoDaysAgoRow,
        weekAgoRow));
  });
  html.push('</table>', '</body>', '</html>');
  MailApp.sendEmail(email, 'Google Ads Account ' +
      AdsApp.currentAccount().getCustomerId() + ' Summary Report', '',
      {htmlBody: html.join('\n')});
}

/**
 * Generates html summary report with the column names todays ,twodays ago, week ago
 *
 * @param {string} title of the report
 * @param {string} column field names
 * @param {!Object} yesterdayRow holds Object containing fields yesterday data
 * @param {!Object} twoDaysAgoRow holds Object containing fields twodays ago data
 * @param {!Object} weekAgoRow holds Object containing fields week ago data
 * @return {!html} html format
 */
function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) {
  yesterdayRow[column] = format(column,yesterdayRow[column]);
  twoDaysAgoRow[column] = format(column,twoDaysAgoRow[column]);
  weekAgoRow[column] = format(column,weekAgoRow[column]);
  const html = [];
  html.push('<tr>',
      "<td style='padding: 5px 10px'>" + title + '</td>',
      "<td style='padding: 0px 10px'>" +  yesterdayRow[column]+ '</td>',
      "<td style='padding: 0px 10px'>" + twoDaysAgoRow[column] +
          formatChangeString(yesterdayRow[column], twoDaysAgoRow[column]) +
      '</td>',
      "<td style='padding: 0px 10px'>" + weekAgoRow[column] +
          formatChangeString(yesterdayRow[column], weekAgoRow[column]) +
      '</td>',
      '</tr>');
  return html.join('\n');
}

/**
 * Retrieves Dates in the format yyyyMMdd
 *
 * @param {!Date} date value
 * @return {!Object} dateStrings
 */
function getReportRowForDate(date) {
  const timeZone = AdsApp.currentAccount().getTimeZone();
  const dateString = Utilities.formatDate(date, timeZone, 'yyyyMMdd');
  return getReportRowForDuring(dateString + ' AND ' + dateString);
}
/** Retrieves data from the select query
 *
 * @param {string} during on which dates to get the reports
 * @return {!Object} rows returns field values as an object
 */
function getReportRowForDuring(during) {
  const fields = REPORT_FIELDS.map(function(field) {return field.columnName;}).join(', ');
  const query = `SELECT ${fields} FROM customer WHERE segments.date BETWEEN ${during}`;
  const report = AdsApp.report(query,REPORTING_OPTIONS);
  return report.rows().next();
}

/**
 * Extracts the percentage value from a percentage string. E.g. given "12.3%" returns 12.3.
 *
 * @param {string} value A field value containing a percentage sign at the end.
 * @return {string} The percentage value.
 */
function extractPercentageValue(value){
   const index = value.indexOf('%');
   value = value.substring(0, index);
   return value;
}

/**
 * Formats a change between two values.
 *
 * @param {string} newValue The new value of a field.
 * @param {string} oldValue The old value of a field.
 * @return {string} A string representing the change in the field.
 */
function formatChangeString(newValue,oldValue) {
  const isPercentage = newValue.indexOf('%') >= 0;
  if(isPercentage){
    newValue = extractPercentageValue(newValue);
    oldValue = extractPercentageValue(oldValue);
  }
  const change = parseFloat(newValue - oldValue).toFixed(2);
  let changeString = change;
  if (isPercentage) {
    changeString = change + '%';
  }
  if (change >= 0) {
    return "<span style='color: #38761d; font-size: 8pt'> (+" +
        changeString + ')</span>';
  } else {
    return "<span style='color: #cc0000; font-size: 8pt'> (" +
        changeString + ')</span>';
  }
}

/**
 * The spreadsheet is accessed and updated.
 *
 * @param {string} spreadsheetUrl takes spreadsheets url
 * @param {string} sheetName is a sheet name
 */
function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  this.sheet = this.spreadsheet.getSheetByName(sheetName);

  // what column should we be looking at to check whether the row is empty?
  this.findEmptyRow = function(minRow, column) {
    const values = this.sheet.getRange(minRow, column,
        this.sheet.getMaxRows(), 1).getValues();
    for (let i = 0; i < values.length; i++) {
      if (!values[i][0]) {
        return i + minRow;
      }
    }
    return -1;
  };
  this.addRows = function(howMany) {
    this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
  };
  this.writeRows = function(rows, startRow, startColumn) {
    this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).
        setValues(rows);
  };
}

/**
 * Gets a date object that is 00:00 yesterday.
 *
 * @return {!Date} A date object that is equivalent to 00:00 yesterday in the
 *     account's time zone.
 */
function getYesterday() {
  const yesterday = new Date(Date.now() - 24 * 3600 * 1000);
  return new Date(getDateStringInTimeZone('MMM dd, yyyy 00:00:00 Z',
      yesterday));
}

/**
 * Returned the last checked date + 1 day, or yesterday if there isn't
 * a specified last checked date.
 *
 * @param {!Spreadsheet} spreadsheet The export spreadsheet.
 * @param {!Date} yesterday The yesterday date.
 * @return {!Date} The date corresponding to the first day to check.
 */
function getFirstDayToCheck(spreadsheet, yesterday) {
  const last_check = spreadsheet.getRangeByName('last_check').getValue();
  let date;
  if (last_check.length == 0) {
    date = new Date(yesterday);
  } else {
    date = new Date(last_check);
    date.setDate(date.getDate() + 1);
  }
  return date;
}

/**
 * Produces a formatted string representing a given date in a given time zone.
 *
 * @param {string} format A format specifier for the string to be produced.
 * @param {!Date} date A date object. Defaults to the current date.
 * @param {string} timeZone A time zone. Defaults to the account's time zone.
 * @return {string} A formatted string of the given date in the given time zone.
 */
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.
 *
 * @return {!Spreadsheet} The spreadsheet object itself, fetched from the URL.
 */
function validateAndGetSpreadsheet() {
  if ('YOUR_SPREADSHEET_URL' == 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.');
  }
  const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const email = spreadsheet.getRangeByName('email').getValue();
  if ('foo@example.com' == email) {
    throw new Error('Please either set a custom email address in the' +
        ' spreadsheet, or set the email field in the spreadsheet to blank' +
        ' to send no email.');
  }
  return spreadsheet;
}