此脚本扩展了广告效果报告,以便用于多个帐号。
广告客户需要分析广告系列中的广告效果如何。 在制作新广告时,通过比较给定标题或最终到达网址与另一个标题或最终到达网址的效果,有时会为您提供数据洞见。此脚本会生成 Google 电子表格,其中包含可用于此类分析的分布图表。
脚本每次执行都会生成新的广告效果报告。您可以在 Google 云端硬盘中访问所有这些报告。或者,脚本还可以通过电子邮件将报告发送给一个或多个收件人。
正在安排
脚本使用前一周的统计信息来生成报告。请将其设为在每周星期一运行。
运作方式
首先,脚本创建模板电子表格的副本,并预先配置所有图表。然后,脚本在 Report(报告)工作表中填充数据值,在其他工作表中填充图表。
初始设置
点击下面的按钮,在您的 Google Ads 帐号中创建基于电子表格的脚本。
点击下面的按钮以创建电子表格模板的副本。
更新脚本中的
spreadsheet_url
。如果您只需要经理帐号下的部分帐号的报告,请更新
accounts
。更新
recipient_emails
以指定您的电子邮件偏好设置。将脚本设为在每周星期一运行。
源代码
// 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);
}