电商离线数仓-业务数仓指标(GMV主题/转化率主题)

GMV主题

GMV的概念

什么是GMV?所谓的GMV指的是电商平台在一段时间内的成交总额。它是电商领域比较重要的一个指标。它实际上指的是拍下订单金额,包含付款和未付款的部分。

GMV表的创建

创建GVM表,这个表里面主要是涉及到当日的GMV,因此包含的字段是当日的日期,订单的个数,当日的GMV订单的总金额,当日支付的金额。

drop table if exists ads_gmv_sum_day;
create table ads_gmv_sum_day( 
    `dt` string COMMENT '统计日期',
    `gmv_count`  bigint COMMENT '当日gmv订单个数',
    `gmv_amount`  decimal(16,2) COMMENT '当日gmv订单总金额',
    `gmv_payment`  decimal(16,2) COMMENT '当日支付金额' 
) COMMENT '每日活跃用户数量'
row format delimited  fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_gmv_sum_day/'
;

这个表里面的数据当日的订单个数,订单总金额,支付总金额,这些数据都在用户行为的宽表当中,具体可以看我写的这篇文章:电商离线数仓项目-用户每日行为宽表(重点!!)这篇文章介绍了宽表的具体数据和字段。
宽表的字段如下:

create external table dws_user_action(
    user_id string comment '用户id',
    order_count bigint  comment '下单次数',
    order_amount decimal(16,2) comment '下单金额',
    payment_count bigint comment '支付次数',
    payment_amount decimal(16,2) comment '支付金额',
    comment_count bigint comment '评论次数'
)comment '每日用户行为宽表'
partitioned by (`dt` string)
location '/warehouse/gmall/dws/dws_user_action';

GMV表里导入数据

那我们的GMV主要是以日期为主,所以说以日期为主键进行groupby统计当天的订单个数,订单的总金额,支付的总金额。SQL语句比较好写,如下:

hive (gmall)>
insert into table ads_gmv_sum_day 
select 
	'2019-02-10' dt ,
    sum(order_count)  gmv_count ,
    sum(order_amount) gmv_amount ,
    sum(payment_amount) payment_amount 
from dws_user_action 
where dt ='2021-06-22'
group by dt
;

转化率

转化率概念

在统计分析指标当中,经常会提到转化率,具体转化率如何定义,看的是转化的目标。

最常见的口径:实际下单的用户在单日总活跃用户中的比例。

也就是: 单日消费用户数/单日日活数量

其他的比如:

新访问用户转化率=单日新访问设备数/日活数(也就是新增用户mid就能搞定)
新注册用户转化率=单日新注册用户数/日活数(需要登录,也就是user_id)
新付费用户转化率=单日新付费用户数/日活数(支付的用户)

转化率表的创建

以日期为主,一个当日活跃数量,一个当日新增设备数量,还有一个两者相除的字段

drop table if exists ads_user_convert_day;
create   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/'
;

转化率表里导入数据

从用户活跃表和用户新增表里直接

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;

注意:在写union all的时候,其中第一个表的字段一定要去挂别名,不挂别名就会报错。

ADS层用户行为漏斗分析

创建表:

hive (gmall)>
drop table if exists ads_user_action_convert_day;
create   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_convert_day/'
;

数据导入:

hive (gmall)>
insert into table ads_user_action_convert_day
select 
    '2021-06-22',
    uv.day_count,
    ua.order_count,
    cast(ua.order_count/uv.day_count*100 as  decimal(10,2)) visitor2order_convert_ratio, 
    ua.payment_count,
    cast(ua.payment_count/ua.order_count*100 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='2021-06-22' 
)ua, ads_uv_count  uv 
where  uv.dt='2021-06-22' 
;

用户行为漏斗类似如下图: