此脚本扩展了帐号异常检测器，以便针对单个经理帐号下的多个帐号运行。
一旦 Google Ads 帐号的行为与过去截然不同，“帐号异常检测器”脚本就会提醒您。遇到问题时，该脚本会发送一封包含提醒的电子邮件。每天只发送一条提醒。
脚本会将今天的统计信息与一周中同一天的历史平均值进行比较。例如，比较星期二下午 1 点的统计信息与之前 26 个星期二的统计信息。您可以根据帐号的使用时间和稳定性调整要回溯的周数。
此脚本提供的视图显示经理帐号下所有帐号的摘要。这样，您就可以轻松、一目了然地了解帐号的效果。
正在安排
将脚本设为每小时运行一次，以便在异常发生后一小时内收到电子邮件。
运作方式
假设脚本在星期二晚上 7 点运行。由于 Google Ads 统计信息可能会有最长 3 小时的延迟，因此脚本仅会考虑下午 4 点之前的统计信息。
该脚本会获取过去 26 个星期二的统计信息，取平均值，然后将平均值与当天的统计信息进行比较。
当天不会触发针对同一帐号的相同类型的后续提醒。如果您想重置提醒，请清除要重置的帐号所在行的背景颜色。
初始设置
点击下面的按钮，在您的 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 MCC Account Anomaly Detector
*
* @fileoverview The MCC Account Anomaly Detector alerts the advertiser whenever
* one or more accounts in a group of advertiser accounts under an MCC account
* is suddenly behaving too differently from what's historically observed. See
* https://developers.google.com/google-ads/scripts/docs/solutions/manager-account-anomaly-detector
* 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.4
* - Added conversions to tracked statistics.
* - version 1.3.2
* - Added validation for external spreadsheet setup.
* - version 1.3.1
* - Improvements to time zone handling.
* - version 1.3
* - Cleanup the script a bit for easier debugging and maintenance.
* - version 1.2
* - Added Google Ads API report version.
* - version 1.1
* - Fix the script to work in accounts where there is no stats.
* - 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/d/1Tj-UPGaTONtUbTAGCuJ2j_8hEABCBRr7bUH7b2aFh88/copy
// Make sure the sheet is owned by or shared with same Google user executing the script
'spreadsheet_url': 'YOUR_SPREADSHEET_URL',
// Uncomment below to include an account label filter
// 'account_label': 'High Spend Accounts',
'mcc_child_account_limit': 50,
// 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'
},
'advanced_options': {
/* Only modify the spreadsheet_setup when you are making corresponding
spreadsheet changes. */
'spreadsheet_setup': {
'const': {
'FIRST_DATA_ROW': 12,
'FIRST_DATA_COLUMN': 2,
'TOTAL_DATA_COLUMNS': 9
},
'columns': {
'NumOfColumns': 4,
'Impressions':
{'Column': 3, 'Color': 'red', 'AlertRange': 'impressions_alert'},
'Clicks':
{'Column': 4, 'Color': 'orange', 'AlertRange': 'clicks_alert'},
'Conversions': {
'Column': 5,
'Color': 'dark yellow 2',
'AlertRange': 'conversions_alert'
},
'Cost': {'Column': 6, 'Color': 'yellow', 'AlertRange': 'cost_alert'}
}
}
}
};
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const REPORTING_OPTIONS = CONFIG.reporting_options;
const MCC_CHILD_ACCOUNT_LIMIT = CONFIG.mcc_child_account_limit;
const STATS = CONFIG.advanced_options.spreadsheet_setup.columns;
const CONST = CONFIG.advanced_options.spreadsheet_setup.const;
const DAYS = [
'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday',
'Sunday'
];
function main() {
let mccAccount;
const alertText = [];
const sheetUtil = new SheetUtil();
const mccManager = new MccManager();
// Set up internal variables; called only once, here.
mccManager.init();
console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
const spreadsheet = validateAndGetSpreadsheet(SPREADSHEET_URL);
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
let dataRow = CONST.FIRST_DATA_ROW;
sheetUtil.setupData(spreadsheet, mccManager);
console.log(`Manager account: ${mccManager.getMccAccount().getCustomerId()}`);
while (mccAccount = mccManager.getNextAccount()) {
console.log(`Processing account ${mccAccount.getCustomerId()}`);
alertText.push(processAccount(mccAccount, spreadsheet, dataRow, sheetUtil));
dataRow++;
}
sendEmail(mccManager.getMccAccount(), alertText, spreadsheet);
}
/**
* For each of Impressions, Clicks, Conversions, and Cost, check to see if the
* values are out of range. If they are, and no alert has been set in the
* spreadsheet, then 1) Add text to the email, and 2) Add coloring to the cells
* corresponding to the statistic.
* @param {string} account An account of Mcc manager.
* @param {string} spreadsheet An Url of spreadsheet.
* @param {number} startingRow A number of a row defined in constant.
* @param {!object} sheetUtil An object of SheetUtil class.
* @return {string} the next piece of the alert text to include in the email.
*/
function processAccount(account, spreadsheet, startingRow, sheetUtil) {
const sheet = spreadsheet.getSheets()[0];
const thresholds = sheetUtil.getThresholds();
const today = AdsApp.search(sheetUtil.getTodayQuery(), REPORTING_OPTIONS);
const past = AdsApp.search(sheetUtil.getPastQuery(), REPORTING_OPTIONS);
const hours = sheetUtil.getHourOfDay();
const todayStats = accumulateRows(today, hours, 1); // just one week
const pastStats = accumulateRows(past, hours, sheetUtil.getWeeksToAvg());
let alertText = [`Account ${account.getCustomerId()}`];
const validWhite = ['', 'white', '#ffffff']; // these all count as white
// Colors cells that need alerting, and adds text to the alert email body.
function generateAlert(field, emailAlertText) {
// There are 2 cells to check, for Today's value and Past value
const bgRange = [
sheet.getRange(startingRow, STATS[field].Column, 1, 1),
sheet.getRange(
startingRow, STATS[field].Column + STATS.NumOfColumns, 1, 1)
];
const bg = [bgRange[0].getBackground(), bgRange[1].getBackground()];
// If both backgrounds are white, change background Colors
// and update most recent alert time.
if ((-1 != validWhite.indexOf(bg[0])) &&
(-1 != validWhite.indexOf(bg[1]))) {
bgRange[0].setBackground([[STATS[field]['Color']]]);
bgRange[1].setBackground([[STATS[field]['Color']]]);
spreadsheet.getRangeByName(STATS[field]['AlertRange'])
.setValue(`Alert at ${hours}:00`);
alertText.push(emailAlertText);
}
}
if (thresholds.Impressions &&
todayStats.Impressions < pastStats.Impressions * thresholds.Impressions) {
generateAlert(
`Impressions`,
` Impressions are too low: ${todayStats.Impressions}` +
` Impressions by ${hours}:00, expecting at least ` +
`${parseInt(pastStats.Impressions * thresholds.Impressions, 10)}`);
}
if (thresholds.Clicks &&
todayStats.Clicks < (pastStats.Clicks * thresholds.Clicks).toFixed(1)) {
generateAlert(
`Clicks`,
` Clicks are too low: ${todayStats.Clicks}` +
` Clicks by ${hours}:00, expecting at least ` +
`${(pastStats.Clicks * thresholds.Clicks).toFixed(1)}`);
}
if (thresholds.Conversions &&
todayStats.Conversions <
(pastStats.Conversions * thresholds.Conversions).toFixed(1)) {
generateAlert(
`Conversions`,
` Conversions are too low: ${todayStats.Conversions}` +
` Conversions by ${hours}:00, expecting at least ` +
`${(pastStats.Conversions * thresholds.Conversions).toFixed(1)}`);
}
if (thresholds.Cost &&
todayStats.Cost > (pastStats.Cost * thresholds.Cost).toFixed(2)) {
generateAlert(
`Cost`,
` Cost is too high: ${todayStats.Cost} ` +
`${account.getCurrencyCode()} by ${hours}` +
`:00, expecting at most ` +
`${(pastStats.Cost * thresholds.Cost).toFixed(2)}`);
}
// If no alerts were triggered, we will have only the heading text. Remove it.
if (alertText.length === 1) {
alertText = [];
}
const dataRows = [[
account.getCustomerId(), todayStats.Impressions, todayStats.Clicks,
todayStats.Conversions, todayStats.Cost, pastStats.Impressions.toFixed(0),
pastStats.Clicks.toFixed(1), pastStats.Conversions.toFixed(1),
pastStats.Cost.toFixed(2)
]];
sheet
.getRange(
startingRow, CONST.FIRST_DATA_COLUMN, 1, CONST.TOTAL_DATA_COLUMNS)
.setValues(dataRows);
return alertText;
}
class SheetUtil {
constructor() {
this.thresholds = {};
this.upToHour = 1; // default
this.weeks = 26; // default
this.todayQuery = '';
this.pastQuery = '';
}
/**
* A function to set the data from spreadsheet.
*/
setupData(spreadsheet, mccManager) {
console.log('Running setupData');
spreadsheet.getRangeByName('date').setValue(new Date());
spreadsheet.getRangeByName('account_id')
.setValue(mccManager.getMccAccount().getCustomerId());
const thresholds = this.thresholds;
function getThresholdFor(field) {
thresholds[field] =
parseField(spreadsheet.getRangeByName(field).getValue());
}
getThresholdFor('Impressions');
getThresholdFor('Clicks');
getThresholdFor('Conversions');
getThresholdFor('Cost');
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);
this.upToHour = parseInt(getDateStringInTimeZone('H', upTo), 10);
spreadsheet.getRangeByName('timestamp')
.setValue(
`${DAYS[getDateStringInTimeZone('u', now)]}, ${this.upToHour}:00`);
if (this.upToHour === 1) {
// First run of the day, clear existing alerts.
spreadsheet.getRangeByName(STATS['Clicks']['AlertRange']).clearContent();
spreadsheet.getRangeByName(STATS['Impressions']['AlertRange'])
.clearContent();
spreadsheet.getRangeByName(STATS['Conversions']['AlertRange'])
.clearContent();
spreadsheet.getRangeByName(STATS['Cost']['AlertRange']).clearContent();
// Reset background and font Colors for all data rows.
const bg = [];
const ft = [];
const bg_single = [
'white', 'white', 'white', 'white', 'white', 'white', 'white', 'white',
'white'
];
const ft_single = [
'black', 'black', 'black', 'black', 'black', 'black', 'black', 'black',
'black'
];
// Construct a 50-row array of colors to set.
for (let a = 0; a < MCC_CHILD_ACCOUNT_LIMIT; ++a) {
bg.push(bg_single);
ft.push(ft_single);
}
const dataRegion = spreadsheet.getSheets()[0].getRange(
CONST.FIRST_DATA_ROW, CONST.FIRST_DATA_COLUMN,
MCC_CHILD_ACCOUNT_LIMIT, CONST.TOTAL_DATA_COLUMNS);
dataRegion.setBackgrounds(bg);
dataRegion.setFontColors(ft);
}
const weeksStr = spreadsheet.getRangeByName('weeks').getValue();
this.weeks = parseInt(weeksStr.substring(0, weeksStr.indexOf(' ')), 10);
const dateRangeToCheck = getDateStringInPast(0, upTo);
const dateRangeToEnd = getDateStringInPast(1, upTo);
const dateRangeToStart = getDateStringInPast(1 + this.weeks * 7, upTo);
const fields = `segments.hour, segments.day_of_week, metrics.clicks, ` +
`metrics.impressions, metrics.conversions, metrics.cost_micros`;
this.todayQuery = `SELECT ${fields} FROM customer ` +
`WHERE segments.date BETWEEN "${dateRangeToCheck}" ` +
`AND "${dateRangeToCheck}"`;
this.pastQuery = `SELECT ${fields} FROM customer ` +
`WHERE segments.day_of_week=` +
`${DAYS[getDateStringInTimeZone('u', now)].toUpperCase()} ` +
`AND segments.date BETWEEN "${dateRangeToStart}" ` +
`AND "${dateRangeToEnd}"`;
}
/**
* Returns the thresholds.
*
* @return {!Object} An object of thresholds data.
*/
getThresholds() {
return this.thresholds;
}
/**
* Returns the hour ofdDay.
*
* @return {number} A value of uptoHour.
*/
getHourOfDay() {
return this.upToHour;
}
/**
* Returns the Weeks .
*
* @return {number} A value of weeks.
*/
getWeeksToAvg() {
return this.weeks;
}
/**
* Returns the past query.
*
* @return {string} Past query is returned.
*/
getPastQuery() {
return this.pastQuery;
}
/**
* Returns the today query.
*
* @return {string} Today query is returned.
*/
getTodayQuery() {
return this.todayQuery;
}
}
function sendEmail(account, alertTextArray, spreadsheet) {
let bodyText = '';
for (const alertText of alertTextArray) {
if (alertText.length != 0) {
bodyText += alertText.join('\n') + '\n\n';
}
}
bodyText = bodyText.trim();
const email = spreadsheet.getRangeByName('email').getValue();
if (bodyText.length > 0 && email && email.length > 0 &&
email != 'foo@example.com') {
console.log('Sending Email');
MailApp.sendEmail(
email, `Google Ads Account ${account.getCustomerId()} misbehaved.`,
`Your account ${account.getCustomerId()}` +
` is not performing as expected today: \n\n` +
`${bodyText}\n\n` +
`Log into Google Ads and take a look: ` +
`ads.google.com\n\nAlerts dashboard: ` +
`${SPREADSHEET_URL}`);
} else if (bodyText.length === 0) {
console.log('No alerts triggered. No email being sent.');
}
}
/**
* 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
* @param {number} value that needs to be converted.
* @return {string} A value that is of type string.
*/
function toFloatFromMicros(value) {
value = parseFloat(value);
return (value / 1000000).toFixed(2);
}
/**
* Accumulate stats for a group of rows up to the hour specified.
*
* @param {!Object} rowsIter The result of query as a iterator over the rows.
* @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(rowsIter, hours, weeks) {
let result = {Clicks: 0, Impressions: 0, Conversions: 0, Cost: 0};
while (rowsIter.hasNext()) {
const row = rowsIter.next();
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
};
}
function checkInRange(today, yesterday, coefficient, field) {
const yesterdayValue = yesterday[field] * coefficient;
if (today[field] > yesterdayValue * 2) {
console.log(`${field} too much`);
} else if (today[field] < yesterdayValue / 2) {
console.log(`${field} too little`);
}
}
/**
* 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);
}
/**
* Module that deals with fetching and iterating through multiple accounts.
*/
class MccManager {
constructor() {
this.accountIterator = '';
this.mccAccount = '';
this.currentAccount = '';
}
/**
* One-time initialization function.
*/
init() {
const accountSelector = AdsManagerApp.accounts();
// Use this to limit the accounts that are being selected in the report.
if (CONFIG.account_label) {
accountSelector.withCondition(
'LabelNames CONTAINS \'' + CONFIG.account_label + '\'');
}
accountSelector.withLimit(MCC_CHILD_ACCOUNT_LIMIT);
this.accountIterator = accountSelector.get();
this.mccAccount = AdsApp.currentAccount(); // save the mccAccount
this.currentAccount = AdsApp.currentAccount();
}
/**
* After calling this, AdsApp will have the next account selected.
* If there are no more accounts to process, re-selects the original
* MCC account.
*
* @return {AdsApp.Account} The account that has been selected.
*/
getNextAccount() {
if (this.accountIterator.hasNext()) {
this.currentAccount = this.accountIterator.next();
AdsManagerApp.select(this.currentAccount);
return this.currentAccount;
} else {
AdsManagerApp.select(this.mccAccount);
return null;
}
}
/**
* Returns the currently selected account. This is cached for performance.
*
* @return {AdsApp.Account} The currently selected account.
*/
getCurrentAccount() {
return this.currentAccount;
}
/**
* Returns the original MCC account.
*
* @return {AdsApp.Account} The original account that was selected.
*/
getMccAccount() {
return this.mccAccount;
}
}
/**
* 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;
}