ecommerce-data-warehouse

电商数据仓库

概述

电商数据仓库会将商店、广告平台、承运商和会计系统中的数据集中到同一个分析层，从而生成任何单个平台都无法独立提供的报告。大多数商家在超出平台内置分析功能的能力范围时（通常是 ARR 达到 100 万美元以上，或需要管理多个渠道）、想要结合广告支出与订单数据来计算真实 ROAS，或需要通过 SQL 访问数据以运行自定义队列和归因分析时，便会开始需要数据仓库。

本技能将指导你从平台提取数据，将其加载到 BigQuery（出于成本考虑，推荐使用），再通过 dbt 转换数据，以便团队在 Looker Studio、Metabase 或 Tableau 中构建仪表板。

何时使用此技能

• 当平台内置的分析功能无法回答重要的业务问题时
• 当你需要整合多个来源的数据（Shopify + Meta Ads + Google Ads + 物流）时
• 当你想要构建自定义的队列留存、LTV 和归因模型时
• 当分析师或数据科学家需要通过 SQL 访问原始订单数据时
• 当你需要跨多个销售渠道建立统一的事实来源时

核心说明

步骤 1：选择数据仓库并设置基础设施

对于大多数电商商家，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）用于存储提取的原始数据，再创建第二个数据集（ecommerce_analytics）用于存储转换后的表

步骤 2：从电商平台提取数据

将平台数据导入 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 → [any report]，然后点击 Export——可导出订单、客户和产品数据
2. 如需批量导出：前往 Settings → Bulk operations，或使用 Shopify Admin API bulk query 将所有数据导出为 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（数据构建工具）使用 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：为电商数据构建 dbt 模型

如果使用 Fivetran + Shopify，请安装预构建的 Shopify dbt 包：

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

运行 dbt deps 进行安装，然后运行 dbt run 构建所有模型。

对于其他平台或自定义架构，请按以下三层模式构建模型：

暂存层 — 清理原始数据，不包含业务逻辑：

-- 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：**常见的企业级选项；Looker 在电商分析方面功能最强大，但成本和设置开销也很高

最佳实践

• 优先使用托管连接器，而非自定义代码 — Fivetran 或 Stitch 每月费用为 200–500 美元，但能节省 40 多个工程工时；除非你的店铺流量极低，否则很快就能收回成本
• 如果使用 Shopify + Fivetran，请使用 dbt Shopify 包 — 它开箱即提供 15 多个常用表的生产级模型，无需重复造轮子
• 为产品和客户实施缓慢变化维度（SCD Type 2） — 跟踪历史变更，以便根据购买时的属性分析订单
• 构建日期维度表 — 预先填充完整日历，包括财务期间、节假日和周数；每个事实表都应与其关联
• 连接仪表板前添加 dbt 测试 — 唯一性、非空和引用完整性测试可以在数据管道故障导致报表数字错误之前将其发现
• 将暂存层、中间层和数据集市层分开 — 暂存层清理原始数据，中间模型进行关联和丰富，数据集市则是 BI 工具查询的最终分析就绪表

常见问题

问题	解决方案
数据仓库中的收入与平台分析数据不一致	核实货币处理方式（分与元）、折扣应用顺序以及是否含税；构建每日对账检查，对比数据仓库汇总数据与平台 API 汇总数据
未记录历史产品价格	在产品维度上实施 SCD Type 2，并将事实表关联到下单时有效的产品记录
ETL 中途失败，留下不完整数据	使用 dbt 的 on_schema_change: sync_all_columns 和原子表交换；对于增量模型，配置 unique_key，以便重新运行时保持幂等
仪表板查询速度太慢	在每日汇总事实表中预先聚合；在 BigQuery 中按日期对大型表分区；避免在加载时运行繁重的 SQL
无法跨渠道解析客户身份	构建客户身份解析表，将电子邮件、电话号码和平台专属 ID 映射到同一个规范客户键

相关技能

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