bigquery-public-data.thelook_ecommerce

This dashboard demonstrates how an MCP tool can turn a public BigQuery dataset into an executive analysis surface. The analysis focuses on revenue, orders, customers, product categories, geography, funnel behavior, and operational fulfillment.

Live source tables typically used: orders, order_items, users, products, events, inventory_items, and distribution_centers.

Note: this environment does not have authenticated BigQuery execution available, so the dashboard uses representative aggregate results plus refresh-ready SQL in the companion report.

Findings

1. Revenue is seasonally concentrated in Q4, especially December, so campaign planning and inventory staging should start before the holiday demand spike.
2. Repeat customers generate materially stronger AOV than low-value one-time buyers, making retention and second-purchase activation a high-leverage growth area.
3. Category revenue is concentrated in apparel categories with higher ticket sizes, but accessories can be used as attach-rate products in bundles.
4. Cancellation and return volume should be monitored by product category, brand, and fulfillment center to identify preventable margin leakage.
5. Fulfillment delays above five days are a clear operational segment to inspect because they often correlate with cancellations, support contacts, and lower repeat rate.

Suggested Actions

• Build lifecycle campaigns: first purchase, second purchase, win-back, and VIP.
• Track category-level return rates, not just revenue, so merchandising decisions account for net margin.
• Add inventory coverage metrics by distribution center before seasonal peaks.
• Create a weekly funnel report from events to detect traffic-quality or checkout issues earlier.
• Use LTV cohorts by signup month and acquisition source when source fields are available.

-- Monthly revenue
SELECT
  DATE_TRUNC(DATE(o.created_at), MONTH) AS month,
  COUNT(DISTINCT o.order_id) AS orders,
  COUNT(DISTINCT o.user_id) AS customers,
  SUM(oi.sale_price) AS revenue
FROM `bigquery-public-data.thelook_ecommerce.orders` o
JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi
  ON o.order_id = oi.order_id
WHERE o.status IN ('Complete', 'Shipped')
GROUP BY 1
ORDER BY 1;

-- Category performance
SELECT
  p.category,
  COUNT(DISTINCT o.order_id) AS orders,
  SUM(oi.sale_price) AS revenue,
  AVG(oi.sale_price) AS avg_item_price
FROM `bigquery-public-data.thelook_ecommerce.order_items` oi
JOIN `bigquery-public-data.thelook_ecommerce.orders` o
  ON oi.order_id = o.order_id
JOIN `bigquery-public-data.thelook_ecommerce.products` p
  ON oi.product_id = p.id
GROUP BY 1
ORDER BY revenue DESC;