Olist E-Commerce — Funnel Performance Analysis

Click here to explore the interactive dashboard →

Cross-filtering enabled click any chart element to filter the entire dashboard

This project delivers a full end-to-end analysis of the Olist Brazilian e-commerce marketplace, examining 104,458 orders across two years of platform operations.

The workflow covers every stage of the analytics pipeline:

Raw Data → SQL Cleaning → Feature Engineering → Exploratory Analysis → Executive Dashboard → Business Recommendations

The central business question driving this analysis:

"Where is the marketplace losing orders, revenue, and customers and what should leadership prioritise to fix it?"

1. Delivery speed is the single largest driver of customer satisfaction. Customers who receive orders in 0–3 days give an average of 4.48 stars. Customers who wait 15+ days give 3.72 stars. That 0.76-point gap across 33% of all orders represents a material, measurable damage to marketplace reputation.

2. Late delivery cuts satisfaction scores by 40%. On-time orders average 4.29 stars. Late orders average 2.56 stars. This is not a marginal difference,it is the difference between a loyal customer and a churned one.

3. R$1,947,012 in revenue is at risk every year. Orders that are late or undelivered represent 12.1% of total revenue at higher-than-average order values (late: R$166.24 avg, undelivered: R$186.97 avg). High-value orders are disproportionately exposed to fulfilment failure.

File: sql/olist_final_clean_script.sql 13 structured steps

The raw Olist dataset required significant preparation before it was fit for analysis. The cleaning pipeline was built in SQLite and executed in strict logical sequence:

Output: final_cleaned_dataset a single analytical table joining orders, reviews, payments, and customer location, ready for analysis.

File: sql/olist_eda.sql 12 structured queries

Platform: Tableau Public 7 charts, cross-filtering enabled

Stage               Orders     Drop-off    Drop-off Rate
─────────────────────────────────────────────────────────
Total Created       104,458         —              —
Total Approved      104,301       157           0.15%   ← payment failures
Total Shipped       102,579     1,722           1.65%   ← largest loss point
Total Delivered     101,324     1,255           1.22%   ← logistics failures

The platform retains 96.98% of orders through to successful delivery. However, the approval-to-shipment gap is the single largest point of order loss 1,722 orders failing to progress from approval to shipment. This points directly to seller-side operational failures: stock unavailability, slow pick-and-pack processes, or warehouse capacity constraints. Payment processing (0.15% loss) is not the problem. Fulfilment execution is.

The platform scaled from near-zero to 7,933 orders in a single month within 14 months of launch, an 818% increase. Critically, the 97% delivery rate was maintained throughout this scaling period, indicating that fulfilment infrastructure kept pace with demand growth. The persistent 33% of orders exceeding 15 days suggests, however, that delivery speed optimisation did not scale at the same rate as order volume.

The typical Olist customer waits 8–14 days for their order. Over a third wait more than 15 days. The relationship between speed and satisfaction is clear and consistent: every delivery window improvement corresponds to a measurable increase in review score. Fast delivery (0–3 days) reaches fewer than 5% of customers despite generating the highest satisfaction at 4.48.

The correlation between delivery and satisfaction is direct and severe. A late delivery reduces the average review score by 40%. A failed delivery produces reviews 59% lower than on-time delivery. The review score by delivery days confirms this is a linear relationship:

Every additional day of delivery time moves customers toward lower scores. Every day saved moves them toward five stars.

São Paulo delivers in 9 days and earns 4.17 stars. Bahia takes 19 days and earns 3.87 stars. This is not a minor regional variation, it is a structurally different customer experience for over 3,000 customers in a single state. Customers in remote northern regions are systematically receiving a slower, lower-satisfaction experience through no fault of their own. Geographic logistics infrastructure is the root cause.

Credit card transactions drive the highest revenue per order at R$163.14 and maintain strong delivery rates. Voucher orders generate 60% less revenue per transaction and have the lowest delivery rate at 95.2%, nearly 2 percentage points below credit card. Boleto's 19.9% share reflects Brazil's structural reliance on cash-equivalent payment infrastructure and represents a customer segment worth retaining.

Same-day approval is already the standard for 82.5% of orders. The 2,311 orders taking more than 3 days to approve show measurably lower delivery rates and satisfaction scores. While the absolute impact is smaller than logistics delays, streamlining the remaining slow approvals is a low-cost improvement with direct customer experience benefit.

-- Funnel analysis using CTE and UNION ALL (Step 2)
WITH funnel AS (
    SELECT 
        SUM(is_created) AS created,
        SUM(is_approved) AS approved,
        SUM(is_shipped) AS shipped,
        SUM(is_delivered) AS delivered
    FROM final_cleaned_dataset
)
SELECT 'Approved → Shipped' AS stage,
       approved - shipped AS drop_off,
       ROUND((approved - shipped) * 100.0 / approved, 2) AS drop_off_rate
FROM funnel;

-- Window function for percentage distribution (Step 4)
SELECT delivery_bucket,
       COUNT(*) AS orders,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM final_cleaned_dataset
GROUP BY delivery_bucket;

-- Date feature engineering using julianday() (Cleaning Step 6)
UPDATE clean_orders
SET delivery_days = julianday(order_delivered_customer_date) 
                  - julianday(order_purchase_timestamp);

I am a data analyst with a Master's degree in Accounting and a Google Data Analytics Certificate (Coursera), with an active portfolio of end-to-end analytics projects on GitHub.

My background spans financial analysis, accounting, and education, giving me the ability to connect data findings to business decisions in language that both technical and non-technical stakeholders can act on. I am actively transitioning into a full-time data analytics role and building a portfolio that demonstrates real analytical capability, not just tool familiarity.

This project represents a complete analytics pipeline built independently: from raw Kaggle data through a 13-step SQL cleaning process, 12-step exploratory analysis, and an interactive Tableau dashboard with cross-filtering and executive-level design.