This script extends Ad Performance Report to work for multiple accounts.
Advertisers like to analyze how their ads are performing in their campaigns. Comparing how a given headline or final URL performs against another sometimes provides insights when creating new ads. This script generates a Google Spreadsheet with distribution charts that can be used in this type of analysis.
A new Ad Performance report gets created whenever the script executes. You can access all of these reports in Google Drive. Optionally, the script can also email the report to one or more recipients.
Scheduling
The script uses last week's statistics to generate the report. Schedule it to run Weekly, on Mondays.
How it works
The script starts off creating a copy of a template spreadsheet, with all graphs pre-configured. The script then populates the data values in the Report sheet and graphs graphs in the other sheets.
Setup
Click the button below to create the spreadsheet-based script in your Google Ads account.
Click the button below to make a copy of the template spreadsheet.
Update
spreadsheet_urlin your script.Update
accountsin case you need reports only for a subset of accounts under your manager account.Update
recipient_emailsto specify your email preference.Schedule the script to run Weekly, on Mondays.
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 Manager Account Ad Performance Report
*
* @overview The Ad Performance Report generates a Google Spreadsheet that
* contains ad performance stats like Impressions, Cost, Click Through Rate,
* etc. as several distribution charts for an advertiser account. Visit
* https://developers.google.com/google-ads/scripts/docs/solutions/manager-ad-performance
* for more details.
*
* @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
*
* @version 2.3
*
* @changelog
* - version 2.3
* - Added discovery_carousel_ad and discovery_multi_asset_ad support
* - version 2.2
* - Removed deprecated ad_group_ad.ad.gmail_ad.marketing_image_headline field
* - version 2.1
* - Split into info, config, and code.
* - version 2.0
* - Updated to use new Google Ads scripts features.
* - version 1.0.1
* - Added validation for spreadsheet URL and email address.
* - version 1.0
* - Released initial version.
*/
/**
* Configuration to be used for the Ad Performance Report.
*/
CONFIG = {
// Array of recipient emails. Comment out to not send any emails.
'recipient_emails': ['YOUR_EMAIL_HERE'],
// URL of the default spreadsheet template. This should be a copy of
// https://docs.google.com/spreadsheets/d/1qWDfOhWBZpsWWDuMJ5W4Zm-zIY8z0wls56ngp8azM6o/copy
// Make sure the sheet is owned by or shared with same Google user executing the script
'spreadsheet_url': 'YOUR_SPREADSHEET_URL',
// If specific accounts should be used, add them here, for example:
// 'accounts' = ['123-456-7890', '234-567-8901', '345-678-9012'];
'accounts': [],
// The maximum number of accounts that Google Ads Scripts can process in
// parallel.
'max_accounts': 50,
};
// Comma-separated list of recipients. Comment out to not send any emails.
const RECIPIENT_EMAILS = CONFIG.recipient_emails;
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const ACCOUNTS = CONFIG.accounts;
const MAX_ACCOUNTS = CONFIG.max_accounts;
/**
* Entry-point for execution.
*/
function main() {
validateEmailAddresses(RECIPIENT_EMAILS);
let accountSelector = AdsManagerApp.accounts();
if (ACCOUNTS.length) {
accountSelector = accountSelector.withIds(ACCOUNTS);
}
accountSelector.withLimit(MAX_ACCOUNTS)
.executeInParallel('processAccount', 'processResults');
}
/**
* Defines a row created from the results of the AD_PERFORMANCE_REPORT query.
* @typedef {Object} ResultRow
* @property {number} impressions The number of impressions in the time period.
* @property {number} clicks The number of clicks in the time period.
* @property {number} cost The associated cost in the given period.
* @property {string} finalUrl The associated URL.
* @property {string} headline The headline of the Ad.
*/
/**
* Retrieves performance data for each enabled Ad in the account that has had
* impressions in the last week.
* @return {string} A stringified-set of results of form Array.<ResultRow>
*/
function processAccount() {
const AD_PERFORMANCE_REPORT_QUERY =
`SELECT metrics.impressions,
metrics.clicks,
metrics.cost_micros,
ad_group_ad.ad.final_urls,
ad_group_ad.ad.type,
ad_group_ad.ad.text_ad.headline,
ad_group_ad.ad.expanded_text_ad.headline_part1,
ad_group_ad.ad.expanded_text_ad.headline_part2,
ad_group_ad.ad.responsive_display_ad.long_headline,
ad_group_ad.ad.video_responsive_ad.long_headlines,
ad_group_ad.ad.responsive_search_ad.headlines,
ad_group_ad.ad.app_engagement_ad.headlines,
ad_group_ad.ad.app_ad.headlines,
ad_group_ad.ad.call_ad.headline1,
ad_group_ad.ad.call_ad.headline2,
ad_group_ad.ad.local_ad.headlines,
ad_group_ad.ad.legacy_responsive_display_ad.long_headline,
ad_group_ad.ad.shopping_comparison_listing_ad.headline,
ad_group_ad.ad.smart_campaign_ad.headlines,
ad_group_ad.ad.video_ad.in_feed.headline,
ad_group_ad.ad.discovery_multi_asset_ad.headlines,
ad_group_ad.ad.discovery_carousel_ad.headline
FROM ad_group_ad
WHERE ad_group_ad.status = "ENABLED"
AND ad_group.status = "ENABLED"
AND campaign.status = "ENABLED"
AND metrics.impressions > 0
AND segments.date DURING LAST_WEEK_MON_SUN`;
const ads = [];
const result = AdsApp.search(AD_PERFORMANCE_REPORT_QUERY);
while(result.hasNext()) {
const row = result.next();
let headline = '';
headline = getHeadline(row);
ads.push({
impressions: formatNumber(row.metrics.impressions),
clicks: formatNumber(row.metrics.clicks),
cost: formatNumber(row.metrics.costMicros)/1000000,
finalUrl: row.adGroupAd.ad.finalUrls,
headline: headline
});
}
return JSON.stringify(ads);
}
/**
* Constructing the headline depending on the Ad type
* @return {string} The headline of the Ad.
*/
function getHeadline(row) {
switch (row.adGroupAd.ad.type) {
case 'TEXT_AD':
return row.adGroupAd.ad.textAd.headline;
case 'EXPANDED_TEXT_AD':
return row.adGroupAd.ad.expandedTextAd.headlinePart1 + ' - ' +
row.adGroupAd.ad.expandedTextAd.headlinePart2;
case 'RESPONSIVE_DISPLAY_AD':
return row.adGroupAd.ad.responsiveDisplayAd.longHeadline.text;
case 'VIDEO_RESPONSIVE_AD':
return row.adGroupAd.ad.videoResponsiveAd.longHeadlines.map(
asset => asset.text);
case 'RESPONSIVE_SEARCH_AD':
return row.adGroupAd.ad.responsiveSearchAd.headlines.map(
asset => asset.text);
case 'APP_ENGAGEMENT_AD':
return row.adGroupAd.ad.appEngagementAd.headlines.map(asset => asset.text);
case 'APP_AD':
return row.adGroupAd.ad.appAd.headlines.map(asset => asset.text);
case 'CALL_AD':
return row.adGroupAd.ad.callAd.headline1 + ' - ' +
row.adGroupAd.ad.callAd.headline2;
case 'LEGACY_RESPONSIVE_DISPLAY_AD':
return row.adGroupAd.ad.legacyResponsiveDisplayAd.longHeadline;
case 'LOCAL_AD':
return row.adGroupAd.ad.localAd.headlines.map(asset => asset.text);
case 'SHOPPING_COMPARISON_LISTING_AD':
return row.adGroupAd.ad.shoppingComparisonListingAd.headline;
case 'SMART_CAMPAIGN_AD':
return row.adGroupAd.ad.smartCampaignAd.headlines.map(asset => asset.text);
case 'VIDEO_AD':
return row.adGroupAd.ad.videoAd.inFeed.headline;
case 'DISCOVERY_CAROUSEL_AD':
return adGroupAd.ad.discoveryCarouselAd.headline;
case 'DISCOVERY_MULTI_ASSET_AD':
return adGroupAd.ad.discoveryMultiAssetAd.headlines.map(asset => asset.text);
default:
return;
}
}
/**
* Combines the results of querying AD_PERFORMANCE_REPORT on each account,
* and writes the results to a newly-created spreadsheet which is emailed to
* the user.
* @param {!Array.<!AdsManagerApp.ExecutionResult>} executionResults
*/
function processResults(executionResults) {
let error = false;
const results = [];
for (const result of executionResults) {
if (result.getError()) {
error = true;
break;
}
const data = JSON.parse(result.getReturnValue());
Array.prototype.push.apply(results, data);
}
if (!error) {
const spreadsheet = createReport(results);
sendSuccessEmail(spreadsheet.getUrl());
} else {
sendFailureEmail(AdsApp.currentAccount().getCustomerId());
}
}
/**
* Creates a spreadsheet from the combined results from all accounts.
* @param {!Array.<!ResultRow>} results
* @return {!Spreadsheet}
*/
function createReport(results) {
const rowsByHeadline = groupArray(results, 'headline');
const rowsByFinalUrl = groupArray(results, 'finalUrl');
console.log(`Using template spreadsheet - ${SPREADSHEET_URL}`);
const spreadsheet = copySpreadsheet(SPREADSHEET_URL);
console.log(
`Generated new reporting spreadsheet ${spreadsheet.getUrl()} ` +
`based on the template spreadsheet. ` +
`The reporting data will be populated here.`);
writeToNamedRange(spreadsheet, 'headline_top_left', rowsByHeadline);
writeToNamedRange(spreadsheet, 'final_url_top_left', rowsByFinalUrl);
const customerId = AdsApp.currentAccount().getCustomerId();
writeToNamedRange(spreadsheet, 'account_id_headline', customerId);
writeToNamedRange(spreadsheet, 'account_id_final_url', customerId);
const today = getDateStringInTimeZone('MMM dd, yyyy');
writeToNamedRange(spreadsheet, 'headline_date', today);
writeToNamedRange(spreadsheet, 'final_url_date', today);
return spreadsheet;
}
/**
* Creates a copy of a specified spreadsheet.
* @param {string} spreadsheetUrl The URL of the spreadsheet to copy.
* @return {!Spreadsheet} The newly-created spreadsheet.
*/
function copySpreadsheet(spreadsheetUrl) {
const today = getDateStringInTimeZone('MMM dd, yyyy HH:mm:ss z');
const spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl)
.copy(`Ad Performance Report - ${today}`);
// Make sure the spreadsheet is using the account's timezone.
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
return spreadsheet;
}
/**
* Converts a string representation of a number to a number, removing commas.
* @param {string} numStr The number to convert.
* @return {number} The resulting number.
*/
function formatNumber(numStr) {
return parseFloat(numStr.replace(',', ''));
}
/**
* Extends a Sheet to meet the number of required rows, where necessary
* @param {!Sheet} sheet The Sheet object
* @param {number} requiredRows The number of rows that are required in total.
*/
function extendSheet(sheet, requiredRows) {
if (requiredRows > sheet.getMaxRows()) {
sheet.insertRowsAfter(
sheet.getMaxRows(), requiredRows - sheet.getMaxRows());
}
}
/**
* Writes either a value or a 2D array to a spreadsheet, starting at the cell
* specified top-left by a NamedRange.
* @param {!Spreadsheet} spreadsheet The spreadsheet to write to.
* @param {string} rangeName The name of the NamedRange to start at.
* @param {string|number|!Date|!Array.<!Array.<string|number|!Date>>} data The
* data to write, either:
* <ul>
* <li>A single value, which is written to the cell.</li>
* <li>A two-dimensional array, which is written starting at the cell.</li>
* </li>
*/
function writeToNamedRange(spreadsheet, rangeName, data) {
const namedRange = spreadsheet.getRangeByName(rangeName);
const sheet = namedRange.getSheet();
const col = namedRange.getColumn();
const row = namedRange.getRow();
if (Array.isArray(data)) {
// Write two-dimensional data
if (data.length && data[0].length) {
extendSheet(sheet, row + data.length - 1);
sheet.getRange(row, col, data.length, data[0].length).setValues(data);
}
} else if (data) {
// Write single value to the named range.
sheet.getRange(row, col).setValue(data);
}
}
/**
* Defines an aggregated row of data, for writing to the final spreadsheet.
* @typedef {Array} GroupedRow
* @property {string} 0 The value grouped by
* @property {number} 1 The total number of Ads.
* @property {number} 2 The total number of impressions.
* @property {number} 3 The total number of clicks.
* @property {number} 4 The click-through-rate (CTR).
* @property {number} 5 The total cost.
*/
/**
* Aggregates a 2D array of data around a given property.
* @param {!Array.<!ReportRow>} reportRows The data to aggregate
* @param {string} groupingKey The property name about which to aggregate.
* @return {!Array.<!GroupedRow>} The aggregated data
*/
function groupArray(reportRows, groupingKey) {
const rows = [];
const group = {};
for (const reportRow of reportRows) {
if (!group[reportRow[groupingKey]]) {
group[reportRow[groupingKey]] =
{numAds: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
}
const data = group[reportRow[groupingKey]];
data.numAds++;
data.totalImpressions += parseFloat(reportRow.impressions);
data.totalClicks += parseFloat(reportRow.clicks);
data.totalCost += parseFloat(reportRow.cost);
}
const groupedKeys = Object.keys(group);
for (let j = 0; j < groupedKeys.length; j++) {
let groupedRow = group[groupedKeys[j]];
const ctr = (groupedRow.totalClicks * 100) / groupedRow.totalImpressions;
rows.push([
groupedKeys[j], groupedRow.numAds, groupedRow.totalImpressions,
groupedRow.totalClicks, ctr, groupedRow.totalCost
]);
}
return rows;
}
/**
* 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=} opt_date A date object. Defaults to the current date.
* @param {string=} opt_timeZone A time zone. Defaults to the account time zone.
* @return {string} A formatted string of the given date in the given time zone.
*/
function getDateStringInTimeZone(format, opt_date, opt_timeZone) {
const date = opt_date || new Date();
const timeZone = opt_timeZone || AdsApp.currentAccount().getTimeZone();
return Utilities.formatDate(date, timeZone, format);
}
/**
* Sends an email to the user with the link to the spreadsheet.
*
* @param {string} url URL of the spreadsheet.
*/
function sendSuccessEmail(url) {
const footerStyle = 'color: #aaaaaa; font-style: italic;';
const scriptsLink = 'https://developers.google.com/google-ads/scripts/';
const subject = `Manager Account Ad Performance Report - ` +
`${getDateStringInTimeZone('MMM dd, yyyy')}`;
const htmlBody = `<html><body>
<p>Hello,</p>
<p>A Google Ads Script has run successfully and the output is
available here:
<ul><li><a href="${url}">
Manager Account Ad Performance Report</a></li></ul></p>
<p>Regards,</p>
<span style="${footerStyle}">This email was automatically
generated by <a href="${scriptsLink}">Google Ads Scripts</a>.
</span></body></html>`;
const body = 'Please enable HTML to view this report.';
const options = {htmlBody: htmlBody};
MailApp.sendEmail(RECIPIENT_EMAILS.join(','), subject, body, options);
}
/**
* Sends an email to the user notifying them of a failed execution.
*
* @param {string} mccId The ID of the Manager Account.
*/
function sendFailureEmail(mccId) {
const footerStyle = 'color: #aaaaaa; font-style: italic;';
const scriptsLink = 'https://developers.google.com/google-ads/scripts/';
const subject = `[Failure] Manager Account Ad Performance Report - ` +
`${getDateStringInTimeZone('MMM dd, yyyy')}`;
const htmlBody = `<html><body>
<p>Hello,</p>
<p>A Google Ads Script has run unsuccessfully for Manager Account:
${mccId}.</p>
<p>For further details on this error, please log into the account and
examine the execution logs</p>
<span style="${footerStyle}">This email was automatically
generated by <a href="${scriptsLink}">Google Ads Scripts</a>.
</span></body></html>`;
const body = 'Please enable HTML to view this email.';
const options = {htmlBody: htmlBody};
MailApp.sendEmail(RECIPIENT_EMAILS.join(','), subject, body, options);
}
/**
* Validates the provided email addresses to make sure it's not the default.
* Throws a descriptive error message if validation fails.
*
* @param {Array<string>} recipientEmails The list of email addresses.
* @throws {Error} If the list of email addresses is still the default
*/
function validateEmailAddresses(recipientEmails) {
if (recipientEmails && recipientEmails[0] == 'YOUR_EMAIL_HERE') {
throw new Error(
'Please either specify a valid email address or clear' +
' the recipient_emails field in Config.');
}
}
/**
* 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);
}