CRO Analytics

CRO Measurement Pyramid:

                    ┌───────────────┐
                    │   REVENUE     │  ← Ultimate metric
                    │   METRICS     │
                    └───────┬───────┘
                            │
                ┌───────────▼───────────┐
                │   CONVERSION METRICS  │  ← Primary KPIs
                │   (Rate, Volume, Value)│
                └───────────┬───────────┘
                            │
        ┌───────────────────▼───────────────────┐
        │         FUNNEL METRICS                │  ← Drop-off points
        │   (Step completion, Time, Bounce)     │
        └───────────────────┬───────────────────┘
                            │
┌───────────────────────────▼───────────────────────────┐
│              MICRO-CONVERSION METRICS                  │  ← Leading indicators
│   (Scroll, Click, Engagement, Form interaction)        │
└───────────────────────────────────────────────────────┘

interface CROPrimaryMetrics {
  // Conversion metrics
  conversionRate: {
    formula: '(Conversions / Visitors) × 100';
    benchmark: '2-5% for e-commerce, 5-15% for lead gen';
    segmentBy: ['traffic_source', 'device', 'page', 'campaign'];
  };

  revenuePerVisitor: {
    formula: 'Total Revenue / Total Visitors';
    importance: 'Better than CR alone - accounts for order value';
    benchmark: 'Industry-specific';
  };

  averageOrderValue: {
    formula: 'Total Revenue / Number of Orders';
    optimization: 'Upsells, cross-sells, bundles';
  };

  // Engagement metrics
  bounceRate: {
    formula: 'Single-page sessions / Total sessions';
    goodRange: '26-40%';
    contextDependent: 'Blog posts naturally higher';
  };

  exitRate: {
    formula: 'Exits from page / Total pageviews';
    useCase: 'Identify problem pages in funnel';
  };

  pageViewsPerSession: {
    formula: 'Total pageviews / Total sessions';
    benchmark: '2-4 for most sites';
  };

  // Time metrics
  timeOnPage: {
    formula: 'Sum of time on page / Pageviews';
    caveat: 'Last page in session not measured';
  };

  sessionDuration: {
    formula: 'Session end - Session start';
    caveat: 'Engagement, not necessarily good';
  };
}

interface FunnelMetrics {
  stepConversionRate: {
    formula: 'Users completing step / Users entering step';
    useCase: 'Identify biggest drop-offs';
  };

  funnelCompletionRate: {
    formula: 'Users completing funnel / Users starting funnel';
    comparison: 'Track over time and by segment';
  };

  averageTimeToConvert: {
    formula: 'Sum of (conversion time - first touch time) / Conversions';
    useCase: 'Understand decision timeline';
  };

  dropOffPoints: {
    analysis: 'Steps with highest abandonment';
    action: 'Prioritize optimization efforts';
  };
}

// Comprehensive GA4 e-commerce tracking
class GA4EcommerceTracker {
  constructor() {
    this.initializeTracking();
  }

  private initializeTracking() {
    // Product view
    this.trackProductView = this.trackProductView.bind(this);
    // Add to cart
    this.trackAddToCart = this.trackAddToCart.bind(this);
    // Remove from cart
    this.trackRemoveFromCart = this.trackRemoveFromCart.bind(this);
    // View cart
    this.trackViewCart = this.trackViewCart.bind(this);
    // Begin checkout
    this.trackBeginCheckout = this.trackBeginCheckout.bind(this);
    // Add shipping info
    this.trackAddShippingInfo = this.trackAddShippingInfo.bind(this);
    // Add payment info
    this.trackAddPaymentInfo = this.trackAddPaymentInfo.bind(this);
    // Purchase
    this.trackPurchase = this.trackPurchase.bind(this);
  }

  trackProductView(product: Product) {
    gtag('event', 'view_item', {
      currency: 'USD',
      value: product.price,
      items: [{
        item_id: product.id,
        item_name: product.name,
        item_category: product.category,
        item_brand: product.brand,
        price: product.price,
        quantity: 1
      }]
    });
  }

