Free PPC

Toolkit

/******************************************************************************
 * QUALITY SCORE TRACKER WITH HISTORICAL TRENDS
 *
 * Generated by PPC.io Script Engine
 * https://ppc.io
 *
 * Purpose: Track Quality Score changes over time and alert on degradation
 * Author:  PPC.io
 * Version: 1.0
 * Updated: 2025-01-14
 *
 * SETUP INSTRUCTIONS:
 * 1. Set SPREADSHEET_URL to 'CREATE_NEW' for first run (or paste existing URL)
 * 2. IMPORTANT: After first run, copy the spreadsheet URL back here
 * 3. Schedule: Daily recommended (to build historical data)
 * 4. Run in Preview mode first to verify
 *
 * USE CASE: "What's happening to my Quality Scores over time? Which keywords are declining?"
 *
 * HOW IT WORKS:
 * - Each run takes a snapshot of all keyword Quality Scores
 * - Appends to historical data sheet for trend analysis
 * - Compares to previous day to detect changes
 * - Alerts on significant drops (configurable threshold)
 *
 * OUTPUTS:
 * - 1. Summary: Account QS averages, alerts, component analysis
 * - 2. Current Snapshot: All keywords with current QS data
 * - 3. Historical Data: Append-only log (rolling 90 days)
 * - 4. Change Alerts: Keywords with recent QS changes
 * - 5. Component Analysis: Breakdown by CTR/Relevance/Landing Page
 *
 * CHANGELOG:
 * v1.0 - Initial release with GAQL-first architecture
 *
 ******************************************************************************/

/******************************************************************************
 * CONFIGURATION - Adjust these values for your account
 ******************************************************************************/
var CONFIG = {
  // ═══════════════════════════════════════════════════════════════════════════
  // OUTPUT SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  // IMPORTANT: After first run, paste the created spreadsheet URL here
  // to maintain historical data across runs
  SPREADSHEET_URL: 'CREATE_NEW',  // Or paste existing spreadsheet URL
  EMAIL_RECIPIENTS: [],           // ['email@example.com']
  SLACK_WEBHOOK_URL: '',          // Slack incoming webhook URL

  // ═══════════════════════════════════════════════════════════════════════════
  // FILTERS
  // ═══════════════════════════════════════════════════════════════════════════
  CAMPAIGN_NAME_CONTAINS: '',     // Filter to campaigns containing this
  CAMPAIGN_NAME_EXCLUDES: '',     // Exclude campaigns containing this
  INCLUDE_PAUSED_CAMPAIGNS: false,// Include paused campaigns
  INCLUDE_PAUSED_KEYWORDS: false, // Include paused keywords
  MINIMUM_IMPRESSIONS: 100,       // Min impressions to include keyword

  // ═══════════════════════════════════════════════════════════════════════════
  // ALERT THRESHOLDS
  // ═══════════════════════════════════════════════════════════════════════════
  QS_DROP_THRESHOLD: 2,           // Alert on drops of this many points
  QS_LOW_THRESHOLD: 5,            // Consider QS below this as "low"
  ALERT_ON_NEW_LOW_QS: true,      // Alert when keyword drops to low QS
  ALERT_ON_COMPONENT_CHANGE: true,// Alert on component downgrades

  // ═══════════════════════════════════════════════════════════════════════════
  // HISTORICAL DATA SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  HISTORICAL_DAYS_TO_KEEP: 90,    // Rolling window for historical data
  COMPARE_TO_DAYS_AGO: 1,         // Compare to snapshot from X days ago

  // ═══════════════════════════════════════════════════════════════════════════
  // 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
};

/******************************************************************************
 * MAIN EXECUTION
 ******************************************************************************/
