AI-generated Key Takeaways
-
The Ad Performance Report uses Google Ads scripts to analyze ad performance and generate a Google Spreadsheet with distribution charts.
-
A new report is created with each script execution, accessible in Google Drive, and can optionally be emailed to recipients.
-
The script should be scheduled weekly on Mondays, using statistics from the previous week.
-
Setup involves installing the script template, copying a template spreadsheet, updating configuration in the script, and scheduling its execution.
Ad Performance Report is an example of advanced reporting functionality provided by Google Ads scripts. Advertisers like to analyze how their ads are performing in their campaigns. Sometimes, comparing how a given headline or final URL performs against others will provide insight in creating new ads. Ad Performance Report generates a Google Spreadsheet with a number of interesting distribution charts.
A new Ad Performance report gets created whenever the script executes. You can access all of these reports in Google Drive . Optionally, the script can also email the report to one or more recipients.
Scheduling
The script uses last week's statistics to generate the report. Schedule it Weekly, on Mondays.
How it works
The script starts off creating a copy of a template spreadsheet , with all graphs pre-configured. The script then populates the data values in the Reportsheet, and graphs in the other sheets get constructed automatically.
Setup
-
Click the button to create the spreadsheet-based script in your Google Ads account.
-
Click the button to make a copy of the template spreadsheet.
-
Update
spreadsheet_urlandrecipient_emailsin your script. -
Schedule your script to run Weekly, on Mondays.
Source code
// 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
);
}
