Attribution Reporting

-- Channel performance with attributed metrics
WITH channel_attribution AS (
  SELECT
    ar.channel,
    ar.model,
    SUM(ar.credit) as attributed_conversions,
    SUM(ar.value) as attributed_revenue,
    COUNT(DISTINCT ar.conversion_id) as conversion_count
  FROM attribution_results ar
  JOIN conversions c ON ar.conversion_id = c.id
  WHERE c.timestamp BETWEEN :start_date AND :end_date
    AND ar.model = :attribution_model
  GROUP BY ar.channel, ar.model
),
channel_spend AS (
  SELECT
    CASE
      WHEN platform = 'google_ads' THEN 'google_ads'
      WHEN platform = 'meta_ads' THEN 'meta_ads'
      WHEN platform = 'linkedin_ads' THEN 'linkedin_ads'
      ELSE platform
    END as channel,
    SUM(spend) as total_spend,
    SUM(clicks) as total_clicks,
    SUM(impressions) as total_impressions
  FROM marketing_spend
  WHERE date BETWEEN :start_date AND :end_date
  GROUP BY 1
),
channel_touchpoints AS (
  SELECT
    channel,
    COUNT(*) as touchpoint_count,
    COUNT(DISTINCT profile_id) as unique_users
  FROM touchpoints
  WHERE timestamp BETWEEN :start_date AND :end_date
  GROUP BY channel
)
SELECT
  COALESCE(ca.channel, cs.channel, ct.channel) as channel,
  -- Spend metrics
  COALESCE(cs.total_spend, 0) as spend,
  COALESCE(cs.total_clicks, 0) as clicks,
  COALESCE(cs.total_impressions, 0) as impressions,
  -- Attribution metrics
  COALESCE(ca.attributed_conversions, 0) as attributed_conversions,
  COALESCE(ca.attributed_revenue, 0) as attributed_revenue,
  -- Engagement metrics
  COALESCE(ct.touchpoint_count, 0) as touchpoints,
  COALESCE(ct.unique_users, 0) as unique_users,
  -- Calculated metrics
  ROUND(COALESCE(ca.attributed_revenue, 0) / NULLIF(cs.total_spend, 0), 2) as roas,
  ROUND(cs.total_spend / NULLIF(ca.attributed_conversions, 0), 2) as cpa,
  ROUND(cs.total_clicks::numeric / NULLIF(cs.total_impressions, 0) * 100, 2) as ctr,
  ROUND(ca.attributed_conversions / NULLIF(cs.total_clicks, 0) * 100, 2) as conversion_rate
FROM channel_attribution ca
FULL OUTER JOIN channel_spend cs ON ca.channel = cs.channel
FULL OUTER JOIN channel_touchpoints ct ON COALESCE(ca.channel, cs.channel) = ct.channel
ORDER BY attributed_revenue DESC NULLS LAST;

-- Campaign-level attribution with spend
WITH campaign_attribution AS (
  SELECT
    ar.platform,
    ar.campaign_id,
    ar.campaign_name,
    SUM(ar.credit) as attributed_conversions,
    SUM(ar.value) as attributed_revenue
  FROM attribution_results ar
  JOIN conversions c ON ar.conversion_id = c.id
  WHERE c.timestamp BETWEEN :start_date AND :end_date
    AND ar.model = :attribution_model
  GROUP BY ar.platform, ar.campaign_id, ar.campaign_name
),
campaign_spend AS (
  SELECT
    platform,
    campaign_id,
    campaign_name,
    SUM(spend) as total_spend,
    SUM(clicks) as total_clicks,
    SUM(impressions) as total_impressions,
    SUM(platform_conversions) as platform_conversions,
    SUM(platform_revenue) as platform_revenue
  FROM marketing_spend
  WHERE date BETWEEN :start_date AND :end_date
  GROUP BY platform, campaign_id, campaign_name
)
SELECT
  cs.platform,
  cs.campaign_id,
  cs.campaign_name,
  cs.total_spend as spend,
  cs.total_clicks as clicks,
  -- Platform-reported (for comparison)
  cs.platform_conversions as platform_reported_conversions,
  cs.platform_revenue as platform_reported_revenue,
  ROUND(cs.platform_revenue / NULLIF(cs.total_spend, 0), 2) as platform_roas,
  -- Unified attribution
  COALESCE(ca.attributed_conversions, 0) as unified_conversions,
  COALESCE(ca.attributed_revenue, 0) as unified_revenue,
  ROUND(COALESCE(ca.attributed_revenue, 0) / NULLIF(cs.total_spend, 0), 2) as unified_roas,
  -- Variance
  ROUND(
    (cs.platform_conversions - COALESCE(ca.attributed_conversions, 0)) /
    NULLIF(cs.platform_conversions, 0) * 100, 1
  ) as overclaim_pct
