Google Ads Search Term Extract Script for Campaigns

[kastri asani]

Hi Support,

I have here a script that extracts the search Terms out of the campaigns.

What I need is the conversion action type name also implementet but I can't find

the correct name convention.

Here is the script:

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

// Please enter your spreadsheet URL here
const SHEET_URL = 'https://docs.google.com/spreadsheets/d/1zNyocNhMtyJMcbIG1xRcJN-mydJXKEMQgJ_ljHwd80c/edit#gid=0';

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

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


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




let ss = SpreadsheetApp.openByUrl(SHEET_URL);
ss.rename('Searchterm Extraction');

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

[kastri asani]

Any Update?

[Kevin Brinkman]

You can't get the conversion action name  from campaign_search_term_insight. Instead you can use  search_term_view. See https://developers.google.com/google-ads/api/fields/v15/search_term_view_query_builder. Something like this:

SELECT
  segments.conversion_action_name,
  metrics.conversions,
  metrics.conversions_value
FROM search_term_view
WHERE
  segments.date DURING LAST_30_DAYS
  AND campaign.id =  '${campaignId}'
ORDER BY
  metrics.conversions DESC 

Another thing is that you can't get metrics like clicks and impressions in the same query as conversion action name. You would need to do those separate and then merge them together in a sheet (maybe by code is possible too). 

Hope this helps.

[Dmytro]

https://gist.github.com/tonkikh/9f5a4ed2ecbd81782bdc297671d867f2

Try this one