Free PPC

Toolkit

/******************************************************************************
 * CONVERSION ACTION HEALTH CHECK
 *
 * Generated by PPC.io Script Engine
 * https://ppc.io
 *
 * Purpose: Audit conversion tracking setup for misconfigurations and issues
 * Author:  PPC.io
 * Version: 1.0
 * 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: Weekly recommended
 *
 * USE CASE: "Are my conversion actions set up correctly? Why might I be missing conversions?"
 *
 * HEALTH CHECKS PERFORMED:
 * - Actions with 0 conversions in last 30 days
 * - Actions with counting type mismatches (lead vs ecommerce)
 * - Actions not included in "Conversions" column
 * - Duplicate actions (same name)
 * - Very old conversion windows
 * - Missing attribution models
 *
 * OUTPUTS:
 * - 1. Summary: Health overview, issue counts, AI prompts
 * - 2. All Actions: Complete inventory with settings
 * - 3. Issues Found: Specific problems and fixes
 * - 4. Best Practices: Setup checklist
 *
 * CHANGELOG:
 * v1.0 - Initial release with comprehensive health checks
 *
 ******************************************************************************/

/******************************************************************************
 * 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 FOR CONVERSION DATA
  // ═══════════════════════════════════════════════════════════════════════════
  DATE_RANGE: 'LAST_30_DAYS',     // Period to check for recent conversions
  LOOKBACK_DAYS: 30,              // Days to look back for conversion activity

  // ═══════════════════════════════════════════════════════════════════════════
  // HEALTH CHECK THRESHOLDS
  // ═══════════════════════════════════════════════════════════════════════════
  ZERO_CONV_WARNING: true,        // Warn on actions with 0 conversions
  MIN_CONV_FOR_HEALTHY: 1,        // Minimum conversions to consider "active"
  FLAG_SECONDARY_NO_DATA: true,   // Flag secondary actions with no data
  FLAG_DUPLICATE_NAMES: true,     // Flag actions with duplicate names
  MAX_CLICK_WINDOW_DAYS: 90,      // Flag conversion windows over this
  MAX_VIEW_WINDOW_DAYS: 30,       // Flag view-through windows over this

  // ═══════════════════════════════════════════════════════════════════════════
  // CONVERSION TYPE RULES
  // ═══════════════════════════════════════════════════════════════════════════
  // Expected counting type by category
  LEAD_CATEGORIES: ['SUBMIT_LEAD_FORM', 'SIGNUP', 'CONTACT', 'REQUEST_QUOTE', 'GET_DIRECTIONS', 'OUTBOUND_CLICK', 'PAGE_VIEW'],
  PURCHASE_CATEGORIES: ['PURCHASE', 'ADD_TO_CART', 'BEGIN_CHECKOUT'],

  // ═══════════════════════════════════════════════════════════════════════════
  // 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', 'Conversion Action Health Check started: ' + startTime.toISOString());

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

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

/******************************************************************************
 * DATA COLLECTION & ANALYSIS
 ******************************************************************************/
function auditConversionActions(startTime) {
  var results = {
    actions: [],
    issues: [],
    healthyActions: [],
    bestPractices: [],
    summary: {
      totalActions: 0,
      primaryActions: 0,
      secondaryActions: 0,
      activeActions: 0,
      inactiveActions: 0,
      totalConversions: 0,
      totalConvValue: 0,
      issuesFound: 0,
      criticalIssues: 0,
      warningIssues: 0,
      healthScore: 0
    }
  };

  // Collect all conversion actions
  log('INFO', 'Fetching conversion actions...');
  collectConversionActions(results, startTime);

  // Run health checks
  log('INFO', 'Running health checks...');
  runHealthChecks(results);

  // Generate best practices checklist
  generateBestPractices(results);

  // Calculate health score
  calculateHealthScore(results);

  return results;
}

