Olist E-Commerce Product Analytics

Overview

This project analyzes the Olist Brazilian E-Commerce Public Dataset (Kaggle) to practice and demonstrate product analytics skills: schema design, SQL querying (joins, window functions, cohort analysis), data validation, and dashboarding in Power BI.

The dataset covers September 2016 – October 2018. It was selected for its relational structure (7+ linked tables) and analytical depth — not for current-market relevance. Findings in this project describe patterns in a 2016–2018 snapshot of e-commerce activity in Brazil, not present-day trends.

Author: Ovidha Das Database: PostgreSQL

---

Dataset

Source: Olist Brazilian E-Commerce Public Dataset (Kaggle)

Table	Rows	Description
orders	99,441	Order status and lifecycle timestamps
customers	99,441	Customer location; customer_unique_id tracks repeat buyers
order_items	112,650	Line-item level product, seller, price, freight
order_payments	103,886	Payment type, installments, amount
order_reviews	99,224	Review score and comments
products	32,951	Product category and dimensions
sellers	3,095	Seller location
geolocation	1,000,163	Zip-code-level lat/lng reference data
product_category_translation	71	Portuguese → English category name mapping

Schema and table creation script: create_tables.sql

---

Data Quality & Validation

Before running any analysis, the dataset was profiled for completeness, referential integrity, and internal logical consistency. Key findings:

Row-level identity

• orders and customers both have 99,441 rows — a 1:1 relationship, since customer_id is generated per-order, not per-person.
• customers.customer_unique_id has only 96,096 distinct values, confirming ~3,345 rows belong to repeat customers. customer_unique_id, not customer_id, is the correct key for cohort/retention analysis.

Missing values

• Nulls in orders timestamp columns increase through the order lifecycle: order_approved_at (160 missing) → order_delivered_carrier_date (1,783) → order_delivered_customer_date (2,965). This reflects natural order attrition (cancellations, unavailable stock, etc.), not a data error.
• order_estimated_delivery_date has 0 nulls — every order gets an estimate at purchase time regardless of outcome.
• 610 products (1.9%) have no product_category_name. These were grouped under 'uncategorized' rather than excluded, to avoid silently dropping revenue from the category-level analysis.
• order_items, order_payments, order_reviews, and customers had no nulls in any analysis-relevant column.

Date range

• Orders span Sept 2016 – Oct 2018, matching the dataset's documentation.
• Order volume in the earliest months is minimal and uneven: only 329 orders total across Sept–Dec 2016, including a complete gap in November 2016 (zero orders). This is consistent with Olist's early platform ramp-up, not a data loading error — confirmed by directly querying that date range. The monthly revenue trend (Query 1) and any cohort-based analysis should treat this period as unrepresentative rather than a meaningful signal.

Orders with no line items

• 775 orders (0.8%) have no matching rows in order_items at all. Breaking down by order_status: unavailable (603), canceled (164), created (5), invoiced (2), shipped (1). 767 of 775 (99%) fall into statuses where having no items is expected — stock was never fulfilled or the order was abandoned/canceled before an item was finalized. The single shipped row is a genuine anomaly (an order can't logically ship with no items attached) and was not further investigated given its negligible scale.
• This explains the NULL revenue values that surface in the monthly revenue trend (Query 1) when using a LEFT JOIN from orders to order_items — those months contain a small number of these no-item orders, which have an order count but no revenue to sum.

Categorical values

• order_status: 97.0% of orders reach delivered. Remaining statuses: shipped (1,107), canceled (625), unavailable (609), invoiced (314), processing (301), created (5), approved (2). canceled orders are excluded from revenue/AOV calculations; unavailable orders should also be evaluated for exclusion since no sale was completed.
• payment_type: dominated by credit_card (76,795) and boleto (19,784, a common Brazilian bank-slip payment method), with voucher (5,775) and debit_card (1,529). 3 rows have payment_type = 'not_defined' and were excluded from payment-type analysis.

Numeric ranges

• price: R$0.85 – R$6,735.00, avg R$120.65 — no negative values.
• freight_value: R$0.00 – R$409.68, avg R$19.99 — no negative values.
• payment_value: R$0.00 – R$13,664.08, avg R$154.10.
  • 9 rows have payment_value = 0: 6 are voucher payments (plausibly legitimate — a secondary $0 payment row on an order paid in full by voucher elsewhere) and 3 are the same not_defined rows flagged above, which are likely broken records and were excluded from payment analysis.
• review_score: ranges 1–5 as expected, avg 4.09 — no invalid values.

Timestamp sequencing (business-logic check)

Checked whether the order lifecycle (purchase → approve → ship → deliver) ever occurs out of order:

• order_approved_at before order_purchase_timestamp: 0 rows — clean.
• order_delivered_carrier_date before order_approved_at: 1,359 rows (1.4%). Breaking these down by gap size:
  • 901 (66%) — gap under 24 hours, consistent with system logging lag between the payment and fulfillment systems rather than a real process failure.
  • 444 (33%) — gap of 1–7 days, plausibly reflecting payment review/bank processing delays.
  • 14 (~1%) — gap over 7 days (up to 171 days), likely genuine data errors; flagged for exclusion or individual review in lifecycle-sequence- sensitive queries (e.g. the funnel analysis).
• order_delivered_customer_date before order_delivered_carrier_date: 23 rows, all order_status = 'delivered'. Unlike the carrier/approval mismatch above, most (70%, 16 of 23) show multi-day gaps — up to 16 days — rather than logging noise typical of a timing artifact. Most likely explanation is an error in the carrier-handoff timestamp specifically, since delivery-to-customer is generally the more reliably logged event. These 23 rows were excluded from delivery-sequence-sensitive calculations.
• The 14 extreme carrier/approval outliers (>7 day gap, from the bucket above) are not independent errors: 11 of the 14 share an order_approved_at timestamp clustered within ~20 minutes on 2017-09-13, indicating a single batch processing event (likely a payment reconciliation or system backfill) rather than 11 separate data quality issues. The remaining 3 — including one 171-day outlier — are unrelated one-off anomalies. All 14 were excluded from lifecycle-timing- sensitive analysis.

Overall conclusion: the dataset is largely clean and internally consistent. All flagged issues above have been traced to plausible root causes (natural attrition, system logging lag, a single batch-processing event, or a small number of one-off errors) and are handled explicitly (exclusion, relabeling, or flagging) in the analysis queries rather than silently ignored.

Validation queries: eda.sql

---

Analysis

Full query set: olist_analysis.sql

1. Revenue & volume trend (monthly)
2. Funnel: order → payment → shipped → delivered
3. Cohort retention (by customer_unique_id, first-purchase-month cohorts)
4. Average order value + top categories
5. Delivery performance vs. review score
6. Extended analysis: seller performance, geographic breakdown, payment/ installment behavior, RFM segmentation

---

Power BI Dashboard

Two-page report built from custom SQL sources (Import mode):

Page 1 — Executive Overview

• KPI cards: total revenue, average order value, % on-time delivery
• Revenue and order volume trend lines (Sept 2016 – Oct 2018), annotated to flag the partial-data edges at both ends of the date range
• Order funnel (placed → paid → shipped → delivered)
• Top 10 categories by revenue (English category labels)
• Delivery performance vs. review score (late vs. on-time average rating)
• Headline-findings callout box

Page 2 — Cohort Retention Analysis

• Cohort retention matrix (heatmap) — cohort month × months since first order, color-scaled by retention %
• Custom tooltip page showing cohort size, customers returned, and retention % for the hovered cell
• Headline-findings callout box

.pbix file included in this repo; dashboard screenshots below.

The full interactive Power BI file (.pbix) exceeds GitHub's file size limit for direct upload. It's available here instead: Power BI Dashboard (.pbix) – Google Drive

---

Key Findings

• Revenue and order volume grew steadily from early 2017 through mid-2018, with a clear spike in November 2017 consistent with Black Friday. The first (Sept–Dec 2016) and last (Sept–Oct 2018) months show artificially low activity due to partial data coverage, not a real business swing.
• The order funnel is very healthy: 97% of placed orders reach delivered, with the largest (still small) drop-off between paid and shipped (98.2% conversion) — fulfillment, not payment, is the weakest stage, though only marginally.
• Late delivery has a strong, measurable impact on customer satisfaction: orders delivered late average a 2.57 review score vs. 4.29 for on-time orders — a 1.72-point gap. 8.11% of delivered orders arrive late.
• Health & Beauty is the top revenue category (R$1.44M), but revenue rank and average order value don't always align — the pcs category ranks lower in total revenue but has by far the highest AOV (~R$1,286), driven by high unit price rather than volume.
• Customer retention is very low. Only ~3.5% of customers (3,345 of 96,096) ever placed a second order across the entire 2-year dataset. Month-over-month cohort retention is typically under 1%, meaning Olist's customer base in this period was overwhelmingly one-time buyers rather than repeat purchasers. This is the most significant business finding in the dataset — for a company relying on repeat revenue, it would warrant investigation into customer experience, loyalty incentives, or category mix.
• Average order value overall is R$160.25, and total revenue across the (non-canceled) dataset is R$15.74M.