Dataset Guide — E-commerce Analytics¶

Source¶

Dataset: Brazilian E-Commerce Public Dataset by Olist URL: https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce Structure: 8 related CSV files (a real relational schema)

The Tables and How They Relate¶

                  ┌─────────────────┐
                  │    customers    │
                  │ customer_id (PK)│
                  └────────┬────────┘
                           │
                  ┌────────▼────────┐         ┌──────────────────┐
                  │     orders      │◄────────│  order_payments  │
                  │  order_id (PK)  │         │   order_id (FK)  │
                  │ customer_id(FK) │         └──────────────────┘
                  └────────┬────────┘         ┌──────────────────┐
                           │            ◄─────│  order_reviews   │
                  ┌────────▼────────┐         │   order_id (FK)  │
                  │   order_items   │         └──────────────────┘
                  │  order_id (FK)  │
                  │ product_id (FK) │
                  │ seller_id (FK)  │
                  └───┬─────────┬───┘
                      │         │
              ┌───────▼──┐  ┌───▼──────┐
              │ products │  │ sellers  │
              └──────────┘  └──────────┘

Key Tables¶

orders (~100k rows)¶

Column	Description
`order_id`	PK
`customer_id`	FK to customers
`order_status`	delivered, shipped, canceled, etc.
`order_purchase_timestamp`	When the order was placed
`order_delivered_customer_date`	When it arrived
`order_estimated_delivery_date`	Promised date

order_items (~112k rows)¶

Column	Description
`order_id`	FK
`product_id`	FK
`seller_id`	FK
`price`	Item price
`freight_value`	Shipping cost

customers (~99k rows)¶

Column	Description
`customer_id`	PK (unique per order!)
`customer_unique_id`	The actual person (use this for repeat analysis)
`customer_city` / `customer_state`	Location

order_payments¶

Column	Description
`order_id`	FK
`payment_type`	credit_card, boleto, voucher, debit_card
`payment_installments`	Number of instalments
`payment_value`	Amount

order_reviews¶

Column	Description
`order_id`	FK
`review_score`	1 to 5
`review_comment_message`	Free text (Portuguese)

products¶

Column	Description
`product_id`	PK
`product_category_name`	Category (Portuguese — join translation table)

Critical Gotcha — customer_id vs customer_unique_id¶

The biggest trap in this dataset

customer_id is unique per order — a returning customer gets a NEW customer_id each time. To analyse repeat purchases or true customer LTV, you MUST use customer_unique_id, which identifies the actual person across orders. Using customer_id would make every customer look like a one-time buyer.

Other Notes¶

Category names are in Portuguese — there's a product_category_name_translation.csv to join for English names.
Reviews are in Portuguese — for text analysis you'd need translation; for this project, the numeric review_score is enough.
Some orders have no delivery date — they were cancelled or not yet delivered.
Data is genuinely clean — making it ideal for practising joins rather than fighting messiness.

Quick Exploration¶

import pandas as pd

orders = pd.read_csv("olist_orders_dataset.csv", parse_dates=[
    "order_purchase_timestamp", "order_delivered_customer_date",
    "order_estimated_delivery_date"])
items = pd.read_csv("olist_order_items_dataset.csv")
customers = pd.read_csv("olist_customers_dataset.csv")
reviews = pd.read_csv("olist_order_reviews_dataset.csv")

print(f"Orders: {len(orders)}, Items: {len(items)}, Reviews: {len(reviews)}")
print(f"Unique customers (by unique_id): {customers['customer_unique_id'].nunique()}")
print(f"Order rows (by customer_id): {customers['customer_id'].nunique()}")
# The two numbers differ — that gap is your repeat customers

← Business Problem · Next: Data Cleaning →