Free PPC

Toolkit

/******************************************************************************
 * BUDGET PACING INTELLIGENCE
 *
 * Generated by PPC.io Script Engine
 * https://ppc.io
 *
 * Purpose: Export daily spend patterns for AI-driven budget optimization
 * Author:  PPC.io
 * Version: 2.1
 * Updated: 2025-01-14
 *
 * SETUP INSTRUCTIONS:
 * 1. Set SPREADSHEET_URL to 'CREATE_NEW' or paste existing URL
 * 2. Run in Preview mode first to verify
 * 3. Schedule: Daily for ongoing tracking, or run on-demand
 *
 * USE CASE: "When should I increase or decrease budget based on this data?"
 *
 * OUTPUTS:
 * - 1. Summary: Overview with pacing status and recommendations
 * - 2. Daily Performance: Day-by-day spend and conversions
 * - 3. Hourly Performance: Hour-by-hour patterns
 * - 4. Day of Week: Which days perform best
 * - 5. Campaign Pacing: Per-campaign budget tracking
 *
 * CHANGELOG:
 * v2.1 - Fixed collectDailyDataFallback to properly collect aggregate stats
 * v2.0 - Added numbered sheets, enhanced recommendations, AI prompts
 * v1.0 - Initial release
 *
 ******************************************************************************/

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

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

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

  // ═══════════════════════════════════════════════════════════════════════════
  // DATE RANGES
  // ═══════════════════════════════════════════════════════════════════════════
  // Main analysis period
  DATE_RANGE: 'LAST_30_DAYS',

  // For hourly analysis (requires more granular data)
  HOURLY_DATE_RANGE: 'LAST_7_DAYS',

  // ═══════════════════════════════════════════════════════════════════════════
  // FILTERS
  // ═══════════════════════════════════════════════════════════════════════════
  CAMPAIGN_NAME_CONTAINS: '',      // Filter campaigns (empty = all)
  CAMPAIGN_NAME_EXCLUDES: '',      // Exclude campaigns containing this
  INCLUDE_PAUSED_CAMPAIGNS: false,

  // ═══════════════════════════════════════════════════════════════════════════
  // PACING THRESHOLDS
  // ═══════════════════════════════════════════════════════════════════════════
  // Flag days over/under these thresholds vs average
  OVERSPEND_THRESHOLD: 0.3,        // 30% over average
  UNDERSPEND_THRESHOLD: 0.3,       // 30% under average

  // Minimum data for analysis
  MIN_SPEND_FOR_ANALYSIS: 10,      // Minimum daily spend to include

  // ═══════════════════════════════════════════════════════════════════════════
  // 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', 'Budget Pacing Intelligence started: ' + startTime.toISOString());

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

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

/******************************************************************************
 * DATA COLLECTION
 ******************************************************************************/
function collectPacingData(startTime) {
  var results = {
    dailyPerformance: [],
    hourlyPerformance: [],
    dayOfWeekAnalysis: [],
    campaignPacing: [],
    recommendations: [],
    summary: {
      totalSpend: 0,
      totalConversions: 0,
      avgDailySpend: 0,
      avgCPA: 0,
      bestDays: [],
      worstDays: [],
      bestHours: [],
      worstHours: [],
      pacingStatus: 'ON_TRACK'
    }
  };

  // Collect daily data
  collectDailyData(results, startTime);

  // Collect hourly data
  collectHourlyData(results, startTime);

  // Analyze day of week patterns
  analyzeDayOfWeek(results);

  // Collect campaign pacing
  collectCampaignPacing(results, startTime);

  // Generate recommendations
  generateRecommendations(results);

  return results;
}

