A user behavior analysis system

Abstract

The present invention discloses a user behavior analysis system, comprising a user behavior session module, a user retention analysis module, a user behavior matching module and a user funnel analysis module; the user behavior session module is used to generate a user behavior session data table; the user retention analysis module is used to generate a user retention rate daily table; the user behavior matching module is used to generate a user behavior matching data table; the user funnel analysis module is used to match event chains in sequence based on a given sliding window, calculate the number of conversion steps within the window event and the number of conversions in each stage, and generate a user funnel conversion analysis table; the present invention has the advantages of solving the problems of the prior art being limited by the physical memory of a single machine, complex development and difficulty in achieving real-time response.

Description

Technical Field

[0001] The present invention relates to the field of user behavior analysis, and more particularly to a user behavior analysis system. Background Art

[0002] In recent years, with the rapid development of global internet technology, the volume of user behavior log data has grown exponentially. The underlying technology behind user behavior analysis software has undergone numerous iterations, and traditional database technology has long been overwhelmed. Big data technologies, such as the Hadoop ecosystem, also face performance, stability, and real-time responsiveness issues. Currently, analytical software places increasingly stringent demands on the real-time performance of the underlying OLAP technology.

[0003] User behavior analysis analyzes user behavior on a product and the data behind that behavior. By building user behavior models and profiles, it can influence product decisions, achieve refined operations, and guide business growth. One way to implement user behavior analysis algorithms is by writing processing programs. Based on the algorithm's intent, the program reads the relevant data from the database and then processes the algorithm within the program framework. Generally, such programs can only run on a single node and cannot process data in parallel. Limited by the memory capacity of the single node on which the program runs, they are generally unable to handle large amounts of data. Another implementation method is to use big data tools such as Hive or Spark. For example, the paper "Ding Pengcheng. User Behavior Analysis System Based on Big Data, School of Information Science and Engineering, Ningbo University, 2021" discloses a distributed cluster system platform architecture using Spark and provides in-depth design of a user behavior data analysis system. Using big data processing requires writing a series of array processing and aggregation functions to compensate for the shortcomings of basic big data algorithms. Development is complex and also subject to the programmer's level of skill, resulting in differences in performance, efficiency, and stability. In addition, the above-mentioned existing technologies are not only limited by the physical memory of a single machine and are complex to develop, but also have difficulty in achieving real-time response in terms of processing speed. Summary of the Invention

[0004] The technical problem to be solved by the present invention is that the user behavior analysis system in the prior art is not only limited by the physical memory of a single machine and is complex to develop, but also has difficulty in achieving real-time response in terms of processing speed.

[0005] The present invention solves the above technical problems through the following technical means: a user behavior analysis system, which analyzes user behavior based on the native multi-parameter aggregation function and high-order function in the ClickHouse distributed database, including a user behavior session module, a user retention analysis module, a user behavior matching module and a user funnel analysis module;

[0006] The user behavior session module is used to extract the user identification field, behavior identification field, and behavior time field from the original access log table, generate a session ID, a session start timestamp field, a session end timestamp field, and a user behavior chain field, and sort and output them according to the user identification field and the session ID field to form an ordered user behavior session data set, which is saved as a user behavior session data table;

[0007] The user retention analysis module is used to calculate the retention rate on the first day and within a preset period based on given date parameters, and generate a daily table of user retention rates;

[0008] The user behavior matching module is used to screen out user data sets that meet the corresponding patterns based on given behavior pattern parameters with or without order requirements, and generate a user behavior matching data table;

[0009] The user funnel analysis module is used to match event chains in sequence based on a given sliding window, calculate the number of conversion steps within the window event and the number of conversions in each stage, and generate a user funnel conversion analysis table.

[0010] The present invention analyzes user behavior based on the native multi-parameter aggregation functions and high-order functions in the ClickHouse distributed database. As an OLAP distributed database with outstanding performance, ClickHouse has obvious advantages in real-time calculation of massive data. It is very suitable for use as the underlying data warehouse of analysis software. Data memory is not limited, and there is no need to write a series of array processing functions and aggregation functions to make up for the deficiencies of the basic algorithm functions of big data. The development process is relatively simple, which solves the problems of existing technologies being limited by the physical memory of a single machine, complex development, and difficulty in achieving real-time response.

[0011] Furthermore, the user behavior session module extracts the user identification field, behavior identification field, and behavior time field from the original access log table to generate a session ID, including:

[0012] Extract the user ID field, behavior ID field, and behavior time field from the original access log table. Convert the behavior time field to a timestamp format. Then sort the extracted data set from small to large based on the value of the behavior time field.

[0013] The dataset is GROUP BY-ed based on the user ID field, and the groupArray function is used to aggregate the corresponding behavior ID field and behavior time field into a behavior ID array field and a behavior timestamp array field, respectively.

[0014] Use the arrayDifference function to calculate the difference between adjacent elements in the behavior timestamp array field to generate the user behavior interval array field;

[0015] Use the arrayMap function to compare the behavior interval in the behavior interval array field with the preset session time threshold. If the behavior interval is less than or equal to the preset session time threshold, it indicates that the user's behavior records are from the same session. If it is greater than the session time threshold, it is considered to be two sessions. The resulting array is the new session ID array field. An element value of 1 indicates a new session, and a value of 0 indicates an old session. The first element of the new session ID array field is set to 1.

[0016] Use ARRAY JOIN to perform a row-to-column expansion operation on the behavior ID array field, behavior timestamp array field, and new session ID array field. Each user's single-row record is expanded into multiple rows based on the number of elements in the array. After expansion, the user ID field, behavior ID field, behavior timestamp field, and new session ID field are generated.

[0017] Use the arrayEnumerate function to return the array index for the new session ID field. Use the arrayJoin function to convert the result of the arrayEnumerate function into columns to generate the session index session_index. Then, use the arraySlice(is_new_session_array,1,session_index) function to slice each row of data. The three input parameters of arraySlice are the new session ID field, the slice offset, and the slice length. Then use the arraySum function to sum the elements in the sliced ​​array, and the sum value is regarded as the session ID.

[0018] Furthermore, the user behavior session module generates a session start timestamp field, a session end timestamp field, and a user behavior chain field, including:

[0019] Perform a left join operation on the behavior ID field and the behavior name field of the behavior information dimension table to convert the behavior ID field into the behavior name field;

[0020] Sort the dataset by the user ID field and the behavior timestamp field, and then perform a GROUP BY aggregation operation on the sorted dataset based on the user ID field and the session ID field.

[0021] After aggregation, the minimum behavior timestamp in each session is used as the session's start timestamp field, and the maximum behavior timestamp is used as the session's end timestamp field. The behavior name fields in the start and end timestamp fields are processed into a behavior name array using the groupArray function. The elements in the array are then concatenated using the arrayStringConcat function to generate the user behavior chain field.

[0022] Furthermore, the construction process of the original access log table is as follows: the log data of the system where the analysis object is located is stored in the ClickHouse database, and then the original access log table is generated. The original access log table contains at least a user identification field, a behavior identification field, and a behavior time field.

[0023] Furthermore, the user retention analysis module is also used to:

[0024] Extract the user ID field and behavior time field from the original access log table, and use the toDate function on the behavior time field to generate the user visit date field visit_day;

[0025] Based on the given date parameter first_day, the INTERVAL operator of ClickHouse is used to generate date data for the 1st, 2nd, ..., and Nth day for subsequent calculations, where day 0 is first_day;

[0026] Use the equals function to set equals(visit_day, first_day) as the first judgment condition parameter of the retention function. Similarly, the Nth judgment condition parameter is equals(visit_day, first_day + INTERVALN DAY). By default, the result of the first judgment condition parameter is retained. The results of all calculated judgment condition parameters are saved in the result array of the retention function.

[0027] Perform a GROUP BY on the result array of the retention function using the user ID field to calculate the user access statistics from day 0 to day N.

[0028] The retention rate on day N is calculated by dividing the number of visits on day N by the number of visits on day 0. This method is used to calculate the retention rate from day 0 to day N and save it as a daily user retention rate table.

[0029] Furthermore, the user behavior matching module is further configured to process user behavior matching with sequence requirements, wherein the user behavior matching with sequence requirements includes:

[0030] Configure user behavior event chains;

[0031] Configure the pattern string corresponding to the user's behavior;

[0032] Extract the user ID field, behavior ID field, and behavior time field from the original access log table, and perform a GROUP BY aggregation operation based on the user ID field.

[0033] After performing a GROUP BY aggregation operation on each user based on the user ID field, the behavior ID field is sorted in the order of the behavior time field. The sorted result is converted into a pattern string. The pattern string is input into the sequenceMatch function to determine whether there is a user behavior event chain that meets the input pattern. The user behavior matching result field is returned. The number of user behavior event chains that meet the input pattern is counted to generate the user behavior statistics result field.

[0034] Combine the results of the user behavior matching result field and the user behavior statistics result field to generate a user behavior matching data table.

[0035] Furthermore, the configuration of the user behavior event chain includes: converting one or more user actions into a judgment expression of a single event. When the user triggers an event, it is determined whether the event satisfies the judgment expression. If so, the user triggers the event in the user behavior event chain.

[0036] Furthermore, the pattern character string corresponding to the configuration user's behavior includes:

[0037] For example, if a user sequentially performs event A and then event B, the above behaviors are represented as (?1)(?2), where (?N) represents the index of the judgment expression of the user behavior event chain, and (?N) corresponds one-to-one to the events in the user behavior event chain.

[0038] The user experiences event A, then some unrelated actions, and then event B, which can be written as (?1).*(?2);

[0039] The user experienced event A twice, followed by event B. The above behavior is represented as (?1)(?2)(?3), where (?1) and (?2) correspond to event A, and (?3) corresponds to event B.

[0040] When there are time requirements between events, if event B occurs at least m seconds after event A, it is written as (?1)(?t>m)(?2); if event B occurs within L seconds after event A, it is written as (?1)(?t<=L)(?2); if event B occurs immediately Q seconds after event A, it is written as (?1)(?t==Q)(?2).

[0041] Furthermore, the user behavior matching module is further configured to process user behavior matching without sequence requirements, wherein the user behavior matching without sequence requirements includes:

[0042] Extract the user ID field, behavior ID field, and behavior time field from the original access log table to form the initial user behavior dataset;

[0043] Query the initial user behavior dataset to check whether each user event that needs to be matched has occurred. Merge user data with the same user event that needs to be matched together to form a sub-query dataset. Each sub-query dataset has a unique event index field.

[0044] Connect each subquery data set through UNION ALL to form an event set;

[0045] Perform a GROUP BY aggregation operation on the event set according to the user ID to generate a new data set;

[0046] The event indexes in the new dataset are aggregated and deduplicated using the groupUniqArray function to form an event index deduplication aggregate array.

[0047] Use the hasAll function to compare the event index de-aggregated array with the matching conditions, filter out the event index de-aggregated array that meets the matching conditions, and generate a user behavior matching data table.

[0048] Furthermore, the user funnel analysis module is further configured to: extract a user identification field, a behavior identification field, and a behavior time field from the original access log table to form a data set;

[0049] The dataset sorts the behavior identification field of each user in the user identification field in ascending order according to the value of the behavior time field;

[0050] Use the windowFunnel function to calculate the maximum number of consecutive trigger condition chains for each user within a given sliding time window and generate the user's longest trigger chain count field;

[0051] Use the arrayWithConstant function to process the user's longest trigger chain count field, generate an array of a specified length, and then convert the rows into columns to generate the phase index value field.

[0052] Perform a GROUP BY aggregation operation on the above dataset based on the stage index value field, and use the COUNT function to calculate the total number of users in each stage. Then sort the dataset based on the stage index value field and output a new dataset containing the stage index value and the total number of users in that stage. Save this new dataset as a user funnel conversion analysis table.

[0053] The advantages of the present invention are:

[0054] (1) The present invention analyzes user behavior based on the native multi-parameter aggregation functions and high-order functions in the ClickHouse distributed database. As an OLAP distributed database with excellent performance, ClickHouse has obvious advantages in real-time calculation of massive data. It is very suitable for use as the underlying data warehouse of analysis software. Data memory is not limited, and there is no need to write a series of array processing functions and aggregation functions to make up for the deficiencies of the basic algorithm functions of big data. The development process is relatively simple, which solves the problems of the existing technology being limited by the physical memory of a single machine, complex development, and difficulty in achieving real-time response.

[0055] (2) The present invention sets up a user behavior session module to generate a user behavior session data table for subsequent business queries and calculations, guide product decisions, achieve refined operations, and guide business growth.

[0056] (3) The present invention sets up a user retention analysis module, which calculates the retention rate on the first day and within a preset period based on given date parameters, and generates a daily table of user retention rates to analyze user usage and activity in the product, guide product decisions, achieve refined operations, and guide business growth.

[0057] (4) The present invention sets up a user behavior matching module, which filters out user data sets that meet the corresponding patterns based on given behavior pattern parameters with or without sequence requirements, generates a user behavior matching data table, and determines whether the user has performed a series of given operations, thereby guiding product decisions, achieving refined operations, and guiding business growth.

[0058] (5) The present invention sets up a user funnel analysis module, which matches the event chain in sequence based on a given sliding window, calculates the number of conversion steps within the window event and the number of conversions in each stage, and generates a user funnel conversion analysis table to guide product decision-making, achieve refined operations, and guide business growth. BRIEF DESCRIPTION OF THE DRAWINGS

[0059] Figure 1 A framework diagram of a user behavior analysis system disclosed in an embodiment of the present invention;

[0060] Figure 2 This is a workflow diagram of a user behavior session module of a user behavior analysis system disclosed in an embodiment of the present invention;

[0061] Figure 3 This is a workflow diagram of a user retention analysis module of a user behavior analysis system disclosed in an embodiment of the present invention;

[0062] Figure 4 This is a workflow diagram of a user behavior matching module of a user behavior analysis system disclosed in an embodiment of the present invention;

[0063] Figure 5 This is a workflow diagram of a user funnel analysis module of a user behavior analysis system disclosed in an embodiment of the present invention. DETAILED DESCRIPTION

[0064] To make the objectives, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. All other embodiments obtained by ordinary technicians in this field based on the embodiments of the present invention without making any creative efforts shall fall within the scope of protection of the present invention.

[0065] like Figure 1 As shown, a user behavior analysis system is based on the native multi-parameter aggregation function and high-order function of the ClickHouse distributed database to analyze user behavior, including a user behavior session module, a user retention analysis module, a user behavior matching module and a user funnel analysis module;

[0066] The user behavior session module is used to extract the user identification field, behavior identification field, and behavior time field from the original access log table, generate a session ID, a session start timestamp field, a session end timestamp field, and a user behavior chain field, and sort and output them according to the user identification field and the session ID field to form an ordered user behavior session data set, which is saved as a user behavior session data table;

[0067] The user retention analysis module is used to calculate the retention rate on the first day and within a preset period based on given date parameters, and generate a daily table of user retention rates;

[0068] The user behavior matching module is used to screen out user data sets that meet the corresponding patterns based on given behavior pattern parameters with or without order requirements, and generate a user behavior matching data table;

[0069] The user funnel analysis module is used to sequentially match event chains within a given sliding window, calculate the number of conversion steps within the window event and the number of conversions at each stage, and generate a user funnel conversion analysis table. The following details the working process of each module.

[0070] 1. User behavior session module

[0071] The user behavior session module aggregates scattered user behavior information through a certain algorithm, identifies it as a series of reasonable user behavior sessions, and can connect a series of user behaviors in the same session in chronological order. This module is based on a given session time threshold parameter (session_gap), and generates a user behavior session data table after a series of ClickHouse SQL processing on the data in the original access log table. Figure 2 The specific process is as follows:

[0072] (1) Extract the user ID (user_id), operation ID (operation_id), and operation time (operation_time) fields from the original access log table. The operation time field needs to be converted to a timestamp format to form the operation timestamp (operation_timestamp) field. The extracted data set is sorted from small to large according to the numerical order of the operation timestamp field.

[0073] (2) The dataset is GROUP BY-ed based on the user ID field, and the groupArray function is used to aggregate the corresponding behavior ID field and behavior timestamp field into a behavior ID array field and a behavior timestamp array field, respectively. The behavior ID array field and the behavior timestamp array field can store zero or more pieces of user behavior ID data and corresponding behavior timestamp data, respectively.

[0074] (3) Use the arrayDifference function to process the behavior timestamp array field. This function calculates the difference between adjacent elements in the array, that is, the number of seconds between two adjacent behaviors of a user, and the result is the user behavior interval array field.

[0075] (4) Use the arrayMap function to calculate the behavior interval array field. arrayMap is a high-order function that can write a lambda function as its input parameter. The other input parameter is the behavior interval array field that needs to be processed. arrayMap calls the lambda function once for each element in the array, and the calculation result is also returned as an array. The calculation logic is to compare the behavior interval with the preset session time threshold. If the behavior interval is less than or equal to the preset session time threshold, it means that it is a behavior record of the same session of the user. If it is greater than the session time threshold, it is considered to be two sessions. The returned result array is the new session identifier array field, where an element value of 1 indicates a new session and a value of 0 indicates an old session.

[0076] (5) The first element of the new session identifier array field needs to be processed. Because the first element of the new session identifier array field generated by the above method is 0, and in reality the first element will always be a new session, it should be set to 1. Therefore, it is necessary to use the arrayPopFront function to delete the first element and then use arrayPushFront to fill it with the value 1.

[0077] (6) The dataset now contains the user ID (user_id) field, the operation ID array (operation_id_array) field, the operation timestamp array (operation_timestamp_array) field, and the new session ID array (is_new_session_array) field. The elements of the operation ID array, the operation timestamp array, and the new session ID array are all in one-to-one correspondence according to the timestamp order.

[0078] (7) Perform a row-to-column expansion operation on the elements in each array. Use ARRAY JOIN to perform an array row-to-column expansion operation on the behavior ID array field, behavior timestamp array field, and new session ID array field. At this time, each user's single row record will be expanded into multiple rows based on the number of elements in the array. The expanded data set consists of the user ID field, behavior ID field, behavior timestamp field, and new session ID field.

[0079] (8) Then generate the session index (session_index) field. The generation of the session index field requires the use of the arrayEnumerate function in conjunction with ARRAY JOIN. This function returns the array index starting from 1 (similar to the row number function in the database). Specifically:

[0080] Assume that the original data is as shown in Table 1:

[0081] Table 1 Raw data

[0082] user_id is_new_session_array user1 [1,0,0,1,0,1]

[0083] The session index (session_index) is generated by using the arrayEnumerate function on the is_new_session_array array and then using arrayJoin to convert the rows to columns.

[0084] Among them, is_new_session_array is the new session identifier array, such as [1,0,0,1,0,1] means that 1, 4, and 6 are new sessions (1, 2, and 3 are one session, 4 and 5 are one session, and 6 is one session).

[0085] The arrayEnumerate function returns the array index, that is, the position value of each element of the array. For example, for the array [1,0,0,1,0,1], arrayEnumerate([1,0,0,1,0,1]) returns [1,2,3,4,5,6], as shown in Table 2.

[0086] Table 2 arrayEnumerate processing results

[0087] user_id is_new_session_array arrayEnumerate function processing user1 [1,0,0,1,0,1] [1,2,3,4,5,6]

[0088] At this point, arrayJoin is used to convert the rows and columns of the result processed by the arrayEnumerate function. One row of user data will be converted into 6 rows, which can be regarded as the session index, as shown in Table 3:

[0089] Table 3 Result of arrayEnumerate function processing row to column conversion

[0090] user_id is_new_session_array Session Index user1 [1,0,0,1,0,1] 1 user1 [1,0,0,1,0,1] 2 user1 [1,0,0,1,0,1] 3 user1 [1,0,0,1,0,1] 4 user1 [1,0,0,1,0,1] 5 user1 [1,0,0,1,0,1] 6

[0091] (9) The session id (session_id) requires an operation on the new session ID array field. First, use the arraySlice function to slice the new session ID array. Its parameters are the new session ID array, the step value 1, and the session index value. Then, the return value is processed by the arraySum function to generate the session id. The generated session id starts at 1, and each new session id will be the value of the previous session id plus the step value 1. Specifically:

[0092] For each row of records, use the arraySlice(is_new_session_array,1,session_index) function to cut.

[0093] arraySlice slices the is_new_session_array array, and the last two parameters are the offset and length of the slice. Here, it represents cutting out the array of session_index length from the first position, as shown in Table 4:

[0094] Table 4 cuts the is_new_session_array array

[0095] user_id is_new_session_array Session Index Use arraySlice function to slice user1 [1,0,0,1,0,1] 1 [1] user1 [1,0,0,1,0,1] 2 [1,0] user1 [1,0,0,1,0,1] 3 [1,0,0] user1 [1,0,0,1,0,1] 4 [1,0,0,1] user1 [1,0,0,1,0,1] 5 [1,0,0,1,0] user1 [1,0,0,1,0,1] 6 [1,0,0,1,0,1]

[0096] Then use arraySum to sum the elements in the array cut out in the previous step. The value can be regarded as the session ID, as shown in Table 5:

[0097] Table 5 Sum of the elements in the cut array

[0098]

[0099] (10) At this point, perform a left join operation on the action ID field and the action name (operation_name) field of the action information dimension table to retrieve and generate the corresponding action name field. Note: This operation is optional. The purpose is to convert the action ID field into the action name field by querying the dimension table for subsequent use.

[0100] (11) Next, the data set is sorted according to the user ID field and the behavior timestamp field, and the sorted data set is then subjected to a GROUP BY aggregation operation based on the user ID field and the session ID field.

[0101] (12) After aggregation, the minimum behavior timestamp in each session is used as the start timestamp field of the session, and the maximum behavior timestamp is used as the end timestamp field of the session. The behavior name is processed into a behavior name array through the groupArray function, and then the elements in the array are connected through the arrayStringConcat function to generate the user behavior chain field.

[0102] (13) The session start timestamp field and the session end timestamp field can be converted into normal year, month, day, hour, minute, and second fields using the ClickHouse timestamp conversion function.

[0103] (14) Finally, the user identification (user_id) field, session id (session_id) field, session start time (session_start_time) field, session end time (session_end_time) field, user behavior chain (user_operation_chain) and other fields in the data set are selected, and they are sorted according to the user identification field and session id field and then output to form an ordered user behavior session data set.

[0104] (15) Save the above data set as a user behavior session data table for subsequent business queries and calculations.

[0105] 2. User retention analysis module

[0106] User retention refers to the user staying and being continuously active on the product page. Retention analysis can be used to analyze the user's usage and activity in the product. This module uses ClickHouse SQL to calculate the retention rate of the first day and the 1st day, 2nd day, ... until the 31st day based on the given date parameters, and generates a daily table of user retention rates. Figure 3 As shown, the specific process is:

[0107] (1) Extract the user ID (user_id) field and the operation time (operation_time) field from the original access log table. Then use the toDate function on the operation time field to generate the user visit date (visit_day) field.

[0108] (2) The data set at this time includes the user ID (user_id) field, the behavior time (operation_time) field, and the user visit date (visit_day) field.

[0109] (3) Based on the given date parameter first_day, use ClickHouse's INTERVAL operator to generate date data for the 1st, 2nd, and 31st days for subsequent calculations. The generation method is: day 0 is first_day, the value of day 1 is first_day + INTERVAL 1DAY, the value of day 2 is first_day + INTERVAL 2DAY, and so on. ClickHouse will recognize the expression and convert it into the correct date for calculation.

[0110] (4) Use the retention function to calculate the retention. This function takes a set of conditions as parameters to indicate whether the event meets the specific conditions. Generally speaking, the result of the first condition is used as a benchmark to observe whether the subsequent conditions are also met. If they are met, they are set to 1 (meaning True), and if they are not met, they are set to 0 (meaning False). That is: if the first and second are both True, the second result will be True, otherwise the result will be False. If the first and third are both True, the third result will be True, and so on. Finally, the function returns an array containing 0 and 1 elements.

[0111] (5) You can use the equals function to determine whether the two input parameters are equal. In this case, equals(visit_day, first_day) should be used as the first parameter of the retention judgment condition, the second parameter should be equals(visit_day, first_day + INTERVAL 1DAY), and so on. If you need to calculate the retention status up to the 31st day, the last parameter should be equals(visit_day, first_day + INTERVAL 31DAY). The final calculation result array can be saved in the result variable.

[0112] (6) Since the result of the retention function is an array, we need to obtain the judgment value. We can use the arrayElement function to extract the array content. For example, arrayElement(result,1) represents the first element in the result array, and arrayElement(result,2) represents the second element in the result array.

[0113] (7) Then, the above calculation results are aggregated by user ID using GROUP BY, and the user's access statistics from day 0 to day 31 are counted. The statistical method is to use the sum function to accumulate each element of the result array. The results can be saved in the variables day0_retention to day31_retention.

[0114] (8) The retention rate on day N (dayN_retention_ratio) is calculated by dividing dayN_retention by day0_retention. Its value range is between 0 and 1.

[0115] (9) The retention rate value can then be multiplied by 100 and rounded to a specified number of decimal places using the round function to become a percentage value between 0 and 100.

[0116] (10) According to the above method, the retention rate data set from day 0 to day 31 is calculated by user ID. This data set contains the retention rate from day 0 (day0_retention_ratio) to day 31 (day31_retention_ratio) fields starting from this date.

[0117] (11) Save the above data set as a daily table of user retention rates for subsequent business queries and calculations.

[0118] 3. User behavior matching module

[0119] The user behavior matching module is used to determine whether the user has performed a series of given operations. A series of actions can be regarded as a combination of multiple events, which can be either with or without behavior order requirements, and the corresponding algorithms need to be implemented separately. This module is based on the given behavior pattern parameters with or without order requirements, and uses ClickHouse SQL to filter out the user data set that meets the pattern and generate a user behavior matching data table. Figure 4 As shown, the specific process is:

[0120] 31. Specific implementation of user behavior matching with sequence requirements

[0121] (1) Configuration of event chain.

[0122] a) According to business requirements, one or more user actions are converted into a judgment expression of a single event, that is, to determine whether a given operation is satisfied.

[0123] b) Each event consists of a conditional expression, and the calculation result returns 1 (true) or 0 (false).

[0124] c) An event chain consists of a single event or up to 32 events.

[0125] (2) Configuration of the pattern string pattern.

[0126] a) For user behaviors that require sequential order, such as when a user performs event A followed by event B, these behaviors can be represented as (?1)(?2). (?N) represents the index of the event conditional expression. (?N) in the pattern string must correspond one-to-one to each event in the event chain.

[0127] b) When user behavior involves other actions in the match, for example, the user sequentially performs event A, then performs some unrelated actions, and then performs event B, it can be written as (?1).*(?2).

[0128] c) User behaviors are repeated in the matching. For example, if a user experiences event A twice, followed by event B, the above behaviors can be represented as (?1)(?2)(?3), where (?1) and (?2) correspond to event A, and (?3) corresponds to event B.

[0129] d) If there is a time requirement between events, for example, if event B occurs at least 1800 seconds after event A, it can be written as (?1)(?t>1800)(?2); similarly, if event B occurs within 500 seconds after event A, it can be written as (?1)(?t<=500)(?2); for example, if event B occurs immediately 60 seconds after event A, it can be written as (?1)(?t==60)(?2).

[0130] (3) Extract the user ID (user_id), operation ID (operation_id), and operation time (operation_time) fields from the original access log table, and perform a GROUP BY aggregation operation based on the user ID (user_id).

[0131] (4) Use the sequenceMatch function to process user behavior matching with sequential requirements and check whether there is an event chain that meets the input pattern. The input of the sequenceMatch function is the pattern string pattern, the behavior time (operation_time), and one or more specific condition judgment expressions in the user behavior event chain. The function returns the user behavior matching result aggregated based on the user ID (user_id) as the user behavior matching result (user_operation_match) field.

[0132] (5) Use the sequenceCount function to process user behavior matching with sequential requirements and count the number of event chains that meet the input pattern. The sequenceCount function takes as input the pattern string pattern, the behavior time (operation_time), and one or more conditional judgment expressions in the user behavior event chain. The function returns a statistical value aggregated based on the user ID (user_id) as the user behavior statistics result (user_operation_count) field.

[0133] (6) Combine the results of the user behavior matching result (user_operation_match) field and the user behavior statistics result (user_operation_count) field to filter out user data that meets the requirements.

[0134] (7) Save the above data set as a user behavior matching table for subsequent business queries and calculations.

[0135] 32. Implementation of user behavior matching without order requirements

[0136] (1) Extract the user ID (user_id), operation ID (operation_id), operation time (operation_time) and other fields from the original access log table to form the initial user behavior data set.

[0137] (2) Based on business needs, query the initial user behavior dataset for each user event that needs to be matched. An event can be one or more user behaviors. Generate a unique event index (event_index) field in each subquery dataset. For example, the index value of the first subquery is 1, the index value of the second subquery is 2, and so on. Among them, user data with the same user event that needs to be matched are merged together to form a subquery dataset.

[0138] (3) Multiple events are connected through UNION ALL to form an event set.

[0139] (4) Perform a GROUP BY aggregation operation on the data set in the previous step according to the user ID (user_id).

[0140] (5) Use HAVING to filter the aggregated data, compare the event index de-aggregation array with the matching conditions, filter out the event index de-aggregation array that meets the matching conditions, and generate a user behavior matching data table. Specifically:

[0141] Assume that the generated dataset is as shown in Table 6:

[0142] Table 6 Dataset containing event index

[0143] User ID Event index (rule_index) user1 1 user1 2 user1 1 user1 3 user2 1 user2 1 user2 2 user3 3

[0144] User IDs are aggregated, and event indexes are aggregated and deduplicated using the groupUniqArray function to form an event index deduplication aggregation array for subsequent matching with the hasAll function, as shown in Table 7.

[0145] Table 7 Event index deduplication aggregation array

[0146] User ID Event index deduplication aggregation array If duplicate removal is not performed after aggregation, subsequent matching will be inconvenient. user1 [1,2,3] [1,2,1,3] user2 [1,2] [1,1,2] user3 [3] [3]

[0147] Use the hasAll function to check if one array is a subset of another array. It returns 1 if it matches and 0 if it doesn't match.

[0148] Here, use the statement:

[0149] HAVING hasAll(groupUniqArray(rule_index),[1,2,3])=1

[0150] That is, the event index de-aggregated array is compared with [1,2,3] (matching condition, indicating that event indexes 1, 2, and 3 are satisfied at the same time), as shown in Table 8.

[0151] Table 8 Comparison results of event index deduplication aggregation array and matching conditions

[0152] User ID Event index deduplication aggregation array hasAll calculation results user1 [1,2,3] 1 user2 [1,2] 0 user3 [3] 0

[0153] Indicates that you want to filter out the user ID that meets event indexes 1, 2, and 3, that is, user1.

[0154] (6) Save the above data set as a user behavior matching data table for subsequent business queries and calculations.

[0155] 4. Funnel analysis module

[0156] User funnel analysis, also known as conversion analysis, is an algorithm that reflects the user conversion rate at each stage of the user behavior state from the starting point to the end point. This module is based on a given sliding window (sliding_time_window) parameter, matches the event chain in sequence, calculates the number of conversion steps within the window event and the number of conversions in each stage through ClickHouse SQL, and generates a user funnel conversion analysis table. Figure 5 As shown, the specific process is:

[0157] (1) Extract the data sets of user ID (user_id), operation ID (operation_id), operation time (operation_time) and other fields from the original access log table, and sort them in ascending order according to user ID (user_id) and operation time (operation_time).

[0158] (2) Perform a GROUP BY aggregation operation on the above data set based on the user ID (user_id) field.

[0159] (3) Use the windowFunnel function to calculate the maximum number of consecutive trigger condition chains for each user within a given sliding time window (sliding_time_window) and generate the user's longest trigger chain count (max_stage_active) field. Specifically:

[0160] a) The windowFunnel function searches for a chain of events in a sliding time window and calculates the maximum number of events that occurred from the chain.

[0161] b) This function searches for the first condition in the trigger chain and sets the event counter to 1. This is the moment the sliding window starts. If the events from the chain occur sequentially within the window, the counter increments. If the event sequence is interrupted, the counter no longer increments. If the data has multiple event chains at different completion points, the function outputs the size of the longest chain.

[0162] c) The first parameter of the function is the size of the sliding window, which represents the maximum interval between the first and last events in the event chain.

[0163] d) The parameters following this function include the time field of the dataset and the event chain's judgment expression. An expression takes a set of conditions as parameters and indicates whether an event meets certain conditions. Each event is composed of a conditional expression, and the result of the evaluation is 1 (true) or 0 (false).

[0164] e) The windowFunnel function returns the longest trigger chain count (max_stage_active) field generated for each user.

[0165] (4) Generate the number of users at each stage of the funnel, specifically:

[0166] a) Use the arrayWithConstant function to process the user's longest trigger chain count (max_stage_active) field. This function generates an array of the specified length for subsequent operations. (For example, if the length is set to 3 and the value is 1, the generated array will be: [1,1,1])

[0167] b) The function takes the user's longest trigger chain count (max_stage_active) field as its first argument and the value 1 as its second argument. The function returns an array with a length equal to the user's longest trigger chain count and an element value of 1.

[0168] c) Use the arrayEnumerate function to process the arrayWithConstant function to generate an array. The arrayEnumerate function returns the array index. For example, after processing the array [1,1,1], the new array [1,2,3] is returned.

[0169] d) Use the arrayJoin function to convert the array returned by the arrayEnumerate function into multiple columns (rows to columns). The expanded fields are the funnel's stage index value (stage_index) field.

[0170] e) Perform a GROUP BY aggregation operation on the above dataset based on the stage index value (stage_index) field.

[0171] f) At this point, the total number of users in each stage is calculated by aggregating the stage index values ​​and using the count function.

[0172] (5) Sorting by stage index value (stage_index) can output a data set containing the stage index value and the total number of users in that stage. At this point, the calculation process of the funnel stage index and the total number of users in each stage of the funnel is completed.

[0173] (6) Save the above data set as a user funnel conversion analysis table for subsequent business queries and calculations.

[0174] Through the above technical solution, the present invention analyzes user behavior based on the native multi-parameter aggregation functions and high-order functions in the ClickHouse distributed database. ClickHouse, as an OLAP distributed database with outstanding performance, has obvious advantages in real-time calculation of massive data. It is very suitable for use as the underlying data warehouse of analysis software. Data memory is not limited, and there is no need to write a series of array processing functions and aggregation functions to make up for the deficiencies of the basic algorithm functions of big data. The development process is relatively simple, which solves the problems of existing technologies being limited by the physical memory of a single machine, complex development, and difficulty in achieving real-time response.

[0175] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the various embodiments of the present invention.

Claims

1. A user behavior analysis system, characterized in that: User behavior analysis based on native multi-parameter aggregation functions and high-order functions in ClickHouse distributed database, including user behavior session module, user retention analysis module, user behavior matching module and user funnel analysis module; The user behavior session module is used to extract the user identification field, the behavior identification field, and the behavior time field from the original access log table, generate a session id, a session start timestamp field, a session end timestamp field, and a user behavior chain field, and output them after sorting according to the user identification field and the session id field to form an ordered user behavior session data set, which is saved as a user behavior session data table; The user retention analysis module is used to calculate the retention rate on the first day and within a preset period based on given date parameters, and generate a user retention rate daily table; The user behavior matching module is used to screen out user data sets that meet the corresponding patterns based on given behavior pattern parameters with order requirements or behavior pattern parameters without order requirements, and generate a user behavior matching data table; The user funnel analysis module is used to match the event chain in sequence based on a given sliding window, calculate the number of conversion steps within the window event and the number of conversions in each stage, and generate a user funnel conversion analysis table; The user funnel analysis module is also used to: extract the user identification field, behavior identification field, and behavior time field from the original access log table to form a data set; The data set sorts the behavior identification field of each user in the user identification field in ascending order according to the value of the behavior time field; Use the windowFunnel function to calculate the maximum number of consecutive trigger condition chains for each user within a given sliding time window, and generate the user's longest trigger chain count field; Use the arrayWithConstant function to process the user's longest trigger chain count field, generate an array of a specified length, and convert the rows of the array of the specified length into columns to generate the phase index value field; Perform a GROUP BY aggregation operation on the above dataset according to the stage index value field, and use the count function to calculate the total number of users in each stage. Then sort according to the stage index value field, output a new dataset containing the stage index value and the total number of users in the stage, and save the new dataset as a user funnel conversion analysis table.

2. A user behavior analysis system according to claim 1, characterized in that: The user behavior session module extracts the user identification field, the behavior identification field, and the behavior time field from the original access log table to generate a session ID, including: Extract the user identification field, behavior identification field, and behavior time field from the original access log table, convert the behavior time field into a timestamp format, and sort the extracted data set from small to large according to the value of the behavior time field; The data set is GROUP BY aggregated according to the user ID field, and the groupArray function is used to aggregate the corresponding behavior ID field and behavior time field into a behavior ID array field and a behavior timestamp array field respectively; Use the arrayDifference function to calculate the difference between adjacent elements in the behavior timestamp array field to generate the user behavior interval array field; Use the arrayMap function to compare the behavior interval in the behavior interval array field with the preset session time threshold. If the behavior interval is less than or equal to the preset session time threshold, it indicates that it is a behavior record of the same user session. If it is greater than the session time threshold, it is considered to be two sessions. The returned result array is the new session ID array field. When the element value is 1, it indicates a new session. When the value is 0, it indicates an old session. The first element of the new session ID array field is set to 1. Use ARRAY JOIN to perform row-to-column expansion operations on the behavior ID array field, behavior timestamp array field, and new session ID array field. Each user's single-row record is expanded into multiple rows according to the number of elements in the array. After expansion, the user ID field, behavior ID field, behavior timestamp field, and new session ID field are generated. Use the arrayEnumerate function to return the array index for the new session ID field, use the arrayJoin function to convert the result rows processed by the arrayEnumerate function to columns, generate the session index session_index, and then use the arraySlice(is_new_session_array, 1, session_index) function to slice each row of data. The three input parameters of arraySlice are the new session ID field, the offset of the slice, and the length of the slice. Then use the arraySum function to sum the elements in the above sliced ​​array, and the value is regarded as the session ID.

3. A user behavior analysis system according to claim 2, characterized in that: The user behavior session module generates a session start timestamp field, a session end timestamp field, and a user behavior chain field, including: Perform a left join operation on the behavior identification field and the behavior name field of the behavior information dimension table to convert the behavior identification field into the behavior name field; Sort the data set according to the user ID field and the behavior timestamp field, and then perform a GROUP BY aggregation operation on the sorted data set according to the user ID field and the session ID field; After aggregation, the minimum behavior timestamp in each session is used as the start timestamp field of the session, and the maximum behavior timestamp is used as the end timestamp field of the session. The behavior name field in the start timestamp field and the end timestamp field is processed into a behavior name array through the groupArray function, and then the elements in the array are connected through the arrayStringConcat function to generate the user behavior chain field.

4. A user behavior analysis system according to claim 1, characterized in that: The construction process of the original access log table is as follows: the log data of the system where the analysis object is located is stored in the ClickHouse database, and then the original access log table is generated. The original access log table contains at least a user identification field, a behavior identification field, and a behavior time field.

5. A user behavior analysis system according to claim 1, characterized in that: The user retention analysis module is also used for: Extract the user ID field and behavior time field from the original access log table, and use the toDate function on the behavior time field to generate the user visit date field visit_day; Based on the given date parameter first_day, the INTERVAL operator of ClickHouse is used to generate date data of the 1st day, the 2nd day, ..., until the Nth day for subsequent calculations, where the 0th day is first_day; Use the equals function to set equals(visit_day, first_day) as the first judgment condition parameter of the retention function. Similarly, the Nth judgment condition parameter is equals(visit_day, first_day +INTERVAL N DAY). By default, the result of the first judgment condition parameter is retained, and the results of all calculated judgment condition parameters are saved in the result array of the retention function. Perform GROUP BY aggregation on the result array of the retention function through the user ID field, and count the user access status from day 0 to day N respectively; The retention rate on day N is calculated by dividing the number of visits on day N by the number of visits on day 0; the retention rate from day 0 to day N is calculated and saved as a daily user retention rate table.

6. A user behavior analysis system according to claim 1, characterized in that: The user behavior matching module is further used to process user behavior matching with sequence requirements, and the user behavior matching with sequence requirements includes: Configure user behavior event chain; Configure the pattern string corresponding to the user's behavior; Extract the user ID field, behavior ID field, and behavior time field from the original access log table, and perform GROUP BY aggregation operations based on the user ID field; After performing the GROUP BY aggregation operation on each user based on the user ID field, the behavior ID field is sorted in the order of the behavior time field, and the sorted result is converted into a pattern string. The pattern string is input into the sequenceMatch function to determine whether there is a user behavior event chain that meets the input pattern. The user behavior matching result field is returned, and the number of user behavior event chains that meet the input pattern is counted to generate the user behavior statistics result field. The user behavior matching data table is generated by combining the results of the user behavior matching result field and the user behavior statistics result field.

7. A user behavior analysis system according to claim 6, characterized in that: The configuration of the user behavior event chain includes: converting one or more user actions into a judgment expression of a single event, and when the user triggers an event, judging whether the event satisfies the judgment expression. If so, the user triggers the event in the user behavior event chain.

8. A user behavior analysis system according to claim 7, characterized in that: The pattern string corresponding to the configuration user's behavior includes: For example, if a user has event A followed by event B, the above behaviors are represented as (?1)(?2), where (?N) represents the index of the judgment expression of the user behavior event chain, and (?N) corresponds to the events of the user behavior event chain one by one; The user has event A, then some unimportant actions, and then event B, written as (?1).*(?2); The user experienced event A twice, and then event B occurred. The above behavior is represented as (?1)(?2)(?3), where (?1) and (?2) correspond to event A, and (?3) corresponds to event B. When there are time requirements between events, if event B occurs at least m seconds after event A, it is written as (?1)(?t>m)(?2); if event B occurs within L seconds after event A, it is written as (?1)(?t<=L)(?2); if event B occurs immediately after Q seconds after event A, it is written as (?1)(?t==Q)(?2).

9. A user behavior analysis system according to claim 1, characterized in that: The user behavior matching module is further used to process user behavior matching without sequence requirements, and the user behavior matching without sequence requirements includes: Extract the user identification field, behavior identification field, and behavior time field from the original access log table to form an initial user behavior data set; Query the initial user behavior data set to check whether each user event that needs to be matched has occurred. Merge the user data with the same user event that needs to be matched together to form a sub-query data set. Each sub-query data set has a unique event index field. Connect each subquery data set through UNION ALL to form an event set; Perform GROUP BY aggregation operation on the event set according to user ID to generate a new data set; The event indexes in the new data set are aggregated and deduplicated using the groupUniqArray function to form an event index deduplicated aggregate array. Use the hasAll function to compare the event index de-duplication aggregation array with the matching condition, filter out the event index de-duplication aggregation array that meets the matching condition, and generate a user behavior matching data table.

Images