一种通过构建周期快照事实表优化用户回流、流失统计指标的方法

目录

1 需求说明

2 数据探索

3 模型优化

3.1  7天流失用户

3.2  7天回流用户

4 ADS层模型设计

5 小结

---

1 需求说明

该需求包括两个指标，分别为流失用户数和回流用户数，以下为对两个指标的解释说明。

统计周期	指标	说明
最近1日（每天的新增流失用户数）	流失用户数	之前活跃过的用户，最近一段时间未活跃，就称为流失用户。此处要求统计7日前（只包含7日前当天）活跃，但最近7日未活跃的用户总数。
最近1日（每天的新增回流用户数）	回流用户数	之前的活跃用户，一段时间未活跃（流失），今日又活跃了，就称为回流用户。此处要求统计回流用户总数。

2 数据探索

      （1）数据调研，该指标可以从用户登录表计算

       （2）直接计算

第一步计算：7天前登录的用户
第二步计算：计算近7天登录的用户
7天前登录的用户
left join 近7天登录的用户

用户登录表数据量非常大，如果这样直接关联相当于大表关联，性能相当差

3 模型优化

3.1 7天流失用户

定义：7日前用户活跃，但最近7日未活跃。换句话说就是用户超过7天未登录APP，如果用户最近一次登录时间为7天前，也就是该用户超过7天未登录。所以我们构建用户登录收敛表。结构如下：

user_id	用户id
login_date_last	用户最近一次登录日期

此时只需要在DWS层维护构建一张用户历史至今的末次登录时间周期快照表

表设计

命名：dws_user_user_login_td

td:截止至今

user:数据域

user_login:表示用户登录事实。

具体建表如下：

DROP TABLE IF EXISTS dws_user_user_login_td;
CREATE EXTERNAL TABLE dws_user_user_login_td
(
    `user_id`         STRING COMMENT '用户id',
    `login_date_last` STRING COMMENT '末次登录日期',
    `login_count_td`  BIGINT COMMENT '累计登录次数'
) COMMENT '用户域用户粒度登录历史至今汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    TBLPROPERTIES ('orc.compress' = 'snappy');

那么流失用户根据以上分析可以进行如下计算：

select
    count(*)
from dws_user_user_login_td
where dt =current_date
and login_date_last=date_add(current_date,-7);

总结：流失用户特征，用户最近一次登录时间距离当天已经达到7天

因此我们可以定义7日流失，14日流失，30日流失，90天流失，方法一致

3.2 7天回流用户

定义： 7天前未活跃，今日活跃了

特征：（1）用户两次登录时间间隔7天以上

           （2） 最后登录日期（今日）减去上次登录日期大于等于8的用户

因此问题的难点转换为如何求用户上次登录的日期，通过上述分析我们注意到，回流用户一定是当日的活跃用户，但对于昨天而言该用户还属于流失用户，即昨天的最后登录日期在7天之前

（1）获取当日活跃用户

使用用户历史至今的末次登录时间表

select user_id,
       login_date_last
from dws_user_user_login_td
where dt = current_date
  and login_date_last = current_date;

（2）获取昨日分区的数据

select user_id,
       login_date_last
from dws_user_user_login_td
where dt = date_add(current_date,-1)

(3)  两表join，取登录间隔大于等于8天的用户

select count(*)
from (select user_id,
             login_date_last
      from dws_user_user_login_td
      where dt = current_date
        and login_date_last = current_date) t1
join
     (select user_id,
             login_date_last
      from dws_user_user_login_td
      where dt = date_add(current_date, -1)
) t2
     on t1.user_id = t2.user_id
where datediff(t1.login_date_last,t2.login_date_last)>=8

4 ADS层模型设计

建表如下：

DROP TABLE IF EXISTS ads_user_change;
CREATE EXTERNAL TABLE ads_user_change
(
    `dt`               STRING COMMENT '统计日期',
    `user_churn_count` BIGINT COMMENT '流失用户数',
    `user_back_count`  BIGINT COMMENT '回流用户数'
) COMMENT '用户变动统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

两个指标合并：采用UNION ALL

select 
       dt,
       sum(user_churn_count) user_churn_count,
       sum(user_back_count) user_back_count
from (
    select
           current_date dt,
           count(*) user_churn_count,
		   null user_back_count
      from
           dws_user_user_login_td
      where dt = current_date
        and login_date_last = date_add(current_date, -7)
union all
    select
           current_date dt,
		   null user_churn_count,
           count(*) user_back_count
from (
    select
             user_id,
             login_date_last
      from
           dws_user_user_login_td
      where dt = current_date
        and login_date_last = current_date) t1
         inner join
     (
         select
             user_id,
             login_date_last
      from
           dws_user_user_login_td
      where dt = date_add(current_date, -1)
         ) t2
     on t1.user_id = t2.user_id
where datediff(t1.login_date_last,t2.login_date_last)>=8
)
group by dt

5 小结

 本文分析了一种通过数仓建模的形式来优化指标计算的方式，针对用户回流及流失这两个指标进行优化，通过对指标的分析，在DWS层构建周期快照事实表，维护用户历史至今末次登录时间周期快照表，简化了指标的计算，提升了性能。