Get a demo

Solutions

Pricing

Documentation

Blog

Sign up

Login

Menu

Menu

Search term fragmentation script

Feb 9, 2026

This Google Ads Script analyzes duplicate search terms across Search, Shopping, PMax, DSA and AI Max campaigns and outputs actionable insights to Google Sheets.

Search term fragmentation, where the same query triggers ads from multiple campaigns or ad groups, is one of the most common yet overlooked sources of potential insights and optimization. There's Shopping, Pmax, AI max, DSA and Search and in all of them the same search term can be triggered.

In addition, duplicate search queries across the same or similar campaign types can exist. If you have an intended search term in campaign A against a target ROAS of 2 and now it triggers in two seperate search campaigns with a target ROAS of 4 and 5, then your bidding is not aligned.

This script automatically detects fragmented search terms across your entire account, including Search, Shopping, Performance Max, and AI Max campaigns, and outputs a full analysis to Google Sheets with charts and actionable breakdowns. This gives you an easy overview of how the search term is performing across different campaign types, where you have duplicates and so forth.

Not interested in the instructions/best practices? I hear you, scroll down for the full script and jump into it.

What the script does

The Search Term Fragmentation Analysis script uses the campaign_search_term_view resource to pull search term data across all campaign types, not just Search campaigns. It then:

  1. Groups all search terms across campaigns and ad groups

  2. Identifies terms appearing in multiple campaigns and/or ad groups

  3. Calculates overlap distributions (how many terms appear in 1, 2, 3+ campaigns)

  4. Outputs everything to a formatted Google Sheet with pie charts and detailed breakdowns

Configuration

The script is fully configurable at the top of the file. Here's the configuration block:

// Spreadsheet URL - leave empty to create new, or paste existing URL
var SPREADSHEET_URL = '';

// Impression Threshold
var IMPRESSION_THRESHOLD = 0;

// Date Range
// Options: 'LAST_7_DAYS', 'LAST_14_DAYS', 'LAST_30_DAYS', 'THIS_MONTH', 'LAST_MONTH', 'CUSTOM'
var DATE_RANGE = 'LAST_30_DAYS';

// Custom date range (only used if DATE_RANGE = 'CUSTOM')
var CUSTOM_START_DATE = '2025-01-01';
var CUSTOM_END_DATE = '2025-12-31';

// Campaign Types to Include
// Options: 'SEARCH', 'SHOPPING', 'PERFORMANCE_MAX', 'AI_MAX'
var CAMPAIGN_TYPES = ['SEARCH', 'AI_MAX'];

// Exclude Paused Entities
var EXCLUDE_PAUSED_CAMPAIGNS = false;
var EXCLUDE_PAUSED_AD_GROUPS = false;

// Minimum fragmentation to highlight
var MIN_CAMPAIGNS_FOR_HIGHLIGHT = 2;
var MIN_ADGROUPS_FOR_HIGHLIGHT = 3;

Key options explained:

  • CAMPAIGN_TYPES: Choose which campaign types to include. Adding 'AI_MAX' automatically detects AI Max for Search campaigns via campaign.ai_max_setting.enable_ai_max and reports them separately from regular Search.

  • IMPRESSION_THRESHOLD: Filter out low-volume terms to focus on meaningful fragmentation.

  • EXCLUDE_PAUSED: Optionally filter out paused campaigns or ad groups so they don't count toward fragmentation. Note: status reflects the current state, not historical.

Output: what you get

The script creates a Google Sheet with three tabs:

  1. Summary

High-level metrics at a glance:

  • Total unique search terms analyzed

  • Terms appearing in multiple campaigns (count and percentage)

  • Terms appearing in multiple ad groups

  • Total cost attributed to fragmented terms

  • Four pie charts showing count and spend distributions by campaign and ad group overlap

  1. Fragmented terms

A sortable table of all search terms appearing in 2+ campaigns, with columns for:

  • Number of campaigns and ad groups

  • Channel types involved (Search, PMax, Shopping, AI Max)

  • Impressions, clicks, cost, conversions, and CPA

  1. Term breakdown

A detailed drilldown view with Summary and Detail rows for each fragmented term. Summary rows show totals; Detail sub-rows show the per-campaign/ad-group split. You can filter by Row Type to toggle between views.

Best practices

