How to realize funnel analysis in ClickHouse

Question

I want to do funnel analysis based on buried point data that are stored in ClickHouse. Let's define a few elements for funnel analysis:

1. A series of events: A (event_id = 1) -> B (event_id = 2) -> C (event_id = 3)

2. Time period: 0 (event_ms) ~ 500 (event_ms)

3. Time window: 100 (event_ms)

I want to know, for each user, if there is an event series (A->B->C) happened within the time period, and intervals between A and C is within the time window.

Here is my test dataset:

CREATE TABLE test_dataset
(
    `event_id` UInt64,
    `event_ms` UInt64,
    `uid` UInt64 // user_id
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(toDate(event_ms))
ORDER BY (event_id, event_ms,uid)
SETTINGS index_granularity = 8192;

INSERT INTO TABLE test_dataset VALUES 
      (1, 100, 123), 
      (1, 120, 123), 
      (1, 130, 123), 
      (1, 150, 345), 
      (1, 180, 345), 
      (2, 150, 123), 
      (2, 200, 234), 
      (2, 140, 345),
      (2, 210, 345),
      (2, 300, 345),
      (3, 180, 123),
      (3, 250, 123),
      (3, 290, 234),
      (3, 270, 345);

I use join to find all qualified event series:

SELECT
    t1.event_ms, t2.event_ms, t3.event_ms, t4.event_ms,
    t1.uid, t2.uid, t3.uid, t4.uid
FROM
(SELECT 
    uid, event_ms 
FROM funnel_join_test_1
WHERE
    event_id = 1 AND event_ms >= 0 AND event_ms <= 500) as t1
ASOF left join
(SELECT 
    uid, event_ms 
FROM funnel_join_test_1
WHERE
    event_id = 2 AND event_ms >= 0 AND event_ms <= 500) as t2
ON t1.uid = t2.uid AND t1.event_ms  < t2.event_ms
ASOF left join
(SELECT 
    uid, event_ms 
FROM funnel_join_test_1
WHERE
    event_id = 3 AND event_ms >= 0 and event_ms <= 500) as t3
ON t2.uid = t3.uid and t2.event_ms < t3.event_ms
ASOF left join
(SELECT 
    uid, event_ms 
FROM funnel_join_test_1
WHERE
    event_id = 3 AND event_ms >= 0 and event_ms <= 500) as t4
ON t3.uid = t4.uid and t4.event_ms < t1.event_ms + 100
WHERE t4.event_ms > 0;

Here are all qualified event series:

┌─t1.event_ms─┬─t2.event_ms─┬─t3.event_ms─┬─t4.event_ms─┬─t1.uid─┬─t2.uid─┬─t3.uid─┬─t4.uid─┐
│         180 │         210 │         270 │         270 │    345 │    345 │    345 │    345 │
└─────────────┴─────────────┴─────────────┴─────────────┴────────┴────────┴────────┴────────┘
┌─t1.event_ms─┬─t2.event_ms─┬─t3.event_ms─┬─t4.event_ms─┬─t1.uid─┬─t2.uid─┬─t3.uid─┬─t4.uid─┐
│         120 │         150 │         180 │         180 │    123 │    123 │    123 │    123 │
└─────────────┴─────────────┴─────────────┴─────────────┴────────┴────────┴────────┴────────┘
┌─t1.event_ms─┬─t2.event_ms─┬─t3.event_ms─┬─t4.event_ms─┬─t1.uid─┬─t2.uid─┬─t3.uid─┬─t4.uid─┐
│         130 │         150 │         180 │         180 │    123 │    123 │    123 │    123 │
└─────────────┴─────────────┴─────────────┴─────────────┴────────┴────────┴────────┴────────┘
┌─t1.event_ms─┬─t2.event_ms─┬─t3.event_ms─┬─t4.event_ms─┬─t1.uid─┬─t2.uid─┬─t3.uid─┬─t4.uid─┐
│         100 │         150 │         180 │         180 │    123 │    123 │    123 │    123 │
└─────────────┴─────────────┴─────────────┴─────────────┴────────┴────────┴────────┴────────┘

Then I know user 123 and 345 have such event series within the time period. Using join is pretty slow in ClickHouse, is there any other way to work around this problem?

By the way, I don't need to know all qualified series, I only want to know if there is one such event series for each user.

Answer 1

There are function windowFunnel that searches for chain of events in sliding window.

SELECT
    uid,
    windowFunnel(100)(event_ms, event_id = 1, event_id = 2, event_id = 3) AS chain_len
FROM test_dataset
WHERE (event_ms > 0) AND (event_ms < 500)
GROUP BY uid;

Result:

┌─uid─┬─chain_len─┐
│ 234 │         0 │
│ 345 │         3 │
│ 123 │         3 │
└─────┴───────────┘

It returns matched chain length, so for users 345 and 123 we have 3 that means that whole chain is matched.

If we decrease window to 10 it will find only beginning of chain and don't match futher events due to condition timestamp of event 2 <= timestamp of event 1 + window is not hold.

SELECT
    uid,
    windowFunnel(10)(event_ms, event_id = 1, event_id = 2, event_id = 3) AS chain_len
FROM test_dataset
WHERE (event_ms > 0) AND (event_ms < 500)
GROUP BY uid

Result:

┌─uid─┬─chain_len─┐
│ 234 │         0 │
│ 345 │         1 │
│ 123 │         1 │
└─────┴───────────┘

So, to check that is there such chain for user you can check that windowFunnel matched appropriate number of events.

Restriction on time interval (Time period: 0 (event_ms) ~ 500 (event_ms)), is simply handled in WHERE clause.

Add more events out of period:

INSERT INTO TABLE test_dataset VALUES (1, 600, 234), (2, 601, 234), (3, 602, 234);

Then check:

SELECT
    uid,
    windowFunnel(100)(event_ms, event_id = 1, event_id = 2, event_id = 3) AS chain_len
FROM test_dataset
WHERE (event_ms > 0) AND (event_ms < 500)
GROUP BY uid

Result:

┌─uid─┬─chain_len─┐
│ 234 │         0 │
│ 345 │         3 │
│ 123 │         3 │
└─────┴───────────┘

Without WHERE

SELECT
    uid,
    windowFunnel(100)(event_ms, event_id = 1, event_id = 2, event_id = 3) AS chain_len
FROM test_dataset
GROUP BY uid

Result:

┌─uid─┬─chain_len─┐
│ 234 │         3 │
│ 345 │         3 │
│ 123 │         3 │
└─────┴───────────┘