ecommerce-data-warehouse

Eコマースデータウェアハウス

概要

Eコマースデータウェアハウスは、ストア、広告プラットフォーム、配送業者、会計システムのデータを1つの分析レイヤーに集約し、単一のプラットフォームだけでは作成できないレポートを可能にします。多くのマーチャントがデータウェアハウスを必要とし始めるのは、プラットフォームに組み込まれた分析機能では対応しきれなくなったとき（通常はARRが100万ドル以上になったとき、または複数の販売チャネルを管理するとき）、広告費と注文データを組み合わせて正確なROASを把握したいとき、あるいはカスタムのコホート分析やアトリビューション分析を実行するためにSQLレベルでアクセスする必要が生じたときです。

このスキルでは、プラットフォームからデータを抽出し、BigQuery（コスト面から推奨）にロードして、dbtで変換する手順を案内します。これにより、チームはLooker Studio、Metabase、またはTableauでダッシュボードを作成できます。

このスキルを使用する場面

• プラットフォームに組み込まれた分析機能では、重要なビジネス上の疑問に答えられない場合
• 複数のソース（Shopify + Meta Ads + Google Ads + 配送）のデータを組み合わせる必要がある場合
• カスタムのコホート継続率、LTV、アトリビューションモデルを構築したい場合
• アナリストやデータサイエンティストが、注文データの生データにSQLでアクセスする必要がある場合
• 複数の販売チャネルを横断する信頼できる唯一の情報源が必要な場合

基本手順

ステップ1：データウェアハウスを選び、インフラを設定する

ほとんどのEコマースマーチャントにとって、BigQueryは最適な出発点です。

• 無料枠：ストレージは最初の10 GB、クエリは月1 TBまで無料
• 管理が必要なインフラはありません。サーバーレスです
• Looker Studio（無料のBIツール）、dbt、主要なデータ統合ツールすべてと優れた連携機能を備えています

代替サービス：

• Snowflake： タイムトラベル、データ共有、高度な同時実行制御が必要なチームに適していますが、小規模な利用ではより高額です
• Redshift： すでにAWSを利用している場合に適しています。BigQueryやSnowflakeより運用上の負担が大きくなります

BigQueryを設定する：

1. console.cloud.google.comにアクセスして、新しいGoogle Cloudプロジェクトを作成します
2. BigQuery APIを有効にします
3. 生データの取り込み用にデータセット（例：ecommerce_raw）を作成し、変換済みテーブル用に2つ目のデータセット（ecommerce_analytics）を作成します

ステップ2：Eコマースプラットフォームからデータを抽出する

プラットフォームのデータをBigQueryに取り込む最も簡単な方法は、マネージドコネクターを使うことです。特別な要件がない限り、カスタムの抽出ツールを作成するのは避けてください。

---

Shopify

オプションA：Fivetran（推奨、$$$）

1. fivetran.comで登録します
2. Shopifyコネクターを追加し、ストアのURLを入力してAPIアクセスを許可します
3. Fivetranは注文、顧客、商品、在庫、財務データをBigQueryに増分同期します
4. 事前構築済みのdbtパッケージ：dbt-fivetran/shopify — ステージングモデルとマートモデルがすぐに利用できます

オプションB：Stitch（中価格帯）

1. stitchdata.comで登録します
2. Shopify連携を追加し、同期するテーブル（orders、order_line_items、customers、products）を選択します
3. Stitchが生データをBigQueryにロードし、dbtで変換します

オプションC：Shopifyのネイティブデータエクスポート（無料、手動）

1. Analytics → Reports → [任意のレポート]に移動してExportをクリックします。注文、顧客、商品で利用できます
2. 一括データの場合：Settings → Bulk operationsに移動するか、Shopify Admin APIの一括クエリを使用して、すべてのデータをJSONLとしてエクスポートします
3. bq load CLIコマンドまたはGoogle Cloud Storageを使用して、JSONLファイルをBigQueryにロードします

オプションD：Polar Analytics（最も簡単なオールインワン）

• Shopify App StoreからPolar Analyticsをインストールします。Shopify、すべての広告プラットフォーム、配送サービスに接続し、標準スキーマの事前構築済みBigQueryエクスポートを提供するため、独自のパイプラインを構築する必要はありません

---

WooCommerce

オプション A：Stitch または Airbyte

1. Stitch： ネイティブの WooCommerce コネクタはありません。代わりに、MySQL コネクタを使用して WordPress データベースに直接接続します
2. Airbyte（オープンソース、セルフホスト可能）： WooCommerce コネクタがあります。月額 $20 の VM で実行するか、Airbyte Cloud を使用します

オプション B：MySQL の直接レプリケーション WooCommerce はすべてのデータを MySQL に保存します。技術チーム向けの方法です。

1. MySQL インスタンスでバイナリログレプリケーションを有効にします
2. Debezium または Airbyte の MySQL CDC コネクタを使用して、変更を BigQuery にストリーミングします
3. DevOps の専門知識が必要ですが、最もリアルタイム性の高いデータを提供できます

オプション C：Metorik エクスポート

1. Metorik を使用して WooCommerce データを取得し、整形済みの CSV に変換します
2. CSV エクスポートをスケジュールし、Cloud Functions またはシンプルな Google Apps Script を介して BigQuery に読み込みます

---

BigCommerce

オプション A：Fivetran BigCommerce コネクタ

• BigCommerce は Fivetran のデータソースとして利用できます。Shopify と同じ方法で設定します

オプション B：BigCommerce Data Solutions

• BigCommerce には、事前構築済みの分析機能を提供するネイティブの Insights 製品があります（上位プランで利用可能）。データウェアハウスを構築する前に、ニーズを満たすかどうか確認してください

オプション C：Stitch BigCommerce コネクタ

• Stitch で利用できます。注文、顧客、商品を BigQuery に同期します

---

ステップ 3：dbt をインストールして設定する

dbt（data build tool）は、SQL を使用して取り込んだ生データを、分析にすぐ使える整形済みテーブルに変換します。依存関係の管理、テスト、ドキュメント作成も行います。

dbt をインストールします：

pip install dbt-bigquery  # for BigQuery
# or: pip install dbt-snowflake / dbt-redshift

プロジェクトを初期化します：

dbt init ecommerce_analytics
cd ecommerce_analytics

~/.dbt/profiles.yml でプロファイルを設定します（BigQuery プロジェクトを指定します）。

ステップ 4：e コマースデータ向けの dbt モデルを構築する

Fivetran + Shopify を使用する場合は、事前構築済みの Shopify dbt パッケージをインストールします。

# packages.yml
packages:
  - package: fivetran/shopify
    version: [">=0.10.0", "<0.11.0"]

dbt deps を実行してインストールし、次に dbt run を実行してすべてのモデルを構築します。

他のプラットフォームやカスタムスキーマを使用する場合は、次の 3 層パターンでモデルを構築します。

ステージング層 — 生データを整形します。ビジネスロジックは含めません。

-- models/staging/stg_orders.sql
with source as (select * from {{ source('shopify_raw', 'order') }}),
renamed as (
    select
        id::varchar as order_id,
        name as order_number,
        customer_id::varchar as customer_id,
        email,
        financial_status,
        fulfillment_status,
        total_price::numeric(12,2) as total_price,
        subtotal_price::numeric(12,2) as subtotal_price,
        total_discounts::numeric(10,2) as total_discounts,
        currency,
        source_name as channel,
        created_at as ordered_at
    from source
    where _fivetran_deleted = false
)
select * from renamed

マート層 — ダッシュボード向けの、ビジネスで使えるテーブルです。

