常见的“间隔”场景及分析思路

填充空白 2022-05-15

293

背景：

在日常分析用户行为时，经常会碰到想要知道，比如:

每隔多久会购买一次某物品；每隔多少天会发生一次购物；每隔多久会使用某功能；

每次进入某APP看几个商品会下单；每隔多久资金会回转；

某事发生多久后会发生另一个事件，等等看起来是2个事件或多个事件之间间隔时长，间隔事件次数的分析。

针对用户的的“需求周期”可以针对性的做触达，或激励，想消费者所想，并帮助提升转化。

上海疫情居家60天。仅以此图致敬阳光灿烂的日子

—

常见的“间隔”场景

本文针对诸如以上的“间隔分析”场景做以下总结：

「每隔多少天发生某事件」-回购周期-间隔天数(间隔时长)
「每隔多少天发生某事件」—同一天购买多次算一次-间隔天数(间隔时长)
「每隔多少天发生某事件」—连续多天发生算一次--间隔天数(间隔时长)
「每发生多少个A，会发生B事件」。每次购买前浏览商品次数---间隔次数
「连续发生B后会经历多少天A再次发生B」,「连续发生B后会经历多少个A再次发生B」
「每发生A后，发生多少个C，会发生B」。每个包含A和C的回合中，有多少个B发生
「连续多少天发生某事件」
「发生A事件后，多久发生B事件」。最后一个A往后找最近的一个B。视频播放时长。时长次均值。人均值。
「发生A事件后，多久发生B事件」。第一个A往后找最近的一个B。
「发生A事件后，多久发生B事件」。每一个A往后找最近的一个B。取平均时长为间隔时长。

喜欢大海的包容，看一下海，心潮澎湃

—

“间隔”场景详情及分析思路

1.「每隔多少天发生某事件」-回购周期-间隔天数(间隔时长)

场景：距上次购买多少天后发生购买，计算回购周期天数或时长。

思路：1).找到每个A。

2).每个A事件发生的时间及后一个A事件发生的时间，并计算时间差(这里计算日期差)。

select d.rebuycircle,count(distinct d.user_id) as user_amounts
from
(select c.user_id,round(avg(days),0) as rebuycircle
from
  (select b.user_id,datediff(b.lastpurtime,b.time) as days 
   from
     (select tt.*,LEAD(tt.time)over(partition by tt.user_id order by tt.time) as lastpurtime   
      from 
        (select t.user_id,t.time,t.order_id,a.pay_freq
        from
          (select user_id,time,event,order_id from events where event='PayOrder' order by time)t  left join
          (select user_id,event,count(order_id) as pay_freq  from events  where event='PayOrder' group by user_id,event having count(order_id)>=2) a
        on t.user_id=a.user_id 
     where a.user_id is not null)tt
order by tt.user_id,tt.time) b
)c
group by c.user_id) d
group by d.rebuycircle
order by d.rebuycircle

2.「每隔多少天发生某事件」—同一天购买多次算一次-间隔天数(间隔时长)

场景：距上次购买多少天后发生购买，计算回购周期天数或时长。同一天可能购买多次，可能是因为多次下单，当天购买不代表当天需求多次。同一天购买多次算一次需求，计算需求周期。

思路：1).找到用户发生的A事件和日期，按照用户，事件和日期去重。

2).每个A事件发生的日期及后一个A事件发生的日期，并计算时间差。

##回购天数。同一天购买多次算一次-购买的间隔天数。
select d.rebuycircle,count(distinct d.user_id) as user_amounts
from
(select c.user_id,round(avg(days),0) as rebuycircle
from
  (select b.user_id,datediff(b.lastpurtime,b.date) as days 
   from
     (select tt.*,LEAD(tt.date)over(partition by tt.user_id order by tt.date) as lastpurtime   
      from 
        (select t.user_id,t.date,a.pay_freq
        from
          (select distinct user_id,date,event from events where event='PayOrder')t  left join
          (select user_id,event,count(distinct date) as pay_freq  from events  where event='PayOrder' 
           group by user_id,event having count(distinct date)>=2) a
        on t.user_id=a.user_id 
     where a.user_id is not null)tt
order by tt.user_id,tt.date) b
)c
group by c.user_id) d
group by d.rebuycircle
order by d.rebuycircle

3.「每隔多少天发生某事件」—连续多天发生算一次--间隔天数(间隔时长)

