Google Ads Script: Spot Overlapping Search Terms

This script helps you track and analyze your Google Ads performance by looking for search terms that are triggering ads in multiple campaigns. You don't need coding experience to set it up. Originally written as a how-to manual, we've adapted it into an article for our site to help more users get the most out of the script and avoid costly campaign overlap.

Running this script weekly, it reports on any duplicate search terms from the past 7 days, along with their associated data like clicks, impressions, and cost.

How It Works

The script generates a report that includes:

• Search terms that appear in more than one campaign.

• How many clicks and impressions those terms received.

• The total cost associated with them.

• The Click-Through Rate (CTR) and Cost Per Click (CPC) for each term.

How to Use the Script

First, get the Google Sheet template HERE. This template is pre-configured to work with the script.

Customize the Script

1. Inside the script, locate the GOOGLE-SHEET-URL and replace it with the URL of your copy of the downloaded template.

2. Find LAST_7_DAYS in the script to adjust the time frame you want to analyze. You can change this to a range that suits your reporting cycle.

3. Adjust for Currency: The script uses Euros (EUR) by default. If you use a different currency: Change the currency symbol in the script to match yours. Make sure you update the currency format in the "Results" sheet (hidden) of the Google Sheet template.

4. Exclude campaigns: If you want to exclude specific campaigns, simply find the line that says ‘// Add campaign IDs you wish to exclude’ and replace the number ‘12345678910’ with the campaign ID of the campaign you wish to exclude from the report. If you want to exclude more than one campaign, separate the campaign IDs using commas (e.g. ['12345678910', '02345678910'])

Remember, you need to share your Google Sheet with editing rights to connect it with the script. This setup allows the script to write the report directly to your sheet.

How to add the script to Google Ads

This part is for those who are new to Google Ads scripts. This brief explanation will help you implement the script easily. Remember to adjust the script with your specific details where needed.

Copy the Script: Highlight the entire script provided below after making your adjustments and copy it (Ctrl+C or Command+C).

Access Google Ads Script Editor:

• Log in to your Google Ads account.

• Click on 'Tools & Settings'.

• Under 'Bulk Actions', select 'Scripts'.

Add the New Script:

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

• In the script editor that opens, clear it and paste (Ctrl+V or Command+V) the copied script.

Save Your Script:

• Click ‘Preview’ to see if it works.

• Once you’re sure it’s working, click 'Save' in the corner of the script editor.

• Name your script for easy identification.

Authorize the Script:

• The first time you save, you'll be asked to authorize the script to access your Google Ads..

• Click 'Authorize', follow the prompts, and allow the necessary permissions.

Set a Schedule:

• Return to the main 'Scripts' page where all your scripts are listed.

• Next to 'Frequency', click the 'Edit' icon (pencil) to set how often your script runs (e.g., daily, weekly). Each time the script runs, it will replace the previous content.

Make sure you have the Google Sheet template and that you replace `GOOGLE-SHEET-URL` in the script with the URL of Google Sheet document. This ensures that the script knows where to send the report.

The script (adjust, copy/paste):

function main() {

  updateOverlappingSearchTerms();
  
  // Logging attribution
  Logger.log("Script by Three Chapter Media | www.threechaptermedia.com"); 

}

function updateOverlappingSearchTerms() {
  var sheetUrl = 'GOOGLE-SHEET-URL';
  var dateRange = 'LAST_7_DAYS';
  var overlapData = {};
  var excludedCampaignIds = ['12345678910']; // Add campaign IDs you wish to exclude

  // Fetch search term reports for all campaigns
  var report = AdsApp.report(
    "SELECT CampaignId, CampaignName, Query, Clicks, Impressions, Cost " +
    "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
    "DURING " + dateRange
  );

  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    var campaignId = row['CampaignId'];

    // Skip the row if the campaign ID is in the excluded list
    if (excludedCampaignIds.includes(campaignId)) continue;

    var query = row['Query'].trim(); // Trim to remove any extra white space
    if (!overlapData[query]) {
      overlapData[query] = { 
        campaigns: new Set(), // Use a Set to store unique campaign IDs
        details: []
      };
    }
    overlapData[query].campaigns.add(campaignId);
    overlapData[query].details.push({
      campaignId: campaignId,
      campaignName: row['CampaignName'],
      clicks: parseInt(row['Clicks'], 10),
      impressions: parseInt(row['Impressions'], 10),
      cost: parseFloat(row['Cost'])
    });
  }

  // Identify overlapping search terms that appear in multiple campaigns
  var overlappingTerms = Object.keys(overlapData).filter(function(query) {
    var campaignCount = overlapData[query].campaigns.size;
    return campaignCount > 1;
  });

  // Proceed to update the Google Sheet if there are overlapping terms
  if (overlappingTerms.length > 0) {
    updateGoogleSheet(sheetUrl, overlappingTerms, overlapData);
  } else {
    Logger.log('No overlapping terms found.');
  }
}

function updateGoogleSheet(sheetUrl, overlappingTerms, overlapData) {
  var sheet = SpreadsheetApp.openByUrl(sheetUrl).getActiveSheet();
  sheet.clearContents(); // Clear the existing contents before appending new data
  sheet.appendRow(['Search Term', 'Campaign ID', 'Campaign Name', 'Clicks', 'Impressions', 'CTR (%)', 'CPC (EUR)', 'Cost (EUR)']); // Append the header

  overlappingTerms.forEach(function(term) {
    overlapData[term].details.forEach(function(detail) { // Make sure to iterate over details
      var ctr = detail.impressions > 0 ? (detail.clicks / detail.impressions) * 100 : 0;
      var cpc = detail.clicks > 0 ? (detail.cost / detail.clicks) : 0;
      sheet.appendRow([
        term,
        detail.campaignId,
        detail.campaignName,
        detail.clicks,
        detail.impressions,
        ctr.toFixed(2), // CTR
        cpc.toFixed(2), // CPC
        detail.cost.toFixed(2) // Cost
      ]);
    });
  });
}

/**
 * Script by Three Chapter Media | www.threechaptermedia.com
*/

---

This article was written with the support of A.I. technology.