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