【黑马甄选离线数仓day05_核销主题域开发】

1. 指标分类

​ 通过沟通调研，把需求进行分析、抽象和总结，整理成指标列表。指标有原子指标、派生指标、 衍生指标三种类型。

​ 原子指标基于某一业务过程的度量值，是业务定义中不可再拆解的指标，原子指标的核心功能就是对指标的聚合逻辑进行了定义。可以得出结论，原子指标包含三要素，分别是业务过程、度量值和聚合逻辑。例如订单总额就是一个典型的原子指标，其中的业务过程为用户下单、度量值为订单金额，聚合逻辑为sum()求和。

​ 派生指标基于原子指标，在原子指标的基础上加了各种限定。与原子指标不同，派生指标通常会对应实际的统计需求。

​ 衍生指标是在一个或多个派生指标的基础上，通过各种逻辑运算复合而成的。例如比率、比例等类型的指标。衍生指标也会对应实际的统计需求。

2. 四大业务需求

2.1 销售业务需求

业务流程

线下销售/退款流程：

线上销售/退款流程：

主要区别:

• 线上流程相对更加复杂
• 线上订单退款与原订单有关联关系，线下订单是没有关联关系
• 线上订单涉及配送，订单完成时间和下单时间间隔会比较长

核销和售卖

​ 业务部门需要全面分析线上线下的销售情况，包括销售、取消、退款的金额、成本、单量、SKU以及活动的情况。

注意点:

• 成本指标, 只能在库存处理之后才能获得
  • 商品下单, 仅为预占库存, 只有当订单完成时, 才会真正扣减
  • 商品进货的每批次的成本价格均有所不同, 为保证成本准确性,只有在扣减库存时才能拿到对应批次的成本, 所以要想获取商品成本只能在库存处理之后才能获得
• 对于线上订单从下单到完成的数据间隔可能会比较长, 如果只从核销维度来统计数据, 延迟性会非常高, 所以, 还需要从售卖维度来统计数据

故: 销售主题按照时间维度分为核销(扣减库存)和售卖(订单下单)两个需求

原子指标:

• 核销主题:

• 售卖主题
  

衍生指标:

2.2 供应链业务需求

业务流程

• 要货到货流程：

• 商品调拨流程:

​ **为精细化运营，业务部门严格管控供应链，要求计算：库存的数量、金额、SKU、周转、动销、损耗数量和金额、盘点差异以及要货、收货、配送、退货、退配、调入、调出、系统调整的数量和金额。**具体指标如下。

原子指标:

• 库存

• 订单

衍生指标:

2.3 会员需求

​ 因为黑马甄选是生鲜新零售业务，包括线上和线下，所以会员也分为线上会员和线下会员。线下会员，类似理发店的VIP卡，可以在线下购物时享受积分和折扣，还可以进行充值，所以线下会员也叫实体卡会员。线上会员，即在平台上注册的会员。因为线上业务是门店大力推广的方向，也是资本看好的板块，并且可以通过线上渠道进行运营和迭代，所以这里重点分析线上会员。

​ 主要统计会员的注册、消费、充值、余额情况。注意线上会员也可以在线下消费，使用相同的手机号即可。具体指标如下:

原子指标:

衍生指标:

2.4 商城需求

​ 在这里，商城需求指的是对商城的访问日志进行分析，主要是流量数据和交易数据。如何评价线上平台的好坏，UV/PV/新访客数量/跳出数/浏览时长等都是非常重要的指标。如何提高平台的运营效果，就是要从各环节进行优化，比如首页展示、商品详情、商品加购、商品结算等，通过绘制转换漏斗，找到薄弱环节，进行专项提高和突破，所以需要计算各环节的具体指标。具体指标如下。

原子指标:

衍生指标:

3. 核销主题需求

3.1 商品销售情况(已核销)分析

说明：

了解不同城市，各个门店以及各个品类商品每天的销售情况（已核销）

指标：

销售单量、销售数量、销售金额、折扣金额、销售成本、余额支付金额、取消商品销售金额、退款商品销售金额、线上单量、线下单量、线上销售金额、线下销售金额、线上销售成本、线下销售成本、损耗金额、收货金额（收货-退货-退配+调入-调出）、要货金额

维度：

时间、区域、类别

粒度：

时间维度（天、下钻至刻）、区域维度（城市、门店）、类别维度（商品、第一品类、第二品类、第三品类）

涉及库：sale、member、order、stock

涉及表：

• 1. 事实表：

  sale: store_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_item
  
  member: member_union
  
  stock: store_stock_adj
  
  order: store_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require
  
  

• 1. 维度表：

   dim: dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f
  

3.2 经营情况(已核销)分析

说明：

了解不同城市，各个门店每天的销售情况（已核销）

指标：

销售单量、销售数量、销售金额、折扣金额、销售成本、余额支付金额、取消商品销售金额、退款商品销售金额、线上单量、线下单量、线上销售金额、线下销售金额、线上销售成本、线下销售成本、损耗金额、收货金额（收货-退货-退配+调入-调出）、要货金额、线上会员单量、实体卡会员单量、线上会员销售金额、实体卡会员销售金额、线上会员销售成本、实体卡会员销售成本、线上会员下单人数、实体卡会员下单人数、使用余额销售金额、使用余额单量、使用余额的销售成本、使用余额的下单人数

维度：

时间、区域

粒度：

