sample_data.sql

-- Sample Data Generator for Causal ROI Dashboard
-- Creates 100 paths with 7 days of realistic treatment/control data
-- Run this to test the dashboard before connecting real data sources

-- ============================================================================
-- SAMPLE NODES (signals, actions, outcomes)
-- ============================================================================

INSERT INTO `causal_roi.nodes` (node_id, node_type, label, props) VALUES
-- Signals
('signal_001', 'signal', 'High Intent Search', JSON '{"source": "google_ads", "priority": "high"}'),
('signal_002', 'signal', 'Email Open', JSON '{"source": "sendgrid", "priority": "medium"}'),
('signal_003', 'signal', 'Cart Abandon', JSON '{"source": "shopify", "priority": "high"}'),
('signal_004', 'signal', 'Product View', JSON '{"source": "web_analytics", "priority": "low"}'),
('signal_005', 'signal', 'Price Drop Alert', JSON '{"source": "pricing_engine", "priority": "high"}'),

-- Actions
('action_001', 'action', 'Send Promo Email', JSON '{"channel": "email", "cost_per": 0.05}'),
('action_002', 'action', 'Display Retargeting Ad', JSON '{"channel": "display", "cost_per": 2.5}'),
('action_003', 'action', 'Search Ad Bid Increase', JSON '{"channel": "search", "cost_per": 3.2}'),
('action_004', 'action', 'Push Notification', JSON '{"channel": "mobile", "cost_per": 0.02}'),
('action_005', 'action', 'SMS Reminder', JSON '{"channel": "sms", "cost_per": 0.15}'),

-- Outcomes
('outcome_001', 'outcome', 'Revenue', JSON '{"kpi": "revenue", "unit": "usd"}'),
('outcome_002', 'outcome', 'Conversion', JSON '{"kpi": "conversion", "unit": "count"}'),
('outcome_003', 'outcome', 'LTV', JSON '{"kpi": "ltv", "unit": "usd"}');

-- ============================================================================
-- SAMPLE EDGES (signal->action->outcome paths)
-- ============================================================================

INSERT INTO `causal_roi.edges` (edge_id, src_node_id, dst_node_id, edge_type, latest_delta_roi, latest_latency_ms, decay_weight, n_observations) VALUES
-- High-performing paths
('edge_s1_a1', 'signal_001', 'action_001', 'signal->action', 2.8, 1200, 0.92, 450),
('edge_a1_o1', 'action_001', 'outcome_001', 'action->outcome', 2.8, 86400000, 0.92, 450),

('edge_s3_a2', 'signal_003', 'action_002', 'signal->action', 3.2, 900, 0.95, 520),
('edge_a2_o1', 'action_002', 'outcome_001', 'action->outcome', 3.2, 172800000, 0.95, 520),

-- Medium-performing paths
('edge_s2_a1', 'signal_002', 'action_001', 'signal->action', 1.5, 2400, 0.78, 320),
('edge_s4_a3', 'signal_004', 'action_003', 'signal->action', 1.2, 3600, 0.65, 280),
('edge_a3_o1', 'action_003', 'outcome_001', 'action->outcome', 1.2, 259200000, 0.65, 280),

-- Low/negative-performing paths
('edge_s4_a4', 'signal_004', 'action_004', 'signal->action', -0.3, 1800, 0.45, 150),
('edge_a4_o1', 'action_004', 'outcome_001', 'action->outcome', -0.3, 86400000, 0.45, 150),

-- Mixed paths
('edge_s5_a5', 'signal_005', 'action_005', 'signal->action', 1.8, 1500, 0.82, 380),
('edge_a5_o1', 'action_005', 'outcome_001', 'action->outcome', 1.8, 43200000, 0.82, 380);

-- ============================================================================
-- SAMPLE PATHS (100 synthetic paths over 7 days)
-- ============================================================================

-- Generate path IDs and forecasts
DECLARE path_counter INT64 DEFAULT 1;
WHILE path_counter <= 100 DO
  INSERT INTO `causal_roi.events_forecast` (path_id, hypothesis_id, t0, features)
  VALUES (
    CONCAT('path_', LPAD(CAST(path_counter AS STRING), 4, '0')),
    CONCAT('hyp_', CAST(MOD(path_counter, 10) AS STRING)),
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL MOD(path_counter, 7) DAY),
    JSON '{"model_version": "v2.3", "confidence": 0.75}'
  );
  SET path_counter = path_counter + 1;
END WHILE;

-- ============================================================================
-- SAMPLE ACTIONS (varying spend levels)
-- ============================================================================

