🚀 Project Overview
This project performs in-depth customer and sales analysis for an e-commerce business using SQL (PostgreSQL).
The goal: improve customer retention and maximize revenue by analyzing segmentation, cohorts, and retention patterns.
- 🧑🤝🧑 Customer Segmentation – Identify high, mid, and low-value customers
- 📆 Cohort Analysis – Measure revenue patterns across acquisition years
- 🔄 Retention Analysis – Understand churn trends and re-engagement opportunities
- Who are our most valuable customers?
- How much revenue do they contribute?
- How do different customer cohorts generate revenue over time?
- Are newer cohorts spending as much as older ones?
- Which customers haven’t purchased recently?
- What does long-term retention look like?
- Database: PostgreSQL
- Tools: DBeaver, ChatGPT for visualization
- SQL Features Used:
- ✅ Common Table Expressions (CTEs) & Subqueries
- ✅ Window Functions (
ROW_NUMBER,LAG,LEAD) - ✅ Aggregations (
SUM,COUNT,AVG) - ✅ Conditional Logic (
CASE,COALESCE) - ✅ Views for Data Cleaning & Transformation
- High-value segment (25%) → 66% of revenue ($135.4M)
- Mid-value segment (50%) → 32% of revenue ($66.6M)
- Low-value segment (25%) → 2% of revenue ($4.3M)
💡 Business Insight:
- Launch VIP program for 12,372 top customers
- Create upgrade paths for mid-value customers to unlock $66M → $135M potential
- Run re-engagement campaigns for low-value, price-sensitive users
- Revenue & customers peaked in 2022–2023 but declined in 2024
- Average customer revenue dropped from ~$2,800 (2016–2018) → ~$1,970 (2024)
💡 Business Insight:
- Stabilize revenue with loyalty or subscription programs
- Apply successful 2016–2018 strategies to new cohorts
- Personalized re-engagement campaigns for 2022–2024 cohorts
- Churn stabilizes around 90% after 2-3 years
- Retention consistently <10% across cohorts
💡 Business Insight:
- Improve first 1–2 year onboarding with incentives & rewards
- Focus on high-value win-back campaigns
- Build churn prediction models to intervene early
- Database: PostgreSQL
- Analysis Tools: PostgreSQL, Dbeaver
- Visualization: ChatGPT
✅ Deepened understanding of window functions, cohort analysis, and retention modeling.
✅ Practiced writing clean, modular SQL queries using CTEs and views.
✅ Learned how to turn raw data into actionable business recommendations.
🔮 Extend with:
Automated retention dashboards (using Power BI or Tableau). Machine learning churn prediction model. Email campaign recommendations for each segment.
Special thanks to Luke Barousse for inspiring this project and providing a clear framework for SQL-based sales analytics.