Brazil Shops on Weekdays: Olist's Exploratory Storytelling

Chapter 1 mapped the relational model and built the master dataframe. Now I let the data speak visually, six different angles on the same business, before training a single model. Full executed notebook, public on Colab.

Temporal: Brazil shops on weekdays

orders_com_hora = orders.dropna(subset=['order_purchase_timestamp']).copy()
orders_com_hora['weekday'] = orders_com_hora['order_purchase_timestamp'].dt.weekday
orders_com_hora['hour'] = orders_com_hora['order_purchase_timestamp'].dt.hour

weekday_hour = (
    orders_com_hora
    .groupby(['weekday', 'hour'])['order_id']
    .nunique()
    .reset_index()
    .rename(columns={'order_id': 'orders'})
)

Loading real data...

168 cells (7 days × 24 hours), the sum matches the total order count exactly, always worth checking before trusting the chart. The peak is Tuesday at 2pm, with 1,124 orders in a single hour. Looking at totals by weekday, Monday through Friday each land between 14 and 16 thousand orders, while Saturday drops to 10,887 and Sunday to 11,960, almost a 30% dip. By hour, lunchtime and early afternoon (11am, 1pm-4pm) hold the bulk of activity, and the early morning hours (3am-5am) practically flatline, under 300 orders in each of those hours across the entire dataset. That's the classic "shopping from work or on a break" pattern, not late-night leisure browsing.

Geographic: the North waits longer

orders_com_cliente = orders.merge(customers, on='customer_id', how='left')

frete_por_pedido = order_items.groupby('order_id')['freight_value'].sum().reset_index()
orders_com_cliente = orders_com_cliente.merge(frete_por_pedido, on='order_id', how='left')

orders_com_cliente['delivery_days'] = (
    orders_com_cliente['order_delivered_customer_date'] - orders_com_cliente['order_purchase_timestamp']
).dt.days

por_estado = orders_com_cliente.groupby('customer_state').agg(
    orders=('order_id', 'nunique'),
    avg_freight=('freight_value', 'mean'),
    avg_delivery_days=('delivery_days', 'mean'),
).reset_index()

Loading real data...

The map shows real average delivery time (purchase to delivery) by customer state. Roraima leads with almost 29 days of average wait (28.98), followed by Amapá (26.73), Amazonas (25.99), and Alagoas (24.04). It's no coincidence those states top the list: Roraima, Amapá, and Amazonas are three of the states farthest from the Southeast's industrial and logistics hub, where most Olist sellers are concentrated. São Paulo, for comparison, sits much closer to the fast end of this distribution. That distance-to-delivery-time relationship is a natural candidate for a strong feature once I build the delay prediction scenario.

Category: health and beauty leads revenue

top_categorias = (
    mestre
    .dropna(subset=['product_category_name_english'])
    .groupby('product_category_name_english')['price']
    .sum()
    .sort_values(ascending=False)
    .head(15)
    .reset_index()
)

Loading real data...

health_beauty leads with 1,301,947.97 dollars in combined revenue, closely followed by watches_gifts (1,254,322.95) and bed_bath_table (1,107,249.09). This is revenue, not freight or item count, so a category can lead either by selling expensive items (watches) or by selling a lot of them (bed/bath/table items get replaced often).

Payment: credit card dominates

tipos_pagamento = (
    payments[payments['payment_type'] != 'not_defined']
    .groupby('payment_type')['order_id']
    .nunique()
    .reset_index()
    .rename(columns={'order_id': 'count'})
    .sort_values('count', ascending=False)
)

Loading real data...

Credit card dominates by a wide margin: 76,505 orders, over 3 times the runner-up (boleto, a Brazilian bank-slip payment method, 19,784). Voucher comes in at 3,866 and debit card at only 1,528. I dropped the not_defined category from the chart, it only had 3 rows out of over 100 thousand payments, pure noise, not worth a pie slice.

Satisfaction: most love it, but the haters are vocal

distribuicao_notas = (
    reviews
    .groupby('review_score')['review_id']
    .count()
    .reset_index()
    .rename(columns={'review_id': 'count'})
)

Loading real data...

57,328 five-star reviews, over half the total, with four-star coming in second at 19,142. But look at third place: one-star reviews, at 11,424, alone outnumber two-star (3,151) and three-star (8,179) combined. That's the classic e-commerce review pattern: a satisfied customer sometimes doesn't bother reviewing at all, a neutral customer almost never does, and a very unhappy customer always shows up to vent. A distribution shaped like this (a big peak at 5, a smaller second peak at 1) is already a warning for when I build the score prediction scenario later: it's not a well-behaved continuous scale, it's closer to a "loved it or hated it" decision with a rare middle ground.

Alongside that, a real sample (400 orders) crossing delivery delay against the score given:

nota_atraso = (
    orders[['order_id', 'order_delivered_customer_date', 'order_estimated_delivery_date']]
    .merge(reviews[['order_id', 'review_score']], on='order_id', how='inner')
    .dropna(subset=['order_delivered_customer_date', 'order_estimated_delivery_date', 'review_score'])
)

nota_atraso['delay_days'] = (
    nota_atraso['order_delivered_customer_date'] - nota_atraso['order_estimated_delivery_date']
).dt.days

Loading real data...

A negative delay_days means the order arrived early, positive means a real delay. Notice how the one-star points (the bottom row) spread across the whole axis, including into negative territory, but with a visibly heavier concentration on the delay side (right) than the five-star points show.

Correlation: the punchline before the model

features_numericas = mestre[['price', 'freight_value', 'product_weight_g', 'payment_value', 'review_score']].copy()
features_numericas['delivery_days'] = (
    mestre['order_delivered_customer_date'] - mestre['order_purchase_timestamp']
).dt.days
features_numericas = features_numericas.dropna()

matriz_corr = features_numericas.corr()

Loading real data...

Here's the number I promised back in Chapter 1's closing: review_score correlates at -0.30 with delivery_days, computed over 114,838 rows of the master dataframe. That's the strongest relationship any numeric feature has with the review score (price, freight, and product weight all sit below 0.08 in absolute value). It's not a huge correlation (nowhere near -1), but it's clearly the standout of the group, and it lines up exactly with what the geographic map already hinted at: slow delivery looks like the single factor most associated with dragging customer satisfaction down, more than what the product cost or weighed.

Wrapping up the chapter

Six angles, one pattern emerging: delivery time shows up three separate times (map, scatter, correlation) as the strongest thread for explaining dissatisfaction. Credit card dominates payment, health and beauty dominates revenue, Tuesday afternoon is peak shopping time. Next chapter I move into feature engineering and selection for the four scenarios defined in Chapter 1, and the data-leakage discussion for the delay scenario is going to land a lot more concretely with these geographic and correlation numbers already in hand.