Superstore Analysis, analysis of a Tableau public data set

Author: Manish Jha

Date: April 19th, 2024

Hello All,

Today I had sometime on my hand and I decided to play around with a publicly available dataset, Superstore Sales, available on Tableau Public website. Dataset is fairly small and after giving the dataset a quick look in Power Query, I decided to work with Excel and Tableau only for this dataset. I needed to do a small calculation about product returns, which I was able to complete within the Tableau environment only. Thanks to teams at Tableau for providing the practice dataset for learners like me.

This dataset has sales from a Superstore, a fictitious company, also probably an online store, offering a wide variety of products and offers nationwide shipping within USA. With no pre-defined business objective and key questions to answer, this dataset offers learners freedom to explore data and make recommendations. I will be honest here and as I started building out the viz, I did not have a clear set of business questions I would be answering. I continued to build clarity around the business questions as I kept working on the data.

As I explored the data, I saw there were a wide variety of products shipped to a lot of states and against each order ID, there was product ID, product details, sales, profit, discounts, customer segment details and then granular customer shipping details. For this analysis, I decided to don a product manager/marketing manager/business strategist hat and wanted to understand the following:

1. Where are the company’s customers located? Which US states are the biggest markets? How can this inform the short-term and long-term business decisions? What kind of decisions can be made using this data?
2. Across the product and customer segments, which are the highest sales and/or most profitable segments? How should this define the business’s customer engagement and marketing strategy?
3. What is the performance at the product sub-category level? What are the product level decisions that need to be made?

Question 1: Where are the company’s customers located and how can this information inform future business decisions?

I plotted the sales, profits, and number of orders against the Lat and Long values, available from the shipping data, and sorted the data as per the sales value, with US states with sales in green and US states with least sales in red. NY and CA were the highest $ value of orders states with TX and WA on a close third and fourth spot.

In addition to the being the biggest markets, CA, NY, WA and TX are also the fastest growing markets with WA leading the pack in terms of YoY growth, ~120% growth in $ sales volume over last 4 years.

Business Impact:

My initial assumption was that Superstore is an e-commerce store and with the biggest and fastest growing markets, company can make long-term decision of establishing distribution centers to best serve the biggest markets. In short-term company can align marketing strategies to gain or solidify leadership in established markets and increase wallet share in growing markets.

Question 2: Which are the most profitable customer and product segments? How should this inform customer engagement strategy?

Consumers are the company’s biggest customer segment, accounting for almost 50% of the company's sales revenues and ~45% to company’s total profits. However, in terms of $value/order, Home office segment has dollar value $241/order, Corporate segment has $234/order and Consumer segment ha $224/order. Between the 3 product categories, technology products continue to biggest in terms of sales as well profits. Following is the list of product sub-categories sold under the three product categories:

Business Impact:

All three customer segments have strong contribution to Superstore’s revenue. Superstore should push for sales of higher value goods in Consumer segment and increase portfolio of technology products to realize exponential growth in company top-line and bottom-line. Company should also invest resources to understand each of these segments better and develop customized marketing strategies.

Another interesting observation from this viz was that furniture segment is comparable to office supplies in terms of revenue across all customer segments but this segments profitability is very low. This warranted further investigation into product sub-categories and isolate products that were dragging Superstore’s profitability down.

Question 3: What is the performance at the product sub-category level performance? What are the product level decisions that need to be made?

While exploring the sales and profitability for product sub-categories, I categorized these sub-categories into four broader segments: Stable and high sales and profit share products, Stable and low sales and profit share products, Positive growth — sales or profits, products, and Negative growth -sales or profits, products.

The bottom graph tracks sales and profits trajectory from 2014–2017. I had started with all the product sub-categories on this graph and excluded stable trajectory products. I could easily classify them as:

1. Mature phase products where Superstore has had stable growth and these products have been maintaining their contribution to the revenue mix. Customers trust the brand for these products and company should continue to provide their best service for these products
2. Decline phase products low revenue share products that are not even growing. Superstore should investigate these products further for other product dependencies, cross-sell revenues etc. to determine if company would want to continue to carry these products.

After excluding these two categories, I was left with Copiers, Machineries and Tables as three product sub-categories that demonstrated highest positive or negative growth. Copiers have been growing in sales and profitability at a very ~55% CAGR and is one of most profitable segments. Machines has a sin curve in revenue growth but a clear downward trend in profitability. Tables has seen slight growth in revenue, ~7.2% CAGR, but a clear decline in profitability, with 2017 losses, deeper than 2014 losses, ~170% decline from 2014 levels.

I wanted to take this analysis a step further and understand Sales/Profit ratio for each product sub-category. In general, higher sales should account to higher profit. Trendline in top-left chart was indicative of the general trend, but it had three outliers — Copiers, Machines, and Tables. After I removed Copiers, trendline now had a negative slop, indicating contrary to the general belief. After I removed Tables from the graph, trendline was again positive and stayed true to the general trend.

Finally, I looked at returns data and found that Tables had second highest rate of return.

Business Impact:

Basis the above three visualizations, it was clear that Tables as a sub-category has been making losses with recent losses increasing multifold despite revenue growth. This warrant immediate investigation into the cost structure for this product sub-category to understand what are the costs that have gone up so significantly that overall profitability of this segment has gone down. If these costs cannot be streamlines, Superstore should consider getting Tables off the shelves for good as it is eating into the profits of other product sub-categories.

Copiers has been a highly profitable sub-category, currently in its growth phase, and company should expand its penetration into all the customer segments.

Overall, most of the Superstores products are in mature phase or entering mature phase. Superstore needs to look at its current product portfolio and identify more growth stage product categories/sub-categories to continue to realize the growth it has seen.

Complete Tableau visualization can be found here. Please note that only the dashboards have been made visible. For any questions or comments, please feel free to reach out to me on LinkedIn: Manish Jha