C データエンジニアリング — 日次マートを「週」に足すと UU 2.5 倍・売上は全社の 2.1 倍・残高 4 倍（加法・半加法・非加法）

概要

問題

MOps 販促システムチームは mart_campaign_kpi_daily（日 × キャンペーン × チャネル）を Looker Studio「販促 KPI」で見せている。既定期間は直近 7 日、合計行あり。定例で次の声が出た（合成データ上の値）。

📋 コピーselect v.visit_date, v.campaign_id, v.channel,
       v.uu,                                          -- count(distinct member_id)
       safe_divide(o.buyers, v.uu)          as buyer_rate,
       safe_divide(o.revenue_own, o.orders) as aov,
       t.revenue_touched                    as campaign_revenue,   -- 触れた全キャンペーンに丸ごと
       b.outstanding_yen / 2                as coupon_outstanding_yen  -- 日末残高を 2 チャネルに半分ずつ
from v left join o using (...) left join t using (...) left join fct_coupon_balance_daily b using (...)
-- テスト: unique_combination_of_columns [visit_date, campaign_id, channel] のみ
-- Looker Studio: uu・campaign_revenue・coupon_outstanding_yen は SUM、buyer_rate・aov は AVG

• 会員 12 万人・キャンペーン 30 本（小さいほど CVR 高）・来訪 約 2 万件/日 仮定
• 注文の 59.5% は購入前 7 日に複数キャンペーンへ接触 実測・合成
• 案 A（後輩）: UU の集計を AVG に、APPROX_COUNT_DISTINCT に。案 B（先輩）: 生のファクトを直接 COUNT(DISTINCT)

期待する回答形式: 問題点 8 点／改善後の dbt モデル・テスト・ダッシュボード側クエリ／実行例／適用パターン／案 A・B の評価と前提が変わったときの判断／扱わなかった範囲

ヒント（段階的開示）

hll_count.init(member_id, 15)        as uu_sketch
sum(revenue_yen)                      as revenue_last_touch   -- 全行の合計 = 全社売上
sum(revenue_yen * attribution_weight) as revenue_attributed
-- ダッシュボード側: hll_count.merge(uu_sketch), safe_divide(sum(rev), sum(orders))

問題点分析（8 点）

図（SVG）— 列の加法性と、壊れ方の大きさ

客単価は Bad でも +0.1% しかずれなかった。比率の平均が外れるのは分母の大きさと比率が相関するとき。今回は合成データで無相関だったため。

Bad → Good（コード差分）

📋 コピー-- 完成品の数字を持つ
select visit_date, campaign_id, channel,
  count(distinct member_id) as uu,  -- 足せない
  safe_divide(buyers, uu)   as buyer_rate,  -- AVG される
  safe_divide(rev, orders)  as aov,  -- AVG される
  revenue_touched           as campaign_revenue,
                    -- 触れた全キャンペーンに丸ごと
  outstanding_yen / 2       as coupon_outstanding_yen
                    -- 時間で SUM される・チャネルで割る
from ...

# テスト: 粒度の一意性だけ
# BI: SUM と AVG の既定集計＋合計行

📋 コピー-- 足せる部品だけを持つ
select visit_date, campaign_id, channel,
  hll_count.init(member_id, 15) as uu_sketch,  -- MERGE で足せる
  hll_count.init(buyer_id, 15)  as buyer_sketch,
  count(*)                      as orders,
  sum(revenue_yen)              as revenue_last_touch,
                    -- 全行の合計 = 全社売上
  sum(revenue_yen * weight)     as revenue_attributed
                    -- 注文ごとの重みの合計 = 1
from ...
-- 残高は別ファクト（日 × キャンペーン）
--   non_additive_dimension: window_choice: max

# テスト: 足すと元の事実に一致する（日別照合）
# BI: 期間で絞ってから MERGE・合計どうしで割る

模範解答（要点）

dbt モデル