-- models/marts/fct_orders.sql
with orders as (select * from {{ ref('stg_orders') }}),
customers as (select * from {{ ref('stg_customers') }}),
first_orders as (
    select customer_id, min(ordered_at) as first_order_at
    from orders group by 1
)
select
    o.order_id,
    o.order_number,
    o.customer_id,
    o.ordered_at,
    o.total_price,
    o.subtotal_price,
    o.total_discounts,
    o.channel,
    o.ordered_at = fo.first_order_at as is_first_order,
    date_trunc(o.ordered_at, month) as order_month
from orders o
left join first_orders fo on o.customer_id = fo.customer_id
where o.financial_status not in ('voided', 'pending')

ステップ 5：データ品質テストを追加する

スキーマ YAML ファイルにテストを追加して、壊れたパイプラインがダッシュボードに届く前に検出されるようにします。

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: total_price
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0

テストを実行します：dbt test

ステップ 6：BI ツールをデータウェアハウスに接続する

BigQuery にデータが格納されたら、可視化レイヤーを接続します。

• Google Looker Studio（無料）： lookerstudio.google.com にアクセスし → データソースを追加 → BigQuery → プロジェクトとテーブルを選択します。ドラッグ＆ドロップでダッシュボードを作成できます
• Metabase（オープンソース、セルフホスト可能）： 月額 $10 の VM にインストールして BigQuery に接続します。技術的な知識がないユーザーも、GUI でデータを参照したりグラフを作成したりできます
• Tableau、Looker、Power BI： 標準的なエンタープライズ向けの選択肢です。e コマース分析では Looker が最も高機能ですが、コストとセットアップの負担が大きくなります

ベストプラクティス

• カスタムコードではなく、マネージドコネクタから始めます — Fivetran や Stitch の費用は月額 $200～$500 ですが、エンジニアリング作業を 40 時間以上削減できます。トラフィックが非常に少ないストアでない限り、すぐに損益分岐点に達します
• Shopify + Fivetran を利用している場合は、dbt Shopify パッケージを使用します — 一般的に必要とされる 15 以上のテーブル向けに、本番環境品質のモデルがすぐに使える状態で提供されるため、独自に作り直す必要はありません
• 商品と顧客には緩やかに変化するディメンション（SCD Type 2）を実装します — 変更履歴を追跡することで、購入時点で有効だった属性を使って注文を分析できます
• 日付ディメンションテーブルを構築します — 会計期間、祝日、週番号を含む完全なカレンダーを事前に用意します。すべてのファクトテーブルをこのテーブルに結合します
• ダッシュボードを接続する前に dbt テストを追加します — 一意性、非 NULL、参照整合性のテストにより、レポートに誤った数値が表示される前にデータパイプラインの障害を検出できます
• ステージング、中間、マートの各層を分離します — ステージング層では生データを整形し、中間モデルではデータを結合して拡充します。マート層は BI ツールがクエリする、最終的な分析用テーブルです

よくある落とし穴

問題	解決策
ウェアハウスの売上がプラットフォームの分析データと一致しない	通貨の扱い（セントかドルか）、割引の適用順序、税の包含・除外を確認します。ウェアハウスの合計とプラットフォーム API の合計を比較する日次照合チェックを構築します
過去の商品価格が記録されていない	商品ディメンションに SCD Type 2 を実装し、注文時点で有効だった商品行にファクトテーブルを結合します
ETL の実行途中で失敗し、データが一部だけ残る	dbt の on_schema_change: sync_all_columns とアトミックなテーブル切り替えを使用します。増分モデルでは、再実行を冪等に処理できるよう unique_key を設定します
ダッシュボードのクエリが遅すぎる	日次サマリーファクトテーブルで事前集計します。BigQuery では大規模なテーブルを日付でパーティション分割します。データの読み込み時に負荷の高い SQL を実行しないようにします
チャネルをまたいで顧客 ID を解決できない	メールアドレス、電話番号、プラットフォーム固有の ID を単一の正規顧客キーに対応付ける顧客 ID 解決テーブルを構築します

関連スキル

• @attribution-modeling
• @customer-analytics
• @sales-reporting-dashboard
• @product-analytics