きっかけ
スプレッドシートで複数モールの売上を集計するのがもう無理だった。
Amazon、楽天、Qoo10、TikTok。この4モールで商品を販売しているD2CブランドのデータをGoogleスプレッドシートで管理していた。
データはパンパンでシートは激重。
毎月末になると、各モールの管理画面からCSV（またはXLSX）をダウンロードして、スプレッドシートに貼り付けて、SUM関数を叩いて…という作業が繰り返されていた。月1000件くらいならまだ頑張れるけど、伸び始めると手が追いつかない。
ということで、BigQuery でちゃんとしたデータ蓄積の基盤を作ることにした。この記事はその設計記録です。
最初の壁：モールごとにIDが全部違う
「とりあえず4モールのデータをまとめればいいだけでしょ」と最初は軽く考えていた。甘かった。
各モールのCSVを開いてみると、同じ商品を指すはずの識別子が全部違う。
| モール | 商品識別子 | 例 |
|---|---|---|
| Amazon | SKU + ASIN |
ABC-123, B0XXXXXXXX
|
| 楽天 | 商品管理番号 + SKU管理番号 | 独自ID体系 |
| Qoo10 | product_code | これまた独自 |
| TikTok | seller_sku | Amazonと同じSKUを入れてる場合もあるが… |
さらに地味につらかったのがファイル形式の差異。
| モール | ファイル形式 | エンコード | 特記事項 |
|---|---|---|---|
| Amazon | TSV | Windows-1252 | スプレッドシートで開くと文字化けする |
| 楽天 | CSV | Shift-JIS | ヘッダ行あり |
| Qoo10 | CSV | Shift-JIS | 金額に「"1,089"」みたいなカンマ入り文字列 |
| TikTok | XLSX | UTF-8 | 2行目が説明行なのでスキップが必要 |
実際にファイルを開いて確認するまで、こんなにバラバラだとは思っていなかった。Windows-1252とShift-JISが混在していることに気づかず、最初は文字化けしたまま取り込もうとしていた。
raw → trn → mart の3層設計で解決した
この混沌を整理するために、データレイヤーを3つに分けることにした。これが結果的に一番大事な判断だったと思っている。
raw_*（生データ）→ trn_*（統合・変換）→ mart_*（集計・分析用）
raw層：生データはそのまま保存する
各モールのCSVをBigQueryに取り込む際、変換・加工は一切しない。エンコードの変換だけして、あとは生のまま入れる。
-- raw_amazon: amazon_order_id, purchase_date, sku, asin, quantity, item_price, ...
-- raw_rakuten: order_number, order_datetime, item_management_id, sku_management_id, ...
-- raw_qoo10: settlement_date, order_number, product_code, quantity, selling_price, ...
-- raw_tts: order_id, seller_sku, quantity, subtotal_before_discount, created_time, ...
なぜ変換しないかというと、「後で何かあったときに元データに戻れる」から。設計が変わったり、取り込みのバグが発覚したりしたとき、rawがあれば再処理できる。
全テーブルに _loaded_at を付けて、PARTITION BY DATE(_loaded_at) でパーティション分割している。コスト面でも助かる。
trn層：バラバラなIDを統合する
ここが一番難しかった。4モールの生データを1つのテーブル（trn_integrated_sales）にまとめる。
問題は、各モールで「売上金額」として使うべきカラムが違うこと。
| モール | 売上として使うカラム | 注意点 |
|---|---|---|
| Amazon | item_price |
税込行合計 |
| 楽天 | total_amount |
単価×個数 |
| Qoo10 | selling_price |
販売価格 |
| TikTok | subtotal_before_discount |
割引前のSKU小計 |
「どれが正しい売上を表してるの？」を各モールの管理画面と突き合わせながら確認する作業が意外と時間かかった。
trn層では、original_sku（元のモール側のSKU）と product_id（自社管理のID）をマッピングする。このマッピングが次の「名寄せ問題」につながる。
mart層：分析しやすい形に集計
mart_sales_unit で日次集計する。ここまで来ると、「今日のモール別売上は？」「先週比で何%増えた？」みたいな質問にSQLで即答できる。Looker Studioで可視化する予定。
名寄せ問題：自動化だけでは解決できなかった
設計中に一番詰まったのが名寄せ（各モールのSKUを自社の商品マスタに紐づける作業）だった。
各モールで商品IDが全部違うので、マッピングテーブル（bq_mst_mall_mappings）を作って管理している。
Amazon SKU → 自社 product_id
楽天 SKU管理番号 → 自社 product_id
Qoo10 product_code → 自社 product_id
TikTok seller_sku → 自社 product_id
最初は「新しいSKUが来たら自動で判定できるようにしたい」と思っていた。でも現実的に無理だと気づいた。商品名の表記ゆれ（「シャンプー300ml」と「シャンプー ３００ｍＬ」など）や、バンドル商品のようなモール限定の複合SKUもある。
なので、名寄せ失敗時の運用方針を先に決めた。
-
マッピング未解決の場合 →
is_mapping_resolved = FALSEで記録、管理画面でアラート表示 -
原価未登録の場合 →
is_cost_resolved = FALSEで記録、粗利計算は0として扱う（売上全額が粗利に見える状態）
完全自動化ではなく「未解決を可視化して人が直す」設計にした。これで運用として回せている。
BigQuery + Cloud SQL のハイブリッド構成
生データ・統合・集計はBigQueryで、マスタデータ（商品・SKU・BOM・コスト履歴）はスプシ（将来的にRDB）で管理する。
BigQueryを選んだ理由はシンプルで、「大量データのSELECTが安くて速い」から。一方でマスタデータはリレーション・整合性が重要なのでRDBの方が合っている。（スプシだとメリットは薄いが）
ETLはCloud Run Jobs + Cloud Schedulerで日次02:00に実行する。
Job1: raw_* 差分取り込み（カーソル管理で重複回避）
Job2: bq_mst_* マスタ同期（WRITE_TRUNCATE）
Job3: trn_integrated_sales 差分追記
Job4: mart_sales_unit 再構築
失敗したら3回リトライ → Slack通知というシンプルな構成。
まとめ
同じようにスプレッドシートで複数ECモールの売上を管理していて限界を感じている人に伝えたいこと：
1. 実ファイルを先に確認する（これ大事）
設計の前に各モールのCSVを実際に開いて、エンコード・スキーマ・列名を確認すること。「たぶんこういう形式だろう」という思い込みで設計すると後で詰まる。
2. raw層は生データのまま入れておく
取り込み時に変換・加工したくなる気持ちはわかるが、rawはrawのまま入れる。後で設計を変えたいときに絶対助かる。
3. 名寄せの完全自動化は諦める
ルールベースで自動化できる部分は自動化するが、「未解決を可視化して人が直す」フローを最初から設計に組み込んでおく方が現実的。
BigQueryで分析基盤を作ったことで、モール別・SKU別の売上傾向を即座に確認できるようになる。スプレッドシートでの手集計との違いは体感として大きいはず。
[補足] この記事はGCPを前提にしていますが、raw→trn→martの3層設計の考え方はBigQuery以外のDWHでも同様に使えます。