NLAS #002 - RSA Performance Analyzer - Google Ads Script

Welcome to the second edition of Next Level Ads Scripts!

This week we are sharing a script that we use for internal purpose to analyze a bulk of Responsive Search Ads within minutes.

We just started our newsletter journey, so if you have any suggestions, questions or other ideas after reading this newsletter.

Feel free to share it! You can find us at [email protected] and [email protected] or at Linkedin of course.

The Issue we faced

With big accounts (read: lots of campaigns and lots of adgroups and ads) it can be time consuming to analyze this. That’s why we created a kind of Adalysis spreadsheet where we can analyze ads in bulk.?

So we wanted to make it easier and less time consuming to analyze this. Everytime we get that thought, we start building a script.?

What does the script do?

The script analyzes all ads based on the thresholds you can setup in the yourself based on amount of:

• Days
• Clicks
• Impressions
• Conversions
• Ad labels

The script exports the data to a spreadsheet and gives more insights over there regarding KPI’s like:

• Conversions per impression
• Revenue per impressions
• ROAS per impressions
• CPA

It automatically labels the best and worst performing ads with a green or red color. Then you can analyze the data in the spreadsheet and can navigate to the right adgroup and ad to pause it.

It’s also giving some insights regarding the performance if the best performing ad had spend all the budget.?

How to install the script

The script is very easy to install. You can go to the script section of your Google Ads account and just copy paste the script.

Follow these next steps to install the script:

Step 1: Log into Google Ads

Step 2: Open Script Management

Step 3: Add the Script Code?

Step 4: Configure the Script

Here’s how to Configure it:

• dateRangeDays: Enter the amount of days the script should analyze.
• spreadsheetUrl: Create a new spreadsheet and copy the link of the spreadsheet and insert it.
• clickThreshold: Set the minimum amount of clicks that should be realized in the chosen date range.
• impressionThreshold: Set the minimum amount of impressions that should be realized in the chosen date range.
• conversionThreshold: Set the minimum amount of conversions that should be realized in the chosen date range.
• adLabelFilter: Select a label that has been given to the ads to only look at a specific amount of ads.?

Step 5: Test the Script

Step 6: Schedule the Script

Step 7: Save and Activate

Common Errors:

• No data in the spreadsheet: This might happen if the Responsive Search Ads do not meet the thresholds. Try less strict thresholds for the analysis

With these steps, your script is now installed and configured to automatically analyze RSA’s based on the thresholds you set for yourself.

The script

Copy the script here:

/**
 * Google Ads Script
 * 
 * Created by: Bernt Muurling & Quint van der Meer
 * Copyright ? 2024 Bernt Muurling & Quint van der Meer
 * 
 * This script exports Google Ads data to a Google Spreadsheet, highlighting the best and worst performing ads based on specific metrics.
 * 
 * Use this script at your own risk. The creators take no responsibility for any issues that arise from its usage.
 * 
 */

// Configuration
const CONFIG = {
    dateRangeDays: 364,  // Adjust this value as needed (e.g., 30, 60, 90, up to 365)
    spreadsheetUrl: "", // add spreadsheet url
    clickThreshold: 10, // for example 10
    impressionThreshold: 1000, // 1000 for example
    conversionThreshold: 1, // 1 for example
    adLabelFilter: "" // If left blank, no label filtering is applied
};

// Do not edit anything below this line