场景：用户需求间隔。某跨境电商ERP系统中，用户平均多少天会再次使用发货功能，供应链模块。此种场景可能会连续多天使用某功能，当连续多天使用时按一次需求。计算对某功能的需求周期。

思路：1).找到A事件，计算每2个A事件之间的间隔天数。

2).筛选出间隔天数>1时，认为是再次有需求。

3).取每用户每次需求的平均间隔天数，为该用户的需求周期。

select a.user_id,round(avg(datediff),0) as rebuycircle from 
(select a.user_id,a.date,datediff from 
(select 
a.user_id,a.date,
lead(a.date,1) over ( partition by  a.user_id order by a.date asc),
datediff(lead(a.date,1) over ( partition by  a.user_id order by a.date asc ),date) as datediff
from 
(select distinct user_id,date,event from events where event='PayOrder' order by user_id,date)a
)a where datediff>1)a
group by a.user_id

4.「每发生多少个A，会发生B事件」。每次购买前浏览商品次数---间隔次数

场景：每次购买前需要浏览多少次商品。

思路：1).找到每个B。

2).计算每个B前发生的A的总次数。

3).每个B减去前一个B发生A的总次数，差即为每次发生B前A的次数

select
a.user_id,a.num,if(a.num=1,a.num1,a.num1-b.num2) as cishu from
(select b.user_id,b.num,count(a.user_id) as num1
from 
(Select user_id,event,time
from events where event='CommodityDetail')a
right join


(Select user_id,event,time, count(*) OVER(PARTITION BY user_id ORDER BY time asc) as num
from events where event='PayOrder'
)b
on a.user_id=b.user_id and a.time<b.time
group by b.user_id,b.num order by  b.user_id,b.num asc)a


left join
(select b.user_id,b.num,count(a.user_id) as num2
from 
(Select user_id,event,time
from events where event='CommodityDetail')a
right join


(Select user_id,event,time, count(*) OVER(PARTITION BY user_id ORDER BY time asc) as num
from events where event='PayOrder'
)b
on a.user_id=b.user_id and a.time<b.time


group by b.user_id,b.num order by  b.user_id,b.num asc)b


on a.user_id=b.user_id and a.num=b.num+1
order by a.user_id,a.num asc

5.「连续发生B后会经历多少天A再次发生B」,「连续发生B后会经历多少个A再次发生B」

资产周期天数～用户每次从低于100回转的周期天数

B后第一个A开始，和A出现后首个B的时间间隔。其实还是BB之间的间隔天数。只是初始取第一个B后的A时间。

思路1：1).找到每个B，并计算B后每隔间隔时长。

2).找到每隔B后出现的A的次数。

3).筛选出B后A次数大于0的B的间隔天数

思路2：见资产周期天数。SQL计算资产周期天数难么？只需要6步？

#思路1#
select a.user_id,avg(datediff) as avg_date
from 
(select a.user_id,a.stat_date,a.end_date,a.datediff,count(b.user_id)
from 
(select a.user_id,a.stat_date ,a.user_asset_total,
lead(a.stat_date,1) over ( partition by  a.user_id order by a.stat_date asc) as end_date,
datediff(lead(a.stat_date,1) over ( partition by  a.user_id order by a.stat_date asc ),stat_date)+1 as datediff
from 
(select a.user_id,a.stat_date as stat_date ,a.user_asset_total from 
(select user_id,stat_date,user_asset_total,if(user_asset_total>100,1,0) as 'is_100'
from asset a)a
where a.is_100=1)a)a


left join 
(select a.user_id,a.stat_date,a.user_asset_total from 
(select user_id,stat_date,user_asset_total,if(user_asset_total>100,1,0) as 'is_100'
from asset a)a
where a.is_100=0)b
on  b.user_id=a.user_id and b.stat_date>=a.stat_date and b.stat_date<=a.end_date
group by a.user_id,a.stat_date,a.end_date,a.datediff having count(b.user_id)>0)a
group by a.user_id

6.「每发生A后，发生多少个C，会发生B」。每个包含A和C的回合中，有多少个B发生

场景：某咖啡/快餐APP，每次启动/进入菜单后，浏览多少个商品后下单。每个包含A和C的回合中，有多少个B发生

思路：1).先找到每个A去排序。

2).再找每2个A之间的B的最小时间。

3).再找有ab的，之间的C的数量

select d.user_id,avg(c_cishu) as avg_cishu from
(select ab.user_id,ab.a_time,ab.b_time,count(c.user_id) as c_cishu from
(select a.user_id,a.a_time,min(b_time) as b_time from 
(select user_id,event,time as a_time, row_number() over (PARTITION BY user_id ORDER BY time ASC) as row_num,
 lead(time,1) OVER (PARTITION BY user_id ORDER BY time ASC) as a_time_end  from events where event='$AppStart')a
 left join
 (select user_id,event,time as b_time from events where event='PayOrder')b
  on a.user_id=b.user_id and b.b_time<a_time_end and b.b_time>a_time
  where b.b_time is not null
  group by a.user_id,a.a_time)ab


  left join 
 (select user_id,event,time as c_time from events where event='CommodityDetail')c
   on ab.user_id=c.user_id and c.c_time<ab.b_time and c.c_time>ab.a_time
  group by ab.user_id,ab.a_time)d
    group by d.user_id

7.「连续多少天发生某事件」

连续多少天发生A事件的用户及天数。

#连续2天及以上购物的用户及其天数#
select  user_id,ymd-rn,count(*)  dayscount
from
（select user_id, datediff(date,'1970-01-01’) ymd,
row_number()over (partition by user_id order by date) rn
From events)a
Group by user_id,ymd-rn
having count(*)>1

8.「发生A事件后，多久发生B事件」。最后一个A往后找最近的一个B。视频播放时长。时长次均值。人均值。

场景：观看视频时，发生视频开始，暂停，开始，开始，恢复，结束等事件。每次在暂停或结束时计算时长，如果在结束或暂停时前边一个事件也是暂停或结束，则时长不计算。诸如此类的ABCB事件，可转化为ABAB事件。当ABAABBAB发生时,只考虑2个不交叉的AB，从A到B之间的时长。

思路：1).找到AB事件。

2).计算AB之间的时长，且限定开始事件为A结束事件为B。

3).计算每用户每次时长的均值或视频时长观看人均值。

select a.user_id,round(sum(timediff)/60) as sum_mins,round(avg(timediff)/60) as avg_mins
from 
(select
a.user_id,event,time,timestampdiff(second,a.start_time,time) as timediff
from 
(select user_id,event,time,
lag(event,1) over (partition by user_id order by time) as start_event,
lag(time,1) over (partition by user_id order by time) as start_time
from 
(select user_id,event,time from events2 where event in ('start' ,'end'))a)a
where start_event='start' and event='end')a group by a.user_id

9.「发生A事件后，多久发生B事件」。第一个A往后找最近的一个B。

场景：首次发生B的时间间隔。比如首次点击「立即开户」后多久完成「开户成功」。首次登录到首次付费的时间间隔。

思路：1).找到用户发生的第一个A事件，第一个B事件。

2).计算AB的时长。

select a.user_id,
a.start_time,b.end_time,
timestampdiff(second,a.start_time,b.end_time) as timediff,
datediff(a.start_time,b.end_time) as datediff
from
(select user_id,min(time) as start_time from events2 where event='start' group by user_id)a
left join
(select user_id,min(time) as end_time   from events2  where event='end' group by user_id)b
on a.user_id=b.user_id

10.「发生A事件后，多久发生B事件」。每一个A往后找最近的一个B。取平均时长为间隔时长

思路：1).找到用户发生的每一个A事件，

2).找到A后发生的最近的B事件，并计算每一个AB的时长。

3).取到同一个B的所有AB的时长的均值，为A事件到B的时长间隔。

select a.user_id,round(avg(timediff)) as avgtimediff from 
(select a.user_id,end_time,avg(timediff) as timediff
from 
(#计算每个A到最近的B的时长#
select a.user_id,end_time,timestampdiff(second,a.start_time,end_time) as timediff
from 
(select a.user_id,a.start_time,min(end_time) as end_time
from
(select user_id,time as start_time from events2 where event='start' )a
left join
(select user_id,time as end_time from events2 where event='end')b
on a.user_id=b.user_id and b.end_time>a.start_time group by a.user_id,a.start_time)a
#计算每个A到最近的B的时长#
)a
group by a.user_id,end_time)a group by a.user_id

欢迎一起探讨好玩的分析和查询命题，或者数据驱动增长的话题，有兴趣可私聊加微：joy691508。疫情之下，愿大家心情愉悦～望上海早日解封～

用户分析计算天数 date select asc

文章转载自填充空白，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

常见的“间隔”场景及分析思路

2.「每隔多少天发生某事件」—同一天购买多次算一次-间隔天数(间隔时长)

4.「每发生多少个A，会发生B事件」。每次购买前浏览商品次数---间隔次数

评论