target:
指标
弹窗的曝光 人数,次数,占首页UV和PV的占比
弹窗给5星好评按钮点击 人数,次数,占弹窗曝光人数或次数的占比
弹窗去反馈按钮点击 人数,次数,占弹窗曝光人数或次数的占比
弹窗关闭按钮点击 人数,次数,占弹窗曝光人数或次数的占比
点击关闭后二次弹出弹窗的曝光 人数,次数,占首页UV和PV的占比
点击关闭后二次弹出弹窗给5星好评按钮点击 人数,次数,占二次弹窗曝光人数或次数的占比
点击关闭后二次弹出弹窗去反馈按钮点击 人数,次数,占二次弹窗曝光人数或次数的占比
点击关闭后二次弹出弹窗关闭按钮点击 人数,次数,占二次弹窗曝光人数或次数的占比
hql;
CREATE TEMPORARY FUNCTION urldecode AS 'com.hiido.hive.udf.URLDecodeUDF';
create TEMPORARY table summary as
select a.uid
,a.dt
,get_json_object(urldecode(a.moreinfo),'$.pop_up_order') pop_up_order
,get_json_object(urldecode(a.moreinfo),'$.function_id') function_id
,coalesce(b.nation,'其他') nation
,coalesce(b.platform,'android') platform
,coalesce(b.ver,'其他') ver
,coalesce(b.user_type,'老用户') user_type
from
(
select uid,ver,moreinfo,dt
from view_huanju.view_other_mbsdkprotocol_original
where eventid='20025727' and dt='${date}'
) a
left join
(
select uid ,nation,ver,
case when appkey='7c74186bd3a1e25806e3e2a11c1f30b7' then 'android' else 'ios' end platform,
case is_new_user when 1 then '新用户' when 0 then '老用户' else is_new_user end as user_type
from eagle.hago_wh_dw_appheartbeat_uid_info_original_day
where dt='${date}'
)b on a.uid=b.uid
;
create TEMPORARY table page_show as
select dt,nation,
coalesce(platform,'all') platform
,coalesce(ver,'all') ver
,coalesce(user_type,'all') user_type
,count(1) pv
,count(distinct uid ) uv
from (
select
a.dt,a.uid
,coalesce(b.nation,'其他') nation
,coalesce(b.platform,'android') platform
,coalesce(b.ver,'其他') ver
,coalesce(b.user_type,'老用户') user_type
from (
select dt,uid
from view_huanju.view_other_mbsdkprotocol_original
where eventid='20023771' and dt='${date}' and get_json_object(urldecode(moreinfo),'$.function_id')='show'
) a left join (
select uid ,nation,ver,
case when appkey='7c74186bd3a1e25806e3e2a11c1f30b7' then 'android' else 'ios' end platform,
case is_new_user when 1 then '新用户' when 0 then '老用户' else is_new_user end as user_type
from eagle.hago_wh_dw_appheartbeat_uid_info_original_day
where dt='${date}'
) b on a.uid=b.uid
) bb
group by dt,nation,platform,ver,user_type
grouping sets (
(dt,nation,platform),
(dt,nation,ver),
(dt,nation,user_type),
(dt,nation,platform,ver),
(dt,nation,platform,user_type),
(dt,nation,ver,user_type),
(dt,nation,platform,ver,user_type),
(dt,nation)
);
create TEMPORARY table results as
select dt ,nation,
coalesce(platform,'all') as platform,
coalesce(ver,'all') as ver,
coalesce(user_type,'all') as user_type,
count(distinct uid) as disuid,
count(uid) as uidnum,
count(distinct if(function_id = 'show',uid,null)) show_uid_num,
count(if(function_id = 'show',uid,null)) as show_cishu_num,
count(distinct if(function_id = 'five_star_click',uid,null)) show_five_num,
count(if(function_id = 'five_star_click',uid,null)) as show_fivecishu_num,
count(distinct if(function_id = 'complain_click',uid,null)) show_complain_num,
count(if(function_id = 'complain_click',uid,null)) as show_complaincishu_num,
count(distinct if(function_id = 'close_click',uid,null)) show_close_num,
count(if(function_id = 'close_click',uid,null)) as show_closecishu_num,
count(distinct if(function_id = 'show' and pop_up_order='2',uid,null)) 2_show_uid_num,
count(if(function_id = 'show' and pop_up_order='2',uid,null)) as 2_show_cishu_num,
count(distinct if(function_id = 'five_star_click' and pop_up_order='2',uid,null)) 2_show_five_num,
count(if(function_id = 'five_star_click' and pop_up_order='2',uid,null)) as 2_show_fivecishu_num,
count(distinct if(function_id = 'complain_click' and pop_up_order='2',uid,null)) 2_show_complain_num,
count(if(function_id = 'complain_click' and pop_up_order='2',uid,null)) as 2_show_complaincishu_num,
count(distinct if(function_id = 'close_click' and pop_up_order='2',uid,null)) 2_show_close_num,
count(if(function_id = 'close_click' and pop_up_order='2',uid,null)) as 2_show_closecishu_num
from summary
group by dt,nation,platform,ver,user_type
grouping sets (
(dt,nation,platform),
(dt,nation,ver),
(dt,nation,user_type),
(dt,nation,platform,ver),
(dt,nation,platform,user_type),
(dt,nation,ver,user_type),
(dt,nation,platform,ver,user_type),
(dt,nation)
);
select
'${date:y-m-d}' dt,
a.nation,a.ver,a.platform,a.user_type,
'人数' data_type,
show_uid_num as uid_num,
show_five_num as five_num,
show_complain_num as complain_num,
show_close_num as close_num,
2_show_uid_num as 2_uid_num,
2_show_five_num as 2_five_num,
2_show_complain_num as 2_complain_num,
2_show_close_num as 2_close_num
from results a
union all
select
'${date:y-m-d}' dt,
a.nation,a.ver,a.platform,a.user_type,
'次数' data_type,
show_cishu_num as uid_num,
show_fivecishu_num as five_num,
show_complaincishu_num as complain_num,
show_closecishu_num as close_num,
2_show_cishu_num as 2_uid_num,
2_show_fivecishu_num as 2_five_num,
2_show_complaincishu_num as 2_complain_num,
2_show_closecishu_num as 2_close_num
from results a
union all
select
'${date:y-m-d}' dt,
a.nation,a.ver,a.platform,a.user_type,
'人数比' data_type,
concat(round(100*show_uid_num/b.uv,2),'%') as uid_num,
concat(round(100*show_five_num/disuid,2),'%') as five_num,
concat(round(100*show_complain_num/disuid,2),'%') as complain_num,
concat(round(100*show_close_num/disuid,2),'%') as close_num,
concat(round(100*2_show_uid_num/disuid,2),'%') as 2_uid_num,
concat(round(100*2_show_five_num/disuid,2),'%') as 2_five_num,
concat(round(100*2_show_complain_num/disuid,2),'%') as 2_complain_num,
concat(round(100*2_show_close_num/disuid,2),'%') as 2_close_num
from results a left join page_show b on a.dt=b.dt and a.nation=b.nation and a.ver=b.ver and a.platform=b.platform and a.user_type =b.user_type
union all
select
'${date:y-m-d}' dt,
a.nation,a.ver,a.platform,a.user_type,
'次数比' data_type,
concat(round(100*show_cishu_num/b.pv,2),'%') as uid_num,
concat(round(100*show_fivecishu_num/uidnum,2),'%') as five_num,
concat(round(100*show_complaincishu_num/uidnum,2),'%') as complain_num,
concat(round(100*show_closecishu_num/uidnum,2),'%') as close_num,
concat(round(100*2_show_cishu_num/uidnum,2),'%') as 2_uid_num,
concat(round(100*2_show_fivecishu_num/uidnum,2),'%') as 2_five_num,
concat(round(100*2_show_complaincishu_num/uidnum,2),'%') as 2_complain_num,
concat(round(100*2_show_closecishu_num/uidnum,2),'%') as 2_close_num
from results a left join page_show b on a.dt=b.dt and a.nation=b.nation and a.ver=b.ver and a.platform=b.platform and a.user_type =b.user_type
;
重要的是gropy set 的作用简化了统计,详见
http://lxw1234.com/archives/2015/04/193.htm
熟悉下logic
结果展示