This project performs end-to-end SQL analysis in a Brazilian e-commerce dataset to extract meaningful business insights related to revenue performance, product trends, customer behavior, and customer segmentation.
The objective of this project is to simulate real-world business analytics tasks using MySQL and advanced SQL techniques.
- MySQL Workbench
- SQL (Joins, Aggregations, Subqueries)
- Window Functions (NTILE)
- CASE Statements
- RFM Customer Segmentation
- Git & GitHub
- Calculated total revenue generated by the business.
- Measured average order value (AOV).
- Analyzed monthly revenue trends.
- Identified top-performing products.
- Identified top-performing product categories.
- Calculated revenue contribution percentage by category.
- Measured repeat customer rate.
- Identified top-spending customers.
- Segmented customers using RFM analysis.
- Total Revenue
- Average Order Value (AOV)
- Monthly Revenue Trend
- Top 10 Products by Revenue
- Top 10 Categories by Revenue
- Revenue Contribution %
- Repeat Customer Rate
- Customer Lifetime Spend
- RFM Scores (Recency, Frequency, Monetary)
- Customer Segment Distribution
Customers were segmented using:
- Recency → Days since last purchase
- Frequency → Number of completed orders
- Monetary → Total spending amount
Using NTILE(5) window function, customers were scored from 1 to 5 and classified into the following segments:
- Champions
- Loyal Customers
- Potential Loyalists
- At Risk
- Others
- Revenue is concentrated among a small percentage of customers.
- Only ~0.4% of customers are classified as Champions.
- Around 34% of customers are Potential Loyalists, representing strong growth opportunity.
- Approximately 11% of customers are At Risk and require retention strategies.
- Repeat customer rate is relatively low (~3%), indicating opportunity for loyalty programs.
- Top product categories contribute a significant share of overall revenue.
- Launch targeted marketing campaigns for Potential Loyalists.
- Retarget At Risk customers using personalized discounts.
- Develop loyalty programs to improve repeat purchase rate.
- Focus marketing efforts on high-performing product categories.
- Create retention strategies for high-monetary customers.
Brazilian E-Commerce Public Dataset (Olist).
- Advanced SQL Querying
- Data Aggregation & Joins
- Window Functions
- Customer Segmentation (RFM)
- KPI Development
- Business Insight Generation
- Analytical Thinking
This project demonstrates the ability to transform raw transactional data into structured business insights using SQL and apply analytical techniques to support strategic decision-making.