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

﹏ヽ暗。殇╰゛Y 2022-09-17 01:57 370阅读 0赞

### GMV和转化率 ###

*  GMV主题
 *   *  GMV的概念
     *  GMV表的创建
     *  GMV表里导入数据
 *  转化率
 *   *  转化率概念
     *  转化率表的创建
     *  转化率表里导入数据
     *  ADS层用户行为漏斗分析

# 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' 
    ;

用户行为漏斗类似如下图:  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_15_color_FFFFFF_t_70_g_se_x_16]

[-]: https://blog.csdn.net/weixin_43586713/article/details/120912360
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5qKm6YeMQ29kaW5n_size_15_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/f0cda1ec8c734c83a3cebf2c8370e045.png