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

How to carry on the session analysis of clickhouse data model, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

What is a session,Session, or session, is a collection of user behaviors that occur on your website / H5 / Mini Program / APP within a specified period of time. For example, a session can contain multiple page views, interactive events, and so on.

Preface

Session has a time attribute, and different lengths of Session; can be generated according to different cutting rules. Session statistics are similar to the above intelligent path detection scenarios, so you need to find the boundaries of the user behavior chain for processing. Session analysis mainly consists of two parts:

Session cutting; for example, a user visits your website, opens a web page, and leaves the computer for something. If you come back for a few hours and continue to visit, how many times should the user's session visits be counted? For example, users add a shopping cart on the pc side and complete the payment on the mobile side, which should be counted as several times.

Session index statistics; session analysis common analysis indicators are, session access times, access depth, access duration, bounce rate and so on.

Model

Case one

Take 30 minutes as the timeout period to count the total number of Session of all users by day (cross-day Session will also be cut)

SELECT ts_date, sum (length (session_gaps)) AS session_cnt # calculates the number of sessions FROM (WITH arraySort (groupArray (toUnixTimestamp (time) AS times, arrayDifference (times) AS times_diff # solves the time difference between adjacent events SELECT toDate (time) as ts_date, arrayFilter (x-> x > 1800) Times_diff) AS session_gaps # set cutting point FROM app.scene_tracker WHERE toDate (time) > = '2020-09-06' AND toDate (time) y > 1800 or z.2payment 'member payment succeeded', event_idxs, arrayDifference (sorted_events.1), sorted_events) as gap_index, arrayMap (x-> xx, gap_index) as gap_index_, arrayMap (x-> if (has (gap_index_, x), 1) 0), event_idxs) as gap_marks, arraySplit ((XMago y)-> y, sorted_events, gap_marks) as session_chain SELECT toDate (time) as ts_date, Uzzi as user_id, arrayJoin (session_chain) event_chain_, arrayCompact (x-> x.2, event_chain_) event_chain FROM app.scene_tracker WHERE toDate (time) > = '2020-09-06' AND toDate (time)

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.