Development of a Data Warehouse in eCommerce with margin analysis and sales insights

  • Connection of data from various source systems such as marketplaces, stores, retail management and accounting
  • Fully automated data processing & standardization of data structures
  • Unified logic for evaluating the billing data of different marketplaces and store systems
  • Profitability benchmark by calculating sales, margins and earnings of products and marketplaces
  • Early warning system for detecting sales slumps and changes in product ranking
Customer
McFilter
Field of Work
eCommerce
to Website
Technologies
Node.js
TypeScript
React
MySQL
RabbitMQ
REST
OpenAPI
Web Sockets
OAuth
Docker
Micro Services
Unimatrix Preview

Standardized data in eCommerce, for more comparability and quick response

Transparency about turnover, sales, contribution margins and profit margins is irreplaceable in e-commerce. With the Unimatrix project, we have created a standardized data basis with which retail management, accounting and marketplaces are connected to create transparency about relevant company key figures and enable a targeted product and pricing strategy. In addition to dashboards for the daily development of company key figures, detailed analysis options are available to examine sales, costs and margins down to product level.

Challenges and solutions

  • With over 20,000 products and variants, transparency is quickly lost
  • Uncertainty as to how high margins are for individual products, especially for various marketplace fees
  • High advertising costs make individual products unprofitable
  • No analyses of sales and profitability across multiple sales channels possible
  • Import data from all relevant marketplaces, stores, retail and accounting systems
  • Standardized eCommerce data model for merging different marketplace logics
  • Fully automated merging and storage of original data.
  • Development and application of calculation logics and providing pre-calculated key figures for analysis tools and third-party applications
  • Data quality rules and early warning system for valid error and deviation analyses
  • Event-driven, near-real-time processing for immediate updates and constantly up-to-date key figures
  • About the costumer

McFilter is a medium-sized eCommerce company that manufactures its own products worldwide and sells them all over Europe via marketplaces and its own stores. In addition to in-house product development, shipping and logistics planning are also managed by the company itself.

Desktop application with cloud technologies and synchronization with cloud servers

Focus-Technologies

Image
Node.js
docker-logo-mark-blue
Docker
Image
React

Other Technologies

TypeScript
MySQL
WebSockets
RabbitMQ
REST
OpenAPI
OAuth
Micro Services

Technological setup

Event-based near-real-time setup (RabbitMQ Message Queueing System). Changes in the evaluations only seconds/minutes after new orders in stores

Landing zone tables for importing raw data from upstream systems (Amazon, JTL, Ebay, Kaufland, Otto, Excel)
Data vault tables for a standardized data model with centralized calculation of key figures (margins, contribution margins, etc.)
Consumption tables for high-performance data preparation for the UI

Technical infrastructure at the customer

The source of the measured values are computers installed on the machines. These were processed in our backend in the customer's virtualized Windows environment and distributed to the engineering offices in the form of predefined Excel reports

Image