  trackAddToCart(product: Product, quantity: number) {
    gtag('event', 'add_to_cart', {
      currency: 'USD',
      value: product.price * quantity,
      items: [{
        item_id: product.id,
        item_name: product.name,
        item_category: product.category,
        item_brand: product.brand,
        price: product.price,
        quantity: quantity
      }]
    });
  }

  trackRemoveFromCart(product: Product, quantity: number) {
    gtag('event', 'remove_from_cart', {
      currency: 'USD',
      value: product.price * quantity,
      items: [{
        item_id: product.id,
        item_name: product.name,
        item_category: product.category,
        price: product.price,
        quantity: quantity
      }]
    });
  }

  trackViewCart(cart: CartItem[]) {
    const value = cart.reduce((sum, item) => sum + (item.price * item.quantity), 0);

    gtag('event', 'view_cart', {
      currency: 'USD',
      value: value,
      items: cart.map(item => ({
        item_id: item.id,
        item_name: item.name,
        item_category: item.category,
        price: item.price,
        quantity: item.quantity
      }))
    });
  }

  trackBeginCheckout(cart: CartItem[], coupon?: string) {
    const value = cart.reduce((sum, item) => sum + (item.price * item.quantity), 0);

    gtag('event', 'begin_checkout', {
      currency: 'USD',
      value: value,
      coupon: coupon,
      items: cart.map(item => ({
        item_id: item.id,
        item_name: item.name,
        item_category: item.category,
        price: item.price,
        quantity: item.quantity
      }))
    });
  }

  trackAddShippingInfo(cart: CartItem[], shippingTier: string) {
    const value = cart.reduce((sum, item) => sum + (item.price * item.quantity), 0);

    gtag('event', 'add_shipping_info', {
      currency: 'USD',
      value: value,
      shipping_tier: shippingTier,
      items: cart.map(item => ({
        item_id: item.id,
        item_name: item.name,
        price: item.price,
        quantity: item.quantity
      }))
    });
  }

  trackAddPaymentInfo(cart: CartItem[], paymentType: string) {
    const value = cart.reduce((sum, item) => sum + (item.price * item.quantity), 0);

    gtag('event', 'add_payment_info', {
      currency: 'USD',
      value: value,
      payment_type: paymentType,
      items: cart.map(item => ({
        item_id: item.id,
        item_name: item.name,
        price: item.price,
        quantity: item.quantity
      }))
    });
  }

  trackPurchase(order: Order) {
    gtag('event', 'purchase', {
      transaction_id: order.id,
      value: order.total,
      tax: order.tax,
      shipping: order.shipping,
      currency: 'USD',
      coupon: order.coupon,
      items: order.items.map(item => ({
        item_id: item.id,
        item_name: item.name,
        item_category: item.category,
        item_brand: item.brand,
        price: item.price,
        quantity: item.quantity
      }))
    });
  }
}

// Initialize
const ecommerceTracker = new GA4EcommerceTracker();

// Custom events for CRO analysis
const croEventConfig = {
  // Engagement events
  engagement: {
    scroll_depth: {
      parameters: ['percent_scrolled', 'page_path'],
      trigger: 'Scroll milestones (25%, 50%, 75%, 100%)'
    },
    time_on_page: {
      parameters: ['engaged_time_sec', 'page_path'],
      trigger: 'Time thresholds (30s, 60s, 120s, 300s)'
    },
    content_interaction: {
      parameters: ['interaction_type', 'element_id', 'element_text'],
      trigger: 'Click on interactive content'
    }
  },

  // Form events
  forms: {
    form_start: {
      parameters: ['form_name', 'form_location'],
      trigger: 'First field focus'
    },
    form_field_complete: {
      parameters: ['form_name', 'field_name', 'field_position'],
      trigger: 'Field completed (blur with value)'
    },
    form_field_error: {
      parameters: ['form_name', 'field_name', 'error_type'],
      trigger: 'Validation error shown'
    },
    form_abandon: {
      parameters: ['form_name', 'last_field', 'fields_completed', 'time_spent'],
      trigger: 'Page exit with incomplete form'
    },
    form_submit: {
      parameters: ['form_name', 'fields_count', 'time_to_complete'],
      trigger: 'Form submission'
    }
  },

  // CTA events
  cta: {
    cta_view: {
      parameters: ['cta_id', 'cta_text', 'cta_location', 'time_to_view'],
      trigger: 'CTA enters viewport'
    },
    cta_click: {
      parameters: ['cta_id', 'cta_text', 'cta_location', 'page_section'],
      trigger: 'CTA clicked'
    }
  },

  // Experiment events
  experiments: {
    experiment_exposure: {
      parameters: ['experiment_id', 'variant_id', 'page_path'],
      trigger: 'User sees experiment variation'
    },
    experiment_conversion: {
      parameters: ['experiment_id', 'variant_id', 'conversion_type', 'value'],
      trigger: 'User converts in experiment'
    }
  }
};

