【黑马甄选离线数仓day08_会员主题域开发】

最新推荐文章于 2024-05-13 19:18:29 发布

LKL1026

最新推荐文章于 2024-05-13 19:18:29 发布

阅读量1.4k

点赞数 27

分类专栏：黑马甄选离线数仓项目文章标签：大数据数据库数据仓库 sql

本文链接：https://blog.youkuaiyun.com/qq_52442855/article/details/134793481

版权

1. 会员主题域需求说明

1.1 各类会员数量统计

说明：公司为了对不同会员进行不同的营销策略，对各类会员的数量都非常敏感，比如注册会员、消费会员、复购会员、活跃会员、沉睡会员。不仅需要看新增数量还要看累积数量。

指标：新增注册会员数、累计注册会员数、新增消费会员数、累计消费会员数、新增复购会员数、累计复购会员数、活跃会员数、沉睡会员数、会员消费金额

维度：时间

粒度：时间维度（天、周、月）

涉及库：sale、member

涉及表：

事实表：
- a. sale：dwm_sell_o2o_order_i
- b. member：member_union、user_point_log_detailed、store_amount_record
维度表：dwd_dim_date_f、dwd_dim_store_i

1.2 门店会员分析

说明：会员对于每个门店来说，都是非常重要的资源，所以每个门店需要看到会员的分析数据。包括注册情况、充值和余额情况、消费情况等。

指标：门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数、余额消费单量、余额支付金额、余额消费金额、会员消费人数、会员消费单量、会员消费金额、会员首单人数、会员首单订单量、会员首单销售额、会员非首单人数、会员非首单订单量、会员非首单销售额

维度：时间

粒度：时间维度（天、周、月）

涉及库：sale、member

涉及表：

事实表：
- a. sale：dwm_sell_o2o_order_i
- b. member：member_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.compress'='SNAPPY');


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.compress'='SNAPPY');

门店会员分析

维度指标:

指标: 门店销售额、门店总订单量、当日注册人数、累计注册会员数、当日注册且充值会员数、当日注册且充值且消费会员数、当日注册且消费会员数、充值会员数、充值金额、累计会员充值金额、当日有余额的会员人数、当日会员余额、余额消费人数/单量、余额支付金额、余额消费金额、会员消费人数/单量、会员消费金额、会员首单人数/订单量/销售额、会员非首单人数/订单量/销售额
维度: 时间维度（天、周、月）

涉及表: 
    门店会员统计周表 和 门店会员统计月表

涉及表字段: 维度字段 + 指标结果字段

建表语句

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.compress'='SNAPPY');


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_c

最低0.47元/天解锁文章