Create different variants of the script:

  • Use the campaign type filter and set it only to Search to understand unintended and unwanted duplicates of search terms across multiple Search ad groups or campaigns

  • Use the campaign type filter and set it to Search, Shopping to understand search terms that are doing well in Shopping, but are getting no volume in Search and vice versa.

  • Use all campaign types and understand performance of your search term across all the different campaign types it gets matched against

    • Filter for campaign type: "AI_MAX" and "SEARCH" to understand overlap between these two campaign types

  • Set the campaign type filter to only DSA and Search, and identify any unwanted matches that are happening in DSA that should be part of only regular search campaigns.

  • Search vs PMax. The #1 most common overlap concern. PMax is notorious for cannibalizing branded and high-value search terms.

  • Search Ecosystem (Search + AI Max + DSA. All search-intent campaign types together, without Shopping/PMax noise. Gives a complete view of your search
    funnel.

  • PMax vs Shopping. For accounts running both Standard Shopping and PMax. Identifies product-level competition between the two.

Getting started

  1. Copy the script (see below) into your Google Ads account under Tools > Scripts

  2. Update the SPREADSHEET_URL with your own Google Sheet (or leave empty to auto-create)

  3. Adjust the configuration to match your needs

  4. Run the script and preview first to check for errors

  5. Open the generated spreadsheet and start optimizing


    //------------------------------------------------------------------------------
// Search Term Fragmentation Analysis Script
// Analyzes how search terms are matched across multiple ad groups and campaigns.
// Outputs analysis to Google Sheets with data tables and charts.
// © Adnine
//------------------------------------------------------------------------------

function main() {
  Logger.log('Beep boop Search Term Fragmentation Analysis starting...');

  // ============================================================================
  // CONFIGURATION
  // ============================================================================

  // Spreadsheet URL - paste spreadsheet URL here (can be an empty spreadsheet)
  var SPREADSHEET_URL = '';

  // Impression Threshold
  // Only search terms with impressions GREATER than this number will be analyzed.
  var IMPRESSION_THRESHOLD = 0;

  // Date Range
  // Options: 'LAST_7_DAYS', 'LAST_14_DAYS', 'LAST_30_DAYS', 'THIS_MONTH', 'LAST_MONTH', 'CUSTOM'
  var DATE_RANGE = 'LAST_30_DAYS';

  // Custom date range (only used if DATE_RANGE = 'CUSTOM')
  var CUSTOM_START_DATE = '2025-01-01';
  var CUSTOM_END_DATE = '2026-01-31';

  // Campaign Types to Include
  // Options: 'SEARCH', 'SHOPPING', 'PERFORMANCE_MAX', 'AI_MAX', 'DSA'

  var CAMPAIGN_TYPES = ['SEARCH', 'SHOPPING', 'PERFORMANCE_MAX', 'AI_MAX', 'DSA'];

  // Exclude Paused Entities
  // Set to true to filter out paused campaigns and/or ad groups from the analysis.
  // Paused entities will not count toward fragmentation.
  // Note: Status reflects the CURRENT state, not historical status during the date range.
  var EXCLUDE_PAUSED_CAMPAIGNS = false;
  var EXCLUDE_PAUSED_AD_GROUPS = false;

  // Minimum fragmentation to highlight on FRAGMENTED_TERMS tab
  // Search terms appearing in this many or more campaigns/ad groups will be shown (OR logic)
  var MIN_CAMPAIGNS_FOR_HIGHLIGHT = 2;
  var MIN_ADGROUPS_FOR_HIGHLIGHT = 2;

  // ============================================================================
  // MAIN EXECUTION
  // ============================================================================

  var spreadsheet = getOrCreateSpreadsheet(SPREADSHEET_URL);
  Logger.log('Using spreadsheet: ' + spreadsheet.getUrl());

  var dateRangeStr = getDateRangeString(DATE_RANGE, CUSTOM_START_DATE, CUSTOM_END_DATE);
  Logger.log('Fetching search terms for: ' + dateRangeStr);
  Logger.log('Campaign types: ' + CAMPAIGN_TYPES.join(', '));

  var searchTermsData = fetchSearchTermsReport(DATE_RANGE, CUSTOM_START_DATE, CUSTOM_END_DATE, IMPRESSION_THRESHOLD, CAMPAIGN_TYPES, EXCLUDE_PAUSED_CAMPAIGNS, EXCLUDE_PAUSED_AD_GROUPS);
  Logger.log('Fetched ' + searchTermsData.length + ' search term rows');

  if (searchTermsData.length === 0) {
    Logger.log('ERROR: No search terms data found. Check campaign filters or lower the impression threshold.');
    return;
  }

  var analysis = runFragmentationAnalysis(searchTermsData);
  Logger.log('Unique search terms: ' + analysis.totalUniqueTerms);
  Logger.log('Terms in multiple campaigns: ' + analysis.termsInMultipleCampaigns);
  Logger.log('Terms in multiple ad groups: ' + analysis.termsInMultipleAdGroups);

  var typeLabels = CAMPAIGN_TYPES.map(getChannelLabel).join(', ');
  writeToSpreadsheet(spreadsheet, analysis, dateRangeStr, MIN_CAMPAIGNS_FOR_HIGHLIGHT, MIN_ADGROUPS_FOR_HIGHLIGHT, typeLabels, CAMPAIGN_TYPES);

  Logger.log('Analysis complete! View results: ' + spreadsheet.getUrl());
}

// ============================================================================
// DATA FETCHING
// ============================================================================

function fetchAiMaxCampaignIds() {
  var ids = {};
  try {
    var query = 'SELECT campaign.id, campaign.name ' +
      'FROM campaign ' +
      'WHERE campaign.advertising_channel_type = "SEARCH" ' +
      'AND campaign.ai_max_setting.enable_ai_max = true';
    var report = AdsApp.search(query);
    while (report.hasNext()) {
      var row = report.next();
      ids[row.campaign.id] = row.campaign.name;
    }
    var count = Object.keys(ids).length;
    Logger.log('AI Max detection: found ' + count + ' AI Max campaign(s)');
    if (count > 0) {
      var names = [];
      var keys = Object.keys(ids);
      for (var i = 0; i < Math.min(keys.length, 5); i++) {
        names.push(ids[keys[i]]);
      }
      Logger.log('  Examples: ' + names.join(', ') + (count > 5 ? '...' : ''));
    }
  } catch (e) {
    Logger.log('Warning: Could not detect AI Max campaigns. ' +
      'The ai_max_setting field may require Google Ads API v21+. ' +
      'Error: ' + e.toString());
  }
  return ids;
}

function fetchDsaAdGroupIds() {
  var ids = {};
  try {
    var query = 'SELECT ad_group.id, ad_group.name, campaign.id, campaign.name ' +
      'FROM ad_group ' +
      'WHERE ad_group.type = "SEARCH_DYNAMIC_ADS"';
    var report = AdsApp.search(query);
    while (report.hasNext()) {
      var row = report.next();
      ids[row.adGroup.id] = row.adGroup.name;
    }
    var count = Object.keys(ids).length;
    Logger.log('DSA detection: found ' + count + ' DSA ad group(s)');
    if (count > 0) {
      var names = [];
      var keys = Object.keys(ids);
      for (var i = 0; i < Math.min(keys.length, 5); i++) {
        names.push(ids[keys[i]]);
      }
      Logger.log('  Examples: ' + names.join(', ') + (count > 5 ? '...' : ''));
    }
  } catch (e) {
    Logger.log('Warning: Could not detect DSA ad groups. Error: ' + e.toString());
  }
  return ids;
}

function fetchSearchTermsReport(dateRange, customStart, customEnd, threshold, campaignTypes, excludePausedCampaigns, excludePausedAdGroups) {
  // If AI_MAX is in the campaign types list, detect AI Max campaigns automatically
  var separateAiMax = campaignTypes && campaignTypes.indexOf('AI_MAX') !== -1;
  var aiMaxCampaignIds = {};
  if (separateAiMax) {
    aiMaxCampaignIds = fetchAiMaxCampaignIds();
  }

  // If DSA is in the campaign types list, detect DSA ad groups automatically
  var separateDsa = campaignTypes && campaignTypes.indexOf('DSA') !== -1;
  var dsaAdGroupIds = {};
  if (separateDsa) {
    dsaAdGroupIds = fetchDsaAdGroupIds();
  }

  var query = 'SELECT ' +
    'campaign_search_term_view.search_term, ' +
    'campaign.id, ' +
    'campaign.name, ' +
    'campaign.status, ' +
    'campaign.advertising_channel_type, ' +
    'ad_group.id, ' +
    'ad_group.name, ' +
    'ad_group.status, ' +
    'metrics.impressions, ' +
    'metrics.clicks, ' +
    'metrics.cost_micros, ' +
    'metrics.conversions, ' +
    'metrics.conversions_value ' +
    'FROM campaign_search_term_view ' +
    'WHERE segments.date DURING ' + dateRange + ' ' +
    'AND metrics.impressions > ' + threshold;

  if (excludePausedCampaigns) {
    query += " AND campaign.status = 'ENABLED'";
  }

  if (dateRange === 'CUSTOM') {
    query = query.replace('DURING ' + dateRange,
      "BETWEEN '" + customStart + "' AND '" + customEnd + "'");
  }

  var results = [];

  try {
    var report = AdsApp.search(query);

    while (report.hasNext()) {
      var row = report.next();

      var channelType = row.campaign.advertisingChannelType || '';

      // Read ad group info first (needed for DSA detection)
      var adGroupId = '';
      var adGroupName = '';
      var adGroupStatus = '';
      try {
        adGroupId = row.adGroup.id || '';
        adGroupName = row.adGroup.name || '';
        adGroupStatus = row.adGroup.status || '';
      } catch (e) {
        adGroupId = 'pmax_' + row.campaign.id;
        adGroupName = '(PMax - no ad group)';
        adGroupStatus = 'ENABLED';
      }

      // Reclassify DSA ad groups (check before AI Max since DSA is more specific)
      if (separateDsa && channelType === 'SEARCH' && dsaAdGroupIds[adGroupId]) {
        channelType = 'DSA';
      }
      // Reclassify AI Max campaigns as a separate channel type
      else if (separateAiMax && channelType === 'SEARCH' && aiMaxCampaignIds[row.campaign.id]) {
        channelType = 'AI_MAX';
      }

      // Filter by campaign types
      if (campaignTypes && campaignTypes.length > 0 && campaignTypes.indexOf(channelType) === -1) {
        continue;
      }

      // Filter paused ad groups
      if (excludePausedAdGroups && adGroupStatus === 'PAUSED') {
        continue;
      }

      results.push({
        searchTerm: row.campaignSearchTermView.searchTerm || '',
        campaignId: row.campaign.id || '',
        campaign: row.campaign.name || '',
        channelType: channelType,
        adGroupId: adGroupId,
        adGroup: adGroupName,
        impressions: parseInt(row.metrics.impressions) || 0,
        clicks: parseInt(row.metrics.clicks) || 0,
        cost: (parseInt(row.metrics.costMicros) || 0) / 1000000,
        conversions: parseFloat(row.metrics.conversions) || 0,
        conversionValue: parseFloat(row.metrics.conversionsValue) || 0
      });
    }
  } catch (e) {
    Logger.log('Error fetching search terms: ' + e.toString());
  }

  return results;
}

function getDateRangeString(dateRange, customStart, customEnd) {
  if (dateRange === 'CUSTOM') {
    return customStart + ' to ' + customEnd;
  }
  return dateRange.replace(/_/g, ' ').toLowerCase();
}

// ============================================================================
// ANALYSIS FUNCTIONS
// ============================================================================

function runFragmentationAnalysis(data) {
  // Group by search term
  var termData = {};

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var term = row.searchTerm.toLowerCase().trim();

    if (!termData[term]) {
      termData[term] = {
        originalTerm: row.searchTerm,
        campaigns: {},
        adGroups: {},
        channelTypes: {},
        totalImpressions: 0,
        totalClicks: 0,
        totalCost: 0,
        totalConversions: 0,
        totalConversionValue: 0,
        rows: []
      };
    }

    var td = termData[term];

    // Track unique campaigns
    var campaignKey = row.campaignId;
    if (!td.campaigns[campaignKey]) {
      td.campaigns[campaignKey] = {
        id: row.campaignId,
        name: row.campaign,
        channelType: row.channelType,
        impressions: 0,
        clicks: 0,
        cost: 0,
        conversions: 0,
        conversionValue: 0
      };
    }
    td.campaigns[campaignKey].impressions += row.impressions;
    td.campaigns[campaignKey].clicks += row.clicks;
    td.campaigns[campaignKey].cost += row.cost;
    td.campaigns[campaignKey].conversions += row.conversions;
    td.campaigns[campaignKey].conversionValue += row.conversionValue;

    // Track unique ad groups
    var adGroupKey = row.campaignId + '_' + row.adGroupId;
    if (!td.adGroups[adGroupKey]) {
      td.adGroups[adGroupKey] = {
        id: row.adGroupId,
        name: row.adGroup,
        campaign: row.campaign,
        channelType: row.channelType,
        impressions: 0,
        clicks: 0,
        cost: 0,
        conversions: 0,
        conversionValue: 0
      };
    }
    td.adGroups[adGroupKey].impressions += row.impressions;
    td.adGroups[adGroupKey].clicks += row.clicks;
    td.adGroups[adGroupKey].cost += row.cost;
    td.adGroups[adGroupKey].conversions += row.conversions;
    td.adGroups[adGroupKey].conversionValue += row.conversionValue;

    // Track channel types
    if (!td.channelTypes[row.channelType]) {
      td.channelTypes[row.channelType] = { impressions: 0, clicks: 0, cost: 0, conversions: 0, conversionValue: 0 };
    }
    td.channelTypes[row.channelType].impressions += row.impressions;
    td.channelTypes[row.channelType].clicks += row.clicks;
    td.channelTypes[row.channelType].cost += row.cost;
    td.channelTypes[row.channelType].conversions += row.conversions;
    td.channelTypes[row.channelType].conversionValue += row.conversionValue;

    // Aggregate totals
    td.totalImpressions += row.impressions;
    td.totalClicks += row.clicks;
    td.totalCost += row.cost;
    td.totalConversions += row.conversions;
    td.totalConversionValue += row.conversionValue;
    td.rows.push(row);
  }

  // Build analysis results
  var terms = Object.keys(termData);
  var fragmentedTerms = [];
  var campaignDistribution = {}; // How many terms appear in X campaigns
  var adGroupDistribution = {}; // How many terms appear in X ad groups
  var campaignSpendDistribution = {}; // Total spend for terms in X campaigns
  var adGroupSpendDistribution = {}; // Total spend for terms in X ad groups

  for (var i = 0; i < terms.length; i++) {
    var term = terms[i];
    var td = termData[term];
    var numCampaigns = Object.keys(td.campaigns).length;
    var numAdGroups = Object.keys(td.adGroups).length;
    var channelTypeList = Object.keys(td.channelTypes);

    // Track distribution (count and spend)
    campaignDistribution[numCampaigns] = (campaignDistribution[numCampaigns] || 0) + 1;
    adGroupDistribution[numAdGroups] = (adGroupDistribution[numAdGroups] || 0) + 1;
    campaignSpendDistribution[numCampaigns] = (campaignSpendDistribution[numCampaigns] || 0) + td.totalCost;
    adGroupSpendDistribution[numAdGroups] = (adGroupSpendDistribution[numAdGroups] || 0) + td.totalCost;

    // Build fragmented term entry
    fragmentedTerms.push({
      term: td.originalTerm,
      numCampaigns: numCampaigns,
      numAdGroups: numAdGroups,
      channelTypes: channelTypeList,
      channelTypeMetrics: td.channelTypes,
      campaigns: td.campaigns,
      adGroups: td.adGroups,
      impressions: td.totalImpressions,
      clicks: td.totalClicks,
      cost: td.totalCost,
      conversions: td.totalConversions,
      conversionValue: td.totalConversionValue,
      cpa: td.totalConversions > 0 ? td.totalCost / td.totalConversions : 0,
      roas: td.totalCost > 0 ? td.totalConversionValue / td.totalCost : 0
    });
  }

  // Sort by number of campaigns (most fragmented first), then by cost
  fragmentedTerms.sort(function(a, b) {
    if (b.numCampaigns !== a.numCampaigns) {
      return b.numCampaigns - a.numCampaigns;
    }
    if (b.numAdGroups !== a.numAdGroups) {
      return b.numAdGroups - a.numAdGroups;
    }
    return b.cost - a.cost;
  });

  // Calculate summary stats
  var termsInMultipleCampaigns = 0;
  var termsInMultipleAdGroups = 0;
  var totalCostInFragmentedTerms = 0;

  for (var i = 0; i < fragmentedTerms.length; i++) {
    var ft = fragmentedTerms[i];
    if (ft.numCampaigns > 1) {
      termsInMultipleCampaigns++;
      totalCostInFragmentedTerms += ft.cost;
    }
    if (ft.numAdGroups > 1) {
      termsInMultipleAdGroups++;
    }
  }

  return {
    totalUniqueTerms: terms.length,
    termsInMultipleCampaigns: termsInMultipleCampaigns,
    termsInMultipleAdGroups: termsInMultipleAdGroups,
    totalCostInFragmentedTerms: totalCostInFragmentedTerms,
    campaignDistribution: campaignDistribution,
    adGroupDistribution: adGroupDistribution,
    campaignSpendDistribution: campaignSpendDistribution,
    adGroupSpendDistribution: adGroupSpendDistribution,
    fragmentedTerms: fragmentedTerms
  };
}