// Event tracking implementation
class CROEventTracker {
  track(category: string, event: string, params: Record<string, any>) {
    const eventName = `cro_${category}_${event}`;

    // GA4
    gtag('event', eventName, {
      event_category: category,
      ...params,
      timestamp: Date.now(),
      session_id: this.getSessionId()
    });

    // Also send to backend for detailed analysis
    this.sendToBackend(eventName, params);
  }

  private getSessionId(): string {
    let sessionId = sessionStorage.getItem('cro_session_id');
    if (!sessionId) {
      sessionId = `sess_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
      sessionStorage.setItem('cro_session_id', sessionId);
    }
    return sessionId;
  }

  private async sendToBackend(event: string, params: Record<string, any>) {
    await fetch('/api/analytics/cro', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        event,
        params,
        url: window.location.href,
        referrer: document.referrer,
        userAgent: navigator.userAgent,
        timestamp: new Date().toISOString()
      }),
      keepalive: true
    });
  }
}

const croTracker = new CROEventTracker();

-- Funnel events table
CREATE TABLE funnel_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id TEXT NOT NULL,
  user_id TEXT,
  funnel_name TEXT NOT NULL,
  step_name TEXT NOT NULL,
  step_order INTEGER NOT NULL,
  page_path TEXT,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),

  -- Indexes
  INDEX idx_funnel_session (session_id),
  INDEX idx_funnel_name (funnel_name),
  INDEX idx_funnel_created (created_at)
);

-- Funnel analysis view
CREATE OR REPLACE VIEW funnel_analysis AS
WITH funnel_users AS (
  SELECT
    funnel_name,
    session_id,
    MAX(step_order) as max_step,
    MIN(created_at) as started_at,
    MAX(created_at) as ended_at,
    EXTRACT(EPOCH FROM (MAX(created_at) - MIN(created_at))) as duration_seconds
  FROM funnel_events
  GROUP BY funnel_name, session_id
),
step_counts AS (
  SELECT
    funnel_name,
    step_order,
    step_name,
    COUNT(DISTINCT session_id) as users
  FROM funnel_events
  GROUP BY funnel_name, step_order, step_name
)
SELECT
  sc.funnel_name,
  sc.step_order,
  sc.step_name,
  sc.users,
  FIRST_VALUE(sc.users) OVER (
    PARTITION BY sc.funnel_name ORDER BY sc.step_order
  ) as funnel_start_users,
  LAG(sc.users) OVER (
    PARTITION BY sc.funnel_name ORDER BY sc.step_order
  ) as previous_step_users,
  ROUND(
    sc.users::numeric /
    NULLIF(LAG(sc.users) OVER (PARTITION BY sc.funnel_name ORDER BY sc.step_order), 0) * 100,
    2
  ) as step_conversion_rate,
  ROUND(
    sc.users::numeric /
    NULLIF(FIRST_VALUE(sc.users) OVER (PARTITION BY sc.funnel_name ORDER BY sc.step_order), 0) * 100,
    2
  ) as cumulative_conversion_rate
FROM step_counts sc
ORDER BY sc.funnel_name, sc.step_order;

-- Funnel drop-off analysis
CREATE OR REPLACE FUNCTION get_funnel_dropoffs(
  p_funnel_name TEXT,
  p_start_date TIMESTAMPTZ,
  p_end_date TIMESTAMPTZ
)
RETURNS TABLE (
  step_name TEXT,
  step_order INTEGER,
  entered INTEGER,
  completed INTEGER,
  dropped_off INTEGER,
  drop_off_rate NUMERIC,
  avg_time_to_complete NUMERIC
) AS $$
BEGIN
  RETURN QUERY
  WITH step_progression AS (
    SELECT
      fe.session_id,
      fe.step_name,
      fe.step_order,
      fe.created_at,
      LEAD(fe.created_at) OVER (
        PARTITION BY fe.session_id ORDER BY fe.step_order
      ) as next_step_time
    FROM funnel_events fe
    WHERE fe.funnel_name = p_funnel_name
      AND fe.created_at BETWEEN p_start_date AND p_end_date
  )
  SELECT
    sp.step_name,
    sp.step_order,
    COUNT(DISTINCT sp.session_id)::INTEGER as entered,
    COUNT(DISTINCT CASE WHEN sp.next_step_time IS NOT NULL THEN sp.session_id END)::INTEGER as completed,
    COUNT(DISTINCT CASE WHEN sp.next_step_time IS NULL THEN sp.session_id END)::INTEGER as dropped_off,
    ROUND(
      COUNT(DISTINCT CASE WHEN sp.next_step_time IS NULL THEN sp.session_id END)::NUMERIC /
      NULLIF(COUNT(DISTINCT sp.session_id), 0) * 100,
      2
    ) as drop_off_rate,
    ROUND(
      AVG(EXTRACT(EPOCH FROM (sp.next_step_time - sp.created_at)))::NUMERIC,
      2
    ) as avg_time_to_complete
  FROM step_progression sp
  GROUP BY sp.step_name, sp.step_order
  ORDER BY sp.step_order;
END;
$$ LANGUAGE plpgsql;

class FunnelTracker {
  private funnelName: string;
  private steps: string[];
  private sessionId: string;
  private currentStep: number = 0;

  constructor(funnelName: string, steps: string[]) {
    this.funnelName = funnelName;
    this.steps = steps;
    this.sessionId = this.getOrCreateSessionId();

    // Auto-track page-based funnels
    this.autoTrackPages();
  }

  private getOrCreateSessionId(): string {
    const key = `funnel_${this.funnelName}_session`;
    let sessionId = sessionStorage.getItem(key);

    if (!sessionId) {
      sessionId = `${this.funnelName}_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
      sessionStorage.setItem(key, sessionId);
    }

    return sessionId;
  }

