Cymbal Pets 營運數據分析儀表板

-- 1. 產出 2024 年 8 月至 2025 年 1 月月度營運指標
WITH order_totals AS (
  SELECT
    o.order_id,
    FORMAT_DATE('%Y-%m', o.order_date) AS order_month,
    o.order_type,
    SUM(oi.price) AS order_amount
  FROM `YOUR_PROJECT_ID.cymbal_pets.orders` o
  JOIN `YOUR_PROJECT_ID.cymbal_pets.order_items` oi
    ON o.order_id = oi.order_id
  WHERE o.order_date BETWEEN '2024-08-01' AND '2025-01-31'
  GROUP BY o.order_id, order_month, o.order_type
)
SELECT
  order_month,
  COUNT(order_id) AS total_orders,
  ROUND(SUM(order_amount), 2) AS total_revenue,
  ROUND(AVG(order_amount), 2) AS avg_order_value
FROM order_totals
GROUP BY order_month
ORDER BY order_month;

-- 2. 依訂單類型 (order_type) 交叉歸因分析
SELECT
  order_month,
  order_type,
  COUNT(order_id) AS total_orders,
  ROUND(SUM(order_amount), 2) AS total_revenue,
  ROUND(AVG(order_amount), 2) AS avg_order_value
FROM order_totals
GROUP BY order_month, order_type
ORDER BY order_month, order_type;