Google Ads script: Uncovered PMax Asset Performance

Uncovered PMax Asset Performance.js

/**
 * Uncovered PMax Assets Performance Script
 * 
 * This script retrieves and processes asset performance data from Google Ads Performance Max campaigns.
 * It performs the following actions:
 *   1. Fetches asset details and performance metrics using multiple queries to the Google Ads API.
 *   2. Consolidates asset data from asset groups, campaign associations, and customer asset associations.
 *   3. Formats the retrieved data and writes it to a designated Google Sheet.
 *
 * Google Ads script for analyzing Performance Max asset performance.
 *
 * Created by Dmytro Tonkikh, dmytro@trueclicks.com
 */


// Make a copy of this Google Spreadsheet: https://docs.google.com/spreadsheets/d/1jEDZwO3XFe-PKMo2qEZE51GSSIUAFcyuSj3Y6fJkcwQ/copy
// Paste URL of recently copied spreadsheet instead of YOUR_SPREADSHEET_URL
var ss = SpreadsheetApp.openByUrl('YOUR_SPREADSHEET_URL');

var assets = {};

function main() {

    getAssets();

    var values = [];
    var headers = ['campaign', 'channel type', 'asset', 'type', 'clicks', 'impressions', 'cost', 'conversions', 'conversionsValue', 'videoViews', 'averageCpv', 'videoCost'];
    var assetsGroupAssets = getAssetGroupAssets();
    var data = getAssetMetrics();
    var campaignLevelAssets = getCampaignAssosiationAssets();
    var extensionAssets = getCustomerAssosiationCampaignAssets(campaignLevelAssets);

    for (var i in data) {

        if (assetsGroupAssets[i]) {

            data[i] = completeAssign(data[i], assetsGroupAssets[i]);

            let item = data[i];
            var name;

            if (item.asset.type == 'TEXT') {
                name = item.asset.textAsset.text;
            }
            if (item.asset.type == 'YOUTUBE_VIDEO') {
                name = item.asset.youtubeVideoAsset.youtubeVideoTitle;
            }

            if (item.asset.type == 'IMAGE') {

                if (!item.asset.name) {
                    name = item.asset.type;
                } else {
                    name = item.asset.name;
                }
            }

            values.push([item.campaign.name,
                item.campaign.advertisingChannelType,
                name,
                item.assetGroupAsset.fieldType,
                item.metrics.clicks,
                item.metrics.impressions,
                item.metrics.costMicros,
                item.metrics.conversions,
                item.metrics.conversionsValue,
                item.metrics.videoViews,
                item.metrics.averageCpv,
                item.metrics.videoCost
            ])


        } else if (extensionAssets[i]) {


            var item = completeAssign(data[i], extensionAssets[i]);

            item.asset = Object.assign(item.asset, assets[item.asset.resourceName].asset);

            var name;

            if (item.asset.type == 'SITELINK') {
                name = item.asset.sitelinkAsset.linkText
            }
            if (item.asset.type == 'IMAGE') {
                if (!item.asset.name) {
                    name = item.asset.type
                } else {
                    name = item.asset.name
                }
            }
            if (item.asset.type == 'PROMOTION') {
                name = item.asset.promotionAsset.occasion
            }

            if (item.asset.type == 'MOBILE_APP') {
                name = item.asset.mobileAppAsset.linkText
            }

            if (item.asset.type == 'CALLOUT') {
                name = item.asset.calloutAsset.calloutText
            }
            if (item.asset.type == 'STRUCTURED_SNIPPET') {
                name = item.asset.structuredSnippetAsset.values.join(", ")
            }

            values.push([item.campaign.name,
                item.campaign.advertisingChannelType,
                name,
                item.asset.type,
                item.metrics.clicks,
                item.metrics.impressions,
                item.metrics.costMicros,
                item.metrics.conversions,
                item.metrics.conversionsValue,
                item.metrics.videoViews,
                item.metrics.averageCpv,
                item.metrics.videoCost
            ])
        } else {

            var rn = data[i].segments.assetInteractionTarget.asset;

            if (assets[rn]) {
                var item = completeAssign(data[i], assets[rn]);
                var name;
                if (item.asset.type == 'TEXT') {
                    name = item.asset.textAsset.text
                }
                if (item.asset.type == 'CALLOUT') {
                    name = item.asset.calloutAsset.calloutText
                }
                values.push([item.campaign.name,
                    item.campaign.advertisingChannelType,
                    name,
                    item.asset.type,
                    item.metrics.clicks,
                    item.metrics.impressions,
                    item.metrics.costMicros,
                    item.metrics.conversions,
                    item.metrics.conversionValue,
                    item.metrics.videoViews,
                    item.metrics.averageCpv,
                    item.metrics.videoCost
                ])
            } else {
                console.log(data[i]);
            }
        }
    }


    if (values.length > 0) {
        values.unshift(headers)
        ss.getSheetByName('data').clear().getRange(1, 1, values.length, values[0].length).setValues(values);
    }

}

/**
 * Retrieves asset metrics for Performance Max campaigns from the Google Ads API.
 *
 * Executes a query that gathers metrics such as clicks, cost, impressions, and video views.
 * Converts cost and video metrics to human-readable formats.
 *
 * @returns {Object} An object mapping composite asset identifiers (campaign.id#asset) to metric data.
 */


function getAssetMetrics() {

    var retVal = {};

    var query = `SELECT campaign.id, campaign.name, campaign.advertising_channel_type, segments.asset_interaction_target.asset, segments.asset_interaction_target.interaction_on_this_asset, metrics.clicks, metrics.conversions, metrics.cost_micros, metrics.conversions_value, metrics.impressions, metrics.video_views, metrics.average_cpv 
               FROM campaign 
                WHERE metrics.impressions > 0 
                AND campaign.advertising_channel_type = 'PERFORMANCE_MAX'
                AND segments.asset_interaction_target.interaction_on_this_asset = FALSE
                AND segments.date DURING LAST_30_DAYS`;

    var report = AdsApp.search(query);

    while (report.hasNext()) {

        var row = report.next();
        row.metrics.costMicros = parseFloat(row.metrics.costMicros / 1000000).toFixed(2);

        if (row.metrics.videoViews > 0) {
            row.metrics.averageCpv = parseFloat(row.metrics.averageCpv / 1000000).toFixed(3);
            row.metrics.videoCost = row.metrics.averageCpv * row.metrics.videoViews
        }
        var id = [row.campaign.id, row.segments.assetInteractionTarget.asset].join('#');

        retVal[id] = row;

    }
    return retVal;
}

/**
 * Retrieves asset group assets for enabled asset groups, campaigns, and assets from the Google Ads API.
 *
 * Executes a query that gathers asset group details and returns them keyed by a composite identifier.
 *
 * @returns {Object} An object mapping composite asset identifiers (campaign.id#asset) to asset group asset data.
 */


function getAssetGroupAssets() {

    var retVal = {};

    var query = `SELECT campaign.name, campaign.id, campaign.advertising_channel_type, asset_group_asset.field_type, asset_group_asset.asset, asset.source, asset_group.id, asset_group.name, asset.id, asset.type, asset.name, asset.image_asset.full_size.url, asset.youtube_video_asset.youtube_video_id, asset.youtube_video_asset.youtube_video_title, asset.text_asset.text, asset.call_to_action_asset.call_to_action, campaign.advertising_channel_type 
               FROM asset_group_asset 
                WHERE asset_group_asset.status = 'ENABLED' 
                AND asset_group.status = 'ENABLED' 
                AND campaign.status = 'ENABLED' 
                AND campaign.serving_status = 'SERVING'`;

    var report = AdsApp.search(query);

    while (report.hasNext()) {

        var row = report.next();
        var id = [row.campaign.id, row.assetGroupAsset.asset].join('#');

        retVal[id] = row;

    }

    return retVal;

}

/**
 * Retrieves all asset details from the Google Ads API and populates the global 'assets' object.
 *
 * Executes a query that gathers asset information and stores each asset in the global 'assets' variable,
 * keyed by the asset resource name.
 */

function getAssets() {


    var query = `SELECT asset.type, asset.source, asset.sitelink_asset.link_text, asset.name, asset.id, asset.resource_name, asset.structured_snippet_asset.values, asset.structured_snippet_asset.header, asset.callout_asset.callout_text, asset.price_asset.price_offerings, asset.text_asset.text, asset.image_asset.full_size.url, asset.promotion_asset.occasion, asset.mobile_app_asset.link_text FROM asset`;

    var report = AdsApp.search(query);

    while (report.hasNext()) {

        var row = report.next();
        var id = row.asset.resourceName;

        assets[id] = row;

    }

}

/**
 * Retrieves campaign association assets from the Google Ads API for the last 30 days.
 *
 * Executes a query that gathers campaign asset data, processes metric values,
 * and updates the global 'assets' object with any missing asset information.
 *
 * @returns {Object} An object mapping composite asset identifiers (campaign.id#asset) to campaign asset data.
 */