function collectDailyData(results, startTime) {
  var query = "SELECT " +
    "segments.date, " +
    "metrics.impressions, " +
    "metrics.clicks, " +
    "metrics.cost_micros, " +
    "metrics.conversions, " +
    "metrics.conversions_value, " +
    "metrics.ctr, " +
    "metrics.average_cpc " +
    "FROM campaign " +
    "WHERE campaign.advertising_channel_type = 'SEARCH' " +
    "AND segments.date DURING " + CONFIG.DATE_RANGE;

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

  var dailyData = {};

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

    while (rows.hasNext()) {
      var row = rows.next();
      var date = row['segments.date'];

      if (!dailyData[date]) {
        dailyData[date] = {
          date: date,
          impressions: 0,
          clicks: 0,
          cost: 0,
          conversions: 0,
          convValue: 0
        };
      }

      var costMicros = parseInt(row['metrics.cost_micros'], 10) || 0;

      dailyData[date].impressions += parseInt(row['metrics.impressions'], 10) || 0;
      dailyData[date].clicks += parseInt(row['metrics.clicks'], 10) || 0;
      dailyData[date].cost += costMicros / 1000000;
      dailyData[date].conversions += parseFloat(row['metrics.conversions']) || 0;
      dailyData[date].convValue += parseFloat(row['metrics.conversions_value']) || 0;
    }
  } catch (e) {
    log('ERROR', 'Daily data query failed: ' + e.message);
    collectDailyDataFallback(dailyData, startTime);
  }

  // Convert to array and calculate metrics
  var totalSpend = 0;
  var totalConv = 0;

  for (var date in dailyData) {
    var day = dailyData[date];

    if (day.cost < CONFIG.MIN_SPEND_FOR_ANALYSIS) continue;

    var ctr = day.impressions > 0 ? day.clicks / day.impressions : 0;
    var convRate = day.clicks > 0 ? day.conversions / day.clicks : 0;
    var cpa = day.conversions > 0 ? day.cost / day.conversions : null;
    var roas = day.cost > 0 ? day.convValue / day.cost : null;

    // Parse date to get day of week
    var dateObj = parseGAQLDate(date);
    var dayOfWeek = getDayOfWeek(dateObj);

    results.dailyPerformance.push({
      'Date': date,
      'Day': dayOfWeek,
      'Impressions': day.impressions,
      'Clicks': day.clicks,
      'Cost': day.cost,
      'Conversions': day.conversions,
      'Conv Value': day.convValue,
      'CTR': ctr,
      'Conv Rate': convRate,
      'CPA': cpa,
      'ROAS': roas
    });

    totalSpend += day.cost;
    totalConv += day.conversions;
  }

  // Sort by date
  results.dailyPerformance.sort(function(a, b) {
    return a.Date.localeCompare(b.Date);
  });

  // Calculate averages
  var numDays = results.dailyPerformance.length;
  results.summary.totalSpend = totalSpend;
  results.summary.totalConversions = totalConv;
  results.summary.avgDailySpend = numDays > 0 ? totalSpend / numDays : 0;
  results.summary.avgCPA = totalConv > 0 ? totalSpend / totalConv : 0;

  // Flag over/under spending days
  var avgSpend = results.summary.avgDailySpend;
  for (var i = 0; i < results.dailyPerformance.length; i++) {
    var perf = results.dailyPerformance[i];
    var deviation = avgSpend > 0 ? (perf.Cost - avgSpend) / avgSpend : 0;

    if (deviation > CONFIG.OVERSPEND_THRESHOLD) {
      perf['Pacing'] = 'OVER';
    } else if (deviation < -CONFIG.UNDERSPEND_THRESHOLD) {
      perf['Pacing'] = 'UNDER';
    } else {
      perf['Pacing'] = 'NORMAL';
    }
    perf['vs Avg'] = deviation;
  }

  log('INFO', 'Collected ' + results.dailyPerformance.length + ' days of data');
}

