A method and apparatus for analyzing and tracking user call data from multi-channel advertising.
By constructing a standardized user inbound detail ledger and association mapping model, the problem of inconsistent data formats for multi-channel advertising was solved, achieving unified integration of inbound data from multiple channels and full-link data association. This provided accurate channel screening and statistical analysis, improving resource utilization and advertising optimization effects.
Patent Information
- Application Number
- CN202611235224.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-08-14
- Publication Date
- 2026-11-10
AI Technical Summary
Existing technologies suffer from inconsistent data formats across multiple advertising channels, a lack of precise channel screening and statistical solutions, and an inability to correlate and track user entry data with subsequent behavioral data, resulting in data silos and low resource utilization.
A standardized user call detail ledger is constructed. The original call data is accessed through multiple pre-set heterogeneous data interfaces. The data format is unified and cleaned to generate a unique user identifier. An association mapping model is constructed to associate delivery-call-after-effect data. A dynamic data aggregation algorithm is executed for filtering and statistics.
It has achieved unified integration and accurate querying of incoming data from multiple channels, opened up a closed data loop across the entire chain, provided a scientific basis for the quantitative evaluation of user quality and channel quality, and improved data support for campaign optimization.
Smart Images

Figure CN123068488A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data management and analysis technology, specifically to a method and apparatus for analyzing and tracking user online data from multi-channel advertising. Background Technology
[0002] In the fields of digital marketing and advertising management, monitoring and evaluating campaign performance is a core element in optimizing campaign strategies and allocating budgets rationally. Currently, businesses' advertising campaigns are typically distributed across multiple third-party platforms, such as 360 Search, Tencent Ads, Baidu Information Feed, and Douyin Information Feed. This multi-platform coexistence presents significant technical challenges.
[0003] First, existing technologies suffer from data silos. Data formats, field definitions, and interface specifications vary across advertising platforms, resulting in fragmented storage of campaign data (such as impressions, clicks, and purchases) and user lead generation data (such as users adding sales representatives on WeChat through ad landing pages) in their respective independent systems (e.g., third-party platforms, FineReport). There is a lack of a standardized mechanism in the market to integrate detailed user lead generation data from different channels for specific marketing scenarios. This prevents companies from building a unified user lead generation ledger, hinders effective traceability of individual user lead generation data by campaign staff, and fails to meet the demands for efficient data querying and batch export required for refined operations.
[0004] Secondly, existing technologies lack precise channel screening and statistical solutions. Due to the lack of a standardized data foundation, advertising personnel struggle to quickly and accurately quantify and analyze the "inbound traffic and inbound traffic percentage from different channels." Without the ability to quantify the "star rating" or contribution of each advertising channel, effective data support cannot be provided for subsequent budget allocation and channel combination optimization. This results in advertising budget allocation often relying on experience rather than data, leading to low resource utilization.
[0005] Finally, and most importantly, current technology completely lacks the ability to correlate and track user lead data with their subsequent behavioral data. For example, whether a user who leads through an ad "talks" to the sales representative within an hour, whether they delete the sales representative's WeChat account within 24 hours, and whether the user subsequently makes a payment and the amount paid—these core indicators that truly reflect user and channel quality are fragmented in current technology. Without connecting the entire data chain from "advertising → lead generation → follow-up," it's impossible to quantitatively assess user quality, let alone guide the optimization of front-end advertising strategies. This not only wastes data assets but also leaves advertising optimization efforts without closed-loop data support.
[0006] Therefore, how to construct a standardized user lead details ledger to achieve the integration and query of full lead data from multiple channels; how to achieve accurate screening and statistical analysis of lead data from multiple channels; and how to establish a correlation mechanism between user lead data and post-implementation data to connect data across the entire chain are technical problems that urgently need to be solved in the current advertising technology field. Summary of the Invention
[0007] In view of this, the purpose of the present invention is to provide a method and apparatus for analyzing and tracking user call data in multi-channel advertising, which aims to solve the problems in the prior art, such as inconsistent data formats across multiple channels, the inability to establish a unified standard data format, the lack of accurate channel screening and statistical technology solutions, and the lack of the ability to correlate and track user call data with their subsequent behavioral data.
[0008] According to a first aspect of the present invention, a method for analyzing and tracking user call data for multi-channel advertising is provided, the method comprising:
[0009] Construct a standardized user call detail ledger: Through multiple preset heterogeneous data interfaces, adapt and access the original call data of at least one advertising channel; for each original call data, perform data format unification cleaning based on a preset field mapping table, filter dirty data, and generate standardized call records with a unified data structure; store the standardized call records in the user call detail ledger, and generate or associate a unique user identifier for each record, thereby constructing a standardized dataset that supports accurate querying and batch export based on the unique user identifier; Construct and execute an association mapping model between incoming and subsequent data: Based on the standardized dataset, obtain user subsequent behavior data corresponding to the standardized incoming record; construct an association mapping model, which includes at least: associating and binding the user subsequent behavior data with the standardized incoming record based on the unique user identifier; and associating the standardized incoming record with delivery channel information; thereby generating an extended dataset containing the full-link data association relationship of delivery-incoming-subsequent data; Execute a dynamic data aggregation algorithm based on a time window: Based on the generated extended dataset, receive one or more filtering parameters from the user interface to form a filtering parameter set; perform conditional filtering on the extended dataset based on the filtering parameter set to generate a filtered data subset; perform group aggregation calculation on the filtered data subset according to a preset grouping dimension to generate an aggregated statistical report that includes at least the incoming line volume, the incoming line percentage, and the core after-effect indicators calculated based on after-effect behavioral data.
[0010] Preferably, The data format unification and cleaning based on the preset field mapping table includes: Obtain first raw incoming data from the first advertising delivery channel, wherein the first raw incoming data includes a first channel field that adopts the first field naming rule; Obtain second raw incoming data from the second advertising delivery channel, the second raw incoming data containing the second channel field using the second field naming rule; Call the pre-configured field mapping table, which defines the conversion rules between the original fields of different advertising channels and the standardized ledger fields; According to the field mapping table, the first channel field is mapped to a first standardized field, and the second channel field is mapped to a second standardized field, wherein the first standardized field and the second standardized field have the same field identifier and data type, thereby achieving unified format of multi-channel data.
[0011] Preferably, The construction of the association mapping model includes: Define and instantiate core data entities: instantiate at least one incoming data entity and one subsequent data entity; the incoming data entity includes a unique identifier for the incoming record, the unique user identifier, the incoming time, and the incoming channel code as key fields; the subsequent data entity includes a unique identifier for the subsequent record, the unique user identifier, the associated unique identifier for the incoming record, and at least one subsequent indicator field; Generate and bind a unique user identifier: In response to a user entering the landing page through any advertising channel, generate an anonymous user identifier; in response to the same user completing the operation of adding WeChat or joining a group, obtain a unique social platform identifier through a third-party platform callback interface; establish a mapping relationship between the anonymous user identifier and the unique social platform identifier, and use the unique social platform identifier as the unique user identifier throughout the entire link; The process involves associating incoming call data with advertising channel information: When a user enters the landing page, the channel linking parameters are parsed from the Uniform Resource Locator (URL) of the landing page; the channel code, promotion account identifier, project identifier, and ad identifier are extracted based on the channel linking parameters; when generating the standardized incoming call record, the channel code, promotion account identifier, project identifier, and ad identifier are stored as components of the record, thus completing the logical association between incoming call data and advertising channel information. Real-time collection and association of post-event data: A user's post-event behavior events are monitored in real time through an event listener; when a post-event behavior event is detected, the user's unique identifier and occurrence time associated with the event are obtained; the incoming line data entity is queried based on the user's unique identifier to determine the target incoming line record most relevant to the post-event behavior; based on the incoming line record's unique identifier and incoming line time, the corresponding post-event indicator field in the post-event data entity is calculated and updated, thereby completing the association and binding.
[0012] Preferably, The user post-event behavior data includes at least user pop-up behavior and user deletion behavior; The step of associating and binding the user's subsequent behavior data with the standardized incoming call record based on the unique user identifier includes: Retrieve the target call record, which includes the call record unique identifier, user unique identifier, call time, and corresponding sales identifier; A first time window and a second time window are set, wherein the first time window starts at the incoming time and lasts for a first preset duration, and the second time window starts at the incoming time and lasts for a second preset duration, and the second preset duration is longer than the first preset duration; In response to detecting a bubbling event initiated by the user's unique identifier to the sales identifier within the first time window, the value of the first bubbling flag field corresponding to the unique identifier of the incoming line record in the subsequent data entity is updated to "Yes"; In response to the detection of the bubbling behavior event within the second time window but not within the first time window, the value of the second bubbling flag field in the subsequent data entity is updated to "Yes"; In response to a deletion behavior event detected within the first time window where the user's unique identifier deletes the friend relationship corresponding to the sales identifier, the values of the first deletion flag field and the deletion time field in the subsequent data entity are updated to "Yes" and the current timestamp, respectively.
[0013] Preferably, The dynamic data aggregation algorithm includes: Conditional filtering stage: Receive the set of filtering parameters P = {p1, p2, …, p n}, where each parameter p i It includes field names, operators, and values; for each data d in the extended dataset D, it is determined whether it meets all the conditions in the filtering parameter set P, and all data d that meet the conditions are combined into a filtered data subset D'; Grouping and aggregation phase: Receives a set of grouping dimensions G = (g1, g2, …, g m); Based on G, perform a grouping operation on the filtered data subset D' to generate at least one grouping key G_key j and its corresponding data subset S j For each data subset S j Calculate a set of predefined aggregation metrics M(S) j The aggregated metric M includes at least a count-based incoming volume metric, a division-based incoming volume percentage metric, and a ratio metric based on the aftereffect metric field. Sorting Output Stage: Receives a sorting field and a sorting direction; sorts all groups and their corresponding aggregation index results according to the sorting field and sorting direction; outputs a sorted aggregation result report.
[0014] Preferably, The calculation of a set of predefined aggregation indices M(S) j It should include at least one of the following ratio metrics with divide-by-zero protection: Calculate the percentage of incoming data: Divide the number of data entries in the group by the total number of data entries in the filtered subset, and then multiply by 100%. Calculate the one-hour bubble rate: Divide the number of data entries in the group that are marked as "yes" for one hour bubble rate by the number of data entries in the group that are marked as "yes" for whether they are within a shift, and then multiply by 100%. Calculate the incoming cost: Sum the consumption amounts of all records in the group and divide by the number of records in the group; To calculate Samsung's cumulative return on investment: divide the sum of the cumulative activation amount of Samsung in all records in the group by the sum of the consumption amount of all records in the group; Any division operation that might result in a divisor of zero is handled by a safe division function. This function returns a predefined non-numeric identifier when the denominator is zero, instead of performing the division operation or throwing an exception.
[0015] Preferably, The dynamic data aggregation algorithm also includes an incremental aggregation optimization logic: Receive a new query request containing the first time range; Determine if there is a cached result of the previous query, and if the second time range of the previous query overlaps with the first time range. When there is overlap, calculate the incremental time interval ΔT between the first time range and the second time range; Only incremental data ΔD within the incremental time interval ΔT is selected from the extended dataset D; Perform grouped aggregation calculations on the incremental data ΔD to obtain the incremental aggregation result ΔR; The cached previous query result R_cache is merged with the incremental aggregation result ΔR to generate the final new query result R_new, thereby avoiding re-aggregation and calculation of the full data.
[0016] Preferably, The dynamic data aggregation algorithm also includes a dynamic time window truncation logic: When calculating cumulative metrics related to user payments, obtain the system timestamp at the time of the current query execution and record it as the current system time; For each incoming line record d, query all user order data associated with it; When calculating the cumulative activation amount, only the order amount of orders whose order time is less than or equal to the current system time is aggregated; When calculating the activation amount for a specific period, the statistical cutoff time is dynamically calculated based on the incoming time of incoming line record d and the preset period offset. Only the order amount for incoming line time less than or equal to the order time and order time less than or equal to the statistical cutoff time is summarized. If the current system time is less than the statistical cutoff time, the current system time is used as the actual statistical cutoff time.
[0017] According to a second aspect of the present invention, an apparatus for analyzing and tracking user call data for multi-channel advertising is provided. The apparatus is used to implement the method described in any one of the preceding embodiments, and the apparatus includes: Ledger Construction Module: This module is used to adapt and access the raw incoming data of at least one advertising channel through multiple preset heterogeneous data interfaces; for each raw incoming data, it performs data format unification cleaning based on a preset field mapping table, filters dirty data, and generates standardized incoming data records with a unified data structure; it stores the standardized incoming data records in the user incoming data details ledger, and generates or associates a unique user identifier for each record, thereby constructing a standardized dataset that supports accurate querying and batch export based on the unique user identifier; The association mapping model execution module is used to obtain user post-event behavior data corresponding to the standardized incoming line record based on the standardized dataset; construct an association mapping model, which includes at least: associating and binding the user post-event behavior data with the standardized incoming line record based on the unique user identifier; and associating the standardized incoming line record with delivery channel information; thereby generating an extended dataset containing the full-link data association relationship of delivery-incoming line-post-event. Data aggregation module: Based on the generated extended dataset, it receives one or more filtering parameters from the user interface to form a filtering parameter set; performs conditional filtering on the extended dataset based on the filtering parameter set to generate a filtered data subset; performs group aggregation calculation on the filtered data subset according to a preset grouping dimension to generate an aggregated statistical report that includes at least the incoming line volume, the incoming line percentage, and the core aftereffect indicators calculated based on aftereffect behavior data.
[0018] The technical solutions provided by the embodiments of the present invention may include the following beneficial effects: This application, by constructing a standardized user lead log, integrates lead data from multiple heterogeneous advertising platforms into a unified format for the first time, solving the data silo problem and enabling precise querying and traceability of each user lead. Through an innovative correlation mapping model, it connects the data flow of the three key links—"advertising channel," "user lead," and "post-event behavior"—constructing a complete "advertising-lead-post-event" data loop, providing an unprecedented data foundation for quantitatively evaluating user and channel quality. Through dynamic data aggregation algorithms, it achieves real-time, multi-dimensional, and refined filtering and statistics of massive amounts of end-to-end data. In particular, the quantification of core post-event indicators such as 1-hour bubble rate, deletion rate, and ROI shifts ad optimization from "experience-based" to "data-driven," providing a scientific basis for accurate budget allocation and optimized customer acquisition paths.
[0019] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit the invention. Attached Figure Description
[0020] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.
[0021] Figure 1 This is a flowchart illustrating a method for analyzing and tracking user call data for multi-channel advertising, according to an exemplary embodiment. Figure 2 This is a system schematic diagram illustrating an analysis and tracking device for user call data of multi-channel advertising delivery, according to another exemplary embodiment; In the attached diagram: 1-Ledger construction module, 2-Association mapping model execution module, 3-Data aggregation module. Detailed Implementation
[0022] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. Rather, they are merely examples of apparatuses and methods consistent with some aspects of the invention as detailed in the appended claims.
[0023] Example 1: Figure 1 This is a flowchart illustrating a method for analyzing and tracking user call data in multi-channel advertising, according to an exemplary embodiment. Figure 1 As shown, the method includes: S1. Construct a standardized user call detail ledger: Through multiple preset heterogeneous data interfaces, adapt and access the original call data of at least one advertising channel; for each original call data, perform data format unification cleaning based on a preset field mapping table, filter dirty data, and generate a standardized call record with a unified data structure; store the standardized call record in the user call detail ledger, and generate or associate a unique user identifier for each record, thereby constructing a standardized dataset that supports accurate querying and batch export based on the unique user identifier; S2, construct and execute the association mapping model between incoming and subsequent data: based on the standardized dataset, obtain user subsequent behavior data corresponding to the standardized incoming record; construct the association mapping model, which includes at least: associating and binding the user subsequent behavior data with the standardized incoming record based on the unique user identifier; and associating the standardized incoming record with delivery channel information; thereby generating an extended dataset containing the full-link data association relationship of delivery-incoming-subsequent data; S3, Execute a dynamic data aggregation algorithm based on a time window: Based on the generated extended dataset, receive one or more filtering parameters from the user interface to form a filtering parameter set; Perform conditional filtering on the extended dataset based on the filtering parameter set to generate a filtered data subset; Perform grouping aggregation calculation on the filtered data subset according to a preset grouping dimension to generate an aggregated statistical report that includes at least the incoming line volume, the incoming line ratio, and the core after-effect indicators calculated based on after-effect behavior data; It is understood that this embodiment specifically includes a standardized ledger construction stage, an association mapping model construction stage, and a dynamic aggregation analysis stage. The following provides a detailed description of each stage, including: Standardized ledger construction phase: This phase aims to address the challenges of heterogeneous data across multiple platforms and the inability to process it uniformly in existing technologies. The objective of this embodiment is to integrate with four mainstream advertising platforms: 360 Search, Tencent Ads, Baidu Information Flow, and Douyin Information Flow, and to standardize their user online data.
[0024] First, this embodiment developed and deployed heterogeneous data interfaces for these four platforms. Each interface encapsulates the authentication, request, rate limiting, and error handling logic of the corresponding platform's API. For example, for the interface of the 360 platform, the API Key provided by it is used for authentication, and its "Promotion Data Report" interface is called periodically; for the interface of the Douyin platform, the event data is retrieved in accordance with its "Conversion Feedback API Integration Instructions".
[0025] Next, the received raw data undergoes format standardization and cleaning, which is the core technical point of this stage. After each interface pulls the raw data, it calls a data cleaning engine, which internally maintains a dynamic "field mapping relationship table", as shown in the table below:
[0026] The engine iterates through each field of the raw data, looking up the mapping table. For example, when processing data from Tencent, it reads the raw field "Advertisement" and maps its value to the standardized field "Project Name". Simultaneously, the engine performs dirty data filtering, such as removing records where key fields (like user ID or call time) are empty, or converting incorrectly formatted date strings to a unified timestamp format (e.g., 2023-10-27 10:00:00). After this process, information such as "Promotion Plan", "Advertisement", and "Plan Name" from different platforms are ultimately unified into "Project Name".
[0027] Then, the records are stored in a standardized ledger and a unique identifier is generated. The cleaned standardized records are written to the T_incoming_standard (standardized inbound ledger table) in the database. The structure of this table is carefully designed, and the key fields are shown in the table below:
[0028] When a record is inserted, the system generates a globally unique record_id and attempts to associate it with or generate a union_id (the specific binding logic is detailed in the construction process of the association mapping model). At this point, a standardized dataset supporting precise queries by union_id, record_id, or time range is complete. Users can perform single-data queries or batch export operations (export format is CSV or Excel) based on this ledger in the front-end interface, perfectly solving the data traceability problem.
[0029] Construct and execute the association mapping model between incoming and outgoing data: This stage is the most innovative part of the invention, aiming to connect the entire data chain from "delivery to inbound to post-effect." Building upon the standardized ledger generated in stage one, this stage constructs a correlation mapping model, which defines three core data entities: T_incoming (inbound data entity), T_effect (post-effect data entity), and T_channel (delivery channel information entity). Specific steps include: Instantiate core data entities: The system has created a table in the database corresponding to the above entities, with the following key fields: The T_incoming (incoming data table) includes: incoming_id(VARCHAR): Incoming record ID, primary key; union_id(VARCHAR): A unique identifier for the user, serving as the core association key; uuid(VARCHAR): Anonymous access identifier; incoming_time(DATETIME): Incoming time; channel_code(VARCHAR): Channel code; account_id, project_id, ad_id(VARCHAR): Targeting dimension identifier; staff_wechat_id(VARCHAR): The WeChat ID of the company that receives and assists sales staff.
[0030] T_effect (aftereffect data table) includes: effect_id(VARCHAR): The ID of the subsequent record, the primary key; union_id(VARCHAR): User-unique identifier, foreign key; incoming_id(VARCHAR): Incoming record ID, fine-grained association key, pointing to... T_incoming; bubble_1h, bubble_24h (TINYINT): Bubble markers for 1 / 24-hour intervals; del_wechat_1h,del_wechat_24h(TINYINT): Delete micro-marks every 1 / 24 hours; del_wechat_time(DATETIME): Deletes the time of the WeChat message; star3_total_amount: Total amount of orders placed for Samsung products; star3_class_amount: The full amount paid upon completion of the Samsung subscription; star3_30d_amount(DECIMAL): The full amount paid by Samsung after 30 days of activation; Other follow-up indicator fields.
[0031] T_channel (Placement Channel Information Table) includes: channel_code: Channel code, primary key; channel_name: Channel name, such as "360 Search" or "Baidu Information Flow"; account_id: Promotion account ID; account_name: The name of the promotional account.
[0032] Perform the generation and binding of unique user identifiers: This step is fundamental to achieving accurate association, and its process is as follows: When a user clicks on an ad and enters the landing page, the front-end JS code generates a UUID (e.g., uuid_a1b2c3) and stores it in the browser's localStorage (the browser's built-in front-end local persistent storage tool). It is also appended to the header of all subsequent page requests. When the system back-end receives the first request, it creates a temporary record in T_incoming (the incoming data table) with uuid_a1b2c3 and union_id empty.
[0033] When a user decides to add a sales WeChat account or join a WeChat group, clicking the button will activate the WeChat account's add function. After the user completes the operation, the WeChat account server will send an event notification to the pre-configured callback interface. This notification contains the key UserID (sales ID) and the user's ExternalUserID. Through the WeChat account API, the user's globally unique identifier UnionID (e.g., union_wxyz) can be obtained from the ExternalUserID. The callback interface processing logic is as follows: After receiving information such as UnionID and Sales ID, it will search for the temporary incoming line record in the database based on the uuid_a1b2c3 transmitted in the current HTTP session or cookie. After finding it, it will update the union_id field of this record to union_wxyz. At this point, the binding from anonymous access (UUID) to real-name social identity (UnionID) is completed. After that, no matter what channel or time the user uses, as long as they use the same WeChat identity, their behavior can be associated through union_id. Linking incoming data with advertising channel information: When a user visits a landing page with delivery parameters for the first time, the URL contains rich channel information. For example, a landing page URL for Baidu Information Flow might be: https: / / xxx.com / landing?bd_vid=12345678&plan_id=111&unit_id=222&... When the system's backend service receives the page request, a unified request interceptor is used. This interceptor is responsible for parsing the request URL and extracting standardized fields according to the pre-defined regular expressions or parameter name mapping rules for each channel. For example, for Baidu Information Flow, it knows that plan_id corresponds to project_id and unit_id corresponds to ad_id. After extraction, this information is temporarily stored in the request context. Subsequently, when the user triggers an incoming event (such as adding WeChat) and generates the final T_incoming record, this delivery information is retrieved from the request context and written to the database, thus forcibly associating a single incoming event with a specific delivery channel, project, and advertisement.
[0034] Real-time collection and association of post-execution data: This is a crucial execution step of the model, populating the T_effect table through event-driven and time window constraints, and maintaining a highly reliable message queue and a set of event listeners. Bubble Behavior Monitoring: The listener monitors callback events in WeChat Work. When a user sends the first message to a salesperson ("bubble"), the callback event includes union_id, staff_wechat_id, and a timestamp. The processor uses union_id and staff_wechat_id to find the most recent incoming line record (incoming_id) and its incoming_time for that user and salesperson that has not yet been bound to a bubbling behavior. Then, it compares the current timestamp with the incoming_time. If the difference is less than or equal to 1 hour, it updates the corresponding record in the T_effect table with bubble_1h=1; if the difference is greater than 1 hour but less than or equal to 24 hours, it updates bubble_24h=1. Deletion behavior monitoring: Similarly, the listener listens for events related to "deleting external contacts" in WeChat Work. When a user deletes a sales WeChat account, the event is triggered. The processor also looks up the associated incoming_id and updates the del_wechat_1h, del_wechat_24h, and del_wechat_time fields based on the time difference. Payment Behavior Association: The system also monitors order creation events in the internal CRM system. When a Samsung product order is generated, the event includes union_id, order_time, and order_amount. The processor executes an association rule: it queries the T_incoming table based on union_id to retrieve all incoming records for that user, sorts them in descending order by incoming_time, finds the incoming record whose incoming time is earlier than and closest to order_time, and attributes this order to that incoming record. At the same time, it determines whether this is the first Samsung order generated by this union_id. If so, it updates is_first_purchase and first_purchase_time. Finally, based on the order amount and time, it updates fields such as star3_total_amount, star3_class_amount (if the order occurred before the end of the shift) or star3_30d_amount (if the order occurred within 30 days after the incoming record). Through the above steps, each record in the T_effect table corresponds one-to-one with an incoming line record in the T_incoming table, and all core post-effect metrics are accurately filled. At this point, an extended dataset containing the full-link relationship of "deployment-incoming-post-effect" is completed. For example, using this solution, one can easily query the complete link information of "from Baidu Information Flow, project name 'Spring Promotion', popped up within 1 hour after entering the network, and subsequently had paying users". Execute a dynamic data aggregation algorithm based on a time window: This phase aims to provide business personnel with efficient and flexible data statistical analysis capabilities. The algorithm receives requests from the front end (including filtering parameters, grouping dimensions, and sorting requirements) and processes the extended dataset generated in Phase 2.
[0035] A specific aggregation analysis scenario: A marketing manager wants to analyze "how the 1-hour bubbling rate and Samsung product ROI performance of users from different inbound channels were last week, and sorted by the number of inbound users."
[0036] Receive filtering parameters and conditions: On the front-end interface, the user filters the "incoming time" to "last Monday to last Sunday". The system back-end receives the request and constructs a filter parameter set P, for example: P = { (incoming_time, BETWEEN, [last_monday_00:00, last_sunday_23:59])}. The data aggregation engine first executes the Filter(D, P) operation, which scans the extended dataset D (essentially a logical view of T_incoming, T_effect, and T_channel), retaining only records where the incoming time is within the previous week, forming a data subset D'. This step filters out data from all other times. Grouped aggregation calculation: The grouping dimension G that users care about is the channel, i.e., G = (channel_code). The aggregation engine performs a GroupBy operation on D' to generate multiple groups with channel_code as the key, for example: Key: 360_search->S_360 (all incoming records from 360 Search); Key: tencent->S_tencent; Key:baidu_infoflow->S_baidu; ... For each group S j The engine calculates the aggregated metrics M(S) that users care about. j ): Incoming line quantity: COUNT(S_360), assumed to be 1500; Incoming line percentage: RATIO(S_360) = 1500 / |D'| 100%; Bubble rate over 1 hour: BUBBLE_1H_RATE(S_360) = Count(records where bubble_1h=1) / Count(records where is_in_class=1) 100%, this will use the bubble_1h field that has already been populated in T_effect; Samsung's cumulative ROI: STAR3_TOTAL_ROI(S) j ) = SUM({d.star3_total_amount | d ∈S j}) / SUM({d.cost | d ∈ S j This section will use incoming charges and order amounts.
[0037] When calculating these ratios, the aggregation engine calls the Safe_Divide function. For example, if the total consumption of a channel is 0, the denominator is zero when calculating ROI, and the ROI result is displayed as "--" instead of an error or displaying infinity, thus ensuring the robustness of the report. Apply special processing logic: Division-to-zero protection: As mentioned above, all division operations are protected. Dynamic time window truncation: For metrics such as "Samsung 30-day ROI", if the query date is less than 30 days from the time some users entered the network, the algorithm will dynamically adjust the statistical cutoff time for these users to CURRENT_TIMESTAMP, only calculating the amount that has already occurred, ensuring the real-time nature and fairness of the metric.
[0038] Incremental aggregation optimization: Suppose a user first views the data for "the whole week last Monday", and then changes the filter to "last Tuesday to this Monday" a few minutes later. The engine finds that the new time interval has a large overlap with the cached results for "the whole week last Monday" (last Tuesday to last Sunday). Therefore, instead of recalculating the entire new interval, it only calculates the incremental part (the data for this Monday), subtracts the decremented part (the data for last Monday) from the full result, and then merges the result with the cached result. This greatly improves the query response speed.
[0039] Sorting and Output: After all the group metrics have been calculated, the engine sorts the group results according to the user-specified "descending order by incoming call volume". After sorting, a structured JSON data response is generated and sent to the front end. After receiving the data, the front end renders statistical reports and visualization charts (such as bar charts to show the incoming call volume of each channel and line charts to show the ROI trend). Users can see at a glance which channel has the largest volume, which channel has the most active users (high bubbling rate), and which channel has the best return on investment (high ROI), thus making scientific budget allocation decisions.
[0040] Extended Implementation Example: Filtering and aggregation based on "whether it is a duplicate within 6 months": This embodiment further explains the implementation logic of the specific business indicator "whether it is repeated within 6 months", which involves a self-join query optimization technique.
[0041] The T_incoming entity already contains incoming_time and union_id. To determine whether an incoming line record is repeated within 6 months prior to the current date, that is, whether there is another incoming line record with the same union_id whose incoming_time is between the current record's incoming_time and incoming_time - 6 months; In constructing the association mapping model described above, the calculation of this field is performed through an offline or real-time data task. The specific algorithm is as follows: For each newly inserted record r_new in the T_incoming table (whose incoming_time is T); Using r_new.union_id as the key, query the set H of all historical records in the T_incoming table whose incoming_time falls within the range [T - 6 months, T). If H is not empty, set the is_repeat_6m field of r_new to 1 (yes); otherwise set it to 0 (no).
[0042] In dynamic aggregation algorithms, this field is very useful as a filtering parameter p_repeat. For example, marketers can set the filtering condition is_repeat_6m = 0 to analyze the quality and conversion effect of "first-time users". This can usually more accurately measure the true performance of new customer acquisition channels. This seemingly simple filtering relies on the association mapping model we have built to support efficient backtracking queries on historical data of the same entity. Aggregation based on the concept of "shift": "Shift" and "whether the shift is completed" are business concepts, but in terms of technical implementation, they are a further extension of T_incoming and T_effect. Assuming there is a separate T_class table that records the shift schedule (shift ID, start time, end time) for each salesperson or project team, when an incoming line record is generated, it can be associated with the T_class table based on its incoming_time and staff_wechat_id (or project_id) to determine which shift class_code it belongs to, and write the class_code and is_in_class (whether it belongs to a valid shift, such as whether the incoming time is within the shift time window) fields into T_incoming; The calculation of subsequent indicators, such as "Star3 Class Activation Amount upon Completion of Shift", is implemented in the payment behavior association. When an order is generated, it is associated with its corresponding incoming line record r. Then, the T_class table is queried according to r.class_code to obtain the end time class_end_time of the shift. If the order_time of the order is less than or equal to class_end_time, the amount is added to the star3_class_amount field. If the order_time of the order is greater than class_end_time, it is classified into other indicators. This design makes aggregation analysis extremely flexible. Marketers can group by class_code to compare the conversion efficiency of different shifts; they can also filter by is_class_finished = 1 to analyze only the completion ROI of users who have completed their shifts, ensuring data consistency.
[0043] Example 2: Figure 2 This is a system schematic diagram illustrating an analysis and tracking device for user call data of multi-channel advertising delivery, according to another exemplary embodiment. The device includes: Ledger Construction Module 1: This module is used to adapt and access the raw incoming data of at least one advertising channel through multiple preset heterogeneous data interfaces; for each raw incoming data, it performs data format unification cleaning based on a preset field mapping table, filters dirty data, and generates standardized incoming data records with a unified data structure; it stores the standardized incoming data records in the user incoming data details ledger, and generates or associates a unique user identifier for each record, thereby constructing a standardized dataset that supports accurate querying and batch export based on the unique user identifier; Association mapping model execution module 2: used to obtain user post-event behavior data corresponding to the standardized incoming line record based on the standardized dataset; construct an association mapping model, which includes at least: associating and binding the user post-event behavior data with the standardized incoming line record based on the unique user identifier; and associating the standardized incoming line record with delivery channel information; thereby generating an extended dataset containing the full-link data association relationship of delivery-incoming line-post-event. Data aggregation module 3: Based on the generated extended dataset, it receives one or more filtering parameters from the user interface to form a filtering parameter set; performs conditional filtering on the extended dataset based on the filtering parameter set to generate a filtered data subset; performs group aggregation calculation on the filtered data subset according to a preset grouping dimension to generate an aggregated statistical report that includes at least the incoming line volume, the incoming line ratio, and the core after-effect indicators calculated based on after-effect behavior data.
[0044] It is understood that the same or similar parts in the above embodiments can be referred to each other, and the contents not described in detail in some embodiments can be referred to the same or similar contents in other embodiments.
[0045] It should be noted that in the description of this invention, the terms "first," "second," etc., are used for descriptive purposes only and should not be construed as indicating or implying relative importance. Furthermore, in the description of this invention, unless otherwise stated, "a plurality of" means at least two.
[0046] Any process or method description in the flowchart or otherwise herein can be understood as representing a module, segment, or portion of code comprising one or more executable instructions for implementing a particular logical function or process, and the scope of the preferred embodiments of the invention includes additional implementations in which functions may be performed not in the order shown or discussed, including substantially simultaneously or in reverse order depending on the functions involved, as will be understood by those skilled in the art to which embodiments of the invention pertain.
[0047] It should be understood that various parts of the present invention can be implemented in hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented in software or firmware stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, it can be implemented using any one or a combination of the following techniques known in the art: discrete logic circuits having logic gates for implementing logical functions on data signals, application-specific integrated circuits (ASICs) having suitable combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), etc.
[0048] Those skilled in the art will understand that all or part of the steps of the methods in the above embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, the program includes one or a combination of the steps of the method embodiments.
[0049] Furthermore, the functional units in the various embodiments of the present invention can be integrated into a processing module, or each unit can exist physically separately, or two or more units can be integrated into a module. The integrated module can be implemented in hardware or as a software functional module. If the integrated module is implemented as a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium.
[0050] The storage media mentioned above can be read-only memory, disk, or optical disk, etc.
[0051] In the description of this specification, references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of the invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.
[0052] Although embodiments of the present invention have been shown and described above, it is understood that the above embodiments are exemplary and should not be construed as limiting the present invention. Those skilled in the art can make changes, modifications, substitutions and variations to the above embodiments within the scope of the present invention.
Claims
1. A method for analyzing and tracking user inbound data from multi-channel advertising, characterized in that, The method includes: Construct a standardized user call detail ledger: Through multiple preset heterogeneous data interfaces, adapt and access the original call data of at least one advertising channel; for each original call data, perform data format unification cleaning based on a preset field mapping table, filter dirty data, and generate standardized call records with a unified data structure; store the standardized call records in the user call detail ledger, and generate or associate a unique user identifier for each record, thereby constructing a standardized dataset that supports accurate querying and batch export based on the unique user identifier; Construct and execute an association mapping model between incoming and subsequent data: Based on the standardized dataset, obtain user subsequent behavior data corresponding to the standardized incoming record; construct an association mapping model, which includes at least: associating and binding the user subsequent behavior data with the standardized incoming record based on the unique user identifier; and associating the standardized incoming record with delivery channel information; thereby generating an extended dataset containing the full-link data association relationship of delivery-incoming-subsequent data; Execute a dynamic data aggregation algorithm based on a time window: Based on the generated extended dataset, receive one or more filtering parameters from the user interface to form a filtering parameter set; perform conditional filtering on the extended dataset based on the filtering parameter set to generate a filtered data subset; perform group aggregation calculation on the filtered data subset according to a preset grouping dimension to generate an aggregated statistical report that includes at least the incoming line volume, the incoming line percentage, and the core after-effect indicators calculated based on after-effect behavioral data.
2. The method according to claim 1, characterized in that, The data format unification and cleaning based on the preset field mapping table includes: Obtain first raw incoming data from the first advertising delivery channel, wherein the first raw incoming data includes a first channel field that adopts the first field naming rule; Obtain second raw incoming data from the second advertising delivery channel, the second raw incoming data containing the second channel field using the second field naming rule; Call the pre-configured field mapping table, which defines the conversion rules between the original fields of different advertising channels and the standardized ledger fields; According to the field mapping table, the first channel field is mapped to a first standardized field, and the second channel field is mapped to a second standardized field, wherein the first standardized field and the second standardized field have the same field identifier and data type, thereby achieving unified format of multi-channel data.
3. The method according to claim 1, characterized in that, The construction of the association mapping model includes: Define and instantiate core data entities: instantiate at least one incoming data entity and one subsequent data entity; the incoming data entity includes a unique identifier for the incoming record, the unique user identifier, the incoming time, and the incoming channel code as key fields; the subsequent data entity includes a unique identifier for the subsequent record, the unique user identifier, the associated unique identifier for the incoming record, and at least one subsequent indicator field; Generate and bind a unique user identifier: In response to a user entering the landing page through any advertising channel, generate an anonymous user identifier; in response to the same user completing the operation of adding WeChat or joining a group, obtain a unique social platform identifier through a third-party platform callback interface; establish a mapping relationship between the anonymous user identifier and the unique social platform identifier, and use the unique social platform identifier as the unique user identifier throughout the entire link; The process involves associating incoming call data with advertising channel information: When a user enters the landing page, the channel linking parameters are parsed from the Uniform Resource Locator (URL) of the landing page; the channel code, promotion account identifier, project identifier, and ad identifier are extracted based on the channel linking parameters; when generating the standardized incoming call record, the channel code, promotion account identifier, project identifier, and ad identifier are stored as components of the record, thus completing the logical association between incoming call data and advertising channel information. Real-time collection and association of post-event data: A user's post-event behavior events are monitored in real time through an event listener; when a post-event behavior event is detected, the user's unique identifier and occurrence time associated with the event are obtained; the incoming line data entity is queried based on the user's unique identifier to determine the target incoming line record most relevant to the post-event behavior; based on the incoming line record's unique identifier and incoming line time, the corresponding post-event indicator field in the post-event data entity is calculated and updated, thereby completing the association and binding.
4. The method according to claim 1, characterized in that, The user post-event behavior data includes at least user pop-up behavior and user deletion behavior; The step of associating and binding the user's subsequent behavior data with the standardized incoming call record based on the unique user identifier includes: Retrieve the target call record, which includes the call record unique identifier, user unique identifier, call time, and corresponding sales identifier; A first time window and a second time window are set, wherein the first time window starts at the incoming time and lasts for a first preset duration, and the second time window starts at the incoming time and lasts for a second preset duration, and the second preset duration is longer than the first preset duration; In response to detecting a bubbling event initiated by the user's unique identifier to the sales identifier within the first time window, the value of the first bubbling flag field corresponding to the unique identifier of the incoming line record in the subsequent data entity is updated to "Yes"; In response to the detection of the bubbling behavior event within the second time window but not within the first time window, the value of the second bubbling flag field in the subsequent data entity is updated to "Yes"; In response to a deletion behavior event detected within the first time window where the user's unique identifier deletes the friend relationship corresponding to the sales identifier, the values of the first deletion flag field and the deletion time field in the subsequent data entity are updated to "Yes" and the current timestamp, respectively.
5. The method according to claim 1, characterized in that, The dynamic data aggregation algorithm includes: Conditional filtering stage: Receive the set of filtering parameters P = {p1, p2, …, p n }, where each parameter p i It includes field names, operators, and values; for each data d in the extended dataset D, it is determined whether it meets all the conditions in the filtering parameter set P, and all data d that meet the conditions are combined into a filtered data subset D'; Grouping and aggregation phase: Receives a set of grouping dimensions G = (g1, g2, …, g m ); Based on G, perform a grouping operation on the filtered data subset D' to generate at least one grouping key G_key j and its corresponding data subset S j For each data subset S j Calculate a set of predefined aggregation metrics M(S) j The aggregated metric M includes at least a count-based incoming volume metric, a division-based incoming volume percentage metric, and a ratio metric based on the aftereffect metric field. Sorting Output Stage: Receives a sorting field and a sorting direction; sorts all groups and their corresponding aggregation index results according to the sorting field and sorting direction; outputs a sorted aggregation result report.
6. The method according to claim 5, characterized in that, The calculation of a set of predefined aggregation indices M(S) j It should include at least one of the following ratio metrics with divide-by-zero protection: Calculate the percentage of incoming data: Divide the number of data entries in the group by the total number of data entries in the filtered subset, and then multiply by 100%. Calculate the one-hour bubble rate: Divide the number of data entries in the group that are marked as "yes" for one hour bubble rate by the number of data entries in the group that are marked as "yes" for whether they are within a shift, and then multiply by 100%. Calculate the cost of incoming data: Divide the sum of the consumption amounts of all records in the group by the number of records in the group; To calculate Samsung's cumulative return on investment: divide the sum of the cumulative activation amount of Samsung in all records in the group by the sum of the consumption amount of all records in the group; Any division operation that might result in a divisor of zero is handled by a safe division function. This function returns a predefined non-numeric identifier when the denominator is zero, instead of performing the division operation or throwing an exception.
7. The method according to claim 6, characterized in that, The dynamic data aggregation algorithm also includes an incremental aggregation optimization logic: Receive a new query request containing the first time range; Determine if there is a cached result of the previous query, and if the second time range of the previous query overlaps with the first time range. When there is overlap, calculate the incremental time interval ΔT between the first time range and the second time range; Only incremental data ΔD within the incremental time interval ΔT is selected from the extended dataset D; Perform grouped aggregation calculations on the incremental data ΔD to obtain the incremental aggregation result ΔR; The cached previous query result R_cache is merged with the incremental aggregation result ΔR to generate the final new query result R_new, thereby avoiding re-aggregation and calculation of the full data.
8. The method according to claim 7, characterized in that, The dynamic data aggregation algorithm also includes a dynamic time window truncation logic: When calculating cumulative metrics related to user payments, obtain the system timestamp at the time of the current query execution and record it as the current system time; For each incoming line record d, query all user order data associated with it; When calculating the cumulative activation amount, only the order amount of orders whose order time is less than or equal to the current system time is aggregated; When calculating the activation amount for a specific period, the statistical cutoff time is dynamically calculated based on the incoming time of incoming line record d and the preset period offset. Only the order amount for incoming line time less than or equal to the order time and order time less than or equal to the statistical cutoff time is summarized. If the current system time is less than the statistical cutoff time, the current system time is used as the actual statistical cutoff time.
9. A device for analyzing and tracking user inbound data from multi-channel advertising, characterized in that, The apparatus is used to implement the method according to any one of claims 1-8, the apparatus comprising: Ledger Construction Module: This module is used to adapt and access the raw incoming data of at least one advertising channel through multiple preset heterogeneous data interfaces; for each raw incoming data, it performs data format unification cleaning based on a preset field mapping table, filters dirty data, and generates standardized incoming data records with a unified data structure; it stores the standardized incoming data records in the user incoming data details ledger, and generates or associates a unique user identifier for each record, thereby constructing a standardized dataset that supports accurate querying and batch export based on the unique user identifier; The association mapping model execution module is used to obtain user post-event behavior data corresponding to the standardized incoming line record based on the standardized dataset; construct an association mapping model, which includes at least: associating and binding the user post-event behavior data with the standardized incoming line record based on the unique user identifier; and associating the standardized incoming line record with delivery channel information; thereby generating an extended dataset containing the full-link data association relationship of delivery-incoming line-post-event. Data aggregation module: Based on the generated extended dataset, it receives one or more filtering parameters from the user interface to form a filtering parameter set; performs conditional filtering on the extended dataset based on the filtering parameter set to generate a filtered data subset; performs group aggregation calculation on the filtered data subset according to a preset grouping dimension to generate an aggregated statistical report that includes at least the incoming line volume, the incoming line percentage, and the core aftereffect indicators calculated based on aftereffect behavior data.
