How to Export Top Performing Google Ads Search Terms to Google Sheets Using Apps Script

If you’re running Performance Max or broad match campaigns, you're already familiar with the pain. Google hides the real search queries. This script brings them back. It connects directly to your MCC, grabs the queries that converted, and drops them into a Google Sheet with CPA and conversion rate calculated.

Our script on "How to Export Top Performing Google Ads Search Terms to Google Sheets Using Apps Script" is perfect for agencies managing multiple clients or marketers who want to stop guessing.

Let’s break it down.

What Export Top Performing Google Ads Search Terms Script Does

• Pulls 6 months of query data across all campaigns
• Filters out garbage: branded terms and low-converting noise
• Calculates cost per conversion and conversion rate
• Drops everything into a clean Google Sheet, one tab per account

How to Add a Google Ads Script

The Scripts section is where all bulk automation is set up within Google Ads MCC.

1. Log in to your MCC (Manager Account) and go to Tools > Bulk Actions > Scripts

2. Click the blue plus (+) button to create a new script

This opens the script editor. Don’t worry, you won’t be coding from scratch. You’ll be pasting a script that’s already written. Clicking this button creates a blank script container for your automation.

Naming your script is essential for long-term management and scheduling.

3. Name the script so it’s easy to identify later

At the top of the script editor window, you’ll see a field to enter a name. Use something clear like “Top Performing Search Terms Report.” This will help you recognize the script later when managing multiple scripts or accounts.

You can name it Top Performing Actual Search Terms and drop in the script (included at the bottom of this post).

Name the script

4. Paste the entire script into the editor

function main() {
// Set the destination Google Sheet URL
var spreadsheetUrl = 'Paste Your Google Sheet URL Here';

// Get the account name to dynamically name the sheet
var accountName = AdsApp.currentAccount().getName();

// Use the account name to label the tab inside the spreadsheet
var sheetName = accountName + ' - Top Performing Terms PC 90 Days';

// Open the spreadsheet by URL
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

// Get the sheet by name, or create it if it doesn't exist
var sheet = spreadsheet.getSheetByName(sheetName) || spreadsheet.insertSheet(sheetName);

// Clear any existing data from the sheet
sheet.clear();

// Write header row to the spreadsheet
sheet.getRange('A1').setValue('Campaign Name');
sheet.getRange('B1').setValue('Ad Group Name');
sheet.getRange('C1').setValue('Search Term');
sheet.getRange('D1').setValue('Conversions');
sheet.getRange('E1').setValue('Conversion Value');
sheet.getRange('F1').setValue('Cost Per Conversion (CPA)');
sheet.getRange('G1').setValue('Conversion Rate (%)');

// Set the custom date range for reporting (format: YYYYMMDD)
var startDate = '20230315';
var endDate = '20240915';

// Build the query from the Search Query Performance Report
var report = AdsApp.report(
"SELECT CampaignName, AdGroupName, Query, Conversions, ConversionValue, Cost, Clicks " +
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE Conversions > 0 " +
"DURING " + startDate + "," + endDate
);

// Get report rows and set starting row for data insertion
var rows = report.rows();
var rowNumber = 2;

// Iterate through each row of the report
while (rows.hasNext()) {
var row = rows.next();

// Lowercase the search term for consistent brand filtering
var searchTerm = row['Query'].toLowerCase();
var conversions = parseFloat(row['Conversions']);

// Skip rows with less than 1 conversion
if (conversions < 1) continue;

// Exclude branded terms to avoid polluting the report
if (searchTerm.includes('parker chase') || searchTerm.includes('parker-chase') || searchTerm.includes('endeavor schools')) {
continue;
}

// Optional generic filter - add or remove terms here
if (
searchTerm.includes('creative') ||
searchTerm.includes('learning') ||
searchTerm.includes('content')
) {
continue;
}

// Extract cost and click values to calculate performance
var cost = parseFloat(row['Cost']);
var clicks = parseFloat(row['Clicks']);

// Calculate cost per conversion (CPA)
var cpa = conversions > 0 ? cost / conversions : 0;

// Calculate conversion rate
var conversionRate = clicks > 0 ? (conversions / clicks) * 100 : 0;

// Format values
cpa = cpa.toFixed(2);
conversionRate = conversionRate.toFixed(2);

// Write the row to the spreadsheet
sheet.getRange(rowNumber, 1).setValue(row['CampaignName']);
sheet.getRange(rowNumber, 2).setValue(row['AdGroupName']);
sheet.getRange(rowNumber, 3).setValue(row['Query']);
sheet.getRange(rowNumber, 4).setValue(conversions);
sheet.getRange(rowNumber, 5).setValue(row['ConversionValue']);
sheet.getRange(rowNumber, 6).setValue('$' + cpa); // Include dollar sign for CPA
sheet.getRange(rowNumber, 7).setValue(conversionRate + '%'); // Add percentage symbol

// Move to the next row in the sheet
rowNumber++;
}

// Log how many rows were processed
Logger.log('Export completed. Total rows processed: ' + (rowNumber - 2));
}

