Free PPC

Toolkit

/******************************************************************************
 * SHOPPING PERFORMANCE EXPORT (AI-READY)
 *
 * Generated by PPC.io Script Engine
 * https://ppc.io
 *
 * Purpose: Export product-level Shopping performance with tier analysis
 * Author:  PPC.io
 * Version: 1.0
 * Updated: 2025-01-14
 *
 * SETUP INSTRUCTIONS:
 * 1. Set SPREADSHEET_URL to 'CREATE_NEW' or paste existing URL
 * 2. Set TARGET_ROAS to your goal ROAS (e.g., 4.0 for 400%)
 * 3. Run in Preview mode first to verify
 * 4. Schedule: Weekly recommended
 *
 * USE CASE: "Which products should I increase bids on? What products are wasting my budget?"
 *
 * PRODUCT TIERS (BCG Matrix):
 * - STARS: High ROAS (>target) + High volume - Scale these
 * - CASH COWS: Good ROAS (>target) + Moderate volume - Maintain
 * - QUESTION MARKS: Low volume - Need more data
 * - DOGS: Low ROAS (<target) + High spend - Fix or pause
 *
 * OUTPUTS:
 * - 1. Summary: Product counts by tier, opportunity values
 * - 2. All Products: Complete product performance data
 * - 3. Stars: Top performers to scale
 * - 4. Dogs: Underperformers to fix
 * - 5. Opportunities: Specific action items
 *
 * CHANGELOG:
 * v1.0 - Initial release with GAQL-first architecture
 *
 ******************************************************************************/

/******************************************************************************
 * 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: false,          // Include paused campaigns
  MINIMUM_IMPRESSIONS: 100,       // Minimum impressions for analysis
  MINIMUM_SPEND: 1,               // Minimum spend ($) for analysis

  // ═══════════════════════════════════════════════════════════════════════════
  // PERFORMANCE THRESHOLDS
  // ═══════════════════════════════════════════════════════════════════════════
  TARGET_ROAS: 4.0,               // Target ROAS (4.0 = 400%)
  GOOD_ROAS_MULTIPLIER: 1.0,      // ROAS >= TARGET * this = good
  HIGH_VOLUME_THRESHOLD: 10,      // Conversions >= this = high volume
  MODERATE_VOLUME_THRESHOLD: 3,   // Conversions >= this = moderate
  HIGH_SPEND_THRESHOLD: 100,      // Spend >= this = high spend for dogs

  // ═══════════════════════════════════════════════════════════════════════════
  // OPPORTUNITY DETECTION
  // ═══════════════════════════════════════════════════════════════════════════
  SCALE_OPPORTUNITY_ROAS: 5.0,    // Products with ROAS >= this are scale opportunities
  WASTE_SPEND_THRESHOLD: 50,      // Products with spend >= this and 0 conv = waste

  // ═══════════════════════════════════════════════════════════════════════════
  // 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', 'Shopping Performance Export started: ' + startTime.toISOString());

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

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

/******************************************************************************
 * DATA COLLECTION
 ******************************************************************************/
function analyzeShoppingPerformance(startTime) {
  var results = {
    products: [],
    stars: [],
    cashCows: [],
    questionMarks: [],
    dogs: [],
    opportunities: [],
    tierBreakdown: {},
    summary: {
      totalProducts: 0,
      totalSpend: 0,
      totalRevenue: 0,
      totalConversions: 0,
      overallROAS: 0,
      starCount: 0,
      cashCowCount: 0,
      questionMarkCount: 0,
      dogCount: 0,
      wastedSpend: 0,
      scaleOpportunityValue: 0
    }
  };

  // Fetch product performance data
  log('INFO', 'Fetching product performance data...');
  fetchProductData(results, startTime);

  // Classify products into tiers
  log('INFO', 'Classifying products into tiers...');
  classifyProducts(results);

  // Identify opportunities
  log('INFO', 'Identifying opportunities...');
  identifyOpportunities(results);

  return results;
}

