用 Apache Kylin 做精准留存分析，对刷量 say『 No』！

互联网人口红利逐渐消失，越来越多的运营、市场、产品同学聚焦用户留存/转化。经常有小伙伴在 Kylin 用户群里问「Kylin 可以做留存分析吗」「Kylin 可不可以做用户增长漏斗分析呀」答案当然是「Yes！」

今天为大家带来“征文赢首届 Kylin Data Summit 门票”活动的第 3 篇投稿文章，来自某互联网金融公司的杨卫将为大家分享如何使用 Kylin 进行渠道精准留存分析。

1. 导读

某互联网金融公司定位于传统机构没有照顾好的金字塔底部人群，助力普通民众获取更快捷、规范的生活消费服务与数字普惠金融服务。经过 3 年多的发展，到目前累计交易金融达 100 亿，越来越大的的业务量带来的数据量快速增长，原来的数据分析已不能满足需求。

在对大数据 OLAP 工具的选型中，通过查询响应速度，SQL 支持（易用性），BI 支持，社区支持等综合比较，我们最终选择了 Apache Kylin。最初的应用只是做一些业务的简单的统计分析，比如财务报表统计等。通过一段时间的使用后，我们发现能应用 Kylin 的场景挺多，比如互金的核心场景之一：渠道精准留存。

2. 场景介绍

流量部门希望看到渠道相关路径的留存情况，这里我介绍一下他们比较关心的两条路径：

1）路径 A   渠道流量引入->注册下载->激活   

2）路径 B   注册下载->激活->认证->申请借款

两条路径不同的地方：路径 A 是手机号去重，路径 B 是用户 ID 去重。

以前我们完全不能做到精确，因为每条路径上的事件都有自己的业务时间，只能粗暴地算一下每个事件各自的业务时间上的统计值来，对比各统计值。

那么问题来了，路径 A 里比如今天渠道流量引入 100 个用户，注册下载 90 个用户，激活 70 个。那么 90 个注册用户可能只有 80 个是今天引入的还有10个是昨天或更早时引入的，同样激活 70 个用户可能有 60 个是今天注册还有 10 个是昨天或更早时注册的。那么我们只有统计值，是没有太好的办法精准计算今天渠道流量引入的 100 个用户后续留存的。

3. Kylin 如何解决

公司对渠道结算费用非常依赖精准留存，他们需要精确数字，否则容易被薅羊毛。经过一番调研对比，我们找到 Kylin 社区的一篇技术博文：

https://kylin.apache.org/blog/2016/11/28/intersect-count/

此博文详细介绍了 Kylin 支持留存计算是基于 Bitmap 和 UDAF 的 intersect_count函数，Kylin 精确去重就是基于 Bitmap 算法。

接着来看下 intersect 的使用：

intersect_count(columnToCount, columnToFilter, filterValueList)

1）columnToCount: the column to cacluate and distinct count.

2）columnToFilter: the variety dimension.

3）filterValueList: the values of variety dimension, should be array.

举个例子：
1）intersect_count(uuid, dt, array['20161014', '20161015'])

对维度 dt 值是 '20161014' 和 '20161015' 的范围内的 uuid 精确去重。

2）intersect_count(uuid, dt, array['20161014'])
对维度 dt 值是 '20161014' 的范围内的 uuid 精确去重。

4. 实现

4.1 埋点设计

先看下如下左图所示的渠道推广页 H5 的埋点位置，首先，我们在蓝色框中的“查看你能借多少”这个按钮设下了埋点：当新用户点击时，H5 前端埋下“借多少（新用户）”这个埋点；当已注册的老用户点击时，H5 前端埋下“借多少（老用户）”这个埋点。当新用户点击如下左图所示的“查看你能借多少”后，会跳转到如下右图。

在“注册下载”按钮下我们下了埋点名字叫“H5-注册”，只要用户输入正常的短信验证码和相应的密码即完成渠道端的注册。在路径 A 下还有一个激活埋点，属于后端埋点，是后端 ETL 程序根据用户登录情况 MySQL 表的写入的埋点。

在路径 B 里的认证埋点与申请借款埋点是在我们产品的APP（IOS 和 Android）里相关按钮下的埋点。

到这里，大家能看到涉及的埋点有渠道 H5 埋点，用户操作产品时的 IOS 及 Android 埋点，以及后端埋点。在公司不同的产品线上，埋点个数是不一样的，随着产品线业务版本的不断演进，埋点从开始的 20 来个扩张到上百个。

4.2 数仓设计

ODS（Operational Data Store）层的事实表可以是原始埋点业务日志，还有埋点维度表。

DW（Data Warehouse）层来源于 ODS 的数据 ETL，一般情况下 Kylin 在 DW 层事实表上做 Model 和 Cube 设计，但是因为可能会出现指标维度的更新，考虑到灵活性，我加了一层 DM 来应对。

DM（Data Mart）层基本是视图层，这层的表其实是指标维度表，在实际应用中，我把 Kylin 的数据源放在了 DM 层，即指标维度事实表。

4.3 Cube 设计

此场景 Cube 设计的关键点如下图所示。路径 A 和 路径 B 的关键区别在于计算留存率的去重方式不同，路径 A 的起点是渠道流量引入（即“借多少（新用户）”），此时我们只有手机号，而路径 B 起点是注册下载（H5 注册)，此时我们有了用户 ID，下图即为 Cube 设计的度量。

核心度量介绍完后，我们看下相关维度吧

这些维度会组成聚合组，例如LOG_HOUR, FUN_NAME, TAG_DAT组成的聚合组在 Superset 上可以展示如下接口调用情况时序图：

TAG_DATE,PRODUCT_TYPE,MAIDIAN_TYPE,CHANNEL_CODE 组成的聚合组才是我们这个渠道留存场景中的聚合组。

每天百万的用户访问埋点日志量不再是难题，这里顺便看一下这个埋点日志有 8 个维度的 Cube 的膨胀情况，此 Cube 已经过优化。

4.4 BI 展示

在 BI 方面，我选用了开源的 Superset。在 Superset 中配置好 Kylin 数据源，选择好事实表在页面上进行维度与指标的编辑工作，我们要把需要展示的维度和指标添加进来，如图所示：

5. 成果

经过前面的几个步骤，我们已经能直观地看到效果。

此次渠道推广链接为 https://jieduoduo.xxx.com/xxx-channel/?channel=t999，t999 即渠道 code，通过渠道维度表我们可以拿到渠道名，看上图的渠道A, 渠道B就是渠道名称。

再看下第 4 节中“编辑事实表VK_API_ACCESS 的指标”图中的指标页的两个漏斗指标：

1）借多少（新用户）->H5注册 %：指从借多少（新用户）到H5注册转化率，分子是H5注册数，分母是借多少（新用户）数。

2）H5注册->H5激活 %：指从H5注册再到H5激活转化情况。分子是H5激活数，分母是H5注册数。

那么恶意刷单，渠道自身不好的情况就一目了然。

呀，这个渠道 B 在刷量，我可不给钱哟

这个渠道 A 怎么搞的，到激活时的留存这么低，那么我可要停掉你少给你钱哟（一笑而过）

这里还可以更进一步分析为什么有些渠道留存比较低。