Add search terms and category labels from Performance Max campaigns on Google Ads to a Google spreadsheet automatically

PMax Search Terms and Categories in a Spreadsheet

function main() {
    /******************************************
     * PMax Search Terms Report
     * @version: 1.0
     * @authors: Frederick Vallaeys (Optmyzr)
     * -------------------------------
     * Install this script in your Google Ads account (not an MCC account)
     * to generate a spreadsheet containing the search terms in your Performance Max campaigns.
     * The spreadsheet also includes data about category labels (groupings of search terms).
     * Metrics include conversion value, conversions, clicks, and impressions
     * --------------------------------
     * v2 (Mar 14, 2024): adds a new column that says if the search terms exists as a keyword in a search campaign. adds better error handling.
     * --------------------------------
     * For more PPC tools and scripts, visit www.optmyzr.com.
     ******************************************/

    var minImp = 10; // Limit the output to only items with at least this many impressions
    var spreadsheetUrl = ""; // leave blank to generate a new spreadsheet or add your own URL to overwrite the data in an existing spreadsheet
    var reportLastNDays = 30; // The number of days to include in the report
    var EMAILADDRESS = ""; // enter your email address

    // Don't edit below this line unless you know how to write scripts
    //-----------------------------------------------------------------
    let pmaxOnly = 1;

    let allCategoryLabels = [
        [
            "Campaign Name",
            "Category Label",
            "Conv Val",
            "Conv",
            "Clicks",
            "Imp",
        ],
    ];
    let allSearchTerms = [
        [
            "Campaign Name",
            "Category Label",
            "Subcat",
            "Search Term",
            "Conv Val",
            "Conv",
            "Clicks",
            "Imp",
            "Exists as Keyword?",
        ],
    ];

    let keywordsList = getAllSearchCampaignKeywords();

    var dateRange = getDateRange(reportLastNDays);

    try {
        let baseQuery = `
    SELECT 
    campaign.id, 
    campaign.name, 
    metrics.clicks, 
    metrics.impressions, 
    metrics.conversions, 
    metrics.conversions_value
    FROM campaign
    WHERE campaign.status != 'REMOVED'
    AND metrics.impressions > 0
    AND segments.date BETWEEN ${dateRange}
    AND metrics.impressions >= ${minImp}
    `;

        // Modify the SQL query if pmaxOnly is true
        if (pmaxOnly) {
            baseQuery +=
                " AND campaign.advertising_channel_type = 'PERFORMANCE_MAX' ";
        }

        baseQuery += "ORDER BY metrics.conversions DESC";

        let campaignIdsQuery = AdsApp.report(baseQuery);

        let rows = campaignIdsQuery.rows();

        while (rows.hasNext()) {
            let campaignRow = rows.next();
            let campaignId = campaignRow["campaign.id"];
            Logger.log(
                campaignRow["campaign.id"] + " " + campaignRow["campaign.name"]
            );

            // Search Labels Report
            let categoryLabelsQuery = `
      SELECT 
                campaign_search_term_insight.category_label, 
                campaign_search_term_insight.id,
                metrics.clicks, 
                metrics.impressions, 
                metrics.conversions,
                metrics.conversions_value
              FROM 
                campaign_search_term_insight 
              WHERE 
                segments.date BETWEEN ${dateRange}
                AND campaign_search_term_insight.campaign_id = '${campaignId}'
                AND metrics.impressions >= ${minImp}
              ORDER BY 
                metrics.conversions DESC
              `;
            let categoryLabelsQueryResult = AdsApp.report(categoryLabelsQuery);
            let categoryLabelsResults = categoryLabelsQueryResult.rows();
            while (categoryLabelsResults.hasNext()) {
                let categoryLabelsRow = categoryLabelsResults.next();
                let categoryLabelId =
                    categoryLabelsRow["campaign_search_term_insight.id"];
                //Logger.log(categoryLabelId + ". " + categoryLabelsRow['campaign_search_term_insight.category_label'] + " " + categoryLabelsRow['metrics.impressions']);
                allCategoryLabels.push([
                    campaignRow["campaign.name"],
                    categoryLabelsRow[
                        "campaign_search_term_insight.category_label"
                    ],
                    categoryLabelsRow["metrics.conversions_value"].toFixed(2),
                    categoryLabelsRow["metrics.conversions"].toFixed(1),
                    categoryLabelsRow["metrics.clicks"],
                    categoryLabelsRow["metrics.impressions"],
                ]);
                // Search Terms Report
                let searchTermsQuery = `
        SELECT 
                  metrics.clicks, 
                  metrics.impressions, 
                  metrics.conversions,
                  metrics.conversions_value,
                  segments.search_term,
                  segments.search_subcategory
                FROM 
                  campaign_search_term_insight 
                WHERE 
                  segments.date BETWEEN ${dateRange}
                  AND campaign_search_term_insight.campaign_id = '${campaignId}'
                  AND campaign_search_term_insight.id = '${categoryLabelId}'
                `;
                let searchTermsQueryResult = AdsApp.report(searchTermsQuery);
                let searchTermsResults = searchTermsQueryResult.rows();
                // Inside the loop where search terms are being processed
                while (searchTermsResults.hasNext()) {
                    let searchTermsRow = searchTermsResults.next();
                    if (searchTermsRow["metrics.impressions"] >= minImp) {
                        let searchTermText = searchTermsRow[
                            "segments.search_term"
                        ].toLowerCase(); // Normalize search term text for comparison
                        let existsAsKeyword = keywordsList[searchTermText]
                            ? "Yes"
                            : "No";

                        allSearchTerms.push([
                            campaignRow["campaign.name"],
                            categoryLabelsRow[
                                "campaign_search_term_insight.category_label"
                            ],
                            searchTermsRow["segments.search_subcategory"],
                            searchTermsRow["segments.search_term"],
                            searchTermsRow["metrics.conversions_value"].toFixed(
                                2
                            ),
                            searchTermsRow["metrics.conversions"].toFixed(1),
                            searchTermsRow["metrics.clicks"],
                            searchTermsRow["metrics.impressions"],
                            existsAsKeyword, // New column indicating if the search term exists as a keyword
                        ]);
                    }
                }
            }
        }

        if (!spreadsheetUrl) {
            var ss = SpreadsheetApp.create("PMax Search Terms", 10000, 20);
            var spreadsheetUrl = ss.getUrl();
        } else {
            var ss = SpreadsheetApp.openByUrl(spreadsheetUrl);
        }

        let categoriesSheet = ss.getSheetByName("categories")
            ? ss.getSheetByName("categories").clear()
            : ss.insertSheet("categories");
        if (allCategoryLabels.length > 1) {
            // Check if there's more than just the header row
            categoriesSheet
                .getRange(
                    1,
                    1,
                    allCategoryLabels.length,
                    allCategoryLabels[0].length
                )
                .setValues(allCategoryLabels);
        }

        let termsSheet = ss.getSheetByName("terms")
            ? ss.getSheetByName("terms").clear()
            : ss.insertSheet("terms");
        if (allSearchTerms.length > 1) {
            // Check if there's more than just the header row
            termsSheet
                .getRange(1, 1, allSearchTerms.length, allSearchTerms[0].length)
                .setValues(allSearchTerms);
        }

        var subject = "PMax Search Terms Report Ready";
        var body =
            "The PMax Search Terms Report has been generated and is available at: " +
            spreadsheetUrl +
            "\n\nReport covers the last " +
            reportLastNDays +
            " days." +
            "\n\nThis is an automated email sent by Google Ads Script.";
    } catch (e) {
        Logger.log("Error: " + e.message);
        var subject = "PMax Search Terms Report Failed";
        var body =
            "The PMax Search Terms Report encountered an error: " +
            e +
            "\n\nThis is an automated email sent by Google Ads Script.";
    }
    Logger.log("spreadsheet: " + spreadsheetUrl);

    // Send the email
    var recipientEmail = EMAILADDRESS;
    MailApp.sendEmail(recipientEmail, subject, body);
}