  trackStep(stepName: string, metadata: Record<string, any> = {}) {
    const stepIndex = this.steps.indexOf(stepName);
    if (stepIndex === -1) {
      console.warn(`Step "${stepName}" not found in funnel "${this.funnelName}"`);
      return;
    }

    this.currentStep = stepIndex;

    const eventData = {
      funnel_name: this.funnelName,
      step_name: stepName,
      step_order: stepIndex + 1,
      total_steps: this.steps.length,
      session_id: this.sessionId,
      page_path: window.location.pathname,
      ...metadata
    };

    // Send to GA4
    gtag('event', 'funnel_step', eventData);

    // Send to backend
    this.sendToBackend(eventData);
  }

  trackCompletion(value?: number, metadata: Record<string, any> = {}) {
    const eventData = {
      funnel_name: this.funnelName,
      session_id: this.sessionId,
      steps_completed: this.currentStep + 1,
      total_steps: this.steps.length,
      value: value,
      ...metadata
    };

    gtag('event', 'funnel_complete', eventData);
    this.sendToBackend(eventData);
  }

  trackAbandonment(reason?: string) {
    const eventData = {
      funnel_name: this.funnelName,
      session_id: this.sessionId,
      last_step: this.steps[this.currentStep],
      last_step_order: this.currentStep + 1,
      steps_completed: this.currentStep + 1,
      total_steps: this.steps.length,
      abandon_reason: reason
    };

    gtag('event', 'funnel_abandon', eventData);
    this.sendToBackend(eventData);
  }

  private autoTrackPages() {
    // Map pages to funnel steps
    const pageStepMapping: Record<string, string> = {
      '/cart': 'cart_view',
      '/checkout': 'checkout_start',
      '/checkout/shipping': 'shipping_info',
      '/checkout/payment': 'payment_info',
      '/checkout/review': 'order_review',
      '/order-confirmation': 'purchase_complete'
    };

    const currentPath = window.location.pathname;
    const stepName = pageStepMapping[currentPath];

    if (stepName && this.steps.includes(stepName)) {
      this.trackStep(stepName);
    }
  }

