Shulou(Shulou.com)06/01 Report--

This article will explain in detail what the ordered funnel is in the clickhouse data model. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

What is an ordered funnel? the ordered funnel needs to satisfy that all the operations on the user event chain are related to the prowl time, and the funnel event cannot have a fault, and the user who touches the current event layer also needs to experience the previous event layer.

Preface

Suppose we have got two main paths of touchdown payment: home page-> details page-> purchase page-> payment "and" search page-> details page-> purchase page-> payment ", but we do not know which path has a high conversion rate. Then funnel analysis will be useful at this time.

The funnel model is an inverted pyramid shape, which is mainly used to analyze the transformation between the page and the page function module. The lower layer is based on the transformation of the adjacent upper layer, that is to say, the former condition is the basis of the latter condition. To solve such scenarios, clickhouse provides a function called windowFunnel to implement: windowFunnel (window) (timestamp, cond1, cond2,..., condN) window: window size, starting from the first event, moving one window size back to extract event data timestamp: can be a time or timestamp type, used to sort time events cond: events satisfied at each layer are easy to understand, here is a simple chestnut. # create a user behavior table that contains at least time, event, and user idCREATE TABLE test.action (`uid` Int32, `event_ type` String, `time` datetime) ENGINE = MergeTree () PARTITION BY uidORDER BY xxHash42 (uid) SAMPLE BY xxHash42 (uid) SETTINGS index_granularity = 8192 insert test data insert into action values (1 'browse', '2020-01-02 11v 0000') Insert into action values (1meme 'click', '2020-01-02 11 insert into action values'); insert into action values (1 Magi 'place an order', '2020-01-02 11 insert into action values'); insert into action values (1 Magi 'pay', '2020-01-02 11 Suzhou 3015')

Insert into action values (2 Magi 'place an order', '2020-01-02 11 purse 0000'); insert into action values (2 Magi 'pay', '2020-01-02 11 10 purl')

Insert into action values (1Jing 'browsing', '2020-01-02 11purl 0000')

Insert into action values (3meme 'browsing', '2020-01-02 11 / 11 / 20 / 20); insert into action values (3 / 7' click', '2020-01-02 12 / 12 / 01 / 02 / 12)

Insert into action values (4Jing 'browsing', '2020-01-02 11 50'); insert into action values (4 'click', '2020-01-02 12')

Insert into action values (5meme 'browsing', '2020-01-02 11 purl 50 insert into action values'); insert into action values (5 recorder 'click', '2020-01-02 12 purse 00'); insert into action values (5 Magi' place order', '2020-01-02 11 purl 10 purl')

Insert into action values (6 Magi 'browsing', '2020-01-02 11 purse 50 insert into action values'); insert into action values (6 Magi 'Click', '2020-01-02 12 purse 00'); insert into action values (6 Magi' place order', '2020-01-02 12 insert into action values 10 insert into action values') It has been 30 minutes as a time window to see what data windowFunnel returned SELECT user_id, windowFunnel (1800) (time, event_type = 'browse', event_type = 'click', event_type = 'place an order', event_type = 'pay') AS level FROM (SELECT time, event_type, uid AS user_id FROM action) GROUP BY user_id

┌─ user_id ─┬─ level ─┐ │ 3 │ 1 │ │ 2 │ 0 │ │ 5 │ 2 │ │ 1 │ 4 │ │ 6 │ 3 │ └─┴─┘ here level only records the level to which the last event in the path belongs. If level is grouped directly, the previous hierarchical data will be lost, resulting in the funnel unable to present the pyramid model and continue to use the above test data. The above result data are processed twice through the high-order function of the array to obtain the complete funnel display effect. Case

Analyze the transformation of the path of "2020-01-02" as "browse-> click-> place order-> pay". SELECT level_index,count (1) FROM (SELECT user_id, arrayWithConstant (level, 1) levels, arrayJoin (arrayEnumerate (levels)) level_index FROM (SELECT user_id, windowFunnel (1800) (time, event_type = 'browse', event_type = 'click', event_type = 'place order' Event_type = 'pay') AS level FROM (SELECT time, event_type, uid as user_id FROM test.action WHERE toDate (time) = '2020-01-02') GROUP BY user_id)) group by level_indexORDER BY level_index

This is the end of the article on "what is an orderly funnel in the clickhouse data model". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.