How to do Flow Analysis in Clickhouse

[Đức Giang Nguyễn]

Hi,

I'm doing a Flow User Analysis in ClickHouse ( like here https://docs.clevertap.com/docs/flows)

Let me illustrate with a specific example. Could anyone help me how to solve in Clickhouse? We have a series of app user event: 

Time                             User         Page ID

1                                     A              App Launched 

5                                     A              App Launched 

10                                   A              App Launched

3                                      A              Content Click

4                                      A              Content Click 

6                                      A              Content Click

I want to do flow analysis, eg: count number user via flow with different depth with different window time. Ex: depth = 3, window time = 10, user A via flow App Launched -> App Launched -> App Launched, etc. 

I found windowFunnel function in CH , but it doesn't count duplicate event, and sequenceMatch function, but it doesn't have window time.

Could anyone have solution for my case?

Thank in advance !

[Đức Giang Nguyễn]

ddit me may thang ngu :) 

Vào lúc 14:37:41 UTC+7 ngày Thứ Tư, 21 tháng 12, 2022, Đức Giang Nguyễn đã viết: