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

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.

Thanks for the amazing script! It gives great insight in the campaigns. We have a question: would it be possible to add a colum in the Google Sheets to show which keywords have been excluded already?