Olist Brazilian E-Commerce¶
- Part 01 — Exploratory Data Analysis (EDA)
- Part 02 — Power BI Dashboard
- Part 03 — ML Model (coming soon)
- Part 04 — SQL Analysis (coming soon)
Part 01 — Exploratory Data Analysis (EDA)¶
Data source: Brazilian E-Commerce Public Dataset by Olist
License: CC BY-NC-SA 4.0
Contents
- Step 1 — Import Libraries
- Step 2 — Load Data (all tables)
- Step 3 — First Overview (shape, dtypes, head)
- Step 4 — Missing Values
- Step 5 — Duplicates
- Step 6 — Convert Data Types
- Step 7 — Analyze Distributions (Numeric Columns)
- Step 8 — Analyze Categorical Columns
- Step 9 — Visualizations (Matplotlib / Seaborn)
- Step 10 — Summary & Key Findings
↑ ContentsStep 1 — Import Libraries¶
In [2]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine, inspect
from pathlib import Path
# Auto-detect environment: Kaggle vs local
kaggle_input = Path('/kaggle/input')
if kaggle_input.exists():
db_path = next(kaggle_input.rglob('olist.sqlite')) # find it on Kaggle
else:
db_path = Path('..') / 'data' / 'raw' / 'olist.sqlite' # local
# Safety check first
print(db_path.exists()) # must be True
print(db_path.resolve()) # shows the full path
engine = create_engine(f'sqlite:///{db_path}')
inspector = inspect(engine)
tabellen = inspector.get_table_names()
print(tabellen)
True C:\Users\todor\Desktop\DAProjects\Olist-Brazilian-E-Commerce\data\raw\olist.sqlite ['customers', 'geolocation', 'leads_closed', 'leads_qualified', 'order_items', 'order_payments', 'order_reviews', 'orders', 'product_category_name_translation', 'products', 'sellers']
↑ ContentsStep 2 — Load Data (all tables)¶
In [3]:
df_customers = pd.read_sql("SELECT * FROM customers", engine)
df_geolocation = pd.read_sql("SELECT * FROM geolocation", engine)
df_leads_closed = pd.read_sql("SELECT * FROM leads_closed", engine)
df_leads_qualified = pd.read_sql("SELECT * FROM leads_qualified", engine).copy()
df_order_items = pd.read_sql("SELECT * FROM order_items", engine)
df_order_payments = pd.read_sql("SELECT * FROM order_payments", engine)
df_order_reviews = pd.read_sql("SELECT * FROM order_reviews", engine)
df_orders = pd.read_sql("SELECT * FROM orders", engine)
df_product_category_name_translation = pd.read_sql("SELECT * FROM product_category_name_translation", engine)
df_products = pd.read_sql("SELECT * FROM products", engine)
df_sellers = pd.read_sql("SELECT * FROM sellers", engine)
In [4]:
# Convert all at once
string_cols = ['customer_id', 'customer_unique_id', 'customer_city', 'customer_state']
df_customers[string_cols] = df_customers[string_cols].astype('string')
# Verify
print(df_customers.dtypes)
customer_id string[python] customer_unique_id string[python] customer_zip_code_prefix int64 customer_city string[python] customer_state string[python] dtype: object
| Table | Description | Key Columns |
|---|---|---|
df_orders |
Central table. One row per order — status, timestamps, links to customer | order_id, customer_id, order_status, 5× date columns, is_delivered |
df_customers |
One row per order-customer. customer_unique_id identifies the real person across orders |
customer_id, customer_unique_id, zip, city, state |
df_order_items |
One row per item in an order. An order can have multiple items from different sellers | order_id, product_id, seller_id, price, freight_value |
df_order_payments |
One row per payment. An order can be paid with multiple methods (card + voucher) | order_id, payment_type, payment_installments, payment_value |
df_order_reviews |
Customer review per order. Comment fields optional — most customers only left a score | order_id, review_score, review_comment_message, has_comment |
df_products |
One row per product. Category and physical dimensions. 610 rows have no category | product_id, product_category_name, weight, dimensions |
df_sellers |
One row per seller. Location data — used to calculate seller-to-customer distance | seller_id, zip, city, state |
df_geolocation |
GPS coordinates per zip code. Aggregated to one centroid per zip after deduplication | zip_code_prefix, lat, lng, city, state |
df_product_category_name_translation |
Lookup table — translates Portuguese category names to English | product_category_name, product_category_name_english |
df_leads_qualified |
Marketing leads that visited Olist’s landing page. First step in the seller funnel | mql_id, first_contact_date, landing_page_id, origin |
df_leads_closed |
Leads that converted to sellers. Links back to leads_qualified via mql_id | mql_id, seller_id, won_date, business_type, has_profile_data |
↑ ContentsStep 3 — First Overview (shape, dtypes, head)¶
In [5]:
print('Table customers:')
print(df_customers.head(2))
print(df_customers.shape)
print(df_customers.dtypes)
print()
Table customers:
customer_id customer_unique_id \
0 06b8999e2fba1a1fbc88172c00ba8bc7 861eff4711a542e4b93843c6dd7febb0
1 18955e83d337fd6b2def6b18a428ac77 290c77bc529b7ac935b93aa66c333dc3
customer_zip_code_prefix customer_city customer_state
0 14409 franca SP
1 9790 sao bernardo do campo SP
(99441, 5)
sellers :
customer_id string[python]
customer_unique_id string[python]
customer_zip_code_prefix int64
customer_city string[python]
customer_state string[python]
dtype: object
In [6]:
print('Table geolocation:')
print(df_geolocation.head(2))
print(df_geolocation.shape)
print(df_geolocation.dtypes)
print()
Table geolocation: geolocation_zip_code_prefix geolocation_lat geolocation_lng \ 0 1037 -23.545621 -46.639292 1 1046 -23.546081 -46.644820 geolocation_city geolocation_state 0 sao paulo SP 1 sao paulo SP (1000163, 5) geolocation : geolocation_zip_code_prefix int64 geolocation_lat float64 geolocation_lng float64 geolocation_city object geolocation_state object dtype: object
In [7]:
print('Table leads_closed:')
print(df_leads_closed.head(2))
print(df_leads_closed.shape)
print(df_leads_closed.dtypes)
print()
Table leads_closed:
mql_id seller_id \
0 5420aad7fec3549a85876ba1c529bd84 2c43fb513632d29b3b58df74816f1b06
sdr_id sr_id \
0 a8387c01a09e99ce014107505b92388c 4ef15afb4b2723d8f3d81e51ec7afefe
won_date business_segment lead_type lead_behaviour_profile \
0 2018-02-26 19:58:54 pet online_medium cat
has_company has_gtin average_stock business_type \
0 NaN NaN None reseller
declared_product_catalog_size declared_monthly_revenue
0 NaN 0.0
(99441, 5)
leads_closed :
mql_id object
seller_id object
sdr_id object
sr_id object
won_date object
business_segment object
lead_type object
lead_behaviour_profile object
has_company float64
has_gtin float64
average_stock object
business_type object
declared_product_catalog_size float64
declared_monthly_revenue float64
dtype: object
In [8]:
print('Table leads_qualified:')
print(df_leads_qualified.head(2))
print(df_leads_qualified.shape)
print(df_leads_qualified.dtypes)
print()
Table leads_qualified:
mql_id first_contact_date \
0 dac32acd4db4c29c230538b72f8dd87d 2018-02-01
1 8c18d1de7f67e60dbd64e3c07d7e9d5d 2017-10-20
landing_page_id origin
0 88740e65d5d6b056e0cda098e1ea6313 social
1 007f9098284a86ee80ddeb25d53e0af8 paid_search
(99441, 5)
leads_qualified :
mql_id object
first_contact_date object
landing_page_id object
origin object
dtype: object
In [9]:
print('Table order_items:')
print(df_order_items.head(2))
print(df_order_items.shape)
print(df_order_items.dtypes)
print()
Table order_items:
order_id order_item_id \
0 00010242fe8c5a6d1ba2dd792cb16214 1
1 00018f77f2f0320c557190d7a144bdd3 1
product_id seller_id \
0 4244733e06e7ecb4970a6e2683c13e61 48436dade18ac8b2bce089ec2a041202
1 e5f2d52b802189ee658865ca93d83a8f dd7ddc04e1b6c2c614352b383efe2d36
shipping_limit_date price freight_value
0 2017-09-19 09:45:35 58.9 13.29
1 2017-05-03 11:05:13 239.9 19.93
(99441, 5)
order_items :
order_id object
order_item_id int64
product_id object
seller_id object
shipping_limit_date object
price float64
freight_value float64
dtype: object
In [10]:
print('Table order_payments:')
print(df_order_payments.head(2))
print(df_order_payments.shape)
print(df_order_payments.dtypes)
print()
Table order_payments:
order_id payment_sequential payment_type \
0 b81ef226f3fe1789b1e8b2acac839d17 1 credit_card
1 a9810da82917af2d9aefd1278f1dcfa0 1 credit_card
payment_installments payment_value
0 8 99.33
1 1 24.39
(99441, 5)
order_payments :
order_id object
payment_sequential int64
payment_type object
payment_installments int64
payment_value float64
dtype: object
In [11]:
print('Table order_reviews:')
print(df_order_reviews.head(2))
print(df_order_reviews.shape)
print(df_order_reviews.dtypes)
print()
Table order_reviews:
review_id order_id \
0 7bc2406110b926393aa56f80a40eba40 73fc7af87114b39712e6da79b0a377eb
1 80e641a11e56f04c1ad469d5645fdfde a548910a1c6147796b98fdf73dbeba33
review_score review_comment_title review_comment_message \
0 4 None None
1 5 None None
review_creation_date review_answer_timestamp
0 2018-01-18 00:00:00 2018-01-18 21:46:59
1 2018-03-10 00:00:00 2018-03-11 03:05:13
(99441, 5)
order_reviews :
review_id object
order_id object
review_score int64
review_comment_title object
review_comment_message object
review_creation_date object
review_answer_timestamp object
dtype: object
In [12]:
print('Table orders:')
print(df_orders.head(2))
print(df_orders.shape)
print(df_orders.dtypes)
print()
Table orders:
order_id customer_id \
0 e481f51cbdc54678b7cc49136f2d6af7 9ef432eb6251297304e76186b10a928d
1 53cdb2fc8bc7dce0b6741e2150273451 b0830fb4747a6c6d20dea0b8c802d7ef
order_status order_purchase_timestamp order_approved_at \
0 delivered 2017-10-02 10:56:33 2017-10-02 11:07:15
1 delivered 2018-07-24 20:41:37 2018-07-26 03:24:27
order_delivered_carrier_date order_delivered_customer_date \
0 2017-10-04 19:55:00 2017-10-10 21:25:13
1 2018-07-26 14:31:00 2018-08-07 15:27:45
order_estimated_delivery_date
0 2017-10-18 00:00:00
1 2018-08-13 00:00:00
(99441, 5)
orders :
order_id object
customer_id object
order_status object
order_purchase_timestamp object
order_approved_at object
order_delivered_carrier_date object
order_delivered_customer_date object
order_estimated_delivery_date object
dtype: object
In [13]:
print('Table product_category_name_translation:')
print(df_product_category_name_translation.head(2))
print(df_product_category_name_translation.shape)
print(df_product_category_name_translation.dtypes)
print()
Table product_category_name_translation:
product_category_name product_category_name_english
0 beleza_saude health_beauty
1 informatica_acessorios computers_accessories
(99441, 5)
product_category_name_translation :
product_category_name object
product_category_name_english object
dtype: object
In [14]:
print('Table products:')
print(df_products.head(2))
print(df_products.shape)
print(df_products.dtypes)
print()
Table products:
product_id product_category_name \
0 1e9e8ef04dbcff4541ed26657ea517e5 perfumaria
1 3aa071139cb16b67ca9e5dea641aaa2f artes
product_name_lenght product_description_lenght product_photos_qty \
0 40.0 287.0 1.0
1 44.0 276.0 1.0
product_weight_g product_length_cm product_height_cm product_width_cm
0 225.0 16.0 10.0 14.0
1 1000.0 30.0 18.0 20.0
(99441, 5)
products :
product_id object
product_category_name object
product_name_lenght float64
product_description_lenght float64
product_photos_qty float64
product_weight_g float64
product_length_cm float64
product_height_cm float64
product_width_cm float64
dtype: object
In [15]:
print('Table sellers:')
print(df_sellers.head(2))
print(df_sellers.shape)
print(df_sellers.dtypes)
print()
Table sellers:
seller_id seller_zip_code_prefix seller_city \
0 3442f8959a84dea7ee197c632cb2df15 13023 campinas
1 d1b65fc7debc3361ea86b5f14c68d2e2 13844 mogi guacu
seller_state
0 SP
1 SP
(99441, 5)
sellers :
seller_id object
seller_zip_code_prefix int64
seller_city object
seller_state object
dtype: object
↑ ContentsStep 4 — Missing Values¶
4.1 customers — Missing Values¶
Finding: No missing values found in any column. Dataset is complete.
In [16]:
print(df_customers.isnull().sum())
customer_id 0 customer_unique_id 0 customer_zip_code_prefix 0 customer_city 0 customer_state 0 dtype: int64
4.2 geolocation — Missing Values¶
Finding: No missing values found in any column. Dataset is complete.
In [17]:
print(df_geolocation.isnull().sum())
geolocation_zip_code_prefix 0 geolocation_lat 0 geolocation_lng 0 geolocation_city 0 geolocation_state 0 dtype: int64
4.3 leads_closed — Missing Values¶
Finding: 4 columns have >90% missing values (has_company, has_gtin, average_stock, declared_product_catalog_size).
These were optional onboarding fields — most leads did not fill them in.
Strategy:
- Columns with >90% missing values are not dropped → no information loss
- Instead, a binary column
has_profile_datais created (1= data provided,0= not provided) - Columns with <5% missing values are filled with
"Unknown"
ML relevance:
has_profile_datacan be used as a feature — leads who provide complete information may show higher conversion potential.
In [18]:
print( df_leads_closed.isnull().sum())
cols_sparse = ['has_company', 'has_gtin', 'average_stock', 'declared_product_catalog_size']
# Create binary column
df_leads_closed['has_profile_data'] = df_leads_closed[cols_sparse].notnull().any(axis=1).astype(int)
# Drop original columns
df_leads_closed.drop(columns=cols_sparse, inplace=True)
# Fill small missing values
df_leads_closed['business_segment'] = df_leads_closed['business_segment'].fillna('Unknown')
df_leads_closed['business_type'] = df_leads_closed['business_type'].fillna('Unknown')
df_leads_closed['lead_type'] = df_leads_closed['lead_type'].fillna('Unknown')
df_leads_closed['lead_behaviour_profile'] = df_leads_closed['lead_behaviour_profile'].fillna('Unknown')
mql_id 0 seller_id 0 sdr_id 0 sr_id 0 won_date 0 business_segment 1 lead_type 6 lead_behaviour_profile 177 has_company 779 has_gtin 778 average_stock 776 business_type 10 declared_product_catalog_size 773 declared_monthly_revenue 0 dtype: int64
4.4 leads_qualified — Missing Values¶
Finding: Only 60 missing values found (<1% of total rows).
Strategy: Rows dropped — negligible data loss.
In [19]:
# print( df_leads_qualified.isnull().sum())
print(f'Row count: {df_leads_qualified["origin"].count()}, Null values: {df_leads_qualified["origin"].isnull().sum()}')
df_leads_qualified= df_leads_qualified.dropna()
print( df_leads_qualified.isnull().sum() )
Row count: 7940, Null values: 60 mql_id 0 first_contact_date 0 landing_page_id 0 origin 0 dtype: int64
4.5 order_items — Missing Values¶
Finding: No missing values found in any column. Dataset is complete.
In [20]:
print( df_order_items.isnull().sum() )
order_id 0 order_item_id 0 product_id 0 seller_id 0 shipping_limit_date 0 price 0 freight_value 0 dtype: int64
4.6 order_payments — Missing Values¶
Finding: No missing values found in any column. Dataset is complete.
In [21]:
print( df_order_payments.isnull().sum() )
order_id 0 payment_sequential 0 payment_type 0 payment_installments 0 payment_value 0 dtype: int64
4.7 order_reviews — Missing Values¶
Finding: review_comment_title (88%) and review_comment_message (59%) have missing values.
These are not data errors — customers simply chose not to leave a written comment.
Strategy:
- Binary column
has_commentcreated (1= comment written,0= no comment) - Missing text fields filled with
"No comment"
ML relevance:
has_commentmay correlate with review score — dissatisfied customers tend to write more comments.
In [22]:
print( df_order_reviews.isnull().sum() )
# print( df_order_reviews.count() )
df_order_reviews['has_comment'] = df_order_reviews['review_comment_message'].notnull().astype(int)
df_order_reviews['review_comment_title'] = df_order_reviews['review_comment_title'].fillna('No comment')
df_order_reviews['review_comment_message'] = df_order_reviews['review_comment_message'].fillna('No comment')
review_id 0 order_id 0 review_score 0 review_comment_title 87656 review_comment_message 58247 review_creation_date 0 review_answer_timestamp 0 dtype: int64
4.8 orders — Missing Values¶
Finding: 3 date columns have missing values — this is expected behavior, not data errors.
| Column | Missing | Reason |
|---|---|---|
order_approved_at |
160 | Orders that were cancelled before approval |
order_delivered_carrier_date |
1783 | Orders not yet picked up by carrier |
order_delivered_customer_date |
2965 | Orders not yet delivered to customer |
Strategy: Dates are kept as NaT — filling with a placeholder would corrupt delivery time calculations.
Binary column is_delivered created (1 = delivered, 0 = not delivered).
ML relevance:
is_deliveredand delivery delay (estimated vs actual) are strong features for predicting customer satisfaction.
In [23]:
print( df_orders.isnull().sum() )
# print( df_orders.count() )
# Binary column — delivered or not
df_orders['is_delivered'] = df_orders['order_delivered_customer_date'].notnull().astype(int)
order_id 0 customer_id 0 order_status 0 order_purchase_timestamp 0 order_approved_at 160 order_delivered_carrier_date 1783 order_delivered_customer_date 2965 order_estimated_delivery_date 0 dtype: int64
4.9 product_category_name_translation — Missing Values¶
Finding: No missing values found in any column. Dataset is complete.
In [24]:
print( df_product_category_name_translation.isnull().sum() )
product_category_name 0 product_category_name_english 0 dtype: int64
4.10 products — Missing Values¶
| Column | Missing | Strategy |
|---|---|---|
product_category_name + description fields |
610 (1.8%) | Filled with "unknown" — products exist but have no category assigned |
product_weight_g, dimensions |
2 | Rows dropped — physical dimensions are required for analysis |
In [25]:
print( df_products.isnull().sum() )
# print( df_products.count() )
df_products['product_category_name'] = df_products['product_category_name'].fillna('unknown')
df_products['product_name_lenght'] = df_products['product_name_lenght'].fillna(0)
df_products['product_description_lenght'] = df_products['product_description_lenght'].fillna(0)
df_products['product_photos_qty'] = df_products['product_photos_qty'].fillna(0)
# Drop the 2 rows with missing dimensions — truly unusable
df_products = df_products.dropna(subset=['product_weight_g', 'product_length_cm'])
product_id 0 product_category_name 610 product_name_lenght 610 product_description_lenght 610 product_photos_qty 610 product_weight_g 2 product_length_cm 2 product_height_cm 2 product_width_cm 2 dtype: int64
4.11 sellers — Missing Values¶
Finding: No missing values found in any column. Dataset is complete.
In [26]:
print( df_sellers.isnull().sum() )
seller_id 0 seller_zip_code_prefix 0 seller_city 0 seller_state 0 dtype: int64
↑ ContentsStep 5 — Duplicates¶
Finding: No duplicates found in any table except geolocation.
geolocation: 261,831 duplicate rows — expected behavior.
Each zip code prefix covers a geographic area with multiple GPS coordinates.
Strategy: Aggregated by zip_code_prefix — mean lat/lng used as centroid per area.
In [27]:
print( df_customers.duplicated().sum() )
print( df_geolocation.duplicated().sum() )
print( df_leads_closed.duplicated().sum() )
print( df_leads_qualified.duplicated().sum() )
print( df_order_items.duplicated().sum() )
print( df_order_payments.duplicated().sum() )
print( df_order_reviews.duplicated().sum() )
print( df_orders.duplicated().sum() )
print( df_product_category_name_translation.duplicated().sum() )
print( df_products.duplicated().sum() )
print( df_sellers.duplicated().sum() )
# Keep only the centroid per ZIP for analysis
df_geolocation = df_geolocation.groupby('geolocation_zip_code_prefix').agg(
geolocation_lat=('geolocation_lat', 'mean'),
geolocation_lng=('geolocation_lng', 'mean'),
geolocation_city=('geolocation_city', 'first'),
geolocation_state=('geolocation_state', 'first')
).reset_index()
0 261831 0 0 0 0 0 0 0 0 0
↑ ContentsStep 6 — Convert Data Types¶
6.1 Convert customers¶
ID and location columns are converted from object to string — enables consistent text operations and reduces memory usage.
In [28]:
# Convert all string columns at once
string_cols = ['customer_id', 'customer_unique_id', 'customer_city', 'customer_state', 'customer_zip_code_prefix']
df_customers[string_cols] = df_customers[string_cols].astype('string')
# Verify
print(df_customers.dtypes)
customer_id string[python] customer_unique_id string[python] customer_zip_code_prefix string[python] customer_city string[python] customer_state string[python] dtype: object
6.2 Convert geolocation¶
geolocation_city and geolocation_state columns are converted from object to string — enables consistent text operations and reduces memory usage.
In [29]:
print( df_geolocation.dtypes )
string_cols = ['geolocation_zip_code_prefix', 'geolocation_city', 'geolocation_state']
df_geolocation[string_cols] = df_geolocation[string_cols].astype('string')
geolocation_zip_code_prefix int64 geolocation_lat float64 geolocation_lng float64 geolocation_city object geolocation_state object dtype: object
6.3 Convert df_leads_closed¶
ID columns converted to string, won_date converted to datetime.
IDs are identifiers, not numbers — string type ensures safe joins across tables.
In [30]:
string_cols = ['mql_id', 'seller_id', 'sdr_id', 'sr_id',
'business_segment', 'lead_type', 'lead_behaviour_profile', 'business_type']
df_leads_closed[string_cols] = df_leads_closed[string_cols].astype('string')
# won_date as datetime
df_leads_closed['won_date'] = pd.to_datetime(df_leads_closed['won_date'])
# Verify
print(df_leads_closed.dtypes)
mql_id string[python] seller_id string[python] sdr_id string[python] sr_id string[python] won_date datetime64[ns] business_segment string[python] lead_type string[python] lead_behaviour_profile string[python] business_type string[python] declared_monthly_revenue float64 has_profile_data int64 dtype: object
6.4 Convert leads_qualified¶
ID columns converted to string, first_contact_date converted to datetime.
In [31]:
string_cols = ['mql_id', 'landing_page_id', 'origin']
df_leads_qualified[string_cols] = df_leads_qualified[string_cols].astype('string')
# first_contact_date as datetime
df_leads_qualified['first_contact_date'] = pd.to_datetime(df_leads_qualified['first_contact_date'])
# Verify
print(df_leads_qualified.dtypes)
mql_id string[python] first_contact_date datetime64[ns] landing_page_id string[python] origin string[python] dtype: object
6.5 Convert order_items¶
ID columns converted to string, shipping_limit_date converted to datetime.
order_item_id, price and freight_value remain numeric — used for calculations.
In [32]:
string_cols = ['order_id', 'product_id', 'seller_id']
df_order_items[string_cols] = df_order_items[string_cols].astype('string')
# shipping_limit_date as datetime
df_order_items['shipping_limit_date'] = pd.to_datetime(df_order_items['shipping_limit_date'])
# Verify
print(df_order_items.dtypes)
order_id string[python] order_item_id int64 product_id string[python] seller_id string[python] shipping_limit_date datetime64[ns] price float64 freight_value float64 dtype: object
6.6 Convert order_payments¶
order_id and payment_type converted to string.
payment_sequential, payment_installments and payment_value remain numeric — used for calculations.
In [33]:
df_order_payments['order_id'] = df_order_payments['order_id'].astype('string')
df_order_payments['payment_type'] = df_order_payments['payment_type'].astype('string')
# Verify
print(df_order_payments.dtypes)
order_id string[python] payment_sequential int64 payment_type string[python] payment_installments int64 payment_value float64 dtype: object
6.7 Convert order_reviews¶
ID and text columns converted to string, date columns converted to datetime.
review_score and has_comment remain numeric — used for analysis and ML.
In [34]:
string_cols = ['review_id', 'order_id', 'review_comment_title', 'review_comment_message']
df_order_reviews[string_cols] = df_order_reviews[string_cols].astype('string')
# Date columns as datetime
df_order_reviews['review_creation_date'] = pd.to_datetime(df_order_reviews['review_creation_date'])
df_order_reviews['review_answer_timestamp'] = pd.to_datetime(df_order_reviews['review_answer_timestamp'])
# Verify
print(df_order_reviews.dtypes)
review_id string[python] order_id string[python] review_score int64 review_comment_title string[python] review_comment_message string[python] review_creation_date datetime64[ns] review_answer_timestamp datetime64[ns] has_comment int64 dtype: object
6.8 Convert orders¶
ID and status columns converted to string, all 5 date columns converted to datetime.
is_delivered remains numeric — used for ML.
In [35]:
string_cols = ['order_id', 'customer_id', 'order_status']
df_orders[string_cols] = df_orders[string_cols].astype('string')
# Date columns as datetime
date_cols = ['order_purchase_timestamp', 'order_approved_at',
'order_delivered_carrier_date', 'order_delivered_customer_date',
'order_estimated_delivery_date']
df_orders[date_cols] = df_orders[date_cols].apply(pd.to_datetime)
# is_delivered stays int64
# Verify
print(df_orders.dtypes)
order_id string[python] customer_id string[python] order_status string[python] order_purchase_timestamp datetime64[ns] order_approved_at datetime64[ns] order_delivered_carrier_date datetime64[ns] order_delivered_customer_date datetime64[ns] order_estimated_delivery_date datetime64[ns] is_delivered int64 dtype: object
6.9 Convert product_category_name_translation¶
Both columns converted to string — pure text reference table, no numeric values.
In [36]:
df_product_category_name_translation = df_product_category_name_translation.astype('string')
# Verify
print(df_product_category_name_translation.dtypes)
product_category_name string[python] product_category_name_english string[python] dtype: object
6.10 Convert products¶
product_id and product_category_name converted to string.
All physical dimensions and counts remain float64 — used for analysis and ML.
In [37]:
string_cols = ['product_id', 'product_category_name']
df_products[string_cols] = df_products[string_cols].astype('string')
# Verify
print(df_products.dtypes)
product_id string[python] product_category_name string[python] product_name_lenght float64 product_description_lenght float64 product_photos_qty float64 product_weight_g float64 product_length_cm float64 product_height_cm float64 product_width_cm float64 dtype: object
6.11 Convert sellers¶
All columns converted to string — ID, zip code and location fields are identifiers, not numbers.
In [38]:
string_cols = ['seller_id', 'seller_zip_code_prefix', 'seller_city', 'seller_state']
df_sellers[string_cols] = df_sellers[string_cols].astype('string')
# Verify
print(df_sellers.dtypes)
seller_id string[python] seller_zip_code_prefix string[python] seller_city string[python] seller_state string[python] dtype: object
↑ ContentsStep 7 — Analyze Distributions (Numeric Columns)¶
7.1 customers — describe()¶
In [44]:
print(df_customers.describe())
customer_id customer_unique_id \
count 99441 99441
unique 99441 96096
top 18955e83d337fd6b2def6b18a428ac77 8d50f5eadf50201ccdcedfb9e2ac8455
freq 1 17
customer_zip_code_prefix customer_city customer_state
count 99441 99441 99441
unique 14994 4119 27
top 22790 sao paulo SP
freq 142 15540 41746
| Insight | Value |
|---|---|
| Total customers | 99,441 — all unique, no duplicates |
| ZIP code areas | 14,994 distinct areas across Brazil |
| Top city | São Paulo — 15,540 customers (15.6%) |
| Top state | SP — 41,746 customers (42%) |
Finding: Customer base is heavily concentrated in São Paulo state — relevant for regional sales analysis.
7.2 geolocation — describe()¶
In [46]:
df_geolocation = df_geolocation[
(df_geolocation['geolocation_lat'].between(-33.75, 5.27)) &
(df_geolocation['geolocation_lng'].between(-73.98, -28.85))
]
print(df_geolocation.describe())
geolocation_lat geolocation_lng count 19006.000000 19006.000000 mean -19.073481 -46.076416 std 7.243093 5.201544 min -33.689948 -72.916069 25% -23.564197 -49.006838 50% -22.419894 -46.632322 75% -15.616915 -43.260094 max 5.013519 -29.384846
Finding: Invalid coordinates detected — lat max 42.18 (Europe) and lng max 121.10 (China).
Brazil bounds: lat (-33.75, 5.27), lng (-73.98, -28.85).
Strategy: Rows outside Brazil’s geographic bounds removed.
7.3 leads_closed — describe()¶
In [47]:
print(df_leads_closed.describe())
won_date declared_monthly_revenue \
count 842 8.420000e+02
mean 2018-04-23 06:04:00.891923968 7.337768e+04
min 2017-12-05 02:00:00 0.000000e+00
25% 2018-03-07 12:48:36.249999872 0.000000e+00
50% 2018-04-13 21:22:15 0.000000e+00
75% 2018-05-21 17:38:27.500000 0.000000e+00
max 2018-11-14 18:04:19 5.000000e+07
std NaN 1.744799e+06
has_profile_data
count 842.000000
mean 0.102138
min 0.000000
25% 0.000000
50% 0.000000
75% 0.000000
max 1.000000
std 0.303009
| Insight | Value |
|---|---|
| Date range | Dec 2017 — Nov 2018 (~1 year) |
| Median monthly revenue | 0 — 75% of leads declared no revenue |
| Max monthly revenue | 50,000,000 — extreme outlier |
| has_profile_data mean | 0.10 — only 10% of leads provided profile data |
Finding:
declared_monthly_revenueis heavily skewed — most leads declared 0, a few declared very high values. Log transformation may be needed before ML.
7.4 leads_qualified — describe()¶
In [48]:
print(df_leads_qualified.describe())
first_contact_date count 7940 mean 2018-02-05 11:49:39.748110848 min 2017-06-14 00:00:00 25% 2017-12-31 00:00:00 50% 2018-02-25 00:00:00 75% 2018-04-15 00:00:00 max 2018-05-31 00:00:00
| Insight | Value |
|---|---|
| Total leads | 7,940 |
| Date range | Jun 2017 — May 2018 (~1 year) |
| Median contact date | Feb 25, 2018 |
Finding: Dataset covers exactly 1 year of marketing activity. Useful for analyzing lead volume trends over time (monthly/quarterly).
7.5 order_items — describe()¶
In [ ]:
print(df_order_items.describe() )
order_item_id shipping_limit_date price \
count 112650.000000 112650 112650.000000
mean 1.197834 2018-01-07 15:36:52.192685312 120.653739
min 1.000000 2016-09-19 00:15:34 0.850000
25% 1.000000 2017-09-20 20:57:27.500000 39.900000
50% 1.000000 2018-01-26 13:59:35 74.990000
75% 1.000000 2018-05-10 14:34:00.750000128 134.900000
max 21.000000 2020-04-09 22:35:08 6735.000000
std 0.705124 NaN 183.633928
freight_value
count 112650.000000
mean 19.990320
min 0.000000
25% 13.080000
50% 16.260000
75% 21.150000
max 409.680000
std 15.806405
| Insight | Value |
|---|---|
| Total items | 112,650 |
| Median price | R$ 74.99 — mean R$ 120.65 (skewed by outliers) |
| Max price | R$ 6,735 — extreme outlier |
| Median freight | R$ 16.26 |
| Max freight | R$ 409.68 |
| Items per order | 75% of orders have only 1 item (max 21) |
Finding:
priceis heavily right-skewed — log transformation recommended before ML.
shipping_limit_datemax is 2020 while order data ends 2018 — possible data quality issue.
7.6 order_payments — describe()¶
In [50]:
print(df_order_payments.describe())
payment_sequential payment_installments payment_value count 103886.000000 103886.000000 103886.000000 mean 1.092679 2.853349 154.100380 std 0.706584 2.687051 217.494064 min 1.000000 0.000000 0.000000 25% 1.000000 1.000000 56.790000 50% 1.000000 1.000000 100.000000 75% 1.000000 4.000000 171.837500 max 29.000000 24.000000 13664.080000
| Insight | Value |
|---|---|
| Total payments | 103,886 |
| Median payment value | R$ 100.00 — mean R$ 154.10 (skewed by outliers) |
| Max payment value | R$ 13,664 — extreme outlier |
| Median installments | 1 — 75% pay in max 4 installments |
| Max installments | 24 — installment payments are common in Brazil |
| Min installments | 0 — possible data anomaly |
Finding: Installment payments (
payment_installments) reflect Brazilian consumer behavior — paying in multiple installments is culturally common.
payment_installments = 0should be investigated — likely voucher or gift card payments.
7.7 order_reviews — describe()¶
In [51]:
print(df_order_reviews.describe())
review_score review_creation_date \
count 99224.000000 99224
mean 4.086421 2018-01-12 20:49:23.948238336
min 1.000000 2016-10-02 00:00:00
25% 4.000000 2017-09-23 00:00:00
50% 5.000000 2018-02-02 00:00:00
75% 5.000000 2018-05-16 00:00:00
max 5.000000 2018-08-31 00:00:00
std 1.347579 NaN
review_answer_timestamp has_comment
count 99224 99224.000000
mean 2018-01-16 00:23:56.977938688 0.412975
min 2016-10-07 18:32:28 0.000000
25% 2017-09-27 01:53:27.249999872 0.000000
50% 2018-02-04 22:41:47.500000 0.000000
75% 2018-05-20 12:11:21.500000 1.000000
max 2018-10-29 12:27:35 1.000000
std NaN 0.492371
| Insight | Value |
|---|---|
| Total reviews | 99,224 |
| Median score | 5.0 — mean 4.09 (left-skewed, mostly positive) |
| Has comment | 41% of customers left a written comment |
| Date range | Oct 2016 — Aug 2018 |
Finding: Review scores are heavily skewed toward 5 stars — most customers are satisfied.
Negative reviews (1–2 stars) are the minority but likely contain the most valuable text comments for NLP analysis.
has_commentcorrelates with dissatisfaction — worth testing as ML feature.
7.8 orders — describe()¶
In [52]:
print(df_orders.describe() )
order_purchase_timestamp order_approved_at \
count 99441 99281
mean 2017-12-31 08:43:12.776581120 2017-12-31 18:35:24.098800128
min 2016-09-04 21:15:19 2016-09-15 12:16:38
25% 2017-09-12 14:46:19 2017-09-12 23:24:16
50% 2018-01-18 23:04:36 2018-01-19 11:36:13
75% 2018-05-04 15:42:16 2018-05-04 20:35:10
max 2018-10-17 17:30:18 2018-09-03 17:40:06
std NaN NaN
order_delivered_carrier_date order_delivered_customer_date \
count 97658 96476
mean 2018-01-04 21:49:48.138278656 2018-01-14 12:09:19.035542272
min 2016-10-08 10:34:01 2016-10-11 13:46:32
25% 2017-09-15 22:28:50.249999872 2017-09-25 22:07:22.249999872
50% 2018-01-24 16:10:58 2018-02-02 19:28:10.500000
75% 2018-05-08 13:37:45 2018-05-15 22:48:52.249999872
max 2018-09-11 19:48:28 2018-10-17 13:22:46
std NaN NaN
order_estimated_delivery_date is_delivered
count 99441 99441.000000
mean 2018-01-24 03:08:37.730111232 0.970183
min 2016-09-30 00:00:00 0.000000
25% 2017-10-03 00:00:00 1.000000
50% 2018-02-15 00:00:00 1.000000
75% 2018-05-25 00:00:00 1.000000
max 2018-11-12 00:00:00 1.000000
std NaN 0.170082
| Insight | Value |
|---|---|
| Total orders | 99,441 |
| Delivered orders | 96,476 (97%) |
| Never approved | 160 — cancelled before approval |
| Never reached carrier | 1,783 |
| Never reached customer | 2,965 |
| Date range | Sep 2016 — Oct 2018 |
Finding: 97% delivery rate is strong. The 3% undelivered orders are worth investigating —
late deliveries and cancellations are a key driver of negative reviews.
Delivery delay (estimated vs actual date) will be a strong ML feature for predicting review score.
7.9 product_category_name_translation — describe()¶
In [53]:
print(df_product_category_name_translation.describe())
product_category_name product_category_name_english count 71 71 unique 71 71 top beleza_saude health_beauty freq 1 1
| Insight | Value |
|---|---|
| Total categories | 71 — all unique, no duplicates |
| Top category | beleza_saude → health_beauty |
Note: Pure lookup table — used to translate Portuguese category names to English before analysis.
7.10 products — describe()¶
In [54]:
print(df_products.describe())
product_name_lenght product_description_lenght product_photos_qty \
count 32949.000000 32949.000000 32949.000000
mean 47.580594 757.232814 2.148502
std 12.069277 637.750493 1.745738
min 0.000000 0.000000 0.000000
25% 41.000000 326.000000 1.000000
50% 51.000000 584.000000 1.000000
75% 57.000000 961.000000 3.000000
max 76.000000 3992.000000 20.000000
product_weight_g product_length_cm product_height_cm \
count 32949.000000 32949.000000 32949.000000
mean 2276.472488 30.815078 16.937661
std 4282.038731 16.914458 13.637554
min 0.000000 7.000000 2.000000
25% 300.000000 18.000000 8.000000
50% 700.000000 25.000000 13.000000
75% 1900.000000 38.000000 21.000000
max 40425.000000 105.000000 105.000000
product_width_cm
count 32949.000000
mean 23.196728
std 12.079047
min 6.000000
25% 15.000000
50% 20.000000
75% 30.000000
max 118.000000
In [56]:
zero_weight = df_products[df_products['product_weight_g'] == 0]
print(f"Produkte mit 0g: {len(zero_weight)}")
print(zero_weight[['product_id', 'product_category_name', 'product_weight_g']])
Produkte mit 0g: 4
product_id product_category_name \
9769 81781c0fed9fe1ad6e8c81fca1e1cb08 cama_mesa_banho
13683 8038040ee2a71048d4bdbbdc985b69ab cama_mesa_banho
14997 36ba42dd187055e1fbe943b2d11430ca cama_mesa_banho
32079 e673e90efa65a5409ff4196c038bb5af cama_mesa_banho
product_weight_g
9769 0.0
13683 0.0
14997 0.0
32079 0.0
In [ ]:
#
df_products = df_products[df_products['product_weight_g'] > 0]
| Insight | Value |
|---|---|
| Total products | 32,949 |
| Median weight | 700g — mean 2,276g (skewed by heavy outliers) |
| Max weight | 40,425g (40kg) — extreme outlier |
| Min weight | 0g — data anomaly, needs filtering |
| Median photos | 1 — most products have only 1 photo |
| Max photos | 20 |
| Min name length | 0 — products with no name, data anomaly |
product_weight_g = 0: 4 products in categorycama_mesa_banho(bed & bath) —- physical products with missing weight data, not digital products. Rows dropped.
7.11 sellers — describe()¶
In [57]:
print(df_sellers.describe())
seller_id seller_zip_code_prefix seller_city \
count 3095 3095 3095
unique 3095 2246 611
top 1b938a7ec6ac5061a66a3766e0e75f90 14940 sao paulo
freq 1 49 694
seller_state
count 3095
unique 23
top SP
freq 1849
| Insight | Value |
|---|---|
| Total sellers | 3,095 — all unique |
| Top city | São Paulo — 694 sellers (22%) |
| Top state | SP — 1,849 sellers (60%) |
| Distinct cities | 611 |
| Distinct states | 23 |
Finding: Sellers are even more concentrated in São Paulo than customers (60% vs 42%).
This geographic overlap may explain shorter delivery times for SP customers.
| Tabelle | Beschreibung | Wichtige Spalten |
|---|---|---|
df_orders |
Zentrale Tabelle. Eine Zeile pro Bestellung — Status, Zeitstempel, Verbindung zum Kunden | order_id, customer_id, order_status, 5× Datumsspalten, is_delivered |
df_customers |
Eine Zeile pro Bestell-Kunde. customer_unique_id identifiziert die echte Person über mehrere Bestellungen |
customer_id, customer_unique_id, PLZ, Stadt, Bundesstaat |
df_order_items |
Eine Zeile pro Artikel in einer Bestellung. Eine Bestellung kann mehrere Artikel von verschiedenen Verkäufern enthalten | order_id, product_id, seller_id, price, freight_value |
df_order_payments |
Eine Zeile pro Zahlung. Eine Bestellung kann mit mehreren Methoden bezahlt werden (Karte + Gutschein) | order_id, payment_type, payment_installments, payment_value |
df_order_reviews |
Kundenbewertung pro Bestellung. Kommentarfelder optional — die meisten Kunden hinterließen nur eine Punktzahl | order_id, review_score, review_comment_message, has_comment |
df_products |
Eine Zeile pro Produkt. Kategorie und physische Abmessungen. 610 Zeilen ohne Kategorie | product_id, product_category_name, Gewicht, Abmessungen |
df_sellers |
Eine Zeile pro Verkäufer. Standortdaten — für Entfernungsberechnung zwischen Verkäufer und Kunde | seller_id, PLZ, Stadt, Bundesstaat |
df_geolocation |
GPS-Koordinaten pro PLZ. Nach Deduplizierung auf einen Mittelpunkt pro PLZ aggregiert | zip_code_prefix, lat, lng, Stadt, Bundesstaat |
df_product_category_name_translation |
Nachschlagetabelle — übersetzt portugiesische Kategorienamen ins Englische | product_category_name, product_category_name_english |
df_leads_qualified |
Marketing-Leads die Olist’s Landingpage besucht haben. Erster Schritt im Verkäufer-Funnel | mql_id, first_contact_date, landing_page_id, origin |
df_leads_closed |
Leads die zu Verkäufern konvertiert sind. Verbindung zu leads_qualified über mql_id | mql_id, seller_id, won_date, business_type, has_profile_data |
In [ ]:
df_customers
df_geolocation
df_leads_closed
df_leads_qualified
df_order_items
df_order_payments
df_order_reviews
df_orders
df_product_category_name_translation
df_products
df_sellers
↑ ContentsStep 8 — Analyze Categorical Columns¶
8.1 order_status — value_counts()¶
In [ ]:
print("=== order_status ===")
print(df_orders['order_status'].value_counts())
=== order_status === order_status delivered 96478 shipped 1107 canceled 625 unavailable 609 invoiced 314 processing 301 created 5 approved 2 Name: count, dtype: Int64
| Status | Count | % |
|---|---|---|
| delivered | 96,478 | 97.0% |
| shipped | 1,107 | 1.1% |
| canceled | 625 | 0.6% |
| unavailable | 609 | 0.6% |
| invoiced | 314 | 0.3% |
| processing | 301 | 0.3% |
| created | 5 | <0.1% |
| approved | 2 | <0.1% |
Finding: 97% of orders are delivered successfully.
canceledandunavailabletogether account for ~1.2% — worth analyzing if they correlate with negative reviews.
8.2 payment_type — value_counts()¶
In [59]:
print("\n=== payment_type ===")
print(df_order_payments['payment_type'].value_counts())
=== payment_type === payment_type credit_card 76795 boleto 19784 voucher 5775 debit_card 1529 not_defined 3 Name: count, dtype: Int64
| Payment Type | Count | % |
|---|---|---|
| credit_card | 76,795 | 74% |
| boleto | 19,784 | 19% |
| voucher | 5,775 | 6% |
| debit_card | 1,529 | 1% |
| not_defined | 3 | <0.1% |
Finding: Credit card dominates with 74%.
boleto(19%) is a Brazilian-specific payment method — a bank slip paid in cash or online.
not_defined(3 rows) is a data anomaly — negligible, can be ignored.
8.3 product_category — Top 10¶
In [60]:
print("\n=== product_category (Top 10) ===")
print(df_products['product_category_name'].value_counts().head(10))
=== product_category (Top 10) === product_category_name cama_mesa_banho 3029 esporte_lazer 2867 moveis_decoracao 2657 beleza_saude 2444 utilidades_domesticas 2335 automotivo 1900 informatica_acessorios 1639 brinquedos 1411 relogios_presentes 1329 telefonia 1134 Name: count, dtype: Int64
| Category (PT) | Category (EN) | Count |
|---|---|---|
| cama_mesa_banho | bed & bath | 3,029 |
| esporte_lazer | sports & leisure | 2,867 |
| moveis_decoracao | furniture & decor | 2,657 |
| beleza_saude | health & beauty | 2,444 |
| utilidades_domesticas | home appliances | 2,335 |
| automotivo | automotive | 1,900 |
| informatica_acessorios | computer accessories | 1,639 |
| brinquedos | toys | 1,411 |
| relogios_presentes | watches & gifts | 1,329 |
| telefonia | telephony | 1,134 |
Finding: Home & lifestyle categories dominate — bed/bath, furniture, and sports are the top 3.
Tech categories (computer accessories, telephony) are also well represented.
8.4 seller_state — value_counts()¶
In [ ]:
print("\n=== seller_state ===")
print(df_sellers['seller_state'].value_counts())
=== seller_state === seller_state SP 1849 PR 349 MG 244 SC 190 RJ 171 RS 129 GO 40 DF 30 ES 23 BA 19 CE 13 PE 9 PB 6 MS 5 RN 5 MT 4 RO 2 SE 2 AC 1 PI 1 MA 1 AM 1 PA 1 Name: count, dtype: Int64
| State | Count | % |
|---|---|---|
| SP (São Paulo) | 1,849 | 60% |
| PR (Paraná) | 349 | 11% |
| MG (Minas Gerais) | 244 | 8% |
| SC (Santa Catarina) | 190 | 6% |
| RJ (Rio de Janeiro) | 171 | 6% |
| Others (18 states) | 292 | 9% |
Finding: 60% of all sellers are based in São Paulo — Brazil’s economic hub.
The top 5 states account for 91% of all sellers, while 18 states share only 9%.
This concentration likely impacts delivery times for customers in remote states.
8.5 customer_state — Top 10¶
In [62]:
print("\n=== customer_state ===")
print(df_customers['customer_state'].value_counts().head(10))
=== customer_state === customer_state SP 41746 RJ 12852 MG 11635 RS 5466 PR 5045 SC 3637 BA 3380 DF 2140 ES 2033 GO 2020 Name: count, dtype: Int64
| State | Count | % |
|---|---|---|
| SP (São Paulo) | 41,746 | 42% |
| RJ (Rio de Janeiro) | 12,852 | 13% |
| MG (Minas Gerais) | 11,635 | 12% |
| RS (Rio Grande do Sul) | 5,466 | 5% |
| PR (Paraná) | 5,045 | 5% |
| SC (Santa Catarina) | 3,637 | 4% |
| BA (Bahia) | 3,380 | 3% |
| DF (Brasília) | 2,140 | 2% |
| ES (Espírito Santo) | 2,033 | 2% |
| GO (Goiás) | 2,020 | 2% |
Finding: SP, RJ and MG together account for 67% of all customers.
Customers are more spread across Brazil than sellers — interesting for delivery time analysis.
States like BA, DF, GO have many customers but very few sellers → longer delivery times expected.
8.6 lead origin — value_counts()¶
In [ ]:
print("\n=== lead origin ===")
print(df_leads_qualified['origin'].value_counts())
=== lead origin === origin organic_search 2296 paid_search 1586 social 1350 unknown 1099 direct_traffic 499 email 493 referral 284 other 150 display 118 other_publicities 65 Name: count, dtype: Int64
| Origin | Count | % |
|---|---|---|
| organic_search | 2,296 | 29% |
| paid_search | 1,586 | 20% |
| social | 1,350 | 17% |
| unknown | 1,099 | 14% |
| direct_traffic | 499 | 6% |
| 493 | 6% | |
| referral | 284 | 4% |
| other / display / publicities | 333 | 4% |
Finding: Organic search is the top lead source (29%) — Olist’s SEO is effective.
Paid search + social together account for 37% — significant marketing spend.
14% unknown origin is a tracking gap worth noting.
8.7 business_type — value_counts()¶
In [64]:
print("\n=== business_type ===")
print(df_leads_closed['business_type'].value_counts())
=== business_type === business_type reseller 587 manufacturer 242 Unknown 10 other 3 Name: count, dtype: Int64
| Business Type | Count | % |
|---|---|---|
| reseller | 587 | 69% |
| manufacturer | 242 | 29% |
| Unknown | 10 | 1% |
| other | 3 | <1% |
Finding: 69% of closed leads are resellers — they buy products and resell on Olist.
Manufacturers (29%) sell their own products directly.
Unknownandotherare negligible — previously filled from missing values.
8.8 review_score — value_counts()¶
In [65]:
print("\n=== review_score ===")
print(df_order_reviews['review_score'].value_counts().sort_index())
=== review_score === review_score 1 11424 2 3151 3 8179 4 19142 5 57328 Name: count, dtype: int64
| Score | Count | % |
|---|---|---|
| 5 | 57,328 | 58% |
| 4 | 19,142 | 19% |
| 1 | 11,424 | 12% |
| 3 | 8,179 | 8% |
| 2 | 3,151 | 3% |
Finding: Distribution is strongly bimodal — customers either love (5★ 58%) or hate (1★ 12%) the experience.
Score 2 and 3 are the least common — customers rarely feel “neutral”.
1★ reviews are the second most common score — key target for ML prediction.
↑ ContentsStep 9 — Visualizations (Matplotlib / Seaborn)¶
9.1 Orders per Month¶
In [103]:
# Orders per month (order_purchase_timestamp already converted to datetime in Step 6.8)
orders_per_month = df_orders.groupby(df_orders['order_purchase_timestamp'].dt.to_period('M')).size()
plt.figure(figsize=(14, 5), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')
plt.plot(orders_per_month.index.astype(str), orders_per_month.values,
marker='o', linewidth=2.5, color='#2196F3', markerfacecolor='white',
markeredgecolor='#2196F3', markeredgewidth=2, markersize=7)
# Fill area under the line
plt.fill_between(orders_per_month.index.astype(str), orders_per_month.values,
alpha=0.1, color='#2196F3')
# Annotate Black Friday
plt.annotate(f'Black Friday\n{orders_per_month["2017-11"]:,} orders',
xy=('2017-11', orders_per_month['2017-11'] ),
xytext=('2017-08', orders_per_month['2017-11'] - 300),
arrowprops=dict(arrowstyle='->', color='red'),
fontsize=10, color='red', alpha=0.7)
ax.yaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.xticks(rotation=45, fontsize=10)
plt.title('Orders per Month', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Month', fontsize=12)
plt.ylabel('Number of Orders', fontsize=12)
sns.despine()
plt.tight_layout()
plt.show()
We group all orders by purchase month and count them.
This reveals seasonality, growth trends, and anomalies in order volume over time.
Expected findings:
- Overall growth trend from 2016 to 2018
- Seasonal peaks (e.g. Black Friday, Christmas)
- Possible drop at the end — dataset may be incomplete for the last month
Finding: Strong growth from late 2017 through mid-2018.
November 2017 spike likely driven by Black Friday — a major shopping event in Brazil.
Sharp drop at the end of the dataset indicates incomplete data for the final months, not a real decline.
9.2 Review Score Distribution¶
In [104]:
palette=['#d73027', '#f46d43', '#fdae61', '#66bd63', '#1a9850']
plt.figure(figsize=(9, 5), facecolor='#f8f8f8')
ax = sns.countplot(data=df_order_reviews, x='review_score',
order=[1, 2, 3, 4, 5], palette=palette,
hue='review_score', legend=False, alpha=0.5)
ax.set_facecolor('#f8f8f8')
ax.yaxis.grid(True, linestyle='--', alpha=0.6)
ax.set_axisbelow(True)
for p in ax.patches:
pct = p.get_height() / len(df_order_reviews) * 100
ax.annotate(f'{int(p.get_height()):,}\n({pct:.1f}%)',
(p.get_x() + p.get_width() / 2., p.get_height()),
ha='center', va='bottom', fontsize=10)
plt.title('Review Score Distribution', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Review Score', fontsize=12)
plt.ylabel('Number of Reviews', fontsize=12)
plt.xticks([0, 1, 2, 3, 4], ['★', '★★', '★★★', '★★★★', '★★★★★'], fontsize=14)
sns.despine()
plt.tight_layout()
plt.show()
Bar chart showing how customers rated their orders (1–5 stars).
Reveals whether the platform has mostly satisfied or dissatisfied customers.
Finding: Strongly bimodal distribution — 58% gave 5 stars, 12% gave 1 star.
Customers rarely feel neutral (scores 2 and 3 are the least common).
1★ reviews are a key target for ML — predicting dissatisfaction before it happens.
9.3 Top 10 Product Categories¶
In [121]:
# Data preparation
top10_count = df_products['product_category_name'].value_counts().head(10).reset_index()
top10_count.columns = ['product_category_name', 'count']
top10_count = top10_count.merge(df_product_category_name_translation, on='product_category_name')
revenue_by_category = df_order_items.merge(df_products[['product_id', 'product_category_name']], on='product_id') \
.merge(df_product_category_name_translation, on='product_category_name') \
.groupby('product_category_name_english')['price'].sum() \
.sort_values(ascending=False).head(10).reset_index()
revenue_by_category.columns = ['product_category_name_english', 'revenue']
fig, axes = plt.subplots(1, 2, figsize=(20, 7), facecolor='#f8f8f8')
# Left — Top 10 by product count
axes[0].set_facecolor('#f8f8f8')
sns.barplot(data=top10_count, y='product_category_name_english', x='count',
palette='Blues_r', hue='product_category_name_english', legend=False, ax=axes[0])
for p in axes[0].patches:
axes[0].annotate(f'{int(p.get_width()):,}',
(p.get_width() + 20, p.get_y() + p.get_height() / 2.),
ha='left', va='center', fontsize=10)
axes[0].xaxis.grid(True, linestyle='--', alpha=0.5)
axes[0].set_axisbelow(True)
axes[0].set_title('Top 10 by Number of Products', fontsize=13, fontweight='bold', pad=15)
axes[0].set_xlabel('Number of Products', fontsize=11)
axes[0].set_ylabel('')
sns.despine(ax=axes[0])
# Right — Top 10 by revenue
axes[1].set_facecolor('#f8f8f8')
sns.barplot(data=revenue_by_category, y='product_category_name_english', x='revenue',
palette='Greens_r', hue='product_category_name_english', legend=False, ax=axes[1])
for p in axes[1].patches:
axes[1].annotate(f' R$ {int(p.get_width()):,}',
(p.get_width() + 100, p.get_y() + p.get_height() / 2.),
ha='left', va='center', fontsize=10)
axes[1].xaxis.grid(True, linestyle='--', alpha=0.5)
axes[1].set_axisbelow(True)
axes[1].set_title('Top 10 by Revenue', fontsize=13, fontweight='bold', pad=15)
axes[1].set_xlabel('Total Revenue (R$)', fontsize=11)
axes[1].set_ylabel('')
sns.despine(ax=axes[1])
plt.suptitle('Top 10 Product Categories', fontsize=15, fontweight='bold', y=1.02)
plt.tight_layout()
plt.show()
Two charts side by side — left shows the most listed categories, right shows the highest revenue generators.
This reveals whether the most common products are also the most profitable.
Finding: Health & beauty and watches & gifts generate high revenue despite fewer products listed.
Bed & bath has the most products but does not lead in revenue — high volume, lower price point.
9.4 Payment Methods¶
In [162]:
payment_counts = df_order_payments['payment_type'].value_counts()
payment_counts = payment_counts[payment_counts.index != 'not_defined']
colors = ['#2196F3', '#4CAF50', '#FF9800', '#9C27B0']
# Hide percentage for small segments
def autopct_format(pct):
return f'{pct:.1f}%' if pct > 3 else ''
fig, ax = plt.subplots(figsize=(8, 8), facecolor='#f8f8f8')
ax.set_facecolor('#f8f8f8')
wedges, texts, autotexts = ax.pie(
payment_counts.values,
labels=None,
autopct=autopct_format,
colors=colors,
startangle=90,
wedgeprops=dict(width=0.5),
pctdistance=0.65
)
for autotext in autotexts:
autotext.set_fontsize(11)
autotext.set_fontweight('bold')
# Manual annotation for debit_card
ax.annotate('Debit Card\n1.5%',
xy=(0.05, 1),
xytext=(-0.11, 1.1),
arrowprops=dict(arrowstyle='->', color='#9C27B0', lw=1.5),
fontsize=11, color='#9C27B0', fontweight='bold')
# Legend with clean labels
ax.legend(wedges, ['Credit Card', 'Boleto', 'Voucher', 'Debit Card'],
loc='lower center', bbox_to_anchor=(0.5, 0),
ncol=4, fontsize=11)
ax.set_title('Payment Methods', fontsize=14, fontweight='bold', pad=15)
plt.tight_layout()
plt.show()
Donut chart showing the distribution of payment methods across all orders.
debit_card is annotated separately due to its small share.
| Payment Method | Share |
|---|---|
| Credit Card | 73.9% |
| Boleto | 19.0% |
| Voucher | 5.6% |
| Debit Card | 1.5% |
Finding: Credit card dominates with 74%.
boleto(19%) is a Brazilian-specific payment method — a bank slip paid in cash or online.
The high boleto share reflects Brazil’s large unbanked population who cannot use credit cards.
9.5 Delivery Time vs. Review Score¶
In [173]:
# Calculate delivery time in days
df_orders['delivery_time'] = (df_orders['order_delivered_customer_date'] -
df_orders['order_purchase_timestamp']).dt.days
# Merge with reviews
df_delivery_review = df_orders[['order_id', 'delivery_time', 'is_delivered']].merge(
df_order_reviews[['order_id', 'review_score']], on='order_id')
# Keep only delivered orders
df_delivery_review = df_delivery_review[df_delivery_review['is_delivered'] == 1]
plt.figure(figsize=(10, 6), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')
sns.boxplot(data=df_delivery_review, x='review_score', y='delivery_time',
palette=['#d73027', '#f46d43', '#fdae61', '#66bd63', '#1a9850'],
hue='review_score', legend=False)
# Mean values per score
means = df_delivery_review.groupby('review_score')['delivery_time'].mean()
for i, mean in enumerate(means):
ax.annotate(f'avg: {mean:.1f}d', xy=(i, mean - 3),
ha='center', va='bottom', fontsize=10,
fontweight='bold', color='white',
xytext=(0, 10), textcoords='offset points')
plt.ylim(0, 80)
ax.yaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.title('Delivery Time vs. Review Score', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Review Score', fontsize=12)
plt.ylabel('Delivery Time (days)', fontsize=12)
plt.xticks([0, 1, 2, 3, 4], ['★', '★★', '★★★', '★★★★', '★★★★★'], fontsize=12)
sns.despine()
plt.tight_layout()
plt.show()
Boxplot showing the relationship between delivery time and customer satisfaction.
Average delivery time per score is annotated directly on each box.
| Score | Avg. Delivery Time |
|---|---|
| ★ | 20.8 days |
| ★★ | 16.2 days |
| ★★★ | 13.8 days |
| ★★★★ | 11.8 days |
| ★★★★★ | 10.2 days |
Finding: Customers who gave 1★ waited on average twice as long as 5★ customers (20.8 vs 10.2 days).
Delivery time is a strong predictor of customer satisfaction — a key feature for the ML model.
9.6 Customers by State¶
In [174]:
# Top 10 customers by state
top10_states = df_customers['customer_state'].value_counts().head(10).reset_index()
top10_states.columns = ['state', 'count']
plt.figure(figsize=(10, 6), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')
sns.barplot(data=top10_states, y='state', x='count',
palette='Blues_r', hue='state', legend=False)
# Values at end of bars
for p in ax.patches:
ax.annotate(f'{int(p.get_width()):,}',
(p.get_width() + 100, p.get_y() + p.get_height() / 2.),
ha='left', va='center', fontsize=10)
ax.xaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.title('Top 10 States by Number of Customers', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Number of Customers', fontsize=12)
plt.ylabel('')
sns.despine()
plt.tight_layout()
plt.show()
Top 10 Brazilian states ranked by number of customers.
Finding: São Paulo (SP) alone accounts for 42% of all customers.
SP, RJ and MG together represent 67% — the southeast region dominates Olist’s customer base.
In [226]:
import plotly.express as px
# Customers per state
customers_per_state = df_customers['customer_state'].value_counts().reset_index()
customers_per_state.columns = ['state', 'count']
fig = px.choropleth(
customers_per_state,
geojson='https://raw.githubusercontent.com/codeforamerica/click_that_hood/master/public/data/brazil-states.geojson',
locations='state',
featureidkey='properties.sigla',
color='count',
color_continuous_scale='Blues',
title='Customers by State — Brazil',
labels={'count': 'Number of Customers'},
hover_data={'state': True, 'count': True}
)
fig.update_geos(
visible=False,
lataxis_range=[-43, 15],
lonaxis_range=[-85, -25],
projection_scale=1.5
)
fig.update_layout(
title_font_size=16,
title_x=0.5,
geo=dict(bgcolor='rgba(0,0,0,0)'),
paper_bgcolor='#f8f8f8',
width=800,
height=800
)
fig.show()
Interactive choropleth map showing customer distribution across Brazilian states.
Finding: São Paulo (SP) dominates with 42% of all customers — visible as the darkest region.
The southeast region (SP, RJ, MG) concentrates 67% of the customer base.
Northern and central states have very few customers despite their large geographic size.
9.7 Average Delivery Time by State¶
In [246]:
# Average delivery time per state
delivery_per_state = df_orders[['order_id', 'customer_id', 'delivery_time']].merge(
df_customers[['customer_id', 'customer_state']], on='customer_id'
)
avg_delivery = delivery_per_state.groupby('customer_state')['delivery_time'] \
.mean().round(1).reset_index()
avg_delivery.columns = ['state', 'avg_days']
avg_delivery = avg_delivery.sort_values('avg_days', ascending=False)
national_avg = avg_delivery['avg_days'].mean()
plt.figure(figsize=(10, 8), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')
sns.barplot(data=avg_delivery, y='state', x='avg_days',
palette='RdYlGn_r', hue='state', legend=False)
# Values at end of bars
for p in ax.patches:
ax.annotate(f'{p.get_width():.1f}d',
(p.get_width() + 0.2, p.get_y() + p.get_height() / 2.),
ha='left', va='center', fontsize=9)
# National average line
plt.axvline(x=national_avg, color='blue', linestyle='-', alpha=0.4)
plt.text(national_avg + 0.2, len(avg_delivery) - 1,
f'National avg: {national_avg:.1f}d', fontsize=10, color='black')
ax.xaxis.grid(True, linestyle='--', alpha=0.5)
ax.set_axisbelow(True)
plt.title('Average Delivery Time by State', fontsize=14, fontweight='bold', pad=15)
plt.xlabel('Average Delivery Time (days)', fontsize=12)
plt.ylabel('')
sns.despine()
plt.tight_layout()
plt.show()
Horizontal bar chart showing average delivery time per customer state, sorted from longest to shortest.
Dashed line shows the national average of 18.3 days.
| Fastest | Days | Slowest | Days |
|---|---|---|---|
| SP | 8.3d | RR | 29.0d |
| PR | 11.5d | AP | 26.7d |
| MG | 11.5d | AM | 26.0d |
Finding: SP customers receive orders in 8.3 days — 3.5x faster than RR (29.0 days).
Remote northern states (RR, AP, AM) wait the longest — few sellers ship to these regions.
15 out of 27 states are above the national average of 18.3 days.
Most sellers are based in SP — distance is the primary driver of delivery time inequality.
9.8 Correlation Heatmap¶
In [ ]:
import numpy as np
# Select relevant numeric columns
df_corr = df_order_items[['order_id', 'price', 'freight_value']].merge(
df_order_payments[['order_id', 'payment_value', 'payment_installments']], on='order_id'
).merge(
df_order_reviews[['order_id', 'review_score', 'has_comment']], on='order_id'
).merge(
df_orders[['order_id', 'delivery_time']], on='order_id'
).drop(columns='order_id').dropna()
corr = df_corr.corr()
# Mask upper triangle
mask = np.triu(np.ones_like(corr, dtype=bool), k=1)
plt.figure(figsize=(10, 8), facecolor='#f8f8f8')
ax = plt.gca()
ax.set_facecolor('#f8f8f8')
sns.heatmap(corr,
mask=mask,
annot=True,
fmt='.2f',
cmap='RdYlGn',
linewidths=1,
linecolor='white',
square=True,
annot_kws={'size': 11, 'weight': 'bold'},
vmin=-1, vmax=1,
cbar_kws={'shrink': 0.8})
plt.title('Correlation Heatmap', fontsize=14, fontweight='bold', pad=20)
plt.xticks(rotation=45, ha='right', fontsize=11)
plt.yticks(rotation=0, fontsize=11)
plt.tight_layout()
plt.show()
Correlation matrix of key numeric variables. Values range from -1 (strong negative) to +1 (strong positive).
Only the lower triangle is shown — the matrix is symmetric.
| Correlation | Value | Interpretation |
|---|---|---|
price ↔ payment_value |
0.74 | Higher priced items lead to higher payment values |
delivery_time ↔ review_score |
-0.30 | Longer delivery = lower review score |
review_score ↔ has_comment |
-0.28 | Dissatisfied customers write more comments |
price ↔ freight_value |
0.41 | Heavier/larger items cost more to ship |
Finding:
delivery_timeis the strongest negative predictor ofreview_score— confirms our earlier analysis.
priceandpayment_valueare strongly correlated (0.74) — expected, as payment covers the item price.
Most variables show weak correlations — suggesting non-linear relationships worth exploring with ML.
↑ ContentsStep 10 — Summary & Key Findings¶
Dataset Overview¶
- 9 tables, 99,441 orders, 96,096 unique customers, 3,095 sellers
- Time period: September 2016 — October 2018 (~2 years)
- Geography: 27 Brazilian states, heavily concentrated in São Paulo
Data Quality¶
| Issue | Table | Action |
|---|---|---|
| >90% missing values | leads_closed |
Binary column has_profile_data created |
| Optional comment fields | order_reviews |
Binary column has_comment created |
| Date columns as NaT | orders |
Kept as NaT — not delivered orders have no date |
| Invalid coordinates | geolocation |
Filtered to Brazil bounds |
| 261,831 duplicates | geolocation |
Aggregated to centroid per ZIP |
| 4 products with 0g weight | products |
Rows dropped |
Key Business Findings¶
Delivery
- 97% of orders are successfully delivered
- Average delivery time: 18.3 days nationally
- SP customers receive orders in 8.3 days — RR customers wait 29.0 days (3.5x longer)
Customer Satisfaction
- 58% of customers give 5★ — platform is generally well received
- 1★ reviews correlate with longer delivery times (avg 20.8 days vs 10.2 days for 5★)
- Dissatisfied customers are more likely to leave a written comment
Revenue & Payments
- Credit card dominates (74%), boleto second (19%) — reflects Brazilian payment culture
- Top revenue categories: health & beauty, watches & gifts, computers
priceandpayment_valuestrongly correlated (0.74)
Geography
- 60% of sellers are in São Paulo — explains delivery time inequality
- 42% of customers are in São Paulo, 67% in SP+RJ+MG combined
Marketing Funnel
- 7,940 qualified leads → 842 closed deals (~10.6% conversion rate)
- Organic search is the top lead source (29%)
- Only 10% of closed leads provided full profile data
Features for ML Model¶
| Feature | Target | Reason |
|---|---|---|
delivery_time |
review_score |
Strongest predictor (-0.30) |
has_comment |
review_score |
Negative reviews correlate with comments |
price |
review_score |
Higher value orders have higher expectations |
payment_installments |
review_score |
Reflects financial stress of customer |
freight_value |
review_score |
High shipping cost may cause dissatisfaction |
Next:
02_SQL_Analysis.ipynb— deeper analysis using SQL queries on the SQLite database.
↑ ContentsPart 02 — Power BI Dashboard
Die EDA aus Teil 1 hat ein paar Muster sichtbar gemacht, die sich besser laufend beobachten als jedes Mal neu aus einem Notebook heraus prüfen lassen: Lieferzeit, die Review-Scores drückt, São Paulo als Zentrum von Verkäufern und Kunden, und Kreditkarte als dominierende Zahlungsmethode. Um diese Erkenntnisse alltagstauglich zu machen, habe ich sie als interaktives Power-BI-Dashboard mit vier Seiten – Overview, Sales, Customer und Logistics – neu aufgebaut, jede davon nach Jahr filterbar.
Seite 1 – Overview
Die Startseite fasst die wichtigsten KPIs zusammen: R$ 13,59 Mio. Gesamtumsatz, 99 Tsd. Bestellungen, ein durchschnittlicher Review-Score von 4,09 und 13,20 Tage durchschnittliche Lieferzeit. Der Revenue-Trend zeigt das Wachstum der Plattform von Ende 2016 bis 2018, und die Delivery-Time-vs-Review-Score-Balken übertragen den EDA-Befund direkt ins Dashboard – 1★-Bestellungen brauchen im Schnitt 22 Tage, 5★-Bestellungen nur 11.
Finding: Die Overview-Seite bestätigt den roten Faden aus
der EDA – Lieferzeit ist die entscheidende Stellschraube – und
ergänzt ihn um die regionale Bestellkarte, auf der die Nachfrage sichtbar
abnimmt, je weiter ein Bundesstaat von São Paulo entfernt liegt.
Seite 2 – Sales
Die Sales-Seite schlüsselt die R$ 13,59 Mio. Gesamtumsatz nach Monat und Kategorie auf. Das saisonale Muster zeigt einen stetigen Anstieg ab Anfang 2017 mit einem Höhepunkt im Mai 2018, und health_beauty, watches_gifts sowie bed_bath_table führen das Kategorie-Ranking an – das deckt sich mit den Umsatz-nach-Kategorie-Ergebnissen aus der EDA. Das Zahlungsarten-Donut bestätigt Kreditkarte mit rund 78 % des Bestellwerts, boleto folgt mit deutlichem Abstand bei 17 %.
Finding: Kategorie- und Zahlungsmix stimmen fast exakt
mit den Notebook-Ergebnissen überein – ein gutes Zeichen, dass die
Dashboard-Aggregationen konsistent mit der EDA sind statt davon
abzuweichen.
Seite 3 – Customer
76,55 % der Reviews sind positiv, und die Score-Verteilung ist stark in Richtung 5★ verschoben (57 Tsd. von rund 99 Tsd. Reviews). Die Review-Score-vs-Delivery-Time-Balken wiederholen das Muster von der Overview-Seite, und ein neues Diagramm – Review Score vs. Freight Cost – zeigt einen kleineren, aber sichtbaren Effekt: 1★-Bestellungen kosteten im Schnitt rund R$ 21,23 Fracht gegenüber R$ 19,58 bei 5★-Bestellungen.
Finding: Frachtkosten sind ein sekundärer Treiber für
Unzufriedenheit hinter der Lieferzeit – das spiegelt die Korrelation
aus der EDA (price ↔ freight_value: 0,41), ist hier aber als konkrete
Kundenerfahrungs-Kennzahl sichtbar statt nur als Korrelationskoeffizient.
Seite 4 – Logistics
92 % aller Bestellungen kommen insgesamt pünktlich an, aber das Diagramm zur durchschnittlichen Lieferzeit pro Bundesstaat zeigt das Ende dieser Verteilung: RR, AP und AM liegen mit 27–30 Tagen weit über dem nationalen Schnitt von 13,20 Tagen – eine direkte Folge davon, dass die meisten Verkäufer in São Paulo sitzen. Die Frachtkosten nach Kategorie bestätigen computers, home_appliances_2 und furniture_mattress_and_upholst… als die teuersten Versandkategorien – passend zu ihrem Gewicht und ihrer Größe.
Finding: Die Logistik-Performance ist ungleich
verteilt – es handelt sich um eine São-Paulo-zentrierte Plattform,
die einen geografisch weit verstreuten Kundenstamm bedient. Die
nördlichen und nordöstlichen Bundesstaaten tragen fast die gesamte
Lieferzeit-Varianz.
Next:
02_SQL_Analysis.ipynb – tiefere Analyse
mit SQL-Queries auf der SQLite-Datenbank.