分享

大数据项目之电商数仓(总结)(四):系统业务数据仓库

问题导读:
1、什么是GMV?
2、什么是转化率?
3、如何计算新增用户占日活跃用户比率?
4、如何使用用户行为做漏斗分析?


上一篇:大数据项目之电商数仓(总结)(三):系统业务数据仓库

第4章 需求一:GMV成交总额
4.1 ADS层
4.1.1 什么是GMV


2021-01-13_193648.jpg

4.1.2 建表语句
  1. hive (gmall)>
  2. drop table if exists ads_gmv_sum_day;
  3. create external table ads_gmv_sum_day(
  4.     `dt` string COMMENT '统计日期',
  5.     `gmv_count`  bigint COMMENT '当日gmv订单个数',
  6.     `gmv_amount`  decimal(16,2) COMMENT '当日gmv订单总金额',
  7.     `gmv_payment`  decimal(16,2) COMMENT '当日支付金额'
  8. ) COMMENT 'GMV'
  9. row format delimited fields terminated by '\t'
  10. location '/warehouse/gmall/ads/ads_gmv_sum_day/';
复制代码



2021-01-13_193724.jpg

4.1.3 数据导入
1)数据导入
  1. hive (gmall)>
  2. insert into table ads_gmv_sum_day
  3. select
  4. '2019-02-10' dt,
  5.     sum(order_count) gmv_count,
  6.     sum(order_amount) gmv_amount,
  7.     sum(payment_amount) payment_amount
  8. from dws_user_action
  9. where dt ='2019-02-10'
  10. group by dt;
复制代码

2)查询导入数据
  1. hive (gmall)> select * from ads_gmv_sum_day;
复制代码


4.1.4 数据导入脚本
1)在/home/kgg/bin目录下创建脚本ads_db_gmv.sh
  1. [kgg@hadoop102 bin]$ vim ads_db_gmv.sh
  2.     在脚本中填写如下内容
  3. #!/bin/bash
  4. # 定义变量方便修改
  5. APP=gmall
  6. hive=/opt/module/hive/bin/hive
  7. # 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
  8. if [ -n "$1" ] ;then
  9.     do_date=$1
  10. else
  11.     do_date=`date -d "-1 day" +%F`
  12. fi
  13. sql="
  14. insert into table "$APP".ads_gmv_sum_day
  15. select
  16.     '$do_date' dt,
  17.     sum(order_count)  gmv_count,
  18.     sum(order_amount) gmv_amount,
  19.     sum(payment_amount) payment_amount
  20. from "$APP".dws_user_action
  21. where dt ='$do_date'
  22. group by dt;
  23. "
  24. $hive -e "$sql"
复制代码


2)增加脚本执行权限
  1. [kgg@hadoop102 bin]$ chmod 777 ads_db_gmv.sh
复制代码


3)执行脚本导入数据
  1. [kgg@hadoop102 bin]$ ads_db_gmv.sh 2019-02-11
复制代码


4)查看导入数据
  1. hive (gmall)>
  2. select * from ads_gmv_sum_day where dt='2019-02-11' limit 2;
复制代码

第5章 需求二:转化率之用户新鲜度及漏斗分析

5.1 什么是转化率


2021-01-13_193800.jpg
5.2 ADS层之新增用户占日活跃用户比率(用户新鲜度)
2021-01-13_193834.jpg

5.2.1 建表语句
  1. hive (gmall)>
  2. drop table if exists ads_user_convert_day;
  3. create external table ads_user_convert_day(
  4.     `dt` string COMMENT '统计日期',
  5.     `uv_m_count`  bigint COMMENT '当日活跃设备',
  6.     `new_m_count`  bigint COMMENT '当日新增设备',
  7.     `new_m_ratio`   decimal(10,2) COMMENT '当日新增占日活的比率'
  8. ) COMMENT '转化率'
  9. row format delimited fields terminated by '\t'
  10. location '/warehouse/gmall/ads/ads_user_convert_day/';
复制代码


5.2.2 数据导入
1)数据导入
  1. hive (gmall)>
  2. insert into table ads_user_convert_day
  3. select
  4.     '2019-02-10',
  5.     sum(uc.dc) sum_dc,
  6.     sum(uc.nmc) sum_nmc,
  7.     sum( uc.nmc)/sum( uc.dc)*100 new_m_ratio
  8. from
  9. (
  10.     select
  11.         day_count dc,
  12.         0 nmc
  13.     from ads_uv_count
  14. where dt='2019-02-10'
  15.     union all
  16.     select
  17.         0 dc,
  18.         new_mid_count nmc
  19.     from ads_new_mid_count
  20.     where create_date='2019-02-10'
  21. )uc;
复制代码

2)查看导入数据
  1. hive (gmall)>
  2. select * from ads_user_convert_day;
复制代码

5.3 ADS层之用户行为漏斗分析


2021-01-13_193913.jpg

2021-01-13_193940.jpg

5.3.1 建表语句
  1. hive (gmall)>
  2. drop table if exists ads_user_action_convert_day;
  3. create external  table ads_user_action_convert_day(
  4.     `dt` string COMMENT '统计日期',
  5.     `total_visitor_m_count`  bigint COMMENT '总访问人数',
  6.     `order_u_count` bigint     COMMENT '下单人数',
  7.     `visitor2order_convert_ratio`  decimal(10,2) COMMENT '访问到下单转化率',
  8.     `payment_u_count` bigint     COMMENT '支付人数',
  9.     `order2payment_convert_ratio` decimal(10,2) COMMENT '下单到支付的转化率'
  10. ) COMMENT '用户行为漏斗分析'
  11. row format delimited  fields terminated by '\t'
  12. location '/warehouse/gmall/ads/ads_user_action_convert_day/';
复制代码



5.3.2 数据导入
1)数据导入
  1. hive (gmall)>
  2. insert into table ads_user_action_convert_day
  3. select
  4.     '2019-02-10',
  5.     uv.day_count,
  6.     ua.order_count,
  7.     cast(ua.order_count/uv.day_count as  decimal(10,2)) visitor2order_convert_ratio,
  8.     ua.payment_count,
  9.     cast(ua.payment_count/ua.order_count as  decimal(10,2)) order2payment_convert_ratio
  10. from  
  11. (
  12. select
  13.     dt,
  14.         sum(if(order_count>0,1,0)) order_count,
  15.         sum(if(payment_count>0,1,0)) payment_count
  16.     from dws_user_action
  17. where dt='2019-02-10'
  18. group by dt
  19. )ua join ads_uv_count  uv on uv.dt=ua.dt;
复制代码

2)查询导入数据
  1. hive (gmall)> select * from ads_user_action_convert_day;
复制代码

最新经典文章,欢迎关注公众号



没找到任何评论,期待你打破沉寂

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

推荐上一条 /2 下一条