// ============================================================================
// SPREADSHEET OUTPUT
// ============================================================================

function getOrCreateSpreadsheet(url) {
  if (url && url.length > 0) {
    try {
      return SpreadsheetApp.openByUrl(url);
    } catch (e) {
      Logger.log('Could not open spreadsheet URL, creating new one.');
    }
  }

  var spreadsheet = SpreadsheetApp.create('Search Term Fragmentation Analysis - ' + formatDateForTitle(new Date()));
  return spreadsheet;
}

function formatDateForTitle(date) {
  return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
}

function writeToSpreadsheet(spreadsheet, analysis, dateRangeStr, minCampaigns, minAdGroups, typeLabels, campaignTypes) {
  // Create temp sheet for deletion workaround
  var tempSheetName = 'Processing_' + new Date().getTime();
  spreadsheet.insertSheet(tempSheetName);

  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var s = sheets[i];
    if (s.getName() !== tempSheetName) {
      spreadsheet.deleteSheet(s);
    }
  }

  writeSummarySheet(spreadsheet, analysis, dateRangeStr, typeLabels);
  writeFragmentedTermsSheet(spreadsheet, analysis, minCampaigns, minAdGroups);
  writeTermDetailSheet(spreadsheet, analysis, campaignTypes);

  var tempSheet = spreadsheet.getSheetByName(tempSheetName);
  if (tempSheet) {
    spreadsheet.deleteSheet(tempSheet);
  }
}

function getOrCreateSheet(spreadsheet, name, position) {
  var sheet = spreadsheet.getSheetByName(name);
  if (sheet) {
    sheet.clear();
  } else {
    sheet = spreadsheet.insertSheet(name, position || 0);
  }
  return sheet;
}

function writeSummarySheet(spreadsheet, analysis, dateRangeStr, typeLabels) {
  var sheet = getOrCreateSheet(spreadsheet, 'SUMMARY', 0);

  sheet.getRange('A1').setValue('SEARCH TERM FRAGMENTATION ANALYSIS');
  sheet.getRange('A1').setFontSize(20).setFontWeight('bold').setFontColor('#1E3A5F');

  sheet.getRange('A2').setValue('Period: ' + dateRangeStr + ' | Campaign types: ' + typeLabels);
  sheet.getRange('A2').setFontSize(11).setFontColor('#666666');

  // Key metrics
  sheet.getRange('A4').setValue('Overview');
  sheet.getRange('A4').setFontSize(14).setFontWeight('bold');

  sheet.getRange('A5:B5').setValues([['Metric', 'Value']]);
  sheet.getRange('A5:B5').setFontWeight('bold').setBackground('#1E3A5F').setFontColor('#FFFFFF');

  var pctMultipleCampaigns = analysis.totalUniqueTerms > 0 ?
    (analysis.termsInMultipleCampaigns / analysis.totalUniqueTerms * 100) : 0;
  var pctMultipleAdGroups = analysis.totalUniqueTerms > 0 ?
    (analysis.termsInMultipleAdGroups / analysis.totalUniqueTerms * 100) : 0;

  var overviewData = [
    ['Total unique search terms', formatNumber(analysis.totalUniqueTerms)],
    ['Terms in multiple campaigns', formatNumber(analysis.termsInMultipleCampaigns) + ' (' + pctMultipleCampaigns.toFixed(1) + '%)'],
    ['Terms in multiple ad groups', formatNumber(analysis.termsInMultipleAdGroups) + ' (' + pctMultipleAdGroups.toFixed(1) + '%)'],
    ['Cost on multi-campaign terms', formatCurrency(analysis.totalCostInFragmentedTerms)]
  ];

  sheet.getRange('A6:B9').setValues(overviewData);

  // ==========================================================================
  // 2x2 DISTRIBUTION GRID
  // Layout: Left (cols A:B + chart at D) | Right (cols H:I + chart at K)
  // ==========================================================================

  // --- Prepare campaign distribution data ---
  var campaignDist = analysis.campaignDistribution;
  var campaignSpendDist = analysis.campaignSpendDistribution;
  var campaignKeys = Object.keys(campaignDist).sort(function(a, b) { return parseInt(a) - parseInt(b); });
  var campaignCountData = [];
  var campaignSpendData = [];
  for (var i = 0; i < campaignKeys.length; i++) {
    var key = campaignKeys[i];
    var label = key + ' campaign' + (key > 1 ? 's' : '');
    campaignCountData.push([label, campaignDist[key]]);
    campaignSpendData.push([label, campaignSpendDist[key]]);
  }

  // --- Prepare ad group distribution data ---
  var adGroupDist = analysis.adGroupDistribution;
  var adGroupSpendDist = analysis.adGroupSpendDistribution;
  var adGroupKeys = Object.keys(adGroupDist).sort(function(a, b) { return parseInt(a) - parseInt(b); });
  var adGroupCountData = [];
  var adGroupSpendData = [];
  for (var i = 0; i < Math.min(adGroupKeys.length, 15); i++) {
    var key = adGroupKeys[i];
    var label = key + ' ad group' + (key > 1 ? 's' : '');
    adGroupCountData.push([label, adGroupDist[key]]);
    adGroupSpendData.push([label, adGroupSpendDist[key]]);
  }
  if (adGroupKeys.length > 15) {
    var remainingCount = 0;
    var remainingSpend = 0;
    for (var i = 15; i < adGroupKeys.length; i++) {
      remainingCount += adGroupDist[adGroupKeys[i]];
      remainingSpend += adGroupSpendDist[adGroupKeys[i]];
    }
    adGroupCountData.push(['16+ ad groups', remainingCount]);
    adGroupSpendData.push(['16+ ad groups', remainingSpend]);
  }

  // --- Top row: Campaign overlap ---
  var topRow = 12;

  writeDistSection(sheet, topRow, 1,
    'Term Count (by Campaign Overlap)',
    'Number of search terms appearing in 1, 2, 3... campaigns',
    'Overlap', '# Terms', campaignCountData, '#,##0',
    4, 'Term Count (Campaigns)');

  writeDistSection(sheet, topRow, 10,
    'Term Spend (by Campaign Overlap)',
    'Total spend on search terms, grouped by how many campaigns they appear in',
    'Overlap', 'Total Spend', campaignSpendData, '$#,##0.00',
    13, 'Spend (Campaigns)');

  // --- Bottom row: Ad group overlap ---
  var topDataRows = Math.max(campaignCountData.length, campaignSpendData.length);
  var topSectionHeight = Math.max(topDataRows + 3, 17); // +3 for title+subtitle+header, 17 for chart clearance
  var bottomRow = topRow + topSectionHeight + 4;

  writeDistSection(sheet, bottomRow, 1,
    'Term Count (by Ad Group Overlap)',
    'Number of search terms appearing in 1, 2, 3... ad groups',
    'Overlap', '# Terms', adGroupCountData, '#,##0',
    4, 'Term Count (Ad Groups)');

  writeDistSection(sheet, bottomRow, 10,
    'Term Spend (by Ad Group Overlap)',
    'Total spend on search terms, grouped by how many ad groups they appear in',
    'Overlap', 'Total Spend', adGroupSpendData, '$#,##0.00',
    13, 'Spend (Ad Groups)');

  // Column widths
  sheet.setColumnWidth(1, 200);
  sheet.setColumnWidth(2, 120);
  sheet.setColumnWidth(10, 200);
  sheet.setColumnWidth(11, 120);
}

