marts層を作る ― 全連載が使うSOLNAスキーマの基本設定

下流の全分析が前提にする「使えるマート」は、どう設計すれば崩れないのか。答えは、ディメンションとファクトで事業のことばに整え、結合キーと粒度を固定すること——これが全連載の基本設定になります。この記事では、過去の受託案件をもとに設計した架空のD2C「SOLNA」を題材に、marts層を実際に組み、真のCAC/LTVと接合率の素地を作るところまでを示します。

前提の確認だけ先に。計測設計の連載（なぜ計測は静かに壊れるのか ― 計測設計という土台）は「データが生まれる前」（収集・同意・識別・送信）、本連載は「入った後」（ローデータ → マート）を扱います。接合率の設計は計測設計の連載、本連載はそれをマートに保持する側です。staging（第2回）で正規化し、intermediate（第4回）で結合・集計を畳んできました。本回はその上に、下流が直接触る最終層を載せます。

martsの責務（事業のことばで表現する）

stagingとintermediateはエンジニアのことば（型・結合・粒度）で書かれます。これに対しmartsは、事業のことばで書く層です。「顧客」「注文」「契約」——BI・Reverse ETL・分析ノートブックが直接触るのは、この層だけにします。第0回で見た「秘伝のタレSQL」は、ここに一度だけ畳み込み、下流は二度とローデータに触らせません。

martsの責務は三つに絞れます。第一に、事業で通じる名前を持つこと。第二に、粒度（grain＝1行が何を表すか）を固定すること。第三に、カラム名と型を安定した契約として下流に提供することです。下流はこの契約だけに依存し、ローデータの都合（GA4のスキーマ変更、広告媒体の仕様変更）からは切り離されます。

ディメンションとファクト（Kimballの考え方）

ここでバックボーンにするのは、新刊でも流行でもない数十年来のコア——Kimballのディメンショナルモデリングです。考え方は単純で、データを二種類に分けます。ファクトは「出来事・測れる量」（注文・セッション・出稿）、ディメンションは「文脈・誰／何」（顧客・商品・チャネル）です。そして設計の最初にグレインを宣言します。「この表は注文1行か、顧客1行か」を決めてから列を足す、という順序です。

この語彙は実装現場のいたるところに埋まっています。既製のdbtパッケージ（たとえばFivetranのもの）が当然のように使う「サロゲートキー」「grain」「SCD（履歴の持ち方・第6回）」は、ほぼKimballのDWHツールキットが出どころです。第0回で「整える層の系譜」として触れたとおり、私たちは新しい型を発明しているのではなく、古典の語彙を継いでいます。

スター・スキーマか、ワイドテーブルか（Kimballと現代の折衷）

ここに、古典と現代がぶつかる論点があります。厳密なスター・スキーマは、dim_ と fct_ を分けて持ち、結合で組み立て、共通の次元（conformed dimension）を共有します。正規化寄りで、整合性が高い設計です。

一方、現代の列指向DWH（BigQuery・Snowflake）では、結合よりスキャンのほうが安く、分析者は「1テーブルで完結」を好みます。そこで最終マートをワイド（OBT＝One Big Table寄り）に畳む流儀が広がりました。Fivetran等の完成マートは後者寄りで、ここはKimballから一部離れます。

これは「ワイドテーブル＝Kimball否定」ではありません。実務解は折衷です。内部はdim/fctの規律（グレイン・サロゲートキー・履歴）を保ちつつ、下流に公開する最終マートだけ事業のことばのワイドに畳む。SOLNAでも、公開するマート名は事業のことば（customers・orders・sessions …）にし、内部の int_ や dim_/fct_ は規律として保ちます。判断材料は単純で、下流がBI中心ならワイド寄りに、再利用される共通ディメンションが多いなら分けて持つ、で足ります。

SOLNAスキーマを組む

ここからが本連載の基本設定です。7つのマートを定義します。カラム名と粒度は、予測（Kaggle）・因果・活性化の各連載と完全に一致させます。齟齬が出たら本回を真とし、各連載側を更新します。

customers / orders / subscriptions（顧客・注文・契約）

