-- Key Business Metrics Dashboard
WITH monthly_metrics AS (
  SELECT 
    DATE_TRUNC('month', date) as month,
    SUM(revenue) as monthly_revenue,
    COUNT(DISTINCT customer_id) as active_customers,
    AVG(order_value) as avg_order_value,
    SUM(revenue) / COUNT(DISTINCT customer_id) as revenue_per_customer
  FROM transactions 
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  GROUP BY DATE_TRUNC('month', date)
),
growth_calculations AS (
  SELECT *,
    LAG(monthly_revenue, 1) OVER (ORDER BY month) as prev_month_revenue,
    (monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY month)) / 
     LAG(monthly_revenue, 1) OVER (ORDER BY month) * 100 as revenue_growth_rate
  FROM monthly_metrics
)
SELECT 
  month,
  monthly_revenue,
  active_customers,
  avg_order_value,
  revenue_per_customer,
  revenue_growth_rate,
  CASE 
    WHEN revenue_growth_rate > 10 THEN 'High Growth'
    WHEN revenue_growth_rate > 0 THEN 'Positive Growth'
    ELSE 'Needs Attention'
  END as growth_status
FROM growth_calculations
ORDER BY month DESC;

import pandas as pd
import numpy as np
from sklearn.cluster import KMeans
import matplotlib.pyplot as plt
import seaborn as sns

# Create RFM scores
rfm['r_score'] = pd.qcut(rfm['recency'], 5, labels=[5,4,3,2,1])
rfm['f_score'] = pd.qcut(rfm['frequency'].rank(method='first'), 5, labels=[1,2,3,4,5])
rfm['m_score'] = pd.qcut(rfm['monetary'], 5, labels=[1,2,3,4,5])

# Customer segments
rfm['rfm_score'] = rfm['r_score'].astype(str) + rfm['f_score'].astype(str) + rfm['m_score'].astype(str)

def segment_customers(row):
    if row['rfm_score'] in ['555', '554', '544', '545', '454', '455', '445']:
        return 'Champions'
    elif row['rfm_score'] in ['543', '444', '435', '355', '354', '345', '344', '335']:
        return 'Loyal Customers'
    elif row['rfm_score'] in ['553', '551', '552', '541', '542', '533', '532', '531', '452', '451']:
        return 'Potential Loyalists'
    elif row['rfm_score'] in ['512', '511', '422', '421', '412', '411', '311']:
        return 'New Customers'
    elif row['rfm_score'] in ['155', '154', '144', '214', '215', '115', '114']:
        return 'At Risk'
    elif row['rfm_score'] in ['155', '154', '144', '214', '215', '115', '114']:
        return 'Cannot Lose Them'
    else:
        return 'Others'

rfm['segment'] = rfm.apply(segment_customers, axis=1)

return rfm

undefined

// Marketing Attribution and ROI Analysis
const marketingDashboard = {
  // Multi-touch attribution model
  attributionAnalysis: `
    WITH customer_touchpoints AS (
      SELECT 
        customer_id,
        channel,
        campaign,
        touchpoint_date,
        conversion_date,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_date) as touch_sequence,
        COUNT(*) OVER (PARTITION BY customer_id) as total_touches
      FROM marketing_touchpoints mt
      JOIN conversions c ON mt.customer_id = c.customer_id
      WHERE touchpoint_date <= conversion_date
    ),
    attribution_weights AS (
      SELECT *,
        CASE 
          WHEN touch_sequence = 1 AND total_touches = 1 THEN 1.0  -- Single touch
          WHEN touch_sequence = 1 THEN 0.4                       -- First touch
          WHEN touch_sequence = total_touches THEN 0.4           -- Last touch
          ELSE 0.2 / (total_touches - 2)                        -- Middle touches
        END as attribution_weight
      FROM customer_touchpoints
    )
    SELECT 
      channel,
      campaign,
      SUM(revenue * attribution_weight) as attributed_revenue,
      COUNT(DISTINCT customer_id) as attributed_conversions,
      SUM(revenue * attribution_weight) / COUNT(DISTINCT customer_id) as revenue_per_conversion
    FROM attribution_weights
    GROUP BY channel, campaign
    ORDER BY attributed_revenue DESC;
  `,
  
  // Campaign ROI calculation
  campaignROI: `
    SELECT 
      campaign_name,
      SUM(spend) as total_spend,
      SUM(attributed_revenue) as total_revenue,
      (SUM(attributed_revenue) - SUM(spend)) / SUM(spend) * 100 as roi_percentage,
      SUM(attributed_revenue) / SUM(spend) as revenue_multiple,
      COUNT(conversions) as total_conversions,
      SUM(spend) / COUNT(conversions) as cost_per_conversion
    FROM campaign_performance
    WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    GROUP BY campaign_name
    HAVING SUM(spend) > 1000  -- Filter for significant spend
    ORDER BY roi_percentage DESC;
  `
};

undefined

undefined

undefined

undefined