function collectDailyDataFallback(dailyData, startTime) {
  log('WARN', 'Using fallback for daily data - daily granularity not available via standard API');

  // Aggregate stats across all campaigns for the period
  var totalCost = 0;
  var totalImpressions = 0;
  var totalClicks = 0;
  var totalConversions = 0;
  var totalConvValue = 0;
  var campaignCount = 0;

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

  while (campaigns.hasNext()) {
    var campaign = campaigns.next();
    var name = campaign.getName();

    if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
        name.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
      continue;
    }
    if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
        name.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
      continue;
    }

    var stats = campaign.getStatsFor(CONFIG.DATE_RANGE);
    totalCost += stats.getCost();
    totalImpressions += stats.getImpressions();
    totalClicks += stats.getClicks();
    totalConversions += stats.getConversions();
    totalConvValue += stats.getConversionValue();
    campaignCount++;

    checkTimeLimit(startTime);
  }

  // Create aggregate entry with today's date as a placeholder
  // Note: Daily trend analysis will be limited with fallback data
  var today = Utilities.formatDate(new Date(), AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
  dailyData[today] = {
    date: today,
    impressions: totalImpressions,
    clicks: totalClicks,
    cost: totalCost,
    conversions: totalConversions,
    convValue: totalConvValue
  };

  log('INFO', 'Fallback collected aggregate from ' + campaignCount + ' campaigns. Daily trend analysis limited.');
}

function collectHourlyData(results, startTime) {
  var query = "SELECT " +
    "segments.hour, " +
    "metrics.impressions, " +
    "metrics.clicks, " +
    "metrics.cost_micros, " +
    "metrics.conversions, " +
    "metrics.conversions_value " +
    "FROM campaign " +
    "WHERE campaign.advertising_channel_type = 'SEARCH' " +
    "AND segments.date DURING " + CONFIG.HOURLY_DATE_RANGE;

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

  var hourlyData = {};
  for (var h = 0; h < 24; h++) {
    hourlyData[h] = {
      hour: h,
      impressions: 0,
      clicks: 0,
      cost: 0,
      conversions: 0,
      convValue: 0,
      days: 0
    };
  }

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

    while (rows.hasNext()) {
      var row = rows.next();
      var hour = parseInt(row['segments.hour'], 10);
      var costMicros = parseInt(row['metrics.cost_micros'], 10) || 0;

      hourlyData[hour].impressions += parseInt(row['metrics.impressions'], 10) || 0;
      hourlyData[hour].clicks += parseInt(row['metrics.clicks'], 10) || 0;
      hourlyData[hour].cost += costMicros / 1000000;
      hourlyData[hour].conversions += parseFloat(row['metrics.conversions']) || 0;
      hourlyData[hour].convValue += parseFloat(row['metrics.conversions_value']) || 0;
    }
  } catch (e) {
    log('WARN', 'Hourly data query failed: ' + e.message);
    // Hourly data is optional, continue without it
    return;
  }

  // Convert to array
  var totalHourlySpend = 0;
  var totalHourlyConv = 0;

  for (var hour in hourlyData) {
    var data = hourlyData[hour];
    totalHourlySpend += data.cost;
    totalHourlyConv += data.conversions;
  }

  var avgHourlySpend = totalHourlySpend / 24;

  for (var hr = 0; hr < 24; hr++) {
    var hData = hourlyData[hr];
    var convRate = hData.clicks > 0 ? hData.conversions / hData.clicks : 0;
    var cpa = hData.conversions > 0 ? hData.cost / hData.conversions : null;
    var efficiency = avgHourlySpend > 0 ? (hData.conversions / (hData.cost / avgHourlySpend)) : 0;

    var hourLabel = formatHour(hr);

    results.hourlyPerformance.push({
      'Hour': hourLabel,
      'Hour (24h)': hr,
      'Impressions': hData.impressions,
      'Clicks': hData.clicks,
      'Cost': hData.cost,
      'Conversions': hData.conversions,
      'Conv Value': hData.convValue,
      'Conv Rate': convRate,
      'CPA': cpa,
      'Efficiency Score': efficiency,
      'Recommendation': getHourRecommendation(hData.cost, hData.conversions, avgHourlySpend, totalHourlyConv / 24)
    });
  }

  // Find best and worst hours
  var sortedByConv = results.hourlyPerformance.slice().sort(function(a, b) {
    return b.Conversions - a.Conversions;
  });

  results.summary.bestHours = sortedByConv.slice(0, 3).map(function(h) { return h.Hour; });
  results.summary.worstHours = sortedByConv.slice(-3).reverse().map(function(h) { return h.Hour; });

  log('INFO', 'Collected hourly performance data');
}

