Tools: SQL | Python (Pandas, Seaborn, Matplotlib, Scikit-learn) | Tableau | MS Word
Type: End-to-End Data Analysis Project
Date: April 2026
This project delivers a comprehensive analysis of ShopSmart USA, a large-scale U.S.-based e-commerce platform with 50,000 orders across all 50 states. The goal is to uncover customer behavior patterns, product performance, churn risk, and revenue trends — and translate them into actionable business strategies.
The full pipeline covers:
- Data extraction & querying with SQL
- Exploratory analysis, RFM segmentation & ML with Python
- Interactive multi-page dashboard in Tableau
- Full business report with strategic recommendations
| Metric | Result |
|---|---|
| Total Revenue | $178,227,925 |
| Total Orders | 50,000 |
| Total Customers | 50,000 |
| Avg. Order Value | $3,564.56 |
| Total Discount Given | $41,274,173 |
| Active Customer Rate | 84.72% |
- ✅ Electronics leads all categories with $32.5M revenue (18.2% of total)
- ✅ Platinum members generate the highest avg. order value at $3,637.69
- ✅ Bronze tier is the largest segment (44.8%) — biggest upsell opportunity
- ✅ Higher discounts reduce avg. order value significantly — $4,608 (0%) → $2,902 (31–40%)
- ✅ 29,665 customers identified as Medium Churn Risk — immediate action needed
- ✅ Revenue is stable at $14–15M/month with Q2–Q3 seasonal peaks
| File | Description |
|---|---|
ShopSmart_USA_Customer_and_Orders_Analysis.ipynb |
Python notebook — data cleaning, EDA, RFM segmentation, churn prediction, forecasting |
ShopSmart_USA_SQL_Results.xlsx |
SQL query results — 15 analysis tasks (Basic → Advanced) |
ShopSmart_USA_Report.docx |
Full business report — executive summary, SQL findings, Python insights, 10 recommendations |
Dashboard_Screenshots/ |
Tableau dashboard screenshots — 4 pages |
- SQL — 15 queries covering customer segmentation, revenue trends, churn risk, cohort analysis
- Python — Pandas, NumPy, Matplotlib, Seaborn, Scikit-learn, Prophet (Jupyter Notebook)
- Tableau Public — 4-page interactive dashboard
- MS Word — Professional business report (7 sections, 10 recommendations)
👉 View All Live Dashboards on Tableau Public
| Dashboard | Link |
|---|---|
| 📊 Executive Summary | Total Revenue, Orders, Monthly Trend, State Map |
| 👥 Customer Intelligence | Membership Tier, Gender, Payment Method, Account Status |
| 📦 Sales & Product Analysis | Category Revenue, Return Rate, Review Score, Discount |
| 🔮 Trends & Forecasting | YoY Growth, Seasonal Trend, Forecast, Churn Risk |
Basic Level
- Total customers & active count
- Membership tier distribution
- Top 10 customers by orders
- State with the most customers
- Total revenue & avg. order value
Intermediate Level
- Category-wise sales & avg. discount
- Most-used payment method
- Monthly revenue trend (2023–2026)
- Highest return rate by category
- Avg. spending of Platinum members
Advanced Level
- Customer Lifetime Value (CLV) segmentation
- Top-selling category per state (Window Function)
- Churn risk customers (inactive 6+ months)
- Cohort analysis — which year buys the most
- Discount impact on revenue
- RFM Segmentation → Champions, Loyal, At Risk, Lost segments
- Churn Prediction → Logistic Regression, ~80% accuracy
- Revenue Forecasting → ARIMA/Prophet, 6-month projection
- Correlation Matrix → Key drivers of revenue identified
- Choropleth Map → State-wise revenue visualization (Plotly)
- Launch Exclusive Platinum Loyalty Program — highest LTV customers deserve VIP perks
- Intensify marketing in CA, TX, NY, FL — high population, untapped potential
- Reduce Jewelry & Electronics return rates — AR try-on, better product descriptions
- Deploy churn win-back campaigns — 35,738 at-risk customers = $211M+ potential
- Replace heavy discounts with value-added offers — protect margins
- Bronze → Silver upgrade campaign — 22,377 Bronze members = huge upsell base
- Pre-position inventory before Q2–Q3 peak season
- Optimize mobile checkout — Apple Pay + Google Pay = 33% of transactions
- Category-specific service improvements for Health & Wellness, Automotive
- Invest in predictive analytics — personalization = $10–15M incremental revenue
Piyas Emon — Data Analyst
📧 piyasemon7@gmail.com
🔗 LinkedIn | Tableau Public