What You Can Customize in the Script

The script includes several marked settings you can adjust before running it. These are all inside the script's comment blocks and are meant to be changed based on your specific goals, reporting range, or formatting preferences.

Here’s a breakdown of what you can modify and why:

1. Spreadsheet URL

var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/xxxxx/edit';

What it does:
This is where the script sends your data. Replace the placeholder URL with the link to your Google Sheet. Ensure the Sheet is shared with the Google account tied to your MCC if you’re using multiple email addresses.

2. Date Range Settings

var startDate = '20230315';
var endDate = '20240915';

What it does:
This controls the reporting window. Dates must be formatted as YYYYMMDD. You can adjust these to match whatever time frame you're analyzing — last 30 days, last 90, custom quarterly ranges, etc.

3. Sheet Name Behavior

var sheetName = accountName + ' - Top Performing Terms PC 90 Days';

What it does:
This line dynamically names the Sheet tab based on the account running the script. You can change the text string portion (' - Top Performing Terms PC 90 Days') if you want to label reports differently. Useful when running across multiple brands or sub-accounts.

4. Branded Term Filtering

if (searchTerm.includes('parker chase') || searchTerm.includes('parker-chase') || searchTerm.includes('endeavor schools')) {
continue;
}

What it does:
This section excludes any branded terms from your report. You can update this list by adding or removing .includes() conditions. For example, if you want to exclude “MyBrand” just add:

|| searchTerm.includes('mybrand')

Use lowercase consistently since the script converts all queries to lowercase.

5. Generic Term Filtering

if (
searchTerm.includes('creative') ||
searchTerm.includes('learning') ||
searchTerm.includes('content')
) {
continue;
}

What it does:
This optional filter weeds out terms that aren’t brand-specific but still muddy the data — like “learning” or “creative.” Add or remove filters here based on what you consider noise.

6. Performance Threshold

if (conversions < 1) continue;

What it does:
This line removes queries with no conversions. You can increase this threshold if you want, only higher-performing terms. For example, change to:

if (conversions < 3) continue;
...to include only search terms with 3+ conversions in the date range.

7. Sheet Output Formatting

sheet.getRange(rowNumber, 6).setValue('$' + cpa);
sheet.getRange(rowNumber, 7).setValue(conversionRate + '%');

What it does:
This controls how CPA and conversion rate are written to the Sheet. If you want to remove symbols (e.g., $ or %), you can edit or delete the string additions.

Output Fields

Once the script runs successfully, it will populate your Google Sheet with a structured table. Each row represents a search term that drove at least one conversion within the specified date range.

The Google sheet example

Output Fields

The script generates a set of performance columns in your Google Sheet, providing a clear view of how each search term contributes. These include the campaign and ad group names for tracking where the term was triggered, the actual search term typed by the user, the number of conversions it drove, the total conversion value, the average cost per conversion, and the overall conversion rate. Each of these fields is critical for understanding what works and what wastes are spent.

• Campaign Name
  Name of the campaign where the ad was triggered

• Ad Group Name
  An ad group that matched the search term

• Search Term
  The exact phrase the user typed into Google

• Conversions
  Number of tracked conversions from that term

• Conversion Value
  Total value of those conversions, based on your account settings

• Cost Per Conversion (CPA)
  Average cost to generate one conversion from that search term

• Conversion Rate (%)
  Percentage of clicks that resulted in a conversion

How This Script Could Be Enhanced

This script is a solid foundation, but it can go further depending on how deep you want your reporting to go. If you're managing multiple brands or accounts under one MCC, adding campaign labels or filters can help isolate data by brand or business unit. Scheduling the script to run automatically on a weekly or monthly basis saves time and ensures consistent reports. You can build logic to send alerts when key metrics, such as CPA or conversion rate, cross certain thresholds. For more granular analysis, you can combine this with other reports  KEYWORDS_PERFORMANCE_REPORT to get match-type breakdowns. Inside the Sheet, adding pivot tables or conditional formatting can surface insights faster. And if you want to go visual, the data can be pulled into Looker Studio for dashboards your clients can understand.

• Add campaign labels or account-level filters to segment data across large MCCs

• Use built-in scheduling to automate reports weekly or monthly

• Trigger email alerts when CPA exceeds a specific value or conversion rate drops below a threshold

• Join data from other reports, like KEYWORDS_PERFORMANCE_REPOR,T to break down match types

• Add basic pivot summaries or conditional formatting directly in Sheets

• Connect the output to Looker Studio for visual performance dashboards

Closing Thoughts: Powered by Bright Vessel

At Bright Vessel, we build custom automation tools like this for clients who need clear performance insights from cluttered ad environments. If you’re tired of digging through poor dashboards or running five manual exports to obtain the data you need, this script is just one of many ways we streamline the process.

Check out our related walkthrough on “How to Monitor Google Ads Goal Status Across MCC Accounts Using Google Sheets and Apps Script” for another method to automate tracking at scale.

Need a version of this that integrates with Slack, builds Looker dashboards, or connects multiple ad accounts into a master sheet? We can make it. Let’s talk.