customers は顧客ディメンション（粒度＝顧客1行）、orders は注文ファクト（粒度＝注文1行）、subscriptions は契約ファクト（粒度＝契約1行）です。初回／リピート判定や最終接点、顧客単位のLTV集計といった「重い計算」は、すべてintermediateで解決済みにしておき、marts側は組み立てに徹します。

-- models/marts/orders.sql  （ファクト：粒度 = 注文1行）
{{ config(materialized='table') }}
 
with stg_orders as (
    select * from {{ ref('stg_shopify__orders') }}
),
 
-- 初回/リピート判定・最終接点は intermediate で解決済みにしておく
enriched as (
    select * from {{ ref('int_orders__classified') }}
)
 
select
    o.order_id,
    o.customer_id,
    o.order_at,
    e.order_type,            -- 'first' | 'repeat' | 'subscription'
    o.amount,
    o.product_id,
    e.last_touch_channel
from stg_orders as o
left join enriched as e using (order_id)
 
 
-- models/marts/customers.sql  （ディメンション：粒度 = 顧客1行）
{{ config(materialized='table') }}
 
with crm as (
    select * from {{ ref('stg_crm__customers') }}
),
 
-- 顧客単位の集計（初回購入・LTV・解約）は中間モデルで一元化
agg as (
    select * from {{ ref('int_customers__order_summary') }}
)
 
select
    c.customer_id,
    agg.first_order_at,
    c.acquisition_channel,
    c.region,
    c.age_band,
    c.membership_tier,
    agg.ltv_to_date,
    agg.churned_flag,
    agg.churn_at
from crm as c
left join agg using (customer_id)

subscriptions は同じ要領で、粒度＝契約1行（subscription_id, customer_id, started_at, status, plan, cancelled_at, n_cycles）。status は 'active' / 'paused' / 'cancelled' の3値に正規化しておきます。会員ランクや購読状態の履歴（いつ gold に上がったか、いつ解約したか）は次回のスナップショットで扱います。本回の customers.membership_tier はあくまで「現在の状態」です。

sessions / ad_spend（行動・出稿）

sessions は行動ファクト（粒度＝セッション1行）。ここに、本連載で最も重要な結合点が現れます。匿名の行動（ga_client_id）と会員（customer_id）の名寄せです。名寄せロジックは中間モデルに一元化し、martsは結果を受け取るだけにします。

-- models/marts/sessions.sql  （ファクト：粒度 = セッション1行）
{{ config(materialized='table') }}
 
with stg as (
    select * from {{ ref('stg_ga4__sessions') }}
),
 
-- ga_client_id ↔ customer_id の名寄せ（接合点）は中間で一元化
spine as (
    select * from {{ ref('int_identity__client_to_customer') }}
)
 
select
    -- サロゲートキー：業務キーが複合でも一意キーを一本張る（Kimball）
    {{ dbt_utils.generate_surrogate_key(['s.session_id']) }} as session_sk,
    s.session_id,
    s.ga_client_id,
    spine.customer_id,       -- 紐づかなければ NULL ＝ 接合できなかった行
    s.session_start,
    s.channel,
    s.gclid,
    s.fbclid,
    s.landing_page,
    s.pageviews,
    s.product_views,
    s.engaged
from stg as s
left join spine using (ga_client_id)

ad_spend は出稿ファクトで、粒度は日×channel×region（date_day, channel, campaign, region, spend, impressions, clicks）。この粒度が、後述の真のCACを成立させる鍵になります。広告媒体ごとにバラバラだった channel 表記は、stagingで共通値に正規化済みである前提です（第2回）。

messages / reviews（配信・テキスト）

messages はCRM配信ファクト（粒度＝配信1行）。holdout_flag をここで保持することが効きます。これは活性化連載・因果連載でランダム割付（uplift）の処置/対照を切る根拠になる列で、配信の意思決定が下流の因果推論を可能にする、という連鎖の起点です。reviews はレビューファクト（粒度＝レビュー1行・rating と text を持つ）で、レコメンドやテキストNLPの入力になります。

粒度とテストは、schema.yml で契約として宣言します。粒度の宣言とは、結局のところ主キーに unique と not_null を置くことです。

