正文 

当前位置： 首页 > IT > java教程

基于clickhouse的用户行为（路径）分析实践(代码片段)

刘元涛 刘元涛     2023-02-06     268

关键词：

前言

ClickHouse为用户提供了丰富的多参聚合函数（parametric aggregate function）和基于数组+Lambda表达式的高阶函数（higher-order function），将它们灵活使用可以达到魔法般的效果。在我们的体系中，ClickHouse定位点击流数仓，所以下面举几个用它来做用户行为（路径）分析的实战例子，包括：

  • 路径匹配
  • 智能路径检测
  • 有序漏斗转化
  • 用户留存
  • Session统计

路径匹配

CK默认提供了sequenceMatch函数检查是否有事件链满足输入的模式，sequenceCount函数则统计满足输入模式的事件链的数量。示例：

SELECT 
  site_id,
  sequenceMatch('(?1)(?t<=15)(?2).*(?3)')(
    ts_date_time,
    event_type = 'shtLogon',
    event_type = 'shtKkclick' AND column_type = 'homePage',
    event_type = 'shtAddCart'
  ) AS is_match
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;

┌─site_id─┬─is_match─┐
│   10030 │        1 │
│   10339 │        1 │
│   10266 │        1 │
│   10022 │        1 │
└─────────┴──────────┘

SELECT 
  site_id,
  sequenceCount('(?1)(?t<=15)(?2).*(?3)')(
    ts_date_time,
    event_type = 'shtLogon',
    event_type = 'shtKkclick' AND column_type = 'homePage',
    event_type = 'shtAddCart'
  ) AS seq_count
FROM ods.analytics_access_log_all
WHERE ts_date >= '2020-07-01'
AND site_id IN (10266,10022,10339,10030)
GROUP BY site_id;

┌─site_id─┬─seq_count─┐
│   10030 │     33611 │
│   10339 │     14045 │
│   10266 │     74542 │
│   10022 │     31534 │
└─────────┴───────────┘

 

这两个函数都需要指定模式串、时间列和期望的事件序列（最多可指定32个事件）。模式串的语法有以下三种：

  • (?N)：表示时间序列中的第N个事件，从1开始。例如上述SQL中，(?2)即表示event_type = 'shtKkclick' AND column_type = 'homePage'。
  • (?t op secs)：插入两个事件之间，表示它们发生时需要满足的时间条件（单位为秒）。例如上述SQL中，(?1)(?t<=15)(?2)即表示事件1和2发生的时间间隔在15秒以内。
  • .*：表示任意的非指定事件。

智能路径检测

CK内置的sequenceMatch和sequenceCount函数只能满足部分需求，现有一个更复杂的需求：

给定期望的路径终点、途经点和最大事件时间间隔，查询出符合条件的路径详情及符合路径的用户数（按用户数降序排列）。

目前并没有现成的函数可以直接出结果，但是我们可以曲线救国，用数组和高阶函数的组合间接实现。完整SQL语句如下，略长：

SELECT
  result_chain,
  uniqCombined(user_id) AS user_count
