Mysql查询用户留存/留存率问题--用户n日(内)留存、某日新增用户n日(内)留存

来源:数据运营与数据分析

Mysql查询用户留存/留存率语法

  • 计算某日的客户在第n日再次出现的概率--用户n日留存率。

  • 计算某日的客户在某个时间段内再次出现的概率--用户n日内留存率。

  • 计算某日新增的用户在第n日再次出现的概率--新用户n日留存率。

  • 计算某日新增的用户在某个时间段内再次出现的概率--新用户n日内留存率。

1.用户n日留存/留存率

计算某日的客户在第n日再次出现的概率--用户n日留存率。

  • 表名:订单总表;字段:客户编号,下单时间

-- 1.用户n日留存/留存率
-- 用户次日、3日、7日、30日,...留存/留存率

select *,
 concat(round(100*次日留存用户/日活跃用户数,2),'%')  次日留存率,
 concat(round(100*三日留存用户/日活跃用户数,2),'%')  三日留存率,
 concat(round(100*七日留存用户/日活跃用户数,2),'%')  七日留存率
from (
 select 
  date(a.下单时间) as 日期,
  count(distinct a.客户编号) as 日活跃用户数,
  count(distinct b.客户编号) as 次日留存用户,
  count(distinct c.客户编号) as 三日留存用户,
  count(distinct d.客户编号) as 七日留存用户
 from 订单总表 a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and date(c.下单时间) = date(a.下单时间) + 3
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and date(d.下单时间) = date(a.下单时间) + 7
 where date(a.下单时间) between "2020/09/01" and "2020/09/05" 
 group by date(a.下单时间)
) p;

2.用户n日内留存/留存率

计算某日的客户在某个时间段内再次出现的概率--用户n日内留存率。

-- 2.用户n日内留存/留存率
-- 用户次日内、3日内、7日内、30日内,...留存/留存率

select *,
 concat(round(100*次日内留存用户/日活跃用户数,2),'%')  次日内留存率,
 concat(round(100*三日内留存用户/日活跃用户数,2),'%')  三日内留存率,
 concat(round(100*七日内留存用户/日活跃用户数,2),'%')  七日内留存率
from (
 select 
  date(a.下单时间) as 日期,
  count(distinct a.客户编号) as 日活跃用户数,
  count(distinct b.客户编号) as 次日内留存用户,
  count(distinct c.客户编号) as 三日内留存用户,
  count(distinct d.客户编号) as 七日内留存用户
 from 订单总表 a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and (date(c.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 3)
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and (date(d.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 7)
 where date(a.下单时间) between "2020/09/01" and "2020/09/05" 
 group by date(a.下单时间)
) p;

3.新增用户n日留存/留存率

计算某日新增的用户在第n日再次出现的概率--新用户n日留存率。

## 3.某日新增用户留存/留存率
## 计算某日新增的用户在次日、3日、7日的留存率--新用户n日留存

select *,
 concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
 concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
 concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率
from (
 select 
  date(a.下单时间) as 新增日期,
  count(distinct a.客户编号) as 日新增用户数,
  count(distinct b.客户编号) as 次日留存用户,
  count(distinct c.客户编号) as 三日留存用户,
  count(distinct d.客户编号) as 七日留存用户
 from 
 (
  select  
   date(t1.下单时间) as 下单时间,
   t1.客户编号 
  from 订单总表 as t1
   left join 订单总表 as t2
   on t1.客户编号=t2.客户编号
   and t2.下单时间<t1.下单时间
  where 
   (date(t1.下单时间) between "2020/09/01" and "2020/09/05") 
  and t2.下单时间 is Null  
 ) as a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and date(c.下单时间) = date(a.下单时间) + 3
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and date(d.下单时间) = date(a.下单时间) + 7
 group by date(a.下单时间)
) p;

4.新增用户n日内留存率

计算某日新增的用户在次日内、3日内、7日内的留存率--新用户n日内留存率。

## 4.新增用户n日内留存率
## 计算某日新增的用户在次日内、3日内、7日内的留存率--新用户n日内留存率。
select *,
 concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
 concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
 concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率
from (
 select 
  date(a.下单时间) as 新增日期,
  count(distinct a.客户编号) as 日新增用户数,
  count(distinct b.客户编号) as 次日留存用户,
  count(distinct c.客户编号) as 三日留存用户,
  count(distinct d.客户编号) as 七日留存用户
 from 
 (
  select  
   date(t1.下单时间) as 下单时间,
   t1.客户编号 
  from 订单总表 as t1
   left join 订单总表 as t2
   on t1.客户编号=t2.客户编号
   and t2.下单时间<t1.下单时间
  where 
   (date(t1.下单时间) between "2020/09/01" and "2020/09/05") 
  and t2.下单时间 is Null  
 ) as a 
  left join 订单总表 b 
   on a.客户编号 = b.客户编号 
   and date(b.下单时间) = date(a.下单时间) + 1
  left join 订单总表 c 
   on a.客户编号 = c.客户编号 
   and (date(c.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 3)
  left join 订单总表 d 
   on a.客户编号 = d.客户编号 
   and (date(d.下单时间) BETWEEN date(a.下单时间) + 1 AND date(a.下单时间) + 7)
 group by date(a.下单时间)
) p;

  • 4
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值