当前位置：首页 > news >正文

使用sql计算每天新增用户的ltv1、ltv2以及次留

news 2024/10/31 14:44:30

1.题目：使用sql计算每天新增用户的ltv1、ltv2以及次留

表名：login;

字段

usercreateymd string #新增日期

accountid string #账号ID

day_pay string #账号在dt当天的充值

logymd string #日期

次留定义：新增第二天有登录，比如20180623新增，在20180624有登录则认为该账号有次留

LTV定义：sun(新增前N天的充值)/sum(账号数)，比如20180623的新增用户，计算LTV1就是sum(20180623的新增用户在当日总充值)/sum(20180623新增用户数)，LTV2是sum(20180623的新增用户在20180623、20180624两日总充值)/sum(20180623新增用户数)

结果输出：

20180623 当日新增账号数次日还有登录的账号数 LTV1 LTV2

20180622 当日新增账号数次日还有登录的账号数 LTV1 LTV2

2.以下使用mysql实现

首先，代码使用了CTE（WITH语句）来创建了四个临时表。

1.new_users表计算了每天的新增用户数。它从login表中统计了每个用户创建账号的日期，并计算每天新增用户的数量。

2.retention_users表计算了次留用户数（第二天仍然有登录的用户数）。它使用了自连接查询，将用户在前一天登录的记录和后一天登录的记录进行比较，统计了每天的次留用户数

3.ltv1表计算了LTV1指标，即首日付费总额除以新增用户数。它从login表中筛选出当天的付费记录，并计算了每天的首日付费总额。

4.ltv2表计算了LTV2指标，即首日和次日付费总额除以新增用户数。它从login表中筛选出当天和次日的付费记录，并计算了每天的首日和次日付费总额。

最后，查询语句将以上四个表按照日期进行了连接，并选择了需要显示的字段。结果按照日期降序排列。

总结：

new_users表计算了每天的新增用户数。
retention_users表计算了每天的次留用户数。
ltv1表计算了每天的LTV1指标。
ltv2表计算了每天的LTV2指标。

3.完整代码

-- 计算每天新增用户的次留和LTV指标
WITH 
-- 计算每天的新增用户数
new_users AS (SELECT usercreateymd,COUNT(DISTINCT accountid) as new_user_countFROM login GROUP BY usercreateymd
),
-- 计算次留用户数（第二天还有登录的用户数）
retention_users AS (SELECT l1.usercreateymd,COUNT(DISTINCT l2.accountid) as retention_countFROM login l1LEFT JOIN login l2 ON l1.accountid = l2.accountid AND l1.usercreateymd = l2.usercreateymdAND l2.logymd = DATE_ADD(l1.usercreateymd, INTERVAL 1 DAY)GROUP BY l1.usercreateymd
),
-- 计算LTV1（首日付费总额/新增用户数）
ltv1 AS (SELECT usercreateymd,SUM(day_pay)/COUNT(DISTINCT accountid) as ltv1_valueFROM login WHERE logymd = usercreateymdGROUP BY usercreateymd
),
-- 计算LTV2（首日+次日付费总额/新增用户数）
ltv2 AS (SELECT usercreateymd,SUM(day_pay)/COUNT(DISTINCT accountid) as ltv2_valueFROM login WHERE logymd <= DATE_ADD(usercreateymd, INTERVAL 1 DAY)GROUP BY usercreateymd
)-- 最终结果
SELECT n.usercreateymd,n.new_user_count as '当日新增账号数',r.retention_count as '次日还有登录的账号数',ROUND(l1.ltv1_value, 2) as 'LTV1',ROUND(l2.ltv2_value, 2) as 'LTV2'
FROM new_users n
LEFT JOIN retention_users r ON n.usercreateymd = r.usercreateymd
LEFT JOIN ltv1 l1 ON n.usercreateymd = l1.usercreateymd
LEFT JOIN ltv2 l2 ON n.usercreateymd = l2.usercreateymd
ORDER BY n.usercreateymd DESC;

建表及测试数据

