最近做一个关于流失周期的计算,连续多少天用户不登陆APP才算用户流失呢,首先我们无法统计到用户是否卸载APP数据,只能根据用户登陆信息来判断用户是否流失
- 第一步:统计用户的最近2次登陆的时间差
方法1如下SQL:
SELECT
方法2如下SQL:
SELECT A.uid,MAX(A.logdate),DATEDIFF(A.logdate,B.logdate) AS ndays
FROM
(
SELECT a.uid,a.logdate,(@i := @i + 1) as ord_num
FROM jzb_login_users_open a,
(SELECT @i := 1) d
WHERE a.logdate>'2019-05-26'
ORDER BY uid ASC,logdate DESC
) AS A LEFT JOIN
(
SELECT a.uid,a.logdate,(@j := @j + 1) as ord_num
FROM jzb_login_users_open a,
(SELECT @j := 0) c
WHERE a.logdate>'2019-05-26'
ORDER BY uid ASC,logdate DESC
) AS B on A.ord_num=B.ord_num and A.uid=B.uid
GROUP BY A.uid
- 第二步:不同活跃天数的用户累计分布图可视化
说明:用户在59天时的累计概率达到79.21%,也就是在60天以后用户再回访的概率只有20.79%