C データエンジニアリング — キャンペーンROI日次集計基盤（incremental_strategy=appendで注文ステータスがconfirmed→refundedに変わっても既存行が更新されず返金後も売上が計上され続ける × refundsとのJOINが無くorder_amountをそのまま合計 × lookback windowがCURRENT_DATE-1固定で最大30日後に確定する返金を再取込対象外に × campaign_coupon_costがUPDATEで上書きされる可変マスタで都度JOINするため単価改定後のバッチ再実行で過去ROIが遡って書き換わる × partition_expiration_days=90固定で最長180日キャンペーンの前半データが月次再集計時に欠落 × dbt exposures未定義でBI依存がlineageから見えない × 返金額≤注文額のビジネスルールを検証するテストが無い）（MOps 販促システムチーム 経営会議報告数値の遡及的乖離インシデント Bad→Good）

概要

問題

ECサイト MOps 販促システムチームでは、キャンペーンごとの費用対効果（ROI = 売上 − 返金 − クーポン原価）を日次で経営会議に報告する基盤 mart_campaign_roi_daily を運用している。

orders（注文、order_statusはconfirmed→refundedに後から変わりうる）とrefunds（返金明細、注文確定から平均5日・最大30日後に別イベントとして確定する）、campaign_coupon_cost（キャンペーンのクーポン原価単価マスタ。値引き施策の途中で単価改定が入ることがある）の3つをBigQuery × dbt Core 1.9+で結合し、Argo Workflows CronWorkflowで毎晩1回インクリメンタル実行している。

先月末に起きた出来事: 経営会議で「先週報告したキャンペーンAのROIが、今週の資料では±180万円もズレて再表示された」「返金確定後もキャンペーンBの売上が減っていない」という2つの指摘を受け調査したところ、以下の7つの設計・データ保持・ガバナンス上の問題が見つかった。

問題点を全て洗い出し、dbt incremental strategyの見直し（append → merge） × lookback windowの拡張 × クーポン原価を「注文成立時点の確定値」として記録する設計への変更 × BigQuery partition_expiration_daysの見直し × dbt exposuresによるBI依存関係の明示 × dbt Unit Testsによるビジネスルール検証を活用したBad→Goodリファクタリングを行ってください。

制約・前提条件

• BigQuery Standard SQL、dbt Core 1.9+（incremental merge、exposures、unit_tests:がいずれも利用可能）、Argo Workflows 3.5+
• orders: order_id, campaign_id, order_amount, ordered_at, order_status（confirmed / refunded）, updated_at
• refunds: refund_id, order_id, campaign_id, refund_amount, refunded_at。注文確定から平均5日・最大30日後に別イベントとして到着する
• campaign_coupon_cost: campaign_id, coupon_cost_total（キャンペーン単価。改定時は既存行をUPDATEで上書きしており履歴を持たない）
• 現状のmart_campaign_roi_dailyのincremental設定はincremental_strategy: append、増分判定はWHERE ordered_at >= CURRENT_DATE - 1（直近1日分のみを毎晩追加）
• 現状のモデルはordersのorder_amountをそのまま日次合計しており、refundsとのJOINが無い
• ROI計算時、campaign_coupon_costを都度JOINして最新のcoupon_cost_totalを参照している
• ordersテーブルはPARTITION BY DATE(ordered_at)されており、partition_expiration_days = 90が設定されている。キャンペーンは最長180日間実施されるものがある
• dbt exposuresは未定義で、経営会議向けBIダッシュボード（Looker Studio）がこのマートに依存していることがdbt lineageから追跡できない
• dbt testsはmart_campaign_roi_dailyに対して行数チェックとnot_null(order_id)のみで、「返金額が対応する注文額を超えない」というビジネスルールを検証するテストが無い

悪いコード (Before)

📋 コピー{{ config(
    materialized='incremental',
    -- 問題①: 状態変化を反映できないappend戦略のまま
    incremental_strategy='append'
) }}

SELECT
  o.order_id, o.campaign_id, o.ordered_at,
  o.order_amount,
  -- 問題④: 都度最新値をJOINする可変マスタ
  c.coupon_cost_total,
  o.order_amount - c.coupon_cost_total AS roi_amount
  -- 問題②: refundsとのJOINが無く返金が一切反映されない
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('campaign_coupon_cost') }} c USING (campaign_id)
{% if is_incremental() %}
-- 問題③: 直近1日分しか再取込しないため最大30日後の返金確定を拾えない
WHERE o.ordered_at >= CURRENT_DATE - 1
{% endif %}

📋 コピー-- 問題⑤: partition_expiration_daysが90日固定
CREATE TABLE `mops-project.raw_events.orders` (...)
PARTITION BY DATE(ordered_at)
OPTIONS (partition_expiration_days = 90);
-- キャンペーンは最長180日 → 前半のパーティションが月次集計前に消える

-- 問題⑥: exposures.ymlが存在せず、Looker Studioダッシュボードの
--        依存がdbt lineageから追跡できない

-- 問題⑦: schema.ymlのテストは行数チェックとnot_nullのみ
-- 「返金額 <= 注文額」というビジネスルールを検証するテストが無い

ヒント（段階的開示）

-- incremental mergeの骨格（既存行の状態変化を反映）
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id'
) }}
SELECT o.order_id, o.campaign_id, o.ordered_at,
  o.order_amount, o.coupon_cost_applied,
  COALESCE(r.refund_amount, 0) AS refund_amount
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('stg_refunds') }} r USING (order_id)
{% if is_incremental() %}
WHERE o.ordered_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL {{ var('refund_lookback_days', 35) }} DAY)
{% endif %}

# dbt Unit Testの骨格
unit_tests:
  - name: test_refund_not_exceeding_order_amount
    model: mart_campaign_roi_daily
    given:
      - input: ref('stg_orders')
        rows: [{order_id: "o1", order_amount: 1000}]
      - input: ref('stg_refunds')
        rows: [{order_id: "o1", refund_amount: 1500}]  # 不正データ
    expect:
      rows: []

問題点分析（7点）

ROI遡及的乖離の比較図 — Bad vs Good の比較

模範解答

📋 コピー{{ config(
    materialized='incremental',
    incremental_strategy='append'
) }}
SELECT o.order_id, o.campaign_id, o.ordered_at,
  o.order_amount, c.coupon_cost_total,
  o.order_amount - c.coupon_cost_total AS roi_amount
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('campaign_coupon_cost') }} c USING (campaign_id)
{% if is_incremental() %}
WHERE o.ordered_at >= CURRENT_DATE - 1
{% endif %}

📋 コピー-- models/marts/mart_campaign_roi_daily.sql
{{ config(
    materialized='incremental',
    -- 修正①: append(挿入のみ)からmergeへ変更し、
    --        返金確定・ステータス変更による既存行の状態変化を反映できるようにする
    incremental_strategy='merge',
    unique_key='order_id',
    partition_by={'field': 'ordered_at', 'data_type': 'timestamp', 'granularity': 'day'},
    cluster_by=['campaign_id']
) }}

WITH orders_with_refund AS (
  SELECT
    o.order_id,
    o.campaign_id,
    o.ordered_at,
    o.order_status,
    o.order_amount,
    o.coupon_cost_applied,                        -- 修正④: 確定単価を参照
    COALESCE(r.refund_amount, 0) AS refund_amount  -- 修正②: refunds反映
  FROM {{ ref('stg_orders') }} o
  LEFT JOIN {{ ref('stg_refunds') }} r
    ON o.order_id = r.order_id
  {% if is_incremental() %}
  -- 修正③: 返金確定の最大想定日数(30日)+バッファ(5日)まで再取込対象を広げる
  WHERE o.ordered_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL {{ var('refund_lookback_days', 35) }} DAY)
  {% endif %}
)

SELECT
  order_id, campaign_id, DATE(ordered_at) AS order_date,
  order_amount, refund_amount, coupon_cost_applied,
  order_amount - refund_amount - coupon_cost_applied AS roi_amount
FROM orders_with_refund

📋 コピー-- 修正④: campaign_coupon_costは「今後の新規注文に適用する単価」の
--        現在値マスタとしてのみ使い、注文成立時点でcoupon_cost_appliedとして
--        orders側に確定記録する。以後のROI再集計はこの確定値を参照するため、
--        単価改定後にバッチを再実行しても過去日のROIが遡って変わらない。
SELECT
  o.order_id, o.campaign_id, o.order_amount,
  o.ordered_at, o.order_status,
  c.coupon_cost_total AS coupon_cost_applied  -- 注文成立時点の単価を固定
FROM {{ source('ecommerce', 'raw_orders') }} o
JOIN {{ source('ecommerce', 'campaign_coupon_cost') }} c
  USING (campaign_id)
WHERE o.order_status IN ('confirmed', 'refunded')

-- coupon_cost_appliedはINSERT時に一度だけ書き込み、
-- 以後campaign_coupon_costマスタが改定されてもUPDATEしない（イミュータブル）

📋 コピーCREATE TABLE `mops-project.raw_events.orders` (...)
PARTITION BY DATE(ordered_at)
OPTIONS (partition_expiration_days = 90);
-- 問題⑤: 最長180日キャンペーンの前半データが月次再集計前に消える

📋 コピー-- 修正⑤: キャンペーン最大実施期間(180日)+再集計バッファ(30日)を見て
--        partition_expiration_daysを210日に拡張する。
ALTER TABLE `mops-project.raw_events.orders`
SET OPTIONS (partition_expiration_days = 210);