-- 创建登录表
CREATE TABLE login (usercreateymd DATE,accountid VARCHAR(50),day_pay DECIMAL(10,2),logymd DATE
);-- 插入一些示例数据
INSERT INTO login (usercreateymd, accountid, day_pay, logymd) VALUES
-- 2018-06-22的新增用户
('2018-06-22', 'user1', 100.00, '2018-06-22'),  -- 首日登录+充值
('2018-06-22', 'user1', 50.00, '2018-06-23'),   -- 次日登录+充值
('2018-06-22', 'user2', 200.00, '2018-06-22'),  -- 首日登录+充值
('2018-06-22', 'user2', 0.00, '2018-06-23'),    -- 次日登录无充值
('2018-06-22', 'user3', 0.00, '2018-06-22'),    -- 首日登录无充值
-- 2018-06-23的新增用户
('2018-06-23', 'user4', 150.00, '2018-06-23'),  -- 首日登录+充值
('2018-06-23', 'user4', 75.00, '2018-06-24'),   -- 次日登录+充值
('2018-06-23', 'user5', 300.00, '2018-06-23'),  -- 首日登录+充值
('2018-06-23', 'user5', 0.00, '2018-06-24');    -- 次日登录无充值

4.日期为YYYYMMDD格式

-- 创建登录表
CREATE TABLE login (usercreateymd VARCHAR(8),    -- YYYYMMDD格式accountid VARCHAR(50),day_pay DECIMAL(10,2),logymd VARCHAR(8)           -- YYYYMMDD格式
);-- 插入一些示例数据
INSERT INTO login (usercreateymd, accountid, day_pay, logymd) VALUES
-- 20180622的新增用户
('20180622', 'user1', 100.00, '20180622'), 
('20180622', 'user1', 50.00, '20180623'), 
('20180622', 'user2', 200.00, '20180622'),  
('20180622', 'user2', 0.00, '20180623'),   
('20180622', 'user3', 0.00, '20180622'),    
-- 20180623的新增用户
('20180623', 'user4', 150.00, '20180623'), 
('20180623', 'user4', 75.00, '20180624'),   
('20180623', 'user5', 300.00, '20180623'), 
('20180623', 'user5', 0.00, '20180624');   -- SQL查询
WITH NewUserStats AS (-- 计算每日新增用户数SELECT usercreateymd,COUNT(DISTINCT accountid) as new_usersFROM loginGROUP BY usercreateymd
),
RetentionStats AS (-- 计算次日留存用户数SELECT l1.usercreateymd,COUNT(DISTINCT l2.accountid) as retained_usersFROM login l1LEFT JOIN login l2 ON l1.accountid = l2.accountid AND l1.usercreateymd = l2.usercreateymd AND l2.logymd = DATE_FORMAT(DATE_ADD(STR_TO_DATE(l1.usercreateymd, '%Y%m%d'), INTERVAL 1 DAY), '%Y%m%d')WHERE l1.usercreateymd = l1.logymdGROUP BY l1.usercreateymd
),
LTVStats AS (-- 计算LTV1和LTV2SELECT usercreateymd,-- LTV1: 新增当天的总充值/新增用户数SUM(CASE WHEN logymd = usercreateymd THEN day_pay ELSE 0 END) as ltv1_total,-- LTV2: 新增当天和次日的总充值/新增用户数SUM(CASE WHEN logymd <= DATE_FORMAT(DATE_ADD(STR_TO_DATE(usercreateymd, '%Y%m%d'), INTERVAL 1 DAY), '%Y%m%d')THEN day_pay ELSE 0 END) as ltv2_totalFROM loginGROUP BY usercreateymd
)
SELECT n.usercreateymd as create_date,n.new_users as new_user_count,IFNULL(r.retained_users, 0) as next_day_active_users,ROUND(IFNULL(l.ltv1_total / n.new_users, 0), 2) as ltv1,ROUND(IFNULL(l.ltv2_total / n.new_users, 0), 2) as ltv2
FROM NewUserStats n
LEFT JOIN RetentionStats r ON n.usercreateymd = r.usercreateymd
LEFT JOIN LTVStats l ON n.usercreateymd = l.usercreateymd
ORDER BY n.usercreateymd DESC;

查看全文

http://www.mrgr.cn/news/62497.html