Free PPC

Toolkit

/******************************************************************************
 * A/B TESTING TOOL WITH STATISTICAL SIGNIFICANCE
 *
 * Generated by PPC.io Script Engine
 * https://ppc.io
 *
 * Purpose: Identify statistically significant ad test winners
 * Author:  PPC.io
 * Version: 1.0
 * Updated: 2025-01-14
 *
 * SETUP INSTRUCTIONS:
 * 1. Set SPREADSHEET_URL to 'CREATE_NEW' or paste existing URL
 * 2. Configure CONFIDENCE_LEVEL (default 95%)
 * 3. Run in Preview mode first to verify
 * 4. Set AUTO_PAUSE_LOSERS and DRY_RUN to enable auto-pausing
 * 5. Schedule: Weekly recommended
 *
 * USE CASE: "Which ad tests can I call winners today? How much longer do my tests need to run?"
 *
 * STATISTICAL METHODS:
 * - Chi-squared test for CTR significance
 * - Z-test for conversion rate significance
 * - Minimum detectable effect calculation
 * - Required sample size estimation
 *
 * OUTPUTS:
 * - 1. Summary: Tests ready for decision, tests running, inconclusive
 * - 2. Ready to Call: Tests with significant winners
 * - 3. Still Running: Tests needing more data
 * - 4. All Ad Details: Individual ad performance metrics
 * - 5. Test History: Log of paused losers (if enabled)
 *
 * CHANGELOG:
 * v1.0 - Initial release with Chi-squared significance testing
 *
 ******************************************************************************/

/******************************************************************************
 * CONFIGURATION - Adjust these values for your account
 ******************************************************************************/
var CONFIG = {
  // ═══════════════════════════════════════════════════════════════════════════
  // OUTPUT SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  SPREADSHEET_URL: 'CREATE_NEW',  // Or paste existing spreadsheet URL
  EMAIL_RECIPIENTS: [],           // ['email@example.com']
  SLACK_WEBHOOK_URL: '',          // Slack incoming webhook URL

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

  // ═══════════════════════════════════════════════════════════════════════════
  // FILTERS
  // ═══════════════════════════════════════════════════════════════════════════
  CAMPAIGN_NAME_CONTAINS: '',     // Filter to campaigns containing this
  CAMPAIGN_NAME_EXCLUDES: '',     // Exclude campaigns containing this
  INCLUDE_PAUSED_CAMPAIGNS: false,
  INCLUDE_PAUSED_ADS: false,
  MINIMUM_IMPRESSIONS_PER_AD: 100,  // Min impressions per ad
  MINIMUM_AD_GROUPS_ADS: 2,         // Min ads per ad group for a test

  // ═══════════════════════════════════════════════════════════════════════════
  // STATISTICAL SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  CONFIDENCE_LEVEL: 0.95,           // 95% confidence (0.90, 0.95, or 0.99)
  MINIMUM_DETECTABLE_EFFECT: 0.10,  // 10% relative difference to detect
  PRIMARY_METRIC: 'CTR',            // CTR or CONV_RATE

  // ═══════════════════════════════════════════════════════════════════════════
  // AUTOMATION (Optional)
  // ═══════════════════════════════════════════════════════════════════════════
  AUTO_PAUSE_LOSERS: false,       // Automatically pause losing ads
  DRY_RUN: true,                  // CRITICAL: Set false to actually pause
  MIN_WINNER_LIFT: 0.10,          // Winner must be 10% better to pause loser
  KEEP_MIN_ADS_PER_GROUP: 1,      // Always keep at least this many ads

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

// Z-scores for confidence levels
var Z_SCORES = {
  0.90: 1.645,
  0.95: 1.96,
  0.99: 2.576
};

/******************************************************************************
 * MAIN EXECUTION
 ******************************************************************************/
function main() {
  var startTime = new Date();
  log('INFO', 'A/B Testing Analysis started: ' + startTime.toISOString());

  try {
    var ss = initializeSpreadsheet();
    var results = analyzeAdTests(startTime);

    if (CONFIG.AUTO_PAUSE_LOSERS) {
      pauseLosingAds(results);
    }

    writeAllSheets(ss, results);
    sendNotifications(results, ss.getUrl(), startTime);
    logSummary(results, startTime);

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

/******************************************************************************
 * DATA COLLECTION & ANALYSIS
 ******************************************************************************/
function analyzeAdTests(startTime) {
  var results = {
    adGroups: [],           // Ad groups with test data
    readyToCall: [],        // Tests with significant winners
    stillRunning: [],       // Tests needing more data
    inconclusive: [],       // Tests with no clear winner
    allAds: [],             // All ads analyzed
    pausedAds: [],          // Ads paused (if automation enabled)
    summary: {
      totalAdGroups: 0,
      adGroupsWithTests: 0,
      testsReadyToCall: 0,
      testsStillRunning: 0,
      testsInconclusive: 0,
      adsAnalyzed: 0,
      adsPaused: 0,
      estimatedImprovementPct: 0
    }
  };

  // Collect ad performance data grouped by ad group
  log('INFO', 'Collecting ad performance data...');
  collectAdData(results, startTime);

  // Analyze each ad group for statistical significance
  log('INFO', 'Analyzing statistical significance...');
  analyzeSignificance(results, startTime);

  return results;
}

function collectAdData(results, startTime) {
  var adGroupMap = {};

  try {
    var query = 'SELECT ' +
      'campaign.id, ' +
      'campaign.name, ' +
      'ad_group.id, ' +
      'ad_group.name, ' +
      'ad_group_ad.ad.id, ' +
      'ad_group_ad.ad.type, ' +
      'ad_group_ad.status, ' +
      'ad_group_ad.ad.responsive_search_ad.headlines, ' +
      'ad_group_ad.ad.final_urls, ' +
      'metrics.impressions, ' +
      'metrics.clicks, ' +
      'metrics.cost_micros, ' +
      'metrics.conversions, ' +
      'metrics.conversions_value ' +
      'FROM ad_group_ad ' +
      'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
      'AND metrics.impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS_PER_AD + ' ' +
      'AND ad_group_ad.ad.type IN ("RESPONSIVE_SEARCH_AD", "EXPANDED_TEXT_AD")';

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

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

    var rows = AdsApp.search(query);
    var count = 0;

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

      if (!passesCampaignFilter(campaignName)) continue;

      var adGroupId = row['ad_group.id'];
      var adGroupName = row['ad_group.name'];
      var adId = row['ad_group_ad.ad.id'];

      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);
      var convValue = parseFloat(row['metrics.conversions_value'] || 0);

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

      var adData = {
        campaignId: row['campaign.id'],
        campaignName: campaignName,
        adGroupId: adGroupId,
        adGroupName: adGroupName,
        adId: adId,
        adType: row['ad_group_ad.ad.type'],
        status: row['ad_group_ad.status'],
        headlines: row['ad_group_ad.ad.responsive_search_ad.headlines'] || '',
        finalUrl: row['ad_group_ad.ad.final_urls'] || '',
        impressions: impressions,
        clicks: clicks,
        cost: cost,
        conversions: conversions,
        convValue: convValue,
        ctr: ctr,
        convRate: convRate,
        adObject: null // Will be populated if we need to pause
      };

      results.allAds.push(adData);
      results.summary.adsAnalyzed++;

      // Group by ad group
      if (!adGroupMap[adGroupId]) {
        adGroupMap[adGroupId] = {
          adGroupId: adGroupId,
          adGroupName: adGroupName,
          campaignName: campaignName,
          campaignId: row['campaign.id'],
          ads: []
        };
      }
      adGroupMap[adGroupId].ads.push(adData);

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

    // Convert to array and filter to valid tests
    for (var agId in adGroupMap) {
      var adGroup = adGroupMap[agId];
      if (adGroup.ads.length >= CONFIG.MINIMUM_AD_GROUPS_ADS) {
        results.adGroups.push(adGroup);
        results.summary.adGroupsWithTests++;
      }
    }

    results.summary.totalAdGroups = Object.keys(adGroupMap).length;

    log('INFO', 'Found ' + results.summary.adGroupsWithTests + ' ad groups with ' +
        CONFIG.MINIMUM_AD_GROUPS_ADS + '+ ads');

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

function collectAdDataFallback(results, startTime) {
  var adGroupMap = {};

  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 adSelector = campaign.ads()
      .withCondition('Impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS_PER_AD)
      .forDateRange(CONFIG.DATE_RANGE);

    if (!CONFIG.INCLUDE_PAUSED_ADS) {
      adSelector = adSelector.withCondition('Status = ENABLED');
    }

    var adIterator = adSelector.get();

    while (adIterator.hasNext()) {
      var ad = adIterator.next();
      var adGroup = ad.getAdGroup();
      var stats = ad.getStatsFor(CONFIG.DATE_RANGE);

      var impressions = stats.getImpressions();
      var clicks = stats.getClicks();

      var adData = {
        campaignId: campaign.getId(),
        campaignName: campaignName,
        adGroupId: adGroup.getId(),
        adGroupName: adGroup.getName(),
        adId: ad.getId(),
        adType: ad.getType(),
        status: ad.isEnabled() ? 'ENABLED' : 'PAUSED',
        impressions: impressions,
        clicks: clicks,
        cost: stats.getCost(),
        conversions: stats.getConversions(),
        convValue: stats.getConversionValue(),
        ctr: impressions > 0 ? clicks / impressions : 0,
        convRate: clicks > 0 ? stats.getConversions() / clicks : 0,
        adObject: ad
      };

      results.allAds.push(adData);
      results.summary.adsAnalyzed++;

      var agId = adGroup.getId();
      if (!adGroupMap[agId]) {
        adGroupMap[agId] = {
          adGroupId: agId,
          adGroupName: adGroup.getName(),
          campaignName: campaignName,
          campaignId: campaign.getId(),
          ads: []
        };
      }
      adGroupMap[agId].ads.push(adData);
    }

    checkTimeLimit(startTime);
  }

  for (var agId in adGroupMap) {
    var adGroup = adGroupMap[agId];
    if (adGroup.ads.length >= CONFIG.MINIMUM_AD_GROUPS_ADS) {
      results.adGroups.push(adGroup);
      results.summary.adGroupsWithTests++;
    }
  }

  results.summary.totalAdGroups = Object.keys(adGroupMap).length;
}

/******************************************************************************
 * STATISTICAL SIGNIFICANCE ANALYSIS
 ******************************************************************************/
function analyzeSignificance(results, startTime) {
  var zScore = Z_SCORES[CONFIG.CONFIDENCE_LEVEL] || 1.96;

  for (var i = 0; i < results.adGroups.length; i++) {
    var adGroup = results.adGroups[i];

    // Sort ads by primary metric
    adGroup.ads.sort(function(a, b) {
      if (CONFIG.PRIMARY_METRIC === 'CONV_RATE') {
        return b.convRate - a.convRate;
      }
      return b.ctr - a.ctr;
    });

    var bestAd = adGroup.ads[0];
    var challengerAds = adGroup.ads.slice(1);

    // Calculate significance for each pair
    var testResult = {
      adGroupId: adGroup.adGroupId,
      adGroupName: adGroup.adGroupName,
      campaignName: adGroup.campaignName,
      adCount: adGroup.ads.length,
      winner: bestAd,
      challengers: [],
      isSignificant: false,
      confidence: 0,
      status: 'RUNNING',
      recommendation: '',
      estimatedLift: 0,
      requiredSampleSize: 0,
      currentSampleSize: 0
    };

    // Calculate total sample size
    testResult.currentSampleSize = adGroup.ads.reduce(function(sum, ad) {
      return sum + ad.impressions;
    }, 0);

    // Compare best ad to each challenger
    var allSignificant = true;
    var maxConfidence = 0;

    for (var j = 0; j < challengerAds.length; j++) {
      var challenger = challengerAds[j];

      var significance;
      if (CONFIG.PRIMARY_METRIC === 'CONV_RATE') {
        significance = calculateConversionRateSignificance(bestAd, challenger, zScore);
      } else {
        significance = calculateCTRSignificance(bestAd, challenger, zScore);
      }

      challenger.vsWinner = significance;

      testResult.challengers.push({
        ad: challenger,
        pValue: significance.pValue,
        confidence: significance.confidence,
        isSignificant: significance.isSignificant,
        lift: significance.lift,
        requiredImpressions: significance.requiredImpressions
      });

      if (significance.confidence > maxConfidence) {
        maxConfidence = significance.confidence;
      }

      if (!significance.isSignificant) {
        allSignificant = false;
      }
    }

    testResult.confidence = maxConfidence;
    testResult.isSignificant = allSignificant && challengerAds.length > 0;

    // Calculate required sample size
    testResult.requiredSampleSize = calculateRequiredSampleSize(
      bestAd, CONFIG.MINIMUM_DETECTABLE_EFFECT, zScore
    );

    // Determine status and recommendation
    if (testResult.isSignificant) {
      testResult.status = 'READY_TO_CALL';
      testResult.recommendation = 'Winner: Ad ' + bestAd.adId + '. Consider pausing losers.';
      testResult.estimatedLift = calculateEstimatedLift(bestAd, challengerAds);
      results.readyToCall.push(testResult);
      results.summary.testsReadyToCall++;

    } else if (testResult.currentSampleSize < testResult.requiredSampleSize * 0.5) {
      testResult.status = 'STILL_RUNNING';
      var remaining = testResult.requiredSampleSize - testResult.currentSampleSize;
      testResult.recommendation = 'Need ~' + Math.round(remaining).toLocaleString() + ' more impressions';
      results.stillRunning.push(testResult);
      results.summary.testsStillRunning++;

    } else {
      testResult.status = 'INCONCLUSIVE';
      testResult.recommendation = 'No significant winner yet. Differences may be too small to detect.';
      results.inconclusive.push(testResult);
      results.summary.testsInconclusive++;
    }

    if ((i + 1) % 100 === 0) {
      checkTimeLimit(startTime);
    }
  }

  // Calculate estimated improvement if we pause losers
  results.summary.estimatedImprovementPct = calculateEstimatedImprovement(results);
}

function calculateCTRSignificance(ad1, ad2, zScore) {
  // Chi-squared test for two proportions
  var n1 = ad1.impressions;
  var n2 = ad2.impressions;
  var x1 = ad1.clicks;
  var x2 = ad2.clicks;

  var p1 = n1 > 0 ? x1 / n1 : 0;
  var p2 = n2 > 0 ? x2 / n2 : 0;

  // Pooled proportion
  var pPool = (n1 + n2) > 0 ? (x1 + x2) / (n1 + n2) : 0;

  // Standard error
  var se = Math.sqrt(pPool * (1 - pPool) * (1/n1 + 1/n2));

  // Z-score
  var z = se > 0 ? (p1 - p2) / se : 0;

  // Two-tailed p-value (simplified)
  var pValue = 2 * (1 - normalCDF(Math.abs(z)));

  // Confidence
  var confidence = 1 - pValue;

  // Lift
  var lift = p2 > 0 ? (p1 - p2) / p2 : 0;

  // Is significant at configured level?
  var isSignificant = Math.abs(z) >= zScore && lift >= CONFIG.MIN_WINNER_LIFT;

  // Required impressions to reach significance (rough estimate)
  var requiredImpressions = estimateRequiredImpressions(p1, p2, zScore);

  return {
    z: z,
    pValue: pValue,
    confidence: confidence,
    isSignificant: isSignificant,
    lift: lift,
    metric: 'CTR',
    ad1Value: p1,
    ad2Value: p2,
    requiredImpressions: requiredImpressions
  };
}

function calculateConversionRateSignificance(ad1, ad2, zScore) {
  var n1 = ad1.clicks;
  var n2 = ad2.clicks;
  var x1 = ad1.conversions;
  var x2 = ad2.conversions;

  if (n1 === 0 || n2 === 0) {
    return {
      z: 0,
      pValue: 1,
      confidence: 0,
      isSignificant: false,
      lift: 0,
      metric: 'CONV_RATE',
      ad1Value: 0,
      ad2Value: 0,
      requiredImpressions: 10000
    };
  }

  var p1 = x1 / n1;
  var p2 = x2 / n2;

  var pPool = (x1 + x2) / (n1 + n2);
  var se = Math.sqrt(pPool * (1 - pPool) * (1/n1 + 1/n2));
  var z = se > 0 ? (p1 - p2) / se : 0;
  var pValue = 2 * (1 - normalCDF(Math.abs(z)));
  var confidence = 1 - pValue;
  var lift = p2 > 0 ? (p1 - p2) / p2 : 0;
  var isSignificant = Math.abs(z) >= zScore && lift >= CONFIG.MIN_WINNER_LIFT;

  return {
    z: z,
    pValue: pValue,
    confidence: confidence,
    isSignificant: isSignificant,
    lift: lift,
    metric: 'CONV_RATE',
    ad1Value: p1,
    ad2Value: p2,
    requiredImpressions: 5000  // Simplified
  };
}

function normalCDF(z) {
  // Approximation of standard normal CDF
  var a1 =  0.254829592;
  var a2 = -0.284496736;
  var a3 =  1.421413741;
  var a4 = -1.453152027;
  var a5 =  1.061405429;
  var p  =  0.3275911;

  var sign = z < 0 ? -1 : 1;
  z = Math.abs(z) / Math.sqrt(2);

  var t = 1.0 / (1.0 + p * z);
  var y = 1.0 - (((((a5 * t + a4) * t) + a3) * t + a2) * t + a1) * t * Math.exp(-z * z);

  return 0.5 * (1.0 + sign * y);
}

function estimateRequiredImpressions(p1, p2, zScore) {
  // Simplified sample size calculation
  var delta = Math.abs(p1 - p2) || 0.01;
  var pAvg = (p1 + p2) / 2 || 0.02;
  var variance = pAvg * (1 - pAvg);

  // n = 2 * (z^2 * variance) / delta^2
  var n = 2 * (Math.pow(zScore, 2) * variance) / Math.pow(delta, 2);

  return Math.ceil(n);
}

function calculateRequiredSampleSize(bestAd, mde, zScore) {
  var p = bestAd.ctr || 0.02;
  var delta = p * mde;
  var variance = p * (1 - p);

  // Two-sample test, both alpha and beta
  var zBeta = 0.84; // 80% power

  var n = 2 * variance * Math.pow(zScore + zBeta, 2) / Math.pow(delta, 2);

  return Math.ceil(n) * 2; // Total for both ads
}

function calculateEstimatedLift(bestAd, challengers) {
  if (challengers.length === 0) return 0;

  var totalImpressions = challengers.reduce(function(sum, c) { return sum + c.impressions; }, 0);
  var weightedLift = 0;

  for (var i = 0; i < challengers.length; i++) {
    var challenger = challengers[i];
    var lift = challenger.ctr > 0 ? (bestAd.ctr - challenger.ctr) / challenger.ctr : 0;
    var weight = challenger.impressions / totalImpressions;
    weightedLift += lift * weight;
  }

  return weightedLift;
}

function calculateEstimatedImprovement(results) {
  if (results.readyToCall.length === 0) return 0;

  var totalImprovement = 0;
  var totalWeight = 0;

  for (var i = 0; i < results.readyToCall.length; i++) {
    var test = results.readyToCall[i];
    var weight = test.currentSampleSize;
    totalImprovement += test.estimatedLift * weight;
    totalWeight += weight;
  }

  return totalWeight > 0 ? (totalImprovement / totalWeight) * 100 : 0;
}

/******************************************************************************
 * AUTOMATION: PAUSE LOSING ADS
 ******************************************************************************/
function pauseLosingAds(results) {
  if (!CONFIG.AUTO_PAUSE_LOSERS) return;

  log('INFO', 'Evaluating ads to pause...');

  for (var i = 0; i < results.readyToCall.length; i++) {
    var test = results.readyToCall[i];

    // Skip if we'd have fewer than minimum ads
    var enabledAds = test.challengers.filter(function(c) {
      return c.ad.status === 'ENABLED';
    }).length + 1; // +1 for winner

    if (enabledAds <= CONFIG.KEEP_MIN_ADS_PER_GROUP) {
      log('DEBUG', 'Skipping ' + test.adGroupName + ' - would leave fewer than ' + CONFIG.KEEP_MIN_ADS_PER_GROUP + ' ads');
      continue;
    }

    // Pause losers that are significantly worse
    for (var j = 0; j < test.challengers.length; j++) {
      var challenger = test.challengers[j];

      if (!challenger.isSignificant) continue;
      if (challenger.ad.status !== 'ENABLED') continue;

      // Double-check lift threshold
      if (challenger.lift < CONFIG.MIN_WINNER_LIFT) continue;

      if (CONFIG.DRY_RUN) {
        log('INFO', '[DRY RUN] Would pause ad ' + challenger.ad.adId + ' in ' + test.adGroupName +
            ' (winner has ' + (challenger.lift * 100).toFixed(1) + '% better ' + CONFIG.PRIMARY_METRIC + ')');

        results.pausedAds.push({
          adId: challenger.ad.adId,
          adGroupName: test.adGroupName,
          campaignName: test.campaignName,
          reason: 'Losing to ad ' + test.winner.adId + ' by ' + (challenger.lift * 100).toFixed(1) + '%',
          confidence: (challenger.confidence * 100).toFixed(1) + '%',
          status: 'DRY_RUN'
        });

      } else {
        // Actually pause the ad
        try {
          if (challenger.ad.adObject) {
            challenger.ad.adObject.pause();

            results.pausedAds.push({
              adId: challenger.ad.adId,
              adGroupName: test.adGroupName,
              campaignName: test.campaignName,
              reason: 'Lost to ad ' + test.winner.adId + ' by ' + (challenger.lift * 100).toFixed(1) + '%',
              confidence: (challenger.confidence * 100).toFixed(1) + '%',
              status: 'PAUSED'
            });

            results.summary.adsPaused++;
            log('INFO', 'Paused ad ' + challenger.ad.adId + ' in ' + test.adGroupName);
          }
        } catch (e) {
          log('WARN', 'Failed to pause ad ' + challenger.ad.adId + ': ' + e.message);
        }
      }
    }
  }
}

/******************************************************************************
 * 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 A/B Testing - ' +
                               AdsApp.currentAccount().getName() + ' - ' +
                               formatDate(new Date()));
    log('INFO', 'Created spreadsheet: ' + ss.getUrl());
  } else {
    ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  }

  return ss;
}

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

  // 2. Ready to Call (significant winners)
  if (results.readyToCall.length > 0) {
    var readyData = results.readyToCall.map(function(test) {
      return {
        'Ad Group': test.adGroupName,
        'Campaign': test.campaignName,
        'Winner Ad ID': test.winner.adId,
        'Winner CTR': (test.winner.ctr * 100).toFixed(2) + '%',
        'Winner Conv Rate': (test.winner.convRate * 100).toFixed(2) + '%',
        'Confidence': (test.confidence * 100).toFixed(1) + '%',
        'Estimated Lift': (test.estimatedLift * 100).toFixed(1) + '%',
        'Ads in Test': test.adCount,
        'Total Impressions': test.currentSampleSize.toLocaleString(),
        'Recommendation': test.recommendation
      };
    });
    writeSheet(ss, '2. Ready to Call', readyData,
      ['Ad Group', 'Campaign', 'Winner Ad ID', 'Confidence', 'Estimated Lift',
       'Winner CTR', 'Winner Conv Rate', 'Ads in Test', 'Recommendation']);
  }

  // 3. Still Running
  if (results.stillRunning.length > 0) {
    var runningData = results.stillRunning.map(function(test) {
      return {
        'Ad Group': test.adGroupName,
        'Campaign': test.campaignName,
        'Current Leader': 'Ad ' + test.winner.adId,
        'Leader CTR': (test.winner.ctr * 100).toFixed(2) + '%',
        'Current Confidence': (test.confidence * 100).toFixed(1) + '%',
        'Ads in Test': test.adCount,
        'Current Impressions': test.currentSampleSize.toLocaleString(),
        'Estimated Required': test.requiredSampleSize.toLocaleString(),
        'Progress': ((test.currentSampleSize / test.requiredSampleSize) * 100).toFixed(0) + '%',
        'Recommendation': test.recommendation
      };
    });
    writeSheet(ss, '3. Still Running', runningData,
      ['Ad Group', 'Campaign', 'Current Leader', 'Current Confidence', 'Progress',
       'Current Impressions', 'Estimated Required', 'Recommendation']);
  }

  // 4. All Ad Details
  if (results.allAds.length > 0) {
    var adData = results.allAds.map(function(ad) {
      return {
        'Ad Group': ad.adGroupName,
        'Campaign': ad.campaignName,
        'Ad ID': ad.adId,
        'Ad Type': ad.adType,
        'Status': ad.status,
        'Impressions': ad.impressions,
        'Clicks': ad.clicks,
        'CTR': (ad.ctr * 100).toFixed(2) + '%',
        'Conversions': ad.conversions.toFixed(2),
        'Conv Rate': (ad.convRate * 100).toFixed(2) + '%',
        'Cost': '$' + ad.cost.toFixed(2),
        'Revenue': '$' + ad.convValue.toFixed(2)
      };
    });

    adData.sort(function(a, b) {
      if (a['Ad Group'] !== b['Ad Group']) return a['Ad Group'].localeCompare(b['Ad Group']);
      return parseFloat(b['CTR']) - parseFloat(a['CTR']);
    });

    writeSheet(ss, '4. All Ad Details', adData,
      ['Ad Group', 'Campaign', 'Ad ID', 'Status', 'Impressions', 'Clicks',
       'CTR', 'Conversions', 'Conv Rate', 'Cost']);
  }

  // 5. Paused Ads (if any)
  if (results.pausedAds.length > 0) {
    var pausedData = results.pausedAds.map(function(p) {
      return {
        'Ad ID': p.adId,
        'Ad Group': p.adGroupName,
        'Campaign': p.campaignName,
        'Reason': p.reason,
        'Confidence': p.confidence,
        'Status': p.status
      };
    });
    writeSheet(ss, '5. Paused Ads', pausedData,
      ['Ad ID', 'Ad Group', 'Campaign', 'Reason', 'Confidence', 'Status']);
  }
}

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

  var data = [
    ['A/B TESTING WITH STATISTICAL SIGNIFICANCE', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Account: ' + AdsApp.currentAccount().getName(), ''],
    ['Date Range: ' + CONFIG.DATE_RANGE, ''],
    ['Confidence Level: ' + (CONFIG.CONFIDENCE_LEVEL * 100) + '%', ''],
    ['Primary Metric: ' + CONFIG.PRIMARY_METRIC, ''],
    ['Export Date: ' + new Date().toISOString(), ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['TEST STATUS SUMMARY', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Total Ad Groups Analyzed', results.summary.totalAdGroups],
    ['Ad Groups with A/B Tests', results.summary.adGroupsWithTests],
    ['Total Ads Analyzed', results.summary.adsAnalyzed],
    ['', ''],
    ['✅ Tests READY TO CALL (significant winner)', results.summary.testsReadyToCall],
    ['⏳ Tests STILL RUNNING (need more data)', results.summary.testsStillRunning],
    ['❓ Tests INCONCLUSIVE (no clear winner)', results.summary.testsInconclusive],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['ESTIMATED IMPACT', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Estimated ' + CONFIG.PRIMARY_METRIC + ' Improvement if Winners Applied', results.summary.estimatedImprovementPct.toFixed(1) + '%']
  ];

  if (CONFIG.AUTO_PAUSE_LOSERS) {
    data.push(['', '']);
    data.push(['═══════════════════════════════════════════════════════════════', '']);
    data.push(['AUTOMATION', '']);
    data.push(['═══════════════════════════════════════════════════════════════', '']);
    data.push(['Mode', CONFIG.DRY_RUN ? 'DRY RUN (preview only)' : 'LIVE']);
    data.push(['Ads Paused', results.summary.adsPaused]);
  }

  data = data.concat([
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['HOW TO READ RESULTS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Confidence %', 'Probability that the difference is real, not random'],
    ['95%+ Confidence', 'Industry standard for declaring a winner'],
    ['Estimated Lift', 'Expected improvement if winner replaces losers'],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['AI ANALYSIS PROMPTS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Copy the test data into Claude with these prompts:', ''],
    ['', ''],
    ['Prompt 1', '"Which ad tests can I call winners today?"'],
    ['Prompt 2', '"How much longer do my tests need to run?"'],
    ['Prompt 3', '"What patterns do winning ads have in common?"'],
    ['Prompt 4', '"Which tests should I prioritize reviewing?"'],
    ['Prompt 5', '"Create an ad testing action plan based on these results"']
  ]);

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

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

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

  for (var i = 0; i < data.length; i++) {
    if (data[i][0].indexOf('READY TO CALL') !== -1) {
      sheet.getRange(i + 1, 2).setBackground('#d4edda').setFontWeight('bold');
    }
    if (data[i][0].indexOf('STILL RUNNING') !== -1) {
      sheet.getRange(i + 1, 2).setBackground('#fff3cd');
    }
    if (data[i][0].indexOf('INCONCLUSIVE') !== -1) {
      sheet.getRange(i + 1, 2).setBackground('#e9ecef');
    }
  }
}

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);
  }

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

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

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

  var message = [
    'A/B Testing Analysis Complete',
    '',
    'Account: ' + AdsApp.currentAccount().getName(),
    'Date Range: ' + CONFIG.DATE_RANGE,
    'Duration: ' + duration + 's',
    '',
    'Test Results:',
    '- Ready to Call: ' + results.summary.testsReadyToCall + ' tests with winners',
    '- Still Running: ' + results.summary.testsStillRunning + ' tests need data',
    '- Inconclusive: ' + results.summary.testsInconclusive + ' tests',
    '',
    'Estimated ' + CONFIG.PRIMARY_METRIC + ' Improvement: ' + results.summary.estimatedImprovementPct.toFixed(1) + '%'
  ];

  if (results.summary.adsPaused > 0) {
    message.push('');
    message.push('Ads Paused: ' + results.summary.adsPaused);
  }

  message = message.concat([
    '',
    'Report: ' + spreadsheetUrl,
    '',
    '--',
    'Generated by PPC.io Script Engine'
  ]);

  var messageText = message.join('\n');

  if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
    try {
      var emoji = results.summary.testsReadyToCall > 0 ? '✅' : '⏳';
      MailApp.sendEmail({
        to: CONFIG.EMAIL_RECIPIENTS.join(','),
        subject: '[PPC.io] A/B Testing ' + emoji + ' - ' + results.summary.testsReadyToCall + ' tests ready to call',
        body: messageText
      });
      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: ':test_tube: *PPC.io A/B Testing*\n```' + messageText + '```'
        })
      });
      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: Analysis 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', 'A/B TESTING ANALYSIS COMPLETE');
  log('INFO', 'Duration: ' + duration + ' seconds');
  log('INFO', 'Tests Analyzed: ' + results.summary.adGroupsWithTests);
  log('INFO', 'Ready to Call: ' + results.summary.testsReadyToCall);
  log('INFO', 'Still Running: ' + results.summary.testsStillRunning);
  log('INFO', 'Ads Paused: ' + results.summary.adsPaused);
  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] A/B Testing 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');
}