function fetchProductData(results, startTime) {
  try {
    // GAQL query for Shopping product performance
    var query = 'SELECT ' +
      'campaign.id, ' +
      'campaign.name, ' +
      'segments.product_item_id, ' +
      'segments.product_title, ' +
      'segments.product_type_l1, ' +
      'segments.product_type_l2, ' +
      'segments.product_brand, ' +
      'segments.product_custom_attribute0, ' +
      'metrics.impressions, ' +
      'metrics.clicks, ' +
      'metrics.cost_micros, ' +
      'metrics.conversions, ' +
      'metrics.conversions_value ' +
      'FROM shopping_performance_view ' +
      'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
      'AND metrics.impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS;

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

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

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

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

      var productId = row['segments.product_item_id'] || '';
      var productTitle = row['segments.product_title'] || '';
      var productType = row['segments.product_type_l1'] || '';
      var productType2 = row['segments.product_type_l2'] || '';
      var brand = row['segments.product_brand'] || '';

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

      // Skip if below spend threshold
      if (cost < CONFIG.MINIMUM_SPEND) continue;

      // Calculate metrics
      var ctr = impressions > 0 ? (clicks / impressions) : 0;
      var convRate = clicks > 0 ? (conversions / clicks) : 0;
      var roas = cost > 0 ? (revenue / cost) : 0;
      var cpa = conversions > 0 ? (cost / conversions) : null;
      var aov = conversions > 0 ? (revenue / conversions) : null;

      var productData = {
        productId: productId,
        productTitle: productTitle,
        productType: productType,
        productType2: productType2,
        brand: brand,
        campaignName: campaignName,
        impressions: impressions,
        clicks: clicks,
        cost: cost,
        conversions: conversions,
        revenue: revenue,
        ctr: ctr,
        convRate: convRate,
        roas: roas,
        cpa: cpa,
        aov: aov,
        tier: null,
        recommendation: null
      };

      results.products.push(productData);
      results.summary.totalProducts++;
      results.summary.totalSpend += cost;
      results.summary.totalRevenue += revenue;
      results.summary.totalConversions += conversions;

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

    // Calculate overall ROAS
    if (results.summary.totalSpend > 0) {
      results.summary.overallROAS = results.summary.totalRevenue / results.summary.totalSpend;
    }

    log('INFO', 'Fetched ' + count + ' products');

  } catch (e) {
    log('WARN', 'GAQL shopping fetch failed, trying fallback: ' + e.message);
    fetchProductDataFallback(results, startTime);
  }
}

function fetchProductDataFallback(results, startTime) {
  // Fallback using product group report
  try {
    var report = AdsApp.report(
      'SELECT ' +
      'CampaignName, ' +
      'ProductTitle, ' +
      'Brand, ' +
      'ProductTypeL1, ' +
      'OfferId, ' +
      'Impressions, ' +
      'Clicks, ' +
      'Cost, ' +
      'Conversions, ' +
      'ConversionValue ' +
      'FROM PRODUCT_PARTITION_REPORT ' +
      'WHERE Impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS + ' ' +
      'DURING ' + CONFIG.DATE_RANGE
    );

    var rows = report.rows();

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

      if (!passesCampaignFilter(campaignName)) continue;

      var cost = parseFloat(row['Cost']) || 0;
      if (cost < CONFIG.MINIMUM_SPEND) continue;

      var impressions = parseInt(row['Impressions'], 10) || 0;
      var clicks = parseInt(row['Clicks'], 10) || 0;
      var conversions = parseFloat(row['Conversions']) || 0;
      var revenue = parseFloat(row['ConversionValue']) || 0;

      var productData = {
        productId: row['OfferId'] || '',
        productTitle: row['ProductTitle'] || '',
        productType: row['ProductTypeL1'] || '',
        brand: row['Brand'] || '',
        campaignName: campaignName,
        impressions: impressions,
        clicks: clicks,
        cost: cost,
        conversions: conversions,
        revenue: revenue,
        ctr: impressions > 0 ? (clicks / impressions) : 0,
        convRate: clicks > 0 ? (conversions / clicks) : 0,
        roas: cost > 0 ? (revenue / cost) : 0,
        cpa: conversions > 0 ? (cost / conversions) : null,
        aov: conversions > 0 ? (revenue / conversions) : null,
        tier: null,
        recommendation: null
      };

      results.products.push(productData);
      results.summary.totalProducts++;
      results.summary.totalSpend += cost;
      results.summary.totalRevenue += revenue;
      results.summary.totalConversions += conversions;
    }

    if (results.summary.totalSpend > 0) {
      results.summary.overallROAS = results.summary.totalRevenue / results.summary.totalSpend;
    }

  } catch (e) {
    log('ERROR', 'Fallback also failed: ' + e.message);
  }
}

