Headings

h1 Heading

h2 Heading

h3 Heading

h4 Heading

Emphasis

This is bold text

This is italic text

Strikethrough

Blockquote

Blockquotes can be nested…

…by using additional greater-than signs.

Lists

Unordered:

• Item one
• Item two
  • Nested item
  • Another nested
    • Deeper nested
• Item three

Ordered:

1. First item
2. Second item
3. Third item

Code

Inline code example.

var foo = function (bar) {
  return bar++;
};

Tables

Links

link text

USE CASE: “Show me all informational queries with >$50 spend and 0 conversions.”

INTENT CATEGORIES:

• BRANDED: Contains your brand terms
• COMPETITOR: Contains competitor brand terms
• TRANSACTIONAL: Buy, order, purchase, pricing, cost, hire, book, etc.
• INFORMATIONAL: How, what, why, guide, tutorial, tips, etc.
• NAVIGATIONAL: Near me, locations, directions, etc.
• COMMERCIAL: Best, top, review, compare, vs, etc.
• OTHER: Doesn’t match any pattern
  • Hello here
  • Lorem2
    • SubHere
• Hello again

/******************************************************************************
 * CONFIGURATION - Adjust these values for your account
 ******************************************************************************/
var CONFIG = {
  // ═══════════════════════════════════════════════════════════════════════════
  // OUTPUT SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  SPREADSHEET_URL: 'CREATE_NEW',  // Or paste existing spreadsheet URL
  SHEET_NAME: '2. Search Terms',

  // Email alerts (leave empty array to disable)
  EMAIL_RECIPIENTS: [],

  // Slack webhook (leave empty to disable)
  SLACK_WEBHOOK_URL: '',

  // ═══════════════════════════════════════════════════════════════════════════
  // DATE RANGE
  // ═══════════════════════════════════════════════════════════════════════════
  DATE_RANGE: 'LAST_30_DAYS',

  // ═══════════════════════════════════════════════════════════════════════════
  // BRAND & COMPETITOR TERMS (customize for your account)
  // ═══════════════════════════════════════════════════════════════════════════
  // Add your brand terms (case-insensitive matching)
  BRAND_TERMS: [
    'your brand',
    'yourbrand',
    'your company'
    // Add more brand variations
  ],

  // Add competitor brand terms (case-insensitive matching)
  COMPETITOR_TERMS: [
    'competitor1',
    'competitor2',
    'competitor3'
    // Add competitor names
  ],

  // ═══════════════════════════════════════════════════════════════════════════
  // FILTERS & THRESHOLDS
  // ═══════════════════════════════════════════════════════════════════════════
  CAMPAIGN_NAME_CONTAINS: '',      // Filter campaigns (empty = all)
  CAMPAIGN_NAME_EXCLUDES: '',      // Exclude campaigns containing this
  MINIMUM_IMPRESSIONS: 0,          // Ignore low-data terms
  MINIMUM_CLICKS: 0,
  MINIMUM_COST: 0,                 // In dollars

  // ═══════════════════════════════════════════════════════════════════════════
  // EXECUTION SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  LOG_LEVEL: 'INFO',               // DEBUG, INFO, WARN, ERROR
  TIME_LIMIT_MINUTES: 25,          // Exit gracefully before this (max 30)
  BATCH_SIZE: 500                  // Rows per spreadsheet write
};

/******************************************************************************
 * INTENT CLASSIFICATION PATTERNS
 ******************************************************************************/
var INTENT_PATTERNS = {
  TRANSACTIONAL: [
    'buy', 'purchase', 'order', 'shop', 'price', 'pricing', 'cost',
    'cheap', 'affordable', 'discount', 'deal', 'sale', 'coupon',
    'hire', 'book', 'schedule', 'appointment', 'quote', 'estimate',
    'subscribe', 'sign up', 'signup', 'register', 'get started',
    'download', 'install', 'free trial', 'demo', 'pricing plans'
  ],

  INFORMATIONAL: [
    'how to', 'how do', 'what is', 'what are', 'why', 'when',
    'guide', 'tutorial', 'tips', 'tricks', 'learn', 'training',
    'example', 'examples', 'template', 'templates', 'ideas',
    'definition', 'meaning', 'explain', 'difference between',
    'pros and cons', 'benefits', 'advantages', 'disadvantages',
    'step by step', 'instructions', 'ways to', 'methods'
  ],

  NAVIGATIONAL: [
    'near me', 'nearby', 'location', 'locations', 'address',
    'directions', 'hours', 'open now', 'closest', 'local',
    'in my area', 'around me', 'close to me', 'nearest'
  ],

  COMMERCIAL: [
    'best', 'top', 'review', 'reviews', 'compare', 'comparison',
    'vs', 'versus', 'alternative', 'alternatives', 'like',
    'similar to', 'rated', 'rating', 'rankings', 'recommended',
    'which', 'should i', 'worth it', 'good', 'bad'
  ]
};

