Script: Search Categories in PMax

Use this script to get Search Category data into a Google Sheet for every PMax, Search & DSA campaign in your account..

Install the script in the normal way (see the JumpStart section for details on how to do this if needed) & then change the top ‘user config’ section of the script.

1. Create a new Google Sheet (just type ‘sheet.new’ into the address bar on chrome/brave) & copy the URL of the sheet into the line that starts ‘const SHEET_URL = ‘

2. Decide if you want data just for PMax campaigns, or for all campaigns. If you want all, set pmaxOnly to equal false

3. Decide if you want the tab names in your Sheet to be the Campaign ID or the Campaign Name. I prefer IDs as they’re much shorter, but some people prefer names as it’s easier to find the data you’re looking for

The authorise & run the script

And once everything’s working the way you want, schedule the script to run daily or weekly.

// -------------------------
// User Configuration Section
// -------------------------

// Please enter your spreadsheet URL here
const SHEET_URL = 'YOUR_SPREADSHEET_URL_HERE';

// Set to false if you want search categories for ALL campaigns, not just PERFORMANCE_MAX
let pmaxOnly = true; 

// Set this variable to true if you want to use campaign names for tab names, otherwise it will use campaign IDs.
let useNameForTabs = false;  


// Don't change the code below this line.
// -------------------------




let ss = SpreadsheetApp.openByUrl(SHEET_URL);
ss.rename('Mike Rhodes - Search Category Insights');

function removeUnneededTabs(campaignData) {
    let allSheets = ss.getSheets();
    let neededSheetNames = campaignData.map(campaign => useNameForTabs ? campaign.name : campaign.id);
    neededSheetNames.push('campaigns');  // Keep the 'campaigns' sheet

    for (let i = 0; i < allSheets.length; i++) {
        let sheetName = allSheets[i].getName();
        if (!neededSheetNames.includes(sheetName)) {
            ss.deleteSheet(allSheets[i]);
        }
    }
}



function formatCampaignsSheet(sheet) {
    // Bold the first row
    sheet.getRange(1, 1, 1, sheet.getLastColumn()).setFontWeight("bold");
    
    // Freeze the first row
    sheet.setFrozenRows(1);
    
    // Set the width of column A to 500
    sheet.setColumnWidth(1, 500);

    // Set the decimal places for conversions & conversion value to 1 decimal place
    sheet.getRange(2, 5, sheet.getLastRow()).setNumberFormat("0.0");  // conversions in col 5
    sheet.getRange(2, 6, sheet.getLastRow()).setNumberFormat("0.0");  // conversion value in col 6
}

function formatCampaignIdSheet(sheet) {
    // Bold the first row
    sheet.getRange(1, 1, 1, sheet.getLastColumn()).setFontWeight("bold");
    
    // Freeze the first row
    sheet.setFrozenRows(1);
    
    // Set the width of column A to 500
    sheet.setColumnWidth(1, 500);

    // Set the decimal places for conversions & conversion value to 1 decimal place
    sheet.getRange(2, 4, sheet.getLastRow()).setNumberFormat("0.0");  // conversions in col 4
    sheet.getRange(2, 5, sheet.getLastRow()).setNumberFormat("0.0");  // conversion value in col 5
}

function main() {

    // 1. Extract Campaign IDs with Status not 'REMOVED' and impressions > 0
    // Initialize SQL query
    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 DURING LAST_30_DAYS
    `;
    
    // 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 campaignIds = [];
    let campaignData = [];  
    let rows = campaignIdsQuery.rows();

    // Create or clear 'campaigns' sheet
    let campaignsSheet = ss.getSheetByName('campaigns');
    if (!campaignsSheet) {
        campaignsSheet = ss.insertSheet('campaigns');
    } else {
        campaignsSheet.clear();
    }
    campaignsSheet.appendRow(['Campaign Name', 'Campaign ID', 'Clicks', 'Impressions', 'Conversions', 'Conversion Value']);


    while (rows.hasNext()) {
        let row = rows.next();
        campaignData.push({id: row['campaign.id'], name: row['campaign.name']});
        campaignsSheet.appendRow([row['campaign.name'], row['campaign.id'], row['metrics.clicks'], row['metrics.impressions'], row['metrics.conversions'], row['metrics.conversions_value']]);
    }

    // Format the 'campaigns' sheet
    formatCampaignsSheet(campaignsSheet);

    // Remove unneeded tabs
    removeUnneededTabs(campaignData);


    // 2. Process each Campaign ID
    for (let i = 0; i < campaignData.length; i++) {
        let campaignId = campaignData[i].id;
        let campaignName = campaignData[i].name;

        let sheetName = useNameForTabs ? campaignName : campaignId;

        // Check if sheet/tab exists with the chosen name
        let sheet = ss.getSheetByName(sheetName);
        if (!sheet) {
            // If not, create a new sheet/tab with the chosen name
            sheet = ss.insertSheet(sheetName);
        }

        // Fetch search terms and related metrics for the campaign ordered by conversions descending
        let query = AdsApp.report(
            `
            SELECT 
              campaign_search_term_insight.category_label, 
              metrics.clicks, 
              metrics.impressions, 
              metrics.conversions,
              metrics.conversions_value
            FROM 
              campaign_search_term_insight 
            WHERE 
              segments.date DURING LAST_30_DAYS 
              AND campaign_search_term_insight.campaign_id = '${campaignId}'
            ORDER BY 
              metrics.conversions DESC
            `
        );

        // Export the results to the sheet/tab with the name of the Campaign ID
        query.exportToSheet(sheet);

        // Format the individual campaign sheet
        formatCampaignIdSheet(sheet);
    }
}