An OLAP database-based associated attribute funnel analysis method and system

Abstract

The application discloses a kind of based on OLAP database's associated attribute funnel analysis method and method, specific steps include: early preparation and environment building include development environment building and function development planning;In OLAP framework source layer, windowFunnelWithAttribute aggregation function development and integration are carried out: including: interface and logic construction, and function registration and deployment, wherein, interface and logic construction are based on ClickHouse original windowFunnel time window and hierarchical flow logic, and associated attribute check is integrated, and the two-way verification mechanism of "forward traversal preliminary screening" plus "reverse backtracking check" is used.The application integrates associated attribute check logic in aggregation function, realizes the three conditions check of "time window", "step sequence" and "attribute matching", and can reduce data transmission and calculation redundancy, flexibly configure the dimension to be matched, and ensure the accuracy of funnel conversion.

Description

Technical Field

[0001] This invention relates to the field of data processing and data analysis technology, specifically, to a method and system for funnel analysis of related attributes based on OLAP databases. Background Technology

[0002] Existing funnel analysis techniques are mainly implemented through the following methods:

[0003] Conventional methods: Utilize SQL window functions such as ROW_NUMBER, or distributed computing frameworks such as Flink and Spark, to filter events that match the step sequence based on the user's unique identifier distinct_id and timestamp, and count the number of users and conversion rate for each step;

[0004] Existing functions in OLAP databases, such as ClickHouse's windowFunnel function, can track user behavior sequences within a time window, but only support dual validation of "time window + step sequence," and cannot associate attribute dimensions such as the same product ID or course title.

[0005] Therefore, existing funnel analysis techniques, in order to accurately match the correlation attributes between multiple steps, need to be implemented through the business layer, which is inefficient. Technical reasons:

[0006] Existing funnel analysis tools, including ClickHouse's windowFunnel, do not integrate associated attribute validation logic at the underlying level. Attribute matching relies on external queries such as multi-table JOINs, resulting in high computational complexity. Furthermore, the lack of a binding mechanism between "steps" and "attributes" makes it impossible to synchronously validate attribute consistency during behavior sequence tracing. Instead, it can only supplement the validation through post-event association, further reducing efficiency. Summary of the Invention

[0007] The purpose of this invention is to provide a method and system for funnel analysis of association attributes based on OLAP databases, which solves the problem that existing funnel analysis based on OLAP databases cannot efficiently and accurately match multi-step association attributes.

[0008] The present invention solves the above problems through the following technical solution:

[0009] A method for funnel analysis of association attributes based on OLAP databases, comprising the following steps:

[0010] S100. Preliminary preparations and environment setup, including development environment setup and function development planning;

[0011] The development environment setup includes: pulling the appropriate version of the source code from the OLAP database open-source code repository, configuring a compatible compiler and basic dependency libraries, and setting up a local development branch.

[0012] The function development plan includes: analyzing the complete conversion path of user operations and locating user drop-off points; excluding invalid conversions across content and only counting valid paths within the same content ID; and clarifying the development direction of aggregate functions to verify the consistency of user behavior steps and associated attributes, and to count conversion paths within the same content ID.

[0013] S200. Development and integration of the windowFunnelWithAttribute aggregation function in the OLAP framework source code layer: including interface and logic construction, and function registration and deployment;

[0014] The interface and logic construction includes: developing a funnel core calculation module. This funnel core calculation module is based on the original windowFunnel time window and hierarchical flow logic of ClickHouse, incorporates associated attribute verification, and adopts a two-way verification mechanism of "forward traversal initial screening" plus "reverse backtracking verification".

[0015] Function registration and deployment include: registering the `windowFunnelWithAttribute` aggregate function according to ClickHouse's custom function specification, configuring parameters and return values; compiling the program containing the `windowFunnelWithAttribute` aggregate function and deploying it to the big data platform's data analysis environment to connect user behavior data with the analysis process.

[0016] As a further improvement, the two-way verification mechanism includes:

[0017] The initial screening is performed by forward traversal, based on the time window and the step order. The level_candidates container is used to store candidate events hierarchically according to the event level, i.e. the step order, and the level_matches container is used to dynamically track the latest valid events at each level.

[0018] Reverse backtracking verification is performed on the candidate events after forward filtering based on the BacktrackState stack structure, and the matching of related attributes is verified in reverse from the highest level.

[0019] As a further improvement, the forward traversal initial screening method specifically includes the following steps:

[0020] Initial checks and special case handling: If the input data is empty, return an empty result directly; if strict order is not required and the total number of events is 1, return the ID and level 1 of the first event;

[0021] Data preparation and sorting: Sort the event list and initialize two core containers: the level_candidates container stores candidate events by event level, and each candidate event includes start time, end time, index, and associated attributes; the level_matches container stores the best currently matched event by level, used to track valid events at each level.