/******************************************************************************
 * MAIN EXECUTION
 ******************************************************************************/
function main() {
  var startTime = new Date();
  log('INFO', 'Search Term AI Export started: ' + startTime.toISOString());

  try {
    var sheet = initializeSheet();
    var results = collectAndClassifySearchTerms(startTime);
    writeResults(sheet, results);
    writeSummarySheet(sheet.getParent(), results);
    sendNotifications(results, startTime);
    logSummary(results, startTime);

  } catch (error) {
    handleFatalError(error, startTime);
  }
}

/******************************************************************************
 * DATA COLLECTION & CLASSIFICATION (GAQL-First)
 ******************************************************************************/
function collectAndClassifySearchTerms(startTime) {
  var results = {
    data: [],
    summary: {
      totalTerms: 0,
      byIntent: {
        'BRANDED': { count: 0, cost: 0, conversions: 0 },
        'COMPETITOR': { count: 0, cost: 0, conversions: 0 },
        'TRANSACTIONAL': { count: 0, cost: 0, conversions: 0 },
        'INFORMATIONAL': { count: 0, cost: 0, conversions: 0 },
        'NAVIGATIONAL': { count: 0, cost: 0, conversions: 0 },
        'COMMERCIAL': { count: 0, cost: 0, conversions: 0 },
        'OTHER': { count: 0, cost: 0, conversions: 0 }
      },
      wasteOpportunities: [],
      conversionOpportunities: []
    }
  };

  try {
    // GAQL-first approach using search_term_view (10-50x faster)
    collectSearchTermsGAQL(results, startTime);
  } catch (e) {
    log('WARN', 'GAQL failed, using fallback: ' + e.message);
    collectSearchTermsFallback(results, startTime);
  }

  // Sort waste by cost desc, opportunities by conversions desc
  results.summary.wasteOpportunities.sort(function(a, b) { return b.cost - a.cost; });
  results.summary.conversionOpportunities.sort(function(a, b) { return b.conversions - a.conversions; });

  log('INFO', 'Classified ' + results.summary.totalTerms + ' search terms');
  return results;
}

/******************************************************************************
 * GAQL SEARCH TERM COLLECTION (Primary - Fast)
 ******************************************************************************/