📋 コピー-- models/marts/fct_campaign_kpi_daily.sql  粒度: visit_date × campaign_id × channel
{% set HLL_PRECISION = 15 %}  -- 既定。99% 信頼区間 ±1.72%、最大 32 KiB
with visits as (
  select visit_date, campaign_id, channel,
         hll_count.init(member_id, {{ HLL_PRECISION }}) as uu_sketch
  from {{ ref('fct_visits') }} group by 1, 2, 3
), orders as (
  select order_date as visit_date, last_campaign_id as campaign_id, channel,
         count(*) as orders, sum(revenue_yen) as revenue_last_touch,
         hll_count.init(member_id, {{ HLL_PRECISION }}) as buyer_sketch
  from {{ ref('fct_orders') }} group by 1, 2, 3
), attributed as (  -- 重み付き橋渡し。注文ごとの重みの合計は 1
  select order_date as visit_date, a.campaign_id, channel,
         sum(revenue_yen * a.weight) as revenue_attributed
  from {{ ref('bridge_order_campaign') }} a join {{ ref('fct_orders') }} using (order_id)
  group by 1, 2, 3
)
select ... from visits full join orders using (...) full join attributed using (...)

-- models/marts/bridge_order_campaign.sql
select order_id, campaign_id,
       1 / count(*) over (partition by order_id) as weight  -- 線形按分（替えるならここだけ）
from {{ ref('fct_orders') }}, unnest(touched_campaign_ids) as campaign_id

テスト（照合）

📋 コピー-- tests/assert_kpi_reconciles_with_orders.sql（行が返れば失敗）
select d.visit_date, d.rev_mart, d.rev_attr, f.rev_fact
from (select visit_date, sum(revenue_last_touch) rev_mart, sum(revenue_attributed) rev_attr
      from {{ ref('fct_campaign_kpi_daily') }} group by 1) d
join (select order_date visit_date, sum(revenue_yen) rev_fact
      from {{ ref('fct_orders') }} group by 1) f using (visit_date)
where d.rev_mart != f.rev_fact or abs(d.rev_attr - f.rev_fact) > 1  -- 按分の端数 1 円まで

-- tests/assert_bridge_weights_sum_to_one.sql
select order_id from {{ ref('bridge_order_campaign') }}
group by order_id having abs(sum(weight) - 1) > 1e-9

意味の定義（dbt Semantic Layer）

📋 コピーmeasures:
  - name: coupon_outstanding_yen
    agg: sum                     # キャンペーン間は足す
    non_additive_dimension:
      name: visit_date
      window_choice: max         # 時間は期末の値（min なら期首）
metrics:
  - name: aov
    type: ratio                  # 分子と分母を別々に集計してから割る
    type_params: {numerator: revenue_last_touch, denominator: orders}

ダッシュボード側（Looker Studio カスタムクエリ）

📋 コピーwith k as (
  select campaign_id,
         hll_count.merge_partial(uu_sketch)    as uu_sketch,  -- 日・チャネルを併合
         hll_count.merge_partial(buyer_sketch) as buyer_sketch,
         sum(orders) orders, sum(revenue_last_touch) rev, sum(revenue_attributed) rev_attr
  from `mops.fct_campaign_kpi_daily`
  where visit_date between parse_date('%Y%m%d', @DS_START_DATE)
                       and parse_date('%Y%m%d', @DS_END_DATE)  -- 期間で絞ってから
  group by campaign_id
)
select campaign_id,
       hll_count.extract(uu_sketch) as uu,
       safe_divide(hll_count.extract(buyer_sketch), hll_count.extract(uu_sketch)) as buyer_rate,
       safe_divide(rev, orders) as aov,  -- 分母 0 は NULL
       rev_attr
from k
-- 合計行は BI の「合計」を使わない。全体 UU は全行の MERGE を 1 行クエリで出す

検証用 Python（要点）

📋 コピーdef good_dashboard(mart, balance, start, end):
    rs = [r for r in mart if start <= r["date"] <= end]
    uu = HLL(); buyers = HLL()
    for r in rs:
        uu = uu.merge(r["uu_sketch"])        # 登録値の max。推定値の合計ではない
        buyers = buyers.merge(r["buyer_sketch"])
    orders = sum(r["orders"] for r in rs)
    rev = sum(r["revenue"] for r in rs)
    last = max(d for d, _ in balance if start <= d <= end)  # 半加法: 期末
    return {
        "uu": uu.estimate(),
        "buyer_rate": buyers.estimate() / uu.estimate() if rs else None,
        "aov": rev / orders if orders else None,  # 1 回目はここで ZeroDivisionError
        "coupon_outstanding": sum(v for (d, _), v in balance.items() if d == last),
    }

適用した設計パターン

• 加法・半加法・非加法ファクト（Kimball）: 比率は加法的な部品を持ち、足してから割る
• 重み付き橋渡しテーブル（Kimball の多値ディメンション）: bridge_order_campaign.weight
• 集約可能なスケッチ（BigQuery HLL++）: 精度 15 で 95% ±1.15%・99% ±1.72%
• 半加法の指標定義（dbt non_additive_dimension）
• 書籍対応: 第 5 章 プリミティブ型への執着（数に足し方のルールが無い）／第 14 章 目的別モデリング

一次資料（2026-10-07 参照）: Kimball Group "Additive, Semi-Additive, and Non-Additive Facts" ／ BigQuery "Sketches"・"HyperLogLog++ functions" ／ dbt "Measures" ／ Looker Studio "Connect to Google BigQuery"。URL は MD 版に記載。

実行例（実測・合成データ）

📋 コピーpython3 demo.py                      # 正解・Bad・Good を並べる（約 3.5 秒）
python3 -m unittest -v test_mart     # Good の約束 14 本（約 25 秒）
BAD=1 python3 -m unittest test_mart  # 同じ検査を Bad に（赤が期待値）
python3 mutate.py                    # Good を 9 通りに壊して赤になるか

• テスト: Good 14 本緑（期間 28 通りすべて）／Bad 8 赤・2 エラー
• 1 回目の Good は 1 本エラー: 注文 0 件の日に客単価を割って ZeroDivisionError。Bad の SQL は SAFE_DIVIDE で守っていたのに、直すときに落とした → None にして検査を追加
• 変異テスト: 9 通り（併合→推定値の合計、比率の平均×2、残高の合計／期首、重み 1、精度 10、線形計数なし、分母 0→0）すべて赤。M9 は 1 回目の検査構成なら見逃していた
• 案 A（UU を AVG）: 日次 UU の平均 15,742 vs 週の正解 55,898（−71.8%）

迷った案と採らなかった理由

ポイント解説

1. 壊れているのはマートではなく足し方: 行は全部正しい。期間を延ばすほど壊れる（キャンペーン別 UU 誤差 1 日 2.8% → 7 日 35.9%）
2. 比率の平均は「たまたま合う」: 客単価 +0.1%。今合っていることは設計が正しい証拠にならない
3. UU の誤差許容を決めると設計が楽になる: 誰が 1% を許したかを残すのも設計の一部
4. 残高は時間だけ足せない: 1 日表示では正しく、7 日で 4 倍。期末か期首かは業務の問い
5. 按分しない売上は合計行に出さない: 按分モデルは bridge の 1 か所で替えられるように

扱わなかった範囲（止め方）

• BigQuery・dbt・Looker Studio は未実行。@DS_START_DATE の形式（YYYYMMDD）は記憶で、公式ページでは形式まで未確認
• HLL は自作の代理（密表現のみ）。BigQuery HLL++ の疎表現・バイアス補正とは小さい範囲の誤差が違う
• 費用比較は仮定から出した上限。オンデマンド単価は掛けていない
• アトリビューションのモデル選び、パーセンタイル（KLL スケッチ）、遅延到着の再計算（9/30 の型）は扱わない

今日のまとめ

マートの行が全部正しくても、BI が既定の SUM・AVG をかけた瞬間に週間 UU は 2.5 倍・売上は全社の 2.1 倍・残高は 4 倍になった。列ごとに足せる軸を決め、足せる部品（件数・按分済み売上・HLL スケッチ・期末で取る残高）だけを置くと、28 通りの期間すべてで売上と客単価は一致し、UU と購入者率の誤差は 0.89% 以内（合成データでの実測）。

自己評価（あとで記入）