🏪楽天・Amazon・自社ECの売上データをBigQueryに集約して一元管理する方法

はじめに：販路ごとに管理画面・CSVがバラバラで全体の売上が掴めない

複数の販路でネットショップを運営していると、こんな状態になりがちです。

• 楽天市場の売上は RMS の管理画面とダウンロードした CSV
• Amazon の売上はセラーセントラルのレポート
• 自社 EC（Shopify や BASE、自前カート）はまた別の管理画面

「今月、全販路あわせていくら売れたのか」を知りたいだけなのに、3つも4つも管理画面を開いて、Excel に貼り付けて、手で足し算する。月初の数時間がこの作業で消えていく、という個人事業主・中小 EC の方は少なくありません。

この記事では、各販路の売上データを BigQuery に集約し、共通スキーマに正規化したうえで、チャネル横断で売上を見られるようにする設計の流れを説明します。難しいデータ基盤を一気に作る話ではなく、「まず全体を一枚で見られるようにする」ための最小構成を意識します。

この記事のコード例はあくまで設計イメージです。実際のカラム名やデータ提供範囲は各販路の仕様により異なるため、後述のとおり最新の公式ドキュメント・規約を必ず確認してください。

全体設計：CSV/API で集めて、正規化して、統合ビューで見る

やることはシンプルで、次の3ステップに分かれます。

1. 集める：各販路から売上データを取り出して BigQuery に取り込む（CSV アップロード、または API 経由）
2. 正規化する：販路ごとにバラバラなカラム構成を、共通スキーマに揃える
3. 統合する：正規化済みのテーブルを1つのビューにまとめ、チャネル横断で集計する

図にすると、こんなイメージです。

楽天 RMS ─── CSV ───┐
Amazon ──── レポート ┼──→ BigQuery（生データ層）
自社EC ──── API ────┘          │
                               ↓ 正規化（販路ごとのクエリ）
                        共通スキーマのテーブル群
                               │
                               ↓ UNION ALL
                        統合ビュー（全販路を1つに）
                               │
                               ↓
                  チャネル横断のSQL・ダッシュボード

データ層を分けて考える

最初から完璧な1枚のテーブルを作ろうとすると挫折します。次の3層に分けると、後からの修正がぐっと楽になります。

• 生データ層（raw）：各販路から取り込んだそのままのデータ。販路ごとにテーブルを分ける（例：raw_rakuten_orders、raw_amazon_orders、raw_own_orders）。
• 正規化層（staging）：共通スキーマに整えた中間テーブル。販路ごとに変換クエリを書く。
• 統合層（mart）：すべての販路を UNION ALL でまとめた、分析用のビューまたはテーブル。

raw を生のまま残しておくと、後で「正規化のロジックを間違えた」と気づいても、変換クエリを直して作り直すだけで済みます。元データを上書きしないのが鉄則です。

取り込み方法は CSV から始めてよい

「API で自動連携」が理想ではありますが、まずは各管理画面から CSV をダウンロードして BigQuery にアップロードする手動運用で十分です。月1回・週1回の手作業でも、3〜4枚の管理画面を行き来するより圧倒的に速く、全体像が見えます。

運用が固まってきて「毎回の手作業がつらい」となった段階で、API 連携やスケジュールクエリでの自動化を検討すれば十分間に合います。

共通スキーマ設計の勘所

統合の肝は、販路ごとにバラバラなカラムを「共通の意味」に揃えることです。最小限、次のカラムを揃えておくと、ほとんどの集計に対応できます。

カラム	意味	補足
channel	販路区分	rakuten / amazon / own など固定値
order_id	注文ID	販路ごとに採番ルールが違うので、後述のとおり工夫が必要
ordered_at	注文日時	タイムゾーンを JST に揃える
product_code	商品コード	自社の SKU に寄せられると理想
product_name	商品名
quantity	数量
sales_amount	売上金額	税込/税抜のどちらかに統一
fee_amount	手数料	モール手数料・決済手数料など

設計時に特に気をつけたいポイントが3つあります。

1. 注文IDは「販路 + 元のID」で一意にする

楽天と Amazon でたまたま同じ注文番号が振られる可能性はゼロではありません。統合層で重複を防ぐため、channel と元の注文IDを組み合わせたキーを用意しておくと安全です。

CONCAT(channel, '-', raw_order_id) AS order_uid

2. 金額の「税込・税抜」と「手数料の扱い」を最初に決める

販路によって、レポートに出てくる金額が税込だったり税抜だったり、手数料が引かれた後だったりします。どの定義に揃えるかを最初に決めて文書化しておかないと、後で「楽天と Amazon で売上の意味が違った」という事故が起きます。

おすすめは「売上は税込の総額」「手数料は別カラムで持つ」という分け方です。これなら、手数料控除後の手取りも sales_amount - fee_amount で後から計算できます。

