How to determine KPIs for a Retail BI Platform

How we determined KPIs for our Retail BI Platform

Share on

When talking about KPIs we must understand their applicability and what they actually mean. In any data science and data engineering project, the determination of KPIs, or rather metrics and insights, is an important step. How would you know what to measure and what can you actually do with all that data if you don’t define which values you need and want to show?

When working on our retail business intelligence platform, we first needed to understand the industry and data itself. Which results are important for retail? How do they measure success? Which data is actually available to us?

In the end, each value represented something, and we needed to analyze and understand what that something is.

How to approach data

It was easy seeing data for what it is. A series of values over time. And when you look at certain values, they represent an action. In our case, we had sales quantity, price per SKU, location, date, product ID, department, and product category. The data set told a story of retail traffic and consumer spending. If observed as individual values, they can be easily interpreted. But, we wanted to create a solution that will draw extra benefits and explanations derived from the original data. Our aim was to use this data set to bring value to more than one department or cost and profit center. We didn’t want to see data through a singular explanation. Certain numbers can speak to a larger variety of people and departments. One metric can influence decisions and actions in more business operations and procedures.


Since the dataset came originally from a data challenge, our task was to estimate unit sales. So, if we have retail data and SKU analysis, we needed to start from that. The question we asked ourselves was what is important in a simple SKU analysis? What does our data tell about SKU performance? And from our data set, the obvious answer was sales. What is the level of revenue per SKU is a pretty straightforward metric. We can now observe which SKUs are better performing and what level of revenue they can bring to a retail store or location.

KPIs through multiple variables and dimensions

But, this one metric won’t suffice. We needed to observe values across more than one attribute or variable. And we didn’t want to focus just on static data. Our goal was to be able to predict future SKU movements. So, it was time to develop a matrix where we could distribute our data to outline basic metrics our solutions should show and continuously calculate.

Metrics matrix

Table 1: KPIs through multiple variables and dimensions

Individual SKUs, if observed across time and locations, tell a variety of stories. For example, one product can have high sales in one location and low in another. This tells us that there are possibly different segments of consumers. They can differentiate based on income, culture, age, consumer preferences, upbringing, etc. But it can also be an indication of different marketing and sales effort in this certain store or area. Or even perhaps, this SKU has varied in stock in different stores. There could’ve been backlog or overstocking. Maybe there was a new trend in that location that stimulated the attractivity of that particular product. So, as we can see, one metric serves multiple explanations and applications. And that’s why this matrix was developed. These SKU KPIs can be compared across multiple variables or dimensions.

This is also a basis for the streaming component of this software solution. These metrics needed to be available to users in real-time, so they can access them anytime for their reporting and instant reactions to unexpected changes. Our objective was to allow users to use data in accordance with their needs, so they wouldn’t need to wait until month-end reporting to see their retail performance. Also, based on those simple metrics and SKU analysis the solution can send real-time notifications and present them to users. Of course, it’s not only values but through visualizations, users can more effectively interpret results and share them with other stakeholders. 

What we can take from this approach is that even the simplest metric analyzed through more than one dimension can provide insights and explanations for numerous events. 
What we must pay attention to are restrictions derived from the chosen technology. We can imagine and define KPIs and metrics, but sometimes data doesn’t support them. And the chosen technology can offer limitations in executing calculations. Not every value can be turned into an impactful KPI. And not every value should be measured. The business requirements have to be aligned with the data science and data engineering side of the process.

KPIs that tell a story

After the matrix and simple KPIs validation, we turned our focus to more complex KPIs. We wanted to see which KPIs are possible to calculate if we have only certain values. For example, if we have units sold and a price of SKU, what can be calculated from that? And of course, the KPI had to make sense to the end user. 

This is why we decided to create another table that outlined our major KPIs across periods of time and location since that was our main filtering determinant.

Metrics and KPIs

Table 2: Major KPIs groups across different locations and time

Revenue indicators

Our first KPI is sales or revenue a certain SKU can bring. We observed it across 5 different levels (from product level to cumulative stores level) and 6 time dimensions (from one day to all time or the whole period for which we have data). Sales or revenue KPI is calculated as sold quantity x price per SKU. This KPI answers the question of which SKUs are outperforming or underperforming others. It’s an indication of which ones play a bigger role in sales performance. 

When also looking at revenue per unit, we can get so many insights about products sold in the store. We can answer questions like:

  • What is the revenue per certain SKU?
  • Which SKUs, departments, or stores generate the most revenue?
  • Which SKUs, departments, or stores don’t generate enough revenue?
  • Are certain products sold more in certain areas than others?
  • Where is the higher demand for certain SKUs?
    • Can this be a basis for segmentation (if certain areas buy more products or categories of products, can we assign that to their behavior and attributes)?
  • Should certain products be discontinued from sales?
  • For which products should stores increase or decrease stock?
Market share

The next one is market share. It’s not an indication of a true market share compared to competitors, but rather a share in sales in certain stores or locations of one retailer. So, based on sales of certain products across different locations we can determine which SKUs bring the most profit or share. We can see what is the share of certain products inside one department or even in the whole assortment. Perhaps some product categories outperform others in certain locations which can be an alert for warehouse management, marketing, and sales. This KPI can be observed on the basis of an individual SKU share across multiple levels, or cumulative sales of more SKUs across those same levels. 

With this indicator, we can provide answers to these questions:

  • Based on sales of certain products across different locations can it be determined which SKUs bring the most profit or share?
  • We want to compare products from the same category and store to see which ones outperform others
  • Are reasonably priced products better performing than expensive ones? Can this be a price sensitivity base for segmentation and for defining the buying power of customers?

Shares don’t have to be observed only through sales. An important aspect is quantities sold which are integral for demand planning. Share of SKU in quantities sold is a KPI that follows supply chain management, and it provides information to:

  • Which SKUs have the most demand?
  • Can this be an alert for warehouse management and stock handling?
  • Which products need to go through a phase-in or phase-out in demand planning?
Price elasticity of demand

One of the KPIs we wanted to include is the price elasticity of demand. It’s a well-known economic indicator that shows how sensitive customers are to price changes

Some questions this KPI can answer are:

  • What is the optimal price range for certain products?
  • Does the price have any influence on the sales/demand of an individual SKU?
  • Does price movement through time influence sales for that period?
  • Does the SKU have substitutes in other products?
  • How much is the price per SKU deviating from the product category average (issue of luxury vs low-budget brands)?
Growth over time

We also want to keep track of sales growth through our set time dimensions. By calculating this we can observe how much our sales numbers have grown from one period to the other. It’s an indication if the company’s goals were met. This is an important metric that shows if products do have a steady growth rate or if the demand is in decline. This metric can provide explanations to questions like:

  • Which SKUs, departments, stores, or locations generate the most sales based on periods?
  • Is this an indication of the seasonality of certain SKUs?
  • Which SKUs show a certain growth correlated to market and consumer trends?
  • Is our retail store maintaining steady growth in sales/revenue?
  • What is the rate of revenue growth based on the previous period for individual SKUs, departments, or stores?
  • Are SKUs, departments, stores, and locations profitable enough?
  • Is the current growth aligned with the company’s strategic goals and forecasts?
Price premium

Another interesting KPI in retail is price premium. A lot of product performance is linked to the price or cost. Are customers willing to pay more than the average price for certain products? Are certain SKU brands stronger than other brands? Which SKUs can be marked as premium products? These are all valid questions that show how much customers place importance on consuming some brands or products.

Simply, do more

All of the KPIs, that we’ve determined could be of value, are the representation of how SKUs perform in retail on multiple levels. A user could see all the data as we did and make his own conclusions, but our dashboard aims to outline the important ones and do the job for them. Retail can generate vast amounts of data on a daily basis and it’s not an easy task to solve the issue of bad data. It’s also not an easy task to transform the values into cohesive and understandable metrics that tell a story. With our approach, we wanted to automate it and set up the streaming to show current values at any given time and not after the fact.


It would’ve been pretty straightforward to analyze data through singular values, but over time it wouldn’t be enough to make informed decisions. Since market conditions change instantly, consumer habits do the same. Retail is extremely dynamic and performance results should be reevaluated continuously. KPIs need to be determined following that same principle. If the data set grows and new variables are introduced, new KPIs will be added. Retail analysis can influence warehouse management or even production management if the data extends to those departments.

It’s all about the value

Certain aspects need to be covered when discussing KPIs for any data science and engineering project. It’s not always as easy as one, two, three. We’ve briefly covered the process behind KPI formation in a short LinkedIn article, and we used the same guide in this project. We knew of limitations in the data set and in the technology as well. As we mentioned before, not everything needs to be measured and not everything can or has to form a KPI.


We can mix and match data, but what’s important is that we discover which parts of data will actually bring value to the user. Also, it’s vital to see if data can be continuously generated or collected. As in any statistical analysis, if the sample isn’t large enough, the results won’t be statistically significant. We want for data to continue coming in, for us to create forecasts and apply methods where results become more accurate with larger data intakes.

Read all our posts in this series:

Intro: How to turn SKU analysis to your advantage

Part 1: Approach for building a business intelligence platform for retail companies

Part 2: How to setup a Kafka Streaming project

Part 3: Create a powerful streaming application with Apache Spark

Stay Connected

More Updates

Croatia
Zadarska 80, Zagreb
HR62004514019
info@digitalpoirots.com

© 2022 DigitalPoirots.com | Deegloo.com