function analyzeDayOfWeek(results) {
  var dowData = {
    'Sunday': { cost: 0, conversions: 0, convValue: 0, days: 0 },
    'Monday': { cost: 0, conversions: 0, convValue: 0, days: 0 },
    'Tuesday': { cost: 0, conversions: 0, convValue: 0, days: 0 },
    'Wednesday': { cost: 0, conversions: 0, convValue: 0, days: 0 },
    'Thursday': { cost: 0, conversions: 0, convValue: 0, days: 0 },
    'Friday': { cost: 0, conversions: 0, convValue: 0, days: 0 },
    'Saturday': { cost: 0, conversions: 0, convValue: 0, days: 0 }
  };

  for (var i = 0; i < results.dailyPerformance.length; i++) {
    var day = results.dailyPerformance[i];
    var dow = day.Day;

    if (dowData[dow]) {
      dowData[dow].cost += day.Cost;
      dowData[dow].conversions += day.Conversions;
      dowData[dow].convValue += day['Conv Value'];
      dowData[dow].days++;
    }
  }

  var dayOrder = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];

  for (var d = 0; d < dayOrder.length; d++) {
    var dayName = dayOrder[d];
    var data = dowData[dayName];

    var avgCost = data.days > 0 ? data.cost / data.days : 0;
    var avgConv = data.days > 0 ? data.conversions / data.days : 0;
    var cpa = data.conversions > 0 ? data.cost / data.conversions : null;
    var roas = data.cost > 0 ? data.convValue / data.cost : null;

    results.dayOfWeekAnalysis.push({
      'Day': dayName,
      'Total Cost': data.cost,
      'Total Conversions': data.conversions,
      'Avg Daily Cost': avgCost,
      'Avg Daily Conversions': avgConv,
      'CPA': cpa,
      'ROAS': roas,
      'Days Analyzed': data.days
    });
  }

  // Find best and worst days
  var sortedByConv = results.dayOfWeekAnalysis.slice().sort(function(a, b) {
    return b['Avg Daily Conversions'] - a['Avg Daily Conversions'];
  });

  results.summary.bestDays = sortedByConv.slice(0, 2).map(function(d) { return d.Day; });
  results.summary.worstDays = sortedByConv.slice(-2).reverse().map(function(d) { return d.Day; });
}

function collectCampaignPacing(results, startTime) {
  var today = new Date();
  var dayOfMonth = today.getDate();
  var daysInMonth = new Date(today.getFullYear(), today.getMonth() + 1, 0).getDate();
  var expectedPace = dayOfMonth / daysInMonth;

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

  while (campaigns.hasNext()) {
    var campaign = campaigns.next();
    var name = campaign.getName();

    if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
        name.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
      continue;
    }
    if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
        name.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
      continue;
    }

    var budget = campaign.getBudget();
    if (!budget) continue;

    var dailyBudget = budget.getAmount();
    var monthlyBudget = dailyBudget * daysInMonth;
    var expectedSpend = monthlyBudget * expectedPace;

    var stats = campaign.getStatsFor('THIS_MONTH');
    var actualSpend = stats.getCost();

    var pacePercent = expectedSpend > 0 ? ((actualSpend / expectedSpend) - 1) * 100 : 0;

    var pacingStatus;
    if (pacePercent > 15) pacingStatus = 'OVER';
    else if (pacePercent < -15) pacingStatus = 'UNDER';
    else pacingStatus = 'ON_TRACK';

    results.campaignPacing.push({
      'Campaign': name,
      'Daily Budget': dailyBudget,
      'Monthly Budget': monthlyBudget,
      'Expected Spend (MTD)': expectedSpend,
      'Actual Spend (MTD)': actualSpend,
      'Pace %': pacePercent,
      'Status': pacingStatus,
      'Conversions (MTD)': stats.getConversions(),
      'Projected Month End': actualSpend / expectedPace
    });

    checkTimeLimit(startTime);
  }

  // Sort by pace deviation
  results.campaignPacing.sort(function(a, b) {
    return Math.abs(b['Pace %']) - Math.abs(a['Pace %']);
  });

  log('INFO', 'Collected pacing for ' + results.campaignPacing.length + ' campaigns');
}