/******************************************************************************
 * PRODUCT CLASSIFICATION (BCG Matrix)
 ******************************************************************************/
function classifyProducts(results) {
  var goodRoasThreshold = CONFIG.TARGET_ROAS * CONFIG.GOOD_ROAS_MULTIPLIER;

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

    var hasGoodROAS = product.roas >= goodRoasThreshold;
    var hasHighVolume = product.conversions >= CONFIG.HIGH_VOLUME_THRESHOLD;
    var hasModerateVolume = product.conversions >= CONFIG.MODERATE_VOLUME_THRESHOLD;
    var hasHighSpend = product.cost >= CONFIG.HIGH_SPEND_THRESHOLD;
    var hasLowVolume = product.conversions < CONFIG.MODERATE_VOLUME_THRESHOLD;

    // Classify into tiers
    if (hasGoodROAS && hasHighVolume) {
      // STAR: High ROAS + High volume
      product.tier = 'STAR';
      product.recommendation = 'Scale - Increase budget/bids';
      results.stars.push(product);
      results.summary.starCount++;

    } else if (hasGoodROAS && hasModerateVolume && !hasHighVolume) {
      // CASH COW: Good ROAS + Moderate volume
      product.tier = 'CASH_COW';
      product.recommendation = 'Maintain - Performing well';
      results.cashCows.push(product);
      results.summary.cashCowCount++;

    } else if (hasLowVolume) {
      // QUESTION MARK: Low volume - need more data
      product.tier = 'QUESTION_MARK';
      product.recommendation = 'Monitor - Needs more data';
      results.questionMarks.push(product);
      results.summary.questionMarkCount++;

    } else if (!hasGoodROAS && hasHighSpend) {
      // DOG: Low ROAS + High spend
      product.tier = 'DOG';
      product.recommendation = 'Fix or Pause - Underperforming';
      results.dogs.push(product);
      results.summary.dogCount++;
      results.summary.wastedSpend += product.cost;

    } else if (!hasGoodROAS) {
      // DOG: Low ROAS (moderate spend)
      product.tier = 'DOG';
      product.recommendation = 'Review - Below target ROAS';
      results.dogs.push(product);
      results.summary.dogCount++;

    } else {
      // Default to question mark
      product.tier = 'QUESTION_MARK';
      product.recommendation = 'Monitor';
      results.questionMarks.push(product);
      results.summary.questionMarkCount++;
    }

    // Track tier breakdown
    if (!results.tierBreakdown[product.tier]) {
      results.tierBreakdown[product.tier] = {
        count: 0,
        spend: 0,
        revenue: 0,
        conversions: 0
      };
    }
    results.tierBreakdown[product.tier].count++;
    results.tierBreakdown[product.tier].spend += product.cost;
    results.tierBreakdown[product.tier].revenue += product.revenue;
    results.tierBreakdown[product.tier].conversions += product.conversions;
  }

  // Sort each tier by relevant metric
  results.stars.sort(function(a, b) { return b.revenue - a.revenue; });
  results.cashCows.sort(function(a, b) { return b.roas - a.roas; });
  results.dogs.sort(function(a, b) { return b.cost - a.cost; });
  results.questionMarks.sort(function(a, b) { return b.impressions - a.impressions; });

  log('INFO', 'Classification: Stars=' + results.summary.starCount +
              ', Cash Cows=' + results.summary.cashCowCount +
              ', Question Marks=' + results.summary.questionMarkCount +
              ', Dogs=' + results.summary.dogCount);
}

