Olist E-Commerce: Sales & Logistics Optimization Dashboard

Case Study: Optimizing Customer Satisfaction through Logistics Analysis at Olist E-Commerce 📌 Executive Summary In the competitive landscape of e-commerce, sales growth means nothing if customer retention is low. This project analyzes a public dataset from Olist (Brazil) to investigate a critical business paradox: Why do customer satisfaction ratings remain stagnant despite consistent revenue growth? By developing an interactive Power BI dashboard, the analysis isolates logistics performance as the primary driver of customer sentiment. The findings disprove the assumption that sellers are to blame for delays, identifying third-party carriers as the true bottleneck. ❓ The Business Challenge Olist operates as a marketplace connecting merchants to customers across Brazil. While the platform has successfully scaled its transaction volume, it faces significant challenges: 1. Stagnant Ratings: Customer review scores fluctuate unpredictably. 2. Operational Blame Game: Difficulty in pinpointing whether delays are caused by merchants (processing time) or logistics partners (shipping time). 3. Uneven Expansion: Sales are heavily concentrated in the Southeast, with limited penetration in other regions. Objective: To diagnose the health of the business and provide data-driven recommendations for operational improvements. 🛠 Methodology & Technical Approach The project followed a rigorous data analysis lifecycle: 1. Data Preparation (ETL) - Source: 9 raw CSV files containing relational data (Orders, Items, Customers, Sellers, etc.). - Transformation: Used Power Query to clean data, handle missing values, and standardize date formats. - Modeling: Constructed a robust Star Schema data model to ensure query performance. - Challenge Resolved: Handled ambiguous geographic relationships by creating separate dimension tables for Geo_Customer and Geo_Seller. 2. Advanced Analysis (DAX) Calculated columns and measures were created to quantify operational metrics: - Lead Time Breakdown: Separating Seller Processing Days vs. Carrier Transit Days. - On-Time Delivery Rate: Dynamic calculation based on estimated vs. actual delivery dates. - Delivery Status Logic: Categorizing orders into 'On-Time', 'Late', or 'Canceled'. 📊 Key Insights & Findings Part 1: Sales Health & Market Behavior - Financial Stability: The platform demonstrates strong traction with over 98k orders and 13.5M BRL in revenue. - Consumer Trust: 78% of transactions are made via Credit Card, indicating a highly bankable user base with high digital trust. - Seasonality: Sales trends show consistent growth, peaking in November (aligned with Black Friday), though a significant dip in September warrants further operational investigation. Part 2: Operational Diagnostics (The Root Cause) The analysis uncovered that logistics is the silent killer of customer satisfaction. - Seller Efficiency vs. Carrier Delays: Data disproves the myth that sellers are slow. The average Seller Processing Time is highly efficient at < 3 days. The bottleneck lies entirely within the Carrier Transit Time, which accounts for approximately 80% of the total delivery duration. - The Cost of Delay: There is a fatal correlation between delivery speed and ratings: - ✅ On-Time Orders: Average Rating 4.23 / 5.0 - ❌ Late Orders: Average Rating Drops to 2.17 / 5.0 - Insight: A single operational failure (delay) slashes customer satisfaction by nearly 50%. - Geographical Inequality: The Freight Cost Matrix reveals why Olist struggles to expand beyond the Southeast. Northern regions (such as Roraima and Paraíba) suffer from the highest shipping costs and delivery times often exceeding 20 days, effectively creating a barrier to entry. 🚀 Recommendations Based on the data, the following strategic actions are recommended: 1. Renegotiate Logistics Contracts: Since the bottleneck is on the Carrier side, Olist must enforce stricter SLAs (Service Level Agreements) with logistics partners to reduce transit time. 2. Decentralized Fulfillment: To address the "Northern Barrier," Olist should consider establishing mini-fulfillment centers in key Northern states to reduce shipping costs and time for high-velocity products. 3. Manage Expectations: Improve the algorithm for "Estimated Delivery Date" (ETA). It is better to promise a 10-day delivery and arrive in 9, than to promise 5 and arrive in 7. 💻 Tech Stack - Visualization: Microsoft Power BI - Data Cleaning: Power Query - Language: DAX (Data Analysis Expressions) - Modeling: Star Schema