이 스크립트는 여러 계정에서 작동하도록 광고 실적 보고서를 확장합니다.
광고주는 캠페인의 광고 실적을 분석하고 싶어 합니다. 특정 광고 제목 또는 최종 URL의 실적을 다른 광고 제목이나 최종 URL과 비교하면 새 광고를 만들 때 통계를 얻을 수 있습니다. 이 스크립트는 이러한 유형의 분석에 사용할 수 있는 분포도가 포함된 Google 스프레드시트를 생성합니다.
스크립트를 실행할 때마다 새 광고 실적 보고서가 생성됩니다. Google Drive에서 이 모든 보고서에 액세스할 수 있습니다. 원하는 경우 스크립트는 보고서를 한 명 이상의 수신자에게 이메일로 보낼 수도 있습니다.
예약
스크립트는 지난 주의 통계를 사용하여 보고서를 생성합니다. 매주, 월요일에 실행되도록 예약합니다.
사용 방법
이 스크립트는 모든 그래프가 미리 구성된 템플릿 스프레드시트의 사본을 만들기 시작합니다. 그러면 스크립트가 보고서 시트에 데이터 값을 채우고 다른 시트의 그래프 그래프를 채웁니다.
설정
아래 버튼을 클릭하여 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);
}