From margin calculation to order planning: data as the basis for decisions in eCommerce (eCommerce Data & Decisions Part 1)

Standardise data from the various sales platforms. And why it's not as easy as it sounds.

The project description was very simple at the beginning: load the billing data from the various platforms (Shop/JTL, Amazon, eBay, Kaufland, Otto) and calculate the net margin per sales platform and product. It quickly became clear that it wasn't that simple. Although all the data is available, it is in completely different formats, granularity and information content.

A short-term solution would have been to load all the data, run logics and calculations on it and add up the results. However, this solution would not have been very sustainable and would have required a lot of maintenance, as all adjustments to the platforms - and these happen very frequently - would have to be repeatedly adapted in code and logic. Furthermore, the comparability of the products for the entire company would have been lacking, there would have been no historisation and the entire application would have been tailored to a single use case.

A data warehouse and data model that standardises all data

So how could we meet the challenge and, instead of developing a customised, high-maintenance and isolated application, create a long-term comprehensive solution that enables multiple use cases? We developed a data warehouse that taps into individual sources such as merchandise management, sales channels, shops and accounting, loads the data, processes it and makes it available in a standardised data model.

A standardised data model first had to be created. All sources have similar data, but are structured differently and have different entities and levels of granularity. For example, offers on Amazon are magically linked and displayed as different options on an offer page, e.g. different colours of a product that can be selected on an offer page. Each colour is a separate offer. On eBay, on the other hand, this is solved differently and these differences had to be brought together.

The standardisation of the data offered further potential. Previously, our customer had the challenge of dealing with almost 3.700 master items that were split across 20.500 platform listings. It became even more complicated as the offers were managed via different parts lists which were then broken down to the master articles. It was not possible to analyse which article was sold how often via which platform using the merchandise management functions. However, the standardised data model for eCommerce, in which all sales were broken down to the master item, suddenly created completely new possibilities.

New possibilities: From profitability at product level to forecasting for purchasing

The database that is now available enables numerous use cases in sales, product management and purchasing. As not everything can be implemented at once, the topics with the greatest benefit for eCommerce retailers have been identified.

Sales analysis

• Products/master items/product groups and their sales on the individual platforms and for the entire company
• Identification of articles and offers that require revision
• Evaluation of the success of marketing measures on sales figures
• Monitoring of critical articles and information in the event of deviations from targets

Profitability and margins

• Profitability of individual items, products and product groups and identification of cost drivers
• Calculation of marketing budgets per item based on available contribution margins and margins
• Identification of products with high profitability but low sales in order to realise their potential

Forecast

• Forecasting sales at supply level, taking into account seasonal fluctuations and article trends
• Derivation of merchandise requirements to serve sales planning
• Forecasting of stock levels based on requirements

Purchase

• Forecasting goods requirements and stock levels enables future purchases to be planned
• Strategic price negotiations with suppliers based on demand forecasts
• Order proposals based on stock development and goods information, e.g. delivery time, from merchandise management

Outlook for future contributions

We have already done a good bit of work, have the basics of the data model and are working on the first use cases. There is still a long way to go before all goals are achieved and further goals and challenges will be identified along the way.

In the coming months, we would like to take you on this journey and go into depth on various topics, explain the goals and challenges and outline the solution. A brief preview of the topics we want to cover in more detail

• Setting up your own data warehouse
• A standardised data model for eCommerce
• Margin calculation from offer to article
• Forecast for sales, warehouse and purchasing
• Order proposals and order planning in purchasing

What would you like?

Are you curious? Do you have similar challenges?

Let us know what you think of our approach and what topics you are interested in and what we should address. And if you have completely new problems, then let us know and we can discuss possible approaches for a solution together.

For your input and feedback, simply write to me at robert.kramer@esveo.com