-- 長期保持が必要なROI集計結果は別のarchiveテーブルに寄せ、
-- 生ログ側は短期保持のままコストを抑える。
CREATE TABLE IF NOT EXISTS `mops-project.marts.mart_campaign_roi_daily_archive`
(
  order_id STRING NOT NULL, campaign_id STRING NOT NULL,
  order_date DATE NOT NULL, order_amount NUMERIC,
  refund_amount NUMERIC, coupon_cost_applied NUMERIC, roi_amount NUMERIC
)
PARTITION BY order_date
CLUSTER BY campaign_id
OPTIONS (partition_expiration_days = NULL);  -- 無期限保持

📋 コピーexposures:
  - name: campaign_roi_executive_dashboard
    type: dashboard
    maturity: high
    url: https://lookerstudio.google.com/campaign-roi-dashboard
    description: >
      経営会議で毎週参照するキャンペーンROIダッシュボード。
      mart_campaign_roi_dailyのスキーマ変更は必ずこのダッシュボードへの
      影響を確認してからリリースする。
    depends_on:
      - ref('mart_campaign_roi_daily')
    owner:
      name: MOps 販促システムチーム
      email: mops-team@example.com

📋 コピー# models/marts/_mart_campaign_roi_daily.yml
unit_tests:
  - name: test_refund_not_exceeding_order_amount
    model: mart_campaign_roi_daily
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: "o1", campaign_id: "c1", order_amount: 1000,
             coupon_cost_applied: 100, ordered_at: "2026-09-01T00:00:00",
             order_status: "refunded"}
      - input: ref('stg_refunds')
        rows:
          # 不正データ: 返金額(1500)が注文額(1000)を超えている
          - {order_id: "o1", campaign_id: "c1", refund_amount: 1500,
             refunded_at: "2026-09-05T00:00:00"}
    expect:
      rows: []

📋 コピー-- tests/assert_refund_not_exceeding_order_amount.sql
-- 修正⑦: 本番データに対しても、返金額が注文額を超える行が無いことを検証する。
SELECT order_id, order_amount, refund_amount
FROM {{ ref('mart_campaign_roi_daily') }}
WHERE refund_amount > order_amount

実行例（input → output）

input（改善前のシナリオ）:

キャンペーンAの注文order_id=o1（order_amount=10,000円）が9月1日に成立し、9月20日に全額返金（refund_amount=10,000円）が確定した。同時期、キャンペーンAのクーポン単価がcoupon_cost_total=500円から800円に9月10日改定された。

output（Before・問題のある実装）:

append戦略のためincrementalの再取込対象は「直近1日分」のみで、9月20日の返金イベントは9月1日の注文行を更新しない。9月1日時点では10,000円の売上がそのまま残り続ける。さらに月次バッチが再実行されると、campaign_coupon_costの現在値（800円）が都度JOINされるため、実際は500円だった9月1日〜9日のROIまで800円で再計算され、経営会議に報告済みだった数値と食い違う。

output（After・改善後）:

incremental_strategy: merge + unique_key: order_id + refund_lookback_days=35により、9月20日の返金確定が9月1日の注文行を正しく上書きし、roi_amountから10,000円が減算される。coupon_cost_appliedは注文成立時点（9月1日、単価500円）でstg_ordersに確定記録されているため、9月10日の単価改定以降にバッチを再実行しても9月1日〜9日のROIは500円のまま変わらない。partition_expiration_days=210により180日キャンペーンの月次再集計でも過去データが欠落しない。

ポイント解説

実務への応用

「イベント発生時点で値を確定記録する」「後から状態が変わりうる事実はmergeで扱う」「保持期間は業務要件から逆算する」という3つの設計判断は、ROIマートに限らず、会員ランク特典の原価計算・配送料率の後からの改定・在庫予約のキャンセル反映など、MOpsチームが扱う「後から取り消される・変わりうる業務イベント」全般に再利用できる。特に「経営会議に報告済みの数値が後から静かに変わる」というインシデントは、可変ディメンションの都度JOINが原因であることが多く、他の経営指標マートにも同じ観点でレビューをかける価値がある。

今日のまとめ

次のステップ

• 発展問題: 今回は返金の遅延到着が最大30日という前提だったが、もし返金確定までの期間が業務上「上限が無い」（法的には無期限にクレーム可能）場合、incrementalのlookback windowという設計はどこまで有効か。全件再スキャン(full-refresh)との使い分け基準を設計せよ。
• 参考: dbt incremental strategy（append / merge） / dbt exposures / dbt Unit Tests（dbt 1.8+） / BigQuery partition_expiration_days / SCD Type 1 と Type 2 の使い分け

自己評価（あとで記入）