Scripts Google Ads

				
					// Copyright 2017, 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 Anomaly Detector
 *
 * @fileoverview The Account Anomaly Detector alerts the advertiser whenever an
 * advertiser account is suddenly behaving too differently from what's
 * historically observed. See
 * https://developers.google.com/google-ads/scripts/docs/solutions/account-anomaly-detector
 * for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.1
 *
 * @changelog
 * - version 2.2
 *   - Fixed time conversion issue.
 *   - Moved email code.
 * - version 2.1
 *   - Split into info, config, and code.
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.1.1
 *   - Fixed bug in handling of reports with 0 rows.
 * - version 1.1
 *   - Added conversions to tracked statistics.
 * - version 1.0.3
 *   - Improved code readability and comments.
 * - version 1.0.2
 *   - Added validation for external spreadsheet setup.
 *   - Updated to use report version v201609.
 * - version 1.0.1
 *   - Improvements to time zone handling.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * Configuration to be used for the Account Anomaly Detector.
 */
CONFIG = {
  // URL of the default spreadsheet template. This should be a copy of
  // https://docs.google.com/spreadsheets/u/1/d/1_KjU9UI8EyWQCIE6xlxnUVaomEaHGJW-dgfkA15JhZY/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'
  }
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const REPORTING_OPTIONS = CONFIG.reporting_options;

const FIELDS = ['segments.hour', 'segments.day_of_week', 'metrics.clicks',
        'metrics.impressions', 'metrics.conversions', 'metrics.cost_micros'];
const DAYS = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
            'Saturday', 'Sunday'];

/**
 * This script detects the anomalies(if any) in the account and alerts the
 * customer in form of a mail and spreadsheet.
 */