function writeDistSection(sheet, startRow, startCol, title, subtitle, header1, header2, data, valueFormat, chartCol, chartTitle) {
  sheet.getRange(startRow, startCol).setValue(title);
  sheet.getRange(startRow, startCol).setFontSize(13).setFontWeight('bold');

  sheet.getRange(startRow + 1, startCol).setValue(subtitle);
  sheet.getRange(startRow + 1, startCol).setFontSize(10).setFontColor('#666666');

  var headerRow = startRow + 2;
  sheet.getRange(headerRow, startCol, 1, 2).setValues([[header1, header2]]);
  sheet.getRange(headerRow, startCol, 1, 2).setFontWeight('bold').setBackground('#1E3A5F').setFontColor('#FFFFFF');

  if (data.length > 0) {
    var dataStartRow = startRow + 3;
    sheet.getRange(dataStartRow, startCol, data.length, 2).setValues(data);
    sheet.getRange(dataStartRow, startCol + 1, data.length, 1).setNumberFormat(valueFormat);

    var chart = sheet.newChart()
      .setChartType(Charts.ChartType.PIE)
      .addRange(sheet.getRange(dataStartRow, startCol, data.length, 2))
      .setPosition(startRow, chartCol, 0, 0)
      .setOption('title', chartTitle)
      .setOption('width', 400)
      .setOption('height', 250)
      .build();
    sheet.insertChart(chart);
  }
}

function writeFragmentedTermsSheet(spreadsheet, analysis, minCampaigns, minAdGroups) {
  var sheet = getOrCreateSheet(spreadsheet, 'FRAGMENTED_TERMS', 1);

  sheet.getRange('A1').setValue('Most Fragmented Search Terms');
  sheet.getRange('A1').setFontSize(16).setFontWeight('bold');

  sheet.getRange('A2').setValue('Search terms appearing in ' + minCampaigns + '+ campaigns or ' + minAdGroups + '+ ad groups, sorted by clicks');
  sheet.getRange('A2').setFontSize(11).setFontColor('#666666');

  sheet.getRange('A4:K4').setValues([[
    'Search Term', '# Campaigns', '# Ad Groups', 'Channel Types',
    'Impressions', 'Clicks', 'Cost', 'CPC', 'Conversions', 'CPA', 'ROAS'
  ]]);
  sheet.getRange('A4:K4').setFontWeight('bold').setBackground('#1E3A5F').setFontColor('#FFFFFF');

  // Filter to terms in multiple campaigns OR multiple ad groups
  var fragmented = analysis.fragmentedTerms.filter(function(t) {
    return t.numCampaigns >= minCampaigns || t.numAdGroups >= minAdGroups;
  });

  // Sort by clicks for truncation
  fragmented.sort(function(a, b) { return b.clicks - a.clicks; });

  if (fragmented.length > 0) {
    var data = [];
    for (var i = 0; i < fragmented.length; i++) {
      var t = fragmented[i];
      var channelTypeStr = t.channelTypes.map(getChannelLabel).join(', ');

      data.push([
        t.term,
        t.numCampaigns,
        t.numAdGroups,
        channelTypeStr,
        t.impressions,
        t.clicks,
        t.cost,
        t.clicks > 0 ? t.cost / t.clicks : 0,
        t.conversions,
        t.conversions > 0 ? t.cpa : 0,
        t.cost > 0 ? t.roas : 0
      ]);
    }

    sheet.getRange(5, 1, data.length, 11).setValues(data);

    // Format columns
    sheet.getRange(5, 5, data.length, 2).setNumberFormat('#,##0');      // Impressions, Clicks
    sheet.getRange(5, 7, data.length, 1).setNumberFormat('$#,##0.00');  // Cost
    sheet.getRange(5, 8, data.length, 1).setNumberFormat('$#,##0.00');  // CPC
    sheet.getRange(5, 9, data.length, 1).setNumberFormat('#,##0.0');    // Conversions
    sheet.getRange(5, 10, data.length, 1).setNumberFormat('$#,##0.00'); // CPA
    sheet.getRange(5, 11, data.length, 1).setNumberFormat('#,##0.00x'); // ROAS

    Logger.log('FRAGMENTED_TERMS: wrote ' + data.length + ' rows');
  } else {
    sheet.getRange('A5').setValue('No fragmented terms found (no terms in ' + minCampaigns + '+ campaigns or ' + minAdGroups + '+ ad groups)');
    sheet.getRange('A5').setFontStyle('italic').setFontColor('#666666');
  }

  sheet.setFrozenRows(4);
  sheet.setColumnWidth(1, 300);
  sheet.setColumnWidth(2, 100);
  sheet.setColumnWidth(3, 100);
  sheet.setColumnWidth(4, 150);
  sheet.setColumnWidth(5, 100);
  sheet.setColumnWidth(6, 80);
  sheet.setColumnWidth(7, 100);
  sheet.setColumnWidth(8, 80);
  sheet.setColumnWidth(9, 100);
  sheet.setColumnWidth(10, 80);
  sheet.setColumnWidth(11, 80);
}