[0022] Event traversal and processing: Traverse all events and classify them according to their event IDs. Initial event processing: Add the event directly to level_candidates[0] and update level_matches[0] to the latest event, marking "first event found"; Regular event processing: When there is a matching event in the previous level, check whether the current event meets the time window constraint; If the condition is met, add the event to the candidate list of the current level and update level_matches to the latest event; If the highest level has been reached, update the maximum level to the total number of events.

[0023] Determine the maximum valid level: If the maximum level is still 0 after the traversal is completed, check level_matches from the back and take the first level with a match as the maximum level.

[0024] As a further improvement, the reverse backtracking verification method specifically includes the following steps:

[0025] Initialize backtracking stack: Create a stack to store backtracking states. Initially, push a state onto the stack: starting from the highest valid level, the initial level is the same as the current level, and the list of matching IDs and attribute tracking is empty;

[0026] Stack loop processing: When the stack is not empty, continuously take out the top state of the stack for processing; Termination condition judgment: If the current level is less than 0, all levels have been backtracked, and the number of matched IDs and the specific IDs are returned; Empty candidate processing: If there are no candidate events at the current level, i.e., level_candidates is empty, decrement the current level by 1, push the new state to the stack and continue backtracking.

[0027] Candidate event verification: Perform reverse traversal, attribute constraints, and time window constraints on candidate events at the current level;

[0028] Update status and advance backtracking: After finding a candidate that meets the criteria, create a new status and add the candidate's ID and time to the matching list;

[0029] If no valid candidate is found, backtracking adjustment is performed: if there is no valid candidate at the current level, the state is adjusted according to the number of candidates at the initial level; the current matching ID and attribute information are cleared, the new state is pushed to the stack, and backtracking is retried.

[0030] As a further improvement, in the candidate event verification, the reverse traversal is as follows: check from back to front to verify whether each candidate meets the conditions; the attribute constraint is: if the level requires attribute matching, check whether the candidate attribute is consistent with the existing attribute and whether the attribute is not reused; the time window constraint is: if there is already a matching ID, check whether the end time of the current candidate and the time of the previous match are within the valid window.

[0031] As a further improvement, the update state and backtracking process also includes: if attribute tracking is required, updating the attribute mapping and the set of used attributes; decrementing the current level by 1, pushing the new state onto the stack, continuing to backtrack to the previous level, and marking it as "a valid candidate has been found".

[0032] As a further improvement, the development and integration of the windowFunnelWithAttribute aggregation function in the OLAP framework source code layer also includes:

[0033] The stack structure and candidate set are designed with performance optimization to reduce memory usage and improve query speed by making the core data structures, namely stack and candidate set, lightweight and hierarchical.

[0034] The flexible design of dynamic attribute configuration supports the dynamic configuration of associated attributes and matching steps, adapting to the conversion path analysis of different business scenarios.

[0035] The underlying integrated attribute validation logic is embedded in the underlying computing chain of the OLAP database, reducing data transmission and redundant calculations.

[0036] As a further improvement, the funnel analysis method further includes the following steps:

[0037] S300, Data Application: including data acquisition and storage, as well as function configuration and invocation;

[0038] Data collection and storage includes collecting user behavior data and storing it in an OLAP database. The behavior data includes timestamps, user identifiers, event types, and related attribute fields.

[0039] Function configuration and invocation include calling the windowFunnelWithAttribute aggregate function via SQL, passing in the time window, event sequence, associated attribute fields, and step parameters of the attributes to be matched, performing funnel analysis, and outputting the results.

[0040] Furthermore, the present invention also solves the above problems through the following technical solutions:

[0041] A system for analyzing association attribute funnels based on an OLAP database, used to implement the association attribute funnel analysis method based on an OLAP database as described above, includes the following modules:

[0042] The data storage module stores user behavior events, including timestamps, unique user identifiers, event types, and associated attributes. The associated attribute fields of the events are forcibly bound to the event fields for storage, providing a data foundation for underlying attribute validation.

[0043] The Function Compute module integrates the windowFunnelWithAttribute aggregate function. It performs forward traversal screening through the level_candidates and level_matches dual containers to store candidate events that meet the time window by level. It also performs reverse backtracking verification through the BacktrackState stack structure to verify the consistency of associated attributes from the highest level. The Function Compute module is developed based on the aggregate function interface of the OLAP framework.

[0044] The interface module provides an SQL call interface, allowing users to configure the steps to be matched and customize associated attribute fields through the match_events parameter. The interface syntax conforms to the SQL standard of the OLAP framework.

[0045] As a further improvement, when the OLAP framework is ClickHouse, the Function Compute module is embedded in the query execution chain of the MergeTree engine and called through the AggregationNode node; when the OLAP framework is Doris, the Function Compute module is embedded in the data processing chain of BrokerLoad and called through the AggregationOperator operator.

[0046] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0047] This invention integrates attribute validation at the underlying level and embeds related attribute matching logic in the ClickHouse source code layer. This eliminates the need for additional joins or subqueries in the upper layer, avoids external joins, reduces query time in scenarios with hundreds of millions of data points, reduces data transmission and computational redundancy, and is highly efficient.

