Overview

This dataset comes from the Tableau website, it has products, sales, and profit data ranging from 2014-2017 for a fictitious store in the US. Some of the columns included are order date, customer id, region, quantity, ship mode, and segment. There are 9994 records in this dataset and 20 columns.

Data Cleaning

I started by sorting and filtering the data and going through each column to get a feel for the data. I checked for spelling, duplicates, missing data, and formatting errors. Overall it didn’t need much work so I loaded the CSV into Google BigQuery to start my analysis.

Approach

I decided to focus on the data for 2017, comparing a few of the KPI’s with 2016. For the dashboard I wanted charts such as total sales and volume over time to see how it changed over the months. There is a distribution of sales boxplot to see the trends and behaviors of customer spending. I also wanted to visualize the profit and sales and compare them among the different segments of customers.

Analyzing the Data

(View the queries here)

Starting with the KPI’s, there was $733,215 in sales (a 20.36% increase from 2016). A total of 1,687 orders for the year meaning an AOV of $434.62. And finally a profit of $93,439 which is up 14.24% from 2016 which had a profit of $81,795. Total sales and volume by month peaks in November, with noticeable dips in February, October, and December. Q4 is the best performing quarter in general but the momentum from November doesn’t carry over into December which is something to work on. They can use strategic planning or some sort of initiative to ensure a more consistent performance towards the end of the year.

There was a wide distribution of sales, 50% of customers spent between $204 and $1335. There were a handful of outliers such as 2 customers spending around $14k, but most of the top 25% spenders spent between $3k and $6k. The outliers come from the technology category of products, office supplies and furniture have similar ranges around $0-$6k while technology goes all the way to $14k. Technology and Office Supplies had similar profits margins, 18.65% and 16.15% respectively. Furniture’s profit margin was low at 1.40% despite having similar sales volume to technology, something needs to change there. They need to closely examine what factors influence this low profit margin to hopefully increase it.

The segment of customers that produced the most sales was consumer, followed by corporate, and home office in last. Looking at the category of products, all of them had similar total sales in the mid 1000’s with the exception of the Canon copier machine which had $35,700 in sales. Recognizing the top-performing products provides valuable information for inventory management and financial decision-making. Since they contribute the most to the success of the store, it important that they receive sufficient resources and attention.

Key Takeaways

The Superstore had excellent growth from the previous year but has some things it could work on. Increasing the profit margins on furniture is essential, 1.40% is not ideal. There are several avenues for improvement such as changing the pricing strategy. Researching the market to understand the perceived value and the positioning compared to their competition to price higher without losing sales volume. Another way to improve is by switching manufacturers, this could lower production costs and increase their margin. The top 5 products list is a practical tool for decision making, especially with inventory management and resource allocation. Overall there is a lot of insightful information that the store can use to grow their success.