function writeTermDetailSheet(spreadsheet, analysis, campaignTypes) {
  var sheet = getOrCreateSheet(spreadsheet, 'TERM_BREAKDOWN', 2);
  var DARK = '#1E3A5F';
  var WHITE = '#FFFFFF';
  var DETAIL_FONT_COLOR = '#70757A';
  var NUM_COLS = 13;

  sheet.getRange('A1').setValue('Search Term Campaign/Ad Group Breakdown');
  sheet.getRange('A1').setFontSize(16).setFontWeight('bold');

  sheet.getRange('A2').setValue('Top 5,000 search terms by cost. Each term has a Summary row with totals. Detail sub-rows show the per-campaign/ad-group split. Gap rows show campaign types with no volume. Filter Row Type to "Summary" for a clean view, or "Gap" to find coverage gaps.');
  sheet.getRange('A2').setFontSize(10).setFontColor('#666666');

  sheet.getRange(4, 1, 1, NUM_COLS).setValues([[
    'Row Type', 'Search Term', 'Campaign', 'Ad Group', 'Channel Type',
    'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conv. Value', 'CPC', 'CPA', 'ROAS'
  ]]);
  sheet.getRange(4, 1, 1, NUM_COLS).setFontWeight('bold').setBackground(DARK).setFontColor(WHITE).setFontSize(9);
  sheet.getRange(4, 1, 1, 1).setBackground('#D7CCC8');

  // All search terms, limited to top 5000 by cost
  var hasGaps = campaignTypes && campaignTypes.length > 1;
  var fragmented = analysis.fragmentedTerms.slice();

  fragmented.sort(function(a, b) { return b.cost - a.cost; });
  fragmented = fragmented.slice(0, 5000);

  var data = [];
  var detailRowIndices = [];

  for (var i = 0; i < fragmented.length; i++) {
    var t = fragmented[i];
    var channelTypeStr = t.channelTypes.map(getChannelLabel).join(', ');
    var campaignNames = {};
    var adGroupEntries = Object.values(t.adGroups);
    for (var j = 0; j < adGroupEntries.length; j++) {
      campaignNames[adGroupEntries[j].campaign] = true;
    }
    var campaignCount = Object.keys(campaignNames).length;
    var campaignDisplay = campaignCount === 1 ? Object.keys(campaignNames)[0] : 'Multiple (' + campaignCount + ')';
    var adGroupDisplay = adGroupEntries.length === 1 ? adGroupEntries[0].name : 'Multiple (' + adGroupEntries.length + ')';

    // Summary row
    data.push([
      'Summary',
      t.term,
      campaignDisplay,
      adGroupDisplay,
      channelTypeStr,
      t.impressions,
      t.clicks,
      t.cost,
      t.conversions,
      t.conversionValue,
      t.clicks > 0 ? t.cost / t.clicks : 0,
      t.conversions > 0 ? t.cpa : 0,
      t.cost > 0 ? t.roas : 0
    ]);

    // Detail sub-rows (per campaign/ad-group)
    adGroupEntries.sort(function(a, b) { return b.cost - a.cost; });
    for (var j = 0; j < adGroupEntries.length; j++) {
      var ag = adGroupEntries[j];
      detailRowIndices.push(data.length);
      data.push([
        'Detail',
        '',
        ag.campaign,
        ag.name,
        getChannelLabel(ag.channelType),
        ag.impressions,
        ag.clicks,
        ag.cost,
        ag.conversions,
        ag.conversionValue,
        ag.clicks > 0 ? ag.cost / ag.clicks : 0,
        ag.conversions > 0 ? ag.cost / ag.conversions : 0,
        ag.cost > 0 ? ag.conversionValue / ag.cost : 0
      ]);
    }

    // Gap rows for missing channel types
    if (hasGaps) {
      for (var j = 0; j < campaignTypes.length; j++) {
        if (t.channelTypes.indexOf(campaignTypes[j]) === -1) {
          detailRowIndices.push(data.length);
          data.push([
            'Gap',
            '',
            '—',
            '—',
            getChannelLabel(campaignTypes[j]),
            0, 0, 0, 0, 0, 0, 0, 0
          ]);
        }
      }
    }

  }

  if (data.length > 0) {
    var dataStartRow = 5;
    sheet.getRange(dataStartRow, 1, data.length, NUM_COLS).setValues(data);
    var dl = data.length;

    // Number formats
    sheet.getRange(dataStartRow, 6, dl, 2).setNumberFormat('#,##0');       // Impressions, Clicks
    sheet.getRange(dataStartRow, 8, dl, 1).setNumberFormat('$#,##0.00');   // Cost
    sheet.getRange(dataStartRow, 9, dl, 1).setNumberFormat('#,##0.0');     // Conversions
    sheet.getRange(dataStartRow, 10, dl, 1).setNumberFormat('$#,##0.00'); // Conv. Value
    sheet.getRange(dataStartRow, 11, dl, 1).setNumberFormat('$#,##0.00'); // CPC
    sheet.getRange(dataStartRow, 12, dl, 1).setNumberFormat('$#,##0.00'); // CPA
    sheet.getRange(dataStartRow, 13, dl, 1).setNumberFormat('#,##0.00x'); // ROAS

    // Conditional formatting for Row Type column
    var rules = sheet.getConditionalFormatRules();
    var rowTypeRange = sheet.getRange(dataStartRow, 1, dl, 1);
    rules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenTextEqualTo('Summary').setBackground('#E3F2FD').setRanges([rowTypeRange]).build());
    rules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenTextEqualTo('Detail').setBackground('#F5F5F5').setRanges([rowTypeRange]).build());
    rules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenTextEqualTo('Gap').setBackground('#FFF3E0').setFontColor('#E65100').setRanges([rowTypeRange]).build());
    sheet.setConditionalFormatRules(rules);

    // Style detail rows — lighter font color + italic
    for (var i = 0; i < detailRowIndices.length; i++) {
      var sheetRow = dataStartRow + detailRowIndices[i];
      sheet.getRange(sheetRow, 1, 1, NUM_COLS).setFontColor(DETAIL_FONT_COLOR).setFontStyle('italic');
    }

    // Enable filter
    sheet.getRange(4, 1, dl + 1, NUM_COLS).createFilter();
  }

  sheet.setFrozenRows(4);
  sheet.setFrozenColumns(2);
  sheet.setColumnWidth(1, 90);
  sheet.setColumnWidth(2, 280);
  sheet.setColumnWidth(3, 200);
  sheet.setColumnWidth(4, 200);
  sheet.setColumnWidth(5, 120);
  for (var c = 6; c <= NUM_COLS; c++) sheet.setColumnWidth(c, 95);
  sheet.setColumnWidth(10, 100); // Conv. Value
}

// ============================================================================
// UTILITY FUNCTIONS
// ============================================================================

function getChannelLabel(channelType) {
  var labels = {
    'PERFORMANCE_MAX': 'PMax',
    'SEARCH': 'Search',
    'SHOPPING': 'Shopping',
    'AI_MAX': 'AI Max',
    'DSA': 'DSA'
  };
  return labels[channelType] || channelType;
}

function formatCurrency(value) {
  if (value === null || value === undefined || isNaN(value)) return '$0.00';
  return '$' + value.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ',');
}

function formatNumber(value) {
  if (value === null || value === undefined || isNaN(value)) return '0';
  return value.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
}


Auction insights script V3: alerting ›

hello@adnine.io
De Cuserstraat 91 Amsterdam

Adnine © 2025.

hello@adnine.io
De Cuserstraat 91 Amsterdam

Adnine © 2025.

hello@adnine.io
De Cuserstraat 91 Amsterdam

Adnine © 2025.