This script extends the single account Declining Ad Groups Report script to run for multiple accounts under a manager account.
The definition of a declining ad group is the same as in the single account version:
- The ad group is ENABLED and belongs to an ENABLED campaign, which means it's serving.
- The ad group's Click-Through Rate (CTR) has been decreasing for three consecutive weeks.
You can also use your own criteria to select declining ad groups.
How it works
The report produces a multi-tabbed spreadsheet. For each account, a separate tab is created showing the declining account report for that account.
There is also a summary tab that gives some aggregate data across accounts. It shows the number of declining ad groups, the average drop in CTR, the average change in cost, and the total change in cost for each account.
An email address can be entered into the report summary tab to have an email sent each time the report runs. The email provides a summary of the accounts having the highest number of declining ad groups.
Scheduling
The script uses three weeks worth of statistics to generate the report, so you should schedule it to run Weekly.
Setup
- Set up a spreadsheet-based script with the source code below. Use the MCC Declining Ad Groups Report template spreadsheet
- Update
CONFIG.SPREADSHEET_URLin the code to reflect your spreadsheet. - Update
CONFIG.TIMEZONEto reflect your timezone. - [Optional] Update
CONFIG.SUMMARY_TAB_NAMEif you want to alter the name of the report summary tab. - Schedule the script to run Weekly.
Source code
// Copyright 2016, 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 Declining AdGroups
*
* @overview The MCC Declining AdGroups script fetches ad groups in advertiser
* accounts under an MCC account, whose performance is considered to be
* worsening. By default, ad groups whose Click Through Rate has been
* decreasing for three consecutive weeks is considered worsening. A more
* sophisticated measure of "worsening" may be developed if required.
* See https://developers.google.com/google-ads/scripts/docs/solutions/adsmanagerapp-declining-adgroups
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.0
*
* @changelog
* - version 2.0
* - Updated to use new Google Ads scripts features.
* - version 1.0.2
* - Added validation for spreadsheet URL.
* - version 1.0.1
* - Improvements to time zone handling.
* - version 1.0
* - Released initial version.
*/
const CONFIG = {
SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL',
TIMEZONE: 'EST', // Timezone code, such as 'PST', 'EST', 'UTC'
ACCOUNT_LABEL: 'High Spend Accounts', // For selecting accounts to process.
// Leave blank if choosing not to filter
SUMMARY_TAB_NAME: 'Report Summary',
FIRST_DATA_ROW: 7,
FIRST_DATA_COLUMN: 2
};
function main() {
debug('Begin processing of MCC declining Ad Groups');
let account;
const mccManager = new MccManager();
const spreadsheetManager = new SpreadsheetManager(mccManager);
// Iterate over accounts.
while (account = mccManager.getNextAccount()) {
debug(`Switching to account: ${account.getCustomerId()}`);
// For each account, compute the declining ad groups.
const rows = getDegradingAccountsFor(account);
debug(` ${rows.length} declining accounts found`);
// Insert rows into the sheet.
spreadsheetManager.insertRowsForTab(account.getCustomerId(), rows);
// Send the data to the MccManager to use for summary tab.
mccManager.saveAdgroupInfo(rows);
}
debug('All accounts computed. Generating summary ...');
// Numbers for the summary.
const sumTabRows = mccManager.getSummaryRows();
// Push summary numbers into summary tab.
spreadsheetManager.insertSummaryRows(sumTabRows);
debug('Summary complete.');
// Send email.
spreadsheetManager.sendEmail(sumTabRows);
debug('MCC declining Ad Groups complete');
}
/**
* Pulls out the declining ad groups for the account specified, and formats the
* information into rows for adding to the spreadsheet later.
*
* @param {!AdsApp.Account} account The account whose information is desired
* @return {!Array.<!Array>} A 2D matrix of data that has been extracted to fit
* the format of the Spreadsheet it will be inserted into.
*/
function getDegradingAccountsFor(account) {
const reportRows = [];
const util=new Util();
// Get recent adGroups.
const adGroupsIter = AdsApp.adGroups()
.withCondition("ad_group.status = 'ENABLED'")
.withCondition("campaign.status = 'ENABLED'")
.forDateRange('LAST_7_DAYS')
.orderBy('metrics.ctr ASC')
.withLimit(100)
.get();
// Find the declining ad groups using criteria specified,
// then return some useful information about them.
for (const adGroup of adGroupsIter) {
const statsThreeWeeksAgo = adGroup.getStatsFor(
util.threeWeeksAgo, util.twoWeeksAgo);
const statsTwoWeeksAgo = adGroup.getStatsFor(
util.twoWeeksAgo, util.oneWeekAgo);
const statsLastWeek = adGroup.getStatsFor(
util.oneWeekAgo,util.today);
// Week over week, the ad group is declining.
// You may change this criteria to suit your needs.
// Set this condition to always true to show all adgroups.
if (statsLastWeek.getCtr() < statsTwoWeeksAgo.getCtr() &&
statsTwoWeeksAgo.getCtr() < statsThreeWeeksAgo.getCtr()) {
// Gather data to return. Note that the data matches the fields in
// the spreadsheet.
reportRows.push([adGroup.getCampaign().getName(), adGroup.getName(),
statsLastWeek.getCtr(), statsLastWeek.getCost(),
statsTwoWeeksAgo.getCtr(), statsTwoWeeksAgo.getCost(),
statsThreeWeeksAgo.getCtr(), statsThreeWeeksAgo.getCost()]);
}
}
return reportRows;
}
/**
* Module that deals with fetching and iterating through multiple accounts.
* Also keeps track of info across accounts for summary at the end.
* Specifically, it currently supports next, current, mccAccount,
* save, and summarize.
*/
class MccManager {
constructor() {
this.accountIterator = null;
this.mccAccount = null;
this.currentAccount = null;
this.summary = [];
this.init();
}
/**
* Private one-time init function.
*/
init() {
const accountSelector = AdsManagerApp.accounts();
// Use this to limit the accounts that are being selected in the report.
if (CONFIG.ACCOUNT_LABEL) {
const labelIterator =
AdsApp.labels()
.withCondition(`label.name = '${CONFIG.ACCOUNT_LABEL}'`)
.get();
if (labelIterator.hasNext()) {
const label = labelIterator.next();
accountSelector.withCondition(
`customer_client.applied_labels CONTAINS ANY ` +
`('${label.getResourceName()}')`);
} else {
throw `No label with name ${CONFIG.ACCOUNT_LABEL} found.`;
}
}
accountSelector.withLimit(50);
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() {
// Make sure we have an iterator to call against.
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;
}
/**
* Computes the sum of the difference between 2 columns, for all rows.
*
* @param {!Array.<?Array>} rows
* @param {number} colA The 0-based index of the first column
* @param {number} colB The 0-based index of the second column
* @return {number} The sum of the differences across all the rows computed
*/
computeTotalChange(rows, colA, colB) {
let sum = 0;
rows.forEach(function(r) {
sum += r[colA] - r[colB];
});
return sum;
}
/**
* Computes and saves the info for the account's adgroups,
* in preparation for the summary. The data is unsorted at this stage.
*
* @param {!Array.<?Array>} rows The data to be added to the spreadsheet
*/
saveAdgroupInfo(rows) {
if (rows.length == 0) return;
summaryRow = [
getCurrentAccount().getCustomerId(),
rows.length, // The number of decreasing adgroups.
this.computeTotalChange(rows, 2, 6) / (rows.length),
// avg(CTR last week - CTR last week 3 weeks ago)
this.computeTotalChange(rows, 3, 7) / (rows.length),
// avg(Cost last week - Cost 3 weeks ago)
this.computeTotalChange(rows, 3, 7)
// Total cost change per account.
];
this.summary.push(summaryRow);
}
/**
* Sorts and returns the summary rows for use on the summary tab.
*
* @return {!Array.<!Array>} The sorted order of the summary rows,
* by the column at index 1, where the number of declining accounts
* per account is recorded
*/
getSummaryRows() {
// Sort the rows.
this.summary.sort(function(a, b) {
return b[1] - a[1]; // Sort descending order is desired.
});
return this.summary;
}
}
/**
* Module for the creation and selection of tabs in the spreadsheet,
* as well as the formatted insertion of data, and the sending of email.
*/
class SpreadsheetManager {
/**
* constructor includes
* insertRowsForTab, insertSummaryRows, setValueForCell, sendEmail
*/
constructor(mccManager) {
this.spreadsheetTabs = []; // List of sheet names.
this.spreadsheet = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);
this.spreadsheet.setSpreadsheetTimeZone(
AdsApp.currentAccount().getTimeZone());
console.log(`Using spreadsheet - ${CONFIG.SPREADSHEET_URL}`);
this.currentTab = null;
this.templateSpreadsheet = this.spreadsheet
.getSheetByName('Report Template');
this.sheetOptions = { template: this.templateSpreadsheet};
this.mccManager = mccManager;
}
/**
* Does basic set up for a given tab, to prepare for data insertion.
*
* @param {!Sheet} tab The tab to setup
* @return {!Object} returns the tab.
*/
setupTab(tab) {
this.currentTab = tab;
// Set the date, clear the rows of any data.
this.currentTab.getRange(1, 2, 1, 1).setValue('Date');
this.currentTab.getRange(1, 3, 1, 1).setValue(new Date());
this.currentTab.getRange(7, 1, this.currentTab.getMaxRows() - 7,
this.currentTab.getMaxColumns()).clear();
// Set account_id cell.
const cellName = this.currentTab.getSheetName() + '!account_id';
this.setValueForCell(cellName, this.mccManager.getCurrentAccount());
return tab;
}
/**
* Sets the value for a given cellname with the value provided.
*
* @param {string} cellName The name of the cell to set
* @param {string} value The value to place into the cell
*/
setValueForCell(cellName, value) {
const cell = this.spreadsheet.getRangeByName(cellName);
if (cell) {
cell.setValue(value);
}
else {
debug(`WARNING: cell not found: ${cellName}`);
}
}
/**
* Applies format, starting at row CONFIG.FIRST_DATA_ROW,
* for the next rowCount rows, in the columns marked by columnArray.
*
* @param {number} rowCount number of rows to format
* @param {!Array.<number>} columnArray Array of columns to apply
* formatting
* @param {string} format Format to apply to those cells
*/
setRowFormatting(rowCount, columnArray, format) {
columnArray.forEach(function(col) {
this.currentTab.
getRange(CONFIG.FIRST_DATA_ROW, col, rowCount, 1).
setNumberFormat(format);
});
}
/**
* Always returns the tab requested by name.
* If it does not exist, it will create one.
*
* @param {string} tabName : name of tab
* @return {Sheet} currentTab: the tab requested.
*/
getTabByName(tabName) {
if (this.currentTab && this.currentTab.getName() == tabName) {
return this.currentTab;
}
// Return a reference to the tab.
this.currentTab = this.spreadsheet.getSheetByName(tabName);
// If it doesn't exist, create it.
if (this.currentTab == null) {
this.currentTab = this.spreadsheet.insertSheet(
tabName, this.sheetOptions);
}
return this.currentTab;
}
/**
* Inserts the rows of data into the appropriate tab, and formats them.
*
* @param {string} tabName : name of the tab to add rows
* @param {!Array.<!Array>} rows : array of arrays for spreadsheet
*/
insertRowsForTab(tabName, rows) {
// Get the tab and clean it up. Do this regardless of data size.
this.setupTab(this.getTabByName(tabName));
if (rows.length == 0)
return;
// Add data, and formatting.
this.setRowFormatting(rows.length, [4, 6, 8], '#0.00%');
this.setRowFormatting(rows.length, [5, 7, 9], '#,##0.00');
this.currentTab.getRange(CONFIG.FIRST_DATA_ROW, CONFIG.FIRST_DATA_COLUMN,
rows.length, rows[0].length).setValues(rows);
return;
}
/**
* Inserts the summary data into the summary tab, and formats them.
*
* @param {!Array.<!Array>} rows : array of arrays for spreadsheet
*/
insertSummaryRows(rows) {
this.getTabByName(CONFIG.SUMMARY_TAB_NAME);
// Set the date, clear the rows of any data.
this.currentTab.getRange(1, 2, 1, 1).setValue('Summary Date');
this.currentTab.getRange(1, 3, 1, 1).setValue(new Date());
this.currentTab.getRange(7, 1, this.currentTab.getMaxRows() - 7,
this.currentTab.getMaxColumns()).clear();
// Put the mcc account # in the summary tab.
const cellName = this.currentTab.getSheetName() + '!account_id';
this.setValueForCell(cellName, this.mccManager.getMccAccount());
if (rows.length == 0)
return;
// Load the data in, and format it.
this.setRowFormatting(rows.length, [4], '#0.00%');
this.setRowFormatting(rows.length, [5, 6], '#,##0.00');
this.currentTab.getRange(CONFIG.FIRST_DATA_ROW, CONFIG.FIRST_DATA_COLUMN,
rows.length, rows[0].length).setValues(rows);
return;
}
/**
* Sends email if an email was provided on the summary tab.
* Otherwise does nothing.
*
* @param {!Array.<!Array>} reportRows the rows to be sent in the email
*
*/
sendEmail(reportRows) {
this.getTabByName(CONFIG.SUMMARY_TAB_NAME);
const rangeName = this.currentTab.getSheetName() + '!email';
const email = this.spreadsheet.getRangeByName(rangeName).getValue();
if (!email || email == 'foo@example.com') {
debug('no email sent');
return; // No address, do nothing.
}
debug(`sending email to ${email}`);
const body = [];
body.push('The Ctr of some of the adgroups in the following accounts' +
' is declining over the last three weeks.\n' +
'The number of declining adgroups is shown below.\n');
body.push(`Full report at ${CONFIG.SPREADSHEET_URL} '\n\n`);
body.push('Account Id => Number of declining ad groups');
for (const reportRow of reportRows) {
body.push(reportRow[0] + ' => ' + reportRow[1]);
}
MailApp.sendEmail(email, `${reportRows.length}` +
` Google Ads accounts have ad groups that` +
` are declining, in Google Ads MCC account` +
` ${mccManager.getMccAccount}`,
body.join('\n'));
}
}
/**
* Utilities that are useful for dealing with dates.
* The properties include today, oneWeekAgo, twoWeeksAgo,
* and threeWeeksAgo.
*/
class Util {
constructor(){
// Returns YYYYMMDD-formatted date.
this.getDateStringInPast = function(numDays, date) {
this.date = date || new Date();
const MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
const past = new Date(this.date.getTime() - numDays * MILLIS_PER_DAY);
return this.getDateStringInTimeZone('yyyyMMdd', past);
};
this.getDateStringInTimeZone = function(format, date, timeZone) {
date = date || new Date();
timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
return Utilities.formatDate(date, timeZone, format);
};
// Compute once, ahead of time, and just return the value directly.
this.today = this.getDateStringInPast(0);
this.oneWeekAgo = this.getDateStringInPast(7);
this.twoWeeksAgo = this.getDateStringInPast(14);
this.threeWeeksAgo = this.getDateStringInPast(21);
}
}
/**
* Wrapper for Logger.log.
*
* @param {string} t The text to log
*/
function debug(t) {
console.log(t);
}
/**
* Validates the provided spreadsheet URL
* to make sure that it's 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 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.');
}
return SpreadsheetApp.openByUrl(spreadsheeturl);
}