基于ClickHouse的用户行为分析实践是怎样的

基于ClickHouse的用户行为分析实践是怎样的

基于ClickHouse的用户行为分析实践是怎样的,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。

前言

ClickHouse为用户提供了丰富的多参聚合函数(parametric aggregate function)和基于数组+Lambda表达式的高阶函数(higher-order function),将它们灵活使用可以达到魔法般的效果。在我们的体系中,ClickHouse定位点击流数仓。

基于ClickHouse的用户行为分析实践是怎样的

路径匹配

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

SELECTsite_id,sequenceMatch('(?1)(?t<=15)(?2).*(?3)')(ts_date_time,event_type='shtLogon',event_type='shtKkclick'ANDcolumn_type='homePage',event_type='shtAddCart')ASis_matchFROMods.analytics_access_log_allWHEREts_date>='2020-07-01'ANDsite_idIN(10266,10022,10339,10030)GROUPBYsite_id;┌─site_id─┬─is_match─┐│10030│1││10339│1││10266│1││10022│1│└─────────┴──────────┘

SELECTsite_id,sequenceCount('(?1)(?t<=15)(?2).*(?3)')(ts_date_time,event_type='shtLogon',event_type='shtKkclick'ANDcolumn_type='homePage',event_type='shtAddCart')ASseq_countFROMods.analytics_access_log_allWHEREts_date>='2020-07-01'ANDsite_idIN(10266,10022,10339,10030)GROUPBYsite_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语句如下,略长:

SELECTresult_chain,uniqCombined(user_id)ASuser_countFROM(WITHtoUInt32(maxIf(ts_date_time,event_type='shtOrderDone'))ASend_event_maxt,arrayCompact(arraySort(x->x.1,arrayFilter(x->x.1<=end_event_maxt,groupArray((toUInt32(ts_date_time),(event_type,column_type))))))ASsorted_events,arrayEnumerate(sorted_events)ASevent_idxs,arrayFilter((x,y,z)->z.1<=end_event_maxtAND(z.2.1='shtOrderDone'ORy>600),event_idxs,arrayDifference(sorted_events.1),sorted_events)ASgap_idxs,arrayMap(x->x+1,gap_idxs)ASgap_idxs_,arrayMap(x->if(has(gap_idxs_,x),1,0),event_idxs)ASgap_masks,arraySplit((x,y)->y,sorted_events,gap_masks)ASsplit_eventsSELECTuser_id,arrayJoin(split_events)ASevent_chain_,arrayCompact(event_chain_.2)ASevent_chain,hasAll(event_chain,[('shtKkClick','homePage')])AShas_midway_hit,arrayStringConcat(arrayMap(x->concat(x.1,'#',x.2),event_chain),'->')ASresult_chainFROM(SELECTts_date,ts_date_time,event_type,column_type,user_idFROMods.analytics_access_log_allWHEREts_date>='2020-06-30'ANDts_date<='2020-07-02'ANDsite_idIN(10266,10022,10339,10030))GROUPBYuser_idHAVINGlength(event_chain)>1)WHEREevent_chain[length(event_chain)].1='shtOrderDone'ANDhas_midway_hit=1GROUPBYresult_chainORDERBYuser_countDESCLIMIT20;

简述思路:

  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语句如下,查询结果是敏感数据,不再贴出来了。

SELECTlevel,user_count,conv_rate_percentFROM(SELECTlevel,uniqCombined(user_id)ASuser_count,neighbor(user_count,-1)ASprev_user_count,if(prev_user_count=0,-1,round(user_count/prev_user_count*100,3))ASconv_rate_percentFROM(SELECTuser_id,windowFunnel(900)(ts_date_time,event_type='shtLogon',event_type='shtKkClick'ANDcolumn_type='homePage',event_type='shtOpenGoodsDetail',event_type='shtAddCart',event_type='shtOrderDone')ASlevelFROM(SELECTts_date,ts_date_time,event_type,column_type,user_idFROMods.analytics_access_log_allWHEREts_date>='2020-06-30'ANDts_date<='2020-07-02'ANDsite_idIN(10266,10022,10339,10030))GROUPBYuser_id)WHERElevel>0GROUPBYlevelORDERBYlevelASC);

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

用户留存

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

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

SELECTsum(ret[1])ASoriginal,sum(ret[2])ASnext_day_ret,round(next_day_ret/original*100,3)ASnext_day_ratio,sum(ret[3])ASseven_day_ret,round(seven_day_ret/original*100,3)ASseven_day_ratioFROM(WITHtoDate('2020-06-24')ASfirst_dateSELECTuser_id,retention(ts_date=first_date,ts_date=first_date+INTERVAL1DAY,ts_date=first_date+INTERVAL7DAY)ASretFROMods.ms_order_done_allWHEREts_date>=first_dateANDts_date<=first_date+INTERVAL7DAYGROUPBYuser_id);

Session统计

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

可见,Session统计与上述智能路径检测的场景有相似之处,都需要寻找用户行为链的边界并进行切割。以下SQL语句以30分钟为超时时间,按天统计所有用户的Session总数(跨天的Session也会被切割)。

SELECTts_date,sum(length(session_gaps))ASsession_cntFROM(WITHarraySort(groupArray(toUInt32(ts_date_time)))AStimes,arrayDifference(times)AStimes_diffSELECTts_date,arrayFilter(x->x>1800,times_diff)ASsession_gapsFROMods.analytics_access_log_allWHEREts_date>='2020-06-30'GROUPBYts_date,user_id)GROUPBYts_date;

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注恰卡编程网行业资讯频道,感谢您对恰卡编程网的支持。

发布于 2021-12-28 22:15:58
收藏
分享
海报
0 条评论
39
上一篇:EMC存储崩溃raid离线恢复数据方法是什么 下一篇:stata中的自相关检验操作及其分析是怎么样的
目录

    0 条评论

    本站已关闭游客评论,请登录或者注册后再评论吧~

    忘记密码?

    图形验证码