An end-to -end data analytics project that analyses customer purchasing patterns to uncover segments, trends, and drivers of revenue, with the goal of informing marketing, merchandising, and retention strategy using Python (cleaning the dataset & feature engineering), MySQL (structured business queries) and PowerBI (interactive dashboard visualization).
- Overview
- Prerequisites
- Business Questions
- Dataset
- Core Libraries
- Methodology
- Key Findings
- Recommendations
- Contribution
- License
This project analyzes retail transactions from 10000 Indian customers over 2years to understand how different customer groups shop, what they buy, and what drives repeat purchases. It covers data cleaning, exploratory analysis, customer segmentation, and visualization of results.
- Python
- MySQL Server
- Power BI Desktop
-
- Which are the top 5 products with the highest average review rating?
-
- What is the total revenue generated by male vs female customers?
-
- What is the revenue contribution of each age group?
-
- Which 5 products have the highest percentage of purchases with discount applied?
-
- Which 50 customers used a discount but still spent more than the average purchase amount?
-
- What are the top 5 most purchased products within each category?
-
- Do subscribed customers spend more? Compare the average spend and total revenue -- between subscribers and non-subscribers
-
- Segment customers into new, returning, loyal based on their -- total number of previous purchases. And show the count of each segment.
-
- Are customers who are reapeated buyers with more than 5 previous purchases also likely to subscribe?
-
- Comapre the average Purchase amount among different payment methods users?
| Field | Description |
|---|---|
| transactionr_id | Unique transaction identifier |
| customer_id | Unique customer identifier |
| purchase_date | Purchasing dates |
| age | Customer age |
| gender | Customer gender |
| location | Customer Cities |
| category | Product category |
| item_purchased | Product name |
| brand | Product brand |
| color | Product Color |
| size | Product size |
| Quantity | Product quantity |
| purchase_amount | Transaction value (INR) |
| discount_percentage | percentage of discount applied on the MRP |
| festival\sale | standard day or a festival day |
| shipping_charges | shipping charge for product |
| delivery_speed | delivery medium |
| deliver_time_days | delivery in days |
| subscription_status | subscription by customers |
| payment_method | Payment type |
| review_rating | Customer rating (1-5) |
| return_status | returned or not |
| previous_purchases | Count of prior purchases |
| frequency_of_purchases | Purchase cadence (weekly, fortnightly, monthly, quartly, rarely) |
Size: [10000] x [26] Time range: [01-01-2023] to [31-12-2024]
Python, Pandas, SQLalchemy, MySQL
- Data Cleaning & Wrangling (Python)
- Exploratory Data Analysis (Jupyter)
- Relational Analysis (MySQL)
- Visualization (Power BI)
- Non Subscribers bought more Products than subscribers.
- Clothing section is responsible for most revenue while accessories are the least revenue generator.
- Middle aged people bought the highest amount.
- February, June, July and November are the months with least revenue generation.
- Allen Solly, Beta and Bewakoof are the most sold brands.
- Clothing items were mostly returned.
- Boost Subscriptions - Promote executive benefits for subscribers.
- Customer Loyalty Program - Reward repeated buyers to move them into the "Loyal" segment.
- Review Discount Policy - Balance Sales boost with Margin control.
- Product Positioning - Highlight top rated and best-selling products in the campaign.
- Target Marketing - Focus efforts on high revenue age groups and new strategy for younger age group.
Rudrajit Das
Apache 2.0 License.