function collectSearchTermsGAQL(results, startTime) {
  var query = 'SELECT ' +
    'campaign.name, ' +
    'ad_group.name, ' +
    'search_term_view.search_term, ' +
    'segments.keyword.info.match_type, ' +
    'metrics.impressions, ' +
    'metrics.clicks, ' +
    'metrics.cost_micros, ' +
    'metrics.conversions, ' +
    'metrics.conversions_value, ' +
    'metrics.ctr, ' +
    'metrics.average_cpc ' +
    'FROM search_term_view ' +
    'WHERE segments.date DURING ' + CONFIG.DATE_RANGE;

  if (CONFIG.MINIMUM_IMPRESSIONS > 0) {
    query += ' AND metrics.impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS;
  }
  if (CONFIG.MINIMUM_CLICKS > 0) {
    query += ' AND metrics.clicks >= ' + CONFIG.MINIMUM_CLICKS;
  }
  if (CONFIG.MINIMUM_COST > 0) {
    query += ' AND metrics.cost_micros >= ' + (CONFIG.MINIMUM_COST * 1000000);
  }

  query += ' ORDER BY metrics.cost_micros DESC';

  var report = AdsApp.report(query);
  var rows = report.rows();
  var processed = 0;

  while (rows.hasNext()) {
    var row = rows.next();
    var campaignName = row['campaign.name'];

    // Apply campaign filters
    if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
        campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
      continue;
    }
    if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
        campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
      continue;
    }

    var searchTermText = row['search_term_view.search_term'];
    var costMicros = parseInt(row['metrics.cost_micros'], 10) || 0;
    var cost = costMicros / 1000000;
    var impressions = parseInt(row['metrics.impressions'], 10) || 0;
    var clicks = parseInt(row['metrics.clicks'], 10) || 0;
    var conversions = parseFloat(row['metrics.conversions']) || 0;
    var convValue = parseFloat(row['metrics.conversions_value']) || 0;

    // Calculate performance metrics
    var ctr = impressions > 0 ? clicks / impressions : 0;
    var convRate = clicks > 0 ? conversions / clicks : 0;
    var avgCpc = clicks > 0 ? cost / clicks : 0;
    var cpa = conversions > 0 ? cost / conversions : null;
    var roas = cost > 0 ? convValue / cost : null;

    // Calculate Performance Score
    var performanceScore = calculatePerformanceScore(clicks, impressions, conversions);

    // Classify intent
    var intent = classifyIntent(searchTermText);
    var intentSignals = getIntentSignals(searchTermText, intent);

    var rowData = {
      'Search Term': searchTermText,
      'Intent Category': intent,
      'Intent Signals': intentSignals,
      'Match Type': row['segments.keyword.info.match_type'] || 'UNKNOWN',
      'Campaign': campaignName,
      'Ad Group': row['ad_group.name'],
      'Impressions': impressions,
      'Clicks': clicks,
      'CTR': ctr,
      'Avg CPC': avgCpc,
      'Cost': cost,
      'Conversions': conversions,
      'Conv Rate': convRate,
      'CPA': cpa,
      'Conv Value': convValue,
      'ROAS': roas,
      'Performance Score': performanceScore,
      'AI Notes': generateAINotes(intent, cost, conversions, clicks)
    };

    results.data.push(rowData);
    results.summary.totalTerms++;

    // Update intent summary
    results.summary.byIntent[intent].count++;
    results.summary.byIntent[intent].cost += cost;
    results.summary.byIntent[intent].conversions += conversions;

    // Flag waste opportunities (high spend, zero conversions)
    if (cost >= 50 && conversions === 0) {
      results.summary.wasteOpportunities.push({
        term: searchTermText,
        intent: intent,
        cost: cost,
        clicks: clicks
      });
    }

    // Flag conversion opportunities (converting non-exact)
    var matchType = row['segments.keyword.info.match_type'] || '';
    if (conversions >= 2 && matchType !== 'EXACT') {
      results.summary.conversionOpportunities.push({
        term: searchTermText,
        intent: intent,
        conversions: conversions,
        cpa: cost / conversions
      });
    }

    processed++;
    if (processed % 500 === 0) {
      log('DEBUG', 'Processed ' + processed + ' terms');
      checkTimeLimit(startTime);
    }
  }
}

/******************************************************************************
 * FALLBACK: Standard API Search Term Collection
 ******************************************************************************/