FROM campaign_spend cs
LEFT JOIN campaign_attribution ca
  ON cs.platform = ca.platform AND cs.campaign_id = ca.campaign_id
ORDER BY cs.total_spend DESC;

-- Compare attribution across different models
WITH model_attribution AS (
  SELECT
    ar.channel,
    ar.model,
    SUM(ar.credit) as conversions,
    SUM(ar.value) as revenue
  FROM attribution_results ar
  JOIN conversions c ON ar.conversion_id = c.id
  WHERE c.timestamp BETWEEN :start_date AND :end_date
  GROUP BY ar.channel, ar.model
),
pivoted AS (
  SELECT
    channel,
    MAX(CASE WHEN model = 'first_touch' THEN conversions END) as first_touch_conv,
    MAX(CASE WHEN model = 'last_touch' THEN conversions END) as last_touch_conv,
    MAX(CASE WHEN model = 'linear' THEN conversions END) as linear_conv,
    MAX(CASE WHEN model = 'time_decay' THEN conversions END) as time_decay_conv,
    MAX(CASE WHEN model = 'position_based' THEN conversions END) as position_based_conv,
    MAX(CASE WHEN model = 'first_touch' THEN revenue END) as first_touch_rev,
    MAX(CASE WHEN model = 'last_touch' THEN revenue END) as last_touch_rev,
    MAX(CASE WHEN model = 'linear' THEN revenue END) as linear_rev,
    MAX(CASE WHEN model = 'time_decay' THEN revenue END) as time_decay_rev,
    MAX(CASE WHEN model = 'position_based' THEN revenue END) as position_based_rev
  FROM model_attribution
  GROUP BY channel
)
SELECT
  channel,
  -- Conversions by model
  ROUND(first_touch_conv, 2) as first_touch,
  ROUND(last_touch_conv, 2) as last_touch,
  ROUND(linear_conv, 2) as linear,
  ROUND(time_decay_conv, 2) as time_decay,
  ROUND(position_based_conv, 2) as position_based,
  -- Variance (max - min shows how much models disagree)
  ROUND(
    GREATEST(first_touch_conv, last_touch_conv, linear_conv, time_decay_conv, position_based_conv) -
    LEAST(first_touch_conv, last_touch_conv, linear_conv, time_decay_conv, position_based_conv),
    2
  ) as model_variance
FROM pivoted
ORDER BY position_based_conv DESC NULLS LAST;

-- Analyze conversion path lengths
WITH conversion_paths AS (
  SELECT
    c.id as conversion_id,
    c.value as conversion_value,
    COUNT(DISTINCT t.id) as touchpoint_count,
    COUNT(DISTINCT t.channel) as unique_channels,
    EXTRACT(DAY FROM c.timestamp - MIN(t.timestamp)) as journey_days
  FROM conversions c
  JOIN touchpoints t ON c.profile_id = t.profile_id
    AND t.timestamp <= c.timestamp
    AND t.timestamp >= c.timestamp - INTERVAL '30 days'
  WHERE c.timestamp BETWEEN :start_date AND :end_date
  GROUP BY c.id, c.value
)
SELECT
  touchpoint_count as path_length,
  COUNT(*) as conversions,
  SUM(conversion_value) as total_revenue,
  ROUND(AVG(conversion_value), 2) as avg_order_value,
  ROUND(AVG(unique_channels), 1) as avg_channels,
  ROUND(AVG(journey_days), 1) as avg_days
