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, [email protected]
 */


// 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;
}