INSERT INTO `causal_roi.events_action` (path_id, action_id, channel, spend, t1, metadata)
SELECT 
  ef.path_id,
  CONCAT('act_', ef.path_id),
  CASE MOD(CAST(SUBSTR(ef.path_id, 6) AS INT64), 5)
    WHEN 0 THEN 'email'
    WHEN 1 THEN 'display'
    WHEN 2 THEN 'search'
    WHEN 3 THEN 'mobile'
    ELSE 'sms'
  END AS channel,
  -- Varying spend: $50-$5000
  50 + (RAND() * 4950) AS spend,
  TIMESTAMP_ADD(ef.t0, INTERVAL CAST(500 + RAND() * 3000 AS INT64) MILLISECOND) AS t1,
  JSON '{"campaign": "Q4_test", "creative": "variant_A"}'
FROM `causal_roi.events_forecast` ef;

-- ============================================================================
-- SAMPLE OUTCOMES (treatment + control with realistic uplift)
-- ============================================================================

-- Treatment outcomes (actual observed revenue)
INSERT INTO `causal_roi.events_outcome` (path_id, kpi, value, t2, cohort_id)
SELECT
  ea.path_id,
  'revenue' AS kpi,
  -- Revenue = spend * base_roi * random_multiplier
  ea.spend * (1.5 + (RAND() * 2)) * 
    CASE 
      WHEN ea.channel IN ('email', 'sms') THEN 1.3  -- higher ROI channels
      WHEN ea.channel = 'display' THEN 1.0
      ELSE 0.8
    END AS value,
  TIMESTAMP_ADD(ea.t1, INTERVAL 1 DAY) AS t2,
  'treatment' AS cohort_id
FROM `causal_roi.events_action` ea;

-- Control outcomes (counterfactual - lower revenue)
INSERT INTO `causal_roi.events_outcome` (path_id, kpi, value, t2, cohort_id)
SELECT
  ea.path_id,
  'revenue' AS kpi,
  -- Control revenue = treatment * 0.6-0.9 (simulate uplift)
  ea.spend * (1.5 + (RAND() * 2)) * 
    CASE 
      WHEN ea.channel IN ('email', 'sms') THEN 1.3
      WHEN ea.channel = 'display' THEN 1.0
      ELSE 0.8
    END * (0.6 + RAND() * 0.3) AS value,  -- control is 60-90% of treatment
  TIMESTAMP_ADD(ea.t1, INTERVAL 1 DAY) AS t2,
  CONCAT('control_geo_', CAST(MOD(CAST(SUBSTR(ea.path_id, 6) AS INT64), 3) AS STRING)) AS cohort_id
FROM `causal_roi.events_action` ea;

-- ============================================================================
-- SAMPLE ATTRIBUTION LINKS
-- ============================================================================

INSERT INTO `causal_roi.attribution_links` (path_id, method, weight, window_days)
SELECT 
  path_id,
  CASE MOD(CAST(SUBSTR(path_id, 6) AS INT64), 3)
    WHEN 0 THEN 'shapley'
    WHEN 1 THEN 'markov'
    ELSE 'geo_holdout'
  END AS method,
  0.3 + (RAND() * 0.7) AS weight,  -- 0.3 to 1.0
  7 AS window_days
FROM `causal_roi.events_forecast`;

-- ============================================================================
-- SAMPLE CONTROL COHORTS
-- ============================================================================

INSERT INTO `causal_roi.control_cohorts` (cohort_id, path_id, method, geo_region, quality_score)
SELECT 
  CONCAT('control_geo_', CAST(MOD(CAST(SUBSTR(ef.path_id, 6) AS INT64), 3) AS STRING)),
  ef.path_id,
  'geo_holdout' AS method,
  CASE MOD(CAST(SUBSTR(ef.path_id, 6) AS INT64), 3)
    WHEN 0 THEN 'US-CA'
    WHEN 1 THEN 'US-TX'
    ELSE 'US-NY'
  END AS geo_region,
  0.75 + (RAND() * 0.2) AS quality_score  -- 0.75 to 0.95
FROM `causal_roi.events_forecast` ef;

-- ============================================================================
-- VERIFY SAMPLE DATA
-- ============================================================================

-- Check row counts
SELECT 'events_forecast' AS table_name, COUNT(*) AS rows FROM `causal_roi.events_forecast`
UNION ALL
SELECT 'events_action', COUNT(*) FROM `causal_roi.events_action`
UNION ALL
SELECT 'events_outcome', COUNT(*) FROM `causal_roi.events_outcome`
UNION ALL
SELECT 'attribution_links', COUNT(*) FROM `causal_roi.attribution_links`
UNION ALL
SELECT 'nodes', COUNT(*) FROM `causal_roi.nodes`
UNION ALL
SELECT 'edges', COUNT(*) FROM `causal_roi.edges`;

-- Sample path check (should show treatment vs control)
SELECT 
  path_id,
  cohort_id,
  AVG(value) AS avg_revenue,
  COUNT(*) AS n_obs
FROM `causal_roi.events_outcome`
WHERE path_id = 'path_0001'
GROUP BY path_id, cohort_id;