离线指标分析

一、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. 求一次购买、二次购买、三次购买
   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,tmp_sku.tm_id
   )tmp
   – 按照品牌进行 +
   group by tm_id, category1_id, category1_name;