function generateRecommendations(results) {
  // Day of week recommendations
  if (results.summary.bestDays.length > 0) {
    results.recommendations.push({
      type: 'DAY_OF_WEEK',
      priority: 'HIGH',
      recommendation: 'Increase budget/bids on ' + results.summary.bestDays.join(' and ') +
                      ' - these are your best converting days.'
    });
  }

  if (results.summary.worstDays.length > 0) {
    var worstDay = results.dayOfWeekAnalysis.find(function(d) {
      return d.Day === results.summary.worstDays[0];
    });
    if (worstDay && worstDay.CPA && results.summary.avgCPA > 0 &&
        worstDay.CPA > results.summary.avgCPA * 1.5) {
      results.recommendations.push({
        type: 'DAY_OF_WEEK',
        priority: 'MEDIUM',
        recommendation: 'Consider reducing spend on ' + results.summary.worstDays.join(' and ') +
                        ' - CPA is significantly higher than average.'
      });
    }
  }

  // Hourly recommendations
  if (results.summary.bestHours.length > 0) {
    results.recommendations.push({
      type: 'HOURLY',
      priority: 'MEDIUM',
      recommendation: 'Peak conversion hours: ' + results.summary.bestHours.join(', ') +
                      '. Ensure budget is available during these times.'
    });
  }

  // Pacing recommendations
  var overPacing = results.campaignPacing.filter(function(c) { return c.Status === 'OVER'; });
  var underPacing = results.campaignPacing.filter(function(c) { return c.Status === 'UNDER'; });

  if (overPacing.length > 0) {
    results.recommendations.push({
      type: 'PACING',
      priority: 'HIGH',
      recommendation: overPacing.length + ' campaigns are overpacing. Review: ' +
                      overPacing.slice(0, 3).map(function(c) { return c.Campaign; }).join(', ')
    });
  }

  if (underPacing.length > 3) {
    results.recommendations.push({
      type: 'PACING',
      priority: 'MEDIUM',
      recommendation: underPacing.length + ' campaigns are underpacing. May have headroom for growth.'
    });
  }

  // Overall pacing status
  var overCount = overPacing.length;
  var underCount = underPacing.length;
  var onTrackCount = results.campaignPacing.length - overCount - underCount;

  if (overCount > underCount && overCount > onTrackCount) {
    results.summary.pacingStatus = 'OVERSPENDING';
  } else if (underCount > overCount && underCount > onTrackCount) {
    results.summary.pacingStatus = 'UNDERSPENDING';
  } else {
    results.summary.pacingStatus = 'ON_TRACK';
  }
}

/******************************************************************************
 * HELPER FUNCTIONS
 ******************************************************************************/
function parseGAQLDate(dateStr) {
  // GAQL date format: YYYY-MM-DD
  var parts = dateStr.split('-');
  return new Date(parseInt(parts[0]), parseInt(parts[1]) - 1, parseInt(parts[2]));
}

function getDayOfWeek(date) {
  var days = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
  return days[date.getDay()];
}

function formatHour(hour) {
  if (hour === 0) return '12 AM';
  if (hour === 12) return '12 PM';
  if (hour < 12) return hour + ' AM';
  return (hour - 12) + ' PM';
}

function getHourRecommendation(cost, conversions, avgCost, avgConv) {
  if (cost === 0) return 'No data';

  var costRatio = cost / avgCost;
  var convRatio = avgConv > 0 ? conversions / avgConv : 0;

  if (convRatio > 1.3 && costRatio < 1.2) return 'INCREASE - High efficiency';
  if (convRatio < 0.5 && costRatio > 0.8) return 'DECREASE - Low efficiency';
  if (convRatio > 1 && costRatio > 1.3) return 'MONITOR - High volume, high cost';
  return 'MAINTAIN';
}