FROM (
  WITH
    toUInt32(maxIf(ts_date_time, event_type = 'shtOrderDone')) AS end_event_maxt,
    arrayCompact(arraySort(
      x -> x.1,
      arrayFilter(
        x -> x.1 <= end_event_maxt,
        groupArray((toUInt32(ts_date_time), (event_type, column_type)))
      )
    )) AS sorted_events,
    arrayEnumerate(sorted_events) AS event_idxs,
    arrayFilter(
      (x, y, z) -> z.1 <= end_event_maxt AND (z.2.1 = 'shtOrderDone' OR y > 600),
      event_idxs,
      arrayDifference(sorted_events.1),
      sorted_events
    ) AS gap_idxs,
    arrayMap(x -> x + 1, gap_idxs) AS gap_idxs_,
    arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,
    arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_events
  SELECT
    user_id,
    arrayJoin(split_events) AS event_chain_,
    arrayCompact(event_chain_.2) AS event_chain,
    hasAll(event_chain, [('shtKkClick', 'homePage')]) AS has_midway_hit,
    arrayStringConcat(arrayMap(
      x -> concat(x.1, '#', x.2),
      event_chain
    ), ' -> ') AS result_chain
  FROM (
    SELECT ts_date,ts_date_time,event_type,column_type,user_id
    FROM ods.analytics_access_log_all
    WHERE ts_date >= '2020-06-30' AND ts_date <= '2020-07-02'
    AND site_id IN (10266,10022,10339,10030)
  )
  GROUP BY user_id
  HAVING length(event_chain) > 1
)
WHERE event_chain[length(event_chain)].1 = 'shtOrderDone'
AND has_midway_hit = 1
GROUP BY result_chain
ORDER BY user_count DESC LIMIT 20;

 简述思路：

  1. 将用户的行为用groupArray函数整理成<时间, <事件名, 页面名>>的元组，并用arraySort函数按时间升序排序；
  2. 利用arrayEnumerate函数获取原始行为链的下标数组；
  3. 利用arrayFilter和arrayDifference函数，过滤出原始行为链中的分界点下标。分界点的条件是路径终点或者时间差大于最大间隔；
  4. 利用arrayMap和has函数获取下标数组的掩码（由0和1组成的序列），用于最终切分，1表示分界点；
  5. 调用arraySplit函数将原始行为链按分界点切分成单次访问的行为链。注意该函数会将分界点作为新链的起始点，所以前面要将分界点的下标加1；
  6. 调用arrayJoin和arrayCompact函数将事件链的数组打平成多行单列，并去除相邻重复项。
  7. 调用hasAll函数确定是否全部存在指定的途经点。如果要求有任意一个途经点存在即可，就换用hasAny函数。当然，也可以修改WHERE谓词来排除指定的途经点。
  8. 将最终结果整理成可读的字符串，按行为链统计用户基数，完成。

有序漏斗转化

CK提供了windowFunnel函数实现漏斗，以指定时长（单位为秒）滑动窗口按序匹配事件链，并返回在窗口内转化到的步数。如有多种匹配，以步数最大（转换最深）的为准。

通过对该步数进行统计，即可得到漏斗中每步的转化率。SQL语句如下，查询结果是敏感数据，不再贴出来了。

SELECT 
  level,user_count,conv_rate_percent
FROM (
  SELECT 
    level,
    uniqCombined(user_id) AS user_count,
    neighbor(user_count, -1) AS prev_user_count,
    if (prev_user_count = 0, -1, round(user_count / prev_user_count * 100, 3)) AS conv_rate_percent
  FROM (
    SELECT
      user_id,
      windowFunnel(900)(
        ts_date_time,
        event_type = 'shtLogon',
        event_type = 'shtKkClick' AND column_type = 'homePage',
        event_type = 'shtOpenGoodsDetail',
        event_type = 'shtAddCart',
        event_type = 'shtOrderDone'
      ) AS level
    FROM (
      SELECT ts_date,ts_date_time,event_type,column_type,user_id
      FROM ods.analytics_access_log_all
      WHERE ts_date >= '2020-06-30' AND ts_date <= '2020-07-02'
      AND site_id IN (10266,10022,10339,10030)
    )
    GROUP BY user_id
  )
  WHERE level > 0
  GROUP BY level
  ORDER BY level ASC
);

如果想要更准确一些，实现漏斗步骤之间的字段关联（如商品详情→加入购物车→下单三步中的商品ID关联）怎么办呢？可以利用housepower/olap2018项目中提出的xFunnel函数。它是windowFunnel函数的鼻祖，不过需要修改ClickHouse源码并重新编译之，今后有时间的话会简单写一下过程。

用户留存

retention函数可以方便地计算留存情况。该函数接受多个条件，以第一个条件的结果为基准，观察后面的各个条件是否也满足，若满足则置1，不满足则置0，最终返回0和1的数组。通过统计1的数量，即可计算出留存率。

