数仓搭建-ADS层-蒲公英云

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

设备主题

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

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

每日新增设备

在这里插入图片描述

沉默用户数

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

本周回流用户数

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

流失用户数

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

留存率

在这里插入图片描述

最近连续三周活跃用户数

在这里插入图片描述
解释having count(*)=3 因为三周所以三个1

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

思路一 根据dt-rank来算相同的时间
在这里插入图片描述

思路二 dt-lead=2说明三天连续

会员主题

会员主题信息

会员新鲜度=新增会员数/活跃会员数
在这里插入图片描述

漏斗分析

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

商品主题

商品个数信息

sku不去重 spu要去重在业务中尽量不要使用count(distinct(spu))
在这里插入图片描述

商品销量排名

求前十
在这里插入图片描述

商品收藏排名

在这里插入图片描述

商品加入购物车排名

在这里插入图片描述

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

在这里插入图片描述

商品差评率

在这里插入图片描述

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

下单数目统计

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

支付信息统计

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

复购率

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

各个阶层的代码

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;