[이제와서 시작하는 Metabase 마스터하기 #16] 실전 프로젝트 - 이커머스 분석 대시보드

게시 2025/10/24

By YonYonWare

21 분읽는 시간

프로젝트 개요

이 최종 포스트에서는 지금까지 배운 모든 것을 활용하여 실전 이커머스 분석 시스템을 구축합니다.

프로젝트 목표

비즈니스 요구사항:
  - 일일 매출 및 주문 추적
  - 고객 세그먼트 분석
  - 제품 성과 모니터링
  - 마케팅 ROI 측정
  - 재고 최적화
  - Executive 리포트

데이터 스키마

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : included_in
    PRODUCTS }o--|| CATEGORIES : belongs_to
    ORDERS }o--|| CAMPAIGNS : attributed_to

    CUSTOMERS {
        int id PK
        string email
        string name
        date signup_date
        string state
        string tier
    }

    ORDERS {
        int id PK
        int customer_id FK
        datetime created_at
        decimal total
        string status
        int campaign_id FK
    }

    ORDER_ITEMS {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal price
    }

    PRODUCTS {
        int id PK
        string name
        int category_id FK
        decimal price
        int stock_quantity
    }

    CATEGORIES {
        int id PK
        string name
    }

    CAMPAIGNS {
        int id PK
        string name
        string channel
        decimal budget
    }

Phase 1: 데이터 모델링

Models 생성

Model 1: Clean Orders

  
-- Model: "Clean Orders"
-- Purpose: 완료된 주문, 필요한 조인 미리 처리

SELECT
  o.id as order_id,
  o.created_at as order_date,
  o.total as order_total,
  o.status,

  -- Customer info
  c.id as customer_id,
  c.name as customer_name,
  c.email as customer_email,
  c.state as customer_state,
  c.tier as customer_tier,
  c.signup_date as customer_signup_date,

  -- Marketing attribution
  camp.id as campaign_id,
  camp.name as campaign_name,
  camp.channel as marketing_channel,

  -- Calculated fields
  DATE_TRUNC('month', o.created_at) as order_month,
  DATE_TRUNC('week', o.created_at) as order_week,
  DATE(o.created_at) as order_day,

  -- Customer segmentation
  CASE
    WHEN c.tier = 'VIP' THEN 'VIP'
    WHEN o.total >= 200 THEN 'High Value'
    WHEN o.total >= 100 THEN 'Medium Value'
    ELSE 'Low Value'
  END as order_segment

FROM orders o
JOIN customers c ON o.customer_id = c.id
LEFT JOIN campaigns camp ON o.campaign_id = camp.id
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01'

Model 2: Product Performance

  
-- Model: "Product Performance"
-- Purpose: 제품별 판매 성과 집계

SELECT
  p.id as product_id,
  p.name as product_name,
  cat.name as category_name,
  p.price as current_price,
  p.stock_quantity,

  -- Sales metrics
  COUNT(DISTINCT oi.order_id) as order_count,
  SUM(oi.quantity) as units_sold,
  SUM(oi.quantity * oi.price) as total_revenue,
  AVG(oi.price) as avg_selling_price,

  -- Inventory metrics
  p.stock_quantity / NULLIF(AVG(daily_sales.daily_units), 0) as days_of_stock,

  -- Last sold
  MAX(o.created_at) as last_sold_date

FROM products p
LEFT JOIN categories cat ON p.category_id = cat.id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id AND o.status = 'completed'
LEFT JOIN LATERAL (
  SELECT
    oi2.product_id,
    AVG(daily.units) as daily_units
  FROM (
    SELECT
      oi3.product_id,
      DATE(o3.created_at) as sale_date,
      SUM(oi3.quantity) as units
    FROM order_items oi3
    JOIN orders o3 ON oi3.order_id = o3.id
    WHERE o3.status = 'completed'
      AND o3.created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY oi3.product_id, DATE(o3.created_at)
  ) daily
  WHERE daily.product_id = p.id
  GROUP BY daily.product_id
) daily_sales ON true
WHERE p.is_active = true
GROUP BY p.id, p.name, cat.name, p.price, p.stock_quantity

Model 3: Customer 360

  
-- Model: "Customer 360"
-- Purpose: 고객별 통합 프로필

SELECT
  c.id as customer_id,
  c.email,
  c.name,
  c.state,
  c.tier,
  c.signup_date,

  -- Order history
  COUNT(o.id) as lifetime_orders,
  SUM(o.total) as lifetime_value,
  AVG(o.total) as avg_order_value,
  MAX(o.created_at) as last_order_date,
  MIN(o.created_at) as first_order_date,

  -- Recency
  DATEDIFF('day', MAX(o.created_at), CURRENT_DATE) as days_since_last_order,

  -- Frequency (90 days)
  SUM(CASE WHEN o.created_at >= CURRENT_DATE - INTERVAL '90 days' THEN 1 ELSE 0 END) as orders_90d,

  -- Segmentation
  CASE
    WHEN SUM(o.total) >= 1000 THEN 'VIP'
    WHEN SUM(o.total) >= 500 THEN 'Regular'
    ELSE 'Casual'
  END as segment,

  -- Lifecycle stage
  CASE
    WHEN DATEDIFF('day', MAX(o.created_at), CURRENT_DATE) > 180 THEN 'Churned'
    WHEN DATEDIFF('day', MAX(o.created_at), CURRENT_DATE) > 90 THEN 'At Risk'
    WHEN DATEDIFF('day', c.signup_date, CURRENT_DATE) <= 30 THEN 'New'
    ELSE 'Active'
  END as lifecycle_stage

FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed'
GROUP BY c.id, c.email, c.name, c.state, c.tier, c.signup_date

Metrics 정의

Metric: Total Revenue
  Model: Clean Orders
  Definition: SUM(order_total)
  Description: 완료된 주문의 총 매출

Metric: Average Order Value (AOV)
  Model: Clean Orders
  Definition: AVG(order_total)
  Description: 주문당 평균 금액

Metric: Active Customers
  Model: Customer 360
  Definition: COUNT(DISTINCT customer_id) WHERE lifecycle_stage = 'Active'
  Description: 활성 고객 수 (90일 내 구매)

Metric: Customer Lifetime Value (LTV)
  Model: Customer 360
  Definition: AVG(lifetime_value)
  Description: 고객당 평균 총 구매액

Metric: Conversion Rate
  Custom calculation
  Definition: (Orders / Sessions) * 100
  Description: 방문 대비 구매 전환율

Phase 2: Executive Dashboard

Dashboard: “Executive Summary”

Row 1: 핵심 KPI (4 Number Cards)

┌──────────────┬──────────────┬──────────────┬──────────────┐
│ Today Revenue│ Today Orders │ Active Cust. │  AOV         │
│   $12,345    │     245      │    1,250     │    $89.50    │
│   +15% ↑     │    +8% ↑     │   +3% ↑      │   +2% ↑      │
└──────────────┴──────────────┴──────────────┴──────────────┘

Question 1: Today Revenue
  Data: Clean Orders
  Filter: order_date = Today
  Metric: SUM(order_total)
  Compare to: Yesterday
  Format: Currency, compact ($12.3K)

Question 2: Today Orders
  Data: Clean Orders
  Filter: order_date = Today
  Metric: COUNT(order_id)
  Goal: 200
  Color: Green if >= goal

Question 3: Active Customers
  Data: Customer 360
  Filter: lifecycle_stage = 'Active'
  Metric: COUNT(customer_id)
  Trend: Last 30 days

Question 4: AOV
  Data: Clean Orders
  Filter: order_date = Today
  Metric: AVG(order_total)
  Format: Currency

Row 2: Revenue Trends (2 Line Charts)

┌─────────────────────────────┬─────────────────────────────┐
│  30-Day Revenue Trend       │  Revenue by Channel         │
└─────────────────────────────┴─────────────────────────────┘

Chart 1: Revenue Trend
  Data: Clean Orders
  X-axis: order_day (Last 30 days)
  Y-axis: SUM(order_total)
  Series:
    - Actual revenue (solid line)
    - 7-day moving average (dashed)
    - Goal line ($50K)
  Visualization: Line + Area

Chart 2: Revenue by Channel
  Data: Clean Orders
  X-axis: order_week (Last 12 weeks)
  Y-axis: SUM(order_total)
  Group by: marketing_channel
  Visualization: Stacked area
  Colors: by channel

Row 3: Customer Insights (2 Charts)

┌─────────────────────────────┬─────────────────────────────┐
│  Customer Segments          │  Lifecycle Distribution     │
└─────────────────────────────┴─────────────────────────────┘

Chart 1: Customer Segments
  Data: Customer 360
  Metrics:
    - COUNT(customer_id) as Customers
    - SUM(lifetime_value) as Revenue
    - AVG(lifetime_value) as Avg LTV
  Group by: segment
  Visualization: Table with conditional formatting
    - VIP: Green highlight
    - Regular: Blue
    - Casual: Gray

Chart 2: Lifecycle Distribution
  Data: Customer 360
  Metric: COUNT(customer_id)
  Group by: lifecycle_stage
  Visualization: Funnel
  Stages:
    - New
    - Active
    - At Risk
    - Churned

Row 4: Top Performers (2 Tables)

┌─────────────────────────────┬─────────────────────────────┐
│  Top 10 Products            │  Top 10 Customers           │
└─────────────────────────────┴─────────────────────────────┘

Table 1: Top Products
  Data: Product Performance
  Columns:
    - product_name
    - category_name
    - units_sold
    - total_revenue
    - days_of_stock
  Sort: total_revenue DESC
  Limit: 10
  Conditional formatting:
    - days_of_stock < 7: Red (reorder soon)

Table 2: Top Customers
  Data: Customer 360
  Columns:
    - name
    - lifetime_value
    - lifetime_orders
    - last_order_date
    - segment
  Sort: lifetime_value DESC
  Limit: 10

Filters (Dashboard-level):

Filter 1: Date Range
  Type: Date
  Default: Last 30 days
  Connected to: All time-based questions

Filter 2: Marketing Channel
  Type: Category
  Options: Auto from data
  Default: All
  Optional: Yes

Filter 3: Customer Segment
  Type: Category
  Options: VIP, Regular, Casual
  Default: All
  Optional: Yes

Phase 3: Operational Dashboards

Dashboard: “Sales Operations”

Real-time 주문 모니터링

  
-- Question: Today's Hourly Performance
SELECT
  DATE_TRUNC('hour', order_date) as hour,
  COUNT(order_id) as orders,
  SUM(order_total) as revenue,
  AVG(order_total) as aov
FROM "Clean Orders"
WHERE order_date >= CURRENT_DATE
GROUP BY DATE_TRUNC('hour', order_date)
ORDER BY hour DESC

Visualization: Line chart
  X: hour
  Y1: revenue (primary axis)
  Y2: orders (secondary axis)
  Refresh: Every 5 minutes

주문 처리 상태

  
-- Question: Orders by Status
SELECT
  status,
  COUNT(*) as order_count,
  SUM(order_total) as total_value
FROM orders
WHERE created_at >= CURRENT_DATE
GROUP BY status
ORDER BY
  CASE status
    WHEN 'pending' THEN 1
    WHEN 'processing' THEN 2
    WHEN 'shipped' THEN 3
    WHEN 'completed' THEN 4
    WHEN 'cancelled' THEN 5
  END

Visualization: Progress bar
  Color coding:
    - pending: Yellow
    - processing: Blue
    - shipped: Light green
    - completed: Green
    - cancelled: Red

Dashboard: “Product Analytics”