// function to get date range
function getDateRange(numDays) {
    const endDate = new Date();
    const startDate = new Date();
    startDate.setDate(endDate.getDate() - numDays);
    const format = (date) =>
        Utilities.formatDate(
            date,
            AdsApp.currentAccount().getTimeZone(),
            "yyyyMMdd"
        );
    return `${format(startDate)} AND ${format(endDate)}`;
}

function getAllSearchCampaignKeywords() {
    let keywordsList = {};
    let keywordIterator = AdsApp.keywords()
        .withCondition("Status = ENABLED")
        .withCondition("CampaignStatus = ENABLED")
        .withCondition("AdGroupStatus = ENABLED")
        .get();

    while (keywordIterator.hasNext()) {
        let keyword = keywordIterator.next();
        let keywordText = keyword.getText().toLowerCase(); // Normalize keyword text for comparison
        keywordsList[keywordText] = true; // Use a map for efficient lookup
    }

    return keywordsList;
}

@meleagant I updated the code a few days ago to add better error handling. The error you're getting is most likely because there is a search theme that doesn't return any search terms. I will create a new version that will continue past this error. Thanks for reporting it!

I am still getting Error: Could not read from Google Ads

That could be an error on the GAds side as explained here but could you send me a screenshot of the log section where you see this error so I can try to help understand why Google isn't responding with data?

