Kylin 精确去重在用户行为分析中的妙用

在上次文章《如何在 1 秒内做到大数据精准去重》中，我们介绍了 Apache Kylin 为什么要支持大数据集上的精确去重，以及基于 Bitmap 的精确去重原理等。看到今天的文章标题，你肯定要问，精确去重跟用户行为分析又能有什么关系呢？原来啊，Kylin 采用 Bitmap 完整记录了每个维度组合下的用户集合，利用 Bitmap 提供的或（or）运算方法来高效精准地回答了各种条件下的去重用户数。其实 Bitmap 除了支持或（or）运算外，还支持与（and）运算。因此，稍加扩展，Kylin 就可以基于 Bitmap 的中间结果，轻松实现诸如留存、漏斗等大量使用交集运算的分析，从而非常方便地运用在用户行为、用户画像等领域中。可以说精确去重功能有着一石两鸟的价值，本文将为您介绍如何使用 Kylin 来实现精准的用户行为分析。

示例

先从一个简单的例子说起吧。现在有一个 app 的用户访问记录表 access_log，它包含三个字段：DT (访问日期），User ID（用户标示）和 Page（访问页）：

DT	User ID	Page
20190101	100	index.html
20190101	101	search.html
20190101	102	detail.html
...	...	...
20190102	100	index.html
20190102	102	detail.html
20190102	103	index.html
...
20190103	101	index.html
...

△ 表 1：样例数据原始表

在 Kylin 里创建一个模型，选择 DT 和 Page 做维度，User ID 做度量；

然后创建一个 Cube，使用 DT 和 page 做维度，定义 count (distinct user_id) 度量且使用 Bitmap 为数据类型（关于 Kylin 的使用，参考 Apache Kylin 官网教程）。

这个 Cube 构建完成后，对于只包含 DT 维度的 Cuboid，它的数据结构如下：

DT	User IDs (Bitmap)
20190101	[100,101,102]
20190102	[100,102,103]
20190103	[101,102,103,104]

△ 表 2：样例 Cube 1

注：上面的用户集合“User IDs"用的是易于阅读的数组格式，在实际存储中使用 Bitmap（位图）格式，也就是一组 0 或 1 的 bit 数组，如 [100,101,102] 就在第 100-102 位放 1，其它位放 0 代表。因此，Bitmap 不但非常省空间，而且非常适合计算机做交并集运算。

如果要计算某天或某几天的 UV，SQL 如下：

select dt, count(distinct user_id) from access_log where dt >= '20190101' and dt <= "20190103" group by dt

根据查询 Bitmap 的 Cardinality（基数），获取到 UV 值：

Date	Count distinct User ID
20190101	3
20190102	3
20190103	4

△ 表 3：样例查询结果 1

留存分析

对于 App 的运营者来说，留存分析是一种常见的分析手段，常用于提升用户留存率，它的主要目标是找到影响用户留存的关键因素。因为获取用户是有一定成本的，如果新获取的用户大部分都留不住，那么拉新的投入产出比就会很低。通常会分析日留存、周留存和月留存。

以日留存为例，要计算第一天访问的用户中，有多少在第二天、第三天继续访问了 app。如果使用 HiveQL或 Spark SQL 来计算第一天和第二天的留存用户数，写法大致如下：

SELECT count(distinct first_day.USER_ID) FROM
  (select distinct USER_ID as USER_ID from access_log where DT = '20190101') as first_day 
    INNER JOIN
  (select distinct USER_ID as USER_ID from access_log where DT = '20190102') as second_day 
  ON first_day.USER_ID = second_day.USER_ID

可以看出，使用 Hive/Spark 计算留存用户，需要先写多个子查询，分别计算出各日的用户集合，然后通过 inner join 的方式实现交集计算，最后在外层再做 count(distinct）运算。在数据量很大的时候，这样的多个子查询的join会非常慢且容易内存溢出。

使用 Kylin 计算日／周／月留存

大家可以看到，其实拿这几日访问用户的 bitmap，相互做与（and）操作，就可以高效地得到留存数字，如“第一天”and“第二天”访问的用户；这样跟第一天的访问用户数做个比较，就可以计算出第二天留存率等。

为了便于在 SQL 中做“与”操作，Kylin 提供了一个自定义函数：“intersect_count”（详见文末“参考”文章【2】）。顾名思义，就是做交集以后的结果数。该函数用法如下：

intersect_count(columnToCount, columnToFilter, filterValueList)
其中：
`columnToCount` 要做计数的列，也就是bitmap存储的列，这里就是“user_id“
`columnToFilter` 做交集计算的列，如果是把多个不同日期的bitmap来做交集，那么这列就是日期；
`filterValueList` 做交集计算的bitmap的key值，需要是一个数组。

例如下面的 SQL 查询了'20190101' 与 '20190102'这两天的交集用户数：

select intersect_count(user_id, dt, array['20190101', '20190102'])
from access_log
where dt in ('20190101', '20190102')

可以看出，使用 Kylin，一个 intersect_count 函数就可以完成多日用户集合的交集计算。这里where条件中也进行了日期的筛查，为的是减少从底层存储加载的数据。显然，相比于Hive 和 Spark SQL，Kylin 的用法更加简单明了，而且基于预计算的 Bitmap 做交集，比现场 join 效率高很多，可以做到秒级响应。

通过 Kylin 还可以很方便地在一条 SQL 中查询多日的 UV 及留存，避免反复查询，如：

select city, version,
intersect_count(user_id, dt, array['20161014']) as first_day_uv,
intersect_count(user_id, dt, array['20161015']) as second_day_uv,
intersect_count(user_id, dt, array['20161016']) as third_day_uv,
intersect_count(user_id, dt, array['20161014', '20161015']) as retention_oneday,
intersect_count(user_id, dt, array['20161014', '20161016']) as retention_twoday
from access_log
where dt in ('2016104', '20161015', '20161016')
group by city, version
order by city, version

同理，如果 Cube 中有周、月做维度，那么这里把日期换成周、月就可以很方便地计算周留存、月留存了。

使用 Kylin 进行漏斗分析

漏斗分析，又叫转化漏斗，就是将一个特定过程的多个步骤间的转化情况，以漏斗的形式展示出来，通过图形直观地发现流失最严重的环节，从而有针对性地去进行优化。

△ 表1：漏斗分析

可以看出，漏斗分析中也要用到交集运算，例如：有多少访问了首页的用户，进入到了产品详细页？看了产品详情页的，有多少用户将它加入到了购物车？产品运营人员非常关心这些指标，因为它代表了用户在使用中每一步的转化关系；如果某一个路径上的转化率较低，意味着潜在的问题和风险，需要及时介入。

以前面的示例数据为例，如果我们将 Page 作为一个维度，User ID 做 count distinct 度量，构建成 Cube 后得到这样的用户访问统计（示例）:

Page	User IDs (Bitmap)
index.html	[100,101,102,103,104]
search.html	[100,102,103]
detail.html	[101,103]

△ 表4：样例 Cube 2

这样通过切换 Page 的值来做交集，我们就可以很容易地计算出它们之间的漏斗转化率，如：

select 
intersect_count(user_id, page, array['index.html']) as first_step_uv,
intersect_count(user_id, page, array['search.html']) as second_step_uv,
intersect_count(user_id, page, array['detail.html']) as third_step_uv,
intersect_count(user_id, page, array['index.html', 'search.html']) as retention_one_two,
intersect_count(user_id, page, array['search.html','detail.html']) as retention_two_three
from access_log
where dt in ('2016104', '20161015', '20161016')

结果：

5， 3， 2， 3， 2

如此行为漏斗的转化率也就很容易得到了：

Page	关联用户数（转化率）
index.html	4（100%）
index.html  -> search.html	3 (3/4=75%)
search.html -> detail.html	2 (2/3=66%)

△ 表 5：样例行为漏斗结果

当然这是一个很简单的例子，实际会复杂很多；这里的 Page（页面）可以换成埋点值或其它维度，从而更加细致地分析各种行为之间的关联关系。

多维度滑动的留存分析

前面的例子中都是单个维度值变化时，使用Kylin的交集函数进行留存和漏斗转化的计算，是比较容易理解的。现实中有时候需要在多个维度上同时进行滑动分析，例如运营可能会问：第一天访问“商品明细页”的用户，有多少在第二天访问了“付款页”？那么 Kylin 是否可以做到呢？

答案是肯定的，虽然 intersect_count 交集函数只接受一个维度值的变化，但我们可以巧妙利用 where 做其它维度的筛选，最后的结果交给 SQL 执行器来计算。如：

select 
intersect_count(user_id, dt, array[‘20190101’])，#第一天的UV
intersect_count(user_id, dt, array[‘20190101’, ‘20190102’])  #第一天和第二天交集
from access_log
where (dt='20190101’ and page=‘detail.html’) #筛选第一天&访问明细页的用户
  or 
(dt=‘20190102’ and page=‘payment.html’) #筛选第二天&访问付款页的用户

这样的条件是只需要把 page 和 dt 都做为维度就可以了，是不是很简单？

先或再与操作

有时候业务人员在分析问题的时候，会动态调整分析的组合，把一些条件先进行或(or)操作，然后再跟其它条件做与(and)运算。例如，访问了“搜索页”和“详情页”中任何一个的用户，有多少访问了“付款页”？前两者是一个或的关系，它们的结果需要跟第三个进行与操作。

为了支持这种特殊的计算，我们可以扩展 intersect_count 函数，让它可以理解或运算符。下面是一个示例（这里默认使用了“|”作为或条件的分隔符，如果维度值中可能包含“|”，需通过配置修改成其它符号）：

select 
intersect_count(user_id, page, array['search.html|detail.html’, 'payment.html']) 
from access_log

在 app 埋点分析中，这也是一个常见的场景。app 开发者为了日后分析的灵活性，会有意埋了很多不同的点：同样的行为在不同客户端、版本中的埋点值可能不同（称为“物理埋点”）；但业务人员在分析的时候，需要将这些物理埋点根据需要装配成“逻辑埋点”。例如，“安卓端登录”、“苹果端登录”和“网页登录”这三个埋点值都代表了“登录”这个行为，它们或的集合去跟“登录失败”做与操作，可以算出 app 整体登录失败率。

过去为了满足业务的这种灵活分析需求，开发者往往需要调整他们的ETL脚本来改变运算逻辑，周期长、效率低并且很容易出错；使用 Kylin 后就没有这些烦恼，用户可以灵活组装查询条件，剩下的事情交给 Kylin 就可以了。

注：此功能目前还处于内部预览阶段，还未在社区版正式发布。

用户画像分析

用户画像分析中需要通过标签进行用户的筛选；作为存储数字集合的最紧凑数据结构，Bitmap 常常被用在用户画像分析中。Kylin 引入 Bitmap 后，也可以用在用户画像的分析。

使用 Kylin 做用户筛查时，通常需要将标签从列转行，将“标签类型”和“标签值”作为维度，将 User ID 作为 Bitmap 度量进行构建，构建后的 Cube 如下：

标签类型(tag_type)	标签值(tag_value)	User IDs (Bitmap)
性别	男	[100,101,102,103,104]
性别	...	...
年龄区间	90后	[100,102,103]
年龄区间	...	...
收入	10-20万	[101,103,105,107]
收入	...	..

△ 表 6：样例用户画像 Cube

例如现在要分析，性别是男的、年龄是 90 后的、收入在 10-20 万区间的人有多少；通过 Kylin 这样查询即可：

select 
intersect_count(user_id, tag_value, array['男', '90后', '10-20万']) 
from user_profile
where (tag_type='性别' and tag_value='男') or (tag_type='年龄' and tag_value='90后') or (tag_type='收入' and tag_value='10-20万')

结果：

2

如果当前标签筛选后的结果集依然很大，用户可以继续添加更多标签，直到将用户数控制到合适的范围（例如打算定向发放一万张优惠券）。

用户明细分析

在用标签筛查后业务人员可能问，能否导出满足这些标签的用户集合明细呢，这样好对他们发券啊？Kylin 是否能在回答用户数的同时，告诉我们具体是哪些 User_ID 呢？

答案是肯定的，因为 Bitmap 忠实地保存了每个 User_ID 值（例如使用第 100 个 bit 位代表 ID 为 100 的用户是否出现），因此它可以在需要的时候告诉我们明细数据。

为了支持此类查询，我们可以参照 intersect_count 函数，开发另一个能返回 Bitmap 明细的函数，这里我们暂且称它为 intersect_value 函数，用法跟 intersect_count 一样，只是它的返回类型是一个整数数组：

select 
intersect_value(user_id, tag_value, array['男', '90后', '10-20万']) 
from user_profile
where (tag_type='性别' and tag_value='男') or (tag_type='年龄' and tag_value='90后') or (tag_type='收入' and tag_value='10-20万')

结果：

[101,103]

有了明细结果，下一步就可以结合其它数据源如用户明细表、CRM 系统等做进一步的分析了，这里就不展开了。

注：此功能目前还处于内部预览阶段，还未在社区版正式发布。

成功案例

Kylin 的精确去重功能和交集函数最初是由美团点评大数据团队根据自身场景和需求开发并贡献到开源社区的，并在美团内部得到大量使用。现在，这些功能正在被越来越多的用户所使用，有的基于 Kylin 再开发一些前端展现，就实现了能满足业务绝大部分需求的一站式用户行为分析平台。

例如满帮集团，它是原运满满和货车帮合并后的集团，有 8 个手机 app，4 类埋点、上千个埋点值，收集了超过千亿条的用户行为日志。过去他们自己开发的分析平台各方面都不能满足业务需求，束缚了业务的发展；后来满帮集团迁移到了基于 Kylin 的分析平台，借助于 Kylin 的丰富功能，自研了名为 APPDATA 的一站式分析平台，极大地满足了业务对于数据分析的需求。

△ 图2：满帮 APPDATA 数据流程图

这是他们基于 Kylin 的日／周／月留存分析报表，对于留存率异常情况，会通过颜色高亮显示：

      △ 图 3：满帮 APPDATA 日留存样例

这是他们开发的、让业务人员可以自定义行为的功能，业务人员可以自由地组合各类埋点，将它们定义称一个“行为”（逻辑埋点）：

      △ 表 4：满帮 APPDATA 自定义用户行为

随后，业务人员可以将一些行为串成一个行为漏斗，然后查看漏斗转化率，背后就是用的 Kylin 交集函数：

      △ 表5：满帮 APPDATA 自定义行为漏斗

在这张图上，业务人员可以很方便地查看每一步的留存／流失率，对于异常情况，可以进一步下钻到明细做进一步的筛查。关于满帮使用Kylin的更多信息，请参考文末的“参考”文章【3】。

总结

Kylin 为实现秒级精确去重引入了 Bitmap 做为用户集合的存储结构，通过扩展 SQL 聚合函数，Kylin 还支持对 Bitmap 的交集、先或再与以及查询明细等操作，可以非常巧妙地运用在用户行为和用户画像分析领域，相比于自己开发具有多种优势：

• 图形化建模操作，无需编程开发；
• 存储和计算基于 Hadoop，能支撑海量用户数据的加工和存储；
• 易于使用，查询全部使用 SQL；
• 高性能高并发，大部分查询在秒级完成；
• 结果精确；
• 稳定可靠，已在许多互联网用户如美团、滴滴、eBay 生产系统使用多年。

想体验 Kylin 秒级精确去重？

Kylin 官网文档中有操作指南哦：https://kylin.apache.org/docs/tutorial/create_cube.html

参考

【1】史少锋《如何在 1 秒内做到大数据精准去重》 https://cn.kyligence.io/blog/apache-kylin-count-distinct/

【2】孙业锐 《Retention Or Conversion Rate Analyze in Apache Kylin》https://kylin.apache.org/blog/2016/11/28/intersect-count/

【3】陈雅婕《Kylin 在满帮集团千亿级用户访问行为分析中的应用》https://cn.kyligence.io/resources/kylin_at_manbang_group/