  private async sendToBackend(data: Record<string, any>) {
    await fetch('/api/analytics/funnel', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        ...data,
        timestamp: new Date().toISOString(),
        user_agent: navigator.userAgent,
        viewport: {
          width: window.innerWidth,
          height: window.innerHeight
        }
      }),
      keepalive: true
    });
  }
}

// Initialize checkout funnel
const checkoutFunnel = new FunnelTracker('checkout', [
  'cart_view',
  'checkout_start',
  'shipping_info',
  'payment_info',
  'order_review',
  'purchase_complete'
]);

import { createClient } from '@supabase/supabase-js';

interface DashboardMetrics {
  conversionRate: number;
  conversionRateChange: number;
  totalConversions: number;
  revenue: number;
  avgOrderValue: number;
  funnelData: FunnelStep[];
  topPages: PageMetric[];
  experiments: ExperimentSummary[];
}

async function getCRODashboard(
  dateRange: DateRange,
  comparisonRange: DateRange
): Promise<DashboardMetrics> {
  const supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_KEY!
  );

  // Run queries in parallel
  const [
    currentMetrics,
    previousMetrics,
    funnelData,
    topPages,
    experiments
  ] = await Promise.all([
    getConversionMetrics(supabase, dateRange),
    getConversionMetrics(supabase, comparisonRange),
    getFunnelMetrics(supabase, dateRange),
    getTopPagesByConversion(supabase, dateRange),
    getActiveExperiments(supabase)
  ]);

  return {
    conversionRate: currentMetrics.conversionRate,
    conversionRateChange: calculateChange(
      currentMetrics.conversionRate,
      previousMetrics.conversionRate
    ),
    totalConversions: currentMetrics.conversions,
    revenue: currentMetrics.revenue,
    avgOrderValue: currentMetrics.avgOrderValue,
    funnelData,
    topPages,
    experiments
  };
}

async function getConversionMetrics(
  supabase: any,
  dateRange: DateRange
): Promise<ConversionMetrics> {
  const { data } = await supabase.rpc('get_conversion_metrics', {
    start_date: dateRange.start,
    end_date: dateRange.end
  });

  return data;
}

async function getFunnelMetrics(
  supabase: any,
  dateRange: DateRange
): Promise<FunnelStep[]> {
  const { data } = await supabase
    .from('funnel_analysis')
    .select('*')
    .eq('funnel_name', 'checkout')
    .order('step_order');

  return data;
}

async function getTopPagesByConversion(
  supabase: any,
  dateRange: DateRange
): Promise<PageMetric[]> {
  const { data } = await supabase
    .rpc('get_pages_by_conversion', {
      start_date: dateRange.start,
      end_date: dateRange.end,
      limit_count: 10
    });

  return data;
}

async function getActiveExperiments(
  supabase: any
): Promise<ExperimentSummary[]> {
  const { data } = await supabase
    .from('experiments')
    .select(`
      id,
      name,
      status,
      variants:experiment_variants(
        id,
        name,
        traffic_allocation
      ),
      results:experiment_results(
        variant_id,
        visitors,
        conversions,
        revenue
      )
    `)
    .eq('status', 'active');

  return data.map(exp => ({
    ...exp,
    variants: exp.variants.map(v => {
      const result = exp.results.find(r => r.variant_id === v.id);
      return {
        ...v,
        visitors: result?.visitors || 0,
        conversions: result?.conversions || 0,
        conversionRate: result?.visitors
          ? (result.conversions / result.visitors * 100).toFixed(2)
          : '0'
      };
    })
  }));
}

function calculateChange(current: number, previous: number): number {
  if (previous === 0) return 0;
  return ((current - previous) / previous) * 100;
}

-- Conversion metrics function
CREATE OR REPLACE FUNCTION get_conversion_metrics(
  start_date TIMESTAMPTZ,
  end_date TIMESTAMPTZ
)
RETURNS JSON AS $$
DECLARE
  result JSON;
BEGIN
  WITH session_data AS (
    SELECT
      s.id as session_id,
      s.user_id,
      s.device_type,
      s.traffic_source,
      s.landing_page,
      COALESCE(c.revenue, 0) as revenue,
      CASE WHEN c.id IS NOT NULL THEN 1 ELSE 0 END as converted
    FROM sessions s
    LEFT JOIN conversions c ON s.id = c.session_id
    WHERE s.created_at BETWEEN start_date AND end_date
  )
  SELECT json_build_object(
    'sessions', COUNT(*),
    'conversions', SUM(converted),
    'conversion_rate', ROUND(
      SUM(converted)::numeric / NULLIF(COUNT(*), 0) * 100,
      2
    ),
    'revenue', ROUND(SUM(revenue)::numeric, 2),
    'avg_order_value', ROUND(
      SUM(revenue)::numeric / NULLIF(SUM(converted), 0),
      2
    ),
    'revenue_per_session', ROUND(
      SUM(revenue)::numeric / NULLIF(COUNT(*), 0),
      2
    ),
    'by_device', (
      SELECT json_agg(json_build_object(
        'device', device_type,
        'sessions', sessions,
        'conversions', conversions,
        'conversion_rate', conversion_rate,
        'revenue', revenue
      ))
      FROM (
        SELECT
          device_type,
          COUNT(*) as sessions,
          SUM(converted) as conversions,
          ROUND(SUM(converted)::numeric / NULLIF(COUNT(*), 0) * 100, 2) as conversion_rate,
          ROUND(SUM(revenue)::numeric, 2) as revenue
        FROM session_data
        GROUP BY device_type
      ) device_breakdown
    ),
    'by_source', (
      SELECT json_agg(json_build_object(
        'source', traffic_source,
        'sessions', sessions,
        'conversions', conversions,
        'conversion_rate', conversion_rate,
        'revenue', revenue
      ))
      FROM (
        SELECT
          traffic_source,
          COUNT(*) as sessions,
          SUM(converted) as conversions,
          ROUND(SUM(converted)::numeric / NULLIF(COUNT(*), 0) * 100, 2) as conversion_rate,
          ROUND(SUM(revenue)::numeric, 2) as revenue
        FROM session_data
        GROUP BY traffic_source
        ORDER BY SUM(revenue) DESC
        LIMIT 10
      ) source_breakdown
    )
  ) INTO result
  FROM session_data;

  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Page conversion metrics
CREATE OR REPLACE FUNCTION get_pages_by_conversion(
  start_date TIMESTAMPTZ,
  end_date TIMESTAMPTZ,
  limit_count INTEGER DEFAULT 20
)
RETURNS TABLE (
  page_path TEXT,
  pageviews BIGINT,
  unique_visitors BIGINT,
  conversions BIGINT,
  conversion_rate NUMERIC,
  revenue NUMERIC,
  bounce_rate NUMERIC
) AS $$
BEGIN
  RETURN QUERY
  WITH page_stats AS (
    SELECT
      pv.page_path,
      COUNT(*) as pageviews,
      COUNT(DISTINCT pv.session_id) as sessions,
      COUNT(DISTINCT pv.user_id) as unique_visitors,
      COUNT(DISTINCT CASE WHEN c.id IS NOT NULL THEN pv.session_id END) as conversions,
      COALESCE(SUM(c.revenue), 0) as revenue,
      COUNT(DISTINCT CASE WHEN pv.is_bounce THEN pv.session_id END)::numeric /
        NULLIF(COUNT(DISTINCT pv.session_id), 0) * 100 as bounce_rate
    FROM pageviews pv
    LEFT JOIN conversions c ON pv.session_id = c.session_id
    WHERE pv.created_at BETWEEN start_date AND end_date
    GROUP BY pv.page_path
  )
  SELECT
    ps.page_path,
    ps.pageviews,
    ps.unique_visitors,
    ps.conversions,
    ROUND(ps.conversions::numeric / NULLIF(ps.sessions, 0) * 100, 2) as conversion_rate,
    ROUND(ps.revenue::numeric, 2) as revenue,
    ROUND(ps.bounce_rate, 2) as bounce_rate
  FROM page_stats ps
  ORDER BY ps.conversions DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

class ExperimentAnalytics {
  private supabase;