function main() {
  Logger.log('Using spreadsheet - %s.', SPREADSHEET_URL);
  const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());

  const impressionsThreshold = parseField(spreadsheet.
      getRangeByName('impressions').getValue());
  const clicksThreshold = parseField(spreadsheet.getRangeByName('clicks').
      getValue());
  const conversionsThreshold =
      parseField(spreadsheet.getRangeByName('conversions').getValue());
  const costThreshold = parseField(spreadsheet.getRangeByName('cost').getValue());
  const weeksStr = spreadsheet.getRangeByName('weeks').getValue();
  const weeks = parseInt(weeksStr.substring(0, weeksStr.indexOf(' ')),10);
  const email = spreadsheet.getRangeByName('email').getValue();

  const now = new Date();

  // Basic reporting statistics are usually available with no more than a 3-hour
  // delay.
  const upTo = new Date(now.getTime() - 3 * 3600 * 1000);
  const upToHour = parseInt(getDateStringInTimeZone('H', upTo),10);

  if (upToHour == 1) {
    // first run for the day, kill existing alerts
    spreadsheet.getRangeByName('clicks_alert').clearContent();
    spreadsheet.getRangeByName('impressions_alert').clearContent();
    spreadsheet.getRangeByName('conversions_alert').clearContent();
    spreadsheet.getRangeByName('cost_alert').clearContent();
  }

  const dateRangeToCheck = getDateStringInPast(0, upTo);
  const dateRangeToEnd = getDateStringInPast(1, upTo);
  const dateRangeToStart = getDateStringInPast(1 + weeks * 7, upTo);
  const fields = FIELDS.join(",");
  const dayOfWeekIndex = parseInt(getDateStringInTimeZone('u', now), 10);
  const dayOfWeek = DAYS[dayOfWeekIndex].toUpperCase();
  const todayQuery = `SELECT ${fields} FROM customer ` +
      `WHERE segments.date BETWEEN "${dateRangeToCheck}" ` +
      `AND "${dateRangeToCheck}"`;
  const pastQuery = `SELECT ${fields} FROM customer ` +
      `WHERE segments.day_of_week=` +
      `"${dayOfWeek}" ` +
      `AND segments.date BETWEEN "${dateRangeToStart}" ` +
      `AND "${dateRangeToEnd}"`;
  const todayStats = getReportStats(todayQuery, upToHour, 1);
  const pastStats = getReportStats(pastQuery, upToHour, weeks);

  const statsExist = todayStats && pastStats;
  const formattedHour = `${upToHour}:00`;

  const alertText = [];
  if (statsExist) {
    if (impressionsThreshold &&
        todayStats.impressions < pastStats.impressions * impressionsThreshold) {
      const ImpressionsAlert = `    Impressions are too low: ` +
          `${todayStats.impressions} impressions by ${formattedHour},` +
          ` expecting at least ` +
          `${parseInt(pastStats.impressions * impressionsThreshold,10)}`;
      writeAlert(spreadsheet, 'impressions_alert', alertText, ImpressionsAlert,
          upToHour);
    }
    if (clicksThreshold &&
        todayStats.clicks < pastStats.clicks * clicksThreshold) {
      const clickAlert = `    Clicks are too low: ` +
          `${todayStats.clicks} clicks by ${formattedHour},` +
          ` expecting at least ` +
          `${(pastStats.clicks * clicksThreshold).toFixed(1)}`;
      writeAlert(spreadsheet, 'clicks_alert', alertText, clickAlert, upToHour);
    }
    if (conversionsThreshold &&
        todayStats.conversions < pastStats.conversions * conversionsThreshold) {
      const conversionsAlert =
          `    Conversions are too low: ` +
          `${todayStats.conversions} conversions by ${formattedHour},` +
          ` expecting at least ` +
          `${(pastStats.conversions * conversionsThreshold).toFixed(1)}`;
      writeAlert(
        spreadsheet, 'conversions_alert', alertText, conversionsAlert,
        upToHour);
    }
    if (costThreshold &&
        todayStats.cost > pastStats.cost * costThreshold) {
      const costAlert = `    Cost is too high: ` +
          `${todayStats.cost} ${AdsApp.currentAccount().getCurrencyCode()} ` +
          `by ${formattedHour}, expecting at most ` +
          `${(pastStats.cost * costThreshold).toFixed(2)}`;
      writeAlert(spreadsheet, 'cost_alert', alertText, costAlert, upToHour);
    }
  }

  if (alertText.length > 0 && email && email.length > 0) {
    MailApp.sendEmail(email,
        `Google Ads Account ${AdsApp.currentAccount().getCustomerId()}` +
        ` misbehaved.`,
        `Your account ${AdsApp.currentAccount().getCustomerId()}` +
        ` is not performing as expected today: \n\n${alertText.join('\n')}` +
        `\n\nLog into Google Ads and take a look.\n\nAlerts dashboard: ` +
        `${SPREADSHEET_URL}`);
  }

  writeDataToSpreadsheet(spreadsheet, now, statsExist, todayStats, pastStats,
      AdsApp.currentAccount().getCustomerId());
}

/**
 * Converts the value passed as number into a float value.
 *
 * @param {number} value that needs to be converted .
 * @return {number} A value that is of type float.
 */
function toFloat(value) {
  value = value.toString().replace(/,/g, '');
  return parseFloat(value);
}

/**
 * Converts the value passed to a float value.
 *
 * @param {number} value that needs to be converted .
 * @return {number} A value that is of type float.
 */
function parseField(value) {
  if (value == 'No alert') {
    return null;
  } else {
    return toFloat(value);
  }
}

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

/**
 * Runs a Google Ads report query for a number of weeks and return the average
 * values for the stats.
 *
 * @param {string} query The formatted report query.
 * @param {number} hours The limit hour of day for considering the report rows.
 * @param {number} weeks The number of weeks for the past stats.
 * @return {Object} An object containing the average values for the stats.
 */
function getReportStats(query, hours, weeks) {
  const reportRows = [];
  const report = AdsApp.search(query, REPORTING_OPTIONS);
  for(const row of report){
     reportRows.push(row);
  }
  return accumulateRows(reportRows, hours, weeks);
}