/******************************************************************************
 * OPPORTUNITY IDENTIFICATION
 ******************************************************************************/
function identifyOpportunities(results) {
  // Scale opportunities (high ROAS products)
  for (var i = 0; i < results.products.length; i++) {
    var product = results.products[i];

    if (product.roas >= CONFIG.SCALE_OPPORTUNITY_ROAS && product.conversions >= CONFIG.MODERATE_VOLUME_THRESHOLD) {
      results.opportunities.push({
        type: 'SCALE',
        productId: product.productId,
        productTitle: product.productTitle,
        metric: product.roas.toFixed(2) + 'x ROAS',
        currentSpend: product.cost,
        currentRevenue: product.revenue,
        action: 'Increase bids by 10-20% to capture more volume',
        priority: 'HIGH',
        potentialValue: product.revenue * 0.2 // Estimated additional revenue
      });
      results.summary.scaleOpportunityValue += product.revenue * 0.2;
    }
  }

  // Waste opportunities (high spend, zero conversions)
  for (var j = 0; j < results.products.length; j++) {
    var product = results.products[j];

    if (product.cost >= CONFIG.WASTE_SPEND_THRESHOLD && product.conversions === 0) {
      results.opportunities.push({
        type: 'WASTE',
        productId: product.productId,
        productTitle: product.productTitle,
        metric: '$' + product.cost.toFixed(2) + ' spent, 0 conversions',
        currentSpend: product.cost,
        currentRevenue: 0,
        action: 'Exclude product or reduce bids significantly',
        priority: 'HIGH',
        potentialSavings: product.cost
      });
    }
  }

  // Low CTR opportunities (impressions but no clicks)
  for (var k = 0; k < results.products.length; k++) {
    var product = results.products[k];

    if (product.impressions >= 1000 && product.ctr < 0.005) {
      results.opportunities.push({
        type: 'LOW_CTR',
        productId: product.productId,
        productTitle: product.productTitle,
        metric: (product.ctr * 100).toFixed(2) + '% CTR with ' + product.impressions + ' impressions',
        currentSpend: product.cost,
        currentRevenue: product.revenue,
        action: 'Improve product title, image, or price competitiveness',
        priority: 'MEDIUM',
        potentialValue: null
      });
    }
  }

  // Sort opportunities by priority
  var priorityOrder = { 'HIGH': 0, 'MEDIUM': 1, 'LOW': 2 };
  results.opportunities.sort(function(a, b) {
    return (priorityOrder[a.priority] || 3) - (priorityOrder[b.priority] || 3);
  });
}