下面的SQL语句计算次日重复下单率与七日重复下单率（语义与留存相同）。

SELECT
  sum(ret[1]) AS original,
  sum(ret[2]) AS next_day_ret,
  round(next_day_ret / original * 100, 3) AS next_day_ratio,
  sum(ret[3]) AS seven_day_ret,
  round(seven_day_ret / original * 100, 3) AS seven_day_ratio
FROM (
  WITH toDate('2020-06-24') AS first_date
  SELECT
    user_id,
    retention(
      ts_date = first_date,
      ts_date = first_date + INTERVAL 1 DAY,
      ts_date = first_date + INTERVAL 7 DAY
    ) AS ret
  FROM ods.ms_order_done_all
  WHERE ts_date >= first_date AND ts_date <= first_date + INTERVAL 7 DAY
  GROUP BY user_id
);

Session统计

Session，即"会话"，是指在指定的时间段内在网站/H5/小程序/APP上发生的一系列用户行为的集合。例如，一次会话可以包含多个页面浏览、交互事件等。Session是具备时间属性的，根据不同的切割规则，可以生成不同长度的Session。

可见，Session统计与上述智能路径检测的场景有相似之处，都需要寻找用户行为链的边界并进行切割。以下SQL语句以30分钟为超时时间，按天统计所有用户的Session总数（跨天的Session也会被切割）。 
SELECT 
  ts_date,
  sum(length(session_gaps)) AS session_cnt
FROM (
  WITH
    arraySort(groupArray(toUInt32(ts_date_time))) AS times,
    arrayDifference(times) AS times_diff
  SELECT
    ts_date,
    arrayFilter(x -> x > 1800, times_diff) AS session_gaps
  FROM ods.analytics_access_log_all
  WHERE ts_date >= '2020-06-30'
  GROUP BY ts_date,user_id
)
GROUP BY ts_date;

 

用户行为分析模型实践——漏斗分析模型

...台内部的具体实现。针对实际使用过程的问题，探索基于ClickHouse漏斗模型实践方案。本文详细介绍漏斗模型的概念及基本原理，并阐述了其在平台内部的具体实现。针对实际使用过程的问题，探索基于ClickHouse漏斗模型实践方案... 查看详情

clickhouse引擎在行为分析场景下的join优化(代码片段)

火山引擎增长分析DataFinder基于ClickHouse来进行行为日志的分析，ClickHouse的主要版本是基于社区版改进开发的字节内部版本。1.背景火山引擎增长分析DataFinder基于ClickHouse来进行行为日志的分析，ClickHouse的主要版本是基于社... 查看详情

基于spark的用户行为路径分析

...客户的一些行为偏好了，无论是电商行业还是金融行业，基于用户行为可以做出很多东西，电商行业可以归纳出用户偏好为用户推荐商品，金融行业可以把用户行为作为反欺诈的一个点，本文主要介绍其中一个重要的功能点，基... 查看详情

最佳实践携程clickhouse日志分析实践

ElasticSearch是一种基于Lucene的分布式全文搜索引擎，携程用ES处理日志，目前服务器规模500+，日均日志接入量大约200TB。随着日志量不断增加，一些问题逐渐暴露出来：一方面ES服务器越来越多，投入的成... 查看详情

clickhouse(流量分析(三).路径分析案例)

参考技术A神策用户分析模型——路径分析的使用方法漏斗分析是固化了具体的分析过程或者业务环节，然后分析几个大的业务环节的转化；而路径分析，是固化了用户的路径顺序，在每个路径次序中，都包含了各个主要业务环... 查看详情

如何做好用户分析

...需求进行前端布局调整。04用户健康度分析用户健康度是基于用户行为数据综合考虑的核心指标，体现产品的运营情况，为产品的发展进行预警。包括三大类型指标：产品基础指标、流量质量指标、产品营收指标。它们三者构成... 查看详情

基于无埋点技术的用户行为分析

