Portfolio 2: Strategic BI Analysis of the Brazilian E-Commerce Platform (Olist)

📎For more details, click here

🎯 Objective

The primary objective of this project was to conduct a comprehensive end-to-end Business Intelligence (BI) analysis of Olist, a leading Brazilian e-commerce marketplace. Utilizing Power BI, Python, and MySQL, the project transformed raw transactional data into actionable insights to evaluate and enhance the platform’s operational performance, logistics efficiency, customer experience, and seller acquisition strategy.

The ultimate goal was to provide data-driven recommendations that support sustainable growth, improve customer retention, and strengthen marketplace competitiveness through analytical rigor and strategic insight.

Specifically, the analysis aimed to:

• Assess marketplace performance by analyzing sales patterns, revenue concentration, and customer purchasing behavior.
• Identify logistical inefficiencies across the order fulfillment and delivery pipeline.
• Uncover customer sentiment drivers through review and feedback data to pinpoint sources of satisfaction and dissatisfaction.
• Assess lead conversion and marketing channel performance to optimize seller acquisition.

⚙️ Tasks

• Data Extraction & Cleaning: Processed and cleaned a dataset of 96,100 unique customers, 3,100 active sellers, and ~99,500 orders using MySQL and Python, ensuring data integrity across 12 relational tables (orders, payments, reviews, geolocation).
• Exploratory Data Analysis (EDA): Conducted SQL-based analysis (CTEs, window functions) and Python visualizations (heatmaps, word clouds) to uncover patterns in sales trends, logistics performance, customer behavior, and lead conversion.
• Modeling & Visualization: Developed interactive Power BI dashboards to visualize key performance indicators (KPIs) across four domains: operational performance, logistics, customer reviews, and lead conversion.
• Strategic Recommendations: Formulated evidence-based strategies to address identified inefficiencies, leveraging machine learning, process optimization, and targeted marketing initiatives.

💡 Results

• Operational Performance: Identified sales peaks in August ($2.18M) and March ($1.77M), with a low average order value ($15.73) and 55% revenue concentration in three categories (Sports & Leisure, Health & Beauty, Cool Stuff). Proposed ML-based demand forecasting, free shipping thresholds, and category diversification to increase customer lifetime value by 10–15%. Highlighted a 93.6% one-time buyer rate, recommending loyalty programs to boost retention.
• Logistics Performance: Uncovered a 2.8-day seller-to-carrier handoff delay and regional disparities, with Southeast states dominating revenue. Suggested a dual logistics model (SP/RJ fulfillment hub, decentralized partnerships) and a Predictive Delivery Promise Engine to reduce fulfillment variability by 10%.
• Customer Reviews: Found negative reviews driven by delivery delays and product mismatches in high-risk categories (e.g., Furniture, Electronics). Recommended packaging compliance, seasonal capacity planning, and a 3-star recovery program to enhance satisfaction.
• Lead Conversion: Noted 62% of closed leads from high-intent channels (Paid Search, Organic Search, Direct Traffic) and 69.7% reseller dominance. Proposed scaling SEO, a tiered referral program, and a Manufacturer Enablement Program to diversify the seller ecosystem and reduce acquisition costs.

In summary, the analysis revealed Olist’s strengths in seller acquisition and sales volume but highlighted logistical bottlenecks and retention challenges. Strategic recommendations, including ML-driven logistics, loyalty initiatives, and ecosystem diversification, are projected to drive a 10–15% increase in customer lifetime value and enhance market penetration in underdeveloped regions.