Nine Tables, One Business: Olist's Relational Model

Before training any model, I need to understand the shape of the data. And the Olist dataset isn't a single table, it's a genuine mini relational database: nine CSVs, each representing a business entity (order, item, payment, review, customer, seller, product), tied together by keys. I ran all of this in the 01_intro_eda.ipynb notebook, right inside the dataset's own directory, and left it public on Colab: check out the full notebook here.

The relational model

orders = pd.read_csv('olist_orders_dataset.csv')
order_items = pd.read_csv('olist_order_items_dataset.csv')
payments = pd.read_csv('olist_order_payments_dataset.csv')
reviews = pd.read_csv('olist_order_reviews_dataset.csv')
customers = pd.read_csv('olist_customers_dataset.csv')
sellers = pd.read_csv('olist_sellers_dataset.csv')
products = pd.read_csv('olist_products_dataset.csv')
geolocation = pd.read_csv('olist_geolocation_dataset.csv')
category_translation = pd.read_csv('product_category_name_translation.csv')

order_id is the key stitching almost everything together: it shows up in orders, order_items, payments, and reviews. customer_id, product_id, seller_id, and the zip code prefix close out the rest of the links. One detail worth flagging already: olist_order_reviews_dataset.csv has 104,719 raw lines of text in the file, but pandas only recognizes 99,224 real records when reading the CSV. The difference is because a lot of review comments contain literal line breaks inside the text field (the customer wrote across several paragraphs), and pandas' parser correctly counts that as one single record, while counting raw file lines overestimates. A good reminder that "number of lines in the file" and "number of records" aren't always the same thing in a CSV with free-text fields.

Data quality: nulls have a story

Not every null is a problem, sometimes it's information. Across the tables where nulls show up:

order_delivered_customer_date being null on 3% of orders isn't a capture error, it's an order that never arrived (canceled, lost, or still in transit when the dataset was frozen). That becomes an important feature down the line, when I build the delay scenario: an order with no delivery date has no way to compute a delay, so those 2,965 orders need explicit handling (excluded from the delay analysis, or treated as their own category), I can't just fill them with zero or the mean. Reviews missing a title or comment (88% and 59% of cases) aren't a problem either, most customers just leave the star rating and write nothing, completely normal e-commerce review behavior.

Building the master dataframe

The dataset's natural granularity is order item, not whole order: one order_id can have several items, from different sellers, each with its own price and freight. That's why the merge starts from order_items, not orders:

produtos_com_categoria_en = products.merge(category_translation, on='product_category_name', how='left')

mestre = (
    order_items
    .merge(orders, on='order_id', how='left')
    .merge(customers, on='customer_id', how='left')
    .merge(produtos_com_categoria_en, on='product_id', how='left')
    .merge(sellers, on='seller_id', how='left')
    .merge(payments, on='order_id', how='left')
    .merge(reviews, on='order_id', how='left')
)

order_items alone has 112,650 rows. After all the merges, the master dataframe has 118,310 rows, more than the starting point. That's not a bug: when an order is paid across several installments recorded as separate rows in payments, or gets more than one entry in reviews, the merge multiplies that order-item row for each combination. It's expected relational-merge behavior, but it's exactly the kind of thing that, if I don't check the shape before and after, slips by unnoticed and inflates counts in any later aggregation.

The four ML scenarios

With the master dataframe in hand, defined for the chapters ahead:

1. Delivery delay (binary classification): compare order_delivered_customer_date against order_estimated_delivery_date.
2. Review score (multiclass classification or regression): review_score, from 1 to 5.
3. Freight or order value (regression): freight_value, or the sum of price per order.
4. Customer segmentation (clustering, no target): Recency, Frequency, and Monetary value per customer, the RFM technique.

Orders per month: the Black Friday spike

A first temporal look, counting unique orders by purchase month:

Loading real data...

September 2016 starts with only 4 orders (Olist had barely launched), volume grows month over month through 2017, and November 2017 jumps sharply to 7,544 orders, against 4,631 in October and 5,673 in December of the same year. That's Black Friday, an isolated spike that breaks the smooth growth trend, and it's going to matter when I get to seasonality in the feature-engineering chapters. September and October 2018 show up with only 16 and 4 orders, a sign the dataset was frozen mid-month, not that sales collapsed.

Wrapping up the chapter

Relational model mapped, master dataframe assembled (118,310 rows), data quality checked (the delivery and review nulls have real explanations, not errors), and the four scenarios defined. Next chapter I get into the real visualization work: a geographic map of orders and delay by state, category distribution, payment method, and the relationship between delay and review score, which already gives an "aha moment" before training a single model.