ClickHouse-based integral user behavior analysis method

Abstract

Through analysis of user behavior data based on Clickhome, an enterprise can be helped to understand user preferences, demands and behavior characteristics, so that a marketing strategy is better formulated, a product is optimized, and user experience is improved: firstly, the enterprise can obtain behavior habits and preferences of the user in a point exchange or marketing activity; data-driven user behavior analysis is particularly important, product iteration is promoted, customized services are provided, and product decision making is driven; secondly, the attribute of a user group is known from the dimensions of user general situation, user grouping, user insight and the like, portraying is performed on the user through data behavior analysis of the user, refined operation is realized, and personalized marketing is developed; and finally, the enterprise can more accurately measure the use and feedback of the credits by the user through real-time data analysis, thereby better managing and optimizing the credits system, helping the enterprise find and solve the problems encountered when the user obtains and uses the credits, making more effective marketing strategies and credits plans, and improving the marketing efficiency. And the satisfaction degree and loyalty of the point users are improved.

Description

Technical Field

[0001] The invention belongs to the field of data analysis technology, and in particular relates to an integrated user path analysis method based on ClickHouse. Background Art

[0002] Customer points are a means of corporate customer relationship management and an operational method to maintain new and old customers. Many companies, banks, e-commerce platforms and other platforms have launched point businesses. By introducing points, companies can motivate users to use the platform, thereby improving the platform's user activity and user stickiness. Then, considering the full-link user analysis based on "attracting new customers-repeat purchases-value enhancement" and the company's analysis of user searches, browsing, purchases and other behaviors, the platform needs to pay attention to effective user conversions, user habits, user preferences, and explore the meaning and rules behind user behavior data, thereby helping the platform to refine the points user operation and improve user satisfaction.

[0003] As the points platform grows in size, user data also increases. Even after reasonable index settings, the data tables originally stored in MySQL still cannot obtain the aggregation of various values ​​in real time, so the data can only be stored in separate tables every day. In order to improve the speed, we calculate the aggregated results in advance. For example, if we want to count the purchase volume of a certain product in a certain province at a certain time, we also set the statistical caliber in advance and calculate the value. This solution, to a certain extent, exposes great drawbacks: 1. Storing data tables every day leads to too many data tables; 2. Because the values ​​are calculated in advance, you can only view the values ​​under the pre-defined statistical caliber, and cannot customize the caliber in real time. For example, if you want to count the points redemption volume of certain products in certain provinces, you cannot support data acquisition because the statistical caliber and values ​​are not set in advance; 3. If you want to check the number of clicks / purchases of a certain product, you need to store a separate data table. You cannot directly obtain it from the history table through SQL. Otherwise, the speed delay cannot meet the demand. We encountered technical bottlenecks in the selection of the basic database MySQL. The product availability was extremely low, the query stability and success rate were not very high, and the tracking point management and reporting format were not fully standardized. In order to solve the above problems, we urgently need an analytical database that can achieve real-time computing performance on billions of data. Clickhouse is designed for processing large-scale data. It can efficiently process and analyze a large amount of user behavior data. It uses columnar storage and query, and has good scalability and fault tolerance, which is very important for the analysis of integrated user behavior. Summary of the invention

[0004] Development of buried points Based on the user operation path, which functions are used, the logical relationship between each module and the data flow, a tracking point solution is designed, statistical events and statistical parameters are added to the data source, and the raw data required for user behavior analysis is obtained. In order to obtain the most complete data, a full tracking point method is usually adopted.

[0005] Data collection Data collection usually adopts visual tracking (i.e. full tracking), SDK tracking, JS tracking, log data, historical data import, etc. In order to obtain the most accurate data, full tracking is usually used to obtain comprehensive, prepared, and real-time data. Data cleaning Clean and process the acquired raw data, remove abnormal data, and perform necessary transformation and normalization on the data for subsequent analysis Data storage Connect user data sources and establish a unified data warehouse. Clean and unify user attribute data and user behavior data. User behavior-related data must meet the 4W1H description, that is, who did what, when, how, where, and what; user attribute data: records of user ID, device number, access IP, time, duration, clicks, and other data.

[0006] Data Grouping Using Clickhouse's high-order array functions, the user's behavior data is organized into user ID, event time, event name, page name, action record, detailed description, and sorted according to certain rules. Analytical Model (1) Page click analysis: Track users’ click behavior on the page to understand which content on the page users are more interested in and which content needs to be discarded or further optimized. (2) User behavior path analysis: By analyzing the user's behavior path in products and websites, such as how users reach the product details page through browsing or searching, how they ultimately make a purchase, and the user's habitual purchasing path (3) Funnel model analysis: By analyzing the user behavior conversion rate at key links, we can identify problems that point users encounter during the purchase process and further optimize and improve business conversion rates. (4) User portrait analysis: By analyzing the user's attributes, behaviors, needs and other characteristics, users are divided into different groups to better meet the needs of different users. Report Output Output the analysis results in a visual form, such as tables, charts, etc., so that business personnel can conduct further analysis and insights Specific examples of the present invention are as follows: China Telecom Points Mall uses Clickhouse's user behavior analysis method and combines it with big data analysis to form a unique portrait group of China Telecom points users, and then conducts differentiated operations for points users, ultimately achieving the effect of thousands of faces for thousands of people: Visualization of user behavior paths: China Telecom Points Mall has a large number of users browsing, searching, placing orders, and paying in the Points Mall every day. Through visualization tools such as Sankey diagrams, heat maps, and user path maps, the company can more intuitively understand the user's behavior preferences and usage habits in the Points Mall, thereby guiding the improvement and optimization of the Points Mall. User behavior preference analysis: It can analyze what types of goods, activities, and habitual purchasing paths users prefer, so as to launch products and services that meet user needs. BRIEF DESCRIPTION OF THE DRAWINGS The attached figure is a flow chart of the integrated user behavior analysis based on ClickHouse.

Claims

1. Use ClickHouse's high-performance computing capabilities and high-order array functions, such as the RoaringBitMap function, to calculate the intersection and difference of the groups of behaviors involved in the calculation, and perform real-time tracking effect analysis on large-scale user behavior data.

2. Use visualization tools such as Sankey diagrams to display the changes in user traffic flow on a certain page or module in the points mall, and realize visual analysis of user behavior paths.

3. Screen and analyze conditions such as the end point, passing points and maximum time interval of the user behavior path to obtain key event data that meets the conditions.

4. Implement behavior analysis platform and label profiling platform based on ClickHouse's RBM (RoaringBitMap), such as event analysis label crowd selection, pre-calculated path analysis, and user grouping for user behavior creation.

5. Full model aggregation: The hive traffic model structure of full information can basically retain all information except the degradation of the time dimension. The segmented behavior chain data is aggregated and deduplicated through the arrayJoin and arrayCompact functions to achieve the integration and statistics of user behavior path data.

Images