function collectConversionActions(results, startTime) {
  try {
    // Use GAQL to get conversion action data
    var query = 'SELECT ' +
      'conversion_action.id, ' +
      'conversion_action.name, ' +
      'conversion_action.status, ' +
      'conversion_action.type, ' +
      'conversion_action.category, ' +
      'conversion_action.counting_type, ' +
      'conversion_action.include_in_conversions_metric, ' +
      'conversion_action.value_settings.default_value, ' +
      'conversion_action.value_settings.always_use_default_value, ' +
      'conversion_action.attribution_model_settings.attribution_model, ' +
      'conversion_action.click_through_lookback_window_days, ' +
      'conversion_action.view_through_lookback_window_days, ' +
      'metrics.all_conversions, ' +
      'metrics.all_conversions_value, ' +
      'metrics.conversions, ' +
      'metrics.conversions_value ' +
      'FROM conversion_action ' +
      'WHERE conversion_action.status != "REMOVED" ' +
      'AND segments.date DURING ' + CONFIG.DATE_RANGE;

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

    while (rows.hasNext()) {
      var row = rows.next();

      var actionName = row['conversion_action.name'] || '';
      var actionId = row['conversion_action.id'] || '';
      var status = row['conversion_action.status'] || '';
      var type = row['conversion_action.type'] || '';
      var category = row['conversion_action.category'] || '';
      var countingType = row['conversion_action.counting_type'] || '';
      var includeInConversions = row['conversion_action.include_in_conversions_metric'];
      var defaultValue = parseFloat(row['conversion_action.value_settings.default_value'] || 0);
      var alwaysUseDefault = row['conversion_action.value_settings.always_use_default_value'];
      var attributionModel = row['conversion_action.attribution_model_settings.attribution_model'] || '';
      var clickWindow = parseInt(row['conversion_action.click_through_lookback_window_days'] || 30, 10);
      var viewWindow = parseInt(row['conversion_action.view_through_lookback_window_days'] || 1, 10);

      var allConversions = parseFloat(row['metrics.all_conversions'] || 0);
      var allConvValue = parseFloat(row['metrics.all_conversions_value'] || 0);
      var conversions = parseFloat(row['metrics.conversions'] || 0);
      var convValue = parseFloat(row['metrics.conversions_value'] || 0);

      // Track duplicate names
      if (!seenNames[actionName]) {
        seenNames[actionName] = [];
      }
      seenNames[actionName].push(actionId);

      var isPrimary = includeInConversions === true || includeInConversions === 'true';
      var isActive = allConversions >= CONFIG.MIN_CONV_FOR_HEALTHY;

      var actionData = {
        id: actionId,
        name: actionName,
        status: status,
        type: type,
        category: category,
        countingType: countingType,
        isPrimary: isPrimary,
        primaryLabel: isPrimary ? 'Primary' : 'Secondary',
        defaultValue: defaultValue,
        alwaysUseDefault: alwaysUseDefault,
        attributionModel: attributionModel,
        clickWindow: clickWindow,
        viewWindow: viewWindow,
        allConversions: allConversions,
        allConvValue: allConvValue,
        conversions: conversions,
        convValue: convValue,
        isActive: isActive,
        activityStatus: isActive ? 'ACTIVE' : (allConversions > 0 ? 'LOW' : 'INACTIVE'),
        issues: [],
        duplicateIds: null
      };

      results.actions.push(actionData);
      results.summary.totalActions++;

      if (isPrimary) {
        results.summary.primaryActions++;
      } else {
        results.summary.secondaryActions++;
      }

      if (isActive) {
        results.summary.activeActions++;
        results.healthyActions.push(actionData);
      } else {
        results.summary.inactiveActions++;
      }

      results.summary.totalConversions += allConversions;
      results.summary.totalConvValue += allConvValue;

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

    // Mark duplicates
    for (var name in seenNames) {
      if (seenNames[name].length > 1) {
        for (var i = 0; i < results.actions.length; i++) {
          if (results.actions[i].name === name) {
            results.actions[i].duplicateIds = seenNames[name];
          }
        }
      }
    }

    log('INFO', 'Collected ' + count + ' conversion actions');

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

function collectConversionActionsFallback(results, startTime) {
  // Use the report API as fallback
  try {
    var report = AdsApp.report(
      'SELECT ' +
      'ConversionTypeName, ' +
      'ConversionCategoryName, ' +
      'ConversionTrackerId, ' +
      'Conversions, ' +
      'ConversionValue ' +
      'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
      'WHERE Conversions > 0 ' +
      'DURING ' + CONFIG.DATE_RANGE
    );

    var actionMap = {};
    var rows = report.rows();

    while (rows.hasNext()) {
      var row = rows.next();
      var name = row['ConversionTypeName'];
      var id = row['ConversionTrackerId'];

      if (!actionMap[id]) {
        actionMap[id] = {
          id: id,
          name: name,
          category: row['ConversionCategoryName'],
          allConversions: 0,
          allConvValue: 0
        };
      }

      actionMap[id].allConversions += parseFloat(row['Conversions'] || 0);
      actionMap[id].allConvValue += parseFloat(row['ConversionValue'] || 0);
    }

    for (var id in actionMap) {
      var action = actionMap[id];
      results.actions.push({
        id: action.id,
        name: action.name,
        status: 'ENABLED',
        type: 'UNKNOWN',
        category: action.category,
        countingType: 'UNKNOWN',
        isPrimary: true,
        primaryLabel: 'Unknown',
        defaultValue: 0,
        alwaysUseDefault: false,
        attributionModel: 'UNKNOWN',
        clickWindow: 30,
        viewWindow: 1,
        allConversions: action.allConversions,
        allConvValue: action.allConvValue,
        conversions: action.allConversions,
        convValue: action.allConvValue,
        isActive: action.allConversions >= CONFIG.MIN_CONV_FOR_HEALTHY,
        activityStatus: action.allConversions >= CONFIG.MIN_CONV_FOR_HEALTHY ? 'ACTIVE' : 'LOW',
        issues: [],
        duplicateIds: null
      });

      results.summary.totalActions++;
      results.summary.totalConversions += action.allConversions;
      results.summary.totalConvValue += action.allConvValue;
    }

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

/******************************************************************************
 * HEALTH CHECKS
 ******************************************************************************/
function runHealthChecks(results) {
  var seenNames = {};

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

    // Check 1: Zero conversions in period
    if (CONFIG.ZERO_CONV_WARNING && action.allConversions === 0) {
      var severity = action.isPrimary ? 'CRITICAL' : 'WARNING';
      addIssue(results, action, severity, 'NO_CONVERSIONS',
        'Zero conversions in ' + CONFIG.DATE_RANGE,
        'Check if tracking tag is installed correctly. Verify the tag fires on the conversion page.');
    }

    // Check 2: Primary action with low volume
    if (action.isPrimary && action.allConversions > 0 && action.allConversions < 5) {
      addIssue(results, action, 'WARNING', 'LOW_VOLUME',
        'Only ' + action.allConversions.toFixed(0) + ' conversions in period',
        'Low conversion volume may affect Smart Bidding performance. Consider if this action is tracking correctly.');
    }

    // Check 3: Counting type mismatch
    checkCountingTypeMismatch(results, action);

    // Check 4: Duplicate names
    if (CONFIG.FLAG_DUPLICATE_NAMES && action.duplicateIds && action.duplicateIds.length > 1) {
      addIssue(results, action, 'WARNING', 'DUPLICATE_NAME',
        'Multiple actions share this name (' + action.duplicateIds.length + ' found)',
        'Duplicate names cause confusion in reporting. Consider renaming for clarity.');
    }

    // Check 5: Long conversion windows
    if (action.clickWindow > CONFIG.MAX_CLICK_WINDOW_DAYS) {
      addIssue(results, action, 'INFO', 'LONG_CLICK_WINDOW',
        'Click window is ' + action.clickWindow + ' days (recommended: ' + CONFIG.MAX_CLICK_WINDOW_DAYS + ')',
        'Long windows can attribute conversions to clicks from months ago. Consider shortening based on your sales cycle.');
    }

    if (action.viewWindow > CONFIG.MAX_VIEW_WINDOW_DAYS) {
      addIssue(results, action, 'INFO', 'LONG_VIEW_WINDOW',
        'View-through window is ' + action.viewWindow + ' days',
        'Long view-through windows may over-attribute. Consider if view-through is valuable for your business.');
    }

    // Check 6: Secondary action with significant volume (should it be primary?)
    if (!action.isPrimary && action.allConversions >= 10) {
      addIssue(results, action, 'INFO', 'SECONDARY_WITH_VOLUME',
        'Secondary action has ' + action.allConversions.toFixed(0) + ' conversions',
        'This action has significant volume but is not included in bidding. Consider if it should be primary.');
    }

    // Check 7: Primary purchase action with ONE_PER_CLICK counting
    if (action.isPrimary && isPurchaseCategory(action.category) && action.countingType === 'ONE_PER_CLICK') {
      addIssue(results, action, 'WARNING', 'PURCHASE_ONE_PER_CLICK',
        'Purchase action using ONE_PER_CLICK counting',
        'Purchase conversions should typically use EVERY counting to track all transactions. Change to EVERY unless intentional.');
    }

    // Check 8: Lead action with EVERY counting
    if (action.isPrimary && isLeadCategory(action.category) && action.countingType === 'EVERY') {
      addIssue(results, action, 'INFO', 'LEAD_EVERY_COUNTING',
        'Lead action using EVERY counting',
        'Lead forms typically should use ONE_PER_CLICK to avoid duplicate leads. Consider changing unless multiple leads per session is expected.');
    }

    // Check 9: No default value set for primary action
    if (action.isPrimary && action.defaultValue === 0 && !isPurchaseCategory(action.category)) {
      addIssue(results, action, 'INFO', 'NO_DEFAULT_VALUE',
        'No default value set for primary action',
        'Setting a conversion value helps Smart Bidding understand action worth. Consider setting a value based on lead value.');
    }

    // Check 10: Using last click attribution
    if (action.isPrimary && action.attributionModel === 'LAST_CLICK') {
      addIssue(results, action, 'INFO', 'LAST_CLICK_ATTRIBUTION',
        'Using Last Click attribution model',
        'Consider Data-Driven attribution for better cross-channel credit. Google recommends DDA for most accounts.');
    }

    // Track for duplicate check
    if (!seenNames[action.name]) {
      seenNames[action.name] = 0;
    }
    seenNames[action.name]++;
  }

  // Check 11: No primary actions at all
  if (results.summary.primaryActions === 0) {
    results.issues.push({
      actionId: 'ACCOUNT',
      actionName: 'Account Level',
      severity: 'CRITICAL',
      issueType: 'NO_PRIMARY_ACTIONS',
      description: 'No primary conversion actions found',
      recommendation: 'You need at least one primary conversion action for Smart Bidding to work. Set your most important action to Primary.',
      isPrimary: true,
      conversions: 0
    });
    results.summary.criticalIssues++;
  }

  // Check 12: Too many primary actions
  if (results.summary.primaryActions > 5) {
    results.issues.push({
      actionId: 'ACCOUNT',
      actionName: 'Account Level',
      severity: 'WARNING',
      issueType: 'TOO_MANY_PRIMARY',
      description: results.summary.primaryActions + ' primary actions found',
      recommendation: 'Having too many primary actions can dilute Smart Bidding signals. Consider which actions truly represent value.',
      isPrimary: true,
      conversions: results.summary.totalConversions
    });
    results.summary.warningIssues++;
  }

  results.summary.issuesFound = results.issues.length;
}

function checkCountingTypeMismatch(results, action) {
  // Check if counting type matches category expectations
  if (isPurchaseCategory(action.category)) {
    // Purchase categories should typically be EVERY
    if (action.countingType === 'ONE_PER_CLICK' && action.isPrimary) {
      // Already handled above
    }
  } else if (isLeadCategory(action.category)) {
    // Lead categories should typically be ONE_PER_CLICK
    if (action.countingType === 'EVERY' && action.isPrimary) {
      // Already handled above
    }
  }
}

function addIssue(results, action, severity, issueType, description, recommendation) {
  var issue = {
    actionId: action.id,
    actionName: action.name,
    severity: severity,
    issueType: issueType,
    description: description,
    recommendation: recommendation,
    isPrimary: action.isPrimary,
    conversions: action.allConversions
  };

  results.issues.push(issue);
  action.issues.push(issueType);

  if (severity === 'CRITICAL') {
    results.summary.criticalIssues++;
  } else if (severity === 'WARNING') {
    results.summary.warningIssues++;
  }
}

function isPurchaseCategory(category) {
  return CONFIG.PURCHASE_CATEGORIES.indexOf(category) !== -1;
}

function isLeadCategory(category) {
  return CONFIG.LEAD_CATEGORIES.indexOf(category) !== -1;
}

/******************************************************************************
 * BEST PRACTICES & HEALTH SCORE
 ******************************************************************************/
function generateBestPractices(results) {
  var practices = [
    {
      category: 'Primary Actions',
      practice: 'Have 1-3 primary conversion actions',
      status: results.summary.primaryActions >= 1 && results.summary.primaryActions <= 3 ? 'PASS' : 'FAIL',
      current: results.summary.primaryActions + ' primary actions',
      recommendation: results.summary.primaryActions === 0 ?
        'Add at least one primary action' :
        (results.summary.primaryActions > 3 ? 'Consider reducing primary actions' : 'Good setup')
    },
    {
      category: 'Active Tracking',
      practice: 'All primary actions should have recent conversions',
      status: results.summary.primaryActions > 0 &&
              results.actions.filter(function(a) { return a.isPrimary && a.allConversions > 0; }).length === results.summary.primaryActions ? 'PASS' : 'FAIL',
      current: results.actions.filter(function(a) { return a.isPrimary && a.allConversions > 0; }).length + '/' + results.summary.primaryActions + ' primary actions have data',
      recommendation: 'Ensure tracking tags are firing correctly'
    },
    {
      category: 'Attribution Model',
      practice: 'Use Data-Driven attribution when available',
      status: results.actions.some(function(a) { return a.attributionModel === 'CROSS_CHANNEL_DATA_DRIVEN'; }) ? 'PASS' : 'INFO',
      current: results.actions.filter(function(a) { return a.attributionModel === 'CROSS_CHANNEL_DATA_DRIVEN'; }).length + ' actions use DDA',
      recommendation: 'Switch primary actions to Data-Driven attribution if available'
    },
    {
      category: 'Conversion Values',
      practice: 'Set values for all conversion actions',
      status: results.actions.filter(function(a) { return a.isPrimary && (a.defaultValue > 0 || isPurchaseCategory(a.category)); }).length === results.summary.primaryActions ? 'PASS' : 'INFO',
      current: results.actions.filter(function(a) { return a.defaultValue > 0; }).length + ' actions have values set',
      recommendation: 'Assign monetary values to help Smart Bidding prioritize'
    },
    {
      category: 'Counting Type',
      practice: 'Use correct counting type for action category',
      status: results.issues.filter(function(i) { return i.issueType === 'PURCHASE_ONE_PER_CLICK' || i.issueType === 'LEAD_EVERY_COUNTING'; }).length === 0 ? 'PASS' : 'WARNING',
      current: 'See issues for details',
      recommendation: 'ONE for leads, EVERY for purchases'
    },
    {
      category: 'Naming Convention',
      practice: 'Use unique, descriptive names for actions',
      status: results.issues.filter(function(i) { return i.issueType === 'DUPLICATE_NAME'; }).length === 0 ? 'PASS' : 'WARNING',
      current: results.issues.filter(function(i) { return i.issueType === 'DUPLICATE_NAME'; }).length + ' duplicate names found',
      recommendation: 'Rename duplicate actions for clarity'
    }
  ];

  results.bestPractices = practices;
}

function calculateHealthScore(results) {
  var score = 100;

  // Deduct for critical issues (-20 each)
  score -= results.summary.criticalIssues * 20;

  // Deduct for warning issues (-10 each)
  score -= results.summary.warningIssues * 10;

  // Deduct if no primary actions (-30)
  if (results.summary.primaryActions === 0) {
    score -= 30;
  }

  // Deduct if no active actions (-20)
  if (results.summary.activeActions === 0) {
    score -= 20;
  }

  // Bonus for using DDA (+5)
  if (results.actions.some(function(a) { return a.attributionModel === 'CROSS_CHANNEL_DATA_DRIVEN' && a.isPrimary; })) {
    score += 5;
  }

  // Ensure score is between 0-100
  results.summary.healthScore = Math.max(0, Math.min(100, score));
}

/******************************************************************************
 * 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 Conversion Health Check - ' +
                               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 Conversion Actions
  if (results.actions.length > 0) {
    var actionData = results.actions.map(function(a) {
      return {
        'Action Name': a.name,
        'Action ID': a.id,
        'Status': a.status,
        'Type': a.type,
        'Category': a.category,
        'Primary/Secondary': a.primaryLabel,
        'Counting Type': a.countingType,
        'Attribution Model': a.attributionModel,
        'Click Window': a.clickWindow + ' days',
        'View Window': a.viewWindow + ' days',
        'Default Value': a.defaultValue > 0 ? '$' + a.defaultValue.toFixed(2) : '-',
        'Conversions (30d)': a.allConversions.toFixed(2),
        'Conv Value (30d)': '$' + a.allConvValue.toFixed(2),
        'Activity': a.activityStatus,
        'Issues': a.issues.length > 0 ? a.issues.join(', ') : 'None'
      };
    });

    // Sort: Primary first, then by conversions
    actionData.sort(function(a, b) {
      if (a['Primary/Secondary'] !== b['Primary/Secondary']) {
        return a['Primary/Secondary'] === 'Primary' ? -1 : 1;
      }
      return parseFloat(b['Conversions (30d)']) - parseFloat(a['Conversions (30d)']);
    });

    writeSheet(ss, '2. All Conversion Actions', actionData,
      ['Action Name', 'Primary/Secondary', 'Category', 'Counting Type', 'Attribution Model',
       'Conversions (30d)', 'Conv Value (30d)', 'Activity', 'Issues', 'Click Window']);
  }

  // 3. Issues Found
  if (results.issues.length > 0) {
    var issueData = results.issues.map(function(i) {
      return {
        'Action': i.actionName,
        'Severity': i.severity,
        'Issue': i.issueType,
        'Description': i.description,
        'How to Fix': i.recommendation,
        'Primary?': i.isPrimary ? 'Yes' : 'No',
        'Conversions': i.conversions.toFixed(2)
      };
    });

    // Sort by severity
    var severityOrder = { 'CRITICAL': 0, 'WARNING': 1, 'INFO': 2 };
    issueData.sort(function(a, b) {
      return (severityOrder[a.Severity] || 3) - (severityOrder[b.Severity] || 3);
    });

    writeSheet(ss, '3. Issues Found', issueData,
      ['Action', 'Severity', 'Issue', 'Description', 'How to Fix', 'Primary?']);
  }

  // 4. Best Practices Checklist
  if (results.bestPractices.length > 0) {
    var practiceData = results.bestPractices.map(function(p) {
      return {
        'Category': p.category,
        'Best Practice': p.practice,
        'Status': p.status,
        'Current State': p.current,
        'Recommendation': p.recommendation
      };
    });
    writeSheet(ss, '4. Best Practices', practiceData,
      ['Category', 'Best Practice', 'Status', 'Current State', 'Recommendation']);
  }

  // 5. Healthy Actions (actions with no issues)
  var healthyActions = results.actions.filter(function(a) { return a.issues.length === 0 && a.isActive; });
  if (healthyActions.length > 0) {
    var healthyData = healthyActions.map(function(a) {
      return {
        'Action Name': a.name,
        'Type': a.type,
        'Category': a.category,
        'Primary/Secondary': a.primaryLabel,
        'Conversions': a.allConversions.toFixed(2),
        'Value': '$' + a.allConvValue.toFixed(2),
        'Status': '✓ Healthy'
      };
    });
    writeSheet(ss, '5. Healthy Actions', healthyData,
      ['Action Name', 'Primary/Secondary', 'Category', 'Conversions', 'Value', 'Status']);
  }
}

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

  var healthEmoji = results.summary.healthScore >= 80 ? '🟢' :
                    results.summary.healthScore >= 60 ? '🟡' : '🔴';
  var healthLabel = results.summary.healthScore >= 80 ? 'HEALTHY' :
                    results.summary.healthScore >= 60 ? 'NEEDS ATTENTION' : 'CRITICAL';

  var data = [
    ['CONVERSION ACTION HEALTH CHECK', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Account: ' + AdsApp.currentAccount().getName(), ''],
    ['Date Range: ' + CONFIG.DATE_RANGE, ''],
    ['Export Date: ' + new Date().toISOString(), ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['HEALTH SCORE', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Overall Score', healthEmoji + ' ' + results.summary.healthScore + '/100 - ' + healthLabel],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['CONVERSION ACTION INVENTORY', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Total Conversion Actions', results.summary.totalActions],
    ['Primary Actions (in bidding)', results.summary.primaryActions],
    ['Secondary Actions (observe only)', results.summary.secondaryActions],
    ['', ''],
    ['Active Actions (with conversions)', results.summary.activeActions],
    ['Inactive Actions (zero conversions)', results.summary.inactiveActions],
    ['', ''],
    ['Total Conversions (30 days)', results.summary.totalConversions.toFixed(2)],
    ['Total Conversion Value', '$' + results.summary.totalConvValue.toFixed(2)],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['ISSUES SUMMARY', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Total Issues Found', results.summary.issuesFound],
    ['Critical Issues', results.summary.criticalIssues],
    ['Warning Issues', results.summary.warningIssues],
    ['Info/Suggestions', results.summary.issuesFound - results.summary.criticalIssues - results.summary.warningIssues],
    ['', '']
  ];

  // Add top issues if any
  if (results.issues.length > 0) {
    data.push(['═══════════════════════════════════════════════════════════════', '']);
    data.push(['TOP ISSUES TO ADDRESS', '']);
    data.push(['═══════════════════════════════════════════════════════════════', '']);

    var topIssues = results.issues
      .filter(function(i) { return i.severity === 'CRITICAL' || i.severity === 'WARNING'; })
      .slice(0, 5);

    if (topIssues.length === 0) {
      data.push(['No critical or warning issues found!', '']);
    } else {
      for (var i = 0; i < topIssues.length; i++) {
        var issue = topIssues[i];
        data.push([
          (i + 1) + '. [' + issue.severity + '] ' + issue.actionName,
          issue.description
        ]);
      }
    }
    data.push(['', '']);
  }

  data = data.concat([
    ['═══════════════════════════════════════════════════════════════', ''],
    ['QUICK REFERENCE', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Primary vs Secondary:', ''],
    ['  - Primary actions are used by Smart Bidding', ''],
    ['  - Secondary actions are tracked but not bid on', ''],
    ['', ''],
    ['Counting Types:', ''],
    ['  - ONE_PER_CLICK: Best for leads (one per session)', ''],
    ['  - EVERY: Best for purchases (all transactions)', ''],
    ['', ''],
    ['Attribution Models:', ''],
    ['  - Data-Driven: Recommended (machine learning)', ''],
    ['  - Last Click: Legacy (all credit to last click)', ''],
    ['', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['AI ANALYSIS PROMPTS', ''],
    ['═══════════════════════════════════════════════════════════════', ''],
    ['Copy conversion data into Claude with these prompts:', ''],
    ['', ''],
    ['Prompt 1', '"Are my conversion actions set up correctly?"'],
    ['Prompt 2', '"Why might I be missing conversions?"'],
    ['Prompt 3', '"What conversion tracking issues should I fix first?"'],
    ['Prompt 4', '"Should any of my secondary actions be primary?"'],
    ['Prompt 5', '"Create a conversion tracking audit action plan"']
  ]);

  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 health score row
  highlightHealthScore(sheet, results.summary.healthScore);
}

function highlightHealthScore(sheet, score) {
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === 'Overall Score') {
      var color = score >= 80 ? '#d4edda' : score >= 60 ? '#fff3cd' : '#f8d7da';
      sheet.getRange(i + 1, 2).setBackground(color).setFontWeight('bold');
    }
    if (data[i][0] === 'Critical Issues' && data[i][1] > 0) {
      sheet.getRange(i + 1, 2).setBackground('#f8d7da');
    }
    if (data[i][0] === 'Warning Issues' && data[i][1] > 0) {
      sheet.getRange(i + 1, 2).setBackground('#fff3cd');
    }
  }
}

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 severity/status columns
  applyStatusColors(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 applyStatusColors(sheet, columns, numRows) {
  var colors = {
    'CRITICAL': '#f8d7da',
    'WARNING': '#fff3cd',
    'INFO': '#cce5ff',
    'PASS': '#d4edda',
    'FAIL': '#f8d7da',
    'ACTIVE': '#d4edda',
    'LOW': '#fff3cd',
    'INACTIVE': '#f8d7da',
    'Primary': '#d4edda',
    'Secondary': '#e9ecef'
  };

  ['Severity', 'Status', 'Activity', 'Primary/Secondary'].forEach(function(colName) {
    var colIndex = columns.indexOf(colName) + 1;
    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 healthEmoji = results.summary.healthScore >= 80 ? '🟢' :
                    results.summary.healthScore >= 60 ? '🟡' : '🔴';

  var message = [
    'Conversion Action Health Check Complete',
    '',
    'Account: ' + AdsApp.currentAccount().getName(),
    'Duration: ' + duration + 's',
    '',
    'Health Score: ' + healthEmoji + ' ' + results.summary.healthScore + '/100',
    '',
    'Conversion Actions:',
    '- Total: ' + results.summary.totalActions,
    '- Primary: ' + results.summary.primaryActions,
    '- Active: ' + results.summary.activeActions,
    '- Inactive: ' + results.summary.inactiveActions,
    '',
    'Issues Found:',
    '- Critical: ' + results.summary.criticalIssues,
    '- Warnings: ' + results.summary.warningIssues,
    '',
    '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] Conversion Health ' + healthEmoji + ' Score: ' + results.summary.healthScore + '/100',
        body: message
      });
      log('INFO', 'Email sent');
    } catch (e) {
      log('ERROR', 'Failed to send email: ' + e.message);
    }
  }

  if (CONFIG.SLACK_WEBHOOK_URL) {
    try {
      UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify({
          text: ':chart_with_upwards_trend: *PPC.io Conversion Health Check*\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: Audit 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', 'CONVERSION HEALTH CHECK COMPLETE');
  log('INFO', 'Duration: ' + duration + ' seconds');
  log('INFO', 'Health Score: ' + results.summary.healthScore + '/100');
  log('INFO', 'Conversion Actions: ' + results.summary.totalActions);
  log('INFO', 'Primary: ' + results.summary.primaryActions);
  log('INFO', 'Issues Found: ' + results.summary.issuesFound);
  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] Conversion Health Check 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');
}