离线指标分析

一、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

1. 求一次购买、二次购买、三次购买

   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

2. 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;