Hive漏斗分析（计算转换率）

漏斗分析 就是算转换率如：业务相对转换率、绝对转换率
例：用户打开首页——>搜索——>详情页——>加购物车——>下订单——>支付
一系列操作的后的转换率

定义漏斗：
1.活动广告曝光事件：eventid="adShowEvent" ad_id='5'
2.支付点击事件 eventid="appClickEvent" element_id='1'
3.活动参与 eventid="appClickEvent" element_id='6'
4.活动广告点击事件：eventid="adClickEvent" ad_id='5'
5.订单支付页面 eventid="appviewEvent" screen_id='600'

--创建漏斗概况报表：
hive>create table ADS_APP_FUNNEL_OVW_DEMO(
funnel_name string,
stepno      int,
user_counts int,
rel_ratio   double,
abs_ratio   double
)
partitioned by (dt string)
stored as orc;


---实例代码：如何获取人数，一个人可能点击多次广告
hive>with stepone as(
select 
guid
from DWD_APP_GLB_DTL_DEMO
where dt='2023-03-01' and eventid='adShowEvent' and event['adId']='5'
group by guid
)
from stepone
select 
'demo_funnel' as demo_funnel,
'1' as stepno,
count(1) as user_counts

结果:
+--------------+---------+--------------+--+
| demo_funnel  | stepno  | user_counts  |
+--------------+---------+--------------+--+
| demo_funnel  | 1       | 1            |
+--------------+---------+--------------+--+






--完整代码
hive>with stepone as(
select 
guid
from DWD_APP_GLB_DTL_DEMO
where dt='2023-03-01' and eventid='adShowEvent' and event['adId']='5'
group by guid
),
steptwo as(
select 
guid
from DWD_APP_GLB_DTL_DEMO
where dt='2023-03-01' and eventid='appClickEvent' and event['element_id']='1'
group by guid
),
stepthree as(
select 
guid
from DWD_APP_GLB_DTL_DEMO
where dt='2023-03-01' and eventid='appClickEvent' and event['element_id']='6'
group by guid
),
stepfour as(
select 
guid
from DWD_APP_GLB_DTL_DEMO
where dt='2023-03-01' and eventid='adClickEvent' and event['adId']='5'
group by guid	
),
stepfive as(
select 
guid
from DWD_APP_GLB_DTL_DEMO
where dt='2023-03-01' and eventid='appviewEvent' and event['screen_id']='600'
group by guid
),
result as(
from stepone
select 
'demo_funnel' as demo_funnel,
'1' as stepno,
count(1) as user_counts

union all

from steptwo
select 
'demo_funnel' as demo_funnel,
'2' as stepno,
count(1) as user_counts

union all

from stepthree
select 
'demo_funnel' as demo_funnel,
'3' as stepno,
count(1) as user_counts

union all

from stepfour
select 
'demo_funnel' as demo_funnel,
'4' as stepno,
count(1) as user_counts

union all

from stepfive
select 
'demo_funnel' as demo_funnel,
'5' as stepno,
count(1) as user_counts
)
select demo_funnel,stepno,user_counts,
if(stepno=1,1,user_counts/lag(user_counts,1,null) over(partition by demo_funnel order by stepno)) as rel_ratio,
user_counts/first_value(user_counts) over(partition by demo_funnel order by stepno) as abs_ratio
from result;