Детектор аномалий учетной записи предупреждает рекламодателя, когда поведение учетной записи Google Рекламы внезапно начинает отличаться от того, что наблюдалось исторически. При возникновении проблемы сценарий отправит пользователю электронное письмо с предупреждением. В день отправляется только одно электронное письмо с оповещением.
Скрипт сравнивает статистику, наблюдаемую сегодня, с исторической статистикой за тот же день недели. Например, статистика за вторник в 13:00 сравнивается со статистикой за 26 предыдущих вторников. Настройте количество недель для просмотра в зависимости от возраста и стабильности вашей учетной записи.
Планирование
Запланируйте выполнение сценария ежечасно , чтобы получить максимальную отдачу от оповещений. Если оповещение слишком шумное, имеет смысл запланировать его ежедневно около полудня.
Как это работает
Предположим, что сценарий запускается в 19:00 во вторник. Поскольку статистика Google Рекламы может иметь задержку до 3 часов , скрипт будет учитывать статистику только до 16:00.
Затем скрипт получит статистику за 26 предыдущих вторников, усреднит ее и сравнит результаты с сегодняшней статистикой.
Последующие оповещения того же типа в течение дня срабатывать не будут. Если вы хотите сбросить оповещение, удалите значение ячейки «Предупреждение» .
Настраивать
Нажмите кнопку ниже, чтобы создать сценарий на основе электронной таблицы в своем аккаунте Google Рекламы.
Нажмите кнопку ниже, чтобы сделать копию таблицы шаблона.
Обновите
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);
}
}