Advanced SQL analysis of the Olist Brazilian E-Commerce dataset.
✅ Status: Completed
This repository contains advanced SQL analytics performed on the Brazilian E-Commerce Public Dataset by Olist. The goal of this project is to extract actionable business insights regarding logistics bottlenecks, customer purchasing behavior, and revenue trends using Senior-level SQL techniques.
- Database: SQLite
- Techniques Used: Complex JOINs, Aggregations, Date Manipulation, Common Table Expressions (CTEs), and Window Functions.
- Day 1: Environment Setup & Database Architecture
- Day 2: Logistics Bottlenecks & VIP Customer Identification (
JOIN,GROUP BY, Date Math) - Day 3: Category Best-Sellers (
Window Functions,PARTITION BY) - Day 4: Customer Retention Analysis (
HAVINGclause) - Day 5: Executive Summary & Business Insights
Through SQL analysis, several critical business opportunities were identified:
- The Logistics Bottleneck: Delivery times to Northern Brazilian states (RR, AP, AM, PA) average nearly 30 days. Optimizing supply chain routes in the Amazon region is critical for customer satisfaction.
- Untapped B2B Potential: Analysis of top spenders revealed single-order values exceeding $13,000, strongly suggesting Olist has a hidden B2B (Business-to-Business) customer base that requires a dedicated VIP or bulk-purchasing program.
- The Retention Problem: Despite having ~96,000 unique customers, the repeat-purchase rate is extremely low (only ~3.1% have made more than one order). Implementing a targeted loyalty program is the #1 highest-ROI opportunity for the marketing team.
Note: Due to data privacy and size constraints, the raw 100MB+ database file is not hosted in this repository. All queries are designed to run on the standard Kaggle Olist schema.