商业智能数据模型

商业智能数据模型分析报告（B2C电商 O2C：订单到收款）

一. 目标与范围

• 目标：构建一套可复用、标准化的星型数据模型，支撑电商经营分析，包括销售、支付、履约、退退货、会员与渠道归因，统一币种与税制口径，确保跨系统对账一致。
• 覆盖流程：获客→浏览/加购→下单→支付→OMS拆单→WMS发货→物流配送→签收→自动开票→退货退款。
• 系统范围：电商前台、OMS、WMS、支付网关、发票、会员系统；多渠道、多币种、多税制。

二. 维度建模总览（星型模型）

• 总体设计

  • 事实表：FactOrder（订单/行级日快照）、FactPayment（支付与退款）、FactShipment（发货至签收）、FactReturn（退货流程）。
  • 维度：DimCustomer（SCD2）、DimProduct（含类目层级）、DimDate、DimChannel、DimWarehouse、DimRegion、DimPromotion。
  • 关键桥接：订单行-促销桥（多对多分摊）、汇率表（统一换算）、状态字典（对齐状态机）。

• 主键与一致性

  • 业务主键统一：customer_id、order_id、line_no、payment_id、shipment_id、refund_id、warehouse_id、channel_id 等在ODS层保持原样；DW层引入代理键（surrogate key）以支持SCD2与跨源整合。
  • 一致时间：所有事实记录同时保留交易时间与快照日期（date_key），以支持时点口径与期间口径分析。

三. 事实表设计

1. FactOrder（订单/行级日快照）

• 粒度：每个 order_id + line_no + snapshot_date（行级快照）；订单汇总可由行聚合获得。
• 外键：customer_key、product_key、channel_key、warehouse_key（源自行级发货仓/预占仓）、order_date_key、snapshot_date_key、expected_delivery_date_key、region_key（建议来源于下单时收货地址快照）。
• 度量（建议字段）
  • qty
  • line_gross_amount = unit_price × qty
  • discount_amount（行级来自 OrderLine；订单级促销通过桥分摊）
  • tax_amount（行级）
  • line_net_amount_excl_tax = line_gross_amount − discount_amount
  • line_net_amount_incl_tax = line_gross_amount − discount_amount + tax_amount
  • currency
  • amount_std_excl_tax / amount_std_incl_tax（按统一基准币种换算）
  • order_status_at_snapshot（对齐后的标准状态机）
  • invoiced_flag、invoice_amount（如已开票）
• 快照策略：每日增量对订单状态与金额进行重算，直至进入终态（completed/cancelled/returned）。

2. Promotion Bridge（订单行-促销分摊桥）

• 粒度：order_id + line_no + promo_id
• 字段：allocation_ratio、allocated_discount_amount
• 用途：解决订单级/行级促销与订单行的多对多关系，保证行级净额口径准确。

3. FactPayment（支付与退款）

• 粒度：一行对应一笔支付或一笔退款（退款为独立记录，关联 payment_id 或 order_id）。
• 外键：order_key、customer_key、paid_date_key / refunded_date_key、channel_key。
• 字段：
  • record_type（payment/refund）
  • provider、method、status（成功/失败/已退款）
  • amount_original、currency
  • amount_std（按支付清算口径或统一财务口径换算，需明确优先级）
  • transaction_ref
• 说明：支持一笔支付对应多笔部分退款；退款为负向度量或以 record_type 区分。

4. FactShipment（履约）

• 粒度：shipment_id（每个包裹/运单）
• 外键：order_key、warehouse_key、ship_date_key、deliver_date_key、channel_key、region_key（目的地）。
• 字段：
  • carrier_code、tracking_no、delivery_status
  • shipped_at、delivered_at
  • ship_to_deliver_days（若已签收）
  • on_time_flag（delivered_at ≤ expected_delivery_date 或基于仓库 service_level SLA）
• 说明：支持一单多包裹，订单层面时效通过聚合（首/末次签收）计算。

5. FactReturn（退货）

• 粒度：return_id（如业务上支持按行退货，建议扩展为 return_line 粒度；当前模型仅有 return_id 与 shipment_id 的关联）
• 外键：order_key、shipment_key、customer_key、apply_date_key、approved_date_key。
• 字段：
  • reason_code、return_method、status
  • return_amount_estimate（若有；或通过退款事实分摊回推）
