数仓建设实践——用户留存分析专题模型设计

吵吵叭火

已于 2024-07-27 22:03:38 修改

阅读量613

点赞数 21

分类专栏：数仓建设文章标签：数据仓库

于 2024-07-24 21:47:51 首次发布

本文链接：https://blog.csdn.net/SHWAITME/article/details/140674083

版权

数仓建设专栏收录该内容

54 篇文章 6 订阅

订阅专栏

一、什么是留存分析

留存分析，就是分析用户随时间变化的活跃情况。获取用户只是第一步，留住用户才是所有产品最终目标。可以理解为：由初期的摇摆用户转化成忠诚&稳定用户的过程。留存率越高，说明用户对产品越有强烈的依赖感。大体上可以分为三个阶段：

初期：新用户刚注册，用户留存下降较快，需快速让用户感受到产品核心价值。

中期：新用户沉淀下来，形成活跃用户，此时需要分析活跃留存，加强核心功能，培养用户对产品的使用习惯。

后期：思考产品核心价值，做好产品迭代与优化。

留存分析专题数据模型包含哪些/如何划分?

（1）从用户维度划分;

（2）常见的有:新用户留存、老用户留存;

（3）从时间维度划分；

（4）第N日留存：指的是活跃用户在第N日依然登录的用户占活跃用户的比例;

（5）N日留存：指的是用户在N日内依然登录的用户占活跃用户的比例;

ps：一般我们说的N日留存都是指第N日留存；

（6）常见的时间周期有：次日留存、3日留存、7日留存、30日留存、周留存、月留存

二、模型方案设计

简化分析场景，不考虑用户分组的情况。比如有以下用户访问表(dws_uuid_visit_1d)汇总数据，计算第N日留存，在此我们对留存分析通过3版本建设，对比出最实用场景。

2.1 留存分析1.0

select
   t2.ds ,
   count(distinct t1.uuid  ) as nd_retention_uv
from dws_uuid_visit_1d t1
left join dws_uuid_visit_1d t2
on t1.uuid = t2.uuid
and t2.ds = '{统计日}'
and t1.ds = date_add( t2.ds,'{留存N天}')
group by t2.ds

缺陷：

性能极差，每次计算都要关联历史N天的用户访问数据；
代码质量&稳定性差，统计留存天数变化，代码也要发生相对应的关联调整；

2.2 留存分析2.0

基于以上分析的缺点，代码可以有些改进的空间，比如关联统计，留存分析周期一般不会拉的太长，基本到90天就足够分析了。

select
   t2.ds as start_date ,
   t1.ds as follow_date ,
   datediff( t1.ds, t2.ds) as nd,
   count(distinct t1.uuid  ) as nd_retention_uv
from dws_uuid_visit_1d t1
left join dws_uuid_visit_1d t2
on t1.uuid = t2.uuid
and t2.ds = '{统计日}'
and t1.ds > t2.ds
and  t1.ds <= date_add( t2.ds,90)
group by 
   t2.ds,
   t1.ds

缺点：

此方案虽然解决了代码的稳定性方面，但是性能问题反而放大了。

2.3 留存分析3.0

主要思考如何解决性能问题，固化分析思路参考2.0，比如固化90天的留存，解决性能问题，这个思路主要借鉴离线同步方案每天做增量merge。

初始化一个90位的bit字符串，每位表示用户在当天访问pv，只有在初始化的时候需要扫描90天的数据，初始化工作只需要做一次，后续每天增量进行merge操作即可

（1）初始化90天的用户数据

-- 代码中的nd_init是自定义的udf函数
select
  uuid,
  nd_init(
    sort_aray(collect_list(concat(ds,'|',pv))),
    date_sub('初始化日期',90),
    '初始化日期'
    ) as view_pv_90d
from dws_uuid_visit_1d t1
where ds >= date_sub('初始化日期',90)
and ds <= '初始化日期'
group by uuid

自定义udf (nd_init)的主要功能就是初始化构建等长的bit字符串，这样做的目的是为了方便后续的merge以及留存计算，UDF代码如下：

（2）每天增量数据合并,后续的留存分析都是基于此 nd 模型进行相应的计算即可

（3）自定义udaf (nd_merge）的主要功能就是merge等长的bit字符串

代码如下：

三、总结

模型设计是不断思考的过程，从思考中不断优化找到最优解，此外也可以借助一些olap 引擎做通用的用户留存分析，比如Doris 、StarRocks 等。其聚合模型中支持了Bitmap，可以直接用自带的 udaf函数来分析，可以将dws_uuid_visit_1d 同步到Doris的聚合模型。

四、bitmap_union精确去重

StarRocks中可以借助bitmap_union函数进行去重

以下示例基于一张广告业务相关的明细表 advertiser_view_record，其中记录了点击日期 click_time、广告代码 advertiser、点击渠道 channel 以及点击用户 ID user_id。

CREATE TABLE advertiser_view_record(
    click_time DATE,
    advertiser VARCHAR(10),
    channel VARCHAR(10),
    user_id INT) distributed BY hash(click_time);

该场景需要频繁使用如下语句查询点击广告的UV。

SELECT  advertiser,
        channel,
        count(distinct user_id)FROM advertiser_view_record
GROUP BY advertiser, channel;

如需实现精确去重查询加速，可以基于该明细表创建一张物化视图，并使用bitmap_union()函数预先聚合数据。

CREATE MATERIALIZED VIEW advertiser_uv AS 
SELECT advertiser, 
       channel, 
       bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

物化视图创建完成后，后续查询语句中的子查询 count(distinct user_id) 会被自动改写为 bitmap_union_count(to_bitmap(user_id)) 以便查询命中物化视图。

ps：字段user_id 需要是Int或者bigint类型

【用户留存分析的优化方式】面试考察了N多次

参考文章：

StarRocks最佳实践经验_starrocks索引-CSDN博客

知识星球 | 深度连接铁杆粉丝，运营高品质社群，知识变现的工具