FROM conversion_paths
GROUP BY touchpoint_count
ORDER BY touchpoint_count;

-- Most common conversion paths
WITH ordered_touchpoints AS (
  SELECT
    c.id as conversion_id,
    c.value,
    t.channel,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY t.timestamp) as touch_order
  FROM conversions c
  JOIN touchpoints t ON c.profile_id = t.profile_id
    AND t.timestamp <= c.timestamp
    AND t.timestamp >= c.timestamp - INTERVAL '30 days'
  WHERE c.timestamp BETWEEN :start_date AND :end_date
),
paths AS (
  SELECT
    conversion_id,
    value,
    STRING_AGG(channel, ' → ' ORDER BY touch_order) as path
  FROM ordered_touchpoints
  GROUP BY conversion_id, value
)
SELECT
  path,
  COUNT(*) as conversions,
  SUM(value) as total_revenue,
  ROUND(AVG(value), 2) as avg_value,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as pct_of_conversions
FROM paths
GROUP BY path
ORDER BY conversions DESC
LIMIT 20;

-- Channels that assist but don't close
WITH touchpoint_positions AS (
  SELECT
    c.id as conversion_id,
    t.channel,
    ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY t.timestamp) as position,
    COUNT(*) OVER (PARTITION BY c.id) as total_touches,
    c.value
  FROM conversions c
  JOIN touchpoints t ON c.profile_id = t.profile_id
    AND t.timestamp <= c.timestamp
    AND t.timestamp >= c.timestamp - INTERVAL '30 days'
  WHERE c.timestamp BETWEEN :start_date AND :end_date
)
SELECT
  channel,
  -- Total appearances
  COUNT(*) as total_touchpoints,
  -- First touch (introducer)
  SUM(CASE WHEN position = 1 THEN 1 ELSE 0 END) as first_touch_count,
  -- Last touch (closer)
  SUM(CASE WHEN position = total_touches THEN 1 ELSE 0 END) as last_touch_count,
  -- Assist only (middle touches)
  SUM(CASE WHEN position > 1 AND position < total_touches THEN 1 ELSE 0 END) as assist_count,
  -- Assist ratio
  ROUND(
    SUM(CASE WHEN position > 1 AND position < total_touches THEN 1 ELSE 0 END)::numeric /
    NULLIF(SUM(CASE WHEN position = total_touches THEN 1 ELSE 0 END), 0),
    2
  ) as assist_to_close_ratio,
  -- Value contribution
  SUM(value) / COUNT(DISTINCT conversion_id) as avg_conversion_value
FROM touchpoint_positions
GROUP BY channel
ORDER BY total_touchpoints DESC;

-- Current ROAS vs Target ROAS
WITH current_performance AS (
  SELECT
    COALESCE(ar.channel, ms.platform) as channel,
    SUM(ms.spend) as spend,
    SUM(ar.value) as attributed_revenue,
    ROUND(SUM(ar.value) / NULLIF(SUM(ms.spend), 0), 2) as roas
  FROM marketing_spend ms
  LEFT JOIN attribution_results ar ON ms.platform = ar.platform
    AND ms.campaign_id = ar.campaign_id
  LEFT JOIN conversions c ON ar.conversion_id = c.id
    AND c.timestamp BETWEEN :start_date AND :end_date
  WHERE ms.date BETWEEN :start_date AND :end_date
  GROUP BY COALESCE(ar.channel, ms.platform)
)
SELECT
  channel,
  spend,
  attributed_revenue,
  roas,
  :target_roas as target_roas,
  CASE
    WHEN roas >= :target_roas * 1.5 THEN 'Scale Up'
    WHEN roas >= :target_roas THEN 'Maintain'
    WHEN roas >= :target_roas * 0.7 THEN 'Optimize'
    ELSE 'Reduce/Pause'
  END as recommendation,
  -- Suggested budget change
  CASE
    WHEN roas >= :target_roas * 1.5 THEN '+25%'
    WHEN roas >= :target_roas THEN '0%'
    WHEN roas >= :target_roas * 0.7 THEN '-10%'
    ELSE '-50%'
  END as budget_adjustment
FROM current_performance
ORDER BY roas DESC;

-- Optimal budget allocation based on marginal ROAS
WITH channel_efficiency AS (
  SELECT
    channel,
    spend,
    attributed_revenue,
    roas,
    -- Estimate marginal efficiency (simplified)
    CASE
      WHEN roas > 5 THEN 0.8  -- High ROAS = likely saturated
      WHEN roas > 3 THEN 0.9  -- Good ROAS = room to grow
      WHEN roas > 2 THEN 1.0  -- Moderate = maintain
      ELSE 0.5                -- Low = decrease
    END as efficiency_score
  FROM (
    SELECT
      ar.channel,
      SUM(ms.spend) as spend,
      SUM(ar.value) as attributed_revenue,
      SUM(ar.value) / NULLIF(SUM(ms.spend), 0) as roas
    FROM attribution_results ar
    JOIN marketing_spend ms ON ar.platform = ms.platform
    JOIN conversions c ON ar.conversion_id = c.id
    WHERE c.timestamp BETWEEN :start_date AND :end_date
      AND ms.date BETWEEN :start_date AND :end_date
    GROUP BY ar.channel
  ) perf
),
total_budget AS (
  SELECT SUM(spend) as total FROM channel_efficiency
)
SELECT
  ce.channel,
  ce.spend as current_spend,
  ce.roas as current_roas,
  -- Recommended allocation
  ROUND(
    (ce.efficiency_score * ce.spend) /
    SUM(ce.efficiency_score * ce.spend) OVER () *
    (SELECT total FROM total_budget),
    2
  ) as recommended_spend,
  -- Change
  ROUND(
    (ce.efficiency_score * ce.spend) /
    SUM(ce.efficiency_score * ce.spend) OVER () *
    (SELECT total FROM total_budget) - ce.spend,
    2
  ) as budget_change
FROM channel_efficiency ce
ORDER BY roas DESC;

interface ChannelReport {
  channel: string;
  spend: number;
  clicks: number;
  impressions: number;
  conversions: number;
  revenue: number;
  roas: number;
  cpa: number;
}

interface JourneyReport {
  pathLength: number;
  conversions: number;
  revenue: number;
  avgOrderValue: number;
}

async function getChannelReport(
  startDate: Date,
  endDate: Date,
  model: string = 'position_based'
): Promise<ChannelReport[]> {
  const { data } = await supabase.rpc('get_channel_attribution_report', {
    start_date: startDate.toISOString(),
    end_date: endDate.toISOString(),
    attribution_model: model
  });

  return data || [];
}

async function getModelComparison(
  startDate: Date,
  endDate: Date
): Promise<Record<string, Record<string, number>>> {
  const models = ['first_touch', 'last_touch', 'linear', 'time_decay', 'position_based'];

  const results = await Promise.all(
    models.map(model => getChannelReport(startDate, endDate, model))
  );

  // Transform to comparison format
  const comparison: Record<string, Record<string, number>> = {};

  results.forEach((modelData, i) => {
    const modelName = models[i];
    modelData.forEach(channel => {
      if (!comparison[channel.channel]) {
        comparison[channel.channel] = {};
      }
      comparison[channel.channel][modelName] = channel.conversions;
    });
  });

  return comparison;
}

async function getJourneyAnalysis(
  startDate: Date,
  endDate: Date
): Promise<JourneyReport[]> {
  const { data } = await supabase.rpc('get_journey_analysis', {
    start_date: startDate.toISOString(),
    end_date: endDate.toISOString()
  });

  return data || [];
}

1. **Lead with Actionable Insights**
   - Start with recommendations, not raw data
   - Highlight anomalies and changes
   - Include clear next steps

2. **Show Multiple Models**
   - Never rely on single model
   - Highlight where models agree/disagree
   - Use variance to identify uncertainty

3. **Compare to Benchmarks**
   - Previous period
   - Target ROAS/CPA
   - Industry benchmarks

4. **Segment Appropriately**
   - New vs returning customers
   - High vs low value
   - By product/service category

5. **Include Platform Comparison**
   - Platform-reported vs unified
   - Highlight overclaim percentages
   - Build trust in unified data