Metric Diagnosis & Segmentation Analysis Questions

-- replace date filters as needed
WITH orders_month AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(DISTINCT order_id) AS orders,
    SUM(order_value) AS revenue
  FROM orders
  WHERE order_date >= '2024-01-01' AND order_date < '2024-03-01'
  GROUP BY 1
),
sessions_month AS (
  SELECT
    DATE_TRUNC('month', session_date) AS month,
    COUNT(*) AS sessions -- or COUNT(DISTINCT session_id) / COUNT(DISTINCT user_id) depending on definition
  FROM sessions
  WHERE session_date >= '2024-01-01' AND session_date < '2024-03-01'
  GROUP BY 1
)
SELECT
  s.month,
  s.sessions,
  o.orders,
  o.revenue,
  (o.orders::numeric / NULLIF(s.sessions,0)) AS conv_rate,
  (o.revenue::numeric / NULLIF(o.orders,0)) AS aov
FROM sessions_month s
LEFT JOIN orders_month o USING(month)
ORDER BY month;

WITH metrics AS (
  -- same as previous CTE but filtered to two months, produce numeric columns
  /* ... insert metrics query here ... */
),
pairs AS (
  SELECT
    m_curr.month AS month_curr,
    m_prev.month AS month_prev,
    m_curr.sessions AS T1, m_prev.sessions AS T0,
    m_curr.conv_rate AS C1, m_prev.conv_rate AS C0,
    m_curr.aov AS A1, m_prev.aov AS A0,
    m_curr.revenue AS R1, m_prev.revenue AS R0
  FROM metrics m_curr
  JOIN metrics m_prev ON m_prev.month = m_curr.month - INTERVAL '1 month'
  WHERE m_curr.month = DATE '2024-02-01'  -- example current month
)
SELECT
  *,
  (R1 - R0) AS delta_revenue,
  -- first-order contributions using baseline (month 0) as reference
  (T1 - T0) * C0 * A0 AS contrib_traffic,
  T0 * (C1 - C0) * A0 AS contrib_conversion,
  T0 * C0 * (A1 - A0) AS contrib_aov,
  -- remainder (interaction) = total delta - sum(first-order)
  (R1 - R0) - (
    (T1 - T0) * C0 * A0 + T0 * (C1 - C0) * A0 + T0 * C0 * (A1 - A0)
  ) AS interaction
FROM pairs;