Retail Sales Analysis · Case study overview
Retail Sales Analysis
The question
The business was growing steadily, at a compound rate of 14.8% a year, and on a sales chart it looked healthy in every direction. Revenue was split almost evenly across the three product categories, and the four regions were close enough to each other that nothing stood out.
What management could not say was which parts of the business were paying for the rest. A balanced revenue mix hides an unbalanced profit mix, and a 12.5% overall margin is an average of things that are not alike. The brief was to find where margin was being made and lost, and to come back with changes worth making rather than a description of the data.
The data
- Source: the Tableau Sample Superstore dataset, a public file widely used for analytics work. Using a public source means every figure on these pages can be reproduced.
- Scope: 9,994 order lines, January 2014 to December 2017.
- Coverage: 49 states and the District of Columbia, grouped into four regions, across three product categories and seventeen sub-categories.
- Structure: 21 original columns, plus derived temporal, financial and categorical fields built during preparation.
Step 2 sets out the cleaning and the derived fields in full, including the Excel formulas and how each one was validated.
Approach
The work ran in five stages, each one feeding the next:
The analysis itself cut the data five ways: over time, by geography, by product category, by customer segment and by operational measures such as shipping mode and discount level. Each cut answered a different question, and the picture only made sense once they were read together.
What the analysis found
The three categories look almost equal on revenue: Technology 36.4%, Furniture 32.3%, Office Supplies 31.3%. On profit they are nothing alike. Technology returns 17.4% and Office Supplies 17.0%, while Furniture returns 2.5%. Nearly a third of the revenue contributes 6.4% of the profit.
Undiscounted orders run at a 29.5% margin. Between 21% and 30% off, the margin is minus 10.0%. Above 30% off it is minus 48.2%. Those two bands are 15.8% of revenue and take $135K off the bottom line.
West 14.9%, East 13.5%, South 11.9%, Central 7.9%. Central is the outlier, and the reason is specific rather than general: Furniture actually loses money there, at minus $2,871, while it is at least marginally profitable everywhere else.
Q4 takes 38.2% of annual sales against 15.7% in Q1, and the pattern repeats in all four years. November peaks at $352K and February bottoms out at $60K, close to a sixfold swing between the best and worst months. That is predictable enough to plan inventory and staffing against.
Tools
Read the case study
Five steps, in order. Each one stands on its own if you would rather jump straight to the part you care about.
The business challenge, the goals, and the five questions the analysis set out to answer.
Step 2 Data cleaning and preparationQuality checks, the derived fields and the Excel formulas behind them, and how each transformation was validated.
Step 3 Analysis and visualisationsThe five cuts of the data, with the pivot tables and dashboards behind each one, plus the interactive Tableau dashboards.
Step 4 Key findings and recommendationsEach business question answered, followed by five recommendations with the data behind them and how to implement each.
Step 5 Conclusion and next stepsWhat the work adds up to, a phased implementation plan, and the methods the project demonstrates.