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

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

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

相关文章:

  • Oracle 数据库历史备份数据恢复验证
  • 【简道云 -注册/登录安全分析报告】
  • 【ANTs】医疗影像工具ANTs多种安装方式教程和使用
  • 51c多模态~合集1
  • 开源生活-分布式管理
  • 零售EDI:HornBach EDI 项目案例
  • 批量删除redis数据【亲测可用】
  • 校园社团信息管理:Spring Boot技术的优势与实现
  • 期货跟单、量化交易模拟演示系统
  • Abaqus自己构建材料库导入材料库
  • AUTOSAR CP中的CDD复杂驱动介绍
  • Javaweb梳理3——SQL概述+DDL语句1
  • SpringBoot旋律:打造现代Web音乐平台
  • 【专题】2024年金融数字化转型白皮书报告汇总PDF洞察(附原数据表)
  • 从“死敌”到“盟友”,英特尔和AMD世纪大“和解”!
  • 不再输入单号查快递,批量查快递单号信息的新方法,智能排序快递时效并查找时效相同的单号,一站式物流查询解决方案
  • 从零开始设计简易Queue:底层原理与实现
  • 【传知代码】检测图像P图痕迹(论文复现)
  • SpringBoot和弦:创建Web音乐网站指南
  • LeetCode每日一题3165---不包含相邻元素的子序列的最大和
  • Springboot3.3 + Mybatis / Mybatis-plus
  • Python虚拟显示器pyvirtualdisplay
  • 这个AI植物整活创意项目,操作起来没难度!
  • 特斯联巨亏数十亿:毛利率剧烈波动下滑,高管动荡引发关注
  • [vulnhub] SecTalks:BNE0x00 - Minotaur
  • 安信金控:K金,金店回收吗?