01 -- Ingesta de Datos y Limpieza¶
Pregunta de negocio: Que aspecto tienen nuestros datos transaccionales, y como construimos una base analitica limpia para analisis de cohortes, retencion y LTV?
Dataset: Olist Brazilian E-Commerce (~99K pedidos, Sep 2016 - Oct 2018)
Este notebook carga los 9 CSVs del dataset de Olist, los une en una tabla analitica maestra (orders_enriched), genera columnas derivadas para analisis de cohortes y activacion, y exporta archivos parquet listos para los notebooks siguientes.
In [1]:
import pandas as pd
import numpy as np
from pathlib import Path
RAW = Path("../data/raw")
OUT = Path("../data/processed")
OUT.mkdir(parents=True, exist_ok=True)
# Load all CSVs
orders = pd.read_csv(RAW / "olist_orders_dataset.csv")
customers = pd.read_csv(RAW / "olist_customers_dataset.csv")
order_items = pd.read_csv(RAW / "olist_order_items_dataset.csv")
products = pd.read_csv(RAW / "olist_products_dataset.csv")
payments = pd.read_csv(RAW / "olist_order_payments_dataset.csv")
reviews = pd.read_csv(RAW / "olist_order_reviews_dataset.csv")
sellers = pd.read_csv(RAW / "olist_sellers_dataset.csv")
geolocation = pd.read_csv(RAW / "olist_geolocation_dataset.csv")
translations = pd.read_csv(RAW / "product_category_name_translation.csv")
datasets = {
"orders": orders, "customers": customers, "order_items": order_items,
"products": products, "payments": payments, "reviews": reviews,
"sellers": sellers, "geolocation": geolocation, "translations": translations,
}
for name, df in datasets.items():
print(f"{name:>15}: {df.shape[0]:>8,} rows x {df.shape[1]:>2} cols | nulls: {df.isnull().sum().sum():,}")
orders: 99,441 rows x 8 cols | nulls: 4,908
customers: 99,441 rows x 5 cols | nulls: 0
order_items: 112,650 rows x 7 cols | nulls: 0
products: 32,951 rows x 9 cols | nulls: 2,448
payments: 103,886 rows x 5 cols | nulls: 0
reviews: 99,224 rows x 7 cols | nulls: 145,903
sellers: 3,095 rows x 4 cols | nulls: 0
geolocation: 1,000,163 rows x 5 cols | nulls: 0
translations: 71 rows x 2 cols | nulls: 0
Perfil rapido de cada tabla¶
Revisamos tipos de datos, rangos de fechas y valores nulos criticos antes de construir la tabla maestra.
In [2]:
# Date columns profiling
date_cols = [
"order_purchase_timestamp", "order_approved_at",
"order_delivered_carrier_date", "order_delivered_customer_date",
"order_estimated_delivery_date",
]
for col in date_cols:
orders[col] = pd.to_datetime(orders[col], errors="coerce")
print(f"Rango de pedidos: {orders['order_purchase_timestamp'].min()} a {orders['order_purchase_timestamp'].max()}")
print(f"\nEstatus de pedidos:")
print(orders["order_status"].value_counts())
print(f"\nNulls en fechas de entrega: {orders['order_delivered_customer_date'].isnull().sum():,} ({orders['order_delivered_customer_date'].isnull().mean():.1%})")
Rango de pedidos: 2016-09-04 21:15:19 a 2018-10-17 17:30:18 Estatus de pedidos: order_status delivered 96478 shipped 1107 canceled 625 unavailable 609 invoiced 314 processing 301 created 5 approved 2 Name: count, dtype: int64 Nulls en fechas de entrega: 2,965 (3.0%)
Construccion de la tabla analitica maestra¶
Filtramos a pedidos entregados (delivered) y enriquecemos con datos de clientes, pagos, reviews, productos y columnas derivadas para cohortes y activacion.
In [3]:
# --- Filter to delivered orders only ---
df = orders[orders["order_status"] == "delivered"].copy()
print(f"Pedidos entregados: {len(df):,} de {len(orders):,} ({len(df)/len(orders):.1%})")
# --- Join customers (customer_unique_id, state) ---
df = df.merge(
customers[["customer_id", "customer_unique_id", "customer_state"]],
on="customer_id", how="left"
)
# --- Aggregate payments per order ---
pay_total = payments.groupby("order_id").agg(
total_payment=("payment_value", "sum"),
payment_installments=("payment_installments", "max"),
).reset_index()
pay_type = (
payments.groupby("order_id")["payment_type"]
.agg(lambda x: x.mode().iloc[0] if len(x.mode()) > 0 else x.iloc[0])
.reset_index()
.rename(columns={"payment_type": "payment_type_mode"})
)
df = df.merge(pay_total, on="order_id", how="left")
df = df.merge(pay_type, on="order_id", how="left")
# --- First review per order ---
first_review = (
reviews.drop_duplicates(subset=["order_id"], keep="first")[["order_id", "review_score"]]
)
df = df.merge(first_review, on="order_id", how="left")
# --- Order items: aggregate per order + product category ---
# Translate categories
products_tr = products.merge(translations, on="product_category_name", how="left")
products_tr["category"] = products_tr["product_category_name_english"].fillna(
products_tr["product_category_name"]
)
items_enriched = order_items.merge(
products_tr[["product_id", "category"]], on="product_id", how="left"
)
items_agg = items_enriched.groupby("order_id").agg(
items_per_order=("order_item_id", "count"),
total_price=("price", "sum"),
total_freight=("freight_value", "sum"),
main_category=("category", lambda x: x.mode().iloc[0] if len(x.mode()) > 0 else x.iloc[0]),
).reset_index()
df = df.merge(items_agg, on="order_id", how="left")
print(f"Columnas despues de joins: {df.shape[1]}")
print(f"Filas: {len(df):,}")
Pedidos entregados: 96,478 de 99,441 (97.0%)
Columnas despues de joins: 18 Filas: 96,478
Columnas derivadas¶
Generamos las variables clave para analisis de cohortes, retencion y activacion.
In [ ]:
# --- Derived columns ---
# order_month
df["order_month"] = df["order_purchase_timestamp"].dt.to_period("M").astype(str)
# total_order_value
df["total_order_value"] = df["total_price"].fillna(0) + df["total_freight"].fillna(0)
# delivery_days
df["delivery_days"] = (
df["order_delivered_customer_date"] - df["order_purchase_timestamp"]
).dt.days
# late_delivery
df["late_delivery"] = (
df["order_delivered_customer_date"] > df["order_estimated_delivery_date"]
)
# cohort_month: first purchase month per customer
cohort = df.groupby("customer_unique_id")["order_month"].min().reset_index()
cohort.columns = ["customer_unique_id", "cohort_month"]
df = df.merge(cohort, on="customer_unique_id", how="left")
# months_since_cohort
order_period = pd.PeriodIndex(df["order_month"], freq="M")
cohort_period = pd.PeriodIndex(df["cohort_month"], freq="M")
df["months_since_cohort"] = (order_period - cohort_period).map(
lambda x: x.n if hasattr(x, "n") else 0
)
# -- Validation: months_since_cohort --
# Orders where months_since_cohort == 0 should be same-month purchases (order_month == cohort_month).
# The lambda above silently maps invalid period diffs to 0, so we verify no corruption occurred.
zero_mask = df["months_since_cohort"] == 0
same_month_mask = df["order_month"] == df["cohort_month"]
n_zeros = zero_mask.sum()
n_same_month = same_month_mask.sum()
pct_zeros = n_zeros / len(df)
# Every zero should correspond to a same-month purchase and vice versa
mismatch = (zero_mask != same_month_mask).sum()
print(f"Validacion months_since_cohort:")
print(f" months_since_cohort=0: {n_zeros:,} pedidos ({pct_zeros:.1%}) -- compras en el mismo mes de cohorte (esperado)")
print(f" order_month == cohort_month: {n_same_month:,} pedidos")
print(f" Discrepancias (ceros no explicados por mismo mes): {mismatch:,}")
if mismatch > 0:
import warnings
warnings.warn(
f"months_since_cohort tiene {mismatch:,} valores en 0 que NO coinciden con "
f"order_month == cohort_month. Revisar la logica del calculo."
)
if pct_zeros > 0.50:
import warnings
warnings.warn(
f"months_since_cohort=0 representa {pct_zeros:.1%} de los pedidos (>50%). "
f"Proporcion sospechosamente alta -- posible corrupcion en el calculo."
)
# order_number: rank per customer (1st, 2nd, 3rd purchase)
df = df.sort_values(["customer_unique_id", "order_purchase_timestamp"])
df["order_number"] = df.groupby("customer_unique_id").cumcount() + 1
# is_repeat_customer
customer_order_counts = df.groupby("customer_unique_id")["order_id"].nunique()
repeat_customers = set(customer_order_counts[customer_order_counts > 1].index)
df["is_repeat_customer"] = df["customer_unique_id"].isin(repeat_customers)
# purchase_dayofweek and purchase_hour
df["purchase_dayofweek"] = df["order_purchase_timestamp"].dt.dayofweek
df["purchase_hour"] = df["order_purchase_timestamp"].dt.hour
df["is_weekend"] = df["purchase_dayofweek"].isin([5, 6])
n_repeat_orders = df["is_repeat_customer"].sum()
n_repeat_customers = df.loc[df["is_repeat_customer"], "customer_unique_id"].nunique()
pct_repeat_customers = n_repeat_customers / df["customer_unique_id"].nunique()
print(f"\nColumnas finales: {df.shape[1]}")
print(f"\nResumen de columnas derivadas:")
print(f" Clientes unicos: {df['customer_unique_id'].nunique():,}")
print(f" Clientes con recompra: {n_repeat_orders:,} pedidos pertenecen a clientes con recompra ({n_repeat_customers:,} clientes unicos, {pct_repeat_customers:.1%})")
print(f" Rango de cohortes: {df['cohort_month'].min()} a {df['cohort_month'].max()}")
print(f" Delivery days (mediana): {df['delivery_days'].median():.0f} dias")
print(f" Entregas tardias: {df['late_delivery'].mean():.1%}")
Limpieza y manejo de valores atipicos¶
In [5]:
# Handle negative delivery_days (data quality issue)
neg_delivery = (df["delivery_days"] < 0).sum()
print(f"Pedidos con delivery_days negativo: {neg_delivery} -- se reemplazan con NaN")
df.loc[df["delivery_days"] < 0, "delivery_days"] = np.nan
# Flag outliers in total_order_value (above 99th percentile)
p99 = df["total_order_value"].quantile(0.99)
df["is_high_value_outlier"] = df["total_order_value"] > p99
print(f"Umbral de outlier (p99): R${p99:,.2f} | Pedidos flaggeados: {df['is_high_value_outlier'].sum():,}")
# Null summary for key columns
key_cols = [
"customer_unique_id", "order_month", "total_payment", "review_score",
"delivery_days", "main_category", "total_order_value", "cohort_month",
]
null_report = df[key_cols].isnull().sum()
null_report = null_report[null_report > 0]
if len(null_report) > 0:
print(f"\nNulls en columnas clave:")
for col, n in null_report.items():
print(f" {col}: {n:,} ({n/len(df):.1%})")
else:
print("\nSin nulls en columnas clave.")
print(f"\nTabla final: {df.shape[0]:,} filas x {df.shape[1]} columnas")
Pedidos con delivery_days negativo: 0 -- se reemplazan con NaN Umbral de outlier (p99): R$1,052.39 | Pedidos flaggeados: 965 Nulls en columnas clave: total_payment: 1 (0.0%) review_score: 646 (0.7%) delivery_days: 8 (0.0%) main_category: 1,332 (1.4%) Tabla final: 96,478 filas x 30 columnas
Tabla resumen a nivel cliente¶
Agregamos a nivel customer_unique_id para tener un dataset de clientes listos para RFM y segmentacion.
In [6]:
# Reference date for recency calculation
reference_date = df["order_purchase_timestamp"].max()
cust = df.groupby("customer_unique_id").agg(
total_orders=("order_id", "nunique"),
total_revenue=("total_order_value", "sum"),
avg_order_value=("total_order_value", "mean"),
total_items=("items_per_order", "sum"),
first_order_date=("order_purchase_timestamp", "min"),
last_order_date=("order_purchase_timestamp", "max"),
cohort_month=("cohort_month", "first"),
customer_state=("customer_state", "first"),
avg_review_score=("review_score", "mean"),
avg_delivery_days=("delivery_days", "mean"),
late_deliveries=("late_delivery", "sum"),
is_repeat_customer=("is_repeat_customer", "first"),
# First order features (for activation analysis)
first_order_payment_type=("payment_type_mode", "first"),
first_order_category=("main_category", "first"),
first_order_weekend=("is_weekend", "first"),
).reset_index()
# Recency in days
cust["recency_days"] = (reference_date - cust["last_order_date"]).dt.days
# Customer lifetime in days
cust["lifetime_days"] = (cust["last_order_date"] - cust["first_order_date"]).dt.days
# First order value
first_orders = df[df["order_number"] == 1][["customer_unique_id", "total_order_value", "items_per_order", "review_score"]].rename(
columns={
"total_order_value": "first_order_value",
"items_per_order": "first_order_items",
"review_score": "first_order_review",
}
)
cust = cust.merge(first_orders, on="customer_unique_id", how="left")
print(f"Clientes: {len(cust):,}")
print(f" Repeat: {cust['is_repeat_customer'].sum():,} ({cust['is_repeat_customer'].mean():.1%})")
print(f" Ingresos totales: R${cust['total_revenue'].sum():,.2f}")
print(f" AOV promedio: R${cust['avg_order_value'].mean():,.2f}")
cust.head()
Clientes: 93,358 Repeat: 2,801 (3.0%) Ingresos totales: R$15,419,773.75 AOV promedio: R$160.29
Out[6]:
| customer_unique_id | total_orders | total_revenue | avg_order_value | total_items | first_order_date | last_order_date | cohort_month | customer_state | avg_review_score | ... | late_deliveries | is_repeat_customer | first_order_payment_type | first_order_category | first_order_weekend | recency_days | lifetime_days | first_order_value | first_order_items | first_order_review | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0000366f3b9a7992bf8c76cfdf3221e2 | 1 | 141.90 | 141.90 | 1 | 2018-05-10 10:56:27 | 2018-05-10 10:56:27 | 2018-05 | SP | 5.0 | ... | 0 | False | credit_card | bed_bath_table | False | 111 | 0 | 141.90 | 1 | 5.0 |
| 1 | 0000b849f77a49e4a4ce2b2a4ca5be3f | 1 | 27.19 | 27.19 | 1 | 2018-05-07 11:11:27 | 2018-05-07 11:11:27 | 2018-05 | SP | 4.0 | ... | 0 | False | credit_card | health_beauty | False | 114 | 0 | 27.19 | 1 | 4.0 |
| 2 | 0000f46a3911fa3c0805444483337064 | 1 | 86.22 | 86.22 | 1 | 2017-03-10 21:05:03 | 2017-03-10 21:05:03 | 2017-03 | SC | 3.0 | ... | 0 | False | credit_card | stationery | False | 536 | 0 | 86.22 | 1 | 3.0 |
| 3 | 0000f6ccb0745a6a4b88665a16c9f078 | 1 | 43.62 | 43.62 | 1 | 2017-10-12 20:29:41 | 2017-10-12 20:29:41 | 2017-10 | PA | 4.0 | ... | 0 | False | credit_card | telephony | False | 320 | 0 | 43.62 | 1 | 4.0 |
| 4 | 0004aac84e0df4da2b147fca70cf8255 | 1 | 196.89 | 196.89 | 1 | 2017-11-14 19:45:42 | 2017-11-14 19:45:42 | 2017-11 | SP | 5.0 | ... | 0 | False | credit_card | telephony | False | 287 | 0 | 196.89 | 1 | 5.0 |
5 rows × 21 columns
Exportar a Parquet¶
Guardamos los dos datasets principales que alimentan todos los notebooks siguientes.
In [7]:
# Save orders_enriched
df.to_parquet(OUT / "orders_enriched.parquet", index=False)
print(f"orders_enriched.parquet: {len(df):,} filas, {(OUT / 'orders_enriched.parquet').stat().st_size / 1024:.0f} KB")
# Save customers_summary
cust.to_parquet(OUT / "customers_summary.parquet", index=False)
print(f"customers_summary.parquet: {len(cust):,} filas, {(OUT / 'customers_summary.parquet').stat().st_size / 1024:.0f} KB")
# Save items_enriched (for category transition analysis in NB04)
items_enriched_full = items_enriched.merge(
df[["order_id", "customer_unique_id", "order_number", "order_month"]].drop_duplicates(),
on="order_id", how="left"
)
items_enriched_full.to_parquet(OUT / "items_enriched.parquet", index=False)
print(f"items_enriched.parquet: {len(items_enriched_full):,} filas, {(OUT / 'items_enriched.parquet').stat().st_size / 1024:.0f} KB")
print("\nExportacion completada.")
orders_enriched.parquet: 96,478 filas, 14509 KB customers_summary.parquet: 93,358 filas, 6278 KB
items_enriched.parquet: 112,650 filas, 9802 KB Exportacion completada.
So What? El dataset tiene ~96K clientes unicos pero solo ~3% realizan una segunda compra. Esto hace que el analisis de retencion sea desafiante, pero revela una oportunidad: identificar que diferencia al 3% que regresa. Los notebooks siguientes exploran exactamente eso.
Archivos generados:
data/processed/orders_enriched.parquet-- Tabla maestra a nivel pedidodata/processed/customers_summary.parquet-- Tabla a nivel cliente (para RFM, LTV)data/processed/items_enriched.parquet-- Items con categoria y cliente (para transiciones)