9（10）第5章需求二：转化率及漏斗分析10

第5章需求二：转化率及漏斗分析
5.2ADS层之新增用户占日活跃用户比率
5.2.1 建表语句

hive (gmall)>
drop table if exists ads_user_convert_day;
create external table ads_user_convert_day(
dt string COMMENT ‘统计日期’,
uv_m_count bigint COMMENT ‘当日活跃设备’,
new_m_count bigint COMMENT ‘当日新增设备’,
new_m_ratio decimal(10,2) COMMENT ‘当日新增占日活的比率’
) COMMENT ‘转化率’
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ads/ads_user_convert_day/’
;
5.2.2数据导入

1）数据导入
hive (gmall)>
insert into table ads_user_convert_day
select
‘2019-02-10’,
sum(uc.dc) sum_dc,
sum(uc.nmc) sum_nmc,
cast(sum( uc.nmc)/sum( uc.dc)*100 as decimal(10,2)) new_m_ratio
from
(
select
day_count dc,
0 nmc
from ads_uv_count
where dt=‘2019-02-10’
union all
select
0 dc,
new_mid_count nmc
from ads_new_mid_count
where create_date=‘2019-02-10’
)uc;
2）查看导入数据
hive (gmall)>
select * from ads_user_convert_day;

5.3ADS层之用户行为漏斗分析
5.3.1 建表语句

hive (gmall)>
drop table if exists ads_user_action_convert_day;
create external table ads_user_action_convert_day(
dt string COMMENT ‘统计日期’,
total_visitor_m_count bigint COMMENT ‘总访问人数’,
order_u_count bigint COMMENT ‘下单人数’,
visitor2order_convert_ratio decimal(10,2) COMMENT ‘访问到下单转化率’,
payment_u_count bigint COMMENT ‘支付人数’,
order2payment_convert_ratio decimal(10,2) COMMENT ‘下单到支付的转化率’
) COMMENT ‘用户行为漏斗分析’
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ads/ads_user_action_convert_day/’
;
5.3.2 数据导入

1）数据导入
hive (gmall)>
insert into table ads_user_action_convert_day
select
‘2019-02-10’,
uv.day_count,
ua.order_count,
cast(ua.order_count/uv.day_count as decimal(10,2)) visitor2order_convert_ratio,
ua.payment_count,
cast(ua.payment_count/ua.order_count as decimal(10,2)) order2payment_convert_ratio
from
(
select
sum(if(order_count>0,1,0)) order_count,
sum(if(payment_count>0,1,0)) payment_count
from dws_user_action
where dt=‘2019-02-10’
)ua, ads_uv_count uv
where uv.dt=‘2019-02-10’
;
2）查询导入数据
hive (gmall)> select * from ads_user_action_convert_day;