基于ClickHouse的用戶行為分析實踐

點擊上方藍色字體，選擇“設為星標”

暴走大數(shù)據(jù)

點擊右側(cè)關注，暴走大數(shù)據(jù)！

前言

ClickHouse為用戶提供了豐富的多參聚合函數(shù)（parametric aggregate function）和基于數(shù)組+Lambda表達式的高階函數(shù)（higher-order function），將它們靈活使用可以達到魔法般的效果。在我們的體系中，ClickHouse定位點擊流數(shù)倉，所以下面舉幾個用它來做用戶行為（路徑）分析的實戰(zhàn)例子，包括：

• 路徑匹配

• 智能路徑檢測

• 有序漏斗轉(zhuǎn)化

• 用戶留存

• Session統(tǒng)計

路徑匹配

CK默認提供了sequenceMatch函數(shù)檢查是否有事件鏈滿足輸入的模式，sequenceCount函數(shù)則統(tǒng)計滿足輸入模式的事件鏈的數(shù)量。示例：

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 │
└─────────┴───────────┘

這兩個函數(shù)都需要指定模式串、時間列和期望的事件序列（最多可指定32個事件）。模式串的語法有以下三種：

• (?N)：表示時間序列中的第N個事件，從1開始。例如上述SQL中，(?2)即表示event_type = 'shtKkclick' AND column_type = 'homePage'。

• (?t op secs)：插入兩個事件之間，表示它們發(fā)生時需要滿足的時間條件（單位為秒）。例如上述SQL中，(?1)(?t<=15)(?2)即表示事件1和2發(fā)生的時間間隔在15秒以內(nèi)。

• .*：表示任意的非指定事件。

智能路徑檢測

CK內(nèi)置的sequenceMatch和sequenceCount函數(shù)只能滿足部分需求，現(xiàn)有一個更復雜的需求：

給定期望的路徑終點、途經(jīng)點和最大事件時間間隔，查詢出符合條件的路徑詳情及符合路徑的用戶數(shù)（按用戶數(shù)降序排列）。

目前并沒有現(xiàn)成的函數(shù)可以直接出結(jié)果，但是我們可以曲線救國，用數(shù)組和高階函數(shù)的組合間接實現(xiàn)。完整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函數(shù)整理成<時間, <事件名, 頁面名>>的元組，并用arraySort函數(shù)按時間升序排序；

2. 利用arrayEnumerate函數(shù)獲取原始行為鏈的下標數(shù)組；

3. 利用arrayFilter和arrayDifference函數(shù)，過濾出原始行為鏈中的分界點下標。分界點的條件是路徑終點或者時間差大于最大間隔；

4. 利用arrayMap和has函數(shù)獲取下標數(shù)組的掩碼（由0和1組成的序列），用于最終切分，1表示分界點；

5. 調(diào)用arraySplit函數(shù)將原始行為鏈按分界點切分成單次訪問的行為鏈。注意該函數(shù)會將分界點作為新鏈的起始點，所以前面要將分界點的下標加1；

6. 調(diào)用arrayJoin和arrayCompact函數(shù)將事件鏈的數(shù)組打平成多行單列，并去除相鄰重復項。

7. 調(diào)用hasAll函數(shù)確定是否全部存在指定的途經(jīng)點。如果要求有任意一個途經(jīng)點存在即可，就換用hasAny函數(shù)。當然，也可以修改WHERE謂詞來排除指定的途經(jīng)點。

8. 將最終結(jié)果整理成可讀的字符串，按行為鏈統(tǒng)計用戶基數(shù)，完成。

有序漏斗轉(zhuǎn)化

CK提供了windowFunnel函數(shù)實現(xiàn)漏斗，以指定時長（單位為秒）滑動窗口按序匹配事件鏈，并返回在窗口內(nèi)轉(zhuǎn)化到的步數(shù)。如有多種匹配，以步數(shù)最大（轉(zhuǎn)換最深）的為準。

通過對該步數(shù)進行統(tǒng)計，即可得到漏斗中每步的轉(zhuǎn)化率。SQL語句如下，查詢結(jié)果是敏感數(shù)據(jù)，不再貼出來了。

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
);

如果想要更準確一些，實現(xiàn)漏斗步驟之間的字段關聯(lián)（如商品詳情→加入購物車→下單三步中的商品ID關聯(lián)）怎么辦呢？可以利用https://github.com/housepower/olap2018項目中提出的xFunnel函數(shù)。它是windowFunnel函數(shù)的鼻祖，不過需要修改ClickHouse源碼并重新編譯之，今后有時間的話會簡單寫一下過程。

用戶留存

retention函數(shù)可以方便地計算留存情況。該函數(shù)接受多個條件，以第一個條件的結(jié)果為基準，觀察后面的各個條件是否也滿足，若滿足則置1，不滿足則置0，最終返回0和1的數(shù)組。通過統(tǒng)計1的數(shù)量，即可計算出留存率。

下面的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統(tǒng)計

Session，即"會話"，是指在指定的時間段內(nèi)在網(wǎng)站/H5/小程序/APP上發(fā)生的一系列用戶行為的集合。例如，一次會話可以包含多個頁面瀏覽、交互事件等。Session是具備時間屬性的，根據(jù)不同的切割規(guī)則，可以生成不同長度的Session。

可見，Session統(tǒng)計與上述智能路徑檢測的場景有相似之處，都需要尋找用戶行為鏈的邊界并進行切割。以下SQL語句以30分鐘為超時時間，按天統(tǒng)計所有用戶的Session總數(shù)（跨天的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;

版權聲明：

本文為大數(shù)據(jù)技術與架構整理，原作者獨家授權。未經(jīng)原作者允許轉(zhuǎn)載追究侵權責任。

編輯｜胡曉

微信公眾號｜import_bigdata

歡迎點贊+收藏+轉(zhuǎn)發(fā)朋友圈素質(zhì)三連

文章不錯？點個【在看】吧！??