【Spark数仓项目】需求七：漏斗模型分析

写在前面：

拿到dwd层的`event_log_detail`表后，就能根据字段创建`dws.user_buy_funnel`漏斗表了，漏斗表中存放用于记录漏斗的信息。表中包含用户设备编号、漏斗名称、最大步骤数、漏斗统计窗口的开始和结束时间等字段。其中使用分区方式按日期分区。

根据日志明细表中的事件序列判断用户是否完成漏斗的不同步骤的事件，并计算最大完成步骤数。根据设定的漏斗统计窗口时间进行筛选。然后将同一个设备的事件按照时间顺序拼接成一个字符串，用正则表达式进行事件序列的匹配。这个表主要是为了存每个用户进行到了业务的哪个步骤。

接下来创建用户行为漏斗分析聚合表` dws.user_funnel_aggr`，用于统计每个漏斗步骤的用户数量。表中包含漏斗名称、步骤数、用户数量、漏斗统计窗口的开始和结束时间等字段。

首先将数据聚合到临时表 `tmp` 中，并通过多次 `UNION ALL` 操作将每个步骤的统计数据进行行转列，合并到 `dws.user_funnel_aggr` 表中。

最后是ads层，创建漏斗转化率统计表 `ads.funnel_rate`，用于计算漏斗各个步骤的转化率和总转化率。表中包含漏斗名称、步骤、转化人数、转化率、总转化率、漏斗统计窗口的开始和结束时间等字段。

ads层要根据 `dws.user_funnel_aggr` 表中的用户数量进行计算，用到` lag `开窗获取上一步骤的用户数量，再用` first_value `函数获取第一步骤的用户数量，然后使用数学相关函数即可计算转化率。

以上是该需求的整体思路和实现步骤，通过分析用户行为日志明细表，按照指定的漏斗定义进行分析和统计，得到各个步骤的转化人数和转化率，以及整个漏斗的总转化率。

一、准备工作：模仿dwd层的日志明细表创建的测试表

建表语句

create table dwd.tmp_event_log_detail( – dwd.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 dwd.tmp_event_log_detail partition(dt='2022-11-25')

继续插入测试语句

insert into dwd.tmp_event_log_detail values
('deviceid9','display',str_to_map('k1:v1_k2:v2'),1005,'2022-11-25'),
('deviceid9','addCart',str_to_map('k1:v1_k2:v2'),1007,'2022-11-25'),
('deviceid9','display',str_to_map('k1:v1_k2:v2'),1009,'2022-11-25'),
('deviceid9','order',str_to_map('k1:v1_k2:v2'),1012,'2022-11-25'),
('deviceid8','pay',str_to_map('k1:v1_k2:v2'),1013,'2022-11-25')

表格内容

select * from dwd.tmp_event_log_detail

 deviceid     eventid         properties   	ts         dt  	
deviceid10	display	{"k1":"v1","k2":"v2"}	1005	2022-11-25
deviceid10	addCart	{"k1":"v1","k2":"v2"}	1007	2022-11-25
deviceid10	display	{"k1":"v1","k2":"v2"}	1009	2022-11-25
deviceid10	order	{"k1":"v1","k2":"v2"}	1012	2022-11-25

二、漏斗主题dws层表设计

我们要根据用户从浏览页面、加购物车、下单、付款的顺序设计漏斗。
创建了一个漏斗，包含event1–>event2–>event3–>event4

问题1：如何从行为日志中，找出漏斗中包含的事件

select * from dwd.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)

统计结果示例：

------------- 【tmp_event_log_detail】 ---------------------
设备编号    事件名称    	事件属性                时间戳   分区
deviceid1	display		{"pid":"1","k2":"v2"}	1001	 2022-11-25
deviceid1	addCart		{"pid":"1","k2":"v2"}	1002	 2022-11-25
deviceid1	order		{"pid":"1","k2":"v2"}	1003	 2022-11-25


------------- 【dws.user_buy_funnel】 ---------------------
设备编号,漏斗名称,最大完成该漏斗的步骤,漏斗统计数据的窗口开始时间,漏斗统计数据的窗口结束时间
deviceid1,购物分析,3,2022-11-25,2022-11-25
deviceid2,购物分析,2,2022-11-25,2022-11-25
deviceid3,购物分析,4,2022-11-25,2022-11-25

开发思路：

1.内层查询
concat_ws(':',sort_array(collect_list(concat(ts,'_',eventid))))
这段查询会将同一个设备的事件按照时间顺序拼接成一个字符串，并使用冒号作为分隔符，用于后续的分析和处理
根据设备编号分组，将时间戳和事件名称拼接 1001_display
将一个设备的多个行为存储到数组中,并且排序 [1001_display,1002_addCart,1003_order,1004_pay]
将数组中元素按照：拼接成一个字符串 [1001_display:1002_addCart:1003_order:1004_pay]

concat_ws(':', ...)：使用冒号 : 作为分隔符，将后面的表达式结果进行拼接。
sort_array(collect_list(concat(ts,'_',eventid)))：先将每条记录的 ts 和 eventid 进行拼接，得到类似 ts_eventid 的字符串。然后使用 collect_list 函数将每个设备的拼接结果收集到一个列表中。最后，使用 sort_array 函数对列表进行排序，按照时间顺序排列。

deviceid	s1
deviceid1	1001_display:1002_addCart:1003_order:1004_pay
deviceid10	1005_display:1007_addCart:1009_display:1012_order:1013_pay
deviceid2	1002_display:1003_addCart:1004_order:1005_pay
deviceid3	1001_display:1002_addCart:1003_order
deviceid4	1001_display:1002_addCart
deviceid5	1001_display:1002_addCart
deviceid6	1002_display
deviceid7	1002_display
deviceid8	1005_display:1007_addCart:1009_display
deviceid9	1005_display:1007_addCart:1009_display:1012_order

