数仓设计之订单模型数据仓库表设计订单表

这一篇整理下订单域的数仓设计。目前市场上的企业都有很多销售渠道，线下门店以及各个电商平台，那么要想及时了解销售情况，获取、整合数据以及进行分析就显得尤为重要，订单模型就是一套按照科学系统的方法设计的整合所有渠道订单数据的、省去人力重复加工数据过程的分析模型，所以订单模型对于大部分企业来讲还是很有价值的。我做过三个项目的订单模型设计，接下来就按照这个分类说说订单模型设计的那些事儿：

订单模型设计目标

订单模型组成

数据获取和传输

业务梳理

数据源探查

订单模型设计

订单模型应用

一、订单模型设计目标 1. 统一口径，加强数据可解释性假如一家销售美妆的企业，有多家线下门店，电商平台有淘宝、京东、微商城，那么假如想查看最近一个月的销售额，则需要手机各方数据再进行汇总，期间可能会因为时间统计周期不同或者销售额口径不一致（例如线下门店是营收额，电商平台是减了退款的不含税的销售额）导致最后的结果失去准确性和可解释性，进而失去分析的意义。订单模型在设计之初就会统一销售渠道的数据口径，确保数据对齐。
2. 缩短数据获取周期和成本以上统一口径中提到的例子中，数据获取可能还相对容易些，因为各门店和电商平台都有自己的销售额看板，但是想分析明细数据的话，就需要自行导出各个平台的明细订单数据，期间可能会因为各个平台的商品编码、订单状态不统一，需要对商品编码、订单状态进行 mapping 转换，在进行真正的分析之前就增加了很多前置预处理工作。那么订单模型就承担了这个工作，统一各渠道数据，共同使用一套数据字典，并且使用数据工具定时拉取个平台的数据进行整合计算，极大地降低了分析数据的成本。
3. 便于数据分析当订单模型上线之后，数据分析人员就可以继续订单模型进行不同维度的分析探查，减少了数据获取和分析的成本，更具时效性；若指标探查后具有分析意义，可落地成标签或者报表指标，由平台自动计算。
二、订单模型组成订单模型一般由“订单父表” 和 “订单子表” 组成，搭配“维度模型”一起使用。
1. 订单父子表订单父表是基于订单维度的一张表，一般包含订单的编号、时间、状态、渠道，消费者信息，收货人信息，数量金额信息以及门店信息等；而订单子表是订单明细表，除了记录订单的基本信息外，还会记录商品明细的一些信息，例如商品编码、商品价格等。
2. 退单父子表退单父子表和订单父子表是一一对应的，退单主要记录退货商品的数量、金额以及正向单据的一些信息，用于正负单关联，得出减退款退件口径的指标。
3. 维度模型订单模型一般搭配维度模型使用，维度模型常见有“商品维度模型”，存储商品的详细信息如编码、生产日期、容量、重量等，一般商品信息繁多，全部存储在订单模型会显得非常冗余，而且分析场景不是特别频繁的情况下，可以只在“订单模型”保留商品编码，需要分析商品其它维度信息的时候，可以用“订单模型”关联“商品维度模型”进行其它粒度的分析。
三、数据获取和传输一般数据仓库是一个独立于各平台的企业级的数据库，各个销售渠道一般有单独的数据库，那通常获取源数据的方式有三种：

API 获取：需要开发 API 接口，有开发成本，而且 API 提供方可能由于各种因素会限制数据的返回数量；但是对于数据提供方来说，这种方式更安全更灵活；
使用ETL工具：从源数据库通过脚本和任务加载到目标数据库，对使用方来说比较方便；但是对于数据提供方来说有安全隐患，数据权限和调用频率也不好控制，一般也是抽取源的备份数据库；
文件传输：也可以使用excel、csv 或者 txt ，上传 SFTP 服务器。
一般数据传输周期是 T+1，当然也有实时传输的场景，可用kafka等消息队列进行传输。
四、业务梳理1. 明确数据的业务含义这一步尤其重要，要先明确各渠道订单数据字段的含义和口径，才能确保数据的真实性和可解释性，例如“销售额”需要明确：是否含税、是否减退款、是否减折扣、是商品总金额还是付款金额，“订单时间”需要明确：是下单时间还是付款时间还是发货时间等。
另外对于用户信息也需要格外注意，明确手机号、openid之类的数据是明文还是密文，因为后续触达的话需要保证身份信息可用。
2. 明确业务规则
数据更新场景
因为涉及到目标数据库的数据更新，所以要了解哪些情况下订单数据会更新。常见的有订单状态、订单时间的更新，以及退款之后金额字段的更新，那么一笔订单从创建到关单的周期是多长也需要了解。
业务规则
常见的业务规则比如淘宝店铺积分是如何产生的、积分有效期如何计算、店铺会员是什么折扣、套装折扣产品的成本价如何计算等等，这些需要根据具体业务场景细细了解，才便于开展后续的建模以及分析工作。
五、数据源探查1. 主键确认【数仓设计之订单模型】这一步对于订单数据更新也很重要，一般订单表的主键是“订单编号”或者“数据库自增id”，订单明细表的主键是“订单编号”+“商品编码”或者“数据库自增id”，但是通常情况下可能不会这么简单，因为很多订单系统设计的不太合理，这时候可以做一些处理，以下是我碰到的几种情况：
订单明细表一笔订单存在多行相同商品编码的订单明细，产生多行的原因是一些商品参加了套装活动，价格产生了折扣即不同于单品的价格，当用户单独购买了该单品和含该单品的套装时，该商品由于价格不同就会被拆成2行，那么在订单明细表没有其它主键标识的情况下，如何标识主键进行后续的数据更新呢？
--最直接的办法还是从源头解决，改造源订单系统，增加主键标识；但是在源订单系统没办法改造的情况下，也可以用一个笨方法，即目标订单表用“订单编号”+自生成的“订单明细序列号”作为主键，当源表订单中任一明细发生改变的情况下，抽取该订单的全部订单明细更新该订单的所有明细。
订单父表主键为“订单编号”+“门店编码”，且主键存在重复，那第一步需要查看重复订单的信息是否相同，若全部相同的话，则可直接忽略；若主键相同订单的订单信息不同，则需要查看原因，是否订单源系统的问题，能否修复，若是极少发生的事件，则可以手动更改历史订单的主键信息；若是经常发生的情况，则需要源订单系统做数据上的修正。
2. 身份id探查在业务梳理的时候了解到的身份 id 例如手机号、openid 等，需要探查数据的可用性和填充率：
例如手机号是否合法、是否为虚拟号，openid 是否合法、字符串是否含空格等；如有空格之类的可处理的需要清洗掉，不合法的身份id需要从源头考虑怎样能获取真实可用的数据，或者有没有其它的 id mapping 关系可以关联获取，提高身份 id 的填充率；
3. 数据字典搞清楚字段的含义之后，就需要统一各渠道的数据字典，便于后续分析。
例如淘宝的订单状态为：
已下单
已付款
已发货
已收货
京东的订单状态为：
未付款
已付款
等待发货
已发货
确认收货
那么从业务流程来讲，京东的”未付款”、“已付款”、“已发货”、“确认收货”和淘宝的“已下单”、“已付款”、“已发货”、“已收货”是一个含义，需要统一名称；京东的“等待发货”较淘宝是多出来的一个中间状态，可以保留，相当于看不到淘宝订单已付款未发货的订单，只能看到京东这个状态的订单；
再例如商品编码统一，我之前碰到企业各渠道的商品编码不统一，例如一款洗发水，A系统存的是 “S001 450ml”，B系统存的是“S001_450ml”，这个时候就需要进行统一清洗处理；
另外还有其它字段：支付方式、退款状态等都需一一对照统一，可根据实际业务而定。
4. 异常值处理订单中可能还会出现一些异常值，例如超过字典范围的枚举值，需确认是否新的枚举或是异常，异常是否可以置空或者转换成其它枚举值；例如日期字段存储成了数值，是否可以将该日期直接置空，或者用其它日期字段赋值。
5. 确认数据类型除了以上步骤，最重要的就是确认数据字段类型了，这会影响到后续指标的计算，例如日期是什么格式、数值类型的是int还是decimal，数值类型是否有空值（计算过程中需将null转0），字符串长度是否超过存储要求等等，特殊情况可以依据实际情况来调整。
六、订单模型设计1. 字段对齐经过之前的业务梳理以及数据源探查之后，数据可以说是清洗干净了，那么就到了订单模型设计阶段，其实比较简单，只需将各个源业务系统的字段分类，然后对应起来即可，如果对应不起来的，单独存在即可，另外也可根据字段信息重要成都酌情删减；常见的订单父表分类如下：

文章图片

文章图片

文章图片

文章图片

文章图片

文章图片

订单子表除了订单基础信息模块和订单主表不同，还多了商品信息模块：

文章图片

文章图片

2. 数据清洗加工设计好之后就可以进行数据ETL的处理，一般将源系统的订单数据分表存储在 ods 层，在 dwd 整合成“订单主表”和“订单子表”，在 dm 层进行个性化指标的计算；当然这个分层也不是绝对的，也要根据具体实施项目的产品和条件来定。
七、订单模型应用整合好的“订单主表”和“订单子表”可以作为“数据集”放在数据分析平台，供 Marketing 同事或者数据分析人员进行分析，dm 层的数据可以作为报表展示，或者可以创建标签。一般 dm 和标签的数据周期都是 T+1，当这套模型落地并投入使用后，可以节省重复劳动，极大的降低数据的分析成本，更好的辅助输出销售策略。