Olist E-Commerce Data Analysis

Transforming 100k+ Brazilian marketplace transactions into strategic business intelligence. Features high-performance data processing, RFM Customer Segmentation, and Logistics Performance Tracking.

---

Project Overview

The goal of this project is to transform raw transactional data from Olist (the largest department store marketplace in Brazil) into actionable insights. By leveraging advanced data analytics techniques, we identify high-value customer segments and operational bottlenecks to optimize e-commerce growth strategies.

Data Source & Big Data Handling

The dataset consists of 100k+ orders from 2016 to 2018, distributed across 9 interrelated tables.

• Original Source: Olist Dataset on Kaggle
• Big Data Optimization: Used PySpark (Spark SQL & DataFrames) to handle large-scale joins and aggregations that would typically overwhelm standard Pandas memory limits.
• Efficiency: Implemented .cache() and .persist() strategies to optimize iterative analytical queries.
• Scalability Ready: While optimized with Pandas for the current 100k records, the environment and requirements.txt are pre-configured for PySpark 3.5.0 to handle future Big Data scalability

Technical Stack

• Language: Python
• Analysis & ETL: Pandas, NumPy.
• Scalability (Ready): PySpark, findspark.
• Visualization: Matplotlib, Seaborn.
• Environment: Google Colab.

Key Features & Workflow

1. Advanced ETL & Data Cleaning

• Engineered a pipeline to merge 9 relational tables (Orders, Items, Customers, Payments, etc.).
• Automated Portuguese-to-English category translations for global business accessibility.
• Processed complex timestamps to calculate delivery lead times.

2. Feature Engineering

• Logistics Tracking: Calculated delivery_days and estimated_diff to identify late shipments.
• Time-series Extraction: Derived purchase hours and weekdays to find peak shopping windows.

3. Customer Segmentation (RFM Model)

• Recency: Days since the last purchase.
• Frequency: Total number of unique orders per customer.
• Monetary: Total revenue generated by each customer.
• Scoring: Used Spark Window Functions (ntile) to rank customers from 1-5.

Project Dashboard

I visualize the core business metrics and customer segments to drive decision-making.

(Olist Business Insights Dashboard: RFM Distribution, Top Categories, and Delivery Performance.)

Business Insights & Interpretations

1. Customer Segmentation (RFM Analysis)

• The "Champions" Advantage (38.3%): Nearly 40% of the customer base consists of high-value shoppers who buy frequently and spend significantly.
  • Insight: This segment is the backbone of Olist’s revenue.
  • Strategy: Implement a VIP Loyalty Program with early access to new launches or exclusive shipping discounts to maintain their high engagement.
• The "At Risk" Alert (40.0%): A critical finding is that 40% of customers are in the "At Risk" category—those who were previously high-value but haven't made a purchase recently.
  • Insight: There is a significant risk of permanent churn.
  • Strategy: Launch an Automated Re-engagement Campaign (e.g., "We miss you" emails with a 10-15% discount code) to win back this massive segment.

2. Peak Shopping Hours (Time-Series Analysis)

• High-Activity Window: Transactions begin to surge at 08:00 AM, peaking between 10:00 AM - 12:00 PM, and remain consistently high until 04:00 PM.
  • Insight: Customers primarily shop during standard business hours (Work-from-home or office hours).
  • Strategy: Schedule Flash Sales and Push Notifications at 09:30 AM to capture the "peak wave" of daily traffic. Marketing spend should be minimized between 12:00 AM and 06:00 AM due to the lowest conversion rates.

3. Revenue Drivers (Product Performance)

• Category Dominance: Health & Beauty is the clear market leader, generating over 1.2M BRL in revenue, followed by Watches & Gifts and Housewares.
  • Insight: These categories are "Anchor Categories" that drive the most traffic and volume to the platform.
  • Strategy: Increase cross-selling efforts by suggesting lower-margin "Add-on" products (e.g., accessories) when customers purchase these high-value items.

4. Logistics Health (Delivery Performance)

• Reliability vs. Risk: While the vast majority of orders are On-time (Green bar), a visible segment of orders is marked as Late (Orange bar).
  • Insight: In E-commerce, even a 5-10% delay rate can severely damage the "Average Review Score" and brand trust.
  • Strategy: Cross-reference "Late" orders with geographic data to identify if specific Brazilian states (e.g., North/Northeast) or specific carriers are causing bottlenecks. Re-evaluate shipping partners in those identified zones.

Repository Structure

• data/: Relational CSV files from the Olist ecosystem.
• dashboard/: Visualization charts and the final Business Dashboard.
• notebooks/: Comprehensive PySpark Notebook with step-by-step ETL and Analysis.
• README.md: Project documentation and business summary.
• requirements.txt: List of required Python libraries.

Results & Business Insights

Actionable Intelligence:

• Customer Loyalty: Identified that the majority of the base are Potential Loyalists, requiring targeted CRM campaigns to convert them into Champions.
• Operational Excellence: Identified peak ordering hours between 10:00 AM - 02:00 PM, suggesting the optimal window for promotional push notifications.
• Product Strategy: The Health & Beauty and Housewares categories are the primary revenue drivers, justifying increased inventory focus.
• Logistics Health: While most orders are "On-time", the "Late" segment shows correlation with specific geographic regions, suggesting a need for carrier re-evaluation.

How to Run

To execute the full analytical pipeline, follow these steps:

1. Clone the repository:

   git clone [https://github.com/PhcPh4m/Olist-Ecommerce-Data-Analysis.git](https://github.com/PhcPh4m/Olist-Ecommerce-Data-Analysis.git)

2. Environment Setup (Google Colab Recommended):
   • Open the .ipynb file located in the notebooks/ folder using Google Colab.
   • Install the required libraries:

     pip install pandas matplotlib seaborn numpy

   • Note: This will automatically set up PySpark 3.5.0, Pandas, and visualization libraries.
3. Execute the Analytics Pipeline:
   • Simply select Runtime > Run all (or press Ctrl + F9).
   • The Automated Workflow will:
     1. Ingest Data: Automatically load 9 relational CSV files directly from the data/ folder.
     2. Process ETL: Execute data cleaning and complex table joins using optimized Pandas logic.
     3. Segment Customers: Perform RFM Scoring to categorize the 100k+ customer base.
     4. Generate Insights: Render the final Business Insights Dashboard for strategic evaluation.