如何使用MaxCompute制作电商漏斗图

背景

漏斗模型是通过产品各项数据的转化率来判断产品运营情况的工具。转化漏斗则是通过各阶段数据的转化，来判断产品在哪一个环节出了问题，然后不断优化产品。电商漏斗模型，用户购买商品的路径，从浏览商品到支付订单的每一个环节的转化。本文将展示从用户「浏览-点击-购买」环节做漏斗分析及展示。

前提条件

为了完成本文案例，您需要以下条件：

开通日志服务

开通MaxCompute

开通Dataworks

开通Quick BI

案例

1. 业务架构图

以下是电商系统的业务架构图，展示了数据从采集到计算再到展示的全过程。

2. 业务流程

具体流程如下：

通过阿里云日志服务采集日志数据。

日志服务的数据同步至大数据计算服务MaxCompute。

MaxCompute进行离线计算。

通过阿里云Quick BI进行数据可视化展示。

3. 准备工作

将日志服务采集的数据增量同步到MaxCompute分区表中（本文案例以时间天为单位，展示每个环节的转化率）。

通过Dataworks设置定时调度执行，每天凌晨定时取前一天的数据，计算以天为单位的转化率漏斗图。

4. 数据建模

以下是相关数据表的建模说明：

表1：日志源表（ods_user_trans_d）

CREATE TABLE IF NOT EXISTS ods_user_trans_d (
    md5 STRING COMMENT '用户uid的md5值前8位',
    uid STRING COMMENT '用户uid',
    ts BIGINT COMMENT '用户操作时间戳',
    ip STRING COMMENT 'ip地址',
    status BIGINT COMMENT '服务器返回状态码',
    bytes BIGINT COMMENT '返回给客户端的字节数',
    device_brand STRING COMMENT '设备品牌',
    device STRING COMMENT '终端型号',
    system_type STRING COMMENT '系统类型，Android、IOS、ipad、Windows_phone',
    customize_event STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览/评论',
    use_time BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时，包含该列'
)
PARTITIONED BY (dt STRING --以dt作为时间分区，单位为天。);

表2：ODS层表（ods_user_trace_data）

CREATE TABLE IF NOT EXISTS ods_user_trace_data (
    md5 STRING COMMENT '用户uid的md5值前8位',
    uid STRING COMMENT '用户uid',
    ts BIGINT COMMENT '用户操作时间戳',
    ip STRING COMMENT 'ip地址',
    status BIGINT COMMENT '服务器返回状态码',
    bytes BIGINT COMMENT '返回给客户端的字节数',
    device_brand STRING COMMENT '设备品牌',
    device STRING COMMENT '终端型号',
    system_type STRING COMMENT '系统类型，Android、IOS、ipad、Windows_phone',
    customize_event STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览/评论',
    use_time BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时，包含该列'
)
PARTITIONED BY (dt STRING --以dt作为时间分区，单位为天。);

表3：明细粒度事实层（dw_user_trace_data）

CREATE TABLE IF NOT EXISTS dw_user_trace_data (
    uid STRING COMMENT '用户uid',
    device_brand STRING COMMENT '设备品牌',
    device STRING COMMENT '终端型号',
    system_type STRING COMMENT '系统类型，Android、IOS、ipad、Windows_phone',
    customize_event STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览/评论',
    use_time BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时，包含该列'
)
PARTITIONED BY (dt STRING --以dt作为时间分区，单位为天。);

表4：结果表（rpt_user_trace_data）

CREATE TABLE IF NOT EXISTS rpt_user_trace_data (
    browse STRING COMMENT '浏览量',
    click STRING COMMENT '点击量',
    purchase STRING COMMENT '购买量',
    browse_rate STRING COMMENT '浏览转化率',
    click_rate STRING COMMENT '点击转化率'
)
PARTITIONED BY (dt STRING --以dt作为时间分区，单位为天。);

5. 业务逻辑

以下是插入数据的SQL语句：

insert OVERWRITE table rpt_user_trace_data 
PARTITION (dt=${bdp.system.bizdate})
SELECT 
    browse as 浏览量,
    click as 点击量,
    purchase as 购买量,
    concat(round((click/browse)*100,2),'%') as 点击转化率,
    concat(round((purchase/click)*100,2),'%') as 购买转化率
from 
(
    SELECT dt,count(1) browse from dw_user_trace_data 
    where customize_event='browse' 
    and dt = ${bdp.system.bizdate} 
    group by dt) a
left JOIN
(
    select dt,count(1) click from dw_user_trace_data 
    where customize_event='click' 
    and dt = ${bdp.system.bizdate} 
    group by dt) b
on a.dt=b.dt
left JOIN
(
    select dt,count(1) purchase from dw_user_trace_data 
    where customize_event='purchase' 
    and dt = ${bdp.system.bizdate} 
    group by dt) c
on  a.dt=c.dt
;

6. 结果

以下是漏斗图的展示结果：

7. 数据可视化展示

通过Quick BI创建网站用户分析画像的仪表板，实现该数据表的可视化。从上图中我们可以发现，浏览到点击中的业务量呈现了明显的缩减的趋势，转化率较低。分析到哪个环节是当前业务流程中的薄弱环节，可以帮助人们更加专注于薄弱环节提高整个流程的产出。进而提高整个流程的效率。

测试数据

根据上文介绍的漏斗模型的案例，阿里云为您提供了部分DEMO数据，您可以下载数据并根据上文示例完成整个案例的操作，从而得到您的漏斗模型图。数据如下：测试数据

转载地址：http://ceqbz.baihongyu.com/

你可能感兴趣的文章