• 时间维度（天，下钻至刻）、区域维度（城市、门店）
• 涉及库：sale、member、order、stock

涉及表：

• 1. 事实表：

  a) sale：store_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_item

  b) member：member_union

  c) stock：store_stock_adj

  d) order：store_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require

• 1. 维度表：

  dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f

3.3 门店营销情况(以核销)分析

​ 说明：了解各个门店营销情况，包括不同销售渠道（已核销）的销售情况以及日清活动的效果。【日清活动指的是，为了保证果蔬的新鲜度，一些商品当日出清，为了能出清商品，会采用打折的方式出售，并且随着时间的推移，越晚折扣越低。】

销售渠道需求：

指标：订单量、退款订单量、取消订单量、商品销售金额、商品销售成本、商品折扣金额

维度：订单渠道

日清活动需求：

指标：销售SKU数、销售单量、销售数量、销售金额、折扣金额、销售成本、销售利润、线上单量、线下单量、线上销售金额、线下销售金额、损耗金额、收货金额（收货-退货-退配+调入-调出）、要货金额、

维度：区域、品类

粒度：区域维度（门店）、品类维度（第一品类）

涉及库：sale、member、order、stock

涉及表：

• 1. 事实表：

  a) sale：store_sale_dtl、store_sale_info、store_sale_pay、shop_order、shop_order_item、shop_sale_pay、shop_refund、shop_refund_item

  b) member：member_union

  c) stock：store_stock_adj

  d) order：store_receive、store_return_to_vendor、store_return_to_dc、store_alloc_in、store_alloc_out、store_require

• 1. 维度表：dwd_dim_date_f、dwd_dim_source_type_map_f、dwd_dim_store_f、dwd_dim_goods_f、dwd_dim_store_goods_f

4. 核销主题数仓建设

ADS

商品销售情况

经营情况

门店营销情况

ADS建表

黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题ADS建表.sql

商品销售情况
	门店商品销售天表: ads_goods_store_goods_statistics_day_i
	城市商品销售天表: ads_goods_city_goods_statistics_day_i
	门店第三品类销售天表: ads_category_store_third_category_statistics_day_i
	门店第二品类销售天表: ads_category_store_second_category_statistics_day_i
	门店第一品类销售天表: ads_category_store_first_category_statistics_day_i
	               
经营情况
	门店经营分析天表: ads_store_manage_statistics_day_i
	城市经营分析天表: ads_store_city_manage_statistics_day_i

门店营销情况
	门店销售渠道分析天表: ads_marketing_store_source_type_day_i
	门店品类日清商品分析天表: ads_marketing_store_category_clean_data_day_i
	门店日清商品分析天表: ads_marketing_store_clean_data_day_i

DWS

商品销售情况

经营情况

DWS层建表

黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题DWS建表.sql

门店商品分析刻表: dws_goods_store_goods_statistics_quarter_i
门店经营分析刻表: dws_store_manage_statistics_quarter_i

DWM

商品销售情况

经营情况

注意: 相比商品销售情况多了会员部分,多了余额部分

此处可以在门店商品销售明细刻表基础上添加会员,余额部分创建一个新表

但是本次项目,我们直接把会员,余额部分已经添加到上述的门店商品销售明细刻表中

DWM层建表

黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题DWM建表.sql

门店商品销售明细刻表: dwm_sold_goods_sold_dtl_i
门店商品损耗刻表: dwm_stock_store_goods_loss_quarter_i
门店商品要货刻表: dwm_order_store_goods_require_quarter_i
门店商品收货刻表: dwm_order_store_goods_receipt_quarter_i

DWD

表汇总分析

DWD层建表

黑马甄选离线数仓项目_完整资料\07_脚本\03_数仓各层建表脚本\核销主题DWD建表.sql

门店销售明细宽表(8张表降维拉宽): dwd_sale_store_sale_dtl_i

线上余额支付明细表: dwd_sale_shop_sale_balance_pay_i
商城订单表(核销表):    dwd_sold_shop_order_i
商城订单明细表(核销表): dwd_sold_shop_order_item_i

商城订单核销明细宽表(10张表降维拉宽): dwd_sold_shop_order_dtl_i

门店库调表(3张表降维拉宽): dwd_stock_store_stock_adj_i

门店收货表(3张表降维拉宽):dwd_order_store_receive_i
门店退货表(3张表降维拉宽): dwd_order_store_return_to_vendor_i
门店退配表(3张表降维拉宽): dwd_order_store_return_to_dc_i

门店调入表(3张表降维拉宽):dwd_order_store_alloc_in_i
门店调出表(3张表降维拉宽):dwd_order_store_alloc_out_i

门店要货表(3张表降维拉宽):dwd_order_store_require_i

5. 核销主题的开发工作

5.1 DWD层开发

建库:
CREATE DATABASE IF NOT EXISTS dwd;

注意：具体计算只计算增量，全量计算直接去掉where条件即可。或者在调度时使用补数的方法，将历史分区一天一天跑出来即可！

在DWD层中, 主要是将事实表数据和维度表数据进行关联, 对事实表数据实施降维工作, 同时针对部分表进行清洗转换处理工作

本次DWD层,共设计为12张事实表, 分别为门店销售明细表、线上余额支付明细、商城订单表(核销表)、订单明细表(核销表)、商城核销明细表、门店库调单、门店收货单、门店退货单、门店退配单、门店调入单、门店调出单、门店要货单