이 스크립트는 계정 이상 감지기를 확장하여 단일 관리자 계정의 여러 계정에 대해 실행합니다.
계정 이상 감지 프로그램 스크립트는 Google Ads 계정의 동작이 과거의 행동과 크게 다를 때마다 알림을 보냅니다. 문제가 발생하면 스크립트는 알림이 포함된 이메일을 보냅니다. 하루에 한 개의 알림만 전송됩니다.
스크립트는 오늘의 통계를 같은 요일의 이전 평균과 비교합니다. 예를 들어 화요일 오후 1시의 통계를 이전 26일 화요일의 통계와 비교합니다. 계정의 사용 기간 및 안정성에 따라 이전 데이터를 확인할 주 수를 조정할 수 있습니다.
이 스크립트에서 제공하는 보기에는 관리자 계정의 모든 계정이 요약되어 있습니다. 이렇게 하면 계정의 실적을 한눈에 손쉽게 파악할 수 있습니다.
예약
비정상 이벤트가 발생한 지 1시간 이내에 이메일을 받으려면 스크립트를 매시간 실행하도록 예약하세요.
사용 방법
스크립트가 화요일 오후 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;
}