/**
 * Accumulate stats for a group of rows up to the hour specified.
 *
 * @param {!Object} rows The result of query.
 * @param {number} hours The limit hour of day for considering the report rows.
 * @param {number} weeks The number of weeks for the past stats.
 * @return {!Object} Stats aggregated up to the hour specified.
 */
function accumulateRows(rows, hours, weeks) {
  let result = {clicks: 0, impressions: 0, conversions: 0, cost: 0};

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i];
    const hour = row['segments']['hour'];
    if (hour < hours) {
      result = addRow(row, result, 1 / weeks);
    }
  }
  return result;
}

/**
 * Adds two stats rows together and returns the result.
 *
 * @param {!Object} row An individual row on which average operations is performed for every property.
 * @param {!Object} previous object initialized as 0 for every property.
 * @param {number} coefficient To get the Average of the properties.
 * @return {!Object} The addition of two stats rows.
 */
function addRow(row, previous, coefficient) {
  coefficient = coefficient || 1;
  row = row || {Clicks: 0, Impressions: 0, Conversions: 0, Cost: 0};
  previous = previous || {clicks: 0, impressions: 0, conversions: 0, cost: 0};
  return {
      clicks: parseInt(row['metrics']['clicks'],10) * coefficient + previous.clicks,
      impressions:
          parseInt(row['metrics']['impressions'],10) * coefficient + previous.impressions,
      conversions:
          parseInt(row['metrics']['conversions'],10) * coefficient + previous.conversions,
      cost: toFloatFromMicros(row['metrics']['costMicros']) * coefficient + previous.cost
    };
}

/**
 * Produces a formatted string representing a date in the past of a given date.
 *
 * @param {number} numDays The number of days in the past.
 * @param {Date} date A date object. Defaults to the current date.
 * @return {string} A formatted string in the past of the given 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('yyyy-MM-dd', past);
}


/**
 * 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 and email address
 * to make sure that they're 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 or email 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.`);
  }
  const spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
  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;
}

/**
 * Writes the alert time in the spreadsheet and push the alert message to the
 * list of messages.
 *
 * @param {Spreadsheet} spreadsheet The dashboard spreadsheet.
 * @param {string} rangeName The named range in the spreadsheet.
 * @param {Array<string>} alertText The list of alert messages.
 * @param {string} alertMessage The alert message.
 * @param {number} hour The limit hour used to get the stats.
 */
function writeAlert(spreadsheet, rangeName, alertText, alertMessage, hour) {
  const range = spreadsheet.getRangeByName(rangeName);
  if (!range.getValue() || range.getValue().length == 0) {
    alertText.push(alertMessage);
    range.setValue(`Alerting ${hour}:00`);
  }
}

/**
 * Writes the data to the spreadsheet.
 *
 * @param {Spreadsheet} spreadsheet The dashboard spreadsheet.
 * @param {Date} now The date corresponding to the running time of the script.
 * @param {boolean} statsExist A boolean that indicates the existence of stats.
 * @param {Object} todayStats The stats for today.
 * @param {Object} pastStats The past stats for the period defined in the
 * spreadsheet.
 * @param {string} accountId The account ID.
 */
function writeDataToSpreadsheet(spreadsheet, now, statsExist, todayStats,
                                pastStats, accountId) {
  spreadsheet.getRangeByName('date').setValue(now);
  spreadsheet.getRangeByName('account_id').setValue(accountId);
  spreadsheet.getRangeByName('timestamp').setValue(
    getDateStringInTimeZone('E HH:mm:ss z', now));

  if (statsExist) {
    const dataRows = [
      [todayStats.impressions, pastStats.impressions.toFixed(0)],
      [todayStats.clicks, pastStats.clicks.toFixed(1)],
      [todayStats.conversions, pastStats.conversions.toFixed(1)],
      [todayStats.cost, pastStats.cost.toFixed(2)]
    ];
    spreadsheet.getRangeByName('data').setValues(dataRows);
  }
}