  constructor() {
    this.supabase = createClient(
      process.env.SUPABASE_URL!,
      process.env.SUPABASE_SERVICE_KEY!
    );
  }

  async getExperimentResults(experimentId: string): Promise<ExperimentResults> {
    // Get experiment config
    const { data: experiment } = await this.supabase
      .from('experiments')
      .select(`
        *,
        variants:experiment_variants(*)
      `)
      .eq('id', experimentId)
      .single();

    // Get results for each variant
    const variantResults = await Promise.all(
      experiment.variants.map(async (variant) => {
        const [exposures, conversions] = await Promise.all([
          this.getVariantExposures(experimentId, variant.id),
          this.getVariantConversions(experimentId, variant.id)
        ]);

        return {
          variant,
          exposures: exposures.count,
          conversions: conversions.count,
          revenue: conversions.revenue,
          conversionRate: exposures.count > 0
            ? (conversions.count / exposures.count) * 100
            : 0,
          revenuePerVisitor: exposures.count > 0
            ? conversions.revenue / exposures.count
            : 0
        };
      })
    );

    // Calculate statistical significance
    const control = variantResults.find(v => v.variant.is_control);
    const treatments = variantResults.filter(v => !v.variant.is_control);

    const statisticalResults = treatments.map(treatment => ({
      ...treatment,
      significance: this.calculateSignificance(
        control!.exposures,
        control!.conversions,
        treatment.exposures,
        treatment.conversions
      )
    }));

    return {
      experiment,
      control,
      treatments: statisticalResults,
      recommendation: this.getRecommendation(control!, statisticalResults)
    };
  }

  private async getVariantExposures(
    experimentId: string,
    variantId: string
  ): Promise<{ count: number }> {
    const { count } = await this.supabase
      .from('experiment_exposures')
      .select('*', { count: 'exact', head: true })
      .eq('experiment_id', experimentId)
      .eq('variant_id', variantId);

    return { count: count || 0 };
  }

  private async getVariantConversions(
    experimentId: string,
    variantId: string
  ): Promise<{ count: number; revenue: number }> {
    const { data, count } = await this.supabase
      .from('experiment_conversions')
      .select('value', { count: 'exact' })
      .eq('experiment_id', experimentId)
      .eq('variant_id', variantId);

    return {
      count: count || 0,
      revenue: data?.reduce((sum, c) => sum + (c.value || 0), 0) || 0
    };
  }

  private calculateSignificance(
    controlVisitors: number,
    controlConversions: number,
    treatmentVisitors: number,
    treatmentConversions: number
  ): SignificanceResult {
    const controlRate = controlConversions / controlVisitors;
    const treatmentRate = treatmentConversions / treatmentVisitors;

    // Pooled standard error
    const pooledRate = (controlConversions + treatmentConversions) /
      (controlVisitors + treatmentVisitors);

    const standardError = Math.sqrt(
      pooledRate * (1 - pooledRate) *
      (1 / controlVisitors + 1 / treatmentVisitors)
    );

    // Z-score
    const zScore = (treatmentRate - controlRate) / standardError;

    // P-value (two-tailed)
    const pValue = 2 * (1 - this.normalCDF(Math.abs(zScore)));

    // Confidence interval
    const marginOfError = 1.96 * standardError;

    return {
      controlRate: controlRate * 100,
      treatmentRate: treatmentRate * 100,
      relativeLift: ((treatmentRate - controlRate) / controlRate) * 100,
      zScore,
      pValue,
      isSignificant: pValue < 0.05,
      confidenceInterval: {
        lower: ((treatmentRate - controlRate) - marginOfError) * 100,
        upper: ((treatmentRate - controlRate) + marginOfError) * 100
      }
    };
  }