function main() {
  var startTime = new Date();
  log('INFO', 'Quality Score Tracker started: ' + startTime.toISOString());

  try {
    var ss = initializeSpreadsheet();
    var results = trackQualityScores(ss, startTime);
    writeAllSheets(ss, results);
    sendNotifications(results, ss.getUrl(), startTime);
    logSummary(results, startTime);

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

/******************************************************************************
 * DATA COLLECTION
 ******************************************************************************/
function trackQualityScores(ss, startTime) {
  var results = {
    currentSnapshot: [],
    previousSnapshot: [],
    changeAlerts: [],
    componentAnalysis: {
      expectedCTR: { above: 0, average: 0, below: 0 },
      adRelevance: { above: 0, average: 0, below: 0 },
      landingPage: { above: 0, average: 0, below: 0 }
    },
    qsDistribution: {},
    summary: {
      totalKeywords: 0,
      keywordsWithQS: 0,
      avgQualityScore: 0,
      medianQS: 0,
      lowQSCount: 0,
      highQSCount: 0,
      qsDrops: 0,
      qsImprovements: 0,
      newAlerts: 0,
      previousAvgQS: null,
      qsChange: null
    }
  };

  // Load previous snapshot for comparison
  log('INFO', 'Loading previous snapshot...');
  results.previousSnapshot = loadPreviousSnapshot(ss);
  if (results.previousSnapshot.length > 0) {
    results.summary.previousAvgQS = calculateAverageQS(results.previousSnapshot);
  }
  log('INFO', 'Previous snapshot: ' + results.previousSnapshot.length + ' keywords');

  // Collect current QS data
  log('INFO', 'Collecting current Quality Score data...');
  collectCurrentQSData(results, startTime);

  // Detect changes
  log('INFO', 'Detecting changes...');
  detectChanges(results);

  // Analyze components
  analyzeComponents(results);

  return results;
}

function collectCurrentQSData(results, startTime) {
  var today = formatDate(new Date());

  try {
    var query = 'SELECT ' +
      'campaign.id, ' +
      'campaign.name, ' +
      'ad_group.id, ' +
      'ad_group.name, ' +
      'ad_group_criterion.criterion_id, ' +
      'ad_group_criterion.keyword.text, ' +
      'ad_group_criterion.keyword.match_type, ' +
      'ad_group_criterion.quality_info.quality_score, ' +
      'ad_group_criterion.quality_info.creative_quality_score, ' +
      'ad_group_criterion.quality_info.post_click_quality_score, ' +
      'ad_group_criterion.quality_info.search_predicted_ctr, ' +
      'metrics.impressions, ' +
      'metrics.clicks, ' +
      'metrics.cost_micros, ' +
      'metrics.conversions ' +
      'FROM keyword_view ' +
      'WHERE ad_group_criterion.status = "ENABLED" ' +
      'AND segments.date DURING LAST_30_DAYS ' +
      'AND metrics.impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS;

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

    var rows = AdsApp.search(query);
    var count = 0;
    var qsTotal = 0;
    var qsValues = [];

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

      // Apply filters
      if (!passesCampaignFilter(campaignName)) continue;

      var keywordText = row['ad_group_criterion.keyword.text'] || '';
      var matchType = row['ad_group_criterion.keyword.match_type'] || '';
      var qualityScore = row['ad_group_criterion.quality_info.quality_score'];
      var expectedCTR = row['ad_group_criterion.quality_info.search_predicted_ctr'] || '';
      var adRelevance = row['ad_group_criterion.quality_info.creative_quality_score'] || '';
      var landingPage = row['ad_group_criterion.quality_info.post_click_quality_score'] || '';

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

      // Parse QS (can be null if not enough data)
      var qsValue = qualityScore ? parseInt(qualityScore, 10) : null;

      var keywordData = {
        date: today,
        campaignId: row['campaign.id'],
        campaignName: campaignName,
        adGroupId: row['ad_group.id'],
        adGroupName: row['ad_group.name'],
        keywordId: row['ad_group_criterion.criterion_id'],
        keyword: keywordText,
        matchType: matchType,
        qualityScore: qsValue,
        expectedCTR: normalizeComponent(expectedCTR),
        adRelevance: normalizeComponent(adRelevance),
        landingPage: normalizeComponent(landingPage),
        impressions: impressions,
        clicks: clicks,
        cost: cost,
        conversions: conversions,
        ctr: impressions > 0 ? (clicks / impressions * 100) : 0,
        // For comparison
        uniqueKey: row['campaign.id'] + '|' + row['ad_group.id'] + '|' + row['ad_group_criterion.criterion_id']
      };

      results.currentSnapshot.push(keywordData);
      results.summary.totalKeywords++;

      if (qsValue !== null) {
        results.summary.keywordsWithQS++;
        qsTotal += qsValue;
        qsValues.push(qsValue);

        // Track distribution
        if (!results.qsDistribution[qsValue]) {
          results.qsDistribution[qsValue] = 0;
        }
        results.qsDistribution[qsValue]++;

        // Track low/high QS
        if (qsValue <= CONFIG.QS_LOW_THRESHOLD) {
          results.summary.lowQSCount++;
        }
        if (qsValue >= 8) {
          results.summary.highQSCount++;
        }

        // Track components
        trackComponent(results.componentAnalysis.expectedCTR, keywordData.expectedCTR);
        trackComponent(results.componentAnalysis.adRelevance, keywordData.adRelevance);
        trackComponent(results.componentAnalysis.landingPage, keywordData.landingPage);
      }

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

    // Calculate averages
    if (results.summary.keywordsWithQS > 0) {
      results.summary.avgQualityScore = qsTotal / results.summary.keywordsWithQS;

      // Calculate median
      qsValues.sort(function(a, b) { return a - b; });
      var mid = Math.floor(qsValues.length / 2);
      results.summary.medianQS = qsValues.length % 2 !== 0 ?
        qsValues[mid] : (qsValues[mid - 1] + qsValues[mid]) / 2;
    }

    // Calculate QS change from previous
    if (results.summary.previousAvgQS !== null) {
      results.summary.qsChange = results.summary.avgQualityScore - results.summary.previousAvgQS;
    }

    log('INFO', 'Collected ' + count + ' keywords, ' + results.summary.keywordsWithQS + ' with QS');

  } catch (e) {
    log('WARN', 'GAQL QS fetch failed, using fallback: ' + e.message);
    collectCurrentQSDataFallback(results, startTime);
  }
}

function collectCurrentQSDataFallback(results, startTime) {
  var today = formatDate(new Date());

  var campaigns = AdsApp.campaigns()
    .withCondition('AdvertisingChannelType = SEARCH');

  if (!CONFIG.INCLUDE_PAUSED_CAMPAIGNS) {
    campaigns = campaigns.withCondition('Status = ENABLED');
  }

  var campaignIterator = campaigns.get();

  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    var campaignName = campaign.getName();

    if (!passesCampaignFilter(campaignName)) continue;

    var keywordIterator = campaign.keywords()
      .withCondition('Status = ENABLED')
      .withCondition('Impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS)
      .forDateRange('LAST_30_DAYS')
      .get();

    while (keywordIterator.hasNext()) {
      var keyword = keywordIterator.next();
      var qsInfo = keyword.getQualityScore();
      var stats = keyword.getStatsFor('LAST_30_DAYS');

      var keywordData = {
        date: today,
        campaignId: campaign.getId(),
        campaignName: campaignName,
        adGroupId: keyword.getAdGroup().getId(),
        adGroupName: keyword.getAdGroup().getName(),
        keywordId: keyword.getId(),
        keyword: keyword.getText(),
        matchType: keyword.getMatchType(),
        qualityScore: qsInfo,
        expectedCTR: keyword.getExpectedCtr() || '',
        adRelevance: keyword.getAdRelevance() || '',
        landingPage: keyword.getLandingPageExperience() || '',
        impressions: stats.getImpressions(),
        clicks: stats.getClicks(),
        cost: stats.getCost(),
        conversions: stats.getConversions(),
        ctr: stats.getCtr() * 100,
        uniqueKey: campaign.getId() + '|' + keyword.getAdGroup().getId() + '|' + keyword.getId()
      };

      results.currentSnapshot.push(keywordData);
      results.summary.totalKeywords++;

      if (qsInfo !== null) {
        results.summary.keywordsWithQS++;
      }
    }

    checkTimeLimit(startTime);
  }
}

function normalizeComponent(value) {
  if (!value) return 'UNKNOWN';

  // Handle both enum formats
  value = value.toString().toUpperCase();

  if (value.indexOf('ABOVE') !== -1) return 'ABOVE_AVERAGE';
  if (value.indexOf('BELOW') !== -1) return 'BELOW_AVERAGE';
  if (value.indexOf('AVERAGE') !== -1) return 'AVERAGE';

  return value;
}

function trackComponent(component, value) {
  if (value === 'ABOVE_AVERAGE') component.above++;
  else if (value === 'BELOW_AVERAGE') component.below++;
  else if (value === 'AVERAGE') component.average++;
}

/******************************************************************************
 * HISTORICAL DATA MANAGEMENT
 ******************************************************************************/
function loadPreviousSnapshot(ss) {
  var previousData = [];

  try {
    var historySheet = ss.getSheetByName('3. Historical Data');
    if (!historySheet) return previousData;

    var lastRow = historySheet.getLastRow();
    if (lastRow < 2) return previousData;

    // Find data from X days ago
    var targetDate = new Date();
    targetDate.setDate(targetDate.getDate() - CONFIG.COMPARE_TO_DAYS_AGO);
    var targetDateStr = formatDate(targetDate);

    // Get all data and filter by date
    var data = historySheet.getDataRange().getValues();
    var headers = data[0];
    var dateCol = headers.indexOf('Date');
    var keyCol = headers.indexOf('Unique Key');
    var qsCol = headers.indexOf('Quality Score');
    var ctrCol = headers.indexOf('Expected CTR');
    var relCol = headers.indexOf('Ad Relevance');
    var lpCol = headers.indexOf('Landing Page');

    for (var i = 1; i < data.length; i++) {
      if (data[i][dateCol] === targetDateStr) {
        previousData.push({
          uniqueKey: data[i][keyCol],
          qualityScore: data[i][qsCol],
          expectedCTR: data[i][ctrCol],
          adRelevance: data[i][relCol],
          landingPage: data[i][lpCol]
        });
      }
    }

    log('DEBUG', 'Loaded ' + previousData.length + ' records from ' + targetDateStr);

  } catch (e) {
    log('WARN', 'Could not load previous snapshot: ' + e.message);
  }

  return previousData;
}

function appendToHistory(ss, results) {
  var sheet = ss.getSheetByName('3. Historical Data');
  if (!sheet) {
    sheet = ss.insertSheet('3. Historical Data');

    // Add headers
    var headers = [
      'Date', 'Campaign', 'Ad Group', 'Keyword', 'Match Type',
      'Quality Score', 'Expected CTR', 'Ad Relevance', 'Landing Page',
      'Impressions', 'Clicks', 'Unique Key'
    ];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
    sheet.setFrozenRows(1);
  }

  // Build rows to append
  var rows = results.currentSnapshot.map(function(kw) {
    return [
      kw.date,
      kw.campaignName,
      kw.adGroupName,
      kw.keyword,
      kw.matchType,
      kw.qualityScore !== null ? kw.qualityScore : '',
      kw.expectedCTR,
      kw.adRelevance,
      kw.landingPage,
      kw.impressions,
      kw.clicks,
      kw.uniqueKey
    ];
  });

  if (rows.length > 0) {
    var lastRow = sheet.getLastRow();
    sheet.getRange(lastRow + 1, 1, rows.length, rows[0].length).setValues(rows);
    log('INFO', 'Appended ' + rows.length + ' rows to history');
  }

  // Clean old data (keep rolling window)
  cleanOldHistoricalData(sheet);
}

function cleanOldHistoricalData(sheet) {
  var cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - CONFIG.HISTORICAL_DAYS_TO_KEEP);
  var cutoffStr = formatDate(cutoffDate);

  var data = sheet.getDataRange().getValues();
  var rowsToDelete = [];

  for (var i = data.length - 1; i >= 1; i--) {
    if (data[i][0] < cutoffStr) {
      rowsToDelete.push(i + 1);
    }
  }

  // Delete rows in reverse order to maintain indices
  for (var j = 0; j < rowsToDelete.length; j++) {
    sheet.deleteRow(rowsToDelete[j]);
  }

  if (rowsToDelete.length > 0) {
    log('INFO', 'Cleaned ' + rowsToDelete.length + ' old historical rows');
  }
}

/******************************************************************************
 * CHANGE DETECTION
 ******************************************************************************/
function detectChanges(results) {
  if (results.previousSnapshot.length === 0) {
    log('INFO', 'No previous data for comparison (first run?)');
    return;
  }

  // Build lookup map of previous data
  var previousMap = {};
  for (var i = 0; i < results.previousSnapshot.length; i++) {
    var prev = results.previousSnapshot[i];
    previousMap[prev.uniqueKey] = prev;
  }

  // Compare current to previous
  for (var j = 0; j < results.currentSnapshot.length; j++) {
    var current = results.currentSnapshot[j];
    var previous = previousMap[current.uniqueKey];

    if (!previous) continue;  // New keyword, no comparison possible
    if (current.qualityScore === null || previous.qualityScore === null) continue;

    var qsChange = current.qualityScore - previous.qualityScore;

    // Detect significant drops
    if (qsChange <= -CONFIG.QS_DROP_THRESHOLD) {
      results.changeAlerts.push({
        keyword: current.keyword,
        campaignName: current.campaignName,
        adGroupName: current.adGroupName,
        matchType: current.matchType,
        previousQS: previous.qualityScore,
        currentQS: current.qualityScore,
        change: qsChange,
        changeType: 'QS_DROP',
        severity: current.qualityScore <= CONFIG.QS_LOW_THRESHOLD ? 'CRITICAL' : 'WARNING',
        expectedCTRChange: componentChanged(previous.expectedCTR, current.expectedCTR),
        adRelevanceChange: componentChanged(previous.adRelevance, current.adRelevance),
        landingPageChange: componentChanged(previous.landingPage, current.landingPage),
        impressions: current.impressions,
        cost: current.cost
      });
      results.summary.qsDrops++;
      results.summary.newAlerts++;
    }

    // Detect improvements
    if (qsChange >= CONFIG.QS_DROP_THRESHOLD) {
      results.summary.qsImprovements++;
    }

    // Detect component downgrades (even without overall QS change)
    if (CONFIG.ALERT_ON_COMPONENT_CHANGE) {
      var componentDowngrade = false;
      var downgradeDetails = [];

      if (componentDowngraded(previous.expectedCTR, current.expectedCTR)) {
        componentDowngrade = true;
        downgradeDetails.push('CTR: ' + previous.expectedCTR + ' → ' + current.expectedCTR);
      }
      if (componentDowngraded(previous.adRelevance, current.adRelevance)) {
        componentDowngrade = true;
        downgradeDetails.push('Relevance: ' + previous.adRelevance + ' → ' + current.adRelevance);
      }
      if (componentDowngraded(previous.landingPage, current.landingPage)) {
        componentDowngrade = true;
        downgradeDetails.push('Landing: ' + previous.landingPage + ' → ' + current.landingPage);
      }

      if (componentDowngrade && qsChange > -CONFIG.QS_DROP_THRESHOLD) {
        // Component downgrade without major QS drop
        results.changeAlerts.push({
          keyword: current.keyword,
          campaignName: current.campaignName,
          adGroupName: current.adGroupName,
          matchType: current.matchType,
          previousQS: previous.qualityScore,
          currentQS: current.qualityScore,
          change: qsChange,
          changeType: 'COMPONENT_DOWNGRADE',
          severity: 'INFO',
          details: downgradeDetails.join('; '),
          impressions: current.impressions,
          cost: current.cost
        });
      }
    }
  }

  // Sort alerts by severity and cost
  var severityOrder = { 'CRITICAL': 0, 'WARNING': 1, 'INFO': 2 };
  results.changeAlerts.sort(function(a, b) {
    var sevDiff = (severityOrder[a.severity] || 3) - (severityOrder[b.severity] || 3);
    if (sevDiff !== 0) return sevDiff;
    return b.cost - a.cost;
  });

  log('INFO', 'Detected ' + results.changeAlerts.length + ' change alerts');
}

function componentChanged(prev, curr) {
  if (prev === curr) return 'No change';
  return prev + ' → ' + curr;
}

function componentDowngraded(prev, curr) {
  var levels = { 'ABOVE_AVERAGE': 2, 'AVERAGE': 1, 'BELOW_AVERAGE': 0 };
  var prevLevel = levels[prev];
  var currLevel = levels[curr];

  if (prevLevel === undefined || currLevel === undefined) return false;
  return currLevel < prevLevel;
}

function calculateAverageQS(snapshot) {
  var total = 0;
  var count = 0;

  for (var i = 0; i < snapshot.length; i++) {
    if (snapshot[i].qualityScore !== null && snapshot[i].qualityScore !== '') {
      total += parseInt(snapshot[i].qualityScore, 10);
      count++;
    }
  }

  return count > 0 ? total / count : null;
}

/******************************************************************************
 * COMPONENT ANALYSIS
 ******************************************************************************/
function analyzeComponents(results) {
  // Calculate component percentages
  var total = results.summary.keywordsWithQS;
  if (total === 0) return;

  results.componentAnalysis.expectedCTR.abovePct = (results.componentAnalysis.expectedCTR.above / total * 100).toFixed(1);
  results.componentAnalysis.expectedCTR.averagePct = (results.componentAnalysis.expectedCTR.average / total * 100).toFixed(1);
  results.componentAnalysis.expectedCTR.belowPct = (results.componentAnalysis.expectedCTR.below / total * 100).toFixed(1);

  results.componentAnalysis.adRelevance.abovePct = (results.componentAnalysis.adRelevance.above / total * 100).toFixed(1);
  results.componentAnalysis.adRelevance.averagePct = (results.componentAnalysis.adRelevance.average / total * 100).toFixed(1);
  results.componentAnalysis.adRelevance.belowPct = (results.componentAnalysis.adRelevance.below / total * 100).toFixed(1);

  results.componentAnalysis.landingPage.abovePct = (results.componentAnalysis.landingPage.above / total * 100).toFixed(1);
  results.componentAnalysis.landingPage.averagePct = (results.componentAnalysis.landingPage.average / total * 100).toFixed(1);
  results.componentAnalysis.landingPage.belowPct = (results.componentAnalysis.landingPage.below / total * 100).toFixed(1);
}

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

  if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL === 'YOUR_SPREADSHEET_URL_HERE' || CONFIG.SPREADSHEET_URL === 'CREATE_NEW') {
    ss = SpreadsheetApp.create('PPC.io Quality Score Tracker - ' +
                               AdsApp.currentAccount().getName() + ' - ' +
                               formatDate(new Date()));
    log('INFO', '!!! IMPORTANT: Created new spreadsheet !!!');
    log('INFO', '!!! Copy this URL to SPREADSHEET_URL config to maintain historical data !!!');
    log('INFO', 'Spreadsheet URL: ' + ss.getUrl());
  } else {
    ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
    log('INFO', 'Opened existing spreadsheet for historical tracking');
  }

  return ss;
}

function writeAllSheets(ss, results) {
  // 1. Summary
  writeSummarySheet(ss, results);

  // 2. Current Snapshot
  if (results.currentSnapshot.length > 0) {
    var snapshotData = results.currentSnapshot
      .filter(function(kw) { return kw.qualityScore !== null; })
      .map(function(kw) {
        return {
          'Campaign': kw.campaignName,
          'Ad Group': kw.adGroupName,
          'Keyword': kw.keyword,
          'Match Type': kw.matchType,
          'Quality Score': kw.qualityScore,
          'Expected CTR': kw.expectedCTR,
          'Ad Relevance': kw.adRelevance,
          'Landing Page': kw.landingPage,
          'Impressions': kw.impressions,
          'Clicks': kw.clicks,
          'CTR': kw.ctr.toFixed(2) + '%',
          'Cost': '$' + kw.cost.toFixed(2)
        };
      });

    // Sort by QS ascending (lowest first)
    snapshotData.sort(function(a, b) { return a['Quality Score'] - b['Quality Score']; });

    writeSheet(ss, '2. Current Snapshot', snapshotData,
      ['Campaign', 'Ad Group', 'Keyword', 'Match Type', 'Quality Score',
       'Expected CTR', 'Ad Relevance', 'Landing Page', 'Impressions', 'Cost']);
  }

  // 3. Append to Historical Data
  appendToHistory(ss, results);

  // 4. Change Alerts
  if (results.changeAlerts.length > 0) {
    var alertData = results.changeAlerts.map(function(alert) {
      return {
        'Keyword': alert.keyword,
        'Campaign': alert.campaignName,
        'Ad Group': alert.adGroupName,
        'Previous QS': alert.previousQS,
        'Current QS': alert.currentQS,
        'Change': alert.change > 0 ? '+' + alert.change : alert.change,
        'Change Type': alert.changeType,
        'Severity': alert.severity,
        'Details': alert.details || (alert.expectedCTRChange + ' | ' + alert.adRelevanceChange + ' | ' + alert.landingPageChange),
        'Cost': '$' + alert.cost.toFixed(2)
      };
    });
    writeSheet(ss, '4. Change Alerts', alertData,
      ['Keyword', 'Campaign', 'Severity', 'Previous QS', 'Current QS', 'Change', 'Change Type', 'Details', 'Cost']);
  }

  // 5. Component Analysis
  writeComponentAnalysisSheet(ss, results);

  // 6. QS Distribution
  writeDistributionSheet(ss, results);
}

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

  var avgQSFormatted = results.summary.avgQualityScore.toFixed(2);
  var qsChangeFormatted = results.summary.qsChange !== null ?
    (results.summary.qsChange > 0 ? '+' : '') + results.summary.qsChange.toFixed(2) : 'N/A (first run)';

  var qsEmoji = results.summary.avgQualityScore >= 7 ? '🟢' :
                results.summary.avgQualityScore >= 5 ? '🟡' : '🔴';

  var data = [
    ['QUALITY SCORE TRACKER', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Account: ' + AdsApp.currentAccount().getName(), ''],
    ['Snapshot Date: ' + formatDate(new Date()), ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['QUALITY SCORE OVERVIEW', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Average Quality Score', qsEmoji + ' ' + avgQSFormatted],
    ['Change from Previous', qsChangeFormatted],
    ['Median Quality Score', results.summary.medianQS.toFixed(1)],
    ['', ''],
    ['Keywords Analyzed', results.summary.totalKeywords],
    ['Keywords with QS Data', results.summary.keywordsWithQS],
    ['', ''],
    ['High QS Keywords (8-10)', results.summary.highQSCount],
    ['Low QS Keywords (1-' + CONFIG.QS_LOW_THRESHOLD + ')', results.summary.lowQSCount],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['CHANGE DETECTION', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['QS Drops Detected', results.summary.qsDrops],
    ['QS Improvements Detected', results.summary.qsImprovements],
    ['New Alerts', results.summary.newAlerts],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['COMPONENT BREAKDOWN', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['EXPECTED CTR:', ''],
    ['  Above Average', results.componentAnalysis.expectedCTR.above + ' (' + results.componentAnalysis.expectedCTR.abovePct + '%)'],
    ['  Average', results.componentAnalysis.expectedCTR.average + ' (' + results.componentAnalysis.expectedCTR.averagePct + '%)'],
    ['  Below Average', results.componentAnalysis.expectedCTR.below + ' (' + results.componentAnalysis.expectedCTR.belowPct + '%)'],
    ['', ''],
    ['AD RELEVANCE:', ''],
    ['  Above Average', results.componentAnalysis.adRelevance.above + ' (' + results.componentAnalysis.adRelevance.abovePct + '%)'],
    ['  Average', results.componentAnalysis.adRelevance.average + ' (' + results.componentAnalysis.adRelevance.averagePct + '%)'],
    ['  Below Average', results.componentAnalysis.adRelevance.below + ' (' + results.componentAnalysis.adRelevance.belowPct + '%)'],
    ['', ''],
    ['LANDING PAGE EXPERIENCE:', ''],
    ['  Above Average', results.componentAnalysis.landingPage.above + ' (' + results.componentAnalysis.landingPage.abovePct + '%)'],
    ['  Average', results.componentAnalysis.landingPage.average + ' (' + results.componentAnalysis.landingPage.averagePct + '%)'],
    ['  Below Average', results.componentAnalysis.landingPage.below + ' (' + results.componentAnalysis.landingPage.belowPct + '%)'],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['IMPORTANT: HISTORICAL TRACKING', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['To maintain historical data across runs:', ''],
    ['1. Copy the spreadsheet URL from your browser', ''],
    ['2. Paste it into SPREADSHEET_URL in the script config', ''],
    ['3. Schedule the script to run daily', ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['AI ANALYSIS PROMPTS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Copy the QS data into Claude with these prompts:', ''],
    ['', ''],
    ['Prompt 1', '"What\'s happening to my Quality Scores over time?"'],
    ['Prompt 2', '"Which keywords have declining QS and why?"'],
    ['Prompt 3', '"What\'s dragging down my account-level Quality Score?"'],
    ['Prompt 4', '"Create a QS improvement action plan prioritized by impact"'],
    ['Prompt 5', '"Which component (CTR, Relevance, Landing Page) needs the most work?"']
  ];

  sheet.getRange(1, 1, data.length, 2).setValues(data);
  sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
  sheet.setColumnWidth(1, 350);
  sheet.setColumnWidth(2, 350);

  // Highlight key metrics
  highlightQSMetrics(sheet, results);
}

function highlightQSMetrics(sheet, results) {
  var data = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
    // Highlight average QS
    if (data[i][0] === 'Average Quality Score') {
      var color = results.summary.avgQualityScore >= 7 ? '#d4edda' :
                  results.summary.avgQualityScore >= 5 ? '#fff3cd' : '#f8d7da';
      sheet.getRange(i + 1, 2).setBackground(color).setFontWeight('bold');
    }

    // Highlight QS change
    if (data[i][0] === 'Change from Previous' && results.summary.qsChange !== null) {
      var color = results.summary.qsChange > 0 ? '#d4edda' :
                  results.summary.qsChange < 0 ? '#f8d7da' : '#ffffff';
      sheet.getRange(i + 1, 2).setBackground(color);
    }

    // Highlight alerts
    if (data[i][0] === 'New Alerts' && results.summary.newAlerts > 0) {
      sheet.getRange(i + 1, 2).setBackground('#f8d7da');
    }

    // Highlight low QS count
    if (data[i][0].indexOf('Low QS Keywords') === 0 && results.summary.lowQSCount > 0) {
      sheet.getRange(i + 1, 2).setBackground('#fff3cd');
    }
  }
}

function writeComponentAnalysisSheet(ss, results) {
  var sheet = ss.getSheetByName('5. Component Analysis');
  if (!sheet) {
    sheet = ss.insertSheet('5. Component Analysis');
  } else {
    sheet.clear();
  }

  var data = [
    ['Component', 'Above Average', 'Average', 'Below Average', 'Biggest Issue?'],
    ['Expected CTR',
     results.componentAnalysis.expectedCTR.above + ' (' + results.componentAnalysis.expectedCTR.abovePct + '%)',
     results.componentAnalysis.expectedCTR.average + ' (' + results.componentAnalysis.expectedCTR.averagePct + '%)',
     results.componentAnalysis.expectedCTR.below + ' (' + results.componentAnalysis.expectedCTR.belowPct + '%)',
     results.componentAnalysis.expectedCTR.below > results.componentAnalysis.adRelevance.below &&
     results.componentAnalysis.expectedCTR.below > results.componentAnalysis.landingPage.below ? 'YES' : ''
    ],
    ['Ad Relevance',
     results.componentAnalysis.adRelevance.above + ' (' + results.componentAnalysis.adRelevance.abovePct + '%)',
     results.componentAnalysis.adRelevance.average + ' (' + results.componentAnalysis.adRelevance.averagePct + '%)',
     results.componentAnalysis.adRelevance.below + ' (' + results.componentAnalysis.adRelevance.belowPct + '%)',
     results.componentAnalysis.adRelevance.below > results.componentAnalysis.expectedCTR.below &&
     results.componentAnalysis.adRelevance.below > results.componentAnalysis.landingPage.below ? 'YES' : ''
    ],
    ['Landing Page',
     results.componentAnalysis.landingPage.above + ' (' + results.componentAnalysis.landingPage.abovePct + '%)',
     results.componentAnalysis.landingPage.average + ' (' + results.componentAnalysis.landingPage.averagePct + '%)',
     results.componentAnalysis.landingPage.below + ' (' + results.componentAnalysis.landingPage.belowPct + '%)',
     results.componentAnalysis.landingPage.below > results.componentAnalysis.expectedCTR.below &&
     results.componentAnalysis.landingPage.below > results.componentAnalysis.adRelevance.below ? 'YES' : ''
    ]
  ];

  sheet.getRange(1, 1, data.length, 5).setValues(data);
  sheet.getRange(1, 1, 1, 5).setFontWeight('bold');
  sheet.setFrozenRows(1);

  // Color code
  for (var i = 2; i <= 4; i++) {
    sheet.getRange(i, 2).setBackground('#d4edda');  // Above = green
    sheet.getRange(i, 3).setBackground('#fff3cd');  // Average = yellow
    sheet.getRange(i, 4).setBackground('#f8d7da');  // Below = red
  }

  for (var col = 1; col <= 5; col++) {
    sheet.autoResizeColumn(col);
  }
}

function writeDistributionSheet(ss, results) {
  var sheet = ss.getSheetByName('6. QS Distribution');
  if (!sheet) {
    sheet = ss.insertSheet('6. QS Distribution');
  } else {
    sheet.clear();
  }

  var data = [['Quality Score', 'Keyword Count', 'Percentage']];

  var total = results.summary.keywordsWithQS;
  for (var qs = 1; qs <= 10; qs++) {
    var count = results.qsDistribution[qs] || 0;
    var pct = total > 0 ? (count / total * 100).toFixed(1) + '%' : '0%';
    data.push([qs, count, pct]);
  }

  sheet.getRange(1, 1, data.length, 3).setValues(data);
  sheet.getRange(1, 1, 1, 3).setFontWeight('bold');
  sheet.setFrozenRows(1);

  // Color code QS values
  for (var i = 2; i <= 11; i++) {
    var qsVal = i - 1;
    var color = qsVal >= 8 ? '#d4edda' : qsVal >= 5 ? '#fff3cd' : '#f8d7da';
    sheet.getRange(i, 1).setBackground(color);
  }

  for (var col = 1; col <= 3; col++) {
    sheet.autoResizeColumn(col);
  }
}

function writeSheet(ss, sheetName, data, columns) {
  if (!data || data.length === 0) {
    log('DEBUG', 'No data for sheet: ' + sheetName);
    return;
  }

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

  sheet.getRange(1, 1, 1, columns.length).setValues([columns]).setFontWeight('bold');
  sheet.setFrozenRows(1);

  var rows = data.map(function(row) {
    return columns.map(function(col) {
      var val = row[col];
      return val !== null && val !== undefined ? val : '';
    });
  });

  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, columns.length).setValues(batch);
  }

  // Color code QS and Severity columns
  applyQSColors(sheet, columns, rows.length);

  for (var col = 1; col <= Math.min(columns.length, 10); col++) {
    sheet.autoResizeColumn(col);
  }

  log('DEBUG', 'Wrote ' + rows.length + ' rows to ' + sheetName);
}

function applyQSColors(sheet, columns, numRows) {
  // Color code Quality Score column
  var qsCol = columns.indexOf('Quality Score') + 1;
  if (qsCol > 0 && numRows > 0) {
    var range = sheet.getRange(2, qsCol, numRows, 1);
    var values = range.getValues();
    var bgColors = values.map(function(row) {
      var qs = parseInt(row[0], 10);
      if (isNaN(qs)) return ['#ffffff'];
      return [qs >= 8 ? '#d4edda' : qs >= 5 ? '#fff3cd' : '#f8d7da'];
    });
    range.setBackgrounds(bgColors);
  }

  // Color code Severity column
  var sevCol = columns.indexOf('Severity') + 1;
  if (sevCol > 0 && numRows > 0) {
    var sevColors = { 'CRITICAL': '#f8d7da', 'WARNING': '#fff3cd', 'INFO': '#cce5ff' };
    var range = sheet.getRange(2, sevCol, numRows, 1);
    var values = range.getValues();
    var bgColors = values.map(function(row) {
      return [sevColors[row[0]] || '#ffffff'];
    });
    range.setBackgrounds(bgColors);
  }
}

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

  var qsEmoji = results.summary.avgQualityScore >= 7 ? '🟢' :
                results.summary.avgQualityScore >= 5 ? '🟡' : '🔴';

  var changeText = results.summary.qsChange !== null ?
    (results.summary.qsChange > 0 ? '+' : '') + results.summary.qsChange.toFixed(2) + ' from previous' :
    'First run - no comparison';

  var message = [
    'Quality Score Tracker Report',
    '',
    'Account: ' + AdsApp.currentAccount().getName(),
    'Date: ' + formatDate(new Date()),
    'Duration: ' + duration + 's',
    '',
    'Quality Score Summary:',
    qsEmoji + ' Average QS: ' + results.summary.avgQualityScore.toFixed(2),
    'Change: ' + changeText,
    '',
    'Keywords: ' + results.summary.keywordsWithQS + ' with QS data',
    'High QS (8-10): ' + results.summary.highQSCount,
    'Low QS (1-' + CONFIG.QS_LOW_THRESHOLD + '): ' + results.summary.lowQSCount,
    '',
    'Alerts: ' + results.summary.newAlerts,
    'QS Drops: ' + results.summary.qsDrops,
    'QS Improvements: ' + results.summary.qsImprovements,
    '',
    'Report: ' + spreadsheetUrl,
    '',
    '--',
    'Generated by PPC.io Script Engine'
  ].join('\n');

  if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
    try {
      var subjectEmoji = results.summary.newAlerts > 0 ? '⚠️' : qsEmoji;
      MailApp.sendEmail({
        to: CONFIG.EMAIL_RECIPIENTS.join(','),
        subject: '[PPC.io] QS Tracker ' + subjectEmoji + ' Avg: ' + results.summary.avgQualityScore.toFixed(1) +
                 ' | Alerts: ' + results.summary.newAlerts,
        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: ':chart_with_downwards_trend: *PPC.io Quality Score Tracker*\n```' + message + '```'
        })
      });
      log('INFO', 'Slack sent');
    } catch (e) {
      log('ERROR', 'Failed to send Slack: ' + e.message);
    }
  }
}

/******************************************************************************
 * UTILITY FUNCTIONS
 ******************************************************************************/
function passesCampaignFilter(campaignName) {
  if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
      campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
    return false;
  }
  if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
      campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
    return false;
  }
  return true;
}

function checkTimeLimit(startTime) {
  var elapsed = (new Date() - startTime) / 1000 / 60;
  if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
    throw new Error('TIME_LIMIT: Tracking stopped 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', 'QUALITY SCORE TRACKING COMPLETE');
  log('INFO', 'Duration: ' + duration + ' seconds');
  log('INFO', 'Keywords: ' + results.summary.keywordsWithQS);
  log('INFO', 'Avg QS: ' + results.summary.avgQualityScore.toFixed(2));
  log('INFO', 'Alerts: ' + results.summary.newAlerts);
  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] QS Tracker Failed - ' + AdsApp.currentAccount().getName(),
        body: 'Script failed after ' + ((new Date() - startTime) / 1000).toFixed(1) +
              ' seconds.\n\nError: ' + 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');
}