Uncovering Top Trends and Customer Secrets from Q1 2019
Viola Aliwarga
Project Overview
The project analyzed supermarket sales data from January to March 2019 to uncover trends, customer behaviors, and performance insights. Tools used included Excel for initial analysis, Python for cleaning and visualization, and SQL for data querying. The dataset contained 1,003 rows and 17 columns, covering transaction details such as invoice ID, branch, customer type, product line, and payment method.
Initial Analysis
The raw dataset was reviewed using Excel to ensure the accuracy of key calculations. Columns such as Tax, Total, Gross Margin, and Gross Income were verified. The data was then prepared for analysis by standardizing column names.
Data Cleaning and Preparation
Missing values were addressed by filling in with mode or mean values, and duplicates were removed. New columns were created for Total Price, Day, and Time Period. The cleaned dataset now includes 1,000 rows and 20 columns.
Data Storage and Management
The dataset was uploaded to PostgreSQL with appropriate data types. SQL queries provided insights such as average gross income, customer ratings, and total sales by product line and branch. Key findings included higher sales in the Afternoon and on Saturdays.
Exploratory Data Analysis
Descriptive statistics highlighted an average gross income of $15.38 per invoice and an average customer rating of 7/10. Sales were highest for Fashion Accessories, and Branch C had the top sales figures. Visualizations illustrated sales trends and branch performance.
Advanced Analysis - Customer Segmentation
K-Means clustering identified three customer segments:
- High-Value Customers: High spending and frequent purchases.
- Mid-Value Customers: Moderate spending and purchase frequency.
- Low-Value Customers: Lower spending and infrequent purchases.
Results and Business Insights
Branch C had the highest sales, with branch A and B not too far behind.
Fashion Accessories led in sales, while Health and Beauty lagged.
Peak sales times were Saturdays and the Afternoon.
Customers prefer using E-Wallet and Cash over Credit Cards for their payments.
Recommendations include focusing on high-value customers, enhancing marketing for underperforming product lines, and optimizing staffing based on sales trends.
Conclusion
The analysis provided actionable insights into sales performance and customer behavior. Recommendations aim to improve sales strategies, enhance customer engagement, and optimize operational efficiency, ultimately boosting overall performance and satisfaction.
*More thorough analysis can be found here.
Like this project
Posted Sep 14, 2024
Analyzed and cleaned sales data, resulting in a streamlined report that helped the client identify key market trends.