2.然后在外层通过正则表达式匹配拼接后的字符串中的事件序列，然后根据不同的事件序列返回相应的步骤数。

insert overwrite table dws.user_buy_funnel
partition(dt='2022-11-25')
select deviceid,'暑期促销漏斗' funnel_name,
       case when regexp_extract(s1,'.*(display):.*(addCart):.*(order):.*(pay)',4) = 'pay' then 4
            when regexp_extract(s1,'.*(display):.*(addCart):.*(order)',3) = 'order' then 3
            when regexp_extract(s1,'.*(display):.*(addCart)',2) = 'addCart' then 2
            when regexp_extract(s1,'.*(display)',1) = 'display' then 1  end   max_step,
       date_sub('2022-11-25',7) funnel_starttime,
       '2022-11-25'				funnel_endtime
from(
	select 
	      deviceid,concat_ws(':',sort_array(collect_list(concat(ts,'_',eventid)))) s1
	from dwd.tmp_event_log_detail 
	where dt >= date_sub('2022-11-25',7)  and dt <= '2022-11-25'
	      and eventid in ('display','addCart','order','pay')
	group by deviceid
)t1

– 暑期促销漏斗
SELECT * from dws.user_buy_funnel
– 每个人进行到的最大步骤

deviceid  |funnel_name|max_step|funnel_starttime|funnel_endtime|dt        |
----------|-----------|--------|----------------|--------------|----------|
deviceid1 |暑期促销漏斗     |       4|2022-11-18      |2022-11-25    |2022-11-25|
deviceid10|暑期促销漏斗     |       4|2022-11-18      |2022-11-25    |2022-11-25|
deviceid2 |暑期促销漏斗     |       4|2022-11-18      |2022-11-25    |2022-11-25|
deviceid3 |暑期促销漏斗     |       3|2022-11-18      |2022-11-25    |2022-11-25|
deviceid4 |暑期促销漏斗     |       2|2022-11-18      |2022-11-25    |2022-11-25|
deviceid5 |暑期促销漏斗     |       2|2022-11-18      |2022-11-25    |2022-11-25|
deviceid6 |暑期促销漏斗     |       1|2022-11-18      |2022-11-25    |2022-11-25|
deviceid7 |暑期促销漏斗     |       1|2022-11-18      |2022-11-25    |2022-11-25|
deviceid8 |暑期促销漏斗     |       2|2022-11-18      |2022-11-25    |2022-11-25|
deviceid9 |暑期促销漏斗     |       3|2022-11-18      |2022-11-25    |2022-11-25|

b)、

用户行为漏斗分析聚合表

计算经过每个步骤的人数

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 tmp as (
	select funnel_name,funnel_starttime,funnel_endtime,
	       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
	from dws.user_buy_funnel 
	where  dt = '2022-11-25' and funnel_name = '暑期促销漏斗'
	group by funnel_name,funnel_starttime,funnel_endtime
)
insert overwrite table dws.user_funnel_aggr
partition(dt='2022-11-25')
select funnel_name,1 step,x1,funnel_starttime,funnel_endtime
from tmp 
union all 
select funnel_name,2 step,x2,funnel_starttime,funnel_endtime
from tmp 
union all 
select funnel_name,3 step,x3,funnel_starttime,funnel_endtime
from tmp 
union all 
select funnel_name,4 step,x4,funnel_starttime,funnel_endtime
from tmp 

select * from dws.user_funnel_aggr
– 经过每个步骤的人数

暑期促销漏斗	1	10	2022-11-18	2022-11-25	2022-11-25
暑期促销漏斗	2	8	2022-11-18	2022-11-25	2022-11-25
暑期促销漏斗	3	5	2022-11-18	2022-11-25	2022-11-25
暑期促销漏斗	4	3	2022-11-18	2022-11-25	2022-11-25

三、 漏斗转化率统计表

最终需求

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 overwrite 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),4)*100  conversion_rate,
	   round(user_count/first_value(user_count) over(partition by funnel_name order by step),4)*100  completion_rate,
	   funnel_starttime,
	   funnel_endtime
from dws.user_funnel_aggr
where dt = '2022-11-25' and funnel_name = '暑期促销漏斗'

funnel_name|step|converted_user|conversion_rate|completion_rate|funnel_starttime|funnel_endtime|
-----------|----|--------------|---------------|---------------|----------------|--------------|
暑期促销漏斗     |   1|            10|            100|            100|2022-11-18      |2022-11-25    |
暑期促销漏斗     |   2|             8|             80|             80|2022-11-18      |2022-11-25    |
暑期促销漏斗     |   3|             5|           62.5|             50|2022-11-18      |2022-11-25    |
暑期促销漏斗     |   4|             3|             60|             30|2022-11-18      |2022-11-25    |

select funnel_name,step,
       user_count converted_user, first_value(user_count) over(partition by funnel_name order by step)
from dws.user_funnel_aggr
where dt = '2022-11-25' and funnel_name = '暑期促销漏斗'

funnel_name|step|converted_user|first_value_window_0|
-----------|----|--------------|--------------------|
暑期促销漏斗     |   1|            10|                  10|
暑期促销漏斗     |   2|             8|                  10|
暑期促销漏斗     |   3|             5|                  10|
暑期促销漏斗     |   4|             3|                  10|