E-commerce enterprise business and financial integrated BI data analysis method and system

Abstract

The invention relates to a business and financial integrated BI data analysis method and system for an e-commerce enterprise. The method comprises the following steps: capturing e-commerce platform data in all directions and in multiple dimensions; a third-party ERP system is in butt joint through an API interface, merchant data are obtained, and the data are converted into formatted data and then fall into an ods layer of the MySQL database; integrating the data to form a number warehouse layer, calculating a core index system, and modeling the data through a dimension modeling methodology; financial accounting is automated and accurate; a scientific and reasonable offline cost allocation mechanism is adopted; and data analysis and decision support. According to the invention, the problems of data non-uniformity, high data acquisition difficulty, low efficiency and high dependence on manual operation due to the fact that e-commerce enterprise data is dispersed in a plurality of systems are solved. And a scientific and reasonable offline cost allocation mechanism enables profit accounting to be more accurate. And an enterprise-level data analysis framework and security control are constructed, and an enterprise data asset platform is created.

Description

Technical Field

[0001] The present invention relates to the technical field of e-commerce, and specifically to a method and system for business-finance integrated BI data analysis in e-commerce enterprises. Background Art

[0002] Business-finance integration refers to the close combination of an enterprise's business processes and financial processes. Through information technology means, seamless connection and integration of business data and financial data are achieved, thereby improving the enterprise's operational efficiency, management level, and decision-making ability. This model breaks down the barriers between traditional business and finance, making finance no longer just a post-event "bookkeeper", but deeply involved in the front end of business, participating in business decision-making and management, and providing strong support for the strategic development of the enterprise.

[0003] A BI system, fully known as a Business Intelligence System, is a comprehensive system integrating data processing, data analysis, and information display functions. It collects, integrates, analyzes, and mines internal and external data of an enterprise, converts the data into valuable information and knowledge, and supports the enterprise's decision-making, operation management, and strategic planning.

[0004] In the current digital business wave, the operation and management of e-commerce enterprises face unprecedented opportunities and challenges. The present invention focuses on the methodology of business-finance integrated data processing and BI (Business Intelligence) system construction in e-commerce enterprises, deeply involved in multiple key fields such as database management technology, financial accounting, BI system construction, and data analysis, aiming to build an efficient, accurate, and intelligent data-driven operation system for e-commerce enterprises, especially focusing on highly targeted e-commerce data acquisition and order management methods and systems.

[0005] First of all, at the data acquisition level, the present invention can comprehensively and multi-dimensionally capture various core data of e-commerce platforms. On the one hand, it accurately obtains the sales order data continuously generated by e-commerce platforms. These order data are like the pulse of enterprise operation, and each beat contains key information such as consumers' purchase preferences, geographical distribution, and purchase time periods. At the same time, after-sales data are also completely collected, including reasons for customer returns and exchanges, after-sales processing time, etc., providing a strong basis for optimizing product quality and service processes. Furthermore, product information data covers detailed information such as product specifications, models, materials, costs, etc., which is the basis for financial accounting and product strategy formulation. Inventory data reflects the in-stock quantity and in-out dynamic of products in real time, not only providing real-time monitoring for warehouse management, but also accurately predicting sales volume based on past sales patterns and current inventory levels. This is of great significance for e-commerce platforms, as it can trigger the replenishment process in a timely manner, avoid order loss due to out-of-stock, effectively improve user satisfaction, and maintain customer loyalty.

[0006] In addition, operation and promotion data are also incorporated, such as advertising costs, evaluation of the effectiveness of traffic introduction channels, etc. Through in-depth mining of this data, the store's ROI (Return On Investment) can be accurately calculated, and based on this, with the help of a big data analysis model, the input-output ratio for a period of time in the future can be predicted, providing scientific decision-making support for the enterprise's marketing budget allocation to ensure that every penny of promotion expenses is spent effectively. Also, the store's bill data is completely collected, and through complex but efficient algorithms, an accurate correspondence between bills and orders is established, enabling financial accounting to be freed from the cumbersome and error-prone manual reconciliation and achieving automation and precision. Whether it is revenue recognition, cost allocation, or expense settlement, everything can be clearly seen and accurate to the penny.

[0007] Finally, a scientific and reasonable allocation mechanism is also established for offline expense data. Since the operation of e-commerce enterprises is not completely online, offline expenses such as office space rental, operation of logistics distribution centers, and business trips of personnel also account for a considerable proportion. How to accurately allocate these expenses to each product line, business department, and even specific orders is a key problem in the integration of business and finance. The present invention uses advanced cost driver analysis methods and combines the characteristics of business processes to allocate offline expenses to corresponding cost objects according to reasonable weights, so as to truly reflect the enterprise's operating costs in the overall financial statements and single-product profit analysis, providing a solid data basis for the enterprise's strategic decision-making and pricing strategy formulation.

[0008] In summary, the methodology for data processing and BI system construction for the integration of business and finance in e-commerce enterprises proposed by the present invention integrates the data resources of the entire e-commerce operation chain, breaks down the barriers between business and finance, drives the efficient operation of the enterprise with data as the link, helps e-commerce enterprises stand out in the fierce market competition, and achieves sustainable development. Summary of the Invention

[0009] The present invention provides a method and system for BI data analysis in the integration of business and finance in e-commerce enterprises, which integrates the data resources of the entire e-commerce operation chain, breaks down the barriers between business and finance, drives the efficient operation of the enterprise with data as the link, helps e-commerce enterprises stand out in the fierce market competition, and realizes the sustainable development of the enterprise.

[0010] To achieve the above object, the present invention provides the following technical solutions:

[0011] A method for BI data analysis in the integration of business and finance in e-commerce enterprises, the method comprising:

[0012] S1. Grab e-commerce platform data comprehensively and multi-dimensionally; connect to a third-party ERP system through an API interface to obtain merchant data, convert the data into formatted data, and store it in the ods layer of a MySQL database. The data includes sales order data, return data, after-sales data, marketing promotion data, store data, purchase receipt, purchase delivery, product information, supplier information, warehouse information, etc.

[0013] S2. Integrate the data to form a data warehouse layer, calculate the core indicator system, and model the data through the methodology of dimensional modeling.

[0014] S3. Automate and accurately perform financial accounting.

[0015] S4. Have a scientific and reasonable offline expense allocation mechanism.

[0016] S5. Data analysis and decision support.

[0017] As a further technical solution of the present invention, step S1 includes:

[0018] S101. Enter the open platform and apply for business permissions. Before starting to call the API to request data, you need to enter the open platform. After successful entry, you will get the application key (app_key) and secret key (app_secret) assigned by the platform for you. After the API interface permissions applied by the ISV / developer are approved on the open platform, you will obtain the corresponding API permissions.

[0019] S102. Configure the connector based on the open platform authorization code. The connector - connector is a tool for server connection information configured specifically for users. It can add and isolate data sources in the development environment and production environment respectively to protect data security. According to specific circumstances, users can choose three different environments, namely production env_production, test env_test, and development env_development. After completing development and testing, before formal operation, it is necessary to switch the connector environment to the production environment.

[0020] S103. Add a new integration solution. The integration solution is the core of the entire API interface integration platform. Each integration solution represents a docking strategy for a business. Users can create multiple integration solutions with different rules according to different businesses, including: purchase order synchronization, online sales delivery synchronization, offline sales delivery synchronization, online sales order synchronization, online after-sales return and refund order synchronization, online product information synchronization, and online inventory information synchronization, etc.

[0021] S104. Integration Solution - Source Platform Configuration, Details Configuration Page. The details page contains two major areas, namely the header area and the sub-tab area. The header area mainly displays basic information and operations such as starting the solution.

[0022] S105. Integration Solution - Target Platform Configuration, Details Configuration Page. Fill in relevant information to complete the target platform configuration.

[0023] S106. Start Solution & Scheduled Policy Configuration.

[0024] Through the above 6 steps, the core data of the e-commerce platform is comprehensively and multi-dimensionally captured.

[0025] As a further technical solution of the present invention, a multi-layer recursive authorization verification algorithm is also introduced in step S1: When performing OAuth2.0 authorization, in order to ensure the security and integrity of the authorization chain, a multi-layer recursive authorization verification algorithm is adopted. This algorithm recursively decomposes the requested authorization code into multiple child nodes, and successively verifies each layer of child nodes through a multi-layer authorization verification model (MLAVM, Multi-Layer Authorization Validation Model). Its core principle is to construct a permission-dependent directed acyclic graph (DAG) and use depth-first search (DFS) to recursively traverse each node in the authorization chain to ensure the permission inheritance relationship of each authorization node.

[0026] Exchange the business authorization code for an authorization token, and based on the authorization token, use the API request to obtain merchant data.

[0027] Specifically, through third-party business authorization, ISVs / developers can, after obtaining the merchant's authorization, obtain the merchant data within the authorized scope to complete relevant business processing or application development (such as merchant store order data to achieve financial record-keeping, etc.), and it is required that the ISV / developer initiate it, or the ISV / developer adds the corresponding function in its own application and the merchant operates to complete the authorization behavior; after the authorization is agreed, the ISV / developer can use the API to request the merchant's relevant data. When performing third-party business authorization, the ISV / developer needs to assemble the authorization URL. Each time the authorization is successful, the ISV / developer will obtain a business authorization code to exchange for the authorization token access_token (equivalent to the access_token in the public request parameters).

[0028] The dynamic weighted average token generation algorithm (DWATG) is also introduced: In the process of obtaining the authorization token access_token, the dynamic weighted average token generation algorithm is adopted, aiming to dynamically generate the optimal token through the weight distribution of historical requests. Its steps include normalizing the weight values of historical requests (using the entropy weight method), and then generating a new token by weighting according to the weight values of each request. The formula is:

[0029] Token=Σ(w_i / Σw_j)×request_i;

[0030] Among them, w_i represents the weight corresponding to the i-th historical request, w_j represents the weight corresponding to the j-th non-historical request; request_i represents the feature vector corresponding to each historical request; n represents the number of requests.

[0031] As a further technical solution of the present invention, step S2 includes:

[0032] S201. Data domain division: Divide the data into different data domains according to different themes or business functions. Each data domain contains relevant data of a specific theme, and the themes include sales orders, return and refund sales, after-sales processing, warehousing, procurement, inbound, outbound, etc.;

[0033] S202. Build a bus matrix to clarify the data domain to which the business process belongs and clarify the relationship between the business process and the dimension;

[0034] S203. Specification definition, define the index system (atomic index, derived index);

[0035] S204. Model design, detailed model design, build a consistent dimension table DIM, build a consistent fact table DWD, summary model design, build a common summary model DWS, and build an application summary model ADS;

[0036] S205. Code development, deployment and operation and maintenance.

[0037] As a further technical solution of the present invention, step S3 includes:

[0038] S301. Revenue accounting, calculate at the ADS data application layer in step S2, obtain the sales order detail table data in the DWS slightly summarized layer, and summarize the sales amount according to the dimensions of order placement time, payment time, shipping time, confirmed receipt time, and store. The sales order filters orders with order statuses such as paid, in shipping, and shipped;

[0039] S302. Cost accounting, calculate at the ADS data application layer in step S2, obtain the sales order detail table and product information detail data in the DWS slightly summarized layer, and calculate the total sales cost of each order by associating with the product sku_id;

[0040] S303. Courier fee calculation. Distinguish between standard parcels and large-item logistics. For standard parcels, the courier fee is directly obtained by multiplying the per-order quotation for each region in the courier company's quotation list by the number of orders. For large-item logistics such as furniture categories, the courier fee consists of several aspects, including trunk line fees, feeder line fees, distribution fees, and installation fees. Calculate the fees for each step separately and summarize them into one order to obtain the courier fee.

[0041] S304. Promotion fee calculation. Calculate at the ADS data application layer in step S2. Obtain the detailed promotion spending data for each platform at the DWS slightly aggregated layer, and summarize it at the store dimension to get the total daily promotion spending for each store.

[0042] S305. Operation fee calculation. Calculate at the ADS data application layer in step S2. Obtain the bill data for each platform at the DWS slightly aggregated layer. Distinguish different subjects from the remarks column of the bill, calculate different operation fees separately, including freight income, freight expenditure, and returned freight. Uniformly summarize them into the freight / logistics expenditure subject, summarize the freight insurance handling fees into the freight insurance handling fee subject, summarize the commissions into the commission subject, and summarize the label fees into the label fee subject. Distinguish all operation fees into each subject and then summarize them at the store dimension.

[0043] As a further technical solution of the present invention, the apportionment method in step S4 is as follows:

[0044] S401. Apportion by usable area;

[0045] Applicable situation: For expenses related to space occupation such as rent and property management fees, it is more reasonable to apportion by usable area.

[0046] Calculation method: First determine the total expense and the total area, then calculate the apportionment expense per square meter, and then calculate the apportionable expense according to the area occupied by each department or project.

[0047] S402. Apportion by order quantity;

[0048] Applicable situation: When the expense is directly related to the order quantity, such as the raw material procurement expense and transportation expense of an enterprise, it can be apportioned by order quantity.

[0049] Calculation method: Apportion the expense according to the proportion of the order quantity of different businesses or projects to the total order quantity.

[0050] S403. Apportion by order amount;

[0051] Applicable situation: When the expense is directly related to the order amount, such as when calculating the offline after-sales expense, calculate the apportionment ratio according to the proportion of the sales amount of a single store to the total sales amount, and multiply the total after-sales expense by the apportionment ratio to obtain the apportioned amount.

[0052] S404. Apportion by headcount;

[0053] Applicable situations: Applicable to situations where the cost is directly related to the number of personnel, such as office cleaning costs, group activity costs, etc.;

[0054] Calculation method: Divide the total cost by the number of participants to obtain the cost to be apportioned per person;

[0055] S405. Apportion according to actual consumption;

[0056] Applicable situations: For costs that can be accurately measured in terms of consumption, such as water and electricity bills, office supplies expenses, etc., apportioning according to actual consumption can more accurately reflect the cost burden of each department or project;

[0057] Calculation method: Apportion by installing metering devices or counting the actual usage.

[0058] Another object of the present invention is to provide a system for business-finance integrated BI data analysis of e-commerce enterprises, and the system includes:

[0059] A data scraping module, which is used to scrape e-commerce platform data in all directions and multi-dimensions; connect to a third-party ERP system through an API interface to obtain merchant data, convert the data into formatted data and then store it in the ods layer of a MySQL database. The data includes sales order data, return data, after-sales data, marketing promotion data, store data, purchase in-stock, purchase out-stock, product information, supplier information, warehouse information, etc.;

[0060] A data integration module, which is used to integrate data to form a data warehouse layer, calculate a core index system, and model the data through the methodology of dimensional modeling;

[0061] A data accounting module, which is used for automated and accurate financial accounting;

[0062] A cost apportionment module, which is used for a scientific and reasonable off-line cost apportionment mechanism;

[0063] A data analysis module, which is used for data analysis and decision support.

[0064] Compared with the prior art, the beneficial effects of the present invention are:

[0065] 1. The method and system for business-finance integrated BI data analysis of e-commerce enterprises: The present invention proposes a method and system for business-finance integrated BI data analysis of e-commerce enterprises. By integrating the entire e-commerce data and establishing a standardized and customized underlying data warehouse model, it solves the problems that the data of e-commerce enterprises are scattered in multiple systems, the data is not unified, the difficulty of data acquisition is high, the efficiency is low, and the dependence on manual operation is high.

[0066] 2. A standardized e-commerce data analysis methodology, with strong data guiding business analysis, and the front-end standardized analysis scenario package can achieve high development efficiency.

[0067] 3. A scientific and reasonable offline expense allocation mechanism makes profit accounting more accurate.

[0068] 4. Report automation improves business operation efficiency and enhances the accuracy and timeliness of report data.

[0069] 5. Build an enterprise-level data analysis framework and security control to create an enterprise data asset platform. Description of the Drawings

[0070] Figure 1 It is a flow chart of the method for e-commerce enterprise business-finance integrated BI data analysis. Detailed Implementation Manner

[0071] In order to make the technical problems, technical solutions and beneficial effects to be solved by the present invention clearer, the present invention will be further described in detail below with reference to the drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0072] As Figure 1 shown, the embodiment of the present invention provides a method for e-commerce enterprise business-finance integrated BI data analysis, and the method includes:

[0073] S1. Grab e-commerce platform data comprehensively and multi-dimensionally; connect to a third-party ERP system through an API interface to obtain merchant data, convert the data into formatted data and store it in the ods layer of the MySQL database. The data includes sales order data, return data, after-sales data, marketing promotion data, store data, purchase receipt, purchase delivery, product information, supplier information, warehouse information, etc.

[0074] As a further technical solution of the present invention, step S1 includes:

[0075] S101. Enter the open platform and apply for business permissions. Before starting to call the API to request data, it is necessary to enter the open platform. After successful entry, obtain the application key (app_key) and secret key (app_secret) assigned by the platform for you, and obtain the corresponding API permissions after the API interface permissions applied by the ISV / developer are approved on the open platform.

[0076] S102. Configure a connector based on the open platform authorization code. The connector - "connector" is a tool for configuring server connection information exclusive to users. It can separately add and isolate data sources in the development environment and the production environment to protect data security. According to specific circumstances, users can choose three different environments, namely production (env_production), testing (env_test), and development (env_development). After completing development and testing, before formal operation, it is necessary to switch the connector environment to the production environment;

[0077] S103. Add new integration solutions. The integration solution is the core of the entire API interface integration platform. Each integration solution represents a docking strategy for a business. Users can create multiple integration solutions with different rules according to different businesses, including: purchase order synchronization, online sales outbound synchronization, offline sales outbound synchronization, online sales order synchronization, online after - sales return and refund order synchronization, online product information synchronization, and online inventory information synchronization, etc.;

[0078] S104. Integration solution - source platform configuration, details configuration page. The details page contains two major areas, namely the header and the sub - tab area; the header area mainly displays basic information and operations such as starting the solution;

[0079] S105. Integration solution - target platform configuration, details configuration page, fill in relevant information to complete the target platform configuration;

[0080] S106. Start the solution & configure the timing strategy.

[0081] Complete the all - around and multi - dimensional capture of the core data of the e - commerce platform through the above 6 steps.

[0082] As a further technical solution of the present invention, a multi - layer recursive authorization verification algorithm is also introduced in step S1: When performing OAuth2.0 authorization, in order to ensure the security and integrity of the authorization chain, we adopt a multi - layer recursive authorization verification algorithm. This algorithm recursively decomposes the requested authorization code into multiple child nodes and successively verifies each layer of child nodes through a multi - layer authorization verification model (MLAVM, Multi - Layer Authorization Validation Model). Its core principle is to construct a permission - dependent directed acyclic graph (DAG) and use depth - first search (DFS) to recursively traverse each node in the authorization chain to ensure the permission inheritance relationship of each authorization node;

[0083] Exchange the business authorization code for an authorization token, and based on the authorization token, use an API request to obtain merchant data.

[0084] Specifically, through third-party business authorization, after obtaining the merchant's authorization, ISVs / developers can obtain the merchant data within the authorized scope to complete relevant business processing or application development (such as merchant store order data to achieve financial bookkeeping, etc.). And it is required that the ISV / developer initiate it, or the ISV / developer adds the corresponding function in its own application and the merchant operates to complete the authorization behavior; after the authorization is approved, the ISV / developer can use the API to request the relevant merchant data. When performing third-party business authorization, the ISV / developer needs to assemble the authorization URL. After each successful authorization, the ISV / developer will obtain a business authorization code to exchange for the authorization token access_token (equivalent to the access_token in the public request parameters).

[0085] The dynamic weighted average token generation algorithm (DWATG) is also introduced: In the process of obtaining the authorization token access_token, the dynamic weighted average token generation algorithm is adopted, aiming to dynamically generate the optimal token through the weight distribution of historical requests. Its steps include normalizing the weight values of historical requests (using the entropy weight method), and then generating a new token by weighting according to the weight values of each request. The formula is:

[0086] Token=Σ(w_i / Σw_j)×request_i;

[0087] Among them, w_i represents the weight corresponding to the i-th historical request, w_j represents the weight corresponding to the j-th non-historical request; request_i represents the feature vector corresponding to each historical request; n represents the number of requests.

[0088] Formula supplement

[0089] Authorization request formula: When assembling the authorization URL, we adopt the following formula to ensure the consistency and security of the request:

[0090] URL_auth=BaseURL+ClientID×exp(α×RedirectURI) / (1+ln(1+Scope));

[0091] Among them, BaseURL represents the base address of the authorization server (such as https: / / auth.example.com / oauth), ClientID represents the unique identifier of the ISV / developer (assigned by the authorization server), α is the key negotiation constant with the authorization server, RedirectURI represents the callback address for redirecting after successful authorization, and Scope represents the authorization scope (such as read:orders write:payments). The formula aims to improve the encryption strength of the URL in a high-concurrency environment through the combination of the exponential function and the logarithmic function.

[0092] Access token encryption formula: When generating the access_token, a symmetric encryption formula is used to ensure the security of the token during transmission:

[0093] Token_encrypted = Token_plain ⊕ SHA256(AppSecret);

[0094] Where Token_encrypted represents the encrypted access token, Token_plain represents the plaintext token (including user identity and permission information), ⊕ represents the bitwise exclusive OR operation, SHA256 is a hash encryption function, and AppSecret represents the application key. This encryption mechanism can prevent the token from being attacked by a man-in-the-middle during network transmission.

[0095] S2. Integrate the data to form a data warehouse layer, calculate the core indicator system, and model the data using the methodology of dimensional modeling;

[0096] Dimensional Modeling is a method of database design and data warehouse architecture, mainly used to support business intelligence and data analysis. It simplifies the query and analysis process of complex data by dividing the data into fact tables and dimension tables. The core idea of dimensional modeling is to be oriented by business requirements and construct a data model that is easy to understand and query from the perspective of users. The modeling layers are the ODS source-attached layer, whose main function is to copy the source-side data to the target database, and the database table names start with ODS; the DWD detail layer, whose main function is to solve data integration and data quality problems, integrate the data from different source systems, and integrate them, eliminate heterogeneity and redundancy, provide consistent data, perform unified cleaning, transformation, and processing on the data, shield dirty data, and standardize the field naming uniformly, and the database table names start with DWD; the DWS lightly aggregated layer, whose main function is to store data aggregation, processed indicators, labels, etc. data tables, and the database table names start with ADS; the ADS data application layer, whose main function is to store the result data of data marts and front-end page queries, and the database table names start with ADS.

[0097] Step S2 includes:

[0098] S201. Data domain division: Divide the data into different data domains according to different themes or business functions. Each data domain contains data related to a specific theme, and the themes include sales orders, return and refund sales, after-sales processing, warehousing, procurement, inbound, outbound, etc.;

[0099] S202. Construct a bus matrix, clarify the data domain to which the business process belongs, and clarify the relationship between the business process and the dimension;

[0100] S203. Standardize the definition and define the index system (atomic index, derived index);

[0101] S204. Model design, detailed model design, construct a consistent dimension table DIM, construct a consistent fact table DWD, summary model design, construct a common summary model DWS, and construct an application summary model ADS;

[0102] S205. Code development, deployment, operation, and maintenance.

[0103] Algorithm introduction:

[0104] Machine learning algorithms are used to analyze the correlation between hot-selling products in the market sales at the summary model layer, so as to rationally allocate subsequent enterprise sales planning and procurement, and serve as basic reference data for the analysis of different product production / processing industrial chains.

[0105] This algorithm belongs to an unsupervised learning model, and we consider it from two aspects:

[0106] Product correlation:

[0107] Product correlation can be calculated from two aspects: the probability of products appearing simultaneously (joint probability) and the correlation of amount changes (linear correlation).

[0108] Confidence level of the correlation coefficient:

[0109] The confidence level reflects the reliability of a statistical indicator and is usually used as a weight in applications. The higher the confidence level of an indicator, the greater the weight assigned to it.

[0110] The confidence level of the product correlation coefficient can be calculated by the frequency of the product appearing in all enterprises and the entropy of the corresponding procurement product (sales product) when it is a procurement product (sales product).

[0111] Formula supplement:

[0112] Joint probability of product correlation:

[0113] The ratio of the probability of two products appearing simultaneously to the probabilities of their individual appearances:

[0114]

[0115] Among them, P(x,y) represents the probability of product X and product Y appearing simultaneously, P(x) represents the probability of product X appearing alone, and P(Y) represents the probability of product Y appearing alone.

[0116] Pearson correlation coefficient (linear correlation):

[0117] The ratio of the covariance and standard deviation of two commodities:

[0118]

[0119] Among them, Cov(X,Y) represents the covariance of commodities X and Y, σx represents the standard deviation of commodity X, and σy represents the standard deviation of commodity y.

[0120] Correlation coefficient:

[0121] The result is obtained by combining the joint probability and the Pearson coefficient:

[0122]

[0123] Among them, Cov(X,Y) represents the covariance of commodities X and Y.

[0124] Correlation coefficient confidence:

[0125] Occurrence frequency formula:

[0126] F(x, y) = ln(x)·ln(y);

[0127] Among them, F(x,y) measures the correlation weight between commodities A and B, ln(x) represents the natural logarithm of the joint occurrence frequency of commodities A and B, and ln(y) represents the natural logarithm of the marginal occurrence frequency of commodity A or commodity B.

[0128] Adjacent information entropy:

[0129]

[0130] Among them, x, y represent two variables to be analyzed, and H(x)·H(y) measures the uncertainty formula of the conditional joint distribution of two variables (such as commodities) within the time series window.

[0131] Confidence:

[0132]

[0133] Among them, F(x,y) represents the occurrence frequency of commodities A and B, and H(x,y) represents the adjacent information entropy.

[0134] S3, Automated and precise financial accounting;

[0135] In the profit accounting process, the recognition of revenue integrates the sales data of major e-commerce platforms, excludes invalid orders, refunded orders, special orders, and obtains the actual net sales amount. According to the occurrence time of the orders, the orders are divided into four dimensions: order placement time, payment time, shipping time, and confirmed receipt time to split the confirmed sales amount of the orders as revenue. The core revenue indicators include the amount of paid orders, refund sales amount, pre-shipment refund amount, post-shipment refund amount, and special order sales amount; The cost is confirmed from several dimensions, including the cost of the goods sold itself, the cost of packaging materials, and the cost of express delivery. The core indicators include the cost of paid orders, refund cost, pre-shipment refund cost, post-shipment refund cost, special order cost, manual adjustment cost, and additional cost. These aspects together constitute the cost;

[0136] Expenses are divided into online sales expenses and operating expenses. Online expenses include promotion expenses and operating expenses. Promotion expenses include Taobao promotion expenses, Douyin promotion expenses, Kuaishou promotion expenses, JD.com promotion expenses, etc. Operating expenses include operating promotion expenses, Douyin operating expenses, Kuaishou operating expenses, JD.com operating expenses, etc. All the above indicators are associated through the order number to achieve automatic profit calculation.

[0137] Step S3 includes:

[0138] S301. Revenue accounting, which is calculated at the ADS data application layer in step S2. Obtain the data of the sales order detail table in the DWS lightly aggregated layer, and summarize the sales amount according to the dimensions of order placement time, payment time, shipping time, confirmed receipt time, and store. The sales order filters orders with order statuses such as paid, in shipping, and shipped;

[0139] S302. Cost accounting, which is calculated at the ADS data application layer in step S2. Obtain the data of the sales order detail table and the product information detail data in the DWS lightly aggregated layer, and associate them through the product sku_id to calculate the total sales cost of each order;

[0140] S303. Express fee accounting, which distinguishes between standard packages and large items in terms of logistics methods. For standard packages, the express fee is directly obtained by multiplying the per-order quotation of each region in the express company's quotation table by the number of orders. For large item logistics such as furniture categories, the express fee consists of several aspects, including trunk line fees, branch line fees, distribution fees, and installation fees. Calculate the fees for each step and summarize them into one order to obtain the express fee;

[0141] S304. Promotion expense accounting, which is calculated at the ADS data application layer in step S2. Obtain the detailed promotion spending data of each platform in the DWS lightly aggregated layer, and summarize it to the store dimension to obtain the total daily promotion spending of each store;

[0142] S305. Operating expense accounting is calculated at the ADS data application layer in Step S2. Obtain the bill data of each platform at the DWS light summary layer, distinguish different subjects from the remarks column of the bill, calculate different operating expenses separately, including freight income, freight expenditure, and returned freight, and uniformly summarize them into the freight / logistics expenditure subject. The freight insurance handling fee is summarized into the freight insurance handling fee subject, the commission is summarized into the commission subject, and the label fee is summarized into the label fee. Distinguish all operating expenses into each subject and then summarize them to the store dimension.

[0143] The formulas used are as follows:

[0144] Gross profit = revenue - cost - promotion expenses - operating expenses;

[0145] Gross profit margin = (revenue - cost - promotion expenses - operating expenses) / revenue;

[0146] Through the accounting of the above various indicators and formula calculations, a complete e-commerce profit accounting statement is obtained.

[0147] S4. Scientific and reasonable offline expense allocation mechanism;

[0148] The allocation method in Step S4 is as follows:

[0149] S401. Allocate according to the usage area;

[0150] Applicable situation: For expenses related to space occupancy such as rent and property management fees, it is more reasonable to allocate according to the usage area.

[0151] Calculation method: First determine the total expenses and the total area, then calculate the allocation expenses per square meter, and then calculate the expenses to be allocated according to the area occupied by each department or project; for example, the office rent is 10,000 yuan per month, the total area is 200 square meters, and a certain department occupies 50 square meters, then the rent to be allocated by this department is 10,000 ÷ 200 × 50 = 2,500 yuan.

[0152] S402. Allocate according to the order volume;

[0153] Applicable situation: When the expenses are directly related to the order volume, such as the raw material procurement expenses and transportation expenses of an enterprise, they can be allocated according to the order volume.

[0154] Calculation method: Allocate the expenses according to the proportion of the order volume of different businesses or projects in the total order volume. For example, a certain factory produces two products, A and B. This month, the total raw material procurement expenses are 100,000 yuan. The output of product A is 1,000 pieces, and the output of product B is 1,500 pieces. Then the raw material expenses to be allocated by product A are 100,000 × (1,000 ÷ (1,000 + 1,500)) = 40,000 yuan, and the expenses to be allocated by product B are 100,000 - 40,000 = 60,000 yuan.

[0155] S403. Allocate according to the order amount;

[0156] Applicable situation: When the cost is directly related to the order amount, such as calculating the offline after-sales cost, calculate the allocation ratio according to the proportion of the sales of a single store in the total sales, and multiply the total after-sales cost by the allocation ratio to obtain the allocated amount;

[0157] S404. Allocate per capita;

[0158] Applicable situation: Applicable to situations where the cost is directly related to the number of personnel, such as office cleaning costs, group activity costs, etc.;

[0159] Calculation method: Divide the total cost by the number of participants to get the cost per person to be allocated. For example, when the company organizes a team-building activity, the total cost is 5,000 yuan and the number of participants is 50, then the cost allocated to each person is 5,000÷50 = 100 yuan;

[0160] S405. Allocate according to actual consumption;

[0161] Applicable situation: For costs that can be accurately measured in terms of consumption, such as water and electricity bills, office supplies expenses, etc., allocating according to actual consumption can more accurately reflect the cost burden of each department or project;

[0162] Calculation method: Allocate by installing metering equipment or counting the actual usage; for example, an office installs an independent electricity meter. This month, a total of 1,000 degrees of electricity are consumed, and the electricity price per degree is 0.5 yuan. If a certain department consumes 300 degrees of electricity this month, then the electricity bill to be allocated to this department is 300×0.5 = 150 yuan;

[0163] Through the above several allocation methods, the offline cost items can be well allocated.

[0164] S5. Data analysis and decision support. Through the analysis of the full-scenario package for refined e-commerce operations, it includes modules such as financial accounting across the entire channel, platform, and link, platform sales tracking, operation analysis, promotion investment tracking, special event promotion, live broadcast special analysis, commodity analysis, and cost analysis, helping enterprises conduct market insights and find directions for market growth; full-link data operation, full-process risk control, full-cycle decision support, assisting e-commerce operations; value orientation, deep integration, realizing the integration of business and finance; supply chain management achieving efficient collaboration, rapid response, and stable back-end support.

[0165] Another object of the present invention is to provide a system for business-finance integrated BI data analysis for e-commerce enterprises, and the system includes:

[0166] The data capture module is used to capture e-commerce platform data in an all-round and multi-dimensional manner; it connects to the third-party ERP system through the API interface to obtain merchant data, converts the data into formatted data, and then stores it in the ODS layer of the MySQL database. The data includes sales order data, return data, after-sales data, marketing promotion data, store data, purchase inbound, purchase outbound, product information, supplier information, warehouse information, etc.

[0167] The entire data module is used to integrate data to form a data warehouse layer, calculate the core indicator system, and model the data through the dimensional modeling methodology;

[0168] Data accounting module, used for automated and accurate financial accounting;

[0169] Cost sharing module, used for scientific and reasonable offline cost sharing mechanism;

[0170] Data analysis module, used for data analysis and decision support.

[0171] 1. The method and system of BI data analysis for integrated business and finance of e-commerce enterprises integrates the global data of e-commerce and establishes a standardized and customized underlying data warehouse model, which solves the problems of e-commerce enterprise data being scattered in multiple systems, data being inconsistent, data acquisition being difficult, inefficient, and highly dependent on manual operations.

[0172] 2. Standardized e-commerce data analysis methodology, starting from the source of data collection, clarifies the access standards for various types of data on different e-commerce platforms. Whether it is transaction order information, user browsing tracks, or product inventory dynamics, they are all collected in a unified format and frequency to ensure the integrity and accuracy of the data. In the data cleaning stage, eliminating duplicate, erroneous or incomplete data according to established rules is like laying a solid foundation for subsequent analysis.

[0173] The analysis process is the core of this methodology, which covers multi-dimensional analysis. On the one hand, from the user dimension, through standardized clustering and association analysis, accurate insights into the consumption preferences and purchase cycles of different user groups can be obtained, thus providing guidance for precision marketing; on the other hand, research is conducted on the product dimension, comparing the sales trends and evaluation feedback of similar competing products, helping merchants optimize product selection and product improvement.

[0174] Furthermore, standardized analysis based on time series can clearly present the seasonal fluctuations and long-term growth trends of e-commerce business, allowing companies to plan marketing strategies and allocate resources in advance.

[0175] Finally, the standardized e-commerce data analysis methodology also emphasizes the standardization of result presentation, with intuitive and easy-to-understand charts and reports output, so that people at all levels of the company can quickly understand the data content, make wise decisions based on it, and promote the continued steady development of the e-commerce business.

[0176] 3. A scientific and reasonable offline cost allocation mechanism makes profit accounting more accurate. For e-commerce enterprises, offline operations cover many aspects, from the efficient allocation of warehousing and logistics, to the daily operation and maintenance of physical stores, and then to the vigorous promotion of offline marketing activities. Each item incurs substantial expenses. When an enterprise constructs a scientific and reasonable offline cost allocation mechanism, it is like building an accurate "balance" for profit accounting.

[0177] Taking the warehousing and logistics costs as an example, the storage requirements of different categories of goods vary. Fresh products require refrigeration and preservation equipment, and high-value electronic products have strict requirements for moisture-proof and shock-proof conditions. Through accurate cost allocation, based on multi-dimensional factors such as the size of the storage space occupied by each category of goods, the storage duration, and the frequency of logistics distribution, the warehousing and logistics costs are reasonably allocated to each order and each product. In terms of the operation and maintenance of physical stores, expenses such as rent, decoration depreciation, and staff salaries are allocated according to the passenger flow and sales proportion of each area of the store, so that every expense can find the corresponding profit source. The same is true for the expenses of offline marketing activities. According to the traffic conversion and sales volume increase ratio brought by the activity to different product lines, the expenses are fairly divided.

[0178] In this way, the costs of each link can be clearly attributed, enabling the final profit accounting to get rid of ambiguity and errors, more accurately reflecting the business conditions of the enterprise, providing a solid and reliable basis for the enterprise's strategic decision-making, and helping e-commerce enterprises move forward steadily in the market tide.

[0179] 4. Report automation, improving business operation efficiency, enhancing the accuracy and timeliness of report data. In the traditional report-making process, data collection often relies on manual operations, where data is screened and entered one by one from various business systems and departmental documents. This process is not only time-consuming and laborious but also highly error-prone. However, after implementing report automation, the situation is completely different. With the help of advanced information technology and intelligent software tools, the system can automatically connect to the scattered data sources within the enterprise. Whether it is the order details in the sales system, the revenue and expenditure accounts in the financial system, or the material in-and-out records in the inventory management system, they can all be captured quickly and accurately. When an e-commerce enterprise used to manually create monthly sales reports, it needed to coordinate with the operation personnel of each store to submit data, and then the headquarters' financial staff would summarize and verify it. The entire process often took several weeks and frequently had data inconsistency issues. After implementing report automation, the system automatically extracts key information at the moment each sales order is completed and updates it in real-time to a unified database. At the end of the month, with just one click, an accurate sales report covering detailed content such as regional sales performance, product category sales, and customer growth trends can be generated immediately. What used to take several weeks of work can now be shortened to a few hours. This efficient data integration and report generation method greatly improves business operation efficiency. On the one hand, enterprise management can promptly gain insights into market dynamics, quickly respond to changes, flexibly adjust marketing strategies based on the latest sales data, and optimize procurement plans in real-time according to the inventory report. On the other hand, report automation fundamentally enhances the accuracy and timeliness of report data. The automated process reduces data entry errors and calculation biases caused by human factors, ensuring that every piece of data is true and reliable. At the same time, the real-time update of data enables decision-makers to always grasp the latest pulse of enterprise operations, making decisions that are more forward-looking and scientific, comprehensively helping the enterprise stand out in the fierce market competition and achieve leapfrog development.

[0180] 5. Building an enterprise-level data analysis framework and security control, creating an enterprise data asset platform. Building an enterprise-level data analysis framework is by no means an easy task, which requires in-depth planning and meticulous construction from multiple dimensions. First of all, it is necessary to comprehensively integrate the massive data resources inside and outside the enterprise and break the data silos between departments. Inside the enterprise, the data generated in different business processes such as R & D, production, sales, and after-sales are very different. The R & D department has product experiment data, technical parameters, etc., the production department covers equipment operation data, material consumption records, the sales team has customer purchase behavior and market demand dynamics, and the after-sales department stores customer feedback and maintenance information. Through unified data interfaces and standardized data format definitions, these scattered data are gathered into the same data pool, laying a solid foundation for subsequent analysis.

[0181] Meanwhile, a set of analysis model systems adapted to the enterprise's strategic goals and business characteristics is crucial. For market trend prediction, models are constructed using time series analysis, machine learning algorithms, etc. to accurately insight into future market trends, helping enterprises plan ahead for new product R & D and adjust production capacity; in the field of customer segmentation, clustering analysis is used to segment customer groups based on dimensions such as customer consumption habits, geographical distribution, loyalty, etc., so that enterprises can carry out personalized marketing and improve customer satisfaction and loyalty. Moreover, the use of visualization tools can present complex data conclusions in the form of intuitive and easy-to-understand charts to management and business personnel, making data insight clear at a glance. This platform is not only a data storage warehouse but also the brain center of the enterprise's intelligent decision-making. Management can log in to the platform at any time to obtain comprehensive business insights and adjust the enterprise's strategic direction based on accurate data indicators; business personnel can conveniently query the required data on the platform, optimize their daily work processes using data analysis results, and improve work efficiency; for the enterprise as a whole, relying on the data asset platform, it can continuously incubate innovative business models, explore new profit growth points, stand firm in the ever-changing market competition, and achieve sustainable and vigorous development.

[0182] It should be noted that in this article, the term "including" or any other variant thereof is intended to cover non-exclusive inclusion, so that a process, method, article, or device including a series of elements not only includes those elements but also includes other elements not explicitly listed, or further includes elements inherent to such a process, method, article, or device. Without further limitation, an element defined by the statement "including a..." does not exclude the existence of another identical element in the process, method, article, or device including that element.

[0183] The above are only the preferred embodiments of the present invention and do not limit the patent scope of the present invention. Any equivalent structural or equivalent process transformation made using the specification and drawings of the present invention, or directly or indirectly applied in other related technical fields, shall be similarly included in the patent protection scope of the present invention.

Claims

1. A method for analyzing BI data of an e-commerce enterprise in an integrated manner, characterized in that: The method comprises: S1. Capture e-commerce platform data in an all-round and multi-dimensional manner; connect to the third-party ERP system through the API interface to obtain merchant data, convert the data into formatted data, and then store it in the ods layer of the MySQL database; S2. Integrate data to form a data warehouse layer, calculate the core indicator system, and model the data using the dimensional modeling methodology; S3, automated and accurate financial accounting; S4. Scientific and reasonable offline cost sharing mechanism; S5. Data analysis and decision support.

2. The method for BI data analysis of e-commerce enterprises according to claim 1 is characterized in that: Step S1 includes: S101. Register in the open platform and apply for business permissions. Before calling the API to request data, you need to register in the open platform. After registering successfully, you will get the application key and secret key assigned by the platform. After the API interface permissions applied by the ISV / developer are approved by the open platform, you will get the corresponding API permissions. S102. Based on the open platform authorization code, configure the connector. The connector is a server connection information tool that is exclusively configured by the user. It adds and isolates the data sources of the development environment and the production environment respectively to protect data security. According to the specific situation, the user selects three different environments, namely production env_production, test env_test, and development env_development. After completing development and testing, the connector environment must be switched to the production environment before formal operation. S103, Added integration solutions. The integration solution is the core of the entire API interface integration platform. Each integration solution represents a business docking strategy. Users can create multiple integration solutions with different rules according to different businesses, including: purchase order synchronization, online sales delivery synchronization, offline sales delivery synchronization, online sales order synchronization, online after-sales return and refund synchronization, online product information synchronization and online inventory information synchronization; S104, Integration Solution - Source Platform Configuration, Details Configuration Page, the details page contains two major areas, namely the header area and the sub-tab area; the header area mainly displays basic information; S105, Integration Solution - Target Platform Configuration, Detailed Configuration Page, fill in relevant information and complete the target platform configuration; S106, start the plan & timing strategy configuration.

3. The method for BI data analysis of e-commerce enterprises according to claim 2 is characterized in that: In step S1, a multi-layer recursive authorization verification algorithm is also introduced: this algorithm recursively decomposes the requested authorization code into multiple sub-nodes, and verifies the sub-nodes of each layer one by one through a multi-layer authorization verification model. Its core principle is to build a permission dependency directed acyclic graph, and use depth-first search to recursively traverse each node in the authorization chain to ensure the permission inheritance relationship of each authorization node; Through third-party business authorization, after obtaining merchant authorization, ISV / developer obtains merchant data within the authorized scope to complete related business processing or application development, and needs to be initiated by ISV / developer, or ISV / developer adds corresponding functions in its own application and the merchant completes the authorization behavior; After the authorization is agreed, the ISV / developer can use the API to request the merchant's relevant data. When authorizing third-party services, the ISV / developer needs to assemble the authorization URL. After each successful authorization, the ISV / developer will obtain a business authorization code to exchange for the authorization token access_token; A dynamic weighted average token generation algorithm is also introduced: In the process of obtaining the authorization token access_token, a dynamic weighted average token generation algorithm is adopted, which aims to dynamically generate the optimal token through the weight distribution of historical requests. The steps include normalizing the weight values ​​of historical requests, and then generating a new token based on the weight value of each request. The formula is: Token=Σ(w_i / Σw_j)×request_i; Among them, w_i represents the weight corresponding to the i-th historical request, w_j represents the weight corresponding to the j-th non-historical request; request_i represents the feature vector corresponding to each historical request; n represents the number of requests.

4. The method for BI data analysis of e-commerce enterprises according to claim 1 is characterized in that: Step S2 includes: S201. Data domain division: data is divided into different data domains according to different subjects or business functions. Each data domain contains relevant data of a specific subject, including sales orders, return and refund sales, after-sales processing, warehousing, procurement, warehousing and outbound delivery; S202, construct a bus matrix to clarify the data domain to which the business process belongs and clarify the relationship between the business process and the dimension; S203, standardize definition, define the indicator system; S204, model design, detailed model design, building consistent dimension tables, building consistent fact tables, summary model design, building common summary models, and building application summary models; S205. Code development, deployment and operation and maintenance.

5. The method for BI data analysis of e-commerce enterprises according to claim 1 is characterized in that: Step S3 includes: S301: Revenue accounting: obtain sales order details table data, summarize sales according to order time, payment time, delivery time, delivery confirmation time and store dimensions, and filter sales orders with order status of paid, shipping and shipped; S302, cost accounting, obtain the sales order details table and product information details data, associate through the product sku_id, and total the sales cost of each order; S303, express delivery fee calculation, distinguish between standard and large-scale logistics. For standard-scale logistics, the express delivery fee is calculated by multiplying the quotation of each order in each region by the number of orders in the express company's quotation table. The express delivery fee for large-scale logistics consists of several aspects, including trunk line fees, branch line fees, distribution fees and installation fees. The fees of each step are calculated separately and aggregated into one order to get the express delivery fee; S304: Calculate promotion expenses, obtain detailed data on promotion expenses on each platform, summarize them in the store dimension, and obtain the total promotion expenses of each store every day; S305. Calculation of operating expenses. Obtain billing data from each platform. Distinguish different items from the notes column of the bill. Calculate different operating expenses separately and summarize them into the freight / logistics expense item. Freight insurance and handling fees are summarized into the freight insurance and handling fees item. Commissions are summarized into the commission item. Label fees are summarized into label fees. All operating expenses are divided into each item and then summarized into the store dimension.

6. The method for BI data analysis of e-commerce enterprises according to claim 1 is characterized in that: The allocation method in step S4 is as follows: S401, apportionment based on the area of ​​use; Applicable situations: Expenses related to space occupancy are apportioned based on the area used; Calculation method: first determine the total cost and total area, then calculate the apportioned cost per square meter, and then calculate the apportioned cost based on the area occupied by each department or project; S402, apportionment by order quantity; Applicable situations: When the cost is directly related to the order volume, it can be allocated according to the order volume; Calculation method: Allocate the cost based on the proportion of the order volume of different businesses or projects to the total order volume; S403, apportionment according to order amount; Applicable situations: When the fee is directly related to the order amount, the apportionment ratio is calculated based on the proportion of a single store's sales to the total sales, and the apportionment amount is obtained by multiplying the total after-sales fee by the apportionment ratio; S404, apportionment by number of persons; Applicable situations: Applicable to situations where the cost is directly related to the number of personnel; Calculation method: Divide the total cost by the number of participants to get the cost per person; S405, apportionment based on actual consumption; Applicable circumstances: For expenses that can be accurately measured; Calculation method: Apportionment is done by installing metering equipment or counting actual usage.

7. The system for BI data analysis of e-commerce enterprises is characterized by: The system comprises: The data capture module is used to capture e-commerce platform data in an all-round and multi-dimensional manner; it connects to the third-party ERP system through the API interface to obtain merchant data, converts the data into formatted data, and then stores it in the ods layer of the MySQL database; The entire data module is used to integrate data to form a data warehouse layer, calculate the core indicator system, and model the data through the dimensional modeling methodology; Data accounting module, used for automated and accurate financial accounting; Cost sharing module, used for scientific and reasonable offline cost sharing mechanism; Data analysis module, used for data analysis and decision support.

Images