/******************************************************************************
 * 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 Budget Pacing - ' +
                               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) {
  // Summary first (position 0)
  writeSummarySheet(ss, results);

  // Numbered sheets for clear navigation
  if (results.dailyPerformance.length > 0) {
    writeSheet(ss, '2. Daily Performance', results.dailyPerformance,
               ['Date', 'Day', 'Cost', 'Conversions', 'Conv Value', 'CPA', 'ROAS', 'Pacing', 'vs Avg']);
  }

  if (results.hourlyPerformance.length > 0) {
    writeSheet(ss, '3. Hourly Performance', results.hourlyPerformance,
               ['Hour', 'Cost', 'Conversions', 'Conv Rate', 'CPA', 'Efficiency Score', 'Recommendation']);
  }

  if (results.dayOfWeekAnalysis.length > 0) {
    writeSheet(ss, '4. Day of Week', results.dayOfWeekAnalysis,
               ['Day', 'Avg Daily Cost', 'Avg Daily Conversions', 'CPA', 'ROAS', 'Days Analyzed']);
  }

  if (results.campaignPacing.length > 0) {
    writeSheet(ss, '5. Campaign Pacing', results.campaignPacing,
               ['Campaign', 'Daily Budget', 'Expected Spend (MTD)', 'Actual Spend (MTD)',
                'Pace %', 'Status', 'Conversions (MTD)', 'Projected Month End']);
  }
}

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

  var data = [
    ['BUDGET PACING INTELLIGENCE', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Account: ' + AdsApp.currentAccount().getName(), ''],
    ['Date Range: ' + CONFIG.DATE_RANGE, ''],
    ['Export Date: ' + new Date().toISOString(), ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['PERFORMANCE OVERVIEW', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Total Spend', '$' + results.summary.totalSpend.toFixed(2)],
    ['Total Conversions', results.summary.totalConversions.toFixed(2)],
    ['Avg Daily Spend', '$' + results.summary.avgDailySpend.toFixed(2)],
    ['Overall CPA', '$' + results.summary.avgCPA.toFixed(2)],
    ['Overall Pacing Status', results.summary.pacingStatus],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['BEST PERFORMANCE WINDOWS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Best Days', results.summary.bestDays.join(', ') || 'N/A'],
    ['Worst Days', results.summary.worstDays.join(', ') || 'N/A'],
    ['Best Hours', results.summary.bestHours.join(', ') || 'N/A'],
    ['Worst Hours', results.summary.worstHours.join(', ') || 'N/A'],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['RECOMMENDATIONS', ''],
    ['═══════════════════════════════════════════════════════════════', '']
  ];

  if (results.recommendations.length === 0) {
    data.push(['No immediate recommendations - performance is balanced.', '']);
  } else {
    for (var i = 0; i < results.recommendations.length; i++) {
      var rec = results.recommendations[i];
      data.push(['[' + rec.priority + '] ' + rec.recommendation, '']);
    }
  }

  data.push(['', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);
  data.push(['AI ANALYSIS PROMPTS', '']);
  data.push(['═══════════════════════════════════════════════════════════════', '']);
  data.push(['Paste this data into Claude and ask:', '']);
  data.push(['', '']);
  data.push(['1. "Based on this data, when should I increase or decrease budget?"', '']);
  data.push(['2. "Create an optimal ad schedule based on hourly performance."', '']);
  data.push(['3. "Which campaigns should I reallocate budget to?"', '']);
  data.push(['4. "Predict next month\'s spend and conversions based on these patterns."', '']);
  data.push(['5. "Identify anomalies in daily spend that need investigation."', '']);

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

function writeSheet(ss, sheetName, data, columns) {
  if (!data || data.length === 0) 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);
  }

  // Format columns
  formatColumn(sheet, columns, 'Cost', '$#,##0.00', rows.length);
  formatColumn(sheet, columns, 'CPA', '$#,##0.00', rows.length);
  formatColumn(sheet, columns, 'Conv Value', '$#,##0.00', rows.length);
  formatColumn(sheet, columns, 'ROAS', '0.00', rows.length);
  formatColumn(sheet, columns, 'Conv Rate', '0.00%', rows.length);
  formatColumn(sheet, columns, 'vs Avg', '0.00%', rows.length);
  formatColumn(sheet, columns, 'Pace %', '0.00%', rows.length);
  formatColumn(sheet, columns, 'Daily Budget', '$#,##0.00', rows.length);
  formatColumn(sheet, columns, 'Expected Spend (MTD)', '$#,##0.00', rows.length);
  formatColumn(sheet, columns, 'Actual Spend (MTD)', '$#,##0.00', rows.length);
  formatColumn(sheet, columns, 'Projected Month End', '$#,##0.00', rows.length);
  formatColumn(sheet, columns, 'Avg Daily Cost', '$#,##0.00', rows.length);

  // Color code status columns
  applyStatusFormatting(sheet, columns, rows.length);

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

function applyStatusFormatting(sheet, headers, numRows) {
  var statusCols = ['Status', 'Pacing'];

  for (var s = 0; s < statusCols.length; s++) {
    var colIndex = headers.indexOf(statusCols[s]) + 1;
    if (colIndex === 0) continue;

    var range = sheet.getRange(2, colIndex, numRows, 1);
    var values = range.getValues();
    var colors = values.map(function(row) {
      if (row[0] === 'OVER' || row[0] === 'OVERSPENDING') return ['#f8d7da'];
      if (row[0] === 'UNDER' || row[0] === 'UNDERSPENDING') return ['#fff3cd'];
      if (row[0] === 'ON_TRACK' || row[0] === 'NORMAL') return ['#d4edda'];
      return ['#ffffff'];
    });
    range.setBackgrounds(colors);
  }
}

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

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

  var message = [
    'Budget Pacing Intelligence Complete',
    '',
    'Account: ' + AdsApp.currentAccount().getName(),
    'Duration: ' + duration + 's',
    '',
    'Summary:',
    '- Total Spend: $' + results.summary.totalSpend.toFixed(2),
    '- Total Conversions: ' + results.summary.totalConversions.toFixed(2),
    '- Avg CPA: $' + results.summary.avgCPA.toFixed(2),
    '- Pacing Status: ' + results.summary.pacingStatus,
    '',
    'Best Days: ' + results.summary.bestDays.join(', '),
    'Best Hours: ' + results.summary.bestHours.join(', '),
    '',
    'Recommendations: ' + results.recommendations.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] Budget Pacing - ' + results.summary.pacingStatus,
        body: message
      });
      log('INFO', 'Email sent');
    } catch (e) {
      log('ERROR', 'Failed to send email: ' + e.message);
    }
  }

  if (CONFIG.SLACK_WEBHOOK_URL) {
    var emoji = results.summary.pacingStatus === 'ON_TRACK' ? ':chart_with_upwards_trend:' : ':warning:';
    try {
      UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify({
          text: emoji + ' *PPC.io Budget Pacing*\n```' + message + '```'
        })
      });
      log('INFO', 'Slack sent');
    } catch (e) {
      log('ERROR', 'Failed to send Slack: ' + e.message);
    }
  }
}

/******************************************************************************
 * UTILITY FUNCTIONS
 ******************************************************************************/
function checkTimeLimit(startTime) {
  var elapsed = (new Date() - startTime) / 1000 / 60;
  if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
    throw new Error('TIME_LIMIT: 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', 'BUDGET PACING COMPLETE');
  log('INFO', 'Duration: ' + duration + ' seconds');
  log('INFO', 'Days Analyzed: ' + results.dailyPerformance.length);
  log('INFO', 'Campaigns: ' + results.campaignPacing.length);
  log('INFO', 'Recommendations: ' + results.recommendations.length);
  log('INFO', '════════════════════════════════════════');
}

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

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

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