function main() {
    try {
        // Limit the date range to a maximum of 365 days
        if (CONFIG.dateRangeDays > 365) {
            Logger.log('Date range cannot exceed 365 days. Setting dateRangeDays to 365.');
            CONFIG.dateRangeDays = 365;
        }

        // Format date range to 'YYYYMMDD' format
        const today = new Date();
        const startDate = new Date(today);
        startDate.setDate(today.getDate() - CONFIG.dateRangeDays);

        const formattedStartDate = Utilities.formatDate(startDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
        const formattedEndDate = Utilities.formatDate(today, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

        // Attempt to open the spreadsheet
        let sheet;
        try {
            sheet = SpreadsheetApp.openByUrl(CONFIG.spreadsheetUrl).getActiveSheet();
            Logger.log('Using existing spreadsheet: ' + CONFIG.spreadsheetUrl);
        } catch (e) {
            Logger.log('Failed to open spreadsheet. Please check the URL: ' + e.message);
            return;
        }

        // Clear existing content
        sheet.clear();
        Logger.log('Cleared existing content from the sheet');
        
        // Set up headers with the correct column names
        const headers = [["Campaign", "Ad Group", "Ad ID", "Ad Label", "Clicks", "CTR (%)", "Conversions", "Impressions", "CPI", "Cost (€)", "CPA (€)", "Conversion Value (€)", "RPI (€)", "ROAS (%)", "Conversions if best ad", "CPA if best ad (€)", "Revenue if best ad (€)", "ROAS if best ad (%)"]];
        sheet.getRange("A1:R1").setValues(headers);

        // Optimize report query by applying thresholds directly
        const labelCondition = CONFIG.adLabelFilter ? `AND LabelIds CONTAINS_ANY [${CONFIG.adLabelFilter}] ` : "";
        
        const report = AdsApp.report(
            `SELECT CampaignId, CampaignName, AdGroupName, Id, Impressions, Clicks, AverageCpc, Conversions, ConversionValue 
            FROM AD_PERFORMANCE_REPORT 
            WHERE CampaignStatus = 'ENABLED' 
            AND AdGroupStatus = 'ENABLED' 
            AND Status = 'ENABLED' 
            AND Clicks >= ${Math.max(0, CONFIG.clickThreshold)} 
            AND Impressions >= ${Math.max(0, CONFIG.impressionThreshold)} 
            AND Conversions > ${Math.max(0, CONFIG.conversionThreshold - 1)} 
            ${labelCondition} 
            DURING ${formattedStartDate},${formattedEndDate}`
        );
        
        const rows = report.rows();
        const adDataList = [];  // List to hold ad data for sorting

        // Loop through the data and gather necessary fields
        while (rows.hasNext()) {
            const stats = rows.next();

            // Parse necessary fields and calculate metrics
            const clicks = parseInt(stats['Clicks']);
            const avgCpc = parseFloat(stats['AverageCpc']);
            const cost = clicks * avgCpc;
            const conversions = parseFloat(stats['Conversions']);
            const impressions = parseInt(stats['Impressions']);
            
            // Handle conversion value correctly by removing commas and parsing it as a number
            const conversionValue = parseFloat(stats['ConversionValue'].replace(/,/g, ''));

            const adData = {
                campaignId: stats['CampaignId'],
                campaignName: stats['CampaignName'],
                adGroupName: stats['AdGroupName'],
                adId: stats['Id'],
                adLabel: getAdLabelsForAd(stats['Id']),
                clicks: clicks,
                conversions: conversions,
                impressions: impressions,
                cost: cost,
                revenue: conversionValue,
            };

            adData.ctr = impressions > 0 ? (clicks / impressions) * 100 : 0;
            adData.cpi = calculateCPI(conversions, impressions);
            adData.cpa = conversions > 0 ? (cost / conversions) : 0;
            adData.rpi = impressions > 0 ? (conversionValue / impressions) : 0;
            adData.roas = cost > 0 ? (conversionValue / cost) * 100 : 0;

            // Format values to two decimal places
            adData.cost = adData.cost.toFixed(2);
            adData.cpa = adData.cpa.toFixed(2);
            adData.revenue = adData.revenue.toFixed(2);
            adData.rpi = adData.rpi.toFixed(2);
            adData.roas = adData.roas.toFixed(2);
            adData.ctr = adData.ctr.toFixed(2);

            // Add ad data to list
            adDataList.push(adData);
        }

        // Sort adDataList by campaign name in alphabetical order
        adDataList.sort((a, b) => a.campaignName.localeCompare(b.campaignName));

        // Write sorted data to spreadsheet starting from row 2
        let currentRow = 2;
        let lastAdGroup = '';
        let adGroupAds = [];

        adDataList.forEach((ad) => {
            if (ad.adGroupName !== lastAdGroup && adGroupAds.length > 0) {
                // Process and highlight best-performing ads in the previous ad group
                highlightBestPerformers(adGroupAds, sheet);
                adGroupAds = [];

                // Add a blank row between ad groups for better separation
                currentRow++; 
                sheet.insertRowAfter(currentRow - 1);
            }

            // Write the ad data to the spreadsheet
            sheet.getRange(currentRow, 1, 1, 14).setValues([[
                ad.campaignName, 
                ad.adGroupName,
                ad.adId,
                ad.adLabel,
                ad.clicks,
                ad.ctr,
                ad.conversions,
                ad.impressions,
                ad.cpi,
                ad.cost,
                ad.cpa,
                ad.revenue,
                ad.rpi,
                ad.roas
            ]]);

            // Add ad to the current ad group list
            adGroupAds.push({ ...ad, row: currentRow });

            lastAdGroup = ad.adGroupName;
            currentRow++;
        });

        // Process the last ad group
        if (adGroupAds.length > 0) {
            highlightBestPerformers(adGroupAds, sheet);
        }

        Logger.log('Data export completed. Spreadsheet URL: ' + CONFIG.spreadsheetUrl);
    } catch (e) {
        Logger.log('An error occurred: ' + e.message);
    }
}

function highlightBestPerformers(ads, sheet) {
    if (ads.length <= 1) {
        return;
    }

    // Find the best and worst performers for CPI, CPA, RPI, and ROAS
    var bestCpi = Math.max.apply(Math, ads.map(ad => parseFloat(ad.cpi)));
    var worstCpi = Math.min.apply(Math, ads.map(ad => parseFloat(ad.cpi)));
    var bestCpa = Math.min.apply(Math, ads.map(ad => parseFloat(ad.cpa)));
    var worstCpa = Math.max.apply(Math, ads.map(ad => parseFloat(ad.cpa)));
    var bestRpi = Math.max.apply(Math, ads.map(ad => parseFloat(ad.rpi)));
    var worstRpi = Math.min.apply(Math, ads.map(ad => parseFloat(ad.rpi)));
    var bestRoas = Math.max.apply(Math, ads.map(ad => parseFloat(ad.roas)));
    var worstRoas = Math.min.apply(Math, ads.map(ad => parseFloat(ad.roas)));

    // Find the best and worst performing ads based on CPA
    var bestAd = ads.find(ad => parseFloat(ad.cpa) === bestCpa);
    var worstAd = ads.find(ad => parseFloat(ad.cpa) === worstCpa);

    // Calculate combined spend of best and worst ads
    var combinedSpend = parseFloat(bestAd.cost) + parseFloat(worstAd.cost);

    // Calculate metrics for best ad with combined spend
    var conversionsIfBest = (combinedSpend / parseFloat(bestAd.cpa)).toFixed(2);
    var cpaIfBest = parseFloat(bestAd.cpa).toFixed(2);
    var revenueIfBest = (combinedSpend * parseFloat(bestAd.revenue) / parseFloat(bestAd.cost)).toFixed(2);
    var roasIfBest = ((revenueIfBest / combinedSpend) * 100).toFixed(2);

    ads.forEach(function(ad) {
        var currentRow = ad.row;

        // Highlight best and worst KPIs
        const highlightCell = (value, best, worst, column) => {
            if (value === best && best !== 0) {
                sheet.getRange(currentRow, column).setBackground("#00FF00");  // Highlight best cell
            }
            if (value === worst && worst !== 0) {
                sheet.getRange(currentRow, column).setBackground("#FF0000");  // Highlight worst cell
            }
        };

        highlightCell(parseFloat(ad.cpi), bestCpi, worstCpi, 9);
        highlightCell(parseFloat(ad.cpa), bestCpa, worstCpa, 11);
        highlightCell(parseFloat(ad.rpi), bestRpi, worstRpi, 13);
        highlightCell(parseFloat(ad.roas), bestRoas, worstRoas, 14);

        // Write calculated values in new columns
        sheet.getRange(currentRow, 15).setValue(conversionsIfBest);  // "Conversions if best ad"
        sheet.getRange(currentRow, 16).setValue(cpaIfBest);          // "CPA if best ad (€)"
        sheet.getRange(currentRow, 17).setValue(revenueIfBest);      // "Revenue if best ad (€)"
        sheet.getRange(currentRow, 18).setValue(roasIfBest);         // "ROAS if best ad (%)"
    });
}

function calculateCPI(conversions, impressions) {
    return impressions > 0 ? (conversions / impressions) * 100 : 0;
}

function getAdLabelsForAd(adId) {
    const labelNames = [];
    const adIterator = AdsApp.ads().withCondition(`Id = '${adId}'`).get();
    if (adIterator.hasNext()) {
        const ad = adIterator.next();
        const labels = ad.labels().get();
        while (labels.hasNext()) {
            const label = labels.next();
            labelNames.push(label.getName());
        }
    }
    return labelNames.join(', ');
}        

Do you make your own scripts or have some tips for us? Please let us know.

Thanks for reading and see you next week Quint van der Meer & Bernt Muurling !

Also check out our previous editions:

NLAS #001 - Consent Mode Checker - Google Ads Scripts