账号异常检测器随时会在有 Google Ads 账号出现以下情况时提醒广告客户： 行为突然与历史记录截然不同。遇到问题时，此脚本会向用户发送提醒电子邮件。提醒电子邮件每天仅发送一封。
脚本会将当天到目前为止观察到的统计信息与每星期同一天的历史统计信息进行比较。例如，比较星期二下午 1 点的统计信息 其中包含之前 26 个星期二的统计数据您可以根据账户的使用时间和稳定性调整回溯的周数。
时间安排
将脚本设为每小时运行可获得最及时的提醒。 如果提醒太吵，您可以将脚本设为在每天的中午运行 合理。
工作原理
假设脚本在星期二晚上 7 点运行。由于 Google Ads 统计信息可以 最多会延迟 3 小时， 脚本将只考虑下午 4 点之前的统计信息。
然后，脚本将提取过去 26 个星期二的统计信息，计算其平均值， 将结果与当天的统计数据进行比较。
在这一天，后面的同类型提醒不会被触发。如果您想重置提醒，请删除 Alerting 单元格的值。
设置
点击下方按钮，在 Google Cloud 控制台中创建基于电子表格的脚本。 Google Ads 账号。
点击下面的按钮，制作电子表格模板的副本。
更新脚本中的
spreadsheet_url。将脚本设为每小时运行一次。
源代码
// 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);
}
}