A Multidimensional Analysis Method and System for E-commerce Financial Data

By automatically matching financial data perspectives, isolating store-level data, implementing multi-dimensional cascading filtering, and detecting duplicate data in Excel, this system solves the problems of cumbersome perspective switching, insufficient data isolation, low filtering efficiency, and data redundancy in e-commerce financial data analysis systems, thus achieving efficient and secure data analysis.

CN123118283APending Publication Date: 2026-11-17SHENZHEN BIFANG YUEHAI TECHNOLOGY CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202611324156.4
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2026-08-28
Publication Date
2026-11-17

AI Technical Summary

Technical Problem

Existing e-commerce financial data analysis systems suffer from several problems: cumbersome user perspective switching leading to data misinterpretation; lack of store-level data isolation mechanisms resulting in data security risks; lack of linkage between filtering dimensions leading to low efficiency; and lack of duplicate detection mechanisms when importing Excel data, resulting in data redundancy and statistical errors.

Method used

By automatically matching financial data perspectives, isolating store-level data, implementing multi-dimensional cascading filtering, and using a duplicate detection mechanism for imported Excel data, the system achieves automatic switching of data perspectives, isolating store-level data, implementing multi-level cascading filtering, and detecting duplicates in Excel data.

Benefits of technology

It improves the accuracy and efficiency of financial data analysis, ensures data security, avoids data misinterpretation and redundancy, and enhances user experience and system applicability.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN123118283A_ABST
    Figure CN123118283A_ABST
Patent Text Reader

Abstract

This invention relates to a method and system for multi-dimensional analysis of e-commerce financial data. The method includes: verifying user identity credentials to obtain user identifiers and role identifiers; automatically matching financial data perspectives based on role identifiers; querying the user's bound store permission list based on the user identifier, performing store-level data isolation filtering, and providing only data within the user's permission scope; establishing a multi-level cascading filtering mechanism to sequentially filter the platform, store, supplier, and category; dynamically aggregating and statistically analyzing the filtered data by dimension, and providing the aggregated statistical results to the user.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] This invention relates to the field of financial data analysis technology, specifically to a method and system for multidimensional analysis of e-commerce financial data. Background Technology

[0002] With the rapid development of the e-commerce industry, the number of e-commerce stores, platforms, and order data has grown rapidly, making the management and analysis of financial data a core aspect of business decision-making. E-commerce companies' financial profit data typically involves multiple platforms (such as Amazon, eBay, and Shopee), multiple stores, various product categories, multiple suppliers, and complex cost structures (including dozens of business fields such as transaction revenue, platform subsidies, shipping revenue, promotional discounts, platform fees, FBA fees, warehousing fees, and advertising fees). This places extremely high demands on the collection, cleaning, storage, and multi-dimensional analysis of financial data.

[0003] Existing e-commerce financial data analysis systems have the following shortcomings:

[0004] First, the finance and operations departments of e-commerce companies use different methods to calculate profits. For example, the finance department typically uses DDP1 (first landed price) and the corresponding gross profit 1 for accounting, while the operations department uses DDP2 (second landed price) and the corresponding gross profit 2. In existing e-commerce financial data analysis systems, users need to manually switch data perspectives to view profit data from different perspectives, which is cumbersome and prone to data misinterpretation due to forgetting to switch.

[0005] Second, there is a lack of store-level data isolation mechanisms. Existing e-commerce financial data analysis systems typically employ function-based access control (i.e., controlling whether users can access a certain function page), but they fail to implement store-level data access isolation. Different operations personnel are responsible for different stores; without data isolation, these personnel could view store financial data outside their authorized scope, posing a data security risk.

[0006] Third, the filtering dimensions lack synergy. Current financial reporting systems typically use independent filtering criteria, failing to achieve cascading linkages between platforms, stores, suppliers, categories, and product names. For example, when a user selects a platform, the store list still displays stores from all platforms; similarly, after selecting certain stores, the supplier list still displays all suppliers, resulting in inefficient filtering and a high likelihood of generating invalid queries.

[0007] Fourth, the Excel data import lacks a duplicate detection mechanism. E-commerce companies typically import financial profit data in batches via Excel files. The existing system cannot detect existing entry dates during import, resulting in duplicate imports of data from the same month, causing data redundancy and statistical errors. Summary of the Invention

[0008] To address the aforementioned issues, this invention provides a method and system for multidimensional analysis of e-commerce financial data, aiming to achieve automatic switching of financial data perspectives, store-level data isolation, multidimensional cascading filtering, and duplicate detection of imported Excel data, thereby improving data analysis efficiency and data security.

[0009] This invention provides a method for multidimensional analysis of e-commerce financial data, including:

[0010] Step S101: Verify the user's identity credentials and obtain the user identifier and role identifier;

[0011] Step S102: Automatically match the financial data perspective based on the role identifier;

[0012] Step S103: Based on the user identifier, query the list of store permissions bound to the user, perform store-level data isolation filtering, and only provide data within the scope of the user's permissions;

[0013] Step S104: Establish a multi-level cascading filtering mechanism to perform multi-level cascading filtering on the provided data about the bound stores;

[0014] Step S105: Perform dynamic aggregation statistics on the filtered data by dimension, and provide the aggregation statistics results to the user.

[0015] The method further includes:

[0016] Step S201: The server receives the Excel file uploaded by the client, parses the worksheet data, and maps it to database fields according to the preset column name mapping rules;

[0017] Step S202: Verify the entry date and the existence of the store platform combination on the mapped data;

[0018] Step S203: Extract the set of unique posting dates and query the database to see if a record with the same date already exists;

[0019] Step S204: If a duplicate entry date is detected, cache the data in the memory buffer, generate a one-time token, and return a warning message;

[0020] Step S205: When the server receives the confirmation import request, it retrieves the cached data based on the token, inserts it into the database in batches, and then deletes the cached data corresponding to the token.

[0021] This invention also provides a multi-dimensional analysis system for e-commerce financial data, comprising:

[0022] The authentication module is used to verify user identity credentials and obtain user identifiers and role identifiers;

[0023] The perspective adaptation module is used to automatically match the perspective of financial data based on the role identifier and pass the perspective matching result to the aggregation statistics module.

[0024] The store isolation module is used to query the list of store permissions bound to a user based on the user identifier, and to perform store-level data isolation and filtering, providing only data within the scope of the user's permissions.

[0025] The cascading filtering module is used to establish a multi-level cascading filtering mechanism to perform multi-level cascading filtering on the provided data about the bound stores;

[0026] The aggregation and statistics module is used to dynamically aggregate and statistically analyze the filtered data by dimension, and provide the aggregation and statistics results to the user.

[0027] The system further includes: a data import module for importing received Excel data and performing duplicate detection, which includes:

[0028] The parsing sub-unit is used to receive the uploaded Excel file, parse the worksheet data, and map it to database fields according to the preset column name mapping rules;

[0029] The verification subunit is used to perform two verifications on the mapped data: (1) verification of the non-empty entry date, filtering out records with empty entry dates and rejecting imports; (2) verification of the existence of the store platform combination, querying the store management table to verify whether the store and platform combination in the Excel data has been registered.

[0030] The duplicate detection subunit is used to extract the set of non-duplicate entry dates from the mapped data and query whether a record with the same date already exists in the financial profit data table;

[0031] The cache sub-unit is used to encapsulate the mapped data row array, the list of duplicate dates, and the creation timestamp into a data object to be imported when duplicate entry dates are detected. It stores the data in a globally shared Map structure with the UUID token as the key, sets an expiration time, automatically cleans up expired entries when writing, and checks whether they have expired when reading.

[0032] The import confirmation sub-unit is used to receive import confirmation requests, retrieve cached data based on the token, generate batch IDs, insert the data into the financial_profit table in batches, and delete the cached data of the corresponding token after successful insertion.

[0033] Beneficial effects:

[0034] This invention provides a method and system for multidimensional analysis of e-commerce financial data, which has the following features:

[0035] (1) By automatically matching the data perspective through role identification, the operation of manually switching perspectives by users is eliminated, avoiding data misreading caused by forgetting to switch perspectives, and improving the accuracy and efficiency of financial data analysis.

[0036] (2) By filtering data at the store level, it is ensured that different operators can only view store data within their authorized scope, while users with full store viewing permissions are not restricted. This achieves fine-grained data permission control and improves data security.

[0037] (3) Through the dynamic computer mechanism of multi-level cascading filtering, the filtering options at each level only include the option values ​​of the data that actually exist under the filtering conditions of the upper level, which avoids the display of invalid options and the generation of invalid queries, and improves the filtering efficiency.

[0038] (4) By using the duplicate detection and confirmation import mechanism of Excel data import, the data is cached and the user is prompted to confirm when duplicate entry dates are detected, which avoids redundancy and statistical errors caused by duplicate data import. At the same time, the expiration mechanism of the memory buffer prevents data residue.

[0039] (5) By executing multiple sets of aggregate queries for the current period, the same period last year, and the month-on-month period in parallel, the data loading time is significantly shortened and the user experience is improved.

[0040] (6) By using a dual-mode authentication mechanism (Cookie + Bearer token), the system supports the authentication requirements of both web browsers and client applications (such as mini-programs), thereby improving the applicability and security of the system. Attached Figure Description

[0041] Figure 1 A flowchart of the multidimensional analysis method for e-commerce financial data provided by the present invention;

[0042] Figure 2 The flowchart of the Excel data import and duplicate detection method provided by the present invention;

[0043] Figure 3 The present invention provides a flowchart of a method for performing multi-level cascading filtering on data related to bound stores;

[0044] Figure 4 The present invention provides a flowchart of a method for dynamically aggregating and statistically analyzing filtered data by dimension and providing the aggregation and statistical results to the user;

[0045] Figure 5 The structural block diagram of the e-commerce financial data multidimensional analysis system provided by the present invention;

[0046] Figure 6 A structural block diagram of the data import module provided by this invention;

[0047] Figure 7 This is a structural block diagram of the cascaded screening module provided by the present invention;

[0048] Figure 8 This is a structural block diagram of the aggregation statistics module provided by the present invention. Detailed Implementation

[0049] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention.

[0050] Example 1

[0051] Figure 1 The implementation flow of the multi-dimensional analysis method for e-commerce financial data provided in Embodiment 1 of the present invention is illustrated.

[0052] The multidimensional analysis method for e-commerce financial data includes:

[0053] Step S101: Verify the user's identity credentials and obtain the user identifier and role identifier.

[0054] The user sends an authentication request to the server through the client, and the authentication request information carries the user's identity credentials. The user identity credentials verification method supports dual-mode authentication:

[0055] The first authentication mode is web-based authentication. When a user accesses the e-commerce financial data multidimensional analysis software system through a web browser, the server generates a JWT (JSON Web Token) authentication token after successful login and stores this token as an httpOnly cookie in the browser. On subsequent requests, the server reads a cookie value named auth_token from the HTTP request's cookie field as the authentication token. The JWT token is signed using the HS256 algorithm and contains three declaration fields: user ID (userId), username (username), and role ID (roleId), with a 24-hour expiration time. The server verifies the JWT signature using a pre-configured key, and extracts the user ID and role ID upon successful verification.

[0056] The second authentication mode is client application authentication. When a user accesses the e-commerce financial data multidimensional analysis software system through a client application (such as a WeChat mini-program), the client obtains a JWT token after successful login and stores it in local storage. In subsequent requests, the client includes the Bearer token in the Authorization header field of the HTTP request. <token>The token is in a specific format. The server extracts the Bearer token from the Authorization header field and performs the same signature verification and expiration time check.

[0057] The server first checks the authentication token in the cookie. If no valid token is found in the cookie, it falls back to check the Bearer token in the Authorization header field. This dual-mode authentication mechanism allows the same backend interface to serve both web browsers and client applications simultaneously.

[0058] In addition, the server intercepts all requests for non-public routes at the routing middleware layer. For requests without an authentication token, if it's an API route, a 401 status code JSON error message is returned; if it's a page route, the user is redirected to the login page. For requests with invalid or expired tokens, a 401 status code is also returned, or the user is redirected to the login page, and invalid cookies are cleared.

[0059] The above password encryption uses the PBKDF2 algorithm combined with SHA-512 hash and random salt value, with 10,000 iterations.

[0060] Step S102: Automatically match the financial data perspective based on the role identifier.

[0061] After obtaining the user's identifier, the server queries the user's role identifier from the pre-established user table. The user table (users) stores fields such as user identifier, username, password hash value, display name, status, and role identifier. The server performs a join query with the role table (roles) using the role identifier to obtain the role name corresponding to the role identifier.

[0062] Automatically match financial data perspectives based on role name:

[0063] When the role name is finance, the data perspective is set to first-person perspective (financial perspective). In the first-person perspective, profit calculation uses the first set of data fields, which contains the DDP1 amount field (ddp1_amount) and the gross profit 1 field (gross_profit_1).

[0064] Specifically, when calculating the total expenditure, the DDP1 amount is included as one of the cost items in the calculation:

[0065] Total expenses = Platform fee + Shipping fee refund + Last-mile delivery fee + Principal and taxes of the evaluation + DDP1 amount + Warehousing fee + Order processing fee + Store rent + Advertising fee + Other expenses + Evaluation commission.

[0066] When the role name is Operations, the data perspective is set to the second perspective (Operations perspective). In the second perspective, profit calculation uses the second set of data fields, which includes the DDP2 amount field (ddp2_amount) and the gross profit 2 field (gross_profit_2).

[0067] Specifically, when calculating the total expenditure, the DDP2 amount is included as one of the cost items in the calculation:

[0068] Total expenses = Platform fee + Shipping fee refund + Last-mile delivery fee + Principal and taxes of the evaluation + DDP2 amount + Warehousing fee + Order processing fee + Store rent + Advertising fee + Other expenses + Evaluation commission.

[0069] The last-mile fee is calculated as follows: shipping label fee + return label fee + warehouse processing fee + last-mile fee.

[0070] The total revenue is calculated as follows:

[0071] Total revenue = Transaction revenue + Shipping revenue + Platform subsidies - Promotional discounts - After-sales refunds.

[0072] Net profit = Total revenue - Total expenses;

[0073] Profit margin = Net profit / Total revenue × 100%.

[0074] The perspective matching process is entirely automated by the server based on role identification; users do not need to manually switch perspectives, and no perspective switching buttons are displayed on any page. Upon login, the system automatically identifies the user's role and applies the corresponding financial data perspective. All data query interfaces (including statistical reports and financial dashboards) select the corresponding set of data fields based on the perspective.

[0075] Step S103: Based on the user identifier, query the list of store permissions bound to the user, perform store-level data isolation filtering, and only provide data within the scope of the user's permissions.

[0076] The e-commerce financial data multidimensional analysis software system pre-establishes and maintains a user-shop association table (user_shops), which stores the correspondence between user identifiers and shop names. After obtaining the user identifier, the following steps are performed:

[0077] First, query the user table (users) and retrieve the user's role name using a JOIN query. If the role name is admin (administrator), then the user is marked as an administrator, and no store filtering conditions are applied. The administrator can view the data of all stores.

[0078] Secondly, if the role name is not admin, then query the association table between users and shops (user_shops) to get the list of shop names bound to that user.

[0079] Then, construct filtering conditions based on the query results:

[0080] (a) If the user is an administrator or has full access to all shops, return {isAdmin: true,shops: null}, indicating that no shop filtering is applied;

[0081] (b) If the user is not an administrator and has bound at least one store, return {isAdmin: false,shops: [store1, store2, ...]}, and append the condition shop_name IN (store1, store2, ...) to the data query to provide the user with all data about the bound stores, including financial profit data tables, etc.

[0082] (c) If the user is not an administrator and has not bound any shops, return {isAdmin: false, shops:[]}, and attach a condition that is always false (such as 1=0) to the data query to make the query result an empty set, ensuring that unauthorized users cannot view any data.

[0083] The store filtering conditions can be applied in two ways:

[0084] The first method is the ORM query builder approach. For queries built using ORM (Object Relational Mapping), you can add IN conditions by calling the query builder's .in(shopColumn, shops) method, or add the .eq('no_access_', '__never__') condition if permissions are not available, returning an empty set.

[0085] The second method is the native SQL concatenation method. For queries executed using database functions (RPC), an SQLWHERE clause string is constructed. When store permissions are available, `shop_name IN ('store1', 'store2', ...)` is generated, where single quotes in the store names are escaped into two consecutive single quotes to prevent SQL injection; when permissions are not available, 1=0 is generated.

[0086] The store filtering conditions are applied to both paginated data queries and summary data queries to ensure that the data rows seen by users in the table have the same data range as the total rows at the bottom.

[0087] When a user manually selects a specific store for filtering on the display interface, the server takes the intersection of the user's selected store list and the user's permission list, and only returns the data within the intersection range to prevent the user from accessing unauthorized store data by manually passing parameters.

[0088] Step S104: Establish a multi-level cascading filtering mechanism to perform multi-level cascading filtering on the provided data about the bound stores.

[0089] The multi-level cascading screening mechanism includes five levels: platform screening layer, store screening layer, supplier screening layer, category screening layer, and product name screening layer.

[0090] The steps for performing multi-level cascading filtering on the provided data regarding the bound stores using a multi-level cascading filtering mechanism are as follows:

[0091] (1) Use the platform filtering layer to query a list of unique platform names from the financial profit data table, and select a platform from the list of platform names.

[0092] After a user selects a platform, the server receives the relevant parameters for that platform, calls the dashboard initialization interface to query the list of stores on that platform, and triggers the next level of data updates.

[0093] (2) Use the store filter layer to filter the record data of the selected platform from the financial profit data table, extract the list of unique store names, and select one or more stores from the store name list.

[0094] After a user selects one or more stores, the server receives a list of store parameters from the selected platform and triggers a data update at the next level.

[0095] (3) Utilize the supplier filtering layer to filter suppliers used by one or more selected stores from the financial profit data table. Specifically, call the first database function fn_distinct_suppliers to query a list of unique suppliers used within the selected platform and store range. The parameters of the first database function include platform parameters (p_platform) and store parameters (p_shops). Internally, the function uses a SELECT DISTINCT statement to query record data from the financial profit data table that meets the selected platform and store conditions, extracts unique supplier field values, and returns a supplier list. The returned supplier list only includes suppliers whose data actually exists under the current filtering conditions.

[0096] After a user selects a platform, store, and supplier, the server receives the platform parameters of the selected platform, the store parameter list of the selected store, and the supplier parameter list of the selected supplier, triggering the next level of data updates.

[0097] (4) Use the category filtering layer to filter the product categories supplied by the selected suppliers from the financial profit data table. Specifically, call the second database function fn_distinct_categories to query a list of unique categories within the selected platform, store, and supplier range. This category list records the product categories supplied by the suppliers. The parameters of the database function include platform parameters (p_platform), store parameters (p_shops), and supplier parameters (p_suppliers). Internally, the function uses the SELECT DISTINCT statement to query the record data in the financial profit data table that meets the conditions of the selected platform, store, and supplier, extracts the unique category field values, and returns the product category list.

[0098] (5) The product name filtering layer is used to search for the desired product in the product name field of the financial profit data table by inputting product name keywords or full names. Specifically, the user manually inputs product name keywords or full names, and the server searches for the desired product in the product name field of the financial profit data table using ILIKE fuzzy matching.

[0099] The core feature of using a multi-level cascading filtering mechanism for financial data is that the filtering option list at each level only includes option values ​​for data that actually exists under all filtering conditions preceding that level. When the filtering conditions at the previous level change (e.g., a user switches platforms), the option sets for all lower-level filtering layers are automatically recalculated to ensure that the option list always remains consistent with the actual data under the current filtering conditions.

[0100] Step S105: Perform dynamic aggregation statistics on the filtered data by dimension, and provide the aggregation statistics results to the user.

[0101] Dynamic aggregation statistics are performed based on the current filtering criteria (platform, store, supplier, category, product name, date range, etc.) and store permission filtering conditions. The specific process is as follows:

[0102] First, the server calculates the year-on-year and month-on-month date ranges based on the current date range. The year-on-year date range is the same time period of the same period last year, and the month-on-month date range is the same time period of the previous period.

[0103] Then, the server initiates three sets of data queries simultaneously using a parallel execution method: the current period query group, the year-on-year period query group, and the month-on-month period query group.

[0104] Specifically, the Promise.all mechanism is used to initiate three sets of asynchronous queries simultaneously, each set of queries containing the following subqueries:

[0105] (a) Metric aggregation query: Call the database function fn_dashboard_metrics, and pass in the start date (p_start_date), end date (p_end_date), store list (p_shops), platform (p_platform), supplier list (p_suppliers), category list (p_categories), and product name (p_product_name) parameters. It returns a single result object containing aggregated metrics such as sales quantity, transaction revenue, platform subsidies, shipping revenue, promotional discounts, platform fees, FBA fees, DDP1 amount, DDP2 amount, warehousing fees, advertising fees, after-sales refunds, and last-mile delivery fees.

[0106] (b) Trend data query: Call the database function fn_dashboard_trend to return a list of trend data grouped by date, which can be used to draw trend charts.

[0107] (c) Platform distribution query: Call the database function fn_dashboard_platform to return aggregated data grouped by platform, which is used to display the distribution of operating indicators of each platform.

[0108] (d) Product ranking query: Call the database function fn_dashboard_top_products, pass in the limit parameter (p_limit), and return the top N products in sales and their aggregate indicators. For example, if p_limit = 10, return the top 10 products in sales and their aggregate indicators.

[0109] (e) Store ranking query: Call the database function fn_dashboard_top_shops to return the top N stores in sales ranking and their aggregate metrics.

[0110] (f) Ranking query of loss-making products: Call the database function fn_dashboard_top_loss_products to return the top N products with the largest loss amount, which is used for risk warning.

[0111] (g) Category ranking query: Call the database function fn_dashboard_top_categories to return the aggregated data ranking grouped by category.

[0112] (h) After-sales order count query: Call the database function fn_dashboard_aftersale_count to return the number of after-sales orders.

[0113] The eight subqueries mentioned above are executed in parallel within the current period query group. The year-on-year period query group and the month-on-month period query group each contain an indicator aggregation query and an after-sales order count query. The three groups of queries are executed in parallel using Promise.all, resulting in a total of more than ten database function calls being initiated simultaneously.

[0114] After the query is completed, the server calculates the returned metric data from the user's perspective. For the current period's metric data, it calculates total revenue, total expenses, net profit, and profit margin:

[0115] Total revenue = Transaction revenue + Shipping revenue + Platform subsidies - Promotional discounts - After-sales refunds

[0116] Total Expenses = Platform Fee + Shipping Fee Refund + Last-Mile Delivery Fee + Principal of Reviews and Taxes + DDP Amount + Warehousing Fee + Order Processing Fee + Store Rent + Advertising Fee + Other Expenses + Review Commission

[0117] When the role's perspective is first-person (financial perspective), the DDP amount is DDP1; when the role's perspective is second-person (operations perspective), the DDP amount is DDP2.

[0118] Net profit = Total revenue - Total expenses;

[0119] Profit margin = Net profit / Total revenue × 100% (Profit margin is 0 when total revenue is 0).

[0120] The same calculation logic is used to calculate the respective indicator data for both year-on-year and month-on-month periods. Then, the rate of change for each indicator is calculated:

[0121] Year-on-year change rate = (Current period value - Year-on-year period value) / Year-on-year period value × 100%

[0122] Month-on-month change rate = (Current period value - Month-on-month period value) / Month-on-month period value × 100%

[0123] The server merges the current period data, year-on-year comparison data, and month-on-month comparison data into a complete aggregated statistical result, and provides the aggregated statistical result to the user. This includes returning the aggregated statistical result to the client for display, or providing the aggregated statistical result to the user through other means, such as sending the statistical result to the user's designated email address via email.

[0124] Example 2

[0125] Figure 2 The flowchart of the Excel data import and duplicate detection method provided in Embodiment 2 of the present invention is shown. The method includes:

[0126] Step S201: The server receives the Excel file uploaded by the client, parses the worksheet data, and maps it to database fields according to the preset column name mapping rules.

[0127] The server receives an Excel file (.xlsx or .xls format) uploaded by the client in FormData format. The server uses an Excel parsing library (such as SheetJS / xlsx) to read the binary data of the file, obtains the name of the first worksheet, and converts the worksheet data into a JSON object array.

[0128] The server has a pre-defined column name mapping table (COLUMN_MAP) that maps Chinese names in the Excel header to database field names. For example:

[0129] The server iterates through each row of the JSON object array, converting each header into a database field name using a mapping table. For numeric fields (such as sales quantity, transaction revenue, etc.), the value is converted to a numeric type; for empty values, they are uniformly set to null; for other fields, they are converted to string types.

[0130] Step S202: Verify the entry date and the existence of the store platform combination on the mapped data.

[0131] The server first filters out records where the accounting date (accounting_date) is empty or a blank string. If such a record exists, the import is rejected and an error message is returned, prompting the user to check if there are any empty values ​​in the "accounting date" column of the Excel file. Data with an empty accounting date is not allowed to be imported because the accounting date is a core dimension for data partitioning and querying.

[0132] The server also performs an existence check on the store-platform combination. It extracts unique "store + platform" combinations from the mapped data and checks if the corresponding store name and platform combination exist in the store management table (shops). If a store + platform combination exists in the Excel data but does not exist in the store management table, the import is rejected and an error message is returned, listing all non-existent store and platform combinations, prompting the user to add the corresponding store in the store management table before importing.

[0133] Step S203: Extract the set of unique posting dates and query the database to see if a record with the same date already exists.

[0134] The server extracts all posting date values ​​from the mapped data and constructs a unique set of posting dates. Then, it converts this set of dates into an array and queries the financial profit data table (financial_profit) to check if a record with the same date as any date in the date array already exists.

[0135] The specific query method is as follows: Use the database query builder to select the accounting_date field from the financial_profit table, add the IN condition accounting_date IN (date1, date2, ...), and limit the returned result to 1 (LIMIT 1) to quickly determine whether there are duplicates.

[0136] If the query result is not empty, it means that there are duplicate posting dates. Then, further query all matching duplicate dates to get a complete list of duplicate dates.

[0137] If the query result is empty, it means that there are no duplicate entry dates, and the corresponding data will be written directly to the database.

[0138] Step S204: If a duplicate entry date is detected, cache the data in the memory buffer, generate a one-time token, and return a warning message.

[0139] When a duplicate posting date is detected, the server performs the following actions:

[0140] (a) Generate a globally unique one-time token using the UUID (Universally Unique Identifier) ​​algorithm.

[0141] (b) The mapped array of data rows, the list of repeating dates, and the creation timestamp are encapsulated into a data object to be imported and stored in a global memory buffer. The memory buffer uses a globally shared Map data structure (ensured to be shared across all route processors via globalThis) to store the data to be imported using tokens as keys.

[0142] (c) Set the expiration time (TTL) to N minutes, for example, 10 minutes. The server automatically cleans up expired entries each time new data to be imported is written; when reading data to be imported, it checks whether the data has expired, and if it has expired, it deletes it and returns an empty value.

[0143] (d) Return a response to the client, which includes: a duplicate date identifier (hasDuplicates: true), a list of duplicate dates (duplicateDates), a one-time token (token), the total number of data rows (totalRows), and a preview of the first 5 rows of data (preview). Upon receiving the response, the client displays a duplicate date warning pop-up, showing the list of duplicate entry dates, and provides two operation options: "Confirm Import" and "Cancel Import".

[0144] Step S205: When the server receives the confirmation import request, it retrieves the cached data based on the token, inserts it into the database in batches, and then deletes the cached data corresponding to the token.

[0145] When the user clicks "Confirm Import," the client sends a confirmation import request to the server, containing a one-time token. The server then performs the following operations:

[0146] (a) Retrieve the data to be imported from the memory buffer based on the token. If the token does not exist or has expired (e.g., more than 10 minutes), return an error message prompting the user to re-upload.

[0147] (b) Generate a new batch ID (batch_id) using the UUID algorithm and append the batch ID to each data record for subsequent batch querying and management.

[0148] (c) Insert data into the database in batches of 500 records each. The server iterates through the data array, groups 500 records into a batch, and calls the database's batch insert interface to write the data into the financial_profit table. Batch insertion avoids request timeouts or URL length limitations caused by inserting too much data at once.

[0149] (d) After successful insertion, delete the data to be imported corresponding to the token from the memory buffer to prevent data residue and duplicate import.

[0150] (e) Return a success response, including a success flag, the total number of rows of data imported, and the batch ID.

[0151] Example 3

[0152] Figure 3 This invention illustrates a method for multi-level cascading filtering of data related to bound stores, as provided in Embodiment 3 of the present invention.

[0153] The multi-level cascaded screening method includes:

[0154] Step S301: Obtain the platform list and return it to the client.

[0155] The server retrieves initialization data from the e-commerce financial data multidimensional analysis software system via the dashboard initialization interface (dashboard / init), including the latest month and platform list. The platform list is obtained by querying the financial_profit table for unique values ​​in the platform field. The server returns the platform list to the client, which renders it as a single-select dropdown.

[0156] Step S302: Receive platform selection parameters and query the list of stores on the platform.

[0157] When a user selects a platform, the client calls the Kanban Shops API (dashboard / shops), passing in the platform parameter. The server filters records from the financial_profit table where the platform field equals the selected platform, extracts unique shop field values, and returns a list of shops to the client. The client renders the shop list as a multi-select dropdown selector (combobox + search functionality). Users can select multiple shops, triggering a next-level update after each selection.

[0158] Step S303: Receive platform and store parameters, and call the first database function to obtain the supplier list corresponding to the platform and store parameters.

[0159] After the user selects a platform and a store, the client calls the dashboard supplier interface (dashboard / suppliers), passing in the `platform` parameter and the `shops` parameter (a comma-separated list of store names). The server calls the database function `fn_distinct_suppliers`, passing in the platform parameter (`p_platform`) and the store parameter (`p_shops`). This function queries the `financial_profit` table for records that meet the platform and store criteria, extracts the unique `supplier` field values, and returns them to the client. The returned supplier list only includes suppliers whose data actually exists under the current filter criteria. The client renders the supplier list as a multi-select dropdown.

[0160] Step S304: Receive platform, store, and supplier parameters, and call the second database function to obtain the category list corresponding to the platform, store, and supplier parameters.

[0161] After the user selects a platform, store, and supplier, the client calls the dashboard category interface (dashboard / categories), passing in the platform, shops, and suppliers parameters (a comma-separated list of supplier names). The server calls the database function fn_distinct_categories, passing in the platform (p_platform), store (p_shops), and supplier (p_suppliers) parameters. This function queries the financial_profit table for records that meet all the above conditions, extracts the unique category field values, and returns them to the client. The client then renders the category list as a single-select dropdown.

[0162] Step S305: When the upper-level filter conditions change, the lower-level filter option set is automatically cleared and re-queried.

[0163] When any filter condition changes, the client automatically clears the selection values ​​of all filter layers below that layer and calls the corresponding API again to retrieve the new option list. For example, when a user switches platforms, the store list, supplier list, and category list are all cleared and requeried; when a user changes a store selection, the supplier list and category list are cleared and requeried. This mechanism ensures that the filter option list always remains consistent with the actual data under the current filter conditions, avoiding the display of invalid options.

[0164] Example 4

[0165] Figure 4 This illustrates the method and process provided in Embodiment 4 of the present invention for dynamically aggregating and statistically analyzing filtered data by dimension and providing the aggregated statistical results to the user.

[0166] The method for dynamically aggregating and statistically analyzing the filtered data by dimension and providing the aggregation and statistical results to the user includes:

[0167] Step S401: Calculate the range of year-on-year dates and the range of month-on-month dates based on the current filtering criteria.

[0168] The server receives the current date range (startDate, endDate) and calculates the year-on-year date range (the same time period in the same year last year) and the month-on-month date range (the same time period in the previous period). For example, if the current date range is March 2024 to May 2024, then the year-on-year date range is March 2023 to May 2023, and the month-on-month date range is January 2024 to February 2024 (the previous quarter).

[0169] Step S402: Simultaneously initiate multiple aggregate queries for the current period, year-on-year period, and month-on-month period using parallel execution.

[0170] The server uses Promise.all to execute three sets of queries in parallel. The first set is the query for the current period, containing eight parallel subqueries:

[0171] Subquery 1: Call the fn_dashboard_metrics function to retrieve the aggregated core metrics data for the current period;

[0172] Subquery 2: Call the fn_dashboard_trend function to retrieve the trend data for the current period;

[0173] Subquery 3: Call the fn_dashboard_platform function to obtain the platform distribution data for the current period;

[0174] Subquery 4: Call the fn_dashboard_top_products function to retrieve the product ranking data (TOP 10) for the current period;

[0175] Subquery 5: Call the fn_dashboard_top_shops function to get the store ranking data (TOP10) for the current period;

[0176] Subquery 6: Call the fn_dashboard_top_loss_products function to get the ranking data of loss-making products in the current period (TOP 10);

[0177] Subquery 7: Call the fn_dashboard_top_categories function to get the category ranking data (TOP 10) for the current period;

[0178] Subquery 8: Call the fn_dashboard_aftersale_count function to get the number of after-sale orders in the current period.

[0179] The second group is a year-on-year period query, which contains two parallel subqueries: calling the fn_dashboard_metrics function to obtain the core indicator aggregation data of the year-on-year period and calling the fn_dashboard_aftersale_count function to obtain the number of after-sales orders of the year-on-year period.

[0180] The third group is the month-on-month period query, which contains two parallel subqueries: calling the fn_dashboard_metrics function to obtain the core indicator aggregated data for the month-on-month period and calling the fn_dashboard_aftersale_count function to obtain the number of after-sales orders for the month-on-month period.

[0181] The three sets of queries are executed in parallel through the outer Promise.all, and the subqueries within each set are executed in parallel through the inner Promise.all, achieving full parallel execution of more than ten database function calls.

[0182] Step S403: Select the corresponding set of data fields from the character's perspective to calculate the profit.

[0183] After the server retrieves the query results, it selects the corresponding data fields from the returned indicator data based on the user's perspective to calculate profits. Specifically:

[0184] The calculation of total income is the same from both perspectives:

[0185] Total revenue = Transaction revenue + Shipping revenue + Platform subsidies - Promotional discounts - After-sales refunds

[0186] Calculation of last-mile freight:

[0187] Last-mile delivery fee = Shipping label fee + Return label fee + Warehouse processing fee + Last-mile delivery fee

[0188] Public expenditure (excluding DDP):

[0189] Public Expenses = Platform Fee + Shipping Fee Refund + Last-Mile Delivery Fee + Taxes on Principal of Reviews + Warehousing Fee + Order Processing Fee + Store Rent + Advertising Fee + Other Expenses + Review Commission

[0190] Total expenditure = Public expenditure + DDP amount

[0191] Net profit = Total revenue - Total expenses

[0192] Profit margin = Net profit / Total revenue × 100%.

[0193] From a first-person (financial) perspective, the DDP amount uses the DDP1 amount, i.e.:

[0194] Total expenditure = Public expenditure + DDP1 amount

[0195] From a second perspective (operations perspective), the DDP amount uses the DDP2 amount, that is:

[0196] Total expenditure = Public expenditure + DDP2 amount.

[0197] Step S404: Calculate the rate of change of each indicator, merge the current period data with the year-on-year period comparison data and the month-on-month period comparison data into a complete response object, and return it to the client.

[0198] After the server calculates the indicator data for the current period, the same period last year, and the month-on-month period, it calculates the rate of change for each indicator:

[0199] Year-on-year change rate = (Current period value - Year-on-year period value) / Year-on-year period value × 100%

[0200] Month-on-month change rate = (Current period value - Month-on-month period value) / Month-on-month period value × 100%

[0201] The server merges the current period data, year-on-year comparison data (including year-on-year change rate), and month-on-month comparison data (including month-on-month change rate) into a complete response object, which is then returned to the client for display. The client renders visualization components such as indicator cards (including year-on-year and month-on-month change rate indicators), trend charts, platform distribution maps, and product / store / category ranking lists based on the response data.

[0202] Example 5

[0203] Figure 5 The structure of the e-commerce financial data multidimensional analysis system provided in Embodiment 5 of the present invention is shown.

[0204] The e-commerce financial data multidimensional analysis system includes:

[0205] The authentication module 501 is used to verify the user's identity credentials and obtain the user identifier and role identifier.

[0206] The authentication module includes a routing middleware submodule, which intercepts all requests to non-public routes. For requests without an authentication token, if it is an API route, a JSON error message with a 401 status code is returned; if it is a page route, the user is redirected to the login page.

[0207] The perspective adaptation module 502 is used to automatically match the perspective of financial data based on the role identifier and pass the perspective matching result to the aggregation statistics module for subsequent profit calculation.

[0208] The store isolation module 503 is used to query the list of store permissions bound to a user based on the user identifier, and to perform store-level data isolation filtering, providing only data within the scope of the user's permissions.

[0209] The cascading filtering module 504 is used to establish a multi-level cascading filtering mechanism to perform multi-level cascading filtering on the provided data about the bound stores.

[0210] The aggregation statistics module 505 is used to dynamically aggregate and statistically analyze the filtered data by dimension and provide the aggregation statistics results to the user.

[0211] The working process of each module can be referred to the corresponding process in the aforementioned embodiments one to four. This working process can be implemented by software programs. The examples and related descriptions listed in embodiments one to four are also applicable to explaining the working process of the module described in embodiment five, and will not be repeated here.

[0212] Example 6

[0213] The e-commerce financial data multidimensional analysis system also includes a data import module 506, which is used to import received Excel data and perform duplicate detection.

[0214] Figure 6 The structural details of the data import module 506 provided in Embodiment 6 of the present invention are shown.

[0215] The data import module includes:

[0216] Parsing subunit 601 receives Excel files uploaded via the client, parses the worksheet data, and maps it to database fields according to preset column name mapping rules. It performs numeric conversion on numeric fields and sets empty values ​​to null.

[0217] The verification subunit 602 is used to perform two verifications on the mapped data: (1) verification that the entry date is not empty, filtering out records with empty entry dates and rejecting imports; (2) verification of the existence of the store platform combination, querying the store management table (shops) to verify whether the store and platform combination in the Excel data has been registered.

[0218] The duplicate detection subunit 603 is used to extract the set of non-duplicate entry dates from the mapped data and query whether a record with the same date already exists in the financial profit data table.

[0219] Cache subunit 604 is used to encapsulate the mapped data row array, the list of duplicate dates, and the creation timestamp into a data object to be imported when duplicate entry dates are detected. This object is then stored in a globally shared Map structure (shared via globalThis) using a UUID token as the key. An expiration time of N minutes (TTL) is set; expired entries are automatically cleaned up during writes, and expired entries are checked during reads.

[0220] The import confirmation subunit 605 is used to receive the client's import confirmation request, retrieve cached data based on the token, generate a batch ID, and insert the data into the financial_profit table in batches of 500 records each; after successful insertion, the cached data of the corresponding token is deleted.

[0221] Example 7

[0222] Figure 7 The structural details of the cascaded screening module 504 provided in Embodiment 7 of the present invention are shown.

[0223] The cascaded filtering module 504 includes:

[0224] Platform filtering sub-unit 701 is used to query a list of unique platform names from the financial_profit table and return it to the client.

[0225] The store filtering sub-unit 702 is used to receive platform parameters, query a list of unique stores on the platform, and return it to the client.

[0226] The supplier filtering subunit 703 is used to receive platform and store parameters and call the first database function fn_distinct_suppliers(parameters: p_platform, p_shops) to obtain a list of unique suppliers within the platform and store scope.

[0227] The category filtering subunit 704 is used to receive platform, store and supplier parameters, and call the second database function fn_distinct_categories(parameters: p_platform, p_shops, p_suppliers) to obtain a list of unique categories within the platform, store and supplier range.

[0228] The dynamic update sub-unit 705 is used to automatically trigger the clearing and re-querying of the lower-level filter option set when the upper-level filter conditions change.

[0229] Specifically, it automatically clears the selection values ​​of all filter layers below this layer and calls the corresponding interface again to obtain a new list of options, ensuring that the list of filter options is always consistent with the actual data under the current filter conditions.

[0230] Example 8

[0231] Figure 8 The sub-module structure of the aggregation statistics module provided in Embodiment 8 of the present invention is shown.

[0232] The aggregation statistics module 505 includes:

[0233] The date range calculation subunit 801 is used to calculate the year-on-year date range (same time period in the same period last year) and the month-on-month date range (same time period in the previous period) based on the current date range (startDate, endDate).

[0234] The parallel query unit 802 is used to initiate multiple aggregate queries for the current period, the same period last year, and the month-on-month period simultaneously through parallel execution.

[0235] The perspective calculation subunit 803 is used to select the corresponding set of data fields for profit calculation based on the character's perspective.

[0236] In this study, the first perspective uses DDP1 amounts for expenditure calculation, while the second perspective uses DDP2 amounts. The calculation of total revenue, tail-end fees, and public expenditures is the same in both perspectives.

[0237] The comparison calculation subunit 804 is used to calculate the rate of change of each indicator, merge the current period data with the year-on-year period comparison data and the month-on-month period comparison data into a complete response object, and return it to the client.

[0238] The working process of each module in embodiments six to eight above can be referred to the corresponding process in embodiments two to four above. This working process can be implemented by software program. The examples and related descriptions listed in embodiments two to four are also applicable to explaining the working process of the modules described in embodiments six to eight, and will not be repeated here.

[0239] In this embodiment of the invention, the e-commerce financial data multidimensional analysis system can be implemented by corresponding hardware or software units. Each unit can be an independent hardware or software unit, or it can be integrated into a hardware or software unit. This is not intended to limit the invention.

[0240] Those skilled in the art will understand that all or part of the steps in the methods of the above embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, such as ROM / RAM, disk, optical disk, etc.

[0241] The above description, in conjunction with specific embodiments, provides a further detailed explanation of the present invention. It should not be construed that the specific implementation of the present invention is limited to these descriptions. For those skilled in the art, several simple deductions or substitutions can be made without departing from the concept of the present invention, and all such deductions or substitutions should be considered to fall within the scope of protection defined by the claims submitted herein.< / token>

Claims

1. A multidimensional analysis method for e-commerce financial data based on role-perspective adaptation, comprising: Step S101: Verify the user's identity credentials and obtain the user identifier and role identifier; Step S102: Automatically match the financial data perspective based on the role identifier; Step S103: Based on the user identifier, query the list of store permissions bound to the user, perform store-level data isolation filtering, and only provide data within the scope of the user's permissions; Step S104: Establish a multi-level cascading filtering mechanism to perform multi-level cascading filtering on the provided data about the bound stores; Step S105: Perform dynamic aggregation statistics on the filtered data by dimension, and provide the aggregation statistics results to the user.

2. The multidimensional analysis method for e-commerce financial data based on role-perspective adaptation according to claim 1, wherein the method further includes: Step S201: The server receives the Excel file uploaded by the client, parses the worksheet data, and maps it to database fields according to the preset column name mapping rules; Step S202: Verify the entry date and the existence of the store platform combination on the mapped data; Step S203: Extract the set of unique posting dates and query the database to see if a record with the same date already exists; Step S204: If a duplicate entry date is detected, cache the data in the memory buffer, generate a one-time token, and return a warning message; Step S205: When the server receives the confirmation import request, it retrieves the cached data based on the token, inserts it into the database in batches, and then deletes the cached data corresponding to the token.

3. The multi-dimensional analysis method for e-commerce financial data based on role-perspective adaptation according to claim 1, wherein in step S101, the user sends an authentication request to the server through the client, the authentication request information carrying the user identity credential, and the user identity credential verification method supports dual-mode authentication: the first authentication mode is the Web authentication mode, and the second authentication mode is the client application authentication mode.

4. The multidimensional analysis method for e-commerce financial data based on role-perspective adaptation as described in claim 1, wherein in step S102, after the server obtains the user identifier, it queries the user's role identifier from the pre-established user table. The user table stores fields such as user identifier, username, password hash value, display name, status, and role identifier. The role identifier is used to perform a correlation query with the role table to obtain the role name corresponding to the role identifier, and the financial data perspective is automatically matched according to the role name.

5. The multidimensional analysis method for e-commerce financial data based on role perspective adaptation according to claim 4, wherein in step S103, the user table is queried to obtain the user's role name. When the role name is admin, it is marked as an administrator. No store filtering conditions are applied. The administrator can view the data of all stores. If the role name is not admin, then query the pre-established association table between users and stores to obtain the list of store names bound to that user.

6. The multi-dimensional analysis method for e-commerce financial data based on role-perspective adaptation according to claim 1, wherein the method for performing multi-level cascading filtering in step S104 includes: Step S301: Obtain the platform list and return it to the client; Step S302: Receive platform selection parameters and query the list of stores on the platform; Step S303: Receive platform and store parameters, and call the first database function to obtain the supplier list corresponding to the platform and store parameters; Step S304: Receive platform, store, and supplier parameters, and call the second database function to obtain the category list corresponding to the platform, store, and supplier parameters; Step S305: When the upper-level filter conditions change, the lower-level filter option set is automatically cleared and re-queried.

7. The method for multidimensional analysis of e-commerce financial data based on adaptive role perspective as described in claim 1, wherein in step S105, the method for dynamically aggregating and statistically analyzing the filtered data by dimension and providing the aggregation and statistical results to the user includes: Step S401: Calculate the range of year-on-year dates and the range of month-on-month dates based on the current filtering criteria; Step S402: Simultaneously initiate multiple aggregate queries for the current period, year-on-year period, and month-on-month period using parallel execution; Step S403: Select the corresponding set of data fields based on the character's perspective to calculate the profit; Step S404: Calculate the rate of change of each indicator, merge the current period data with the year-on-year period comparison data and the month-on-month period comparison data into a complete response object, and return it to the client.

8. A multi-dimensional analysis system for e-commerce financial data based on role-perspective adaptation, comprising: The authentication module is used to verify user identity credentials and obtain user identifiers and role identifiers; The perspective adaptation module is used to automatically match the perspective of financial data based on the role identifier and pass the perspective matching result to the aggregation statistics module. The store isolation module is used to query the list of store permissions bound to a user based on the user identifier, and to perform store-level data isolation and filtering, providing only data within the scope of the user's permissions. The cascading filtering module is used to establish a multi-level cascading filtering mechanism to perform multi-level cascading filtering on the provided data about the bound stores; The aggregation and statistics module is used to dynamically aggregate and statistically analyze the filtered data by dimension, and provide the aggregation and statistics results to the user.

9. The multi-dimensional analysis system for e-commerce financial data based on role-perspective adaptation according to claim 8, further comprising: The module for importing received Excel data and performing duplicate detection includes: The parsing sub-unit is used to receive the uploaded Excel file, parse the worksheet data, and map it to database fields according to the preset column name mapping rules; The verification subunit is used to perform two verifications on the mapped data: (1) verification of the non-empty entry date, filtering out records with empty entry dates and rejecting imports; (2) verification of the existence of the store platform combination, querying the store management table to verify whether the store and platform combination in the Excel data has been registered. The duplicate detection subunit is used to extract the set of non-duplicate entry dates from the mapped data and query whether a record with the same date already exists in the financial profit data table; The cache sub-unit is used to encapsulate the mapped data row array, the list of duplicate dates, and the creation timestamp into a data object to be imported when duplicate entry dates are detected. It stores the data in a globally shared Map structure with the UUID token as the key, sets an expiration time, automatically cleans up expired entries when writing, and checks whether they have expired when reading. The import confirmation sub-unit is used to receive import confirmation requests, retrieve cached data based on the token, generate batch IDs, insert the data into the financial_profit table in batches, and delete the cached data of the corresponding token after successful insertion.

10. The multi-dimensional analysis system for e-commerce financial data based on role-perspective adaptation according to claim 8, wherein the cascading filtering module includes: The platform filtering sub-unit is used to query a list of unique platform names from the financial_profit table and return it to the client; The store filtering sub-unit is used to receive platform parameters, query a list of unique stores on the platform, and return it to the client. The supplier filtering subunit is used to receive platform and store parameters and call the first database function fn_distinct_suppliers to obtain a list of unique suppliers within the platform and store scope; The category filtering subunit is used to receive parameters from the platform, store, and supplier, and call the second database function fn_distinct_categories to obtain a list of unique categories within the scope of the platform, store, and supplier. Dynamically update sub-cells to automatically trigger the clearing and re-querying of the lower-level filter option set when the upper-level filter conditions change; The aggregation statistics module includes: The date range calculation subunit is used to calculate the year-on-year and month-on-month date ranges based on the current date range. The parallel query unit is used to simultaneously initiate multiple aggregate queries for the current period, year-on-year period, and month-on-month period through parallel execution. The perspective calculation subunit is used to select the corresponding set of data fields for profit calculation based on the character's perspective. The comparison calculation subunit is used to calculate the rate of change of each indicator, merge the current period data with the year-on-year period comparison data and the month-on-month period comparison data into a complete response object, and return it to the client.