怎么做国内网站,榆林建设网站,排版设计模板网站,北京网站建设方案排名1. 会员主题域需求说明
1.1 各类会员数量统计
说明#xff1a;公司为了对不同会员进行不同的营销策略#xff0c;对各类会员的数量都非常敏感#xff0c;比如注册会员、消费会员、复购会员、活跃会员、沉睡会员。不仅需要看新增数量还要看累积数量。
指标#xff1a;新增…1. 会员主题域需求说明
1.1 各类会员数量统计
说明公司为了对不同会员进行不同的营销策略对各类会员的数量都非常敏感比如注册会员、消费会员、复购会员、活跃会员、沉睡会员。不仅需要看新增数量还要看累积数量。
指标新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额
维度时间
粒度时间维度天、周、月
涉及库sale、member
涉及表 事实表 a. saledwm_sell_o2o_order_i b. membermember_union、user_point_log_detailed、store_amount_record 维度表dwd_dim_date_f、dwd_dim_store_i 1.2 门店会员分析
说明会员对于每个门店来说都是非常重要的资源所以每个门店需要看到会员的分析数据。包括注册情况、充值和余额情况、消费情况等。
指标门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数、余额消费单量、余额支付金额、余额消费金额、会员消费人数、会员消费单量、会员消费金额、会员首单人数、会员首单订单量、会员首单销售额、会员非首单人数、会员非首单订单量、会员非首单销售额
维度时间
粒度时间维度天、周、月
涉及库sale、member
涉及表 事实表 a. saledwm_sell_o2o_order_i b. membermember_union、user_point_log_detailed、store_amount_record 维度表dwd_dim_date_f、dwd_dim_store_i 1.3 其他需求分析
说明除了分析以上两个需求外还会分析一些零散的需求比如会员首次充值、门店新老会员消费、会员复购统计、会员贡献等。从各种角度来分析会员的一些指标。 需求一: 会员首次充值(统计每个会员首次充值的时间, 交易单ID以及对应门店和充值金额)
需求二: 门店新老会员消费(统计每个门店每个月新会员、老会员、全部会员、非会员的数量、消费金额、消费单量(新会员指的首次消费后30天内, 老会员指的首次消费后大于30天))
需求三: 会员复购统计(留存)(统计的指标为统计日期用户量、一日后用户量、二日后用户量、三日后用户量、四日后用户量、五日后用户量、六日后用户量)
需求四: 会员贡献(统计各个会员每天在各个门店消费单量、消费金额、消费成本、线上订单量、线上消费金额、线上消费成本、线下订单量、线下消费金额、线下消费成本) 2. 会员主题建设
2.1 ADS层建设
各类会员数量统计分析
维度指标: 指标新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额
维度: 时间维度天、周、月
涉及ADS表:门店会员分类月表 和 门店会员分类周表
表字段的组成: 维度字段 指标结果字段 建表语句:
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_classify_week_i(trade_date STRING COMMENT 周一日期,
store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,
reg_num_add BIGINT COMMENT 新增注册会员数,reg_num_sum BIGINT COMMENT 累计注册会员数,consume_num_add BIGINT COMMENT 新增消费会员数,consume_num_sum BIGINT COMMENT 累计消费会员数,repurchase_num_add BIGINT COMMENT 新增复购会员数,repurchase_num_sum BIGINT COMMENT 累计复购会员数,active_member_num BIGINT COMMENT 活跃会员数,sleep_member_num BIGINT COMMENT 沉睡会员数,sale_amount_bind DECIMAL(27, 2) COMMENT 会员消费金额
)
comment 门店会员分类周表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_classify_month_i(trade_date STRING COMMENT 月一日期,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,reg_num_add BIGINT COMMENT 新增注册会员数,reg_num_sum BIGINT COMMENT 累计注册会员数,consume_num_add BIGINT COMMENT 新增消费会员数,consume_num_sum BIGINT COMMENT 累计消费会员数,repurchase_num_add BIGINT COMMENT 新增复购会员数,repurchase_num_sum BIGINT COMMENT 累计复购会员数,active_member_num BIGINT COMMENT 活跃会员数,sleep_member_num BIGINT COMMENT 沉睡会员数,sale_amount_bind DECIMAL(27, 2) COMMENT 会员消费金额
)
comment 门店会员分类月表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY); 门店会员分析
维度指标: 指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额
维度: 时间维度天、周、月
涉及表: 门店会员统计周表 和 门店会员统计月表
涉及表字段: 维度字段 指标结果字段 建表语句
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_statistics_week_i(trade_date STRING COMMENT 周一日期,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,store_sale_amount DECIMAL(27, 2) COMMENT 门店销售金额,store_orders_number BIGINT COMMENT 门店总订单量,register_member_num BIGINT COMMENT 当日注册人数,register_member_num_all BIGINT COMMENT 累计注册会员数,register_recharge_num BIGINT COMMENT 当日注册且充值会员数,rg_rc_td_num BIGINT COMMENT 当日注册且充值且消费会员数,register_trade_num BIGINT COMMENT 当日注册且消费会员数,recharge_member_num BIGINT COMMENT 充值会员数,recharge_amount DECIMAL(27, 2) COMMENT 充值金额,recharge_amount_all DECIMAL(27, 2) COMMENT 累计会员充值金额,remain_member_num BIGINT COMMENT 当周最后一天有余额的会员人数,remain_member_amount DECIMAL(27, 2) COMMENT 当周最后一天会员余额,balance_member_num BIGINT COMMENT 余额消费人数,balance_member_order_num BIGINT COMMENT 余额消费单量,balance_pay_amount DECIMAL(27, 2) COMMENT 余额支付金额,balance_member_amount DECIMAL(27, 2) COMMENT 余额消费金额,member_num BIGINT COMMENT 会员消费人数,member_order_num BIGINT COMMENT 会员消费单量,member_amount DECIMAL(27, 2) COMMENT 会员消费金额,member_first_num BIGINT COMMENT 会员首单人数,member_first_order_num BIGINT COMMENT 会员首单订单量,member_first_amount DECIMAL(27, 2) COMMENT 会员首单销售额,member_nofirst_num BIGINT COMMENT 会员非首单人数,member_nofirst_order_num BIGINT COMMENT 会员非首单订单量,member_nofirst_amount DECIMAL(27, 2) COMMENT 会员非首单销售额
)
comment 门店会员统计周表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_member_statistics_month_i(trade_date STRING COMMENT 月一日期,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,store_sale_amount DECIMAL(27, 2) COMMENT 门店销售金额,store_orders_number BIGINT COMMENT 门店总订单量,register_member_num BIGINT COMMENT 当日注册人数,register_member_num_all BIGINT COMMENT 累计注册会员数,register_recharge_num BIGINT COMMENT 当日注册且充值会员数,rg_rc_td_num BIGINT COMMENT 当日注册且充值且消费会员数,register_trade_num BIGINT COMMENT 当日注册且消费会员数,recharge_member_num BIGINT COMMENT 充值会员数,recharge_amount DECIMAL(27, 2) COMMENT 充值金额,recharge_amount_all DECIMAL(27, 2) COMMENT 累计会员充值金额,remain_member_num BIGINT COMMENT 当月最后一天有余额的会员人数,remain_member_amount DECIMAL(27, 2) COMMENT 当月最后一天会员余额,balance_member_num BIGINT COMMENT 余额消费人数,balance_member_order_num BIGINT COMMENT 余额消费单量,balance_pay_amount DECIMAL(27, 2) COMMENT 余额支付金额,balance_member_amount DECIMAL(27, 2) COMMENT 余额消费金额,member_num BIGINT COMMENT 会员消费人数,member_order_num BIGINT COMMENT 会员消费单量,member_amount DECIMAL(27, 2) COMMENT 会员消费金额,member_first_num BIGINT COMMENT 会员首单人数,member_first_order_num BIGINT COMMENT 会员首单订单量,member_first_amount DECIMAL(27, 2) COMMENT 会员首单销售额,member_nofirst_num BIGINT COMMENT 会员非首单人数非去重,member_nofirst_order_num BIGINT COMMENT 会员非首单订单量,member_nofirst_amount DECIMAL(27, 2) COMMENT 会员非首单销售额
)
comment 门店会员统计月表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
其他指标需求
维度指标 需求一: 会员首次充值(统计每个会员首次充值的时间, 交易单ID以及对应门店和充值金额)
需求二: 门店新老会员消费(统计每个门店每个月新会员、老会员、全部会员、非会员的数量、消费金额、消费单量(新会员指的首次消费后30天内, 老会员指的首次消费后大于30天))
需求三: 会员复购统计(留存)(统计的指标为统计日期用户量、一日后用户量、二日后用户量、三日后用户量、四日后用户量、五日后用户量、六日后用户量)
需求四: 会员贡献(统计各个会员每天在各个门店消费单量、消费金额、消费成本、线上订单量、线上消费金额、线上消费成本、线下订单量、线下消费金额、线下消费成本)
涉及表:会员首次充值表 和 门店新老会员消费月表 和 会员复购统计天表 以及 会员贡献天表 建设语句:
CREATE TABLE IF NOT EXISTS ads.ads_mem_member_first_recharge_i(trade_date_time STRING COMMENT 交易时间,trade_date STRING COMMENT 日期,trade_order_id STRING COMMENT 对应的交易单id,zt_id BIGINT COMMENT 中台 会员id,store_no STRING COMMENT 门店编号,city_id BIGINT COMMENT 城市ID,recharge_amount DECIMAL(27, 2) COMMENT 充值金额
)
comment 会员首次充值表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
CREATE TABLE IF NOT EXISTS ads.ads_mem_store_new_old_member_month_i(trade_date STRING COMMENT 月一时间,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,
member_type BIGINT COMMENT 会员类型:1新会员,2老会员,3会员,4非会员,member_num BIGINT COMMENT 消费会员数,sale_amount DECIMAL(27, 2) COMMENT 消费金额,order_num BIGINT COMMENT 消费单量
)
comment 门店新老会员消费月表
partitioned by (dt STRING COMMENT 消费日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
CREATE TABLE IF NOT EXISTS ads.ads_mem_repurchase_day_i(trade_date STRING COMMENT 统计时间,
store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,
member_count BIGINT COMMENT 统计日期用户量,next_member_count_1 BIGINT COMMENT 一日后用户量,next_member_count_2 BIGINT COMMENT 二日后用户量,next_member_count_3 BIGINT COMMENT 三日后用户量,next_member_count_4 BIGINT COMMENT 四日后用户量,next_member_count_5 BIGINT COMMENT 五日后用户量,next_member_count_6 BIGINT COMMENT 六日后用户量
)
comment 会员复购统计天表
partitioned by (dt STRING COMMENT 消费日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
CREATE TABLE IF NOT EXISTS ads.ads_mem_contribution_day_i(trade_date STRING COMMENT 统计时间,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,zt_id BIGINT COMMENT 会员id,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,consume_times BIGINT COMMENT 消费单量,consume_amount DECIMAL(27, 2) COMMENT 消费金额,consume_cost DECIMAL(27, 2) COMMENT 消费成本,online_consume_times BIGINT COMMENT 线上订单量,online_consume_amount DECIMAL(27, 2) COMMENT 线上消费金额,online_consume_cost DECIMAL(27, 2) COMMENT 线上消费成本,offline_consume_times BIGINT COMMENT 线下订单量,offline_consume_amount DECIMAL(27, 2) COMMENT 线下消费金额,offline_consume_cost DECIMAL(27, 2) COMMENT 线下消费成本
)
comment 会员贡献天表
partitioned by (dt STRING COMMENT 消费日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
2.2 DWS层建设
数仓建设分析: 采用自下而上、基于需求、逐层分析出每一层工作内容 DWS层: 数据服务层 (进行最细粒度的统计分析工作)
各类会员数量统计分析
维度指标: 指标新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额
维度: 时间维度天、周、月
涉及表: 门店会员分类天表
表字段的组成: 维度字段 指标结果字段 建表语句:
CREATE TABLE IF NOT EXISTS dws.dws_mem_store_member_classify_day_i(trade_date STRING COMMENT 统计时间,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,reg_num_add BIGINT COMMENT 新增注册会员数,reg_num_sum BIGINT COMMENT 累计注册会员数,consume_num_add BIGINT COMMENT 新增消费会员数,consume_num_sum BIGINT COMMENT 累计消费会员数,repurchase_num_add BIGINT COMMENT 新增复购会员数,repurchase_num_sum BIGINT COMMENT 累计复购会员数,active_member_num BIGINT COMMENT 活跃会员数,sleep_member_num BIGINT COMMENT 沉睡会员数,sale_amount_bind DECIMAL(27, 2) COMMENT 会员消费金额
)
comment 门店会员分类天表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
门店会员分析
维度指标: 指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额
维度: 时间维度天、周、月涉及表:门店会员统计天表表字段的组成: 维度字段 指标结果字段 建表语句:
CREATE TABLE IF NOT EXISTS dws.dws_mem_store_member_statistics_day_i(trade_date STRING COMMENT 统计时间,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,store_sale_amount DECIMAL(27, 2) COMMENT 门店销售金额,store_orders_number BIGINT COMMENT 门店总订单量,register_member_num BIGINT COMMENT 当日注册人数,register_member_num_all BIGINT COMMENT 累计注册会员数,register_recharge_num BIGINT COMMENT 当日注册且充值会员数,rg_rc_td_num BIGINT COMMENT 当日注册且充值且消费会员数,register_trade_num BIGINT COMMENT 当日注册且消费会员数,recharge_member_num BIGINT COMMENT 充值会员数,recharge_amount DECIMAL(27, 2) COMMENT 充值金额,recharge_amount_all DECIMAL(27, 2) COMMENT 累计会员充值金额,remain_member_num BIGINT COMMENT 当日有余额的会员人数,remain_member_amount DECIMAL(27, 2) COMMENT 当日会员余额,balance_member_num BIGINT COMMENT 余额消费人数,balance_member_order_num BIGINT COMMENT 余额消费单量,balance_pay_amount DECIMAL(27, 2) COMMENT 余额支付金额,balance_member_amount DECIMAL(27, 2) COMMENT 余额消费金额,member_num BIGINT COMMENT 会员消费人数,member_order_num BIGINT COMMENT 会员消费单量,member_amount DECIMAL(27, 2) COMMENT 会员消费金额,member_first_num BIGINT COMMENT 会员首单人数,member_first_order_num BIGINT COMMENT 会员首单订单量,member_first_amount DECIMAL(27, 2) COMMENT 会员首单销售额,member_nofirst_num BIGINT COMMENT 会员非首单人数,member_nofirst_order_num BIGINT COMMENT 会员非首单订单量,member_nofirst_amount DECIMAL(27, 2) COMMENT 会员非首单销售额
)
comment 门店会员统计日表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
2.3 DWM层建设
DWM层(数仓中间层), 再此层我们主要进行细粒度轻度汇总工作, 以便于能够在DWS层更好的完成聚合计算
维度指标: 各类会员数量统计:指标新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额
维度: 时间维度天、周、月门店会员分析:指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额
维度: 时间维度天、周、月说明:
由于各类会员数据统计分析和门店会员分析中, 有大量的指标存在一定的依赖关系, 所以在此处我们合并在一起进行分析, 向上抽取出一些公共的DWM层的数据表, 便于后续两个DWS层表数据的聚合统计, 本次主要涉及有四张DWM层表:会员销售订单表、会员首次消费表、会员第二次消费表、会员行为天表 建表语句:
CREATE TABLE IF NOT EXISTS dwm.dwm_mem_sell_order_i(create_time STRING COMMENT 订单创建时间,trade_date STRING COMMENT 交易日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,hourly BIGINT COMMENT 交易小时(0-23),quarter BIGINT COMMENT 刻钟:1.0-15,2.15-30,3.30-45,4.45-60,quarters BIGINT COMMENT 刻钟数:hourly*4quarters,parent_order_no STRING COMMENT 父单订单号/源单号,order_no STRING COMMENT 订单编号,trade_type BIGINT COMMENT 结算类型(0.正常交易,1.赠品发放,2.退货,4.培训,5.取消交易),source_type BIGINT COMMENT 交易来源1:线下POS;2:三方平台;3:传智鲜商城;4:黑马优选团;5:传智大客户;6:传智其他;7:黑马优选;8:优选海淘;9:优选大客户;10:优选POS;11:优选APP;12:优选H5;13:店长工具线下;14:店长工具线上;15:黑马其他,source_type_name STRING COMMENT 交易来源名称,sale_type BIGINT COMMENT 销售类型 1.实物,2.代客,3.优选小程序,4.离店,5.传智鲜小程序,6.第三方平台,7.其他,8.大客户,is_online_order BIGINT COMMENT 是否为线上单:0否,1是,member_type BIGINT COMMENT 会员类型:0非会员,1线上会员,2实体卡会员,is_balance_consume BIGINT COMMENT 是否有余额支付:0否,1是,order_type BIGINT COMMENT 配送类型真正的订单类型由业务类型来决定1-及时送2-隔日送3-自提单4-线下单,express_type BIGINT COMMENT 配送方式0-三方平台配送1-自配送2-快递3-自提4-线下,store_no STRING COMMENT 店铺编码,store_name STRING COMMENT 店铺名称,store_sale_type BIGINT COMMENT 店铺销售类型,store_type_code BIGINT COMMENT 分店类型,worker_num BIGINT COMMENT 员工人数,store_area DECIMAL(27, 2) COMMENT 门店面积,city_id BIGINT COMMENT 城市ID,city_name STRING COMMENT 城市名称,region_code STRING COMMENT 区域编码,region_name STRING COMMENT 区域名称,is_day_clear BIGINT COMMENT 是否日清:0否,1是,is_cancel BIGINT COMMENT 是否取消,cancel_time STRING COMMENT 取消时间,cancel_reason STRING COMMENT 取消原因,last_update_time TIMESTAMP COMMENT 最新更新时间,cashier_no STRING COMMENT 收银员编码,cashier_name STRING COMMENT 收银员名称,zt_id BIGINT COMMENT 中台ID,member_id BIGINT COMMENT 会员ID,card_no STRING COMMENT 卡号,r_name STRING COMMENT 收货人姓名,r_province STRING COMMENT 收货人省份,r_city STRING COMMENT 收货人城市,r_district STRING COMMENT 收货人区域,is_tuan_head BIGINT COMMENT 是否为团长订单,store_leader_id BIGINT COMMENT 团长id,order_group_no STRING COMMENT 团单号,settle_amount DECIMAL(27, 2) COMMENT 结算金额,share_user_id BIGINT COMMENT 分享人用户ID,commission_amount DECIMAL(27, 2) COMMENT 佣金,order_total_amount DECIMAL(27, 2) COMMENT 订单总金额,product_total_amount DECIMAL(27, 2) COMMENT 商品总金额原价,pack_amount DECIMAL(27, 2) COMMENT 餐盒费/打包费,delivery_amount DECIMAL(27, 2) COMMENT 配送费,discount_amount DECIMAL(27, 2) COMMENT 订单优惠金额商家承担优惠金额平台补贴金额,seller_discount_amount DECIMAL(27, 2) COMMENT 商家承担优惠金额,platform_allowance_amount DECIMAL(27, 2) COMMENT 平台补贴金额,real_paid_amount DECIMAL(27, 2) COMMENT 实付金额,product_discount DECIMAL(27, 2) COMMENT 商品优惠金额,real_product_amount DECIMAL(27, 2) COMMENT 商品实际金额,round_amount DECIMAL(27, 2) COMMENT 舍分金额, wechat_amount DECIMAL(27, 4) COMMENT 微信支付,ali_pay_amount DECIMAL(27, 4) COMMENT 支付宝支付,cash_amount DECIMAL(27, 4) COMMENT 现金支付,balance_amount DECIMAL(27, 4) COMMENT 余额支付,point_amount DECIMAL(27, 4) COMMENT 积分支付,unionpay_amount DECIMAL(27, 4) COMMENT 银行支付,member_card_amount DECIMAL(27, 4) COMMENT 线下实体卡支付,gift_amount DECIMAL(27, 4) COMMENT 礼品卡支付,czapi_amount DECIMAL(27, 4) COMMENT 传智支付,other_pay_amount DECIMAL(27, 4) COMMENT 其他支付
)
comment 会员销售订单表
partitioned by (dt STRING COMMENT 销售日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);CREATE TABLE IF NOT EXISTS dwm.dwm_mem_first_buy_i(zt_id BIGINT COMMENT 中台 会员id,trade_date_time STRING COMMENT 首次消费时间,trade_date STRING COMMENT 首次消费日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,store_no STRING COMMENT 消费门店,sale_amount DECIMAL(27, 2) COMMENT 消费金额,order_no STRING COMMENT 订单编号,source_type BIGINT COMMENT 交易来源
)
comment 会员首次消费表
partitioned by (dt STRING COMMENT 消费日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);CREATE TABLE IF NOT EXISTS dwm.dwm_mem_second_buy_i(zt_id BIGINT COMMENT 中台 会员id,trade_date_time STRING COMMENT 第二次消费时间,trade_date STRING COMMENT 第二次消费日期,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,store_no STRING COMMENT 消费门店,sale_amount DECIMAL(27, 2) COMMENT 消费金额,order_no STRING COMMENT 订单编号,source_type BIGINT COMMENT 交易来源
)
comment 会员第二次消费表
partitioned by (dt STRING COMMENT 消费日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);CREATE TABLE IF NOT EXISTS dwm.dwm_mem_member_behavior_day_i(trade_date STRING COMMENT 时间,week_trade_date STRING COMMENT 周一日期,month_trade_date STRING COMMENT 月一日期,zt_id BIGINT COMMENT 中台 会员id,bind_md STRING COMMENT 归属门店(绑定门店),reg_md STRING COMMENT 注册门店,reg_time TIMESTAMP COMMENT 中台 注册时间,is_register BIGINT COMMENT 当日是否注册,is_recharge BIGINT COMMENT 当日是否充值,recharge_times BIGINT COMMENT 充值次数没有充值则为0,recharge_amount DECIMAL(27, 2) COMMENT 充值金额没有充值则为0,is_consume BIGINT COMMENT 当日是否消费,consume_times BIGINT COMMENT 消费次数没有消费则为0,consume_amount DECIMAL(27, 2) COMMENT 消费金额没有消费则为0,is_first_consume BIGINT COMMENT 当日是否首次消费,first_consume_store STRING COMMENT 首次消费门店没有则为null,first_consume_amount DECIMAL(27, 2) COMMENT 首次消费金额没有消费则为0,is_balance_consume BIGINT COMMENT 当日是否余额消费,balance_consume_times BIGINT COMMENT 余额消费次数没有消费则为0,balance_pay_amount DECIMAL(27, 2) COMMENT 余额支付金额没有消费则为0,balance_consume_amount DECIMAL(27, 2) COMMENT 余额消费金额没有消费则为0,is_point_consume BIGINT COMMENT 当日是否积分消费,point_consume_times BIGINT COMMENT 积分消费次数没有消费则为0,point_pay_amount DECIMAL(27, 2) COMMENT 积分支付金额没有消费则为0,point_consume_amount DECIMAL(27, 2) COMMENT 积分消费金额没有消费则为0,point_add BIGINT COMMENT 增加积分没有则为0,point_reduce BIGINT COMMENT 减少积分没有则为0,point_change BIGINT COMMENT 变动积分没有则为0,online_consume_times BIGINT COMMENT 线上订单量,online_consume_amount DECIMAL(27, 2) COMMENT 线上消费金额,offline_consume_times BIGINT COMMENT 线下订单量,offline_consume_amount DECIMAL(27, 2) COMMENT 线下消费金额
)
comment 会员行为天表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
2.4 DWD层建设
DWD层: 数仓明细层(清洗转换、降维操作) 此层核心目标: 基于数据探查情况, 对相关表数据进行合并
会员基础信息表: 需要对此表进行历史数据拉链 说明: 将每日会员基础信息表中新增及更新的数据, 通过拉链的方式记录下来 CREATE TABLE IF NOT EXISTS dwd.dwd_mem_member_union_i(zt_id BIGINT COMMENT 中台会员ID,member_id BIGINT COMMENT 会员ID,user_id BIGINT COMMENT 用户ID,card_no STRING COMMENT 卡号,member_name STRING COMMENT 会员名称,mobile STRING COMMENT 手机号,user_email STRING COMMENT 邮箱,sex BIGINT COMMENT 用户的性别1男性2女性0未知,birthday_date STRING COMMENT 生日,address STRING COMMENT 地址,reg_time TIMESTAMP COMMENT 注册时间,reg_md STRING COMMENT 注册门店,bind_md STRING COMMENT 绑定门店,flag BIGINT COMMENT 0正常,1删除,is_black BIGINT COMMENT 是否被拉黑 1被拉黑,0正常用户,user_state BIGINT COMMENT 会员状态0停用/注销,1正常,2冻结,user_type STRING COMMENT 用户类型-1:传智鲜用户;0:普通用户;1:企业用户 2:内部员工 3:黑马门店 4:商铺会员 5:大买家 6:中间商 7:军区员工,member_type BIGINT COMMENT 会员状态 10未付费会员 20付费会员,member_status BIGINT COMMENT 付费会员状态 -1:未付费会员 1正常 2试用 3过期 4:试用已过期,expired_time TIMESTAMP COMMENT 过期时间,user_source BIGINT COMMENT 用户来源 ,member_level BIGINT COMMENT 会员等级,growth BIGINT COMMENT 成长值,invite_member_id BIGINT COMMENT 邀请人标识,invite_type BIGINT COMMENT 邀请类型0为内部,register_store_leader_id BIGINT COMMENT 注册归属团长 ID,last_update_time TIMESTAMP COMMENT 更新日期,end_date STRING COMMENT 生效结束日期
)
comment 会员基础信息表
partitioned by (start_date STRING COMMENT 生效开始日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
会员积分变动表: 用于记录每个会员每天积分的变动情况(需统计) CREATE TABLE IF NOT EXISTS dwd.dwd_mem_member_point_change_i(trade_date STRING COMMENT 快照时间,zt_id BIGINT COMMENT 中台ID,occupy_subject_id BIGINT COMMENT 占用主体ID0为全部101优选,102传智鲜,103传智商城,point_add BIGINT COMMENT 增加积分没有则为0,point_reduce BIGINT COMMENT 减少积分没有则为0,point_change BIGINT COMMENT 变动积分没有则为0
)
comment 会员积分变动表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
线上会员每日余额变动表:
用于记录每个会员每天余额的变动情况(需统计) CREATE TABLE IF NOT EXISTS dwd.dwd_mem_balance_change_i(trade_date STRING COMMENT 统计日期,zt_id BIGINT COMMENT 中台ID,member_id BIGINT COMMENT 会员ID,record_type BIGINT COMMENT 记录类型0全部,1消费,2充值,3退款,4.清退余额,5.转化,6.系统清除,7.礼品卡兑换,8.现付结余,9.结余退款,10.退卡,times BIGINT COMMENT 次数,change_amount DECIMAL(27, 2) COMMENT 变动金额
)
comment 线上会员每日余额变动表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
线上会员每日余额表:
用于记录每个会员每天余额的情况(需统计) CREATE TABLE IF NOT EXISTS dwd.dwd_mem_balance_online_i(trade_date STRING COMMENT 统计日期,zt_id BIGINT COMMENT 中台ID,member_id BIGINT COMMENT 会员ID, member_type BIGINT COMMENT 会员类型,member_type_name STRING COMMENT 会员类型名称,store_no STRING COMMENT 门店编码,city_id BIGINT COMMENT 城市ID,balance_amount DECIMAL(27, 2) COMMENT 余额
)
comment 线上会员每日余额表
partitioned by (dt STRING COMMENT 统计日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);
3- 会员主题开发
3.0 拉链表 1.从ODS层获取增量数据(上一天新增和更新的数据)
2.拿着DWD原始拉链表数据 left join 增量数据 ,修改原始拉链中历史数据的结束时间
3.拿着left join 的结果集 union all 增量数据
4.把最新的拉链数据优先保存到DWD对应的临时表中
5.使用insertselect 方式把临时表中数据灌入DWD拉链表中 拉链实现流程: 3.1 DWD层开发
DWD层: 数仓明细层(清洗转换、降维操作) 此层核心目标: 基于数据探查情况, 对相关表数据进行合并
会员基础信息表:
需要对此表进行历史数据拉链
说明: 将每日会员基础信息表中新增及更新的数据, 通过拉链的方式记录下来
首次导入
-- 首次导入:
insert overwrite table dwd.dwd_mem_member_union_i partition (start_date)
selectzt_id,member_id,user_id,card_no,member_name,mobile,user_email,sex,birthday_date,address,reg_time,reg_md,bind_md,flag,is_black,user_state,user_type,member_type,member_status,expired_time,user_source,member_level,growth,invite_member_id,invite_type,register_store_leader_id,last_update_time,9999-99-99 as end_date,date_format(date_sub(current_date(),2),yyyy-MM-dd) as start_datefrom ods.ods_mem_member_union_i;
后续导入
-- 步骤一: 在MySQL中, 添加增量的测试数据, 包含 新增 和 更新的数据 (测试)
insert into member.member_union (zt_id, member_id, user_id, card_no, member_name, mobile, user_email, sex, birthday_date, address,reg_time, reg_md, bind_md, flag, is_black, user_state, user_type, member_type, member_status, expired_time, user_source, member_level, growth, invite_member_id, invite_type, register_store_leader_id,last_update_time)
values (32015926,2160344,NULL,,32015925,114,163,0,,不详,2023-10-05 17:09:28,W121,W121,0,0,1,-1,10,-1,NULL,-1,0,0,NULL,NULL,NULL,2023-10-05 17:09:28);UPDATE member.member_union SET SEX 1, last_update_time 2023-10-05 17:10:20 WHERE zt_id 32015925;注意: 日期需要写为上一天的日期-- 步骤二: 执行DataX, 将新增数据和增量数据导入到ODS层 (应该在数据采集中执行)
说明: 此步骤详细过程参考day02实施-- 步骤三: 执行增量数据导入-- 先创建一张目标表的临时表, 用于放置计算后的结果
CREATE TABLE IF NOT EXISTS dwd.dwd_mem_member_union_i_temp(zt_id BIGINT COMMENT 中台会员ID,member_id BIGINT COMMENT 会员ID,user_id BIGINT COMMENT 用户ID,card_no STRING COMMENT 卡号,member_name STRING COMMENT 会员名称,mobile STRING COMMENT 手机号,user_email STRING COMMENT 邮箱,sex BIGINT COMMENT 用户的性别1男性2女性0未知,birthday_date STRING COMMENT 生日,address STRING COMMENT 地址,reg_time TIMESTAMP COMMENT 注册时间,reg_md STRING COMMENT 注册门店,bind_md STRING COMMENT 绑定门店,flag BIGINT COMMENT 0正常,1删除,is_black BIGINT COMMENT 是否被拉黑 1被拉黑,0正常用户,user_state BIGINT COMMENT 会员状态0停用/注销,1正常,2冻结,user_type STRING COMMENT 用户类型-1:传智鲜用户;0:普通用户;1:企业用户 2:内部员工 3:黑马门店 4:商铺会员 5:大买家 6:中间商 7:军区员工,member_type BIGINT COMMENT 会员状态 10未付费会员 20付费会员,member_status BIGINT COMMENT 付费会员状态 -1:未付费会员 1正常 2试用 3过期 4:试用已过期,expired_time TIMESTAMP COMMENT 过期时间,user_source BIGINT COMMENT 用户来源 ,member_level BIGINT COMMENT 会员等级,growth BIGINT COMMENT 成长值,invite_member_id BIGINT COMMENT 邀请人标识,invite_type BIGINT COMMENT 邀请类型0为内部,register_store_leader_id BIGINT COMMENT 注册归属团长 ID,last_update_time TIMESTAMP COMMENT 更新日期,end_date STRING COMMENT 生效结束日期
)
comment 会员基础信息表
partitioned by (start_date STRING COMMENT 生效开始日期)
row format delimited fields terminated by ,
stored as orc
tblproperties (orc.compressSNAPPY);with t2 as (selectt1.zt_id,t1.member_id,t1.user_id,t1.card_no,t1.member_name,t1.mobile,t1.user_email,t1.sex,t1.birthday_date,t1.address,t1.reg_time,t1.reg_md,t1.bind_md,t1.flag,t1.is_black,t1.user_state,t1.user_type,t1.member_type,t1.member_status,t1.expired_time,t1.user_source,t1.member_level,t1.growth,t1.invite_member_id,t1.invite_type,t1.register_store_leader_id,t1.last_update_time,if(t2.zt_id is null OR t1.end_date ! 9999-99-99,t1.end_date,t2.dt) as end_date,t1.start_datefrom dwd.dwd_mem_member_union_i t1left join (select * from ods.ods_mem_member_union_i where dt date_format(date_sub(current_date(),1),yyyy-MM-dd)) as t2on t1.zt_id t2.zt_idunion allselectzt_id,member_id,user_id,card_no,member_name,mobile,user_email,sex,birthday_date,address,reg_time,reg_md,bind_md,flag,is_black,user_state,user_type,member_type,member_status,expired_time,user_source,member_level,growth,invite_member_id,invite_type,register_store_leader_id,last_update_time,9999-99-99 as end_date,date_format(date_sub(current_date(),1),yyyy-MM-dd) as start_datefrom ods.ods_mem_member_union_iwhere dt date_format(date_sub(current_date(),1),yyyy-MM-dd)
)
insert overwrite table dwd.dwd_mem_member_union_i_temp partition (start_date)
select
*
from t2 ;-- 将临时表数据覆盖回目标表中
insert overwrite table dwd.dwd_mem_member_union_i partition (start_date)
select * from dwd.dwd_mem_member_union_i_temp;-- 将临时表删除
drop table dwd.dwd_mem_member_union_i_temp;
情况说明: 1- 目前所做的拉链表是针对历史所有数据, 哪怕这个数据是五年前创建后, 然后五年后发生修改, 我们依然会进行维护2- 目前所有的拉链表是针对表中所有的字段, 只要表中任何字段发生变更, 都会进行维护但是:在实际开发中,我们一般不需要维护历史所有数据, 也不需要维护表中所有的字段一般维护最近一段周期的数据(一个月、一个季度、一年(最常用))一般维护的核心与后续指标计算相关的字段: 用哪些一般维护哪些
