# 100,000 orders told me why their customers don't come back

2026-07-12 · Source: Data Engineering on Medium · Field: Technology & Digital — Data Science & Analytics, E-commerce & Digital Commerce · Depth: Intermediate, short

Summary

The article describes building a data warehouse for Olist, a Brazilian marketplace, processing 99,441 orders from 2016 to 2018, which generated \$15.8 million in revenue. A critical finding was a low customer repurchase rate of 3.05%, with recurring customers contributing only 5.71% of revenue, indicating a heavy reliance on continuous new customer acquisition. This insight emerged only after constructing a data warehouse from nine disparate CSV files, highlighting the necessity of proper data infrastructure before analysis. The author also identified common data modeling pitfalls, such as fan-out issues from multiple payment methods or reviews per order, and incorrect customer identification where `customer_id` did not represent a unique person, leading to inflated metrics. Correct modeling revealed that delivery delays severely impact customer satisfaction (e.g., 4.21 rating on time vs. 1.70 with severe delay) and that the top quartile of sellers generates 86.58% of revenue.

Key takeaway

For Data Scientists or Data Engineers building analytical systems, rigorously validate your data models and JOIN operations. Naive joins or incorrect entity definitions can silently produce false metrics, leading to flawed business decisions. Prioritize data integrity and a well-structured data warehouse to ensure your insights reflect reality, especially when analyzing critical metrics like customer retention or revenue contribution. Your analytical rigor directly impacts business strategy.

Key insights

Data integrity and proper modeling are crucial for accurate business insights, preventing misleading conclusions.

Principles

• Raw data requires structuring before analysis.
• Naive JOINs can silently inflate metrics.
• Delivery timeliness dictates customer satisfaction.

Method

Build a layered data warehouse architecture (CSV -> Staging -> Star Model -> Views) to transform disparate files into a cohesive business model for reliable querying.

In practice

• Validate `customer_id` uniqueness before aggregation.
• Identify fan-out issues from one-to-many relationships.
• Prioritize delivery performance for customer retention.

Topics

• Data Warehouse
• Data Modeling
• Customer Retention
• E-commerce Analytics
• Data Quality
• Star Schema

Code references

• dnarram/olist-ecommerce-datawarehouse

Best for: Data Scientist, Data Engineer, AI Student

Related on AIssential

• How Data Warehouses Turn Chaos into Clarity — Data warehouse architecture transforms chaotic, multi-source data into trusted, actionable insights through structured processing layers.
• Your analysis was right. Nobody acted on it. — Effective communication of data insights to stakeholders is more critical for impact and career growth than technical accuracy.
• The 8-Point SQL Checklist That Distinguishes Junior Analysts from Senior Engineers — Accurate SQL querying demands a systematic pre-execution audit covering data grain, filtering, joins, NULL handling, and aggregate validation.
• Your LLM issues are really data issues — Inconsistent data and weak governance undermine analytics and ML, necessitating AI-ready data practices.
• Detecting Join Duplication — Silent join duplication corrupts data pipelines; systematic auditing of join keys, row counts, and match coverage is essential.
• Proven Data Warehouse Use Cases in 2025 — Data warehouses provide integrated, time-variant, and immutable data for strategic decision-making across industries.

See Counsel's argued verdicts on the open AI decisions leaders are weighing →

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Data Engineering on Medium.