xDev Asia

Data warehouse architecture cho Fashion POD analytics, dashboards cho sales, designer, production, trend analytics, niche research tools và BI stack.

Kiến trúc Hệ thống Fashion Design & Print-on-Demand — Từ Domain Analysis đến Production

Phần 6: Data Platform & Analytics

xdev.asia

1. Data Warehouse Architecture

Sources (OLTP + Events)
   -> ELT/CDC Pipeline
   -> Staging Layer
   -> Data Models (dbt)
   -> Data Warehouse (ClickHouse/BigQuery)
   -> BI Dashboards (Metabase/Superset)

2. Star Schema cho POD

-- Fact table
fact_orders(
  order_id,
  date_key,
  shop_key,
  product_key,
  channel_key,
  supplier_key,
  gross_revenue_cents,
  shipping_cents,
  tax_cents,
  discount_cents,
  quantity,
  status
)

-- Dimensions
dim_date(date_key, day, week, month, quarter, year)
dim_shop(shop_key, shop_id, segment, country)
dim_product(product_key, product_id, category, style, tags)
dim_channel(channel_key, channel_name)
dim_supplier(supplier_key, supplier_name, region)

3. Core Dashboards

Dashboard	KPI chính
Sales	GMV, AOV, conversion, CAC, ROAS
Designer	Top designs, royalty earned, repeat rate
Production	Lead time, defect rate, SLA breach
Channel	Revenue by Shopify/Etsy/Amazon/TikTok
Trend/Niche	Top trending tags, growth velocity, competition

4. Sales Funnel Analytics

Impression -> Product View -> Add to Cart -> Checkout Start -> Payment Success

SELECT
  date,
  SUM(impressions) AS impressions,
  SUM(product_views) AS views,
  SUM(add_to_cart) AS atc,
  SUM(checkout_start) AS checkout,
  SUM(purchases) AS purchases,
  ROUND(SUM(purchases)::numeric / NULLIF(SUM(product_views),0), 4) AS view_to_buy
FROM mart_funnel_daily
GROUP BY date
ORDER BY date DESC;

5. Production Metrics

P50/P95 production time theo supplier
Defect rate theo product type
Return reason distribution
On-time delivery rate theo region

6. Trend & Niche Analytics

interface TrendInsight {
  tag: string;
  weekGrowthPct: number;
  monthGrowthPct: number;
  competitionScore: number;
  opportunityScore: number;
}

function opportunity(trend: number, competition: number) {
  return 0.7 * trend - 0.3 * competition;
}

7. Data Quality & Governance

Rule	Ví dụ
Freshness	fact_orders trễ không quá 30 phút
Uniqueness	order_id unique trong mart table
Completeness	channel_key không null
Consistency	gross >= tax + shipping

8. Tổng kết

Star schema phù hợp cho dashboard vận hành và tài chính
Sales + production + trend là 3 cụm dashboard cốt lõi của POD
Funnel analytics giúp tối ưu conversion theo từng bước
Data quality checks phải được tự động hóa cùng pipeline

Bài 20: Analytics Dashboard — Sales, Trend & Niche Research