SUPERMARKET SALES ANALYSIS
Imagine a bustling supermarket with three thriving branches, pulling in substantial revenue each month.
But amidst the success, management notices something troubling; sales are fluctuating unexpectedly, with certain days and products performing far better than others, and no clear explanation for the inconsistencies. Alarms are ringing, as these unpredictable sales trends could mean missed opportunities or worse, lost revenue. The need for a solution is urgent, but without understanding the data behind their operations, the supermarket is left guessing.
To address this kind of scenario, I embarked on a personal project using a random supermarket sales dataset from Kaggle, representing three branches over a three-month period. My goal was to dig into the data, identify any inconsistencies and the factors driving them, and uncover necessary insights. Through exploratory data analysis, I examined each branch's performance, the customer payment preferences, top-selling products, and overall sales trends to pinpoint the areas for improvement.
​
I also created visualizations to illustrate the distribution of sales over time, popular product categories, and customer purchasing habits. The insights generated from this analysis offered clear, data-driven strategies to refine the supermarket's revenue, optimize branch operations, and align their marketing efforts with customer preferences, ultimately driving higher revenue and strengthening their market position.
Source of Data
Tools & Files
Exploratory Data Analysis
EDA involved exploring the sales data to answer these key questions:
-
What is the highest performing branch?
-
Which is the preferred payment method?
-
Which products are top-sellers?
-
What is the overall sales trend?
-
Branch Performance: The analysis reveals that Branch C in Naypyitaw stands out as the top-performing location among the supermarket's branches, showcasing exceptional sales figures.
-
Payment Preferences: E-wallets emerged as the preferred payment method among customers, indicating a growing trend toward digital transactions that streamline the purchasing process.
-
Top-Selling Products: Electronic accessories and food & beverages have proven to be the supermarket's top-selling categories. Notably, food & beverages received the highest customer ratings, suggesting strong customer satisfaction and loyalty in this product segment.
-
Sales Trends: The sales data indicates that January generated the highest revenue, with March closely trailing behind. In contrast, February experienced the lowest sales revenue, highlighting potential seasonal fluctuations in customer purchasing behavior.
Insights
Recommendations
-
With electronic accessories and food & beverages being the top-selling products, the supermarket should consider expanding its inventory in these categories. Additionally, maintaining high standards of quality in food & beverages will help sustain customer satisfaction and loyalty.
​​
-
To mitigate the low sales revenue observed in February, the supermarket could implement targeted marketing strategies during this period to boost sales. This may include seasonal promotions, limited-time offers, or events that encourage customers to shop during slower months.
Data Analysis Process
​This project follows these phases:
-
Data Loading & Initial Preparation.
-
Data Cleaning and Formatting.
-
Analysis.
-
Visualizations and Dashboard Creation.
THE PROCESS
1. Data Loading and Initial Preparations
To initiate my analysis, I loaded the supermarket sales dataset into Excel. This allowed me to easily access the information required for subsequent exploration and analysis.
To enhance data readability and facilitate navigation through the dataset, I applied freeze panes to the top row.
This ensured that the column headers remained visible while scrolling, allowing for seamless interaction with the data.
2. Data Cleaning and Formatting
I conducted a thorough check for spelling errors, particularly focusing on categorical data such as product line names, payment methods, and city names. This ensured data consistency and helped maintain accuracy throughout the dataset.
To eliminate unwanted entries, I identified and removed empty rows and extra spaces, which contributed to a cleaner and more reliable dataset. Additionally, I addressed all missing values to uphold data integrity, and took the necessary steps to identify and remove duplicate entries. By doing this, I ensured that each Invoice ID record was unique, and thus enhancing the dataset's reliability for analysis.
​
To standardize the appearance of the data, I formatted the date and currency fields. This step was essential for enhancing readability and preventing any misinterpretation of dates during the analysis phase, thereby ensuring that subsequent analyses were based on a clear and consistent dataset.
​
3. Analysis
In the analysis phase, I first focused on calculating critical financial metrics, including the total cost of goods sold (COGS), total revenue, and gross income. These calculations provided a foundational understanding of the supermarket's financial aspects and were essential for guiding subsequent analyses.
​
To identify key patterns and trends, I then created several pivot tables:
The Total Sales by Branch table revealed the revenue contributions of different branches, facilitating performance comparisons and resource allocation while also assessing geographic performance across the three cities.
The Sales by Payment Method table allowed me to determine the popularity of various payment options, which informed financial processing strategies.​
The Performance Across Product Lines table highlighted high and low-performing product categories, offering insights that could guide marketing decisions.
The Top-Selling Products table identified key revenue drivers, thus enabling targeted promotions.
To illustrate temporal sales patterns essential for forecasting and seasonal planning, I developed the Sales Trends Over Time table.
The Cost Analysis by Product Line table assessed profitability and cost efficiency across different product categories, while the Customer Demographics table enhanced my understanding of customer segmentation, aiding in the development of personalized marketing strategies.
4. Visualizations and Dashboard Creation
To effectively communicate my findings, I created a variety of visualizations. The Clustered Column Chart for Demographics displayed the distribution of different customer types, which enriched demographic analysis. Meanwhile, the Clustered Column Chart for Total Sales by Branch provided an intuitive comparison of branch performance, making it easier to visualize differences in revenue across locations.
The Clustered Column-Line Chart for Top-Selling Product Lines and Ratings offered a combined view of top-selling product lines alongside their ratings, allowing me to correlate sales performance with customer satisfaction. Additionally, the Pie Chart for Sales by Payment Method provided a clear visual representation of payment preferences, informing financial operations effectively. The Line Chart for Sales Trend Over Time highlighted temporal sales patterns, supporting both trend analysis and forecasting efforts.
To aid in cost management and optimization, I created the Clustered Column Chart for Product Line Cost Analysis, which depicted the cost distribution across various product lines. To further enhance the analysis, I incorporated two slicers for the three different branch cities and gender, enabling more granular data exploration and allowing users to delve deeper into the insights generated from the dataset.
​
The Dashboard is as shown below:
​
Conclusion
The comprehensive analysis of the supermarket sales data has provided valuable insights into branch performance, payment preferences, product popularity, and seasonal trends.
By implementing the recommended strategies, the supermarket can optimize its operations, enhance customer engagement, and drive sales growth.
This analysis not only sheds light on current
performance but also lays the groundwork for informed decision-making and future success in a competitive retail landscape.
THANK YOU!
Thank you for taking the time out to view my project!
In case you would like to discuss this project further, feel free to email me at: