「廣告成效報表」是進階報表功能 是由 Google Ads 指令碼所提供廣告客戶喜歡分析自家廣告的成效 獲得一定成效有時候，比較特定廣告標題 最終到達網址的運作成效與其他網址的成效比較，可讓您深入瞭解如何製作新廣告。廣告 成效報表會產生 Google 試算表，其中列出一些有趣的資訊 分佈圖
每當系統執行指令碼時，就會建立新的廣告成效報表。你可以 存取 Google 雲端硬碟中的所有報告。(選用) 此外，指令碼也可以透過電子郵件將報表寄給一或多位收件者。
排程
指令碼使用上週的統計資料來產生報表，安排發布時間 每週一。
運作方式
指令碼會從建立範本副本的開始 試算表、 預先設定所有圖表接著指令碼會將資料值填入 並建構出「Report」工作表，以及其他工作表中的圖表 。
設定
按一下下方按鈕，即可在 Google 建立試算表的指令碼 Google Ads 帳戶。
請點選下方按鈕來複製範本試算表。
更新指令碼中的
spreadsheet_url和recipient_emails。排定指令碼於每週一執行。
原始碼
// Copyright 2015, 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 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. See
* https://developers.google.com/google-ads/scripts/docs/solutions/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.1
* - Updated to use expanded text ads.
* - version 1.0.1
* - Improvements to time zone handling.
* - 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://goo.gl/aN49Nk
'spreadsheet_url': 'YOUR_SPREADSHEET_URL',
'advanced_options': {
/**
* Adding new metrics to the list will not get them automatically included
* unless corresponding changes are made in the spreadsheet and the code
* section.
* Removing fields in the list will result in the corresponding
* field not being rendered in the report.
*/
'fields': [
'ad_group_ad.ad.id',
'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.final_urls',
'ad_group_ad.ad.discovery_multi_asset_ad.headlines',
'ad_group_ad.ad.discovery_carousel_ad.headline',
'metrics.clicks',
'metrics.cost_micros',
'metrics.impressions',
]
}
};
const RECIPIENT_EMAILS = CONFIG.recipient_emails;
const SPREADSHEET_URL = CONFIG.spreadsheet_url;
const FIELDS = CONFIG.advanced_options.fields;
/**
* This script computes an Ad performance report
* and outputs it to a Google spreadsheet.
*/
function main() {
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.`);
const headlineSheet = spreadsheet.getSheetByName('Headline');
headlineSheet.getRange(1, 2, 1, 1).setValue('Date');
headlineSheet.getRange(1, 3, 1, 1).setValue(new Date());
const finalUrlSheet = spreadsheet.getSheetByName('Final Url');
finalUrlSheet.getRange(1, 2, 1, 1).setValue('Date');
finalUrlSheet.getRange(1, 3, 1, 1).setValue(new Date());
spreadsheet.getRangeByName('account_id_headline').setValue(
AdsApp.currentAccount().getCustomerId());
spreadsheet.getRangeByName('account_id_final_url').setValue(
AdsApp.currentAccount().getCustomerId());
// Only include ad types on the headline sheet for which the concept of a
// headline makes sense.
outputSegmentation(headlineSheet, 'Headline', (adGroupAd) => {
switch (adGroupAd.ad.type) {
case 'TEXT_AD':
return adGroupAd.ad.textAd.headline;
case 'EXPANDED_TEXT_AD':
return adGroupAd.ad.expandedTextAd.headlinePart1 + ' - ' +
adGroupAd.ad.expandedTextAd.headlinePart2;
case 'RESPONSIVE_DISPLAY_AD':
return adGroupAd.ad.responsiveDisplayAd.longHeadline.text;
case 'VIDEO_RESPONSIVE_AD':
return adGroupAd.ad.videoResponsiveAd.longHeadlines.map(
asset => asset.text);
case 'RESPONSIVE_SEARCH_AD':
return adGroupAd.ad.responsiveSearchAd.headlines.map(
asset => asset.text);
case 'APP_ENGAGEMENT_AD':
return adGroupAd.ad.appEngagementAd.headlines.map(asset => asset.text);
case 'APP_AD':
return adGroupAd.ad.appAd.headlines.map(asset => asset.text);
case 'CALL_AD':
return adGroupAd.ad.callAd.headline1 + ' - ' +
adGroupAd.ad.callAd.headline2;
case 'LEGACY_RESPONSIVE_DISPLAY_AD':
return adGroupAd.ad.legacyResponsiveDisplayAd.longHeadline;
case 'LOCAL_AD':
return adGroupAd.ad.localAd.headlines.map(asset => asset.text);
case 'SHOPPING_COMPARISON_LISTING_AD':
return adGroupAd.ad.shoppingComparisonListingAd.headline;
case 'SMART_CAMPAIGN_AD':
return adGroupAd.ad.smartCampaignAd.headlines.map(asset => asset.text);
case 'VIDEO_AD':
return adGroupAd.ad.videoAd.inFeed.headline;
case 'DISCOVERY_CAROUSEL_AD':
return adGroupAd.ad.discoveryCarouselAd.headline.text;
case 'DISCOVERY_MULTI_ASSET_AD':
return adGroupAd.ad.discoveryMultiAssetAd.headlines.map(asset => asset.text);
default:
return;
}
});
outputSegmentation(
finalUrlSheet, 'Final Url', (adGroupAd) => adGroupAd.ad.finalUrls);
console.log(`Ad performance report available at\n${spreadsheet.getUrl()}`);
validateEmailAddresses(RECIPIENT_EMAILS);
MailApp.sendEmail(
RECIPIENT_EMAILS.join(','), 'Ad Performance Report is ready',
spreadsheet.getUrl());
}
/**
* Retrieves the spreadsheet identified by the URL.
*
* @param {string} spreadsheetUrl The URL of the spreadsheet.
* @return {SpreadSheet} The spreadsheet.
*/
function copySpreadsheet(spreadsheetUrl) {
const spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl).copy(
'Ad Performance Report - ' +
getDateStringInTimeZone('MMM dd, yyyy HH:mm:ss z'));
// Make sure the spreadsheet is using the account's timezone.
spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
return spreadsheet;
}
/**
* Generates statistical data for this segment.
*
* @param {Sheet} sheet Sheet to write to.
* @param {string} segmentName The Name of this segment for the header row.
* @param {function(AdsApp.Ad): string} segmentFunc Function that returns
* a string used to segment the results by.
*/
function outputSegmentation(sheet, segmentName, segmentFunc) {
// Output header row.
const rows = [];
const header = [
segmentName,
'Num Ads',
'Impressions',
'Clicks',
'CTR (%)',
'Cost'
];
rows.push(header);
const segmentMap = {};
// Compute data.
const fields = FIELDS.join(",");
const results = AdsApp.search(`SELECT ${fields} FROM ad_group_ad ` +
`WHERE metrics.impressions > 0 AND ` +
`segments.date DURING LAST_7_DAYS`);
let skipped = 0;
for (const row of results) {
let rawSegments = segmentFunc(row.adGroupAd);
// In the case of the headline segmentation segmentFunc will return null
// where there is no headline e.g. an HTML5 ad or other non-text ad, for
// which metrics are therefore not aggregated.
if (!rawSegments) {
skipped += 1;
continue;
}
let segments = [];
if (typeof (rawSegments) == 'string') {
segments[0] = rawSegments;
}
else {
segments = rawSegments;
}
for (const segment of segments) {
if (!segmentMap[segment]) {
segmentMap[segment] =
{numAds: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
}
const data = segmentMap[segment];
data.numAds++;
data.totalImpressions += parseFloat(row.metrics.impressions);
data.totalClicks += parseFloat(row.metrics.clicks);
data.totalCost += parseFloat((row.metrics.costMicros)/1000000);
}
}
// Write data to our rows.
for (const key in segmentMap) {
if (segmentMap.hasOwnProperty(key)) {
let ctr = 0;
if (segmentMap[key].numAds > 0) {
ctr = (segmentMap[key].totalClicks /
segmentMap[key].totalImpressions) * 100;
}
const row = [
key,
segmentMap[key].numAds,
segmentMap[key].totalImpressions,
segmentMap[key].totalClicks,
ctr.toFixed(2),
segmentMap[key].totalCost];
rows.push(row);
}
}
// Write a warning if we skipped ads that were missing segmentation info
if (skipped) {
rows.push(['SKIPPED', skipped, '', '', '', '']);
}
sheet.getRange(3, 2, rows.length, 6).setValues(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} date A date object. Defaults to the current date.
* @param {string} timeZone A time zone. Defaults to the account's time zone.
* @return {string} A formatted string of the given date in the given time zone.
*/
function getDateStringInTimeZone(format, date, timeZone) {
date = date || new Date();
timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
return Utilities.formatDate(date, timeZone, format);
}
/**
* 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 spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
}