...个是过程。现在国内市场上关于用户行为分析的产品分为基于前台数据的用户行为分析和基于后台数据的用户行为分析。基于前台技术的用户行为分析侧重于用户的行为分析，而基于后台技术的用户行为分析侧重于用户行为的结... 查看详情

用户行为路径分析——附python桑基图代码实现

参考技术A用户路径，就是用户在网站或APP中的访问行为路径，为了衡量网站/APP的优化效果或者营销推广效果，了解用户的行为偏好，要对访问路径的数据进行分析。用户路径分析和转化分析有点类似，转化分析能告诉我们最终... 查看详情

flink规则引擎实践分享(代码片段)

...则、条件查询封装**3.1规则封装3.2查询规则封装四、基于ClickHouse实现用户行为明细查询服务支持4.1ClickHouse从Kafka摄取数据4.2ClickHouse查询服务中的sql设计**4.3ClickHouse查询时间跨度问题与解决**4.4查询路由模块**五、缓存模块 查看详情

常见用户行为分析模型：用户行为路径分析模型

用户行为路径分析同样是重要的数据分析模型，它为企业实现理想的数据驱动与布局调整提供科学指导，对精准勾勒用户画像也有重要参考价值。用户访问APP／网络，如同参观画展，观众是感受和传达画展参展方和展品的目的受... 查看详情

用户行为分析模型——路径分析(代码片段)

...网页或者营销渠道中，用户行为模型有比较多，基于渠道的，笔者觉得有：渠道类型渠道重要性渠道跳转与流失单渠道，多节点路径分析，漏斗功能多渠道归因分析这里多渠道指的是，单渠道多节点的... 查看详情

基于网站的用户行为分析

...;设定了一些常用的用户指标和值得关注的用户指标，基于这些分类用户指标的分析可以发现用户运营和推广中的诸多问题，其中活跃用户和流失用户的定义中已经用到了与用户行为相关的指标，这里重点介绍常用的... 查看详情

常见用户行为分析模型：用户行为路径分析模型

用户行为路径分析同样是重要的数据分析模型，它为企业实现理想的数据驱动与布局调整提供科学指导，对精准勾勒用户画像也有重要参考价值。用户访问APP／网络，如同参观画展，观众是感受和传达画展参展方和展品的目的受... 查看详情

周路：为什么是clickhouse？ebay广告数据平台架构实践！

...瑶传智播客出品平台：DataFunTalk导读：本文的主题是基于ClickHouse的广告数据平台架构实践。包括广告业务面临的现状，为什么会使用ClickHouse来提供数据多维分析服务，如何基于ClickHouse的优势和特点在适应亿贝广告业务场景的前... 查看详情

如何用sql分析电商用户行为数据（案例）

...法当没有清晰的数据看板时我们需要先清洗杂乱的数据，基于分析模型做可视化，搭建描述性的数据看板。然后基于描述性的数据挖掘问题，提出假设做优化，或者基于用户特征数据进行预测分析找规律，基于规律设计策略。简... 查看详情

clickhouse(流量分析(一).漏斗分析案例)

参考技术A神策用户分析模型——漏斗分析的使用方法Clickhouse数据模型之有序漏斗分析Hologres漏斗分析函数JavaUDFStarRocksDocswindow—滑动窗户的大小，单位是秒。mode-这是一个可选的参数。‘strict’-当‘strict’设置时，windowFunnel()仅... 查看详情

推荐系统实践（项亮）—第2章利用用户行为数据

2.1用户行为数据简介用户行为数据可分为显性反馈行为和隐性反馈行为；用户数据的统一表示；2.2用户行为分析　　在设计推荐算法之前需要对用户行为数据进行分析，了解数据中蕴含的一般规律可以对算法的设计起到指导作用... 查看详情

用户行为分析模型实践——h5通用分析模型

...ivo互联网大数据团队-ZhaoWei、TianFengbiao、LiXiong本文从提升用户行为分析效率角度出发，详细介绍了H5埋点方案规划，埋点数据采集流程，提供可借鉴的用户行为数据采集方案；且完整呈现了针对页面分析，留存分析的数仓模型规... 查看详情