什么是漏斗模型
漏斗模型是一套流程式数据分析模型，用来反映用户在流程里的关键行为以及从起点到终点个阶段转化和流失情况
漏斗分析模型已经广泛应用于用户行为分析类产品，且功能十分强大：它可以评估总体或各个环节的转化情况、促销活动效果；也可以与其他数据分析模型结合进行深度用户行为分析（如多维下钻分析、用户分群、对比分析等），从而找到用户流失的原因，以提升用户量、活跃度、留存率。
漏斗分析需求开发
分析师（报表提需求方）定义了一种业务路径，用户沿着这个路径上的各个步骤，不断走向业务目标； 路径上的各个步骤，人数通常是会逐步递减，形如一个漏斗；把这种分析形象地称呼为：漏斗模型分析；
比如，分析师定义了一个业务路径：淘宝购物
步骤1： 搜索（搜索词为：手机）
步骤2： 点击了一个搜索结果中的商品
步骤3： 将搜索结果中的商品添加到了购物车
步骤4： 提交订单
步骤5： 支付订单
比如，分析师定义了一个业务路径：淘宝秒杀
步骤1： 点击了秒杀运营位广告
步骤2： 点击了广告落地页中的促销商品
步骤3： 订阅了这个商品的秒杀通知
了解行为日志表中需要涉及的字段
select deviceid ,eventid , properties,timestamp from dwd.event_log_detail设备编号,行为名称,触发行为附带的属性,触发行为的时间戳
deviceid,eventid,properties,timestamp
注意：上述表数据量比较大，且数据为随机生成，不方便演示，所以创建一张表测试表
1、创建一张测试表，对应数仓中dwd.event_log_detail表，方便漏斗主题测试
create table tmp_event_log_detail(
deviceid string,
eventid string,
properties map<string,string>,
ts bigint
)partitioned by (dt string)
row format delimited fields terminated by ',' -- 列于列之间使用,
collection items terminated by '_' -- 集合中元素与元素之间分隔符
map keys terminated by ':' -- map集合中k和v之间的分隔符添加测试数据
deviceid1,display,k1:v1_k2:v2,1001
deviceid1,addCart,k1:v1_k2:v2,1002
deviceid1,order,k1:v1_k2:v2,1003
deviceid1,pay,k1:v1_k2:v2,1004
deviceid2,display,k1:v1_k2:v2,1002
deviceid2,addCart,k1:v1_k2:v2,1003
deviceid2,order,k1:v1_k2:v2,1004
deviceid2,pay,k1:v1_k2:v2,1005
deviceid3,display,k1:v1_k2:v2,1001
deviceid3,addCart,k1:v1_k2:v2,1002
deviceid3,order,k1:v1_k2:v2,1003
deviceid4,display,k1:v1_k2:v2,1001
deviceid4,addCart,k1:v1_k2:v2,1002
deviceid5,display,k1:v1_k2:v2,1001
deviceid5,addCart,k1:v1_k2:v2,1002
deviceid6,search,k1:v1_k2:v2,1001
deviceid6,display,k1:v1_k2:v2,1002
deviceid7,lauch,k1:v1_k2:v2,1001
deviceid7,display,k1:v1_k2:v2,1002
load data local inpath '/root/loudou.txt' into table tmp_event_log_detail partition(dt='2022-11-25')2、漏斗主题dws层表设计
分析师提出漏斗模型，包含event1-->event2-->event3-->event4
dispaly -> addCart --> order --> pay
问题1：如何从行为日志中，找出漏斗中包含的事件
select * from tmp_event_log_detail
where (eventid = 'display' or eventid = 'addCart' or
eventid = 'order' or eventid = 'pay')
and dt = '2022-11-25'问题2：漏斗中需要统计出每个事件触发的次数，才能统计每一步到每一步到比例
数据可以保证某个设备出现event3事件之前，一定存在event1、event2事件的话
可以直接通过设备编号分组，count统计每个行为的次数
如果触发event3事件的情况有多种，那么就需要从代码层统计漏斗中规定的路径轨迹
a)、用户购买路径分析漏斗表
create table dws.user_buy_funnel(
deviceid string, -- 设备编号(用户id)
funnel_name string, -- 漏斗名称 [自定义]
max_step int, -- 最大完成该漏斗的步骤
funnel_starttime string, -- 漏斗统计数据的窗口开始时间
funnel_endtime string -- 漏斗统计数据的窗口结束时间
)partitioned by (dt string)设备编号,漏斗名称,最大完成该漏斗的步骤,漏斗统计数据的窗口开始时间,漏斗统计数据的窗口结束时间
deviceid1,购物分析,3,2022-11-25,2022-11-25
deviceid2,购物分析,2,2022-11-25,2022-11-25
deviceid3,购物分析,4,2022-11-25,2022-11-25
------------- 【dws.user_buy_funnel】 ---------------------
测试-1漏斗的行为轨迹：display -> addCart -> order -> pay
开发思路： 1.内层查询
根据设备编号分组，将时间戳和事件名称拼接 1001_display 将一个设备的多个行为存储到数组中,并且排序 [1001_display,1002_addCart,1003_order,1004_pay] 将数组中元素按照：分隔符拼接成一个字符串 1001_display:1002_addCart:1003_order:1004_pay
2.外层查询
insert overwrite table dws.user_buy_funnel partition(dt='2022-11-25')
select
deviceid,'测试1' funnel_name,
case when regexp_extract(x1,'\\w*(display):\\w*(addCart):\\w*(order):\\w*(pay)',4) = 'pay' then 4
when regexp_extract(x1,'\\w*(display):\\w*(addCart):\\w*(order)',3) = 'order' then 3
when regexp_extract(x1,'\\w*(display):\\w*(addCart)',2) = 'addCart' then 2
when regexp_extract(x1,'\\w*(display)',1) = 'display' then 1
else 0 end max_step,
'2022-11-25' funnel_starttime,
'2022-11-25' funnel_endtime
from(
select deviceid,
concat_ws(':',sort_array(collect_list(concat(ts,'_',eventid)))) x1
from tmp_event_log_detail
where dt = '2022-11-25'
and (eventid = 'display' or eventid = 'addCart' or eventid = 'order' or eventid = 'pay')
group by deviceid
)t1在dws层创建漏斗聚合结果表
create table dws.user_funnel_aggr(
funnel_name string, -- 漏斗名称
step int, -- 漏斗的步骤数
user_count int, -- 完成到该步骤的用户数
funnel_starttime string, -- 漏斗统计数据的窗口开始时间
funnel_endtime string -- 漏斗统计数据的窗口结束时间
)partitioned by (dt string);with t1 as (
select funnel_name,
count(if(max_step>=1,1,null)) x1,
count(if(max_step>=2,1,null)) x2,
count(if(max_step>=3,1,null)) x3,
count(if(max_step>=4,1,null)) x4,
'2022-11-25' funnel_starttime,
'2022-11-25' funnel_endtime
from dws.user_buy_funnel
where funnel_name = '测试1'
group by funnel_name
)
insert overwrite table dws.user_funnel_aggr partition(dt='2022-11-25')
select funnel_name,1,x1 user_count, funnel_starttime,funnel_endtime from t1
union all
select funnel_name,2,x2 user_count, funnel_starttime,funnel_endtime from t1
union all
select funnel_name,3,x3 user_count, funnel_starttime, funnel_endtime from t1
union all
select funnel_name,4,x4 user_count, funnel_starttime,funnel_endtime from t1;a) with里面的子查询结果
funnel_name x1 x2 x3 x4
测试1 6 5 3 2
b) 使用union all 完成列转行
funnel_name step user_count
测试1 1 6
测试1 2 5
测试1 3 3
测试1 4 2
3、在ads层创建漏斗转化率表
create table ads.funnel_rate(
funnel_name string , -- 漏斗名称
step int , -- 步骤
converted_user int , -- 转化人数
conversion_rate double , -- 转化率
completion_rate double , -- 总转化率
funnel_starttime string, -- 漏斗统计数据的窗口开始时间
funnel_endtime string -- 漏斗统计数据的窗口结束时间
)partitioned by (dt string);insert into table ads.funnel_rate partition(dt ='2022-11-25')
select funnel_name,step,user_count converted_user,
round(user_count/
lag(user_count,1,user_count) over(partition by funnel_name order by step)*100,2) conversion_rate,
round(user_count/
first_value(user_count) over(partition by funnel_name order by step)*100,2) completion_rate,
'2022-11-25' funnel_starttime,
'2022-11-25' funnel_endtime
from dws.user_funnel_aggr;
漏斗名称 步骤 转化人数 转化率 总转化率 funnel_starttime funnel_endtime
测试1 1 1000 100% 100% 2023-09-10 2023-09-13
测试1 2 300 30% 30% 2023-09-10 2023-09-13
测试1 3 100 33.33% 10% 2023-09-10 2023-09-13
测试1 4 10 10% 1% 2023-09-10 2023-09-13指标口径：转化率=当前步骤转化人数/上一步转化人数
总转化率=当前步骤转化人数/第一步转化人数
第一步的转化率、总转化率：100%
扫一扫
636
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
