はじめに
複数のモール（Amazon・楽天のようなマーケットプレイス）と自社サイトで販売していると、次のような悩みがよく出てきます。
- チャネルごとに注文データの形式が違い、重複や表記ゆれが混ざる
- 部署ごとに「売上」の数字が合わない
- 生データに直接つないだダッシュボードが、元データの変更で壊れる
こういうとき、スプレッドシートにデータを集めて、GASや関数でなんとかしようとしがちですよね。
これを解決するデータ基盤のひな形を、dbt・BigQuery・Terraform で作って公開しました。データはすべてスクリプトで生成した架空のものです。CIでは BigQuery の代わりに DuckDB を使うので、クラウドのアカウントなしで、手元で1分以内にパイプライン全体を動かせます。
全体像
| 層 | 実体 | やること |
|---|---|---|
| raw | seed / ロード | 受け取ったままのデータ |
| staging | view | 重複排除・型変換・キーの正規化（ソースごとに1モデル） |
| intermediate | view | 使い回す結合と粗利計算 |
| marts | table | BI向けのファクト・ディメンション・KPIテーブル |
ポイントは、汚れを落とすのは staging の1か所だけ、業務ルール（売上の定義など）を決めるのも marts の1か所だけにしていることです。
1. 汚れたデータを staging で一度だけきれいにする
生成スクリプトでは、実際のエクスポートで起きがちな問題をわざと混ぜています。
| 生データの問題 | 対処する場所 | 検知するテスト |
|---|---|---|
| 同じ注文が2回取り込まれる（約1%） |
stg_orders（qualify row_number()） |
order_id の unique
|
" MARKETPLACE_A" のような表記ゆれ |
clean_text マクロ |
accepted_values |
| 大文字小文字や空白が混ざったメールアドレス |
clean_text マクロ |
email の unique
|
重複排除と正規化はこれだけです。
with source as (
select * from {{ ref('raw_orders') }}
),
deduplicated as (
select *
from source
qualify row_number() over (partition by order_id order by ordered_at) = 1
)
select
order_id,
customer_id,
{{ clean_text('channel') }} as channel,
cast(ordered_at as timestamp) as ordered_at,
cast(cast(ordered_at as timestamp) as date) as ordered_on,
{{ clean_text('status') }} as status,
cast(shipping_fee as integer) as shipping_fee
from deduplicated
{% macro clean_text(column_name) -%}
lower(trim({{ column_name }}))
{%- endmacro %}
正規化をマクロにしておくと、「チャネル名は小文字・前後の空白なし」というルールを全モデルで同じに保てます。
2. 「数字が合う」をテストで保証する
dbt の標準テスト（unique / not_null / relationships / accepted_values）に加えて、金額の突き合わせを自作テストにしています。
-- 注文ファクトの金額が、注文明細の合計と一致すること。
-- 1行でも返れば失敗。
with lines as (
select order_id, sum(line_amount) as line_total
from {{ ref('stg_order_items') }}
group by order_id
)
select f.order_id, f.merchandise_amount, l.line_total
from {{ ref('fct_orders') }} as f
inner join lines as l on f.order_id = l.order_id
where f.merchandise_amount <> l.line_total
同じ考え方で、「日次×チャネルの売上マートの合計が、注文ファクトの合計と一致すること」もテストしています。「売上は完了した注文だけを数える」という定義を fct_orders の1か所に置き、マートはすべてそこから作るので、部署ごとに数字がずれる余地をなくせます。
シード・モデル・テストを合わせた42項目が、push のたびにCIで実行されます。
3. 再現できる指標にする（RFM分析）
CRM施策向けに、顧客をRFM（最終購入日・購入回数・購入金額）で5段階にスコアリングしています。
reference_date as (
select max(last_ordered_on) as as_of from base
),
scored as (
select
b.customer_id,
{{ dbt.datediff('b.last_ordered_on', 'r.as_of', 'day') }} as recency_days,
ntile(5) over (order by {{ dbt.datediff('b.last_ordered_on', 'r.as_of', 'day') }} desc) as r_score,
ntile(5) over (order by b.order_count) as f_score,
ntile(5) over (order by b.lifetime_revenue) as m_score
from base as b
cross join reference_date as r
)
最終購入からの日数を「今日」ではなくデータ内の最新注文日から数えているのがポイントです。いつ実行しても同じ結果になるので、テストやレビューがしやすくなります。
4. 1つのSQLで DuckDB と BigQuery の両方を動かす
profiles.yml に ci（DuckDB）と prod（BigQuery）の2つのターゲットを用意しています。モデルは移植性のあるSQLと dbt のクロスデータベースマクロ（dbt.datediff など）だけで書いているので、CIは速く無料で、本番は BigQuery という使い分けができます。
git clone https://github.com/yahayuta/ec-analytics-platform.git
cd ec-analytics-platform
pip install -r requirements-ci.txt
make build # データ生成 + DuckDB で dbt build
dbt_utils などのパッケージにも依存していません（複合キーの一意性テストは小さな自作のジェネリックテストで代用）。CIの準備が pip install だけで済みます。
5. Terraform で最小権限のインフラを用意する
本番向けには、Terraform で次を作ります。
- ランディング用の GCS バケット（公開アクセスを禁止し、古いファイルは自動削除）
- 層ごとの BigQuery データセット（raw / staging / intermediate / marts）
- dbt 専用のサービスアカウント（ジョブの実行と、上記データセットの編集だけを許可）
- アナリスト用グループには marts の閲覧権限だけを付与
resource "google_bigquery_dataset_iam_member" "dbt_editor" {
for_each = google_bigquery_dataset.layer
dataset_id = each.value.dataset_id
role = "roles/bigquery.dataEditor"
member = "serviceAccount:${google_service_account.dbt.email}"
}
# アナリストやBIツールは、業務向けの層だけを読める
resource "google_bigquery_dataset_iam_member" "analyst_marts_viewer" {
count = var.analyst_group == "" ? 0 : 1
dataset_id = google_bigquery_dataset.layer["marts"].dataset_id
role = "roles/bigquery.dataViewer"
member = "group:${var.analyst_group}"
}
プロジェクト全体の編集者権限を付けると楽ですが、「dbt は自分の層だけ、人は marts だけ」と分けておくと、事故が起きても影響範囲を限定できます。CIでは terraform fmt と validate も実行しています。
まとめ
- 汚れを落とすのは staging の1か所、業務ルールを決めるのは marts の1か所にまとめる
- 「数字が合う」ことを、金額の突き合わせテストで保証する
- 指標は実行日に左右されないように作る
- CIは DuckDB で速く無料に、本番は BigQuery に
- 権限は層ごとに最小限に
今後は、遅れて届く更新に対応したインクリメンタルモデルや、ソースの鮮度チェックを追加する予定です。
スプレッドシートでの集計からBigQueryへの移行は、DXの現場でよくあるパターンだと思います。その移行先の形の一例として、参考になれば幸いです。