Analyzed 100,000+ e-commerce transactions to quantify delivery risk concentration across a multi-vendor marketplace. Late deliveries affect $1.12M in revenue (6.99% of total) and correlate with a 47.1% reduction in review scores (4.29★ on-time vs 2.27★ late). Applied Empirical Bayes shrinkage to adjust seller late rates for volume bias, identifying that a small subset of sellers accounts for the majority of late-delivery revenue exposure. Supplementary RFM + K-Means analysis (k=4, elbow validated) confirms low repeat-purchase behavior.
A multi-vendor e-commerce marketplace with thousands of independent sellers exhibits operational challenges around delivery timeliness:
- Surface-level metrics appear healthy, but late deliveries correlate with lower satisfaction scores
- 96.96% of customers make only one purchase
- Review scores decline from 4.29 to 2.27 stars when deliveries exceed promised dates (47.1% reduction)
Key Question: Are late deliveries randomly distributed, or do specific sellers disproportionately damage the platform?
E-Commerce dataset with 100,000+ orders containing:
| Table | Records | Key Fields |
|---|---|---|
| Orders | 99,441 | order_status, delivery timestamps, estimated dates |
| Order Items | 112,650 | seller_id, price, freight, shipping_limit |
| Customers | 99,441 | customer_state, customer_city |
| Sellers | 3,095 | seller_state, seller_city |
| Reviews | 99,224 | review_score, review_comment |
| Payments | 103,886 | payment_type, installments, value |
| Products | 32,951 | category, weight, dimensions |
Module 1 — Platform Baseline
- Total revenue, delivered orders, late delivery count and revenue
- Late rate and % of revenue exposed
Module 2 — Satisfaction Impact
- Average review score by delivery status (on-time vs late)
- Order count and standard deviation per bucket
- Correlation between delivery timeliness and satisfaction
Module 3 — Delay Severity Curve
- Review score by days-late bucket
- Order count per delay group
- Observable degradation pattern (no cliff assumption)
Module 4 — Seller Concentration
- Raw late rate and volume per seller
- Empirical Bayes shrinkage to correct for small-sample bias:
Where
adjusted_rate = (n / (n + m)) * seller_rate + (m / (n + m)) * platform_avg_ratem = 100(sellers below 100 orders are pulled toward the platform mean) - Sellers ranked by
adjusted_rate × total_orders(volume-adjusted impact)adjusted_rate≈ probability of late delivery (corrected for sample size)total_orders≈ exposure (how many customers are affected)- Product ≈ expected late orders, adjusted for small-sample bias
- Intervention targets are selected using volume-adjusted impact, not raw late count
- Top 100 comparison: raw count ranking vs Bayes-adjusted ranking
Module 5 — Revenue Exposure Decomposition
- Late revenue as % of total
- Concentration of late revenue among top sellers
- Effective platform exposure = late_revenue_pct × concentration_pct
- RFM scoring (Recency, Frequency, Monetary)
- K-Means clustering (k=4, elbow method validated)
- 4 behavioral segments identified from feature distribution
- Confirms low repeat-purchase behavior (96.96% single-order)
| Metric | Value | Implication |
|---|---|---|
| One-time purchase rate | 96.96% | Transactional marketplace |
| Repeat customer rate | 3.04% | Low repeat behavior |
| Revenue from repeat customers | 5.66% | Repeats high-value |
| Avg Customer Lifetime Value | $137.75 | Single-order dominance |
| Metric | Value | Implication |
|---|---|---|
| Late delivery cost | $1.12M | 6.99% of total revenue |
| On-time review score | 4.29 stars | Baseline satisfaction |
| Late delivery score | 2.27 stars | 47.1% satisfaction reduction |
| Delivery degradation | Gradual | Ratings decline with delay |
| Finding | Value | Method |
|---|---|---|
| Raw top 100 concentration | 53.18% of late deliveries | Ranked by raw late count |
| Bayes-adjusted top 100 | Computed via EB shrinkage | adjusted_rate × volume |
| Revenue exposed to late-delivery risk | $1.12M | 6.99% of total platform revenue |
| Total late orders | 6,381 | Delivered orders past estimate |
| Prior strength (m) | 100 | Shrinks small-sample rates |
| State | Revenue Share | Avg Transit Days |
|---|---|---|
| SP (São Paulo) | 42% | 8.2 days |
| RJ (Rio) | 13% | 10.1 days |
| MG (Minas Gerais) | 12% | 11.8 days |
| Remote states | 8% | 18+ days ("Black Holes") |
- Address top 100 sellers - Focus on sellers contributing 53% of late deliveries
- Analyze delivery thresholds - Identify delivery day ranges with satisfaction degradation
- Segment customer base - Identify repeat-purchaser characteristics for targeted retention
- Operational review for sellers with high Bayes-adjusted impact scores
- Regional logistics analysis for high-transit states (RR, AP, AM)
- Delivery estimate calibration using seller + region performance data
- Seller performance monitoring with volume-adjusted risk scoring
- Dynamic delivery estimates based on historical seller + carrier data
- Retention analysis targeting repeat-purchase behavior patterns
| Category | Technologies |
|---|---|
| Database | PostgreSQL 15 |
| SQL Techniques | CTEs, Window Functions (LAG, LEAD, NTILE, RANK), Complex Aggregations |
| Python | Pandas, NumPy, Matplotlib, Seaborn, Plotly, Scikit-learn |
| ML/Statistics | Empirical Bayes Shrinkage, K-Means Clustering, RFM Analysis |
| Visualization | Power BI Dashboard, Interactive Plotly Charts |
| Version Control | Git & GitHub |
retail-sql-analytics/
│
├── sql/
│ ├── business_overview.sql # Revenue trends, KPIs
│ ├── customer_analytics.sql # CLV, Cohort, RFM analysis
│ ├── product_revenue.sql # Category & regional performance
│ └── advance_analysis.sql # Window functions, complex patterns
│
├── python/
│ ├── setup.py # Shared imports & DB connection
│ ├── analysis.ipynb # EDA & customer segmentation
│ ├── analysis2.ipynb # Delivery forensics & seller risk
│ └── analysis3.ipynb # Seller risk bucketing & visualization
│
├── dashboard/
│ ├── retail_dashboard.pbix # Power BI interactive dashboard
│ └── *.png # Exported visualizations
│
├── requirements.txt # Python dependencies
└── README.md
# 1. Clone repository
git clone https://github.com/yourusername/retail-sql-analytics.git
# 2. Install dependencies
pip install -r requirements.txt
# 3. Set up database connection
# Update python/setup.py with your PostgreSQL credentials
# 4. Run Jupyter notebooks
jupyter notebook python/The following modules build a structured case from baseline measurement through seller-level risk decomposition.
Question: Does delivery timeliness impact review scores?
Finding: On-time deliveries average 4.29★. Late deliveries average 2.27★, representing a 47.1% reduction in satisfaction scores.
Implication: Delivery timeliness is a significant driver of customer satisfaction.
Question: Are late deliveries driven by seller processing time or carrier transit time?
Finding: The scatter plot reveals two distinct delay sources:
- Carrier-driven delays: Sellers ship within 0-2 days, but carriers take 8-15 days in transit
- Seller-driven delays: Sellers take 5-8+ days to hand off to carrier
Implication: Different delay sources require different interventions — seller process improvement vs carrier/regional logistics optimization.
Question: How do satisfaction scores vary with delivery delays?
Finding: Customer satisfaction shows gradual degradation as delivery days increase. Days 5-10 (early delays): 5,734 orders. Days 15+ (extended delays): 589 orders. The drop reflects both satisfaction decline and reduced repeat ordering.
Implication: Customers become progressively less likely to engage as delivery extends beyond promise date.
Question: How are late deliveries distributed across sellers?
Finding:
- Top 100 sellers (7.85% of 1,274 sellers with late orders): 53.18% of all late deliveries
- Top 3 sellers: ~1,000 late orders combined (15% of total late orders)
- Top 20 sellers: ~3,500 late orders (35% of platform late deliveries)
Implication: Late delivery risk is concentrated in a small fraction of sellers, creating targeted improvement opportunities.
Raw late counts overweight high-volume sellers. Raw late rates overweight low-volume sellers. Empirical Bayes shrinkage corrects for both.
Sellers are then ranked by adjusted_rate × total_orders — treating adjusted rate as probability of failure and order volume as exposure, so the product approximates expected late orders corrected for bias:
adjusted_rate = (n / (n + m)) * seller_rate + (m / (n + m)) * platform_avg_rate
Cumulative late-delivery revenue by Bayes-adjusted seller rank. Shows concentration of risk after volume correction.
Scatter plot: seller order volume against Empirical Bayes adjusted late rate. Color intensity = adjusted impact score. High-volume + high-adjusted-rate = primary intervention targets.
Platform delivery status: 93% on-time, 7% late.
Late-rate distribution across sellers with 50+ orders.
Sellers classified into SUSPEND / CRITICAL / HIGH_RISK / MONITOR based on late rate and volume thresholds.
Revenue vs late order count per seller. Top-right quadrant = highest financial exposure.
Additional analysis to demonstrate ML capability and confirm marketplace purchase patterns.
4-cluster segmentation using Recency, Frequency, Monetary features. Segments identified from feature distribution, not pre-labeled.
k=4 selected at elbow inflection point. Inertia: k=3→4 showed significant drop, k=4→5 showed diminishing returns.
Distribution analysis: payment methods, price ranges, review scores across 99,441 customers.
Every claim in this project is backed by executed queries and actual data. Run python verify_claims.py to regenerate the verification report.
All claims verified via verify_claims.py. Source queries use INNER JOINs on production tables.
| Claim | Verified Value | Method |
|---|---|---|
| Late delivery revenue cost | $1,118,391.48 (6.99% of total) | SUM(payment_value) WHERE delivered > estimated |
| One-time purchase rate | 96.96% of customers | COUNT(DISTINCT orders) per customer |
| Satisfaction drop | 2.27★ late vs 4.29★ on-time (47.1% difference) | AVG(review_score) grouped by delivery status |
| Delay severity | 5,734 orders (days 5-10) vs 589 orders (days 15+) | COUNT(order_id) grouped by days_late |
| Seller concentration (raw) | 7.85% of sellers with late orders (100/1,274) → 53.18% of late deliveries | ROW_NUMBER() by late_order count |
| Seller concentration (EB) | Top 100 by Bayes-adjusted impact score | EB shrinkage (m=100) × volume |
| K-Means segmentation | k=4 selected via elbow method (inertia=3070.30) | KMeans k=2-8, supplementary analysis |
verify_claims.py- Automated script executing all claim verification queriesverification_results.json- Machine-readable output with exact metricsRESUME_VERIFIED.md- Extended analysis summary
All SQL queries use INNER JOINs on production tables (customers, orders, order_items, payments, reviews). Run python verify_claims.py to regenerate results.
- SQL: Complex CTEs, window functions (ROW_NUMBER, LAG), FILTER aggregations, subqueries
- Statistical Methods: Empirical Bayes shrinkage, correlation analysis, bias correction
- Machine Learning: K-Means clustering, elbow method validation, RFM feature engineering
- Python: Pandas, scikit-learn, automated verification pipelines
- Visualization: Pareto curves, scatter diagnostics, Power BI dashboards
- Business Analysis: Revenue exposure decomposition, risk concentration quantification
Manikanta DM
Data Analyst | SQL & Python Specialist
This project is for portfolio and educational purposes.