优化电商漏斗分析从 3 分钟 + 到 10 秒

简介: A电商公司常用漏斗转化率分析来统计用户购物行为。此过程需处理大量用户会话数据,传统SQL实现复杂低效。文中提供了一种基于SPL的专业数据计算引擎解决方案,通过预先排序数据和有序归并算法,显著提升了计算性能,使14天跨度3步漏斗分析在10秒内完成,远超预期。该方法不仅代码简洁,易于扩展,还大幅降低了内存消耗,适合处理大规模数据集。

问题描述
在 A 电商公司,漏斗转化率分析是常用且重要的统计需求。

用户使用智能设备购物时,系统会建立连接形成会话 session。每个会话又包含很多个操作事件 event,比如:访问网站,浏览产品页,下单购买等等。每个用户的操作事件有一定的先后顺序,事件顺序越靠后,完成该事件的用户数量越少,就像是一个漏斗。漏斗转化分析先要统计各步骤操作事件的去重用户数量,在此基础上再做转化率等进一步的计算。

问题分析
漏斗分析写成 SQL 非常复杂,即使勉强写出来,漏斗的每个步骤都要写一个子查询,还要反复 JOIN,代码很难看懂,执行效率低下。

A电商公司的表结构脱敏后简化成一张表 T,包括字段事件编号 id,用户号 gid,操作时间 etime,事件类型 eventtype。

三步漏斗的 SQL 语句经过简单修改,写成 Oracle 的语法,大致是下面这样的:

with e1 as (
       select gid,1 as step1,min(etime) as t1
       from T
       where etime>= to_date('2021-01-10', 'yyyy-MM-dd') and etime<to_date('2021-01-25', 'yyyy-MM-dd')     and eventtype='eventtype1' and …
       group by 1
),
e2 as (
      select gid,1 as step2,min(e1.t1) as t1,min(e2.etime) as t2
      from T as e2
      inner join e1 on e2.gid = e1.gid
      where e2.etime>= to_date('2021-01-10', 'yyyy-MM-dd') and e2.etime<to_date('2021-01-25', 'yyyy-MM-dd') and e2.etime > t1 and e2.etime < t1 + 7  and eventtype='eventtype2' and …
      group by 1
),
e3 as (
      select gid,1 as step3,min(e2.t1) as t1,min(e3.etime) as t3
      from T as e3
      inner join e2 on e3.gid = e2.gid
      where e3.etime>= to_date('2021-01-10', 'yyyy-MM-dd') and e3.etime<to_date('2021-01-25', 'yyyy-MM-dd') and e3.etime > t2 and e3.etime < t1 + 7 and eventtype='eventtype3' and …
      group by 1
)
select
  sum(step1) as step1,
  sum(step2) as step2,
  sum(step3) as step3
from
  e1
  left join e2 on e1.gid = e2.gid
  left join e3 on e2.gid = e3.gid

子查询 e1 先从 T 表中过滤出指定时间段内事件类型为 eventtype1 的数据。指定时间段的起止时间通常是传入参数,会动态变化。“and …”表示可能还要满足其他条件,子查询 e2、e3 也要满足同样的条件。然后按照 gid 分组,每组取 etime 最小值作为漏斗转化第一步的时间 t1,定义新字段 step1 值为 1。

子查询 e2 用 T 表(别名 e2)和 e1 做内连接,关联字段是 gid。过滤出指定时间段内 e2.etime 在 t1 后 7 天以内,且事件类型为 eventtype2 的数据,这里的 7 天被称为漏斗窗口期,也可以是传入参数。按照 gid 分组,每组取 e2.etime 最小值作为漏斗转化第二步的时间 t2,定义新字段 step2 值为 1。

子查询 e3 用 T 表(别名 e3)和 e2 做内连接,关联字段 gid。过滤出指定时间段内 e3.etime 大于 t2,且在 t1 后 7 天内(漏斗窗口期),同时事件类型为 eventtype3 的数据。也按照 gid 分组,每组取出 e3.etime 最小值作为漏斗转化第三步的时间 t3,定义新字段 step3 值为 1。

最后,用 e1 左连接 e2、e3,关联字段是 gid,对 step1、step2、step3 汇总求和。

这里给出的是 3 步漏斗转化分析,如果要实现更多步骤的漏斗分析,需要参照 e3,写出子查询 e4、e5…,主查询也要加上对应的关联和汇总代码。

一般来说,T 表在一定时间段内的数据,按 gid 分组结果集会很大,而大分组需要外存缓存,所以数据库计算的性能会比较差。

在 A 电商的实际环境中,T 表每个月大于 3 亿条数据,这个 SQL 语句在 Snowflake 的 Medium 级集群(4 节点)三分钟没跑出结果。

解决方案
一、预先排序,有序计算

在数据准备阶段,我们要预先将原数据按照分组字段有序存放。做漏斗转化分析时,遍历符合时间段和事件类型等条件的有序数据,依次把各组数据读入内存计算。具体的,第一步将当前分组数据按照时间排序;第二步,在当前组中,找到第一种事件类型的第一条记录,时间记为 t1,赋值给当前分组计算结果数组的第一个成员,如果找不到 t1 则当前组舍弃;第三步,在当前组的第二种事件类型的记录中,找出发生时间在 t1 之后,且早于 t1+7(漏斗窗口期)的第一条记录,其时间 t2 赋值给结果数组的第二个成员(如果找不到 t2 则赋值为空);第四步,在当前组第三种事件类型的记录中,找出发生时间在 t2 之后,且早于 t1+7 的第一条记录,其时间 t3 赋值给结果数组的第三个成员(如果 t2 为空,或者找不到 t3 则赋值为空);第五步,用各组结果数组的三个成员做汇总计数,即可得到最终结果。这样做,对数据遍历一次就可以完成计算,不用大分组,性能提升会很明显。

我们还可以预先按照分组字段和时间字段都有序存放,计算时不必每组再排序,对性能提升的作用相对较小,但可以简化代码。

事实上,很多组内时序计算的分组字段都是确定的(比如用户号、帐号),不会是随意选择的字段。只要按照这些确定的字段做一种排序,就能适用于很多组内时序计算了。其他组内时序计算只是组内的具体算法不一样,我们将另外介绍。

预先排序虽然慢,但是一次性的,而且只需要保持一种存储即可,没有冗余。

SQL 基于无序集合,不能严格保证每组数据连续存放,所以不能直接应用有序算法。

二、新增数据

新增数据并不总是按分组字段继续有序,所以不能简单的追加到有序数据的末尾。而直接将有序数据和新增数据一起重新做常规大排序,会非常耗时。

我们可以将新增数据排序后,和原有序数据一起,用低成本的有序归并算法生成新的有序数据。这样整体复杂度相当于把所有数据读写一遍,可以避免常规大排序中产生很多临时外存缓存的现象,从而获得更好的性能。

更进一步,可以另外保持一个小规模的有序数据 (以下称为补数据)。新增数据排序后和补数据归并,原有序数据不变。经过适当的时间后,补数据积累到合适大小时,再和原有序数据归并。做组内时间有序计算时,从有序数据和补数据中分别读取,归并后再计算,性能会比只有一份有序数据时下降一些,但仍能利用有序实现快速计算。

这个适当时间的确定,与新增数据的周期有关。比如每天都有新增数据,则每个月做一次原有序数据和补数据的归并。补数据不会超过一个月的数据量,原有序数据存储一个月之前的所有数据。也就是说补数据可能会比原有序数据小很多,所以每天归并的数据量相对较小,很快就能完成数据追加。每个月才需要完成一次全量有序归并,耗时长一些也可以接受了。

技术选型
关系数据库和大数据技术普遍采用的 SQL 语言是基于无序集合理论的,不能严格保证每组数据连续存放,所以不能直接应用上面说的用户和时间有序算法。

如果我们用 Java 等高级语言实现上述计算方法,那么代码量会非常巨大,而且很难通用。

专业的数据计算引擎 esProc SPL 支持数据物理有序存储,很容易的实现上述计算方法。而且,SPL 封装了大量的数据计算函数,可以用很简捷的代码实现漏斗分析计算。

实际效果
在 GCP 的 16C128G 的虚拟机上计算 14 天跨度 3 步漏斗,可以 10 秒计算完成,达到且超过了用户的期望值。

从开发难度来看,SPL 做了大量封装,提供了丰富的函数,内置了上述优化方案需要的基本算法。计算三步漏斗的 SPL 代码大致是下面这样:

QQ_1730359505537.png

代码要比前面的 SQL 更短、更灵活,再增加几步漏斗也还是这段代码。

A1:定义三个事件类型,也可以通过参数传入。

B1:打开组表 T.ctx。

A2:建立游标,过滤出满足时间段、事件类型等条件的数据,过滤条件都可以通过参数传入。

A3:定义分组计算,每组按照 etime 排序。

