Tập lệnh này mở rộng Báo cáo hiệu suất quảng cáo để hoạt động cho nhiều tài khoản.
Các nhà quảng cáo muốn phân tích hiệu suất của quảng cáo trong chiến dịch của họ. Việc so sánh hiệu suất của một dòng tiêu đề hoặc URL cuối cùng nhất định với một URL khác đôi khi sẽ cung cấp thông tin chi tiết khi tạo quảng cáo mới. Tập lệnh này sẽ tạo một Bảng tính Google chứa các biểu đồ phân phối có thể sử dụng trong loại phân tích này.
Báo cáo Hiệu suất quảng cáo mới sẽ được tạo bất cứ khi nào tập lệnh thực thi. Bạn có thể truy cập tất cả báo cáo này trong Google Drive. Tập lệnh cũng có thể gửi báo cáo qua email đến một hoặc nhiều người nhận (không bắt buộc).
Lập lịch
Tập lệnh sử dụng số liệu thống kê của tuần trước để tạo báo cáo. Hãy lên lịch để chiến dịch đó chạy Hằng tuần, vào thứ Hai.
Cách hoạt động
Tập lệnh bắt đầu tạo bản sao của bảng tính mẫu, với tất cả các biểu đồ được định cấu hình sẵn. Sau đó, tập lệnh sẽ điền các giá trị dữ liệu vào trang tính Báo cáo và biểu đồ dạng đồ thị trong các trang tính khác.
Thiết lập
Hãy nhấp vào nút bên dưới để tạo tập lệnh dựa trên bảng tính trong tài khoản Google Ads.
Nhấp vào nút bên dưới để tạo bản sao của bảng tính mẫu.
Cập nhật
spreadsheet_urltrong tập lệnh của bạn.Hãy cập nhật
accountstrong trường hợp bạn chỉ cần báo cáo cho một nhóm nhỏ các tài khoản trong tài khoản người quản lý của mình.Cập nhật
recipient_emailsđể chỉ định lựa chọn ưu tiên về email.Lên lịch để tập lệnh chạy Hằng tuần, vào thứ Hai.
Mã nguồn
// 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);
}