[0048] By using dynamic attribute binding, users can dynamically specify associated attributes and flexibly configure the dimensions to be matched, adapting to the analysis needs of multiple scenarios such as e-commerce and education, thus improving flexibility.

[0049] By using time windows and attribute co-validation, and based on time window constraints, the consistency of related attributes in each step is forcibly verified, eliminating invalid cross-attribute conversions such as behaviors of different courses, thereby improving the accuracy of funnel analysis results by 20% and enhancing precision. Attached Figure Description

[0050] Figure 1 This is a framework diagram of a correlation attribute funnel analysis system based on an OLAP database according to the present invention;

[0051] Figure 2 This is a flowchart of the function processing of the present invention;

[0052] Figure 3 This is a detailed flowchart of the forward traversal initial screening process of the present invention;

[0053] Figure 4 This is a detailed flowchart of the reverse backtracking verification process of the present invention;

[0054] Figure 5 This is a performance comparison chart between existing technical methods and the funnel analysis method of the present invention;

[0055] Figure 6 This is a diagram illustrating the specific process of calling an SQL statement. Detailed Implementation

[0056] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0057] Example:

[0058] See attached document Figure 1-5 A funnel analysis method for association attributes based on OLAP databases, the specific steps of which include:

[0059] S100. Preliminary preparations and environment setup, including development environment setup and function development planning;

[0060] S101. Development environment setup;

[0061] Pull the compatible version of the source code from the ClickHouse open-source code repository for the OLAP database, configure a compatible compiler such as GCC to meet ClickHouse compilation requirements, ensure that basic dependency libraries such as Boost and LLVM run normally, and set up a local development branch for subsequent development of the windowFunnelWithAttribute function to prepare the underlying layer for funnel transformation analysis.

[0062] S102, Function Development Planning;

[0063] Business Scenario: The smart TV platform needs to analyze the complete conversion path of a user from "powering on" to "content playback" to pinpoint user churn points. Since users may switch content during the process, such as jumping from "Recommended Content A" to the details page of "Content B," invalid conversions across content categories need to be excluded, and only valid paths with the same content_id should be counted. Clear Function Development Direction: The function should validate the consistency of user behavior steps and associated attributes, accurately counting conversion paths within the same content ID (content_id).

[0064] S200. Develop and integrate the windowFunnelWithAttribute aggregation function at the source code level of the OLAP framework, including: interface and logic construction, and function registration and deployment;

[0065] (i) Interface and logic construction, including: developing the funnel core calculation module. This funnel core calculation module is based on the original windowFunnel time window and hierarchical flow logic of ClickHouse, incorporates associated attribute verification, and adopts a two-way verification mechanism of "forward traversal screening" plus "reverse backtracking verification".

[0066] Core functional module development:

[0067] Overall architecture as follows Figure 1 As shown, it comprises three modules: a data storage layer, a function computation layer, and an interface layer. The data storage layer uses ClickHouse to store user behavior data containing associated attribute fields; the core of the function computation layer is the windowFunnelWithAttribute function, which integrates three logics: "time window verification, step sequence verification, and associated attribute matching"; the interface layer provides an SQL call entry point and supports users dynamically passing in parameters.

[0068] The development of the funnel core computing module is based on the above architecture, implementing a two-way verification mechanism:

[0069] The initial screening process, which involves forward traversal, is detailed as follows: Figure 3As shown. Based on ClickHouse's original windowFunnel time window and hierarchical flow logic, preliminary screening is incorporated. The steps include: initial checks and handling of special cases; data preparation and sorting; initialization of two core containers, level_candidates (storing candidate events by level) and level_matches (tracking the latest valid events at each level); traversing events and performing hierarchical and time window constraint checks; and finally determining the initial maximum valid level.

[0070] Reverse backtracking verification: The detailed process is as follows Figure 4 As shown, the BacktrackState stack structure is used to perform attribute consistency checks on candidate events after forward filtering. The steps include: initializing the backtrack stack; processing the top state of the stack in a loop; performing reverse verification on the candidate events at the current level; updating the state and advancing or adjusting the backtrack.

[0071] Specifically, this invention employs a bidirectional collaborative logic of "forward traversal initial screening + reverse backtracking verification," achieving efficient and accurate conversion path analysis through hierarchical screening and attribute binding. Its complete processing flow is as follows: Figure 2 The above describes the collaborative logic of event sequence tracking and attribute matching, namely "input parsing → forward traversal → reverse backtracking → result output". The key innovation lies in the collaboration between forward traversal and reverse backtracking—forward traversal quickly narrows down the candidate range, while reverse backtracking accurately verifies attributes, forming a closed loop of "efficiency" and "accuracy".

[0072] 2.1.1 Two-way verification mechanism: a collaborative design of forward initial screening and reverse attribute verification;

[0073] To balance the efficiency and accuracy of funnel analysis, this invention innovatively adopts a two-way collaborative logic of "forward traversal initial screening + reverse backtracking verification", which achieves efficient and accurate conversion path analysis through hierarchical screening and attribute binding.

[0074] Forward traversal hierarchical filtering design: Forward traversal uses two containers, `level_candidates` and `level_matches`, to achieve initial filtering of events. The core is to quickly narrow down the range of candidate events that meet the time and step constraints.

[0075] a) `level_candidates` stores candidate events hierarchically according to event level, such as steps 1-4 in a funnel. Each candidate event includes "start time, end time, event index, and associated attributes," such as product ID and course title. This design avoids the full data traversal caused by traditional single-list storage by using hierarchical division, retaining only events that meet the time window constraints, such as step 2 time ≤ step 1 time + 24 hours. The amount of candidate data can be reduced by more than 30%.

[0076] b. `level_matches` dynamically tracks the "latest valid events" at each level and sorts them based on timestamps to ensure that candidate events with higher timeliness are checked first during subsequent reverse backtracking, reducing invalid check operations.

[0077] Precise attribute verification through reverse backtracking: Based on the `BacktrackState` stack structure, attribute consistency verification is performed on candidate events after forward filtering. The core is to verify the matching of related attributes from the highest level in reverse.

[0078] a. The stack structure stores intermediate states such as "current level, list of matched event IDs, set of associated attributes, and time window boundaries". Starting from the highest completed level of the user behavior sequence, such as step 4, it traverses backwards and checks whether the associated attributes of each candidate event at each level are consistent with the previous level, such as product ID in step 3 = product ID in step 2.

[0079] b. If a candidate event at a certain level does not meet the attribute constraints, such as cross-product conversion, the stack backtracking can be used to quickly return to the previous level for re-selection, avoiding the performance loss caused by the traditional technology of "forward full re-traversal", and improving the attribute verification efficiency by more than 20%.

[0080] By combining forward traversal prioritizing efficiency with reverse backtracking prioritizing accuracy, a closed loop of "narrowing the scope first and then matching precisely" is formed. This solves the problem of "misjudgment caused by filtering only by time" in existing technologies and avoids the inefficiency caused by "full attribute verification".

[0081] 2.1.2 Stack structure and performance optimization design of candidate sets;

[0082] To adapt to efficient computing in scenarios with massive amounts of data, this invention adopts a lightweight and layered design for the core data structure stack and candidate set, reducing memory usage and improving query speed.

[0083] Lightweight stack structure memory management: The `BacktrackState` stack uses OLAP framework underlying memory interfaces such as ClickHouse's `Arena` memory pool and Doris's `MemoryPool` to manage memory. Each stack element stores only necessary fields. Simplified state information: Only core data such as "level index is 1 byte, attribute hash value is 8 bytes, and timestamp is 8 bytes" are retained, and the memory usage of a single state is controlled within 32 bytes. Dynamic stack depth control: The stack depth is limited by the `max_level` parameter, with a default of ≤10 levels, which is suitable for the number of funnel steps in different scenarios such as 3-5 steps in e-commerce and 5-8 steps in education, avoiding memory overflow.

[0084] Storage optimization of hierarchical candidate sets: `level_candidates` is stored hierarchically according to "event level", and invalid data is dynamically removed by combining time windows: Hierarchical storage: each level stores candidate events independently. When backtracking, the target data can be directly located through the level index without traversing all events, reducing query time by 40%; Expired data removal: events exceeding the time window are automatically filtered during forward traversal (such as step 2 time > step 1 time + 24 hours), reducing the amount of candidate set data by 30%-60% and memory usage by more than 60%.

[0085] 2.1.3 Flexible design for dynamic attribute configuration;

[0086] To meet the personalized analysis needs of various industries, this invention supports dynamic configuration of associated attributes and matching steps, adapting to conversion path analysis in different business scenarios.

[0087] Parameterized associated attribute settings: Users can customize associated attribute fields via the `attribute` parameter, such as `product_id` in e-commerce scenarios and `course_title` in education scenarios. The function automatically adapts to the native data types of the OLAP framework, such as `String` in ClickHouse and `VARCHAR` in Doris, without the need for additional type conversion.

[0088] Supports multiple attribute types: compatible with strings such as course name, integers such as product ID, and enumerations such as page type, to meet the dimensional analysis needs of different businesses;

[0089] Automatic attribute validation: The function integrates attribute format validation logic at the underlying level, such as NOT NULL checks and type matching, to avoid calculation errors caused by invalid parameters.

[0090] On-demand matching step configuration: The `match_events` parameter specifies the steps for validating related attributes. For example, `match_events=2,3` means that only steps 2 and 3 are validated, solving the rigid problem of "full match or no match" in traditional technology.

[0091] Flexible adaptation to different scenarios: In e-commerce scenarios, the "add to cart → place order → pay" step attribute matching can be configured with `match_events=2,3,4`, while the "login → browse" step does not require matching;

[0092] Parameter syntax compatibility: Supports continuous range `1-4`, discrete steps `1,3,5` and other formats, and is seamlessly compatible with OLAP framework SQL syntax such as ClickHouse SQL, reducing the learning cost for users.

[0093] 2.1.4 Underlying integrated attribute validation logic;

[0094] To completely resolve the performance bottleneck caused by external query associations, this invention embeds attribute validation logic into the underlying computation link of the OLAP framework, reducing data transmission and redundant computation.

[0095] Integration of source code-level attribute validation module: Add an "associative attribute validation module" to the aggregation function interfaces of OLAP frameworks such as ClickHouse's `IAggregateFunction` and Doris's `AggregationHandler`:

[0096] Embedded core process: The attribute matching logic is directly written into the event matching stage of funnel analysis, instead of relying on the upper-level SQL 'JOIN'. The entire process of user behavior event reading and verification is completed on a single machine, avoiding cross-node data 'shuffle' in distributed scenarios, reducing data transmission volume by 70%.

[0097] Collaboration with storage engines: Data storage layers such as ClickHouse's `MergeTree` force the binding of associated attribute fields and event fields. For example, the `event` table contains an `attr` column, avoiding cross-table access during attribute queries. In scenarios with hundreds of millions of data points, query time is reduced by 60%.

[0098] Through the above technical solution, the `windowFunnelWithAttribute` function implements triple verification of "time window + step sequence + associated attributes". While ensuring the accuracy of conversion analysis and excluding invalid conversions across attributes, it significantly improves the computing efficiency of massive data scenarios, providing an efficient solution for user conversion path analysis in e-commerce, education, finance and other fields.

[0099] (ii) Function registration and deployment, including registering the `windowFunnelWithAttribute` function according to the ClickHouse custom function specification, configuring parameters such as time window, associated attributes, behavior steps, and return values ​​such as conversion level and event ID list. Compile the ClickHouse program containing the new function and deploy it to the big data platform data analysis environment to connect the user behavior data storage and analysis links.

[0100] S300, Data Application: including data acquisition and storage, as well as function configuration and invocation;

[0101] (i) Data collection and storage, including collecting user behavior data and storing it in an OLAP database, wherein the behavior data includes timestamps, user identifiers, event types and related attribute fields;

[0102] Behavioral data collection: Conversion analysis of smart TV users from powering on to content playback. Funnel steps: S1: Power on (startup) → S2: Click on the recommended spot (click) → S3: Jump to the category page (jump) → S4: Enter the details page (detail_enter) → S5: Enter the playback page (play_start).

[0103] Attribute matching requirement: Only verify the consistency of content_id across S2-S5 (i.e., the content clicked, redirected, entered the details page, and started playing must be the same).

[0104] Data Warehouse Storage: A user behavior table is built using ClickHouse's MergeTree table engine, with user ID, event time, event type, and content_id as core fields. The collected behavioral data is stored in this table, leveraging ClickHouse's efficient storage capabilities to support rapid queries of massive amounts of user data, laying the foundation for subsequent analysis. The storage results are as follows (using user IDs u001 and u002 as examples):

[0105] u001 1690000000000 startup null S1 u001 1690000100000 click c001 S2 u001 1690000200000 jump c001 S3 u001 1690000300000 detail_enter c002 S4 u001 1690000400000 play_start c001 S5 u002 1690001000000 startup null S1 u002 1690001100000 click c003 S2 u002 1690001200000 jump c003 S3 u002 1690001300000 detail_enter c003 S4 u002 1690001400000 play_start c003 S5

[0106] (ii) Function configuration and invocation: This includes calling the windowFunnelWithAttribute aggregate function via SQL, passing in the time window, event sequence, associated attribute field and the step parameters of the attribute to be matched, performing funnel analysis and outputting the results.

[0107] The analysis is performed using the windowFunnelWithAttribute function of this invention, with the following parameter configuration:

[0108] 1. Time window: 3,600,000 milliseconds (1 hour; users must complete consecutive steps within 1 hour).

[0109] 2. Event sequence (step_events): [startup, click, jump, detail_enter, play_start];

[0110] 3. Related attribute field (attr_field): content_id;

[0111] 4. Steps requiring attribute matching (match_indices): [2,3,4,5] (i.e., S2 to S5 require checking the consistency of content_id);

[0112] SQL call statement: See appendix for details. Figure 6 ;

[0113] Forward traversal initial screening:

[0114] For each user's behavioral data, candidate events that fit within a time window are filtered step by step:

[0115] User u001:

[0116] level_candidates[0] (S1): (startup, null, 1690000000000);

[0117] level_candidates[1] (S2): (click, c001, 1690000100000) (100 seconds from S1, within the window);

[0118] level_candidates[2] (S3): (jump, c001, 1690000200000) (100 seconds from S2, within the window);

[0119] level_candidates[3] (S4): (detail_enter, c002, 1690000300000) (100 seconds from S3, within the window);

[0120] level_candidates[4] (S5): (play_start, c001, 1690000400000) (100 seconds from S4, within the window);

[0121] level_matches tracks the latest events at each level: [S1, S2, S3, S4, S5];

[0122] User u002:

[0123] Similarly, level_candidates are generated, and all steps are within the time window, with level_matches being [S1,S2,S3,S4,S5].

[0124] Reverse backtracking verification:

[0125] BacktrackState uses a stack structure to reverse-engineer the consistency of content_id from the highest level:

[0126] User u001:

[0127] Initial stack state: (Current level = 4 (S5), Matched events = [S5], Attribute set = {c001});

[0128] Verify S4 (Level 3): content_id=c002≠c001, mismatch, adjust the stack state to (current level=2 (S3), matched events=[S5], attribute set={c001});

[0129] Verify S3 (Level 2): ​​content_id=c001=c001, match, stack state updated to (current level=1 (S2), matched events=[S5,S3], attribute set={c001});

[0130] Verify S2 (Level 1): content_id=c001=c001, match, stack state updated to (current level=0 (S1), matched events=[S5,S3,S2], attribute set={c001});

[0131] Backtracking complete, effective level is 3 (S1-S3);

[0132] User u002:

[0133] Reverse verification from S5 (content_id=c003) shows that the content_id of S4-S2 is all c003, which is a complete match. The effective level is 5 (S1-S5).

[0134] Output results:

[0135] User u001: max_funnel_level=3;

[0136] User u002: max_funnel_level=5.

[0137] Example 2:

[0138] In one specific embodiment, refer to the appendix. Figure 3-4 , Figure 3 This is a flowchart of the forward traversal of the core logic of the function computation layer of this invention; the specific steps of the forward traversal are as follows:

[0139] 1) Initial checks and special case handling: If the input data is empty, return an empty result directly. If strict order is not required and the total number of events is 1, return the ID and level 1 of the first event.

[0140] 2) Data Preparation: Sort the event list and initialize two core containers: `level_candidates`: Stores candidate events by level, with each candidate containing a start time, end time, index, and attributes. `level_matches`: Stores the best currently matched event by level to track valid events at each level.

[0141] 3) Event Traversal and Processing: Traverse all events and classify them according to their event IDs (converted to 0-based indexes): Initial Events (Level 0): Directly add them to `level_candidates[0]` and update `level_matches[0]` to the latest event, marking "First event found". Regular Event Processing: When a matching event exists in the previous level, check whether the current event meets the time window constraint, i.e., whether it is within the window. If strict increment is enabled, it must be later than the previous event time. If the condition is met, add the event to the candidate list of the current level and update `level_matches` to the latest event; if the highest level has been reached, update the maximum level to the total number of events.

[0142] 4) Determine the maximum valid level: If the maximum level is still 0 after the traversal is completed, check `level_matches` from the back and take the first level with a match as the maximum level.

[0143] The entire process involves forward traversal of the event list, filtering candidate events that meet the time constraints by level, tracking the best match at each level, and finally determining the maximum event level that can be reached, providing a candidate basis for subsequent reverse backtracking.

[0144] Figure 4 This is a flowchart illustrating the reverse backtracking process of the core logic of the function computation layer in this invention; the specific reverse backtracking verification steps are as follows:

[0145] 1) Initialize the backtrack stack: Create a stack to store the backtrack state `BacktrackState`. Initially, push a state: the current level is `max_level - 1`, starting from the highest valid level. The initial level is the same as the current level, and the lists of matching IDs, attribute tracking, etc. are empty.

[0146] 2) Stack loop processing: When the stack is not empty, continuously retrieve the top state for processing; Termination condition judgment: If the current level is less than 0, all levels have been backtracked, and the number of matched IDs and the specific IDs are returned. Empty candidate processing: If there are no candidate events at the current level, i.e., `level_candidates` is empty, decrement the current level by 1, push the new state onto the stack, and continue backtracking.

[0147] 3) Candidate event verification: Perform reverse traversal, attribute constraints, and time window constraints on candidate events at the current level;

[0148] The process includes: Reverse traversal: Checking from back to front to verify if each candidate meets the conditions; Attribute constraints: If the level requires attribute matching `events_with_params` to include the level, checking if the candidate attributes are consistent with existing attributes, and if deduplication is required, confirming that the attributes are not reused; Time window constraints: If a matching ID already exists, checking if the end time of the current candidate and the time of the previous match are within the valid window, i.e., not exceeding the time range.

[0149] 4) Update State and Advance Backtracking: After finding a candidate that meets the criteria: Create a new state, add the candidate's ID and time to the matching list `matched_ids`. If attribute tracking is required, update the attribute mapping `level_attrs` and the used attribute set `used_attributes`. Decrement the current level by 1, push the new state onto the stack, continue backtracking to the previous level, and mark it as "a valid candidate found".

[0150] 5. Backtracking adjustment when no valid candidate is found: If there are no valid candidates at the current level: adjust the state according to the number of candidates at the initial level. If there is only one candidate at the initial level, backtrack to the previous level; otherwise, maintain the current initial level. Clear the current matching ID and attribute information, clearing attribute information as needed, push the new state to the stack, and retry backtracking.

[0151] The entire process uses a stack to save intermediate states, verifies candidate events in reverse from the highest level, and searches for valid matches by combining attributes and time constraints. If no match is found, the state is adjusted and backtracking is performed to finally obtain a matching result that meets the conditions.

[0152] Example 3:

[0153] A funnel analysis system for relational attributes based on an OLAP database, comprising:

[0154] The data storage module stores user behavior events, including timestamps, unique user identifiers, event types, and associated attributes. The associated attribute fields of the events are forcibly bound to the event fields, such as the association between the event column and the attr column in the MergeTree table of ClickHouse, providing a data foundation for underlying attribute validation.

[0155] The Function Compute module integrates the windowFunnelWithAttribute function, performs forward traversal screening through the level_candidates and level_matches dual containers, and stores candidate events that meet the time window according to level. It also performs reverse backtracking verification through the BacktrackState stack structure to verify the consistency of associated attributes from the highest level. The Function Compute module is developed based on the aggregation function interface of the OLAP framework, such as IAGgregateFunction of ClickHouse.

[0156] The interface module provides an SQL call interface, allowing users to configure the steps to be matched and customize associated attribute fields through the match_events parameter. The interface syntax conforms to the SQL standard of the OLAP framework.

[0157] In an optional embodiment, when the OLAP framework is ClickHouse, the Function Compute module is embedded in the query execution chain of the MergeTree engine and called through the AggregationNode node; when the OLAP framework is Doris, the BrokerLoad data processing chain is embedded and called through the AggregationOperator operator.

[0158] During the initial screening in the forward traversal, the level_candidates container stores candidate events according to event level. Each candidate event includes a start time, end time, index, and associated attributes. The serialization / deserialization logic of the container is adapted to the block data format of the OLAP framework, such as ClickHouse's Block.

[0159] Furthermore, during the forward traversal, if an event satisfies the time window constraint and belongs to the current level, it is added to level_candidates and level_matches is updated to the latest time event. The calculation of the time window is based on the time function of the OLAP framework, such as ClickHouse's toUnixTimestamp.

[0160] During reverse backtracking verification, the BacktrackState of the stack structure contains the current level, a list of matched event IDs, associated attribute mappings, and a set of used attributes. The upper limit of the stack depth can be dynamically adjusted through custom configuration parameters of the OLAP framework.

[0161] The termination condition for reverse backtracking is: the current level is less than 0, which means that all levels have been backtracked. The number of matched event IDs and the specific ID are returned. The output format of the result is adapted to the query result type of the OLAP framework, such as the UInt32 array of ClickHouse.

[0162] Although the present invention has been described herein with reference to illustrative embodiments, the above embodiments are merely preferred embodiments of the present invention, and the implementation of the present invention is not limited to the above embodiments. It should be understood that those skilled in the art can devise many other modifications and implementations, which will fall within the scope and spirit of the principles disclosed in this application.

Claims

1. A method for funnel analysis of association attributes based on OLAP databases, characterized in that, The specific steps include: S100. Preliminary preparations and environment setup, including development environment setup and function development planning; The development environment setup includes: pulling the appropriate version of the source code from the OLAP database open-source code repository, configuring a compatible compiler and basic dependency libraries, and setting up a local development branch. The function development plan includes: analyzing the complete conversion path of user operations and locating user drop-off points; excluding invalid conversions across content and only counting valid paths within the same content ID; and clarifying the development direction of aggregate functions to verify the consistency of user behavior steps and associated attributes, and to count conversion paths within the same content ID. S200. Development and integration of the windowFunnelWithAttribute aggregation function in the OLAP framework source code layer: including interface and logic construction, and function registration and deployment; The interface and logic construction includes: developing a funnel core calculation module. This funnel core calculation module is based on the original windowFunnel time window and hierarchical flow logic of ClickHouse, incorporates associated attribute verification, and adopts a two-way verification mechanism of "forward traversal initial screening" plus "reverse backtracking verification". Function registration and deployment include: registering the `windowFunnelWithAttribute` aggregate function according to ClickHouse's custom function specification, configuring parameters and return values; compiling the program containing the `windowFunnelWithAttribute` aggregate function and deploying it to the big data platform's data analysis environment to connect user behavior data with the analysis process.

2. The method for funnel analysis of association attributes based on OLAP database according to claim 1, characterized in that, The two-way verification mechanism is as follows: The initial screening is performed by forward traversal, based on the time window and the step order. The level_candidates container is used to store candidate events hierarchically according to the event level, i.e. the step order, and the level_matches container is used to dynamically track the latest valid events at each level. Reverse backtracking verification is performed on the candidate events after forward filtering based on the BacktrackState stack structure, and the matching of related attributes is verified in reverse from the highest level.

3. The method for funnel analysis of association attributes based on OLAP database according to claim 1, characterized in that, The forward traversal initial screening method includes the following steps: Initial checks and special case handling: If the input data is empty, return an empty result directly; if strict order is not required and the total number of events is 1, return the ID and level 1 of the first event; Data preparation and sorting: Sort the event list and initialize two core containers: the level_candidates container stores candidate events by event level, and each candidate event includes start time, end time, index, and associated attributes; the level_matches container stores the best currently matched event by level, used to track valid events at each level. Event traversal and processing: Traverse all events and classify them according to their event IDs. Initial event processing: Add the event directly to level_candidates[0] and update level_matches[0] to the latest event, marking "first event found"; Regular event processing: When there is a matching event in the previous level, check whether the current event meets the time window constraint; If the condition is met, add the event to the candidate list of the current level and update level_matches to the latest event; If the highest level has been reached, update the maximum level to the total number of events. Determine the maximum valid level: If the maximum level is still 0 after the traversal is completed, check level_matches from the back and take the first level with a match as the maximum level.

4. The method for funnel analysis of association attributes based on OLAP database according to claim 1, characterized in that, The reverse backtracking verification method specifically includes the following steps: Initialize backtracking stack: Create a stack to store backtracking states. Initially, push a state onto the stack: starting from the highest valid level, the initial level is the same as the current level, and the list of matching IDs and attribute tracking is empty; Stack loop processing: When the stack is not empty, continuously take out the top state of the stack for processing; Termination condition judgment: If the current level is less than 0, all levels have been backtracked, and the number of matched IDs and the specific IDs are returned; Empty candidate processing: If there are no candidate events at the current level, i.e., level_candidates is empty, decrement the current level by 1, push the new state to the stack and continue backtracking. Candidate event verification: Perform reverse traversal, attribute constraints, and time window constraints on candidate events at the current level; Update status and advance backtracking: After finding a candidate that meets the criteria, create a new status and add the candidate's ID and time to the matching list; If no valid candidate is found, backtracking adjustment is performed: if there is no valid candidate at the current level, the state is adjusted according to the number of candidates at the initial level; the current matching ID and attribute information are cleared, the new state is pushed to the stack, and backtracking is retried.

5. The method for funnel analysis of association attributes based on OLAP database according to claim 4, characterized in that, In the candidate event verification, the reverse traversal is as follows: check from back to front to verify whether each candidate meets the conditions: the attribute constraint is: if the level requires attribute matching, check whether the candidate attribute is consistent with the existing attribute and whether the attribute is not reused; the time window constraint is: if there is already a matching ID, check whether the end time of the current candidate and the time of the previous match are within the valid window.

6. The method for funnel analysis of association attributes based on OLAP database according to claim 4, characterized in that, The process of updating the state and advancing backtracking also includes: if attribute tracking is required, updating the attribute mapping and the set of used attributes; decrementing the current level by 1, pushing the new state onto the stack, continuing to backtrack to the previous level, and marking it as "a valid candidate has been found".

7. The method for funnel analysis of association attributes based on OLAP database according to claim 1, characterized in that, The development and integration of the windowFunnelWithAttribute aggregate function in the OLAP framework source code layer also includes: The stack structure and candidate set are designed with performance optimization to reduce memory usage and improve query speed by making the core data structures, namely stack and candidate set, lightweight and hierarchical. The flexible design of dynamic attribute configuration supports the dynamic configuration of associated attributes and matching steps, adapting to the conversion path analysis of different business scenarios. The underlying integrated attribute validation logic is embedded in the underlying computing chain of the OLAP database, reducing data transmission and redundant calculations.

8. A method for funnel analysis of association attributes based on an OLAP database according to any one of claims 1-7, characterized in that, The funnel analysis method further includes the following steps: S300, Data Application: including data acquisition and storage, as well as function configuration and invocation; Data collection and storage includes collecting user behavior data and storing it in an OLAP database. The behavior data includes timestamps, user identifiers, event types, and related attribute fields. Function configuration and invocation include calling the windowFunnelWithAttribute aggregate function via SQL, passing in the time window, event sequence, associated attribute fields, and step parameters of the attributes to be matched, performing funnel analysis, and outputting the results.

9. A funnel analysis system for relational attributes based on an OLAP database, characterized in that, A method for implementing a correlation attribute funnel analysis based on an OLAP database as described in any one of claims 1-8 includes the following modules: The data storage module stores user behavior events, including timestamps, unique user identifiers, event types, and associated attributes. The associated attribute fields of the events are forcibly bound to the event fields for storage, providing a data foundation for underlying attribute validation. The Function Compute module integrates the windowFunnelWithAttribute aggregate function. It performs forward traversal screening through the level_candidates and level_matches dual containers to store candidate events that meet the time window by level. It also performs reverse backtracking verification through the BacktrackState stack structure to verify the consistency of associated attributes from the highest level. The Function Compute module is developed based on the aggregate function interface of the OLAP framework. The interface module provides an SQL call interface, allowing users to configure the steps to be matched and customize associated attribute fields through the match_events parameter. The interface syntax conforms to the SQL standard of the OLAP framework.

10. The association attribute funnel analysis system based on an OLAP database according to claim 9, characterized in that, When the OLAP framework is ClickHouse, the Function Compute module is embedded in the query execution chain of the MergeTree engine and called through the AggregationNode node; When the OLAP framework is Doris, the Function Compute module is embedded in the BrokerLoad data processing chain and called through the AggregationOperator operator.

Images