function collectSearchTermsFallback(results, startTime) {
  var selector = AdsApp.searchTerms()
    .forDateRange(CONFIG.DATE_RANGE)
    .orderBy('Cost DESC');

  if (CONFIG.MINIMUM_IMPRESSIONS > 0) {
    selector = selector.withCondition('Impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS);
  }
  if (CONFIG.MINIMUM_CLICKS > 0) {
    selector = selector.withCondition('Clicks >= ' + CONFIG.MINIMUM_CLICKS);
  }
  if (CONFIG.MINIMUM_COST > 0) {
    selector = selector.withCondition('Cost >= ' + (CONFIG.MINIMUM_COST * 1000000));
  }

  var terms = selector.get();
  var processed = 0;

  while (terms.hasNext()) {
    var term = terms.next();
    var campaign = term.getCampaign();
    var adGroup = term.getAdGroup();
    var campaignName = campaign.getName();

    // Apply campaign filters
    if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
        campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
      continue;
    }
    if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
        campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
      continue;
    }

    var stats = term.getStatsFor(CONFIG.DATE_RANGE);
    var searchTermText = term.getText();
    var cost = stats.getCost();
    var impressions = stats.getImpressions();
    var clicks = stats.getClicks();
    var conversions = stats.getConversions();
    var convValue = stats.getConversionValue();

    // Calculate Performance Score
    var performanceScore = calculatePerformanceScore(clicks, impressions, conversions);

    // Classify intent
    var intent = classifyIntent(searchTermText);
    var intentSignals = getIntentSignals(searchTermText, intent);

    var row = {
      'Search Term': searchTermText,
      'Intent Category': intent,
      'Intent Signals': intentSignals,
      'Match Type': term.getMatchType(),
      'Campaign': campaignName,
      'Ad Group': adGroup.getName(),
      'Impressions': impressions,
      'Clicks': clicks,
      'CTR': stats.getCtr(),
      'Avg CPC': cost / Math.max(clicks, 1),
      'Cost': cost,
      'Conversions': conversions,
      'Conv Rate': stats.getConversionRate(),
      'CPA': conversions > 0 ? cost / conversions : null,
      'Conv Value': convValue,
      'ROAS': cost > 0 ? convValue / cost : null,
      'Performance Score': performanceScore,
      'AI Notes': generateAINotes(intent, cost, conversions, clicks)
    };

    results.data.push(row);
    results.summary.totalTerms++;

    // Update intent summary
    results.summary.byIntent[intent].count++;
    results.summary.byIntent[intent].cost += cost;
    results.summary.byIntent[intent].conversions += conversions;

    // Flag waste opportunities (high spend, zero conversions)
    if (cost >= 50 && conversions === 0) {
      results.summary.wasteOpportunities.push({
        term: searchTermText,
        intent: intent,
        cost: cost,
        clicks: clicks
      });
    }

    // Flag conversion opportunities (converting non-exact)
    if (conversions >= 2 && term.getMatchType() !== 'EXACT') {
      results.summary.conversionOpportunities.push({
        term: searchTermText,
        intent: intent,
        conversions: conversions,
        cpa: cost / conversions
      });
    }

    processed++;
    if (processed % 500 === 0) {
      log('DEBUG', 'Processed ' + processed + ' terms');
      checkTimeLimit(startTime);
    }
  }
}

/******************************************************************************
 * PERFORMANCE SCORE CALCULATION
 ******************************************************************************/
function calculatePerformanceScore(clicks, impressions, conversions) {
  if (impressions === 0) return 0;

  var ctr = clicks / impressions;
  var convRate = clicks > 0 ? conversions / clicks : 0;

  // Score = (CTR × 1000) + (ConvRate × 5000)
  return (ctr * 1000) + (convRate * 5000);
}

function classifyIntent(searchTerm) {
  var termLower = searchTerm.toLowerCase();

  // Check branded first (highest priority)
  for (var i = 0; i < CONFIG.BRAND_TERMS.length; i++) {
    if (termLower.indexOf(CONFIG.BRAND_TERMS[i].toLowerCase()) !== -1) {
      return 'BRANDED';
    }
  }

  // Check competitor
  for (var j = 0; j < CONFIG.COMPETITOR_TERMS.length; j++) {
    if (termLower.indexOf(CONFIG.COMPETITOR_TERMS[j].toLowerCase()) !== -1) {
      return 'COMPETITOR';
    }
  }

  // Check intent patterns
  for (var intent in INTENT_PATTERNS) {
    var patterns = INTENT_PATTERNS[intent];
    for (var k = 0; k < patterns.length; k++) {
      if (termLower.indexOf(patterns[k]) !== -1) {
        return intent;
      }
    }
  }

  return 'OTHER';
}

function getIntentSignals(searchTerm, intent) {
  var termLower = searchTerm.toLowerCase();
  var signals = [];

  if (intent === 'BRANDED') {
    for (var i = 0; i < CONFIG.BRAND_TERMS.length; i++) {
      if (termLower.indexOf(CONFIG.BRAND_TERMS[i].toLowerCase()) !== -1) {
        signals.push(CONFIG.BRAND_TERMS[i]);
      }
    }
  } else if (intent === 'COMPETITOR') {
    for (var j = 0; j < CONFIG.COMPETITOR_TERMS.length; j++) {
      if (termLower.indexOf(CONFIG.COMPETITOR_TERMS[j].toLowerCase()) !== -1) {
        signals.push(CONFIG.COMPETITOR_TERMS[j]);
      }
    }
  } else if (INTENT_PATTERNS[intent]) {
    var patterns = INTENT_PATTERNS[intent];
    for (var k = 0; k < patterns.length; k++) {
      if (termLower.indexOf(patterns[k]) !== -1) {
        signals.push(patterns[k]);
      }
    }
  }

  return signals.slice(0, 3).join(', ');  // Max 3 signals
}

function generateAINotes(intent, cost, conversions, clicks) {
  var notes = [];

  // Waste flag
  if (cost >= 50 && conversions === 0) {
    notes.push('HIGH WASTE: $' + cost.toFixed(2) + ' with 0 conversions');
  } else if (cost >= 25 && conversions === 0) {
    notes.push('Moderate waste: $' + cost.toFixed(2) + ' with 0 conversions');
  }

  // Intent-based insights
  if (intent === 'INFORMATIONAL' && cost > 20) {
    notes.push('Informational query spending - consider content strategy');
  }

  if (intent === 'COMPETITOR' && conversions > 0) {
    notes.push('Converting on competitor term - conquest opportunity');
  }

  if (intent === 'TRANSACTIONAL' && conversions === 0 && clicks >= 10) {
    notes.push('High-intent term not converting - check landing page');
  }

  if (intent === 'NAVIGATIONAL' && cost > 10) {
    notes.push('Local intent - verify location targeting');
  }

  return notes.join(' | ') || '-';
}

/******************************************************************************
 * OUTPUT FUNCTIONS
 ******************************************************************************/
function initializeSheet() {
  var ss;

  if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL === 'YOUR_SPREADSHEET_URL_HERE' || CONFIG.SPREADSHEET_URL === 'CREATE_NEW') {
    ss = SpreadsheetApp.create('PPC.io Search Terms AI - ' +
                               AdsApp.currentAccount().getName() + ' - ' +
                               formatDate(new Date()));
    log('INFO', 'Created spreadsheet: ' + ss.getUrl());
  } else {
    ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  }

  var sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(CONFIG.SHEET_NAME);
  } else {
    sheet.clear();
  }

  return sheet;
}

function writeResults(sheet, results) {
  if (results.data.length === 0) {
    log('WARN', 'No data to write');
    sheet.getRange(1, 1).setValue('No search terms found matching criteria');
    return;
  }

  var headers = Object.keys(results.data[0]);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
  sheet.setFrozenRows(1);

  var rows = results.data.map(function(row) {
    return headers.map(function(h) { return row[h] !== null ? row[h] : ''; });
  });

  // Batch write
  for (var i = 0; i < rows.length; i += CONFIG.BATCH_SIZE) {
    var batch = rows.slice(i, Math.min(i + CONFIG.BATCH_SIZE, rows.length));
    sheet.getRange(2 + i, 1, batch.length, headers.length).setValues(batch);
  }

  // Format columns
  formatColumn(sheet, headers, 'CTR', '0.00%', rows.length);
  formatColumn(sheet, headers, 'Conv Rate', '0.00%', rows.length);
  formatColumn(sheet, headers, 'Avg CPC', '$#,##0.00', rows.length);
  formatColumn(sheet, headers, 'Cost', '$#,##0.00', rows.length);
  formatColumn(sheet, headers, 'CPA', '$#,##0.00', rows.length);
  formatColumn(sheet, headers, 'Conv Value', '$#,##0.00', rows.length);
  formatColumn(sheet, headers, 'ROAS', '0.00', rows.length);
  formatColumn(sheet, headers, 'Performance Score', '0.00', rows.length);

  // Color code by intent
  applyIntentFormatting(sheet, headers, rows.length);

  // Auto-resize
  for (var col = 1; col <= Math.min(headers.length, 12); col++) {
    sheet.autoResizeColumn(col);
  }

  log('INFO', 'Wrote ' + rows.length + ' rows');
}

function applyIntentFormatting(sheet, headers, numRows) {
  var intentCol = headers.indexOf('Intent Category') + 1;
  if (intentCol === 0) return;

  var range = sheet.getRange(2, intentCol, numRows, 1);
  var values = range.getValues();

  var colors = {
    'BRANDED': '#d4edda',       // Green
    'COMPETITOR': '#fff3cd',    // Yellow
    'TRANSACTIONAL': '#cce5ff', // Blue
    'INFORMATIONAL': '#f8d7da', // Light red
    'NAVIGATIONAL': '#e2d5f1',  // Purple
    'COMMERCIAL': '#d1ecf1',    // Cyan
    'OTHER': '#e9ecef'          // Gray
  };

  var bgColors = values.map(function(row) {
    return [colors[row[0]] || '#ffffff'];
  });

  range.setBackgrounds(bgColors);
}