# models/marts/_marts.yml  （契約：粒度・テスト・ドキュメント）
models:
  - name: customers
    description: "顧客ディメンション。粒度＝顧客1行。下流の基本設定。"
    columns:
      - name: customer_id
        description: "会員ID（業務キー）。"
        tests: [unique, not_null]           # この2つで「粒度＝顧客1行」を保証
      - name: membership_tier
        tests:
          - accepted_values:
              values: ['bronze', 'silver', 'gold']
 
  - name: orders
    description: "注文ファクト。粒度＝注文1行。"
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:                   # 参照整合：注文の顧客は必ず customers に居る
              to: ref('customers')
              field: customer_id
      - name: order_type
        tests:
          - accepted_values:
              values: ['first', 'repeat', 'subscription']

結合キーの置き場と真のCAC/LTVの素地

marts層が下流に効くのは、二つの素地をここに据えるからです。

一つ目は結合キーの置き場。sessions.customer_id を nullable で持つことで、「匿名行動のうち、どれだけ会員まで辿れたか」がマートそのものに刻まれます。紐づかなかった行は NULL として残るので、customer_id が埋まった割合がそのまま接合率（spine coverage）になります。接合率を「常時監視」するのは第3回のテストの仕事、本回の仕事は接合率を計算できる形でmartsに保持することです。設計は計測①、恒常化は本連載、という分担がここで閉じます。

二つ目は真のCAC/LTVの素地。ad_spend を日×channel×regionで持ち、orders に last_touch_channel と region を持たせておくと、チャネル別に「出稿コスト ÷ 実際の成果」を組めます。ここで言う成果は、媒体が報告するCVではなく orders の実購入です。媒体報告CV ≠ 実購入という乖離を、同じ粒度で並べられる——それがマートに据える価値です。LTVは customers.ltv_to_date に集計済みなので、CACと回収期間を突き合わせる土台もそろいます。

本業の受託でこの素地を作ったときの一次データを、レンジで記しておきます。経路により接合率は概ね40〜70%のレンジに散らばり、媒体報告ベースのCACと真のCAC（実成約ベース）には1.5〜3倍の開きが出ました。ただしこれはN=1の観測で、交絡や経路ごとの母集団差を統制したものではありません。「接合率が低い経路は真のCACが必ず悪い」とまでは言えず、相関と因果は分けて読む必要があります。マートが据えるのは判断材料であって、結論ではない、という線引きです。

このマートが下流で何を可能にするか

基本設定としてのmartsが固定されると、各連載は「customers と orders がある前提」でコードを書けるようになります。

予測（Kaggle連載）は、sessions + customers + orders から初回CVや解約を予測します。リーク防止は粒度と order_at の扱いに依存するので、ここでの粒度宣言がそのまま予測の健全性を支えます。因果（因果連載）は、orders（region）× ad_spend（region）でDiD、messages.holdout_flag でuplift、customers.membership_tier の閾値でRDD——いずれも本回で保持した列が処置・対照の切り口になります。活性化の連載は、customers や解約スコアをReverse ETLでそのままオーディエンスに同期します（定義＝マート）。そして記述・予測編で扱った真のCAC・接合率・コホートは、本回のマートの上で初めて再現可能になります。

逆に言えば、下流がこれだけ素直に書けるのは、本回がスキーマを基本設定として固定したからです。マートがぶれれば、その上のすべてがぶれます。整える層が下流の上限を決める、という第0回の宣言は、この回で具体化されます。

章末チェック

• ディメンションとファクトを区別できる（誰／何 vs 出来事・測れる量）
• スター・スキーマとワイドテーブルを使い分けられる（内部は規律、公開は事業のことば）
• SOLNAの7マートを、固定した粒度で組める
• 結合キー（sessions.customer_id）と接合率をmartsに保持できる
• 真のCAC/LTVを組める素地（ad_spend の粒度・orders の成果）を据えた

「そもそもデータ基盤に投資すべきか」の判断は、決裁者向けの基幹記事データ基盤に投資すべきかへ。「何を測り何を動かすか」は記述・予測編へ。次回は増分処理とスナップショット——会員ランクや購読状態の「過去の状態」を保ち、因果・churn・コホートの前提を作る仕組みを扱います（第6回）。