Competitor Search Term Analysis Script for Google Ads

This script automatically extracts Google Ads search terms that are related to competitor keywords and domains from the past 30 days (default setting). It then exports the filtered data to a Google Sheet and sends an email notification with the report link.

The Script

/**
 * Competitor Search Term Analysis
 * This script analyzes search terms, identifies those related to competitors,
 * and exports the data to a Google Sheet.
 */

function main() {
  // Google Sheet URL - Replace with your actual Google Sheet URL
  const SPREADSHEET_URL = "YOUR_GOOGLE_SHEET_URL_HERE";

  // Email settings - Replace with the recipient email address
  const RECIPIENT_EMAIL = "your.email@example.com";
  const EMAIL_SUBJECT = "Google Ads Competitor Search Term Report";
  const EMAIL_BODY = "The latest Google Ads competitor search term report has been generated. You can access it here:\n\n" + SPREADSHEET_URL;

  // Define competitor keywords and domains
  const COMPETITOR_KEYWORDS = ["competitor1", "competitor2", "competitor3"];
  const COMPETITOR_DOMAINS = ["competitor.com", "rivalbrand.com", "example.com"];

  // Date range for search terms (default: last 30 days)
  const DATE_RANGE = "LAST_30_DAYS";  // Modify if needed (e.g., "LAST_7_DAYS", "THIS_MONTH")

  // Open the Google Sheet
  const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const sheet = spreadsheet.getActiveSheet();

  // Clear old data
  sheet.clear();
  sheet.appendRow([
    "Search Term", "Matched Keyword", "Ad Group", "Campaign", 
    "Impressions", "Clicks", "CTR (%)", "CPC (AUD)", "Cost (AUD)", "Conversions"
  ]);

  // Define the correct report query for Search Query Performance
  const reportQuery = `
    SELECT
      Query,
      KeywordTextMatchingQuery,
      AdGroupName,
      CampaignName,
      Impressions,
      Clicks,
      Cost,
      Conversions
    FROM SEARCH_QUERY_PERFORMANCE_REPORT
    WHERE Clicks > 0
    DURING ${DATE_RANGE}
  `;

  const report = AdsApp.report(reportQuery);
  const rows = report.rows();

  let hasData = false; // Track if any data was added

  while (rows.hasNext()) {
    const row = rows.next();

    const searchTerm = row["Query"].toLowerCase();
    const matchedKeyword = row["KeywordTextMatchingQuery"];
    const adGroup = row["AdGroupName"];
    const campaign = row["CampaignName"];
    const impressions = parseInt(row["Impressions"]);
    const clicks = parseInt(row["Clicks"]);
    const cost = parseFloat(row["Cost"]) / 1000000; // Convert micros to AUD
    const conversions = parseFloat(row["Conversions"]);

    // Calculate CTR and CPC
    const ctr = (clicks / impressions) * 100 || 0;
    const cpc = clicks > 0 ? cost / clicks : 0;

    // Check if search term matches competitor keywords or domains
    if (
      COMPETITOR_KEYWORDS.some(keyword => searchTerm.includes(keyword.toLowerCase())) ||
      COMPETITOR_DOMAINS.some(domain => searchTerm.includes(domain.toLowerCase()))
    ) {
      sheet.appendRow([
        searchTerm, matchedKeyword, adGroup, campaign,
        impressions, clicks, ctr.toFixed(2), cpc.toFixed(2), cost.toFixed(2), conversions
      ]);
      hasData = true;
    }
  }

  // Send email notification if data was added
  if (hasData) {
    MailApp.sendEmail(RECIPIENT_EMAIL, EMAIL_SUBJECT, EMAIL_BODY);
    Logger.log("Report generated and email sent.");
  } else {
    Logger.log("No competitor search terms found. No email sent.");
  }
}

How It Works

1. Retrieves search term data from the SEARCH_QUERY_PERFORMANCE_REPORT in Google Ads.
2. Filters search terms that match a predefined list of competitor keywords or domains.
3. Extracts key metrics, including:
   • Search term
   • Matched keyword
   • Ad group and campaign names
   • Impressions, clicks, CTR, CPC, cost, and conversions
4. Saves the results to a Google Sheet, replacing old data.
5. Emails the report link to a specified recipient, but only if competitor search terms were found.

Customisation Options

• Change the competitor keyword and domain list to match your business needs.
• Modify the date range (e.g., LAST_7_DAYS, THIS_MONTH).
• Set a different Google Sheets URL for report storage.
• Specify an email recipient for automatic notifications.