function formatColumn(sheet, headers, columnName, format, numRows) {
  var colIndex = headers.indexOf(columnName);
  if (colIndex !== -1) {
    sheet.getRange(2, colIndex + 1, numRows, 1).setNumberFormat(format);
  }
}

function writeSummarySheet(ss, results) {
  var sheet = ss.getSheetByName('1. Summary');
  if (!sheet) {
    sheet = ss.insertSheet('1. Summary', 0);
  } else {
    sheet.clear();
  }

  var data = [
    ['SEARCH TERM AI ANALYSIS', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Account: ' + AdsApp.currentAccount().getName(), ''],
    ['Date Range: ' + CONFIG.DATE_RANGE, ''],
    ['Export Date: ' + new Date().toISOString(), ''],
    ['Total Terms Analyzed: ' + results.summary.totalTerms, ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['INTENT BREAKDOWN', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Intent', 'Count', 'Cost', 'Conversions', 'CPA']
  ];

  // Add intent breakdown rows
  var intents = ['BRANDED', 'COMPETITOR', 'TRANSACTIONAL', 'INFORMATIONAL', 'NAVIGATIONAL', 'COMMERCIAL', 'OTHER'];
  for (var i = 0; i < intents.length; i++) {
    var intent = intents[i];
    var stats = results.summary.byIntent[intent];
    var cpa = stats.conversions > 0 ? stats.cost / stats.conversions : '-';
    data.push([
      intent,
      stats.count,
      '$' + stats.cost.toFixed(2),
      stats.conversions.toFixed(2),
      typeof cpa === 'number' ? '$' + cpa.toFixed(2) : cpa
    ]);
  }

  data.push(['', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);
  data.push(['TOP WASTE OPPORTUNITIES (>$50, 0 conversions)', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);

  var wasteOps = results.summary.wasteOpportunities.slice(0, 15);
  if (wasteOps.length === 0) {
    data.push(['No significant waste opportunities found', '']);
  } else {
    for (var j = 0; j < wasteOps.length; j++) {
      var w = wasteOps[j];
      data.push([w.term, w.intent, '$' + w.cost.toFixed(2), w.clicks + ' clicks']);
    }
  }

  data.push(['', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);
  data.push(['KEYWORD PROMOTION OPPORTUNITIES (2+ conversions, non-exact)', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);

  var convOps = results.summary.conversionOpportunities.slice(0, 15);
  if (convOps.length === 0) {
    data.push(['No keyword promotion opportunities found', '']);
  } else {
    for (var k = 0; k < convOps.length; k++) {
      var c = convOps[k];
      data.push([c.term, c.intent, c.conversions.toFixed(2) + ' conv', '$' + c.cpa.toFixed(2) + ' CPA']);
    }
  }

  data.push(['', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);
  data.push(['AI ANALYSIS PROMPTS', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);
  data.push(['Copy these prompts to use with Claude:', '']);
  data.push(['', '']);
  data.push(['1. "Show me all INFORMATIONAL queries with >$50 spend and zero conversions. Create a negative keyword list."', '']);
  data.push(['2. "Which intent categories have the worst CPA? What does this tell us about targeting?"', '']);
  data.push(['3. "Find patterns in high-performing TRANSACTIONAL terms. What themes should we expand?"', '']);
  data.push(['4. "Analyze COMPETITOR terms - are we profitably conquesting or wasting budget?"', '']);
  data.push(['5. "Create a priority list: which terms should be (1) added as exact match, (2) added as negatives, (3) investigated?"', '']);

  // Write data
  var maxCols = Math.max.apply(null, data.map(function(row) { return row.length; }));
  for (var m = 0; m < data.length; m++) {
    while (data[m].length < maxCols) {
      data[m].push('');
    }
  }

  sheet.getRange(1, 1, data.length, maxCols).setValues(data);

  // Format
  sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
  sheet.getRange(11, 1).setFontWeight('bold');
  sheet.getRange(13, 1, 1, 5).setFontWeight('bold');
  sheet.setColumnWidth(1, 400);
  sheet.setColumnWidth(2, 150);
  sheet.setColumnWidth(3, 100);
  sheet.setColumnWidth(4, 100);
  sheet.setColumnWidth(5, 100);
}

/******************************************************************************
 * NOTIFICATION FUNCTIONS
 ******************************************************************************/
function sendNotifications(results, startTime) {
  var duration = ((new Date() - startTime) / 1000).toFixed(1);

  var message = [
    'Search Term AI Analysis Complete',
    '',
    'Account: ' + AdsApp.currentAccount().getName(),
    'Date Range: ' + CONFIG.DATE_RANGE,
    'Duration: ' + duration + 's',
    '',
    'INTENT SUMMARY:',
    '- Branded: ' + results.summary.byIntent['BRANDED'].count,
    '- Competitor: ' + results.summary.byIntent['COMPETITOR'].count,
    '- Transactional: ' + results.summary.byIntent['TRANSACTIONAL'].count,
    '- Informational: ' + results.summary.byIntent['INFORMATIONAL'].count,
    '- Commercial: ' + results.summary.byIntent['COMMERCIAL'].count,
    '',
    'OPPORTUNITIES:',
    '- Waste (>$50, 0 conv): ' + results.summary.wasteOpportunities.length,
    '- Promotion candidates: ' + results.summary.conversionOpportunities.length,
    '',
    '--',
    'Generated by PPC.io Script Engine'
  ].join('\n');

  if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
    try {
      MailApp.sendEmail({
        to: CONFIG.EMAIL_RECIPIENTS.join(','),
        subject: '[PPC.io] Search Term AI Analysis - ' + results.summary.totalTerms + ' terms',
        body: message
      });
      log('INFO', 'Email sent');
    } catch (e) {
      log('ERROR', 'Failed to send email: ' + e.message);
    }
  }

  if (CONFIG.SLACK_WEBHOOK_URL) {
    try {
      UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify({
          text: ':mag: *PPC.io Search Term AI*\n```' + message + '```'
        })
      });
      log('INFO', 'Slack sent');
    } catch (e) {
      log('ERROR', 'Failed to send Slack: ' + e.message);
    }
  }
}

/******************************************************************************
 * UTILITY FUNCTIONS
 ******************************************************************************/
function checkTimeLimit(startTime) {
  var elapsed = (new Date() - startTime) / 1000 / 60;
  if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
    throw new Error('TIME_LIMIT: Processed up to current batch after ' +
                    elapsed.toFixed(1) + ' minutes.');
  }
}

function log(level, message) {
  var levels = { 'DEBUG': 0, 'INFO': 1, 'WARN': 2, 'ERROR': 3 };
  if (levels[level] >= levels[CONFIG.LOG_LEVEL]) {
    Logger.log('[' + level + '] ' + message);
  }
}

function logSummary(results, startTime) {
  var duration = ((new Date() - startTime) / 1000).toFixed(1);
  log('INFO', '════════════════════════════════════════');
  log('INFO', 'SEARCH TERM AI EXPORT COMPLETE');
  log('INFO', 'Duration: ' + duration + ' seconds');
  log('INFO', 'Total Terms: ' + results.summary.totalTerms);
  log('INFO', 'Waste Opportunities: ' + results.summary.wasteOpportunities.length);
  log('INFO', 'Promotion Candidates: ' + results.summary.conversionOpportunities.length);
  log('INFO', '════════════════════════════════════════');
}

function handleFatalError(error, startTime) {
  log('ERROR', '════════════════════════════════════════');
  log('ERROR', 'FATAL ERROR: ' + error.message);
  log('ERROR', 'Stack: ' + error.stack);
  log('ERROR', '════════════════════════════════════════');

  if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
    try {
      MailApp.sendEmail({
        to: CONFIG.EMAIL_RECIPIENTS.join(','),
        subject: '[PPC.io ERROR] Search Term AI Export Failed',
        body: 'Error: ' + error.message + '\n\nStack:\n' + error.stack
      });
    } catch (e) {
      log('ERROR', 'Could not send error email: ' + e.message);
    }
  }
}

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