查询N日登录和次N日留存率

查询用户次N日登录和次N日留存率

下面查询用到两张表，一张是用户表users,一张是用户登录表user_logins
users表只记录用户创建， user_logins表记录用户登录

-- 计算某日新增用户留存率（次日、3日、4日、...）
SELECT 
    *,
    CONCAT(ROUND(100*次日留存用户/日新增用户数,2),'%')  '次日留存率',
    CONCAT(ROUND(100*三日留存用户/日新增用户数,2),'%')  '三日留存率',
    CONCAT(ROUND(100*四日留存用户/日新增用户数,2),'%')  '四日留存率'
FROM 
(    
    SELECT 
    t.`dt` '日期',
    t.channel_id '渠道',
    COUNT(t.id)  '日新增用户数',
    COUNT(t1.user_id)  '次日留存用户',
    COUNT(t2.user_id)  '三日留存用户',
    COUNT(t3.user_id)  '四日留存用户'
    FROM 
    (
        SELECT dt, id, channel_id FROM users
    ) t
    LEFT JOIN user_logins t1 ON t.id = t1.user_id AND DATEDIFF(t1.`time`,t.`dt`) = 1
    LEFT JOIN user_logins t2 ON t.id = t2.user_id AND DATEDIFF(t2.`time`,t.`dt`) = 2
    LEFT JOIN user_logins t3 ON t.id = t3.user_id AND DATEDIFF(t3.`time`,t.`dt`) = 3
    GROUP BY t.dt,t.channel_id
) p WHERE 日期 BETWEEN '2021-09-15' AND '2021-09-17'  AND 渠道 = '0';

如果users数据太多，导致查询慢的话，条件可以附加在查询users表后面。

SELECT dt, id, channel_id FROM users WHERE dt BETWEEN '2021-09-15' AND '2021-09-17'  AND channel_id = '0';

查询用户的留存率，流失率，流失增率

查询思路

1. 首先查询到步骤1的记录
2. 左连接查询到步骤2的记录
3. 统计到每个步骤的人数
4. 计算流失人数，步骤1-步骤2
5. 计算留存率，步骤2/步骤1
6. 计算流失率，1-(步骤2/步骤1)
7. 计算流失增率, (1-(步骤2人数/步骤1人数)) - (1-(步骤1人数/步骤1人数))

SELECT *,
    CONCAT(步骤1人数-步骤2人数) AS '步骤2流失人数',
    CONCAT(步骤1人数-步骤3人数) AS '步骤3流失人数',
    CONCAT(ROUND(100*步骤2人数/步骤1人数, 2), "%") AS '步骤2留存率',
    CONCAT(ROUND(100*步骤3人数/步骤1人数, 2), "%") AS '步骤3留存率',
    CONCAT(ROUND(100*(1-(步骤2人数/步骤1人数)), 2), "%") AS '步骤2留失率',
    CONCAT(ROUND(100*(1-(步骤3人数/步骤1人数)), 2), "%") AS '步骤3留失率',
    CONCAT(ROUND(100*((1-(步骤2人数/步骤1人数)) - (1-(步骤1人数/步骤1人数))),2), "%") AS '步骤2留失增率',
    CONCAT(ROUND(100*((1-(步骤3人数/步骤1人数)) - (1-(步骤2人数/步骤1人数))),2), "%") AS '步骤3留失增率'
    FROM 
    ( 
        SELECT  
            t1.`time` '日期',
            t1.channel_id '渠道',
            COUNT(DISTINCT(t1.device_id))  '步骤1人数',
            COUNT(DISTINCT(t2.device_id))  '步骤2人数',
            COUNT(DISTINCT(t3.device_id))  '步骤3人数'
            FROM 
            ( 
                SELECT 
                    `time`, 
                    step_type, 
                    device_id,
                    channel_id
                    FROM 
                    user_funnels 
                    WHERE 
                    step_type = 1  
                    AND `time` = '2021-09-22'

            ) AS t1 
            LEFT JOIN user_funnels t2 ON t1.device_id = t2.device_id AND t2.step_type - t1.step_type = 1
            LEFT JOIN user_funnels t3 ON t1.device_id = t3.device_id AND t3.step_type - t1.step_type = 2
            LEFT JOIN user_funnels t4 ON t1.device_id = t4.device_id AND t4.step_type - t1.step_type = 3
            GROUP BY t1.`time`,t1.channel_id
) AS p WHERE 渠道 = 'c3002';

本作品采用《CC 协议》，转载必须注明作者和本文链接