Thanks for your help! I will check out the link you sent. I got this error on 4 different accounts in the same spot, right after the log for the first campaign. I tried running this multiple times on each account and got the same error. [image: image.png]

…

On Wed, Mar 20, 2024 at 12:47 PM Frederick Vallaeys < ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ That could be an error on the GAds side as explained here <https://nilsrooijmans.com/google-ads-scripts-faq/failed-to-read-from-adwords-error-and-fix/> but could you send me a screenshot of the log section where you see this error so I can try to help understand why Google isn't responding with data? — Reply to this email directly, view it on GitHub <https://gist.github.com/siliconvallaeys/a579409e0f94d8269744d6460b9cb466#gistcomment-4995322> or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABYGJQYKPYBBEUZ3JPBPAPTYZHRTRBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVEYTEOBYHE4DSNZQU52HE2LHM5SXFJTDOJSWC5DF> . You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

I'm getting the same error "Error: Could not read from Google Ads"

I'm getting the same error: "Error: Could not read from Google Ads"

@aavolo it seems you may have attached an image of the error but that didn't come through. Can you email it to me or Optmyzr as it may help us track down the problem?

The error appears not to be tied to a particular line in the script and it sounds like some people are getting it after a campaign name is printed to the logs. This would indicate a problem on the Google side rather than with the script itself. I'll try to find a solution...

I am also getting the "Error: Could not read from Google Ads" error

I'll try to find a solution...

Any update on a solution?

I am also getting the "Error: Could not read from Google Ads" error

Hi, thanks for the code, should prove very useful. Hate not being able to see what search terms are triggering ads. I've added the code an it seems to run fine with out errors, but I'm not seeing any data in the Google Sheet. My PMax has been running for 2 days and received 72 clicks. Should I expect to see data from this?

Thanks for your help.

Hi, I set up the script a few weeks ago and it worked until this latest update. I received this notification "PMax search terms report failed". Do you know what's happening? Could you help me?
Thanks for your help.

I love this script. Thanks so much Frederick Vallaeys.

I've tried it on three accounts. On the first account, it works great. On the other two accounts I get the same errors the others have noted above.

I do not believe this is the script's fault, because when I go to download (export) the report manually using the insights tab in Google Ads, Google refuses to process that as well. I'm going to open a case with support about the account failing but am not optimistic that they will be helpful.

Thank you so much for that script! I will share it at my channel with direct link to Github and author mention.