function getCampaignAssosiationAssets() {

    var retVal = {};

    var query = `SELECT campaign.id, campaign.name, campaign.advertising_channel_type, campaign_asset.asset, metrics.clicks, metrics.conversions, asset.type, asset.source, asset.resource_name, metrics.cost_micros, metrics.impressions, metrics.video_views, metrics.average_cpv, asset.sitelink_asset.link_text, asset.text_asset.text, asset.youtube_video_asset.youtube_video_title, asset.youtube_video_asset.youtube_video_id, asset.name, asset.image_asset.full_size.url, asset.id, asset.callout_asset.callout_text, asset.call_to_action_asset.call_to_action, asset.mobile_app_asset.link_text, asset.price_asset.type, asset.price_asset.price_offerings, asset.promotion_asset.occasion, asset.structured_snippet_asset.values, asset.structured_snippet_asset.header
               FROM campaign_asset 
               WHERE segments.date DURING LAST_30_DAYS`;

    var report = AdsApp.search(query);

    while (report.hasNext()) {

        var row = report.next();
        row.metrics.costMicros = parseFloat(row.metrics.costMicros / 1000000).toFixed(2);
        if (row.metrics.videoViews > 0) {
            row.metrics.averageCpv = parseFloat(row.metrics.averageCpv / 1000000).toFixed(2);
            row.metrics.videoCost = row.metrics.averageCpv * row.metrics.videoViews
        }
        var id = [row.campaign.id, row.campaignAsset.asset].join('#');

        if (!assets[row.asset.resourceName]) {
            assets[row.asset.resourceName] = row.asset;
        }

        retVal[id] = row;

    }

    return retVal;


}

/**
 * Retrieves customer asset association campaign assets from the Google Ads API for the last 30 days.
 *
 * Executes a query that gathers customer asset data, processes metric values,
 * and updates the global 'assets' object with any missing asset information.
 *
 * @param {Object} retVal - An object to accumulate customer asset association campaign assets.
 * @returns {Object} An object mapping composite asset identifiers (campaign.id#asset) to customer asset association data.
 */

function getCustomerAssosiationCampaignAssets(retVal) {

    var query = `SELECT campaign.id, campaign.name, campaign.advertising_channel_type, customer_asset.asset, metrics.clicks, metrics.conversions, asset.type, asset.source, asset.resource_name, metrics.cost_micros, metrics.impressions, metrics.video_views, metrics.average_cpv, customer_asset.field_type, asset.sitelink_asset.link_text, asset.text_asset.text, asset.youtube_video_asset.youtube_video_title, asset.youtube_video_asset.youtube_video_id, asset.name, asset.image_asset.full_size.url, asset.id, asset.callout_asset.callout_text, asset.call_to_action_asset.call_to_action, asset.mobile_app_asset.link_text, asset.price_asset.type, asset.price_asset.price_offerings, asset.promotion_asset.occasion, asset.structured_snippet_asset.values, asset.structured_snippet_asset.header 
               FROM customer_asset 
               WHERE segments.date DURING LAST_30_DAYS`;

    var report = AdsApp.search(query);

    while (report.hasNext()) {

        var row = report.next();
        row.metrics.costMicros = parseFloat(row.metrics.costMicros / 1000000).toFixed(2);
        if (row.metrics.videoViews > 0) {
            row.metrics.averageCpv = parseFloat(row.metrics.averageCpv / 1000000).toFixed(3);
        }
        var id = [row.campaign.id, row.customerAsset.asset].join('#');

        if (!assets[row.asset.resourceName]) {
            assets[row.asset.resourceName] = row.asset;
        }

        retVal[id] = row;

    }

    return retVal;

}

/**
 * Merges properties from one or more source objects into a target object.
 * Unlike Object.assign, this function copies property descriptors, including non-enumerable and symbol properties.
 *
 * @param {Object} target - The object to which properties will be assigned.
 * @param {...Object} sources - One or more source objects.
 * @returns {Object} The target object with properties copied from the source objects.
 */

function completeAssign(target, ...sources) {
    sources.forEach((source) => {
        const descriptors = Object.keys(source).reduce((descriptors, key) => {
            descriptors[key] = Object.getOwnPropertyDescriptor(source, key);
            return descriptors;
        }, {});

        // By default, Object.assign copies enumerable Symbols, too
        Object.getOwnPropertySymbols(source).forEach((sym) => {
            const descriptor = Object.getOwnPropertyDescriptor(source, sym);
            if (descriptor.enumerable) {
                descriptors[sym] = descriptor;
            }
        });
        Object.defineProperties(target, descriptors);
    });
    return target;
}

/**
 * Recursively flattens a nested object into a single-level object with dot-separated keys.
 *
 * @param {Object} ob - The object to flatten.
 * @returns {Object} A new object with a flattened key structure.
 */

function flattenObject(ob) {
    var toReturn = {};

    for (var i in ob) {
        if (!ob.hasOwnProperty(i)) continue;

        if ((typeof ob[i]) == 'object' && ob[i] !== null) {
            var flatObject = flattenObject(ob[i]);
            for (var x in flatObject) {
                if (!flatObject.hasOwnProperty(x)) continue;

                toReturn[i + '.' + x] = flatObject[x];
            }
        } else {
            toReturn[i] = ob[i];
        }
    }
    return toReturn;
}

This is fantastic! My scripting skills are terrible, but I have been attempting to tweak this script to segment by day and pull YTD instead of 30 days but have been very unsuccessful at that so far.

Regardless, this is so helpful!