数仓搭建-ADS层

太过爱你忘了你带给我的痛 2023-03-03 11:10 58阅读 0赞

### 目录 ###

*   *  设备主题
     *   *   *  活跃设备数（日、周、月）
             *  每日新增设备
             *  沉默用户数
             *  本周回流用户数
             *  流失用户数
             *  留存率
             *  最近连续三周活跃用户数
             *  最近七天内连续三天活跃用户数
     *  会员主题
     *   *   *  会员主题信息
             *  漏斗分析
     *  商品主题
     *   *   *  商品个数信息
             *  商品销量排名
             *  商品收藏排名
             *  商品加入购物车排名
             *  商品退款率排名(最近 30 天)
             *  商品差评率
     *  营销主题（用户+商品+购买行为）
     *   *   *  下单数目统计
             *  支付信息统计
             *  复购率
     *  各个阶层的代码

## 设备主题 ##

#### 活跃设备数（日、周、月） ####

需求定义：  
日活：当日活跃的设备数  
周活：当周活跃的设备数  
月活：当月活跃的设备数  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]

#### 每日新增设备 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]

#### 沉默用户数 ####

需求定义：  
沉默用户：只在安装当天启动过，且启动时间是在 7 天前  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 2]

#### 本周回流用户数 ####

需求定义：  
本周回流用户：上周未活跃，本周活跃的设备，且不是本周新增设备  
DWT层拿不到历史数据，所以往DWS拿数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 3]  
**`解释一下最后一排为啥where last_wk.mid_id=null`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 4]

#### 流失用户数 ####

需求定义：  
流失用户：最近 7 天未活跃的设备  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 5]

#### 留存率 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 7]

#### 最近连续三周活跃用户数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 8]  
**`解释having count(*)=3 因为三周所以三个1`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 9]

#### 最近七天内连续三天活跃用户数 ####

思路一 **`根据dt-rank来算相同的时间`**  
![在这里插入图片描述][20200731195321973.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 10]  
思路二 **`dt-lead=2说明三天连续`**  
![在这里插入图片描述][2020073120133798.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 11]

## 会员主题 ##

#### 会员主题信息 ####

**`会员新鲜度=新增会员数/活跃会员数`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 12]

#### 漏斗分析 ####

统计“浏览->购物车->下单->支付”的转化  
思路：统计各个行为的人数，然后计算比值。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 13]

## 商品主题 ##

#### 商品个数信息 ####

**`sku不去重 spu要去重 在业务中尽量不要使用count(distinct(spu))`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 14]

#### 商品销量排名 ####

**`求前十`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 15]

#### 商品收藏排名 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 16]

#### 商品加入购物车排名 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 17]

#### 商品退款率排名(最近 30 天) ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 18]

#### 商品差评率 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 19]

## 营销主题（用户+商品+购买行为） ##

#### 下单数目统计 ####

需求分析：统计每日下单数，下单金额及下单用户数  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 20]

#### 支付信息统计 ####

每日支付金额、支付人数、支付商品数、支付笔数以及下单到支付的平均时长（取自DWD）  
**`运用了3张表`**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 21]

#### 复购率 ####

需求各一级品类下月品牌复购率  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 22]

## 各个阶层的代码 ##

