Consecutive Event Sequence Matching in Clickhouse

user_6440 0
Viewed 326

I am trying to do some funnel analysis using Clickhouse. I am aware of sequenceMatch/windowFunnel functions but they allow events in between sequences. I am trying to show how many users navigated to a certain a certain path with different querystring params consecutively.

Given the following array [url, eventsequence]

['/someurl/page?a=1', 1]
['/someurl/page?a=2', 2]
['/someurl/page?a=3', 4]
['/someurl/page?a=4', 5]
['/someurl/page?a=4', 6]

I would like to evaluate that the above sequence of events saw the user navigate directly from page to page 3 seperate times, events 1->2, 4->5 and 5-6.

clickhouse
1 Answers

Worked this out - you can pass in a sequence to sequenceCount and use the pattern which says make sure the events have no gaps

(?1)(?t<=1)(?2)

 sequenceCount('(?1)(?t<=1)(?2)')(sequence,
        ilike(page, '%a%'),
        ilike(page, '%a%')) as sequences
user_64400
Related