3. 日付は JST に統一する

Amazon のレポートは UTC で出てくることがあります。販路ごとにタイムゾーンが混ざると、日次集計が1日ずれます。取り込み時、あるいは正規化のタイミングで JST に揃えておきましょう。

DATETIME(timestamp_utc, 'Asia/Tokyo') AS ordered_at

統合してチャネル横断で見るSQL例

正規化層のテーブルが揃ったら、統合ビューを作ります。各販路のテーブルから共通カラムだけを SELECT し、UNION ALL でつなぎます。

-- 統合ビュー：全販路の売上を1つにまとめる
CREATE OR REPLACE VIEW mart.sales_unified AS
SELECT
  'rakuten' AS channel,
  CONCAT('rakuten-', order_id) AS order_uid,
  ordered_at,
  product_code,
  quantity,
  sales_amount,
  fee_amount
FROM staging.rakuten_orders

UNION ALL

SELECT
  'amazon' AS channel,
  CONCAT('amazon-', order_id) AS order_uid,
  ordered_at,
  product_code,
  quantity,
  sales_amount,
  fee_amount
FROM staging.amazon_orders

UNION ALL

SELECT
  'own' AS channel,
  CONCAT('own-', order_id) AS order_uid,
  ordered_at,
  product_code,
  quantity,
  sales_amount,
  fee_amount
FROM staging.own_orders;

このビューさえできれば、あとは普段の SQL でチャネル横断の集計が自由に書けます。

-- 月次 × 販路ごとの売上・手数料・手取り
SELECT
  FORMAT_DATE('%Y-%m', DATE(ordered_at)) AS month,
  channel,
  SUM(sales_amount)                  AS sales,
  SUM(fee_amount)                    AS fee,
  SUM(sales_amount - fee_amount)     AS net,
  COUNT(DISTINCT order_uid)          AS orders
FROM mart.sales_unified
GROUP BY month, channel
ORDER BY month DESC, channel;

「どの販路が一番手数料負けしていないか」「全販路あわせた今月の売上」が、1本のクエリで出ます。商品単位で横断したいときも同じビューが使えます。

-- 商品コード別に、どの販路でよく売れているか
SELECT
  product_code,
  SUM(IF(channel = 'rakuten', sales_amount, 0)) AS rakuten_sales,
  SUM(IF(channel = 'amazon',  sales_amount, 0)) AS amazon_sales,
  SUM(IF(channel = 'own',     sales_amount, 0)) AS own_sales,
  SUM(sales_amount)                             AS total_sales
FROM mart.sales_unified
GROUP BY product_code
ORDER BY total_sales DESC
LIMIT 50;

同じ商品が販路によって売れ方が違う、という発見はよくあります。これが見えると、在庫配分や広告予算の判断材料になります。

注意：各モールのデータ取得手段・規約・手数料の扱い

実装に入る前に、必ず押さえておきたい注意点です。

データ取得の手段と提供範囲はモールごとに異なります。 楽天 RMS、Amazon セラーセントラル / SP-API、各 EC カートの API は、取得できる項目・期間・取得方法（CSV か API か）・利用条件がそれぞれ違い、仕様変更もあります。この記事のスキーマやカラム名はあくまで設計の考え方を示すもので、実際の項目名や取得可否は、必ず各サービスの最新の公式ドキュメントと利用規約を確認してください。

規約の遵守を最優先に。 API の利用条件、取得したデータの保管・利用範囲、個人情報の取り扱いは、各モールの規約と関連法令に従ってください。スクレイピングなど規約で認められていない手段は使わないでください。

手数料の定義は販路ごとに揃わない前提で。 モール手数料・決済手数料・ポイント原資・送料の扱いはレポートごとにバラバラです。「自社として手数料に何を含めるか」を先に定義し、その定義に合わせて各販路の値をマッピングしてください。レポートの数字をそのまま足すと、販路間で意味の違う数字を比較してしまいます。

まとめ

複数販路の売上を一元管理するポイントを整理します。

• データ層を 生データ / 正規化 / 統合 の3層に分けると、後からの修正に強くなる
• 共通スキーマでは 注文ID（販路+元ID で一意に）・日付（JST 統一）・金額と手数料の定義 を最初に固めておく
• 統合ビューは各販路を UNION ALL でまとめるだけ。あとは普段の SQL でチャネル横断に集計できる
• まずは CSV 手動取り込みで全体像を掴み、つらくなったら自動化を検討すればよい

「全販路の売上を1枚で見る」が実現すると、月初の集計作業から解放されるだけでなく、販路ごとの強み・弱みがデータで見えるようになります。

統合した売上データに広告やアクセス解析をひも付けたい場合は、ECの注文データとGA4を結合して売上を要因分解する もあわせてどうぞ。取り込みを自動化したくなったら、BigQueryのスケジュールクエリでデータマートを毎日自動更新する が参考になります。