需求:
我们漏斗分析中定义的需求如下:
注册-> 点击新闻-> 进入详情页-> 发布评论转换成事件:
SignUp -> AppClick[element_page=‘新闻列表页’] ->
AppClick[element_page=‘内容详情页’]->NewsAction[action_type=‘评论’]接下来我们用SQL实现这个需求:
我们来查询 20201227到20201230 事件范围内,并且窗口时间是3天的漏斗注意:我们这里数据就三天,所以窗口期也就是不用判断,但是我们以后可能会拿到N天数据,所以要加窗口期判断
sql实现:
-- 分析sql,首先我们可以先把每一个事件的数据按照条件查询出来,然后在将每一个事件中的时间拿到,进行关联查询,通过时间进行判断该事件是否在窗口期以内,并且还要和上一个事件判断,一定要大于它
-- 拿到三天内每一个事件数据
with t1 as(
select
distinct_id,
ctime,
event
from ods_db_news.event
where event='SignUp'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
),
t2 as(
select
distinct_id,
ctime,
event
from ods_db_news.event
where event='AppClick' and element_page='新闻列表页'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
),
t3 as(
select
distinct_id,
ctime,
event
from ods_db_news.event
where event='AppClick' and element_page='内容详情页'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
),
t4 as(
select
distinct_id,
ctime,
event
from ods_db_news.event
where event='NewsAction' and action_type='评论'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') >='20201201'
and format_datetime(from_unixtime(ctime/1000),'yyyyMMdd') <='20201230'
)
select
count(distinct t1.distinct_id) step1,
count(distinct t2.distinct_id) step2,
count(distinct t3.distinct_id) step3,
count(distinct t4.distinct_id) step4
from t1
left join t2
on t1.distinct_id=t2.distinct_id
and t1.ctime<t2.ctime and t2.ctime-t1.ctime<86400*3*1000
left join t3
on t2.distinct_id=t3.distinct_id
and t2.ctime<t3.ctime and t3.ctime-t1.ctime<86400*3*1000
left join t4
on t3.distinct_id=t4.distinct_id
and t3.ctime<t4.ctime and t4.ctime-t1.ctime<86400*3*1000;
结果:
执行上述查询可以看到如下类似结果
step1 | step2 | step3 | step4
-------±------±------±------
3154 | 79 | 2 | 1
代表着我们的漏斗的每一步的人数