  private normalCDF(x: number): number {
    const a1 = 0.254829592;
    const a2 = -0.284496736;
    const a3 = 1.421413741;
    const a4 = -1.453152027;
    const a5 = 1.061405429;
    const p = 0.3275911;

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

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

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

  private getRecommendation(
    control: VariantResult,
    treatments: (VariantResult & { significance: SignificanceResult })[]
  ): string {
    const significantWinners = treatments.filter(
      t => t.significance.isSignificant && t.significance.relativeLift > 0
    );

    if (significantWinners.length === 0) {
      const anySignificant = treatments.some(t => t.significance.isSignificant);
      if (anySignificant) {
        return 'Keep control - no treatment shows significant improvement';
      }
      return 'Continue testing - not yet statistically significant';
    }

    const bestWinner = significantWinners.reduce((best, current) =>
      current.significance.relativeLift > best.significance.relativeLift ? current : best
    );

    return `Implement ${bestWinner.variant.name} - ${bestWinner.significance.relativeLift.toFixed(1)}% lift with ${((1 - bestWinner.significance.pValue) * 100).toFixed(1)}% confidence`;
  }
}

interface WeeklyCROReport {
  dateRange: {
    start: string;
    end: string;
  };
  summary: {
    conversionRate: MetricWithChange;
    conversions: MetricWithChange;
    revenue: MetricWithChange;
    avgOrderValue: MetricWithChange;
  };
  funnelPerformance: {
    name: string;
    completionRate: number;
    biggestDropoff: {
      step: string;
      dropoffRate: number;
    };
  }[];
  experimentsUpdate: {
    running: number;
    concluded: number;
    winners: number;
    topWin: {
      name: string;
      lift: number;
    } | null;
  };
  topInsights: string[];
  nextWeekPriorities: string[];
}

async function generateWeeklyCROReport(): Promise<WeeklyCROReport> {
  const thisWeek = getDateRange('this_week');
  const lastWeek = getDateRange('last_week');

  const [currentMetrics, previousMetrics, funnels, experiments] = await Promise.all([
    getConversionMetrics(thisWeek),
    getConversionMetrics(lastWeek),
    getFunnelPerformance(thisWeek),
    getExperimentsSummary()
  ]);

  return {
    dateRange: {
      start: thisWeek.start,
      end: thisWeek.end
    },
    summary: {
      conversionRate: {
        value: currentMetrics.conversionRate,
        change: calculateChange(currentMetrics.conversionRate, previousMetrics.conversionRate),
        trend: currentMetrics.conversionRate > previousMetrics.conversionRate ? 'up' : 'down'
      },
      conversions: {
        value: currentMetrics.conversions,
        change: calculateChange(currentMetrics.conversions, previousMetrics.conversions),
        trend: currentMetrics.conversions > previousMetrics.conversions ? 'up' : 'down'
      },
      revenue: {
        value: currentMetrics.revenue,
        change: calculateChange(currentMetrics.revenue, previousMetrics.revenue),
        trend: currentMetrics.revenue > previousMetrics.revenue ? 'up' : 'down'
      },
      avgOrderValue: {
        value: currentMetrics.avgOrderValue,
        change: calculateChange(currentMetrics.avgOrderValue, previousMetrics.avgOrderValue),
        trend: currentMetrics.avgOrderValue > previousMetrics.avgOrderValue ? 'up' : 'down'
      }
    },
    funnelPerformance: funnels,
    experimentsUpdate: experiments,
    topInsights: generateInsights(currentMetrics, previousMetrics, funnels),
    nextWeekPriorities: generatePriorities(funnels, experiments)
  };
}

## CRO Analytics Setup Checklist

### Foundation
□ GA4 properly configured
□ Enhanced e-commerce tracking enabled
□ Custom dimensions for segmentation
□ Conversion goals defined
□ User ID tracking (if applicable)

### Funnel Tracking
□ Key funnels identified and mapped
□ Step events tracked consistently
□ Abandonment tracking implemented
□ Funnel visualization in dashboard

### Micro-Conversions
□ Scroll depth tracking
□ Time on page tracking
□ Form interaction tracking
□ CTA engagement tracking
□ Content interaction tracking

### Experiment Tracking
□ Exposure events tracked
□ Conversion attribution to variants
□ Statistical significance calculation
□ Segment-level analysis enabled

### Reporting
□ Daily automated reports
□ Weekly summary reports
□ Real-time dashboard available
□ Alert thresholds configured
□ Stakeholder access set up

### Data Quality
□ Bot traffic filtered
□ Internal traffic excluded
□ Duplicate events deduplicated
□ Cross-domain tracking (if needed)
□ Data validation in place