B3:定义新游标,每组中第一种事件类型的第一条数据命名为 first 字段,原分组命名为 all 字段。再定义过滤,去掉 first 字段为空的。

A4:对 B3 定义计算。每条记录都按照 A1 循环计算。第一次循环,将 first 的时间,也就是第一种事件类型最早的发生时间,同时赋值给变量 t1、t 和结果序列的第一个成员。第二次循环,在 all 中找 eventtype 为第二种事件类型,且 etime 大于 t,小于 t1 之后 7 天的第一条记录,其 etime 赋值给 t 和结果序列的第二个成员(找不到则赋值为空)。第三次循环,如果 t 不为空,在 all 中找 eventtype 为第三种事件类型,且 etime 大于 t,小于 t1 之后 7 天的第一条记录,其 etime 同时赋值给 t 和结果序列的第三个成员(t 为空或者找不到则赋值为空)。注意,这里的 7 天(漏斗窗口期)也可以通过参数传入。

A5:根据 A1 的漏斗步数,动态生成 count 语句count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3。

A6:实际执行前面定义的计算,并对各组结果序列的三个成员做小结果集汇总计数。其中利用了 A5 生成的 count 代码,实际执行的是:

=A4.groups(; count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3)

这里给出的也是 3 步漏斗转化分析,如果要实现更多步骤计算,只要在 A1 的事件类型序列中增加后续步骤的事件类型即可,例如:["eventtype1","eventtype2","eventtype3","eventtype4","eventtype5"…]。如果通过参数传入事件类型序列,代码就可以不做任何变动。相比 SQL 增加子查询、修改主查询的方法,要简单很多了。

后记
漏斗分析问题涉及的用户数量众多,事件表非常大一般都需要外存存储。但每个用户的数据量却并不是很大,只有几条到几千条。

而且操作事件的计算虽然复杂,却都是以用户为单位进行的。不同用户之间的事件无关,对一个用户的计算一般不涉及其他用户的事件。

我们可以利用 SPL 有序存储机制将一个个用户的数据分别加载到内存中进行计算,这样可以降低计算的复杂度,有效提高性能。

这样做,只需要对数据遍历一次就可以完成漏斗转化分析,计算速度快且占用的内存空间很小。而且,这样的计算逻辑,完全符合我们的自然思维习惯,可以降低写代码的难度。

关系数据库和大数据技术普遍采用的 SQL 语言是基于无序集合理论的,不能严格保证每个用户数据连续存放,所以不能直接应用上面说的用户和时间有序算法。如果我们用 Java 等高级语言实现上述计算方法,那么代码量会非常巨大,而且很难通用,更多问题可前往乾学院探讨!

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
相关文章
|
6月前
|
机器学习/深度学习 SQL 数据可视化
数据分享|数据探索电商平台用户行为流失可视化分析
数据分享|数据探索电商平台用户行为流失可视化分析
|
6月前
|
安全 数据可视化 数据挖掘
数据分享|AARRR模型淘宝用户行为分析、电商销售分析
数据分享|AARRR模型淘宝用户行为分析、电商销售分析
|
SQL 安全 数据挖掘
【业务数据分析】—— 用户留存分析(以挖掘Aha时刻为例)
【业务数据分析】—— 用户留存分析(以挖掘Aha时刻为例)
724 0
|
数据采集 机器学习/深度学习 算法
大数据分析案例-基于RFM模型对电商客户价值分析(聚类)
大数据分析案例-基于RFM模型对电商客户价值分析(聚类)
1035 0
大数据分析案例-基于RFM模型对电商客户价值分析(聚类)
|
监控 搜索推荐 算法
电商数据分析常用业务指标整理(共72个)
电商数据分析常用业务指标整理(共72个)
|
大数据 数据库 开发者
电商项目之总体运营指标统计表分析|学习笔记
快速学习电商项目之总体运营指标统计表分析
电商项目之总体运营指标统计表分析|学习笔记
|
监控 大数据 开发者
电商项目之总体运营指标其他指标分析|学习笔记
快速学习电商项目之总体运营指标其他指标分析
|
分布式计算 数据可视化 搜索推荐
用户画像分析-21
用户画像分析-21
263 0
用户画像分析-21
|
运维 分布式计算 搜索推荐
用户画像分析-15
用户画像分析-15
185 0
用户画像分析-15
|
SQL 分布式计算 搜索推荐
用户画像分析-14
用户画像分析-14
154 0
用户画像分析-14