Approach for building a business intelligence platform for retail companies
Let us introduce our new series of blog posts where you can discover how to build a system and a business intelligence platform that consists of streaming, real time analysis, predictive component, batch processing, and reporting. We will dive into our approach to a new challenge where sales data plays the leading part. After winning Croatia osiguranje & Bird data challenge, we started looking for another challenge that we could learn from and have some fun. We targeted challenges with larger datasets from the real world and preferably from industries like supply chain, retail, insurance, and finance. Applicability and meaningfulness were important factors – the challenge has to have some purpose and the potential to turn into a product. After some research we’ve found the M5 data challenge that consists of two related competitions: Estimate the unit sales (we will focus on the first competition) Estimate the uncertainty of sales The competition is very popular, in this edition there were more than 7000 competitors. In the series of blog posts, we will talk about our spin-off project based on this data competition through technology and business topics. The original competition and spin-off The task of the original competition is to estimate the unit sales for 3049 products in 10 Walmart stores based on the 5+ years of sales data. The test set contains 28 days, that is 10 x 3049 x 28 = 853720 predictions. We have modified the task to get the most out of the dataset. The first thing that came into our minds was the streaming of sales events. Each sale can be represented by an event in Kafka. The dataset is static (a CSV file) so we’ll need some kind of generator process that will be used by a Kafka producer. Sales data is grouped by date, nevertheless, we can simulate the real world by applying appropriate distribution of sales to data and generating events w.r.t. that distribution. Stream of sales events can be analyzed and visualized in real-time by Apache Spark or KStream applications and visualization tools. There is also room for real-time notifications and alerts, for instance, if the sales of an item are high we can recommend restocking. The original dataset does not contain warehouse data, but you could generate it and implement warehouse management features. We have some experience in data generation in the insurance domain, so we know that data generation is tricky and requires a lot of time to do properly. That’s why this is on our nice-to-have list. Streamed data is persisted in a data warehouse where additional batch analysis, reporting, and predictions (the original task!) can be done. Here we can use Apache Spark, Apache Airflow, Python ML libraries, and visualization tools. You see how many additional tasks you can get from this dataset. It’s the exact thing that we were looking for! Data overview The purpose of this section is not to analyze data in detail but to give you a quick overview of data so you can follow the rest of the post easily. Data is organized into files: calendar.csv – contains information about the dates on which the products are sold; sales_train_evaluation.csv – contains the historical daily unit sales data per product and store for 1941 days. It contains the same data as sales_train_validation.csv with additional 28 days used for testing purposes; sell_prices.csv – contains information about the price of the products sold per store and date. The figure shows the hierarchical organization of the dataset which is divided into levels: state – store – category – department – item. The calendar contains a list of all 1941 dates and information about special events like sports and cultural events, national holidays, and religious holidays which can have an effect on sales. This is important because some holidays can change dates so forecasting algorithms can not rely just on date information. For instance, Christmas trees and decorations will sell a few weeks before the 25th of December each year, but chocolate Easter bunnies will have peak sales in different weeks or even months depending on the year.Sales data contains daily sales of each item for all stores. The figure below shows the histogram of sales on a store level. The highest sales numbers happen in the CA_3 store, and the same store has the largest sales variance. We can see that stores in Texas and Wisconsin have lower sales variance and that those stores hit comparable sales numbers. Data can be analyzed on a more detailed level, for example per category. The following figure shows the sales of food products. Each line represents a year, and you clearly see the seasonality in the data. Sell prices data contains weekly average prices for each product and store. Prices for the same item vary depending on the store, which makes sense as some states have higher standards than others, and some items have higher shipping costs to certain areas. Here is the example for the first item in the dataset whose price also varies between states, but also within a state (look at the WI_3 store). Store Price CA_1 1276.00 CA_2 1256.84 CA_3 1265.10 CA_4 1277.32 TX_1 1270.10 TX_2 1256.84 TX_3 1265.48 WI_1 1282.60 WI_2 1280.72 WI_3 1248.46 Price comparison for HOBBIES_1_001 item For some items we have detected outliers which we marked as bad data. For instance, there are products for which the price drops to $0.01. It is too cheap for any item, and even if an item of a $0.01 value exists there is another thing – for most of them the sales numbers also drop to zero so we conclude that represents an out of stock situation. However, there are cases when sales numbers do not drop to zero which confuses us a bit, that could be bad data caused by errors in the process. Architecture overview In this section we’ll briefly explain components, data flow, and main tasks. We divided work into three main components: Datasources preparation Streaming and stream processing Batch