离线指标分析
⼀、DWS层
主要分为5个主题
⼆、ADS层
我主要负责 设备 商品 活动（营销）主题的指标
1、设备主题
– 1. 什么是活跃设备
a、打开应⽤的⽤户即为活跃⽤户，不考虑⽤户的使⽤情况。
b、每天⼀台设备打开多次会被计为⼀个活跃⽤户
病案追踪– 2. 需求：
‘⽇活’：当⽇活跃的设备数
‘周活’：当周活跃的设备数，在这⼀周内，多次活跃也计算为1次
‘⽉活’：当⽉活跃的设备数，在这⼀⽉内，多次活跃也计算为1次
思路：从dws层设备主题宽表获取数据
求⽇活：对当天设备数求count，where login_date_last=‘2020-06-14’
求周活：使⽤next_day()函数，本周的下周⼀减7就是这周星期⼀，下周⼀减1就是这周的星期天
where login_date_last>=date_add(next_day(‘2020-06-14’,‘MO’),-7)
and login_date_last<= date_add(next_day(‘2020-06-14’,‘MO’),-1)
‘⽉活’ 使⽤date_format(‘2020-06-14’,‘yyyy-MM’) 求出本⽉⽇期，最后登录⽇期等于本⽉⽇期的都要求count where date_format(login_date_last,‘yyyy-MM’)=date_format(‘2020-06-14’,‘yyyy-MM’)
② 留存率，数据来源于uv表
– 1. 什么是留存率？
a、某段时间内的新增⽤户，经过⼀段时间后，仍然使⽤应⽤的被认作是留存⽤户；这部分⽤户占当时新增⽤户的⽐例即是留存率。
b、例如，5⽉份新增⽤户200，这200⼈启动情况：
6⽉份：启动⼈数为100⼈，5⽉份新增⽤户⼀个⽉后的留存率是50%
7⽉份：启动⼈数为80⼈，5⽉份新增⽤户⼆个⽉后的留存率是40%
8⽉份：启动⼈数为100⼈，5⽉份新增⽤户三个⽉后的留存率是50%
c、留存⽤户⼀般是统计留存率，同时必须有两个参数：哪个⽉份的⼏⽉的留存率
– 2. 本案例需要统计的指标是：
自动友情链接
计算每天的1、2、3⽇留存率
– 3. 实现⽅式
第⼀步：统计当天所有的活跃⽤户
第⼆步：统计昨天的1⽇留存率，求出昨天的新⽤户但是今天上线的⽤户/昨天的新⽤户
第三步：统计前天的2⽇留存率，求出前天的新⽤户但是今天上线的⽤户/前天的新⽤户
所以需要统计的数量有：
1. 昨天的新⽤户但是今天上线的⽤户
2. 昨天的新⽤户
3. 前天的新⽤户但是今天上线的⽤户
4. 前天的新⽤户
③ 沉默⽤户，数据来源于uv表
– 1. 什么是沉默⽤户？
只在安装当天启动过，且启动时间是在7天前
– 2. 实现过程
1. 统计⾸次活跃时间 = 最后末次活跃时间，且最后活跃时间在7天前的⽤户
where login_date_first=login_date_last
and login_date_last < date_add(‘2020-06-25’,-7);
④ 本周回流⽤户数，数据来源于⽤户详情表dws_uv_detail_daycount
– 1. 什么是本周回流⽤户？
上周未活跃，本周活跃的设备，且不是本周新增设备，即
本周回流=本周活跃-本周新增-上周活跃
– 2. 实现步骤：
第⼀步：获取本周活跃的⽤户-本周新增的⽤户
第⼆步：获取上周的活跃的⽤户
第三步：第⼀步的值 - 本周活跃⽼⽤户数
⑤ 流失⽤户
– 1. 什么是流失⽤户
最近7天未活跃的设备
– 2. 实现步骤
第⼀步：获取最近活跃时间⼩于7天
最后登录的⽇期⼩于当天减去7
where login_date_last < date_add(‘2020-06-25’,-7)
⑥ 最近连续三周活跃⽤户数
– 1. 实现步骤
第⼀步: 从dws层获取前⼀周、前两周以及当前周的所有活跃的⽤户
第⼆步： 然后进⾏内连接，能连接上的，则说明这连续的3周都活跃了，最后按照⽤户进⾏分组去重后求count。
本周：这周的下周⼀减7
where dt >= date_add(next_day(‘2020-06-25’,‘mo’),-7)
上⼀周：
where dt >= date_add(next_day(‘2020-06-25’,‘mo’),-14)
and dt < date_add(next_day(‘2020-06-25’,‘mo’),-7)
上两周：
where dt >= date_add(next_day(‘2020-06-25’,‘mo’),-21)
and dt < date_add(next_day(‘2020-06-25’,‘mo’),-14)
2、商品主题
① 商品个数信息
– 1. 需求分析：
sku_num string COMMENT ‘sku个数’,
统计到⽬前为⽌的sku数量
spu_num string COMMENT ‘spu个数’
统计到⽬前为⽌的spu数量
从sku主题表中分别求sku个数和spu个数，count(*)
– 1. 商品销量排名：
1. 是按照什么规则进⾏排名？本需求是按照当天的产品的⽀付⾦额的⼤⼩进⾏排名
数据来源于⽤户⾏为表，按⽀付⾦额排序，取前10
……
from dws_sku_action_daycount
where dt = ‘2020-06-25’
order by payment_amount desc
limit 10
③ 商品收藏排名
数据来源于⽤户⾏为表，按当天收藏总数排序，取前10
④ 商品加⼊购物车排名
数据来源于⽤户⾏为表，按当天加购总数排序，取前10
⑤ 商品退款率排名(近30天)
近30天退款 / 近30天付款 x 100%，再按退款率排序
refund_last_30d_count/payment_last_30d_count*100 refund_ratio
from dwt_sku_topic
order by refund_ratio desc
limit 10;
⑥ 商品差评率
当天差评个数 / 当天好评数 + 中评数 + 差评数 + 默认数
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=‘2020-06-25’
order by appraise_bad_ratio desc
limit 10;
3、营销主题
① 下单数⽬统计
对当天⽤户⾏为表中的下单笔数、下单⾦额、下单⽤户数进⾏求sum
insert into ads_order_daycount
select
‘2020-06-25’ dt ,
sum(order_count) order_count, --单⽇下单笔数
sum(order_amount ) order_amount ,–单⽇下单⾦额
sum(if(order_count > 0 , 1 , 0)) --单⽇下单⽤户数
from dws_user_action_daycount
where dt = ‘2020-06-25’
② ⽀付信息统计
数据来源：⽤户⾏为表、商品表、订单表细胞膜通透性
‘2020-06-25’ dt , --统计⽇期
sum(payment_count) order_count, --单⽇⽀付笔数
sum(payment_amount) order_amount,–单⽇⽀付⾦额
sum(if(order_count > 0 , 1 , 0)) payment_user_count --单⽇⽀付⼈数原子核物理评论
sum(if(payment_amount > 0,1,0)) payment_sku_count --单⽇⽀付商品数
sum(unix_timestamp(payment_time)-unix_timestamp(create_time))/count(*)/60 payment_avg_time --下单到⽀付的平均时长，取分钟数
from dwd_fact_order_info
where dt=‘2020-06-25’
and payment_time is not null
③ 品牌复购率(⽉复购)（难）
主题：品牌信息、是哪个⼀级品类
统计的是⼀个品牌在当前⽉的复购率
复购率：商品被重复购买的频率
单词复购率：两次及以上购买⼈数 / 购买过的总⼈数
多次复购率：三次及以上购买⼈数 / 购买过的总⼈数
难点：
1. 需要进⾏列转⾏，把⽤户当天购买的每个商品的明细炸开
– 使⽤侧写的⽅式，将⼀个⽤户当天购买的每个商品的明细（数组）进⾏侧写，形成多⾏
from dws_user_action_daycount lateral view explode(order_detail_stats) tmp as order_stats_struct
2. 求⼀次购买、⼆次购买、三次购买卡波940
sum(if(order_count>=1,1,0)) buycount,
sum(if(order_count>=2,1,0)) buyTwiceLast,
sum(if(order_count>=2,1,0))/sum( if(order_count>=1,1,0)) buyTwiceLastRatio,
sum(if(order_count>=3,1,0)) buy3timeLast ,
sum(if(order_count>=3,1,0))/sum( if(order_count>=1,1,0)) buy3timeLastRatio ,
date_format(‘2020-06-25’ ,‘yyyy-MM’) stat_mn,
‘2020-06-25’ stat_date
3. sum(order_count) order_count – 购买同⼀品牌数量
from tmp_order
join tmp_sku
on tmp_order.sku_id=tmp_sku.id
– 按照⽤户 + 1级品类 + 品牌id分组，得到⼀个⽤户购买某⼀个1级品类的某⼀品牌的个数
group by tmp_order.user_id,tmp_sku.category1_id,tmp_sku.category1_name,_id )tmp
小说春– 按照品牌进⾏ +
group by tm_id, category1_id, category1_name;
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议