An Analysis of a Supermarket Sales data
📄 Executive Summary
In the highly competitive retail sector, understanding the intersection of customer demographics and purchasing habits is the key to sustainable growth. This project involved an end-to-end analysis of 1,000 sales transactions from a supermarket chain operating across three major cities.
By analyzing variables such as product line performance, payment preferences, and member vs. normal customer behavior, I identified specific growth levers that could increase the gross income (currently at $15,379) and optimize the average customer rating (6.97/10).
❓ The Business Problem
The supermarket chain was operating with a high volume of transactions but lacked a granular view of its profitability and customer satisfaction drivers.
Key challenges included:
Revenue Stagnation: Which product lines are the primary drivers of our $322,966 total revenue?
Customer Loyalty: Does the "Member" program actually result in higher spend compared to "Normal" walk-in customers?
Regional Variance: Why are some branches outperforming others in terms of gross income?
Operational Timing: When are the peak hours that require higher staffing to maintain service quality?
🛠️ The Analysis Methodology
I executed this project using an approach of Excel for visualization and for data auditing:
1. Data Auditing & Cleaning
Processed 1,000 records across 17 attributes (Invoice ID, Branch, Product Line, Unit Cost, etc.).
Verified the calculation of COGS ($307,587) and Gross Income to ensure financial accuracy.
Standardized time formats to allow for hourly trend analysis.
2. Visualization & Reporting
Built an interactive Excel Dashboard featuring KPI cards, trend lines, and categorical breakdowns.
📊 Deep-Dive Insights
1. Product Line Performance
The Revenue Driver: Food and Beverages and Fashion Accessories emerged as the top-performing product lines.
Profitability Leader: Health and Beauty generated the highest gross income relative to its volume, signaling a high-margin opportunity.
The Laggard: Home and Lifestyle showed the lowest growth potential in the current period.
2. Customer & Demographic Nuances
Loyalty Pays: Members generated $164,223 in revenue, slightly outperforming Normal customers ($158,743). This validates the effectiveness of the membership program but suggests room for even deeper member engagement.
Gender Spend Gap: Female customers generated significantly higher gross income ($7,994) compared to Male customers ($7,384), particularly in the Fashion and Food segments.
3. Operational Peaks
Peak Hours: The data shows a surge in orders between 1:00 PM – 3:00 PM and a secondary peak around 7:00 PM – 8:00 PM.
Payment Trends: E-wallets are the most popular payment method (345 orders), followed closely by Cash (344 orders).
🚀 Strategic Recommendations
Recommendation 1: Targeted "Member-Only" Bundles
Why? Members already spend more, but the gap between Member and Normal revenue is narrow.
Action: Introduce exclusive "Member Bundles" for the Food and Beverages line. Increasing the average quantity per member transaction will widen the profitability gap.
Recommendation 2: Optimize the "Power Hours"
Why? Peak traffic at 1:00 PM and 7:00 PM creates potential friction at checkout, which may be contributing to the average rating of 6.97.
Action: Increase floor staff and open additional registers during these 2-hour windows to improve the customer experience and drive the rating toward an 8.0 goal.
Recommendation 3: Gender-Specific Marketing
Why? Females are the primary drivers of gross income.
Action: Launch targeted marketing for Health and Beauty and Fashion Accessories specifically during the 1:00 PM lunch-hour rush when female foot traffic is at its highest.
Recommendation 4: Digital Payment Incentives
Why? E-wallets are already the #1 payment method.
Action: Partner with E-wallet providers for "cashback" incentives. This reduces the burden of cash handling for the supermarket and speeds up the checkout process during peak hours.
💻 Technical Stack & Skills Demonstrated
Excel: Advanced Formulas, Pivot Tables, Power Query, Dashboard UI/UX.
Business Intelligence: Trend forecasting and demographic segmentation.