insert into table ads_continuity_wk_count
    select
       '$do_date',
       concat(date_add(next_day('$do_date','MO'),-7*3),'_',date_add(next_day('$do_date','MO'),-1)),
       count(*)
    from
    (select
       mid_id
    from
    (
    select
       mid_id
    from dws_uv_detail_daycount
    where dt>=date_add(next_day('$do_date','MO'),-7)
    and dt<=date_add(next_day('$do_date','MO'),-1)
    group by mid_id
      union all
    select
       mid_id
    from dws_uv_detail_daycount
    where dt>=date_add(next_day('$do_date','MO'),-7*2)
    and dt<=date_add(next_day('$do_date','MO'),-1-7)
    group by mid_id
      union all
    select
       mid_id
    from dws_uv_detail_daycount
    where dt>=date_add(next_day('$do_date','MO'),-7*3)
    and dt<=date_add(next_day('$do_date','MO'),-1-7*2)
    group by mid_id
    )t1
    group by mid_id
    having count(*)=3)t2;
    
    
    insert into table ads_continuity_uv_count
    select 
       '$do_date',
       concat(date_add('$do_date',-6),'_','$do_date'),
       count(*)
    from
    (select
      mid_id
    from
    (select
       mid_id,
       datediff
    from 
       (select 
      mid_id,
      (lead-dt) datediff 
    from 
      (select 
      mid_id,
      dt,
      lead(dt,2,'1970-01-01') over(partition by mid_id order by dt)  lead
    from dws_uv_detail_daycount)t1)t2
    where datediff=2)t3
    group by mid_id)t4;
    
    insert into table ads_user_topic
    select 
       '$do_date',
       sum(if(login_date_last='$do_date',1,0)),
       sum(if(login_date_first='$do_date',1,0)),
       sum(if(payment_date_first='$do_date',1,0)),
       sum(if(payment_count>0,1,0)),
       count(*),
       sum(if(login_date_last='$do_date',1,0))/count(*),
       sum(if(payment_count>0,1,0))/count(*),
       sum(if(login_date_first='$do_date',1,0))/
    sum(if(login_date_last='$do_date',1,0))
    from dwt_user_topic; 
    
    insert into table ads_user_action_convert_day
    select
       uv.dt,
       uv.day_count,
       cart_count,
       cart_count/uv.day_count,
       order_count,
       order_count/cart_count,
       payment_count,
       payment_count/order_count
    from
    (
    select
       '$do_date' dt,
       sum(if(cart_count>0,1,0)) cart_count,
       sum(if(order_count>0,1,0)) order_count,
       sum(if(payment_count>0,1,0)) payment_count
    from dws_user_action_daycount
    where dt='$do_date'
    )ua
    join ads_uv_count uv
    on ua.dt=uv.dt;
    
    insert into table ads_product_info
    select
       '$do_date' dt,
       sku_num,
       spu_num
    from
    (
    select
       '$do_date' dt,
       count(*) sku_num
    from dwt_sku_topic
    )tmp_sku_num 
    join
    (
    select
       '$do_date' dt,
       count(*) spu_num
    from
    (
    select
       spu_id
    from
       dwt_sku_topic
    group by spu_id
    )tmp_spu_id
    )tmp_spu_num
    on tmp_spu_num.dt=tmp_sku_num.dt;
    
    
    insert into table ads_product_sale_topN
    select
       '$do_date',
       sku_id,
       payment_amount
    from dws_sku_action_daycount
    where dt='$do_date'
    order by payment_amount desc;
    
    
    insert into table ads_product_favor_topN
    select 
       '$do_date',
       sku_id,
       favor_count 
    from dws_sku_action_daycount
    where dt='$do_date'
    order by favor_count desc
    limit 10;
    
    
    insert into table ads_product_cart_topN
    select
       '$do_date',
       sku_id,
       cart_num
    from dws_sku_action_daycount
    where dt='$do_date'
    order by cart_num desc
    limit 10;
    
    
    insert into table ads_product_refund_topN
    select
       '$do_date',
       sku_id,
       refund_last_30d_count/payment_last_30d_count*100 refund_ratio 
    from dwt_sku_topic 
    order by refund_ratio desc 
    limit 10;
    
    
    insert into table ads_appraise_bad_topN
    select
       '$do_date',
       sku_id,
       appraise_bad_count/(appraise_good_count+appraise_mid_count+
    appraise_bad_count+appraise_default_count) appraise_bad_ratio 
    from dws_sku_action_daycount
    where dt='$do_date'
    order by appraise_bad_ratio desc
    limit 10;
    
    
    insert into table ads_order_daycount
    select
       '$do_date',
       sum(order_count),
       sum(order_amount),
       sum(if(order_count>0,1,0))
    from  dws_user_action_daycount
    where dt='$do_date';
    
    
    
    insert into table ads_payment_daycount
    select
       tmp_payment.dt, 
       tmp_payment.payment_count,
       tmp_payment.payment_amount,
       tmp_payment.payment_user_count,
       tmp_skucount.payment_sku_count,
       tmp_time.payment_avg_time
    from
    (
    select
       '$do_date' dt,
       sum(payment_count) payment_count,
       sum(payment_amount) payment_amount,
       sum(if(payment_count>0,1,0)) payment_user_count 
    from dws_user_action_daycount
    where dt='$do_date'
    )tmp_payment 
    join
    (
    select
       '$do_date' dt,
       sum(if(payment_count>0,1,0)) payment_sku_count
    from dws_sku_action_daycount
    where dt='$do_date'
    )tmp_skucount 
    on tmp_payment.dt=tmp_skucount.dt
    join
    (
    select
       '$do_date' dt,
       sum(unix_timestamp(payment_time)-
    unix_timestamp(create_time))/count(*)/60 
    payment_avg_time
    from dwd_fact_order_info
    where dt='$do_date' and payment_time is not null 
    )tmp_time
    on tmp_payment.dt=tmp_time.dt;
    
    
    
    insert into table ads_payment_daycount
    select
       tmp_payment.dt, 
       tmp_payment.payment_count,
       tmp_payment.payment_amount,
       tmp_payment.payment_user_count,
       tmp_skucount.payment_sku_count,
       tmp_time.payment_avg_time
    from
    (
    select
       '$do_date' dt,
       sum(payment_count) payment_count,
       sum(payment_amount) payment_amount,
       sum(if(payment_count>0,1,0)) payment_user_count 
    from dws_user_action_daycount
    where dt='$do_date'
    )tmp_payment 
    join
    (
    select
       '$do_date' dt,
       sum(if(payment_count>0,1,0)) payment_sku_count
    from dws_sku_action_daycount
    where dt='$do_date'
    )tmp_skucount 
    on tmp_payment.dt=tmp_skucount.dt
    join
    (
    select
       '$do_date' dt,
       sum(unix_timestamp(payment_time)-
    unix_timestamp(create_time))/count(*)/60 
    payment_avg_time
    from dwd_fact_order_info
    where dt='$do_date' and payment_time is not null 
    )tmp_time
    on tmp_payment.dt=tmp_time.dt;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]: /images/20230208/0b8fbd9d7082467d80dbc035fcb839fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]: /images/20230208/60adc1945bd04ddcb61ef751cf6178e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 2]: /images/20230208/1c5f18cf6d304974ba7360ce5efd112d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 3]: /images/20230208/08769d62a1fc4c5c98b9e2fb4f856ab8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 4]: /images/20230208/8058556cfe89464c841a2e0945895606.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 5]: /images/20230208/6a058b2165014cada02e447431473e31.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 6]: /images/20230208/80fe0f031abf4c03905fef80c26d17dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 7]: /images/20230208/52b1a162d5094b24b2357740734c4002.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 8]: /images/20230208/9368bb25a9734828acea2e04dabdfccc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 9]: /images/20230208/7d753fb76a5b412fac6101c1df978d4e.png
[20200731195321973.png]: /images/20230208/f786b5a66b8a4c879958f5b065da03e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 10]: /images/20230208/6fdc78fd3d784dfaa447f992b5483543.png
[2020073120133798.png]: /images/20230208/d7f6aad585a74c238b0084bbdc4f22a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 11]: /images/20230208/0bd8b1bf973e412bb264355993d5ec2c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 12]: /images/20230208/b1d14fe636dc4d25a4c639192c952b27.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 13]: /images/20230208/70394216c0f34d25b7b4c449c7df8d92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 14]: /images/20230208/887bbd161b694088beaa4e568346f694.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 15]: /images/20230208/fbb103b0e284458d937409f70857feac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 16]: /images/20230208/3f1e8449b34e4da3aa54db57930ec8d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 17]: /images/20230208/94bb79bb25244ac986cfdcff70c3abb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 18]: /images/20230208/693684f68342466c902283f90d1874a2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 19]: /images/20230208/80f2dfd36acd49ba846370e4560d32fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 20]: /images/20230208/40ac2ab24a784452a728d225449dbd79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 21]: /images/20230208/633ba669c80a436fbbf28be5ea39784a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 22]: /images/20230208/8d622160253f434ea887636f618ffb4e.png