xDev Asia

Data warehouse architecture for Fashion POD analytics, dashboards for sales, designer, production, trend analytics, niche research tools and BI stack.

Fashion Design & Print-on-Demand System Architecture — From Domain Analysis to Production

Part 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 for 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	Key KPIs
Sales	GMV, AOV, conversions, 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 according to supplier
Defect rate by product type
Return reason distribution
On-time delivery rate by 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	For example
Freshness	actual_orders is no more than 30 minutes late
Uniqueness	order_id unique in mart table
Completeness	channel_key is not null
Consistency	gross >= tax + shipping

8. Summary

Star schema suitable for operational and financial dashboards
Sales + production + trend are the 3 core dashboard clusters of POD
Funnel analytics Helps optimize conversion step by step
Data quality checks must be automated with the pipeline

Lesson 20: Analytics Dashboard — Sales, Trend & Niche Research