使用MaxCompute构建离线计算数据模型并连接Quick BI实现漏斗模型分析_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

本文以电商场景为例，为您介绍如何使用MaxCompute进行离线数据计算并连接Quick BI进行漏斗模型展示。

背景信息

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

前提条件

已开通日志服务SLS。详情请参见日志服务SLS购买指导。
已开通MaxCompute服务并创建MaxCompute项目。详情请参见开通MaxCompute服务和创建MaxCompute项目。
已开通DataWorks服务。详情请参见DataWorks购买指导。
已开通Quick BI服务。详情请参见Quick BI购买指导。

操作步骤

通过日志服务采集日志数据。
日志服务采集日志数据，详情请参见数据采集概述。本文以测试数据为例演示，如需下载测试数据，请您单击TestData下载。

使用MaxCompute构建离线计算数据模型。

按照如下建表语句，在DataWorks数据开发界面创建数据引入层（ODS）表ods_user_trace_data。

--以dt作为时间分区，单位为天。
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 '设备品牌',
    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  
);

说明

以上建表语句中的字段是根据测试数据构建的。如何在DataWorks创建表，请参见开发ODPS SQL任务。
更多ODS说明，请参见数据引入层（ODS）。

向ods_user_trace_data表中添加分区，命令如下。

ALTER TABLE ods_user_trace_data ADD PARTITION (dt=${bdp.system.bizdate});

将采集的日志数据迁移至MaxCompute。
采集的日志数据迁移至MaxCompute，详情请参见日志数据迁移至MaxCompute。

按照如下建表语句，在DataWorks数据开发界面创建明细粒度事实层（DWD）表dw_user_trace_data。

--以dt作为时间分区，单位为天。
CREATE TABLE IF NOT EXISTS dw_user_trace_data
(
    uid                     STRING COMMENT '用户uid',
    device_brand            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 
);

说明

更多DWD说明，请参见明细粒度事实层（DWD）。

向dw_user_trace_data表中插入数据，命令如下。

INSERT INTO dw_user_trace_data PARTITION (dt = '${bdp.system.bizdate}')
SELECT  uid
        ,device_brand
        ,system_type
        ,customize_event
        ,use_time
        ,customize_event_content
FROM    ods_user_trace_data
WHERE   dt = '${bdp.system.bizdate}'
;

按照如下建表语句，在DataWorks数据开发界面创建数据应用层（ADS）表rpt_user_trace_data。

 --以dt作为时间分区，单位为天。
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 
);

说明

更多ADS说明，请参见数仓分层。

按照如下SQL语句，在DataWorks数据开发界面编写业务代码逻辑。

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 
;

说明

用户路径是从浏览到点击再到购买。各个环节的转化率为从当一个页面进入下一页面的人数比率，例如点击转换率=进入点击页面的人数/浏览页面人数。

查询rpt_user_trace_data表中的离线数据计算结果。

SQL示例：

SELECT * FROM rpt_user_trace_data WHERE dt='20231126';

查询结果：

+------------+------------+------------+-------------+------------+------------+
| browse     | click      | purchase   | browse_rate | click_rate | dt         |
+------------+------------+------------+-------------+------------+------------+
| 35         | 16         | 2          | 45.71%      | 12.5%      | 20231126   |
+------------+------------+------------+-------------+------------+------------+

数据可视化展示。
通过Quick BI创建网站用户分析画像的仪表板，实现该数据表的可视化，详情请参见云数据源MaxCompute和漏斗图。
重要
- 在Quick BI中创建数据集时，您需要先判断MaxCompute项目是否开启了三层模型，再根据对应的方式创建数据集：
  可在MaxCompute中使用setproject;命令查看项目空间属性中odps.namespace.schema参数的值。
  值为true：表示该项目已开启三层模型，您需要使用自定义SQL方式创建数据集。因为Quick BI没有正式对接MaxCompute的三层模型，如果使用拖拽表（即Quick BI内部生成SQL）的方式，会出现找不到表的报错信息。
  值为false：表示该项目未开启三层模型，您可以使用自定义SQL或拖拽表（即Quick BI内部生成SQL）的方式创建数据集。
- 要生成下述漏斗图，您需要选择表对象为ods_user_trace_data的数据集，并将customize_event字段作为漏斗分层/维度。