This is a project on the analysis and visualization of a supermarket sales dataset. It aimed to provide insights into the sales performance of the supermarket's major three branches, over three months.
By analysing various aspects of the sales data, I sought to identify trends, gain a deeper understanding of the supermarket's performance, and provide actionable insights for decision-making.
- Kaggle
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 C in Naypyitaw is the top-performing branch.
- E-wallets are the most preferred payment method.
- Electronic accessories and food & beverages are the top-selling products, with food & beverages receiving the highest ratings.
- January generated the highest sales revenue, closely followed by March, while February recorded the lowest sales revenue.
...and others - as shown in the dashboard!
- 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.
- Microsoft Excel. (Import Data, Freeze Panes, Spell Check, Remove Empty Rows, TRIM Function, Find & Replace, Remove Duplicates, Date and Currency Formatting, Formulas, Pivot Tables, Charts, Slicers, Dashboard Creation, etc)
- Microsoft Word.
- Microsoft PowerPoint.
I first loaded the dataset into Excel, to enable data access for subsequent analysis.
I then applied freeze panes to the top row to ensure that column headers remained visible during scrolling, enhancing data readability and navigation.
I checked for spelling errors, to ensure data consistency and accuracy, particularly in the categorical data like the product line names, payment methods and city names.
I identified and removed empty rows and extra spaces, ensuring clean and reliable data.
I checked for and addressed missing values, which helped maintain data integrity.
I identified and removed duplicate values, ensuring each Invoice_ID record is unique.
I also formatted the date and currency, thus standardizing the appearance of the date data which enhanced the readability and prevented misinterpretation of the dates in the analysis stage.
Calculating Key Metrics: I calculated total cost of goods sold (COGS), sum of total revenue, and sum of gross income.
These provided a foundational understanding of the dataset's financial aspects, essential for subsequent analysis.
As a crucial step for identifying key patterns and trends, I created the following pivot tables:
The total sales by branch table helped identify the revenue contributions of different branches, useful for performance comparison and resource allocation. It also helped assess the geographic performance of the three cities, which would aid in regional strategy development.
The sales by payment method table helped to determine the popularity and customer preference for different payment options. This would inform financial processing strategies.
The performance across product lines table helped identify the high and low-performing product categories, which would possibly guide marketing decisions.
The top-selling products table recognized key revenue drivers, which would help facilitate targeted promotions.
The sales trends over time table identified the temporal sales patterns, essential for forecasting and seasonal planning.
The cost analysis by product line table assessed profitability and cost efficiency across different product categories.
The customer demographics table helped me understand customer segmentation, aiding in personalized marketing strategies.
- Clustered Column Chart for Demographics - This helped in displaying the distribution of different customer types, useful for demographic analysis.
- Clustered Column Chart for Total Sales by Branch- This enhanced visual representation of branch performance, making comparisons more intuitive.
- Clustered Column-Line Chart for Top-Selling Product Lines and Ratings - I created a combined chart to show top-selling product lines alongside their ratings; and this correlated sales performance with customer satisfaction, aiding in product quality assessment.
- Pie Chart for Sales by Payment Method - This provided a clear visual of payment preferences, informing financial operations.
- Line Chart for Sales Trend Over Time - Highlighted temporal sales patterns, supporting trend analysis and forecasting.
- Clustered Column Chart for Product Line Cost Analysis - Visualized cost distribution across product lines, aiding in cost management and optimization.
- Added two slicers for the three different Branch cities, and gender to help analyze the data further.
This end-to-end process provides a clear understanding of sales performance across various dimensions, identifies key revenue drivers, highlightes customer preferences, and supports data-driven decision-making processes.