DWHモデリング

月商3億円のECサイト。BIツール（Tableau）のダッシュボードが47個乱立し、同じ「月次売上」でも数値が3通り存在していた。原因は、各アナリストがソーステーブルから独自にSQLを書いており、定義がバラバラだったこと。

スタースキーマで再設計:

ファクト: fact_order_lines（粒度: 注文×商品×行）

• order_id, product_id, customer_id, date_id, channel_id
• quantity, unit_price, discount_amount, revenue, tax

ディメンション:

• dim_customers: customer_id, name, segment, prefecture, registration_date（SCD Type 2で履歴管理）
• dim_products: product_id, name, category_l1, category_l2, brand, supplier
• dim_dates: date_id, date, day_of_week, month, quarter, year, is_holiday
• dim_channels: channel_id, channel_name, channel_category

dbtで全変換を管理し、テスト210件を組み込んだ。移行後、「月次売上」の定義が1つに統一され、ダッシュボードを47個→15個に整理。アナリストが新しい分析を始める際のSQL作成時間が平均3時間→30分に短縮された。

BtoB SaaS企業（ARR 12億円、顧客数800社）で、MRR・チャーンレート・NDR（Net Dollar Retention）の計算がスプレッドシートベースだった。月初に経理が手動で集計しており、数値の確定に5営業日かかっていた。

サブスクリプション特有のモデリング:

ファクト: fact_subscription_events（粒度: 契約×イベント）

• subscription_id, customer_id, date_id, event_type（new / expansion / contraction / churn / reactivation）
• mrr_change, arr_change

このイベント粒度のファクトから、日次のfact_subscription_snapshotを自動生成:

• subscription_id, customer_id, date_id, status, current_mrr

ディメンション:

• dim_customers: 企業規模・業種・契約開始日・CSM担当者
• dim_plans: プラン名・価格帯・機能セット
• dim_dates: 標準日付ディメンション

成果: MRR・チャーンレート・NDRが日次で自動計算され、確定が月初1日目に。経理の集計工数は月40時間→0時間。さらに、イベント粒度のファクトがあることで「チャーンの3か月前から利用頻度が低下している」というパターンを発見し、CS施策に活用できた。

1日5万件の配送を行う物流企業。配送遅延の原因分析に毎回2日かかっていた。基幹システム・GPS追跡システム・顧客管理システムの3つのソースからデータを手動で突合していたためだ。

段階的なモデリングアプローチ:

Phase 1（4週間）: 配送ファクトのみ構築

• fact_deliveries: 粒度=配送1件、delivery_id / date_id / driver_id / route_id / origin_id / destination_id / planned_time / actual_time / delay_minutes / status

Phase 2（3週間）: ディメンション拡充

• dim_drivers: ドライバー属性・所属拠点・免許種別
• dim_routes: ルート属性・距離・想定所要時間
• dim_locations: 拠点/配送先の住所・地域・種別

Phase 3（2週間）: 派生ファクト追加

• fact_delivery_daily_summary: 日次×拠点×ルート粒度の集約テーブル
• fact_delay_incidents: 遅延イベントに特化したファクト（遅延理由コード付き）

成果: 遅延原因の分析が2日→15分に短縮。「特定ルートの火曜午後に遅延が集中」というパターンを発見し、配車を最適化した結果、遅延率が12%→7.5%に改善。段階構築により、Phase 1の2か月後から分析チームが活用を開始でき、完成を待たずに価値を出せた。