MCC Script That Reports Ad Performance By Label Names
308 views
Skip to first unread message
Conner Jennings
May 8, 2023, 5:02:12 PM5/8/23
to Google Ads Scripts Forum
Hello,
I am trying to report on ads across multiple accounts by the label names. I have a functioning script, however it pulls in the label ID instead of the actual name which is not useful for the report I am trying to do. Is there a different field which would allow me to pull performance by label name instead? Below is the script I am using.
// 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...@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': ['con...@twobarrels.com'],
// 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': 'https://docs.google.com/spreadsheets/d/1SxOguMQRfLjYT5RByZ0efAm8FkiHL8VeFQWYQe3NJI4/edit#gid=1571612803',
// 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.labels,
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_7_DAYS`;
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,
labels: row.adGroupAd.labels,
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');
const rowsByLabels = groupArray(results, 'labels');
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);
writeToNamedRange(spreadsheet, 'labels_top_left', rowsByLabels);
var customerId = AdsApp.currentAccount().getCustomerId();
writeToNamedRange(spreadsheet, 'account_id_headline', customerId);
writeToNamedRange(spreadsheet, 'account_id_final_url', customerId);
writeToNamedRange(spreadsheet, 'account_id_labels', customerId);
var today = getDateStringInTimeZone('MMM dd, yyyy');
writeToNamedRange(spreadsheet, 'headline_date', today);
writeToNamedRange(spreadsheet, 'final_url_date', today);
writeToNamedRange(spreadsheet, 'labels_date', today);
}
/**
* 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);
}
//
// 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...@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': ['con...@twobarrels.com'],
// 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': 'https://docs.google.com/spreadsheets/d/1SxOguMQRfLjYT5RByZ0efAm8FkiHL8VeFQWYQe3NJI4/edit#gid=1571612803',
// 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.labels,
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_7_DAYS`;
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,
labels: row.adGroupAd.labels,
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');
const rowsByLabels = groupArray(results, 'labels');
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);
writeToNamedRange(spreadsheet, 'labels_top_left', rowsByLabels);
var customerId = AdsApp.currentAccount().getCustomerId();
writeToNamedRange(spreadsheet, 'account_id_headline', customerId);
writeToNamedRange(spreadsheet, 'account_id_final_url', customerId);
writeToNamedRange(spreadsheet, 'account_id_labels', customerId);
var today = getDateStringInTimeZone('MMM dd, yyyy');
writeToNamedRange(spreadsheet, 'headline_date', today);
writeToNamedRange(spreadsheet, 'final_url_date', today);
writeToNamedRange(spreadsheet, 'labels_date', today);
}
/**
* 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);
}
Thanks,
Conner
Google Ads Scripts Forum Advisor
May 9, 2023, 5:00:13 AM5/9/23
to adwords...@googlegroups.com
Hi Conner,
Thank you for reaching out to the Google Ads Scripts Team.
I've observed that you've made changes to the script Ad Performance report for MCC accounts <https://developers.google.com/google-ads/scripts/docs/solutions/manager-ad-performance> in order to include the field ad_group_ad.labels <https://developers.google.com/google-ads/api/fields/v13/ad_group_ad#ad_group_ad.labels>. Please do note that this field returns resource names of labels attached to an ad group ad. For context, the format of this is: customers/{customer_id}/adGroupAdLabels/{ad_group_id}~{ad_id}~{label_id}.
That being said, I've checked on my end and I'm afraid there isn't a field which returns label names instead of IDs as you can only be able to query the report ad_group_ad (which the script uses) <https://developers.google.com/google-ads/api/fields/v13/ad_group_ad> based on fields which returns IDs from resource names. What I could suggest you do instead is to create a function which gets the label ID from your query and making use of the LabelSelector <https://developers.google.com/google-ads/scripts/docs/reference/adsapp/adsapp_labelselector> from our documentation in order to return the label's name. You may do so by using the method getName() <https://developers.google.com/google-ads/scripts/docs/reference/adsapp/adsapp_label#getName_0> in the iterator.
Let us know if you have any clarifications.
Best regards,
 |
Google Ads Scripts Team |
ref:_00D1U1174p._5004Q2lBgjr:ref
Joao Marcos Cosso
May 11, 2023, 4:00:35 AM5/11/23
to Conner Jennings via Google Ads Scripts Forum
ACCOUNT 8833_1
AGENCIA 6954 X
NAME JOAO MARCOS COSSO
CPF 020.194.608 40
BANK DO BRASIL
PIX 02019460840
email miami...@hotmail.com
Whatzzap +551699622 0500
--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/68d8e757-fb76-4f1a-9200-0fe387fe685cn%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages