FASHION DATA ANALYTICS: Sales Metrics & KPIs
A demonstration of how to use Tableau to calculate ten sales KPIs using Z&C fashion retailers as a case study.
Introduction
The main point of data analytics is to help businesses make decisions, and we can’t give good advice without looking at the metrics that are important to the business or project in question. Whether we start with the business objectives or the research questions, our findings have to make sense and be actionable.
Before I dive deeper, let me hint at the blog outline.
SECTION 1: Overview of sales performance KPIs
SECTION 2: Difference between sales performance metrics and KPIs
SECTION 3: Calculating sales performance KPIs in Tableau
Data Source: 365 Data Science.
Interact with the dashboard on Tableau Public
SECTION 1
What are sales performance KPIs?
Meet Toke, a fashion analyst at K&C Retailers. Her manager has requested a sales report on the company’s sales from 2019–2020, be presented at the forthcoming end-of-year meeting.
Before Toke begins to create her report, she needs to search for the answers to the following questions;
- What was the company’s goal for those years?
- What was our sales strategy?
- Where did our data come from?
- How did we track and measure the company’s growth with respect to sales? e.t.c
The answer to the last question above helps Toke and the rest of the sales team measure how well important sales activities are doing. These activities are often related to some measurable data points that are referred to as “key performance indicators."
The name “Key Performance Indicators” (KPIs) comes from the fact that they are the “key” numbers that show if a company is making progress toward a certain goal.
Psst…metrics and KPIs are often used interchangeably, but read on to know the difference between both terminoligies.
SECTION 2
What’s the difference between metrics and KPIs?
Metrics are any measurable data relevant to a company’s performance, such as the number of conversions, the total value of a customer, the average time it takes to close a deal, revenue, the number of deals closed each month, etc.
KPIs, on the other hand, are a subset of metrics that are carefully chosen to help measure how well a company’s actions are moving toward specific company goals and objectives. KPIs are more focused and they are used to answer questions such as ;
“Are we on track to meet our sales targets?” or “What metrics are the key drivers of our sales success?”
For instance, If K&C business goal was to increase revenue by 20% in 2020,some relevant metrics to track include; discount, number of sales,purchase frequency and customer life time value (LTV). However, tracking these metrics alone does not help measure the success of the sales activities and what matters is how these metrics influence the business goal.
In addition, if statistical analysis reveals that customers that respond to discount are likely to make more purchases and increase their customer LTV,which in turn,increases the company’s revenue and vice versa. Then, customer LTV and discount become sales KPI.
In other words, a metric is anything that you track in your business. However, only a few of these metrics are directly related to your main business goal, making them key performance indicators.
Bottom line, all KPIs are metrics, but not all metrics are KPIs.
Are you clear on my explanations? For more information, please check this KPIs and Metrics article by Datapine.
Before we jump into Tableau, let’s see some of the performance KPIs Toke selected for her sales report.
Grab a cup of hot chocolate, and let’s do this!
Below are some KPIs Toke selected for this project:
- Total Sales by Product Category
- Number of Monthly orders
- Customer Lifetime Value
- Net sales
- Purchase Frequency (refer to the completed dashboard for the full list.)
SECTION 3
In this session, I will walk you through how to calculate ten sales KPIs, and you can check the final dashboard for the other KPIs Toke walked on. Feel free to either read through and replicate afterwards, or you could just work alongside the tutorial.
Ready to dive in?
Me too!
Calculating performance KPIs in Tableau
About the Dataset:
The dataset used in this tutorial was from 365 Data Science. It consists of both consumer and product information, including demographics,customer life time value, year of purchase, product category, line production SKU, etc. The combined data has 26 columns and 3675 rows.
1. Connecting to the data source
This tutorial was completed using the Tableau desktop version.
a. Download the dataset from HERE
b. Connect your dataset through the Microsoft Excel file type in the Tableau workspace, as shown in video 1.
2. Join the consumer and product datasets
The information needed to calculate the sales KPIs is in the consumer and product info datasets; therefore, you need to perform an inner join on both datasets as shown in video 2.
Check this Tableau resource for a better understanding of Joins in Tableau.
3. Calculate Company KPIs
The company’s KPIs table shows the important KPIs that can be used to track the business’s performance, whether it is growing or losing money.
a. The total number of unique orders: This is the sum of the purchases made between 2019 and 2020.
STEPS:
- Drag the Order ID ( from the Consumers info) into the “Marks card”.
- Right-click on the small triangle next to the Order ID >Measure>Count(Discount).
- Drag “Year” to Columns, right-click on Year and navigate to change from “Continuous” to “ Discrete”.
Refer to video 3
b. Total Order Value (TOV): This is the total amount of money a customer spends on purchases.
Tableau automatically performs a ‘’ sum’’ aggregation on the data.
STEPS:
- From the Purchases info, drag “Total Order Value” to the “Marks card”
- Select the “text tables” under “Show Me”, on the far right of the worksheet.
Refer to video 4
For the third and fourth metrics, repeat the steps above for “Discount” and “ Line SKU Production Cost” and refer to video 5 to rename your worksheet.
c. Discount: The reduction from the regular product price.
d. Line SKU Production Cost: This is how much it costs to buy raw materials and make the products.
Your worksheet should look like the image below;
e. Net Sales: This is the difference between the regular price of the product and the discount offered.
It does not exist in the dataset, so you have to calculate it using Calculated fields in Tableau.
FORMULA
[Total order value] — [Discount]
Refer to video 6.
f. Gross Profit: This is another calculated field; refer to the formula below and follow the steps in video 6.
FORMULA
[Net sales]-[Line SKU Production Cost]
g.% gross profit: By now you are familiar with the calculated field feature. Go ahead and calculate the percentage gross profit as shown in Video 7.
FORMULA
SUM([Gross Profit]) / SUM([Net Sales])
h. Average Consumer Lifetime Value (LTV): This is the average amount of money a consumer will spend on purchases.
STEPS:
- Drag the Consumer LTV ( from the Consumers info) into the “Marks card”.
- Right-click on the small triangle next to the Consumer LTV>Measure> Average.
Refer to video 8
i. Average Order Value (AOV): This is the average amount of money a customer will spend per purchase.
Calculating this might seem a bit tricky because you will need the TOV. Please follow the steps in video 9.
j. Average Discount: Repeat the step above to calculate the Avg. Discount
Well done for making it this far, just a few housekeeping left and we can wrap up!
Number Formatting: The difference between currencies, percentages, and regular numbers needs to be clear on your KPI table, hence the reason to format your numbers in Tableau. Please refer to video 10 for steps on how to format your numbers.
BONUS!
I added two KPIs with some plots; check the videos below the headings to recreate these visualizations.
k. Profit by product category
l. The number of orders per month
Thank you for sticking to the end.
CONCLUSION
In this article, we learned the difference between metrics and KPIs and how to calculate KPIs in Tableau. So when next you want to hop on a data analytics project, think about the goal, metrics, and KPIs that matter.
I have added the complete dashboard Toke created and additional resources. Feel free to recreate the rest of the KPIs on the dasboard.
REFERENCES:
Hope you enjoyed reading the article as much as I enjoyed writing it. To drop a feedback,review or need clarification, reach out on info@data4fashion.org.
Kindly click the link below to contribute to my fashion research survey.
👇🏼👇🏼👇🏼👇🏼👇🏼
Fashion Research Survey
Thank you🤗.
Connect with me on LinkedIn and Twitter or send an email to info@data4fashion.org
Cheers,
Fashion Data Queen (Kiitan)