/******************************************************************************
 * 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 Shopping Performance - ' +
                               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. All Products
  if (results.products.length > 0) {
    var productData = results.products.map(function(p) {
      return {
        'Product ID': p.productId,
        'Product Title': p.productTitle.substring(0, 80),
        'Brand': p.brand,
        'Product Type': p.productType,
        'Campaign': p.campaignName,
        'Tier': p.tier,
        'Impressions': p.impressions,
        'Clicks': p.clicks,
        'Cost': '$' + p.cost.toFixed(2),
        'Conversions': p.conversions.toFixed(2),
        'Revenue': '$' + p.revenue.toFixed(2),
        'ROAS': p.roas.toFixed(2) + 'x',
        'CTR': (p.ctr * 100).toFixed(2) + '%',
        'Conv Rate': (p.convRate * 100).toFixed(2) + '%',
        'CPA': p.cpa ? '$' + p.cpa.toFixed(2) : '-',
        'Recommendation': p.recommendation
      };
    });

    // Sort by revenue descending
    productData.sort(function(a, b) {
      return parseFloat(b.Revenue.replace(/[$,]/g, '')) - parseFloat(a.Revenue.replace(/[$,]/g, ''));
    });

    writeSheet(ss, '2. All Products', productData,
      ['Product Title', 'Tier', 'Cost', 'Revenue', 'ROAS', 'Conversions',
       'CTR', 'Conv Rate', 'Recommendation', 'Brand', 'Campaign']);
  }

  // 3. Stars (products to scale)
  if (results.stars.length > 0) {
    var starData = results.stars.map(formatProductRow);
    writeSheet(ss, '3. Stars (Scale)', starData,
      ['Product Title', 'ROAS', 'Revenue', 'Conversions', 'Cost', 'CTR', 'Brand', 'Recommendation']);
  }

  // 4. Dogs (underperformers)
  if (results.dogs.length > 0) {
    var dogData = results.dogs.map(formatProductRow);
    writeSheet(ss, '4. Dogs (Fix/Pause)', dogData,
      ['Product Title', 'ROAS', 'Cost', 'Revenue', 'Conversions', 'CTR', 'Brand', 'Recommendation']);
  }

  // 5. Opportunities
  if (results.opportunities.length > 0) {
    var oppData = results.opportunities.map(function(o) {
      return {
        'Type': o.type,
        'Product Title': o.productTitle.substring(0, 60),
        'Metric': o.metric,
        'Current Spend': '$' + o.currentSpend.toFixed(2),
        'Current Revenue': '$' + o.currentRevenue.toFixed(2),
        'Action': o.action,
        'Priority': o.priority,
        'Potential Value': o.potentialValue ? '$' + o.potentialValue.toFixed(2) : (o.potentialSavings ? 'Save $' + o.potentialSavings.toFixed(2) : '-')
      };
    });
    writeSheet(ss, '5. Opportunities', oppData,
      ['Type', 'Product Title', 'Priority', 'Metric', 'Action', 'Potential Value', 'Current Spend']);
  }

  // 6. Tier Analysis
  writeTierAnalysisSheet(ss, results);
}

function formatProductRow(product) {
  return {
    'Product Title': product.productTitle.substring(0, 60),
    'Product ID': product.productId,
    'Brand': product.brand,
    'Campaign': product.campaignName,
    'Impressions': product.impressions,
    'Clicks': product.clicks,
    'Cost': '$' + product.cost.toFixed(2),
    'Conversions': product.conversions.toFixed(2),
    'Revenue': '$' + product.revenue.toFixed(2),
    'ROAS': product.roas.toFixed(2) + 'x',
    'CTR': (product.ctr * 100).toFixed(2) + '%',
    'CPA': product.cpa ? '$' + product.cpa.toFixed(2) : '-',
    'Recommendation': product.recommendation
  };
}

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

  var roasEmoji = results.summary.overallROAS >= CONFIG.TARGET_ROAS ? '🟢' :
                  results.summary.overallROAS >= CONFIG.TARGET_ROAS * 0.7 ? '🟡' : '🔴';

  var data = [
    ['SHOPPING PERFORMANCE EXPORT', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Account: ' + AdsApp.currentAccount().getName(), ''],
    ['Date Range: ' + CONFIG.DATE_RANGE, ''],
    ['Target ROAS: ' + CONFIG.TARGET_ROAS + 'x', ''],
    ['Export Date: ' + new Date().toISOString(), ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['OVERALL PERFORMANCE', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Total Products Analyzed', results.summary.totalProducts],
    ['Total Spend', '$' + results.summary.totalSpend.toFixed(2)],
    ['Total Revenue', '$' + results.summary.totalRevenue.toFixed(2)],
    ['Total Conversions', results.summary.totalConversions.toFixed(2)],
    ['Overall ROAS', roasEmoji + ' ' + results.summary.overallROAS.toFixed(2) + 'x'],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['PRODUCT TIER BREAKDOWN (BCG Matrix)', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['⭐ STARS (Scale these)', results.summary.starCount + ' products'],
    ['💰 CASH COWS (Maintain)', results.summary.cashCowCount + ' products'],
    ['❓ QUESTION MARKS (Monitor)', results.summary.questionMarkCount + ' products'],
    ['🐕 DOGS (Fix or Pause)', results.summary.dogCount + ' products'],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['OPPORTUNITY SUMMARY', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Scale Opportunity Value', '$' + results.summary.scaleOpportunityValue.toFixed(2) + ' potential additional revenue'],
    ['Wasted Spend (Dogs)', '$' + results.summary.wastedSpend.toFixed(2) + ' on underperforming products'],
    ['Total Opportunities', results.opportunities.length],
    ['', '']
  ];

  // Add tier performance breakdown
  data.push(['═══════════════════════════════════════════════════════════════', '']);
  data.push(['TIER PERFORMANCE', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);

  for (var tier in results.tierBreakdown) {
    var tierData = results.tierBreakdown[tier];
    var tierRoas = tierData.spend > 0 ? (tierData.revenue / tierData.spend).toFixed(2) : 0;
    data.push([tier, tierData.count + ' products | $' + tierData.spend.toFixed(0) + ' spend | ' + tierRoas + 'x ROAS']);
  }

  data = data.concat([
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['TIER DEFINITIONS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['STAR', 'ROAS >= ' + CONFIG.TARGET_ROAS + 'x AND Conversions >= ' + CONFIG.HIGH_VOLUME_THRESHOLD],
    ['CASH COW', 'ROAS >= ' + CONFIG.TARGET_ROAS + 'x AND Conversions ' + CONFIG.MODERATE_VOLUME_THRESHOLD + '-' + (CONFIG.HIGH_VOLUME_THRESHOLD - 1)],
    ['QUESTION MARK', 'Conversions < ' + CONFIG.MODERATE_VOLUME_THRESHOLD + ' (insufficient data)'],
    ['DOG', 'ROAS < ' + CONFIG.TARGET_ROAS + 'x with significant spend'],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['AI ANALYSIS PROMPTS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Copy the product data into Claude with these prompts:', ''],
    ['', ''],
    ['Prompt 1', '"Which products should I increase bids on?"'],
    ['Prompt 2', '"What products are wasting my budget?"'],
    ['Prompt 3', '"How can I improve my Shopping campaign ROAS?"'],
    ['Prompt 4', '"What patterns do my top-performing products share?"'],
    ['Prompt 5', '"Create a product optimization action plan prioritized by impact"']
  ]);

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

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

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

  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === 'Overall ROAS') {
      var color = results.summary.overallROAS >= CONFIG.TARGET_ROAS ? '#d4edda' :
                  results.summary.overallROAS >= CONFIG.TARGET_ROAS * 0.7 ? '#fff3cd' : '#f8d7da';
      sheet.getRange(i + 1, 2).setBackground(color).setFontWeight('bold');
    }

    if (data[i][0].indexOf('STARS') !== -1) {
      sheet.getRange(i + 1, 2).setBackground('#d4edda');
    }
    if (data[i][0].indexOf('DOGS') !== -1) {
      sheet.getRange(i + 1, 2).setBackground('#f8d7da');
    }
    if (data[i][0] === 'Wasted Spend (Dogs)' && results.summary.wastedSpend > 0) {
      sheet.getRange(i + 1, 2).setBackground('#f8d7da');
    }
  }
}

function writeTierAnalysisSheet(ss, results) {
  var sheet = ss.getSheetByName('6. Tier Analysis');
  if (!sheet) {
    sheet = ss.insertSheet('6. Tier Analysis');
  } else {
    sheet.clear();
  }

  var headers = ['Tier', 'Products', 'Spend', 'Revenue', 'ROAS', 'Conversions', '% of Spend', '% of Revenue'];
  var data = [headers];

  var tierOrder = ['STAR', 'CASH_COW', 'QUESTION_MARK', 'DOG'];

  for (var i = 0; i < tierOrder.length; i++) {
    var tier = tierOrder[i];
    var tierData = results.tierBreakdown[tier] || { count: 0, spend: 0, revenue: 0, conversions: 0 };

    var roas = tierData.spend > 0 ? (tierData.revenue / tierData.spend) : 0;
    var spendPct = results.summary.totalSpend > 0 ? (tierData.spend / results.summary.totalSpend * 100) : 0;
    var revPct = results.summary.totalRevenue > 0 ? (tierData.revenue / results.summary.totalRevenue * 100) : 0;

    data.push([
      tier,
      tierData.count,
      '$' + tierData.spend.toFixed(2),
      '$' + tierData.revenue.toFixed(2),
      roas.toFixed(2) + 'x',
      tierData.conversions.toFixed(2),
      spendPct.toFixed(1) + '%',
      revPct.toFixed(1) + '%'
    ]);
  }

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

  // Color code tiers
  var tierColors = { 'STAR': '#d4edda', 'CASH_COW': '#cce5ff', 'QUESTION_MARK': '#fff3cd', 'DOG': '#f8d7da' };
  for (var j = 2; j <= data.length; j++) {
    var tierName = data[j - 1][0];
    if (tierColors[tierName]) {
      sheet.getRange(j, 1).setBackground(tierColors[tierName]);
    }
  }

  for (var col = 1; col <= headers.length; 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 Tier column
  applyTierColors(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 applyTierColors(sheet, columns, numRows) {
  var tierCol = columns.indexOf('Tier') + 1;
  var priorityCol = columns.indexOf('Priority') + 1;

  var colors = {
    'STAR': '#d4edda',
    'CASH_COW': '#cce5ff',
    'QUESTION_MARK': '#fff3cd',
    'DOG': '#f8d7da',
    'HIGH': '#f8d7da',
    'MEDIUM': '#fff3cd',
    'LOW': '#d4edda'
  };

  [tierCol, priorityCol].forEach(function(colIndex) {
    if (colIndex > 0 && numRows > 0) {
      var range = sheet.getRange(2, colIndex, numRows, 1);
      var values = range.getValues();
      var bgColors = values.map(function(row) {
        return [colors[row[0]] || '#ffffff'];
      });
      range.setBackgrounds(bgColors);
    }
  });
}

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

  var roasEmoji = results.summary.overallROAS >= CONFIG.TARGET_ROAS ? '🟢' :
                  results.summary.overallROAS >= CONFIG.TARGET_ROAS * 0.7 ? '🟡' : '🔴';

  var message = [
    'Shopping Performance Export Complete',
    '',
    'Account: ' + AdsApp.currentAccount().getName(),
    'Date Range: ' + CONFIG.DATE_RANGE,
    'Duration: ' + duration + 's',
    '',
    'Performance:',
    roasEmoji + ' Overall ROAS: ' + results.summary.overallROAS.toFixed(2) + 'x (Target: ' + CONFIG.TARGET_ROAS + 'x)',
    '- Spend: $' + results.summary.totalSpend.toFixed(2),
    '- Revenue: $' + results.summary.totalRevenue.toFixed(2),
    '',
    'Product Breakdown:',
    '- Stars: ' + results.summary.starCount,
    '- Cash Cows: ' + results.summary.cashCowCount,
    '- Question Marks: ' + results.summary.questionMarkCount,
    '- Dogs: ' + results.summary.dogCount,
    '',
    'Opportunities: ' + results.opportunities.length,
    '',
    'Report: ' + spreadsheetUrl,
    '',
    '--',
    '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] Shopping Performance ' + roasEmoji + ' ROAS: ' + results.summary.overallROAS.toFixed(2) + 'x',
        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: ':shopping_trolley: *PPC.io Shopping Performance*\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: Export 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', 'SHOPPING EXPORT COMPLETE');
  log('INFO', 'Duration: ' + duration + ' seconds');
  log('INFO', 'Products: ' + results.summary.totalProducts);
  log('INFO', 'ROAS: ' + results.summary.overallROAS.toFixed(2) + 'x');
  log('INFO', 'Stars: ' + results.summary.starCount);
  log('INFO', 'Dogs: ' + results.summary.dogCount);
  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] Shopping Export 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');
}