未来星开发团队的官网

关于用户留存率的计算的几个层次-费元星

       最近接到一个需求,需要统计用户留存率,而且要一次统计多个模块,多个日期的留存率,那如何通过一段hive(SQL)来实现该需求呢,在网上看到一篇很精妙的文章,参考这篇文章实现了需求,可以说是目前见过的一次性统计多个时间区间留存率的最佳实践了,感谢楼主的经验分享,好东西,多记录,多分享!

首先用户留存率一般是面向新增用户的概念,是指某一天注册后的几天还是否活跃,是以每天为单位进行计算的.

一般收到的需求都是一个时间段内的新增用户的几天留存

(1)找到这个时间段内的新增用户(也可能含有地区啊的各种附加条件),一般在日活表中有记录是否是新增状态.
  注意,需要以天为单位进行分组找出用户的id.因为留存率都是以每天为单位进行计算的.
  表结构(register_date,user_id)

(2)找到这个时间段内的活跃用户(active_date,user_id)

(3)以 1表 为主表left join 2表 以user_id为关联键,统计留存数

  这样后的记录类型为:register_date,user_id,active_date

  register_date为新增日期,即留存率的单位天.

  user_id为用户id,distinct user_id来计算用户数

 

留存率怎么算?
  active_date - register_date = 1,说明注册的次日用户是活跃的,所以count+1
  所以我们只要关注 active_date 和 register_date 相差天数即可统计留存数
  取天数差的时候用datediff(active_date,register_date)来计算,active_date 和 register_date 的格式为 yyyy-MM-dd

(4)计算留存率

代码模板:原文中存在一些小错误,已改正,group by 中的数字我这边是报错的,所以可直接替换为对象的列名

select dim_date
  ,node_id
  ,total_cnt
  ,concat_ws('% | ', cast(round(dif_1cnt*100/total_cnt, 2) as string), cast(dif_1cnt as string))
  ,concat_ws('% | ', cast(round(dif_2cnt*100/total_cnt, 2) as string), cast(dif_2cnt as string))
  ,concat_ws('% | ', cast(round(dif_3cnt*100/total_cnt, 2) as string), cast(dif_3cnt as string))
  ,concat_ws('% | ', cast(round(dif_4cnt*100/total_cnt, 2) as string), cast(dif_4cnt as string))
from (
  select p1.state dim_date,
  p1.node_id,
  count(distinct p1.user_id) total_cnt,
  count(distinct if(datediff(p3.state,p1.state) = 1, p1.user_id, null)) dif_1cnt  ,
  count(distinct if(datediff(p3.state,p1.state) = 2, p1.user_id, null)) dif_2cnt  ,
  count(distinct if(datediff(p3.state,p1.state) = 3, p1.user_id, null)) dif_3cnt  ,
  count(distinct if(datediff(p3.state,p1.state) = 4, p1.user_id, null)) dif_4cnt
  from (
    select
    from_unixtime(unix_timestamp(cast(partition_date as string), 'yyyyMMdd'), 'yyyy-MM-dd') state,
    user_id,
    node_id
    from user_active_day
    where partition_date between date1 and date2
    and user_is_new = 1
    group by 1,2,3 --如果不行用字段替换,group by去重优于distinct
  )p1 left outer join (   --日新增用户名单(register_date,user_id)
    select
    from_unixtime(unix_timestamp(cast(partition_date as string), 'yyyyMMdd'), 'yyyy-MM-dd') state,
    user_id,
    node_id
    from active_users
    where partition_date between date1 and date2
    group by 1,2,3
  )p3 on (p3.user_id = p1.user_id and p3.node_id=p1.node_id) --期间活跃用户(active_date,user_id)
group by 1,2
) p4;
  

  #################################################################################

分割线:如上是计算留存率的一种通用方法(如上抄来的,学来的),但当用户和数据量特别大时,或者业务调整频繁时,性能还是非常差。

2020年初接到鑫哥一个任务,计算整体垂类的留存率数据,背景:为LTV等其他指标做基础数据,指导业务发展,要求:支持动态业务调整和查询秒级返回。

通常留存率指标都是作用在新用户上的,首先第一步明确新用户的定义:集团给的规范是一年内未登录的用户即为新用户,所有时间比较系数为365。

调研业界,腾讯的IMEI分析,美团的订单分析,都在探索bitmap,所以尝试使用bitmap完成此需求,开源实现的。

上手比较快的,可以用doris,我厂开源的产品,技术还是不出的,链接:http://doris.apache.org/master/zh-CN/administrator-guide/alter-table/alter-table-bitmap-index.html#%E5%8F%96%E6%B6%88%E4%BD%9C%E4%B8%9A

 https://www.bookstack.cn/read/ApacheDoris-0.12-zh/e46d8c8d445173f6.md

 

初始数据:原始日志表

 

 

中间的结构:

 

 

最终的结构:

CREATE TABLE `pv_bitmap` (
  `dt` int(11) NULL COMMENT "",
  `page` varchar(10) NULL COMMENT "",
  `user_id` bitmap BITMAP_UNION NULL COMMENT ""
) ENGINE=OLAP
AGGREGATE KEY(`dt`, `page`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`dt`) BUCKETS 2;

  

 

 

 

 

 

 

posted @ 2020-09-04 12:11  费元星的博客  阅读(29)  评论(0编辑  收藏
未来星开发团队的官网