• 重要说明（数据缺口）：当前 Return 未提供行级数量与金额，退货率建议：
  • 优先：引入 return_line（order_id + line_no + qty_returned + amount_returned）
  • 备选：用退款金额按订单行比例分摊近似估计退货金额/数量（需标记为估算口径）

四. 维度设计与SCD策略

1. DimCustomer（SCD2）

• 主键：customer_key（代理键），业务键：customer_id
• 重要属性：register_time、customer_tier（SCD2）、gender、birth_date、region_code、is_vip、source_channel（SCD2）
• 有效期：effective_from、effective_to、is_current

2. DimProduct（建议 SCD2）

• 主键：product_key，业务键：product_id / sku_id
• 属性：sku_name、brand、online_flag（SCD2）、launch_date、attributes_json（可衍生扁平化字段）
• 类目：建议在维表内扁平化 category_l1/l2/l3（或独立 DimCategory SCD2，再在 DimProduct 存当前路径代理键）

3. DimChannel（SCD1 或轻量SCD2）

• 属性：channel_type（web/app/mini）、campaign_id、utm_source

4. DimWarehouse（SCD1）

• 属性：warehouse_name、region_code、type（owned/3pl）、service_level

5. DimRegion（SCD1）

• 来源：行政区划与跨境地区码映射（province/city/district/country）

6. DimPromotion（SCD2）

• 属性：promo_type（coupon/fullcut/bundle）、scope（order/line）、start_at、end_at、budget、状态（active/expired）

7. DimDate

• 标准日历，含周/月/季/年、是否节假日、财务期间

五. 口径与KPI标准定义

• 币种与税：所有金额度量同时保留原币与标准币，提供税前/含税两套口径。引入汇率表：fx_rate(date, from_currency, to_currency, rate_type)，rate_type 建议固定为“财务月均/日终中间价”，支付清算报表保留 PSP 口径以对账。
• 订单状态机统一：created → paid → shipped → completed；cancelled 与 returned 为终态分支。快照中的 order_status_at_snapshot 按时间线单调演进，不允许逆转。

KPI定义（建议采用“定义+过滤条件+时间维”三段式）

• GMV（下单口径，含税）：sum(line_gross_amount) − sum(discount_amount) + sum(tax_amount)，过滤 order_status ∈ {created, paid, shipped, completed}（可选排除取消）
• Paid GMV（支付口径，含税）：聚合 FactPayment，record_type=payment，成功状态
• 净GMV（含税）：Paid GMV − 期间内有效退款金额（record_type=refund，已完成）
• AOV：Paid GMV / 支付成功订单数（distinct order_id）
• 转化率（浏览→加购→下单→支付）：基于前台事件事实（view_product/add_to_cart/submit_order/payment_success），同一会话或同一访客归因；各环节独立 UV 或会话数分母
• 复购率：期间内有2单及以上支付成功的客户数 / 期间内有1单及以上支付成功的客户数
• 退货率（优先数量口径）：退货数量 / 已签收数量；金额口径：退款金额 / Paid GMV
• 履约时效达成率：on_time_flag=1 的 shipment 数 / 有承诺SLA的 shipment 数
• 库存周转天数：365 /（COGS / 平均库存额）；若暂缺 COGS/库存快照，用发货含税净额近似 COGS 与 WMS 库存估值近似，需标记为“估算”。

六. 数据流与对账逻辑

• 源到汇聚
  • 前台日志（事件流）：浏览、加购、下单、支付尝试 → 行为漏斗事实
  • OMS：Order/OrderLine、拆单信息、订单状态、期望到达时间、促销代码
  • 支付网关：Payment、Refund、provider、method、清算参考号
  • WMS：Shipment、仓库、出库/签收时间、承运商
  • 发票服务：Invoice（类型、抬头、金额、开票时间）
  • 会员系统：Customer、等级、归因
• 关键对账
  • OMS应收 vs 支付成功：sum(order应付) ≈ sum(payment成功)（考虑币种与手续费口径差异）
  • 支付 vs PSP对账单：以 transaction_ref 对齐，金额以 PSP 清算币种校验
  • 发货/签收 vs 物流回传：shipped_at ≤ delivered_at；签收状态一致
  • 发票 vs 订单：invoice_amount ≤ 已支付金额
  • 退款 vs 退货：金额可相等或小于（考虑仅退款/无货补差），差异拉清单说明

七. 增量装载与快照策略

• ODS层：基于CDC/变更时间戳日增量装载；保证业务主键唯一与幂等。
• 维度：
  • DimCustomer、DimProduct、DimPromotion 采用 SCD2（effective_from/to、is_current）
  • DimChannel、DimWarehouse、DimRegion 多为 SCD1
• 事实：
  • FactOrder：每日对仍非终态订单重算快照；进入终态后不再变更
  • FactPayment/FactShipment/FactReturn：按事件时间增量插入
• 迟到数据处理：保留业务时间与加载时间；对迟到的支付/签收回补相应日期分区并重算衍生指标
• 分区与性能：大表按 snapshot_date/payed_at/shipped_at 分区；列式存储；常用聚合建立物化视图（如日维 GMV、按渠道 AOV）

八. 数据治理与标准

• 币种换算：统一“标准币种”“标准汇率类型”，区分统计口径（业务 vs 清算 vs 财务）。所有事实表同时落原币与标准币。
• 税率口径统一：明确 tax_amount 是否含在 unit_price 中；建议统一存储税前净额与税额两列，避免重复计税。
• 状态机对齐：建立标准状态映射表，屏蔽各系统差异；确保状态演进不可逆。
• 主数据编码：客户、商品、类目、仓库、渠道的全局编码，通过MDM或一致编码表维护。
• 隐私与合规：对电话、地址做脱敏/访问控制；日志与事实分权访问。
• 元数据与血缘：为每个KPI记录定义、来源、筛选条件与刷新频率，保证可追溯。

九. 数据质量规则（示例）

• 唯一性：order_id、(order_id,line_no)、payment_id、shipment_id 在各自域唯一
• 参照完整：事实表外键可在维表命中；命中失败落 Unknown 成员并报警
• 金额校验：discount_amount ≥ 0、refund_amount ≥ 0；单笔退款累计 ≤ 对应支付金额
• 时间一致：delivered_at ≥ shipped_at；paid_at ≥ order_datetime
• 业务平衡：订单应付（税前/含税）与行聚合一致；订单级促销与行级分摊总额一致
• 对账阈值：系统间金额偏差超阈值（例如0.5%或绝对值X）告警

十. 重点落地建议

• 促销分摊落地：上线订单行-促销桥，明确 allocation_ratio（按行金额、数量或配置规则）以提升净额准确性。
• 收货地址快照：在 OMS 下单时固化收货 region_code 与地址字段，作为订单目的地分析与 DimRegion 关联锚点。
• 退货行级化：扩展 Return 明细（return_line），包含 line_no、return_qty、return_amount，以提升退货率与净GMV精度。
• 汇率策略确定：确定统一标准币种与日终/月均汇率；支付清算分析使用 PSP 汇率，财务经营看板用标准汇率。
• 库存指标补全：引入 WMS 日库存快照与成本字段（如标准成本/最近进价），以准确计算库存周转与毛利类指标。

十一. 交付物与里程碑（建议）

• 第1周：口径与状态机对齐文档、KPI定义清单、源字段到目标字段映射（数据字典）
• 第2-3周：维度与事实模型DDL、ETL/ELT作业、SCD与促销分摊实现
• 第4周：对账与DQA规则上线、核心看板（GMV/AOV/转化/退货/时效）
• 第5周：性能优化、迟到数据重算流程、权限与审计
• 持续：口径变更管理与数据质量监控

十二. 已知风险与待决事项

• Return 缺少行级数量与金额：需产品或数据团队评估扩展；在未补全前，退货相关指标以“退款金额口径”为准并标注。
• 订单目的地区域：若OMS未固化下单时地址，需引入“订单收货地址快照”副本表。
• 税额口径差异：确认 unit_price 是否含税以及不同税制（跨境、一般纳税）处理方式，需在ETL中归一。
• 多币种订单与多支付分摊：需定义订单级应付与多笔支付/退款的配比规则（先入先出/比例法），以支持订单行层面的退款回溯分析。

本方案在不夸大前提下覆盖O2C全链路关键分析诉求，提供可执行的数据模型与治理策略。建议优先补齐退货行级明细与收货地址快照，以确保退货率、区域分析与净GMV的口径一致与可控。