재고 관리

  
-- Question: Low Stock Alert
SELECT
  product_name,
  category_name,
  stock_quantity,
  days_of_stock,
  units_sold as units_last_30d,
  CEIL(units_sold / 30.0 * 14) as recommended_order_quantity
FROM "Product Performance"
WHERE days_of_stock < 14
  OR stock_quantity < 10
ORDER BY days_of_stock ASC

Visualization: Table
  Highlight: days_of_stock < 7 (Red)

Alert:
  Condition: COUNT(*) > 0
  Recipients: inventory@company.com
  Frequency: Daily 9 AM

Product Performance Matrix

  
-- Question: Product BCG Matrix
SELECT
  product_name,
  total_revenue as sales,
  (total_revenue - LAG(total_revenue) OVER (PARTITION BY product_id ORDER BY analysis_month)) / LAG(total_revenue) OVER (PARTITION BY product_id ORDER BY analysis_month) * 100 as growth_rate,
  CASE
    WHEN sales_rank <= total_products * 0.2 AND growth_rate >= 20 THEN 'Star'
    WHEN sales_rank <= total_products * 0.2 AND growth_rate < 20 THEN 'Cash Cow'
    WHEN sales_rank > total_products * 0.2 AND growth_rate >= 20 THEN 'Question Mark'
    ELSE 'Dog'
  END as bcg_category
FROM (
  SELECT
    product_name,
    total_revenue,
    ROW_NUMBER() OVER (ORDER BY total_revenue DESC) as sales_rank,
    COUNT(*) OVER () as total_products
  FROM "Product Performance"
)

Visualization: Scatter plot
  X: growth_rate
  Y: total_revenue
  Color: bcg_category
  Size: units_sold

Phase 4: 마케팅 Analytics

Dashboard: “Marketing ROI”

Campaign Performance

  
-- Question: Campaign ROI
SELECT
  camp.name as campaign_name,
  camp.channel,
  camp.budget as spend,
  COUNT(DISTINCT o.id) as orders,
  SUM(o.total) as revenue,
  (SUM(o.total) - camp.budget) as profit,
  ((SUM(o.total) - camp.budget) / NULLIF(camp.budget, 0) * 100) as roi_percent,
  SUM(o.total) / NULLIF(COUNT(DISTINCT o.id), 0) as revenue_per_order,
  camp.budget / NULLIF(COUNT(DISTINCT o.customer_id), 0) as cac
FROM campaigns camp
LEFT JOIN orders o ON camp.id = o.campaign_id AND o.status = 'completed'
WHERE camp.start_date >= '2025-01-01'
GROUP BY camp.id, camp.name, camp.channel, camp.budget
ORDER BY roi_percent DESC

Visualization: Table
  Conditional formatting:
    - ROI > 100%: Green
    - ROI 0-100%: Yellow
    - ROI < 0: Red

Customer Acquisition Funnel

  
-- Question: Marketing Funnel
WITH funnel_stages AS (
  SELECT
    campaign_id,
    COUNT(DISTINCT CASE WHEN event = 'ad_click' THEN user_id END) as clicks,
    COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) as signups,
    COUNT(DISTINCT CASE WHEN event = 'first_purchase' THEN user_id END) as purchases
  FROM marketing_events
  WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY campaign_id
)
SELECT
  c.name as campaign,
  clicks,
  signups,
  purchases,
  (signups * 100.0 / NULLIF(clicks, 0)) as signup_rate,
  (purchases * 100.0 / NULLIF(signups, 0)) as purchase_rate,
  (purchases * 100.0 / NULLIF(clicks, 0)) as overall_conversion
FROM funnel_stages f
JOIN campaigns c ON f.campaign_id = c.id
ORDER BY overall_conversion DESC

Visualization: Funnel chart
  Stages: Clicks → Signups → Purchases
  Show conversion rates between stages

Phase 5: 자동화 및 알림

Subscriptions 설정

Subscription 1: Daily Executive Report
  Dashboard: "Executive Summary"
  Recipients: exec@company.com
  Schedule: Monday-Friday, 8:00 AM
  Format: PDF
  Filters: Last 7 days

Subscription 2: Weekly Sales Review
  Dashboard: "Sales Operations"
  Recipients: sales@company.com
  Schedule: Monday, 9:00 AM
  Format: PNG + CSV
  Slack: #sales-team

Subscription 3: Monthly Performance
  Dashboard: "Product Analytics"
  Recipients: product@company.com
  Schedule: 1st of month, 10:00 AM
  Format: PDF (detailed)

Alerts 설정

Alert 1: Revenue Drop
  Question: "Today Revenue vs Yesterday"
  Condition: Decreases by > 20%
  Check: Hourly (9 AM - 6 PM)
  Recipients: ops@company.com, Slack #urgent

Alert 2: Low Stock Critical
  Question: "Products with < 3 days stock"
  Condition: Has any results
  Check: Every 4 hours
  Recipients: inventory@company.com

Alert 3: High-Value Order
  Question: "Orders > $1000"
  Condition: New result появляется
  Check: Every 15 minutes
  Recipients: sales@company.com, Slack #vip-orders

Alert 4: Customer Churn Risk
  Question: "VIP customers not ordered in 60 days"
  Condition: Has any results
  Check: Weekly (Monday 9 AM)
  Recipients: crm@company.com

Phase 6: 성능 최적화

Materialized Views

  
-- Daily Sales Summary (Refresh nightly)
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
  order_day,
  COUNT(DISTINCT order_id) as orders,
  SUM(order_total) as revenue,
  COUNT(DISTINCT customer_id) as customers,
  AVG(order_total) as aov
FROM "Clean Orders"
GROUP BY order_day;

CREATE INDEX idx_daily_sales_date ON daily_sales_summary(order_day);

-- Refresh schedule (Cron: 2 AM daily)
REFRESH MATERIALIZED VIEW daily_sales_summary;

Caching Strategy

Hot Questions (5 min cache):
  - Today's revenue
  - Today's orders
  - Active customers now

Warm Questions (1 hour cache):
  - Last 30 days trends
  - Product performance
  - Customer segments

Cold Questions (24 hour cache):
  - Historical comparisons
  - Yearly trends
  - Customer lifetime value

Query Optimization

  
-- Before: Slow (15s)
SELECT
  p.name,
  COUNT(DISTINCT o.id) as orders
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
GROUP BY p.name

-- After: Fast (0.5s) - Use Model
SELECT
  product_name,
  order_count
FROM "Product Performance"
ORDER BY order_count DESC

Phase 7: 권한 및 보안

Groups 설정

Group: Executives
  Data: All databases (Limited - GUI only)
  Collections: All (View)
  Features: Public links disabled

Group: Sales Team
  Data: Clean Orders, Customer 360 (Unrestricted)
  Collections: Sales folders (Edit)
  Row-level: Own territory
    Filter: customer_state = 

Group: Marketing Team
  Data: Clean Orders (Limited)
  Collections: Marketing folders (Edit)
  Row-level: Own campaigns
    Filter: campaign_id IN ()

Group: Analysts
  Data: All (Unrestricted + SQL)
  Collections: All (Curate)
  Features: All enabled

Row-Level Security

  
-- Sales reps see only their customers
CREATE POLICY sales_rep_policy ON orders
FOR SELECT
USING (
  customer_id IN (
    SELECT customer_id FROM customer_assignments
    WHERE sales_rep_id = current_user_id()
  )
)

프로젝트 완성도 체크리스트

데이터 레이어

Models로 복잡한 조인 추상화
Metrics로 일관된 계산 정의
Segments로 재사용 가능한 필터
인덱스 최적화
Materialized views

대시보드

Executive dashboard (high-level KPIs)
Operational dashboards (daily operations)
Analytical dashboards (deep insights)
일관된 디자인 및 레이아웃
모바일 친화적

자동화

일일 리포트 구독
주간 요약 구독
실시간 알림
예외 상황 알림

성능

쿼리 최적화 (<3초)
캐싱 전략
Dashboard 로딩 최적화
슬로우 쿼리 모니터링

보안

Role-based access control
Row-level security
Data masking (PII)
Audit logging

운영

자동 백업 (일일)
모니터링 대시보드
Alert 설정
문서화

Best Practices 총정리

1. 데이터 모델링

✅ Do:
  - Models로 복잡한 로직 캡슐화
  - 명확한 Metrics 정의
  - 비즈니스 용어 사용
  - 문서화

❌ Don't:
  - 중복 로직
  - 일관성 없는 계산
  - 기술 용어만 사용
  - 문서 누락

2. 대시보드 디자인

✅ Do:
  - 명확한 목적
  - 시각적 계층 구조
  - 5-second rule
  - 일관된 색상/폰트

❌ Don't:
  - 차트 과다 (>15개)
  - 정보 과부하
  - 불명확한 레이블
  - 일관성 없는 스타일

3. 성능

✅ Do:
  - 인덱스 활용
  - 캐싱 전략
  - 쿼리 최적화
  - 정기 모니터링

❌ Don't:
  - SELECT *
  - 불필요한 JOIN
  - 캐싱 미사용
  - 성능 무시

4. 보안

✅ Do:
  - 최소 권한 원칙
  - Row-level security
  - 정기 감사
  - PII 마스킹

❌ Don't:
  - 과도한 권한
  - 민감 데이터 노출
  - 감사 로그 무시
  - 비밀번호 공유

5. 운영

✅ Do:
  - 정기 백업
  - 모니터링
  - 문서화
  - 교육

❌ Don't:
  - 백업 누락
  - 모니터링 부재
  - 문서화 없음
  - 팀 교육 소홀

추가 학습 리소스

공식 문서

커뮤니티

도서 추천

“Data Visualization: A Practical Introduction” by Kieran Healy
“Storytelling with Data” by Cole Nussbaumer Knaflic
“The Data Warehouse Toolkit” by Ralph Kimball

SQL 학습

다음 단계

이제 Metabase를 마스터했습니다! 다음 여정:

실무 적용: 회사/프로젝트에 적용
심화 학습: DBT, Airflow 등 데이터 파이프라인
커뮤니티 기여: 오픈소스 기여, 블로그 작성
최신 기능: Metabase 업데이트 팔로우
고급 주제: ML/AI 통합, 예측 분석

시리즈 마무리

Metabase 마스터하기 시리즈를 완료하셨습니다!

배운 내용 요약

#1  소개 및 설치
#2  데이터베이스 연결
#3  Questions 만들기
#4  대시보드 구축
#5  필터와 파라미터
#6  SQL 고급 활용
#7  데이터 모델링
#8  자동화와 알림
#9  권한과 보안
#10 임베딩과 공유
#11 성능 최적화
#12 멀티 데이터소스
#13 커스터마이징
#14 운영과 모니터링
#15 실전 프로젝트 ✓

핵심 역량

이제 다음을 할 수 있습니다:

✅ Metabase 설치 및 설정
✅ 복잡한 데이터 모델 설계
✅ 효과적인 대시보드 구축
✅ 고급 SQL 쿼리 작성
✅ 자동화 및 알림 시스템
✅ 권한 및 보안 관리
✅ 성능 최적화
✅ 운영 및 모니터링
✅ 실전 프로젝트 완성

마지막 조언

"데이터는 도구가 아니라 문화입니다."

- 지속적으로 학습하세요
- 커뮤니티에 기여하세요
- 실전 경험을 쌓으세요
- 비즈니스 가치에 집중하세요
- 사용자 피드백을 경청하세요

Happy Data Analyzing! 🎉📊

이 시리즈가 도움이 되셨나요? 피드백과 질문은 언제나 환영합니다! excerpt_separator: “” —