OLIST E-Commerce SQL Analysis

Dataset: Brazilian E-Commerce by Olist (around ~100k real orders from 2016-2018)
Database: PostgreSQL 15 running in Docker
Stack: PostgreSQL, Python, pandas, matplotlib/seaborn

I built this project to work through a real world e-commerce dataset in SQL. OLIST is a Brazilian marketplace (similar to Amazon) and the dataset is publicly available on Kaggle. It has enough tables and relationships to make the queries interesting, and enough rows that performance starts to matter.

---

Questions this analysis covers

1. Is revenue growing month over month?
2. Where do orders drop out of the funnel?
3. Which Brazilian states are the strongest markets?
4. When do customers shop, and are they coming back?
5. Which product categories drive the most revenue?
6. Does faster delivery actually lead to better reviews?
7. Who are the top sellers, and who is dragging performance down?
8. Which customer segments deserve the most attention?

import warnings
warnings.filterwarnings('ignore')

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
from sqlalchemy import create_engine

from scipy.stats import kruskal

engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/olist_db')

# Query fct:
def q(sql):
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)

sns.set_theme(style = 'whitegrid', context = 'notebook')
plt.rcParams.update({
    'figure.dpi': 130,
    'font.family': 'sans-serif',
    'axes.spines.top': False,
    'axes.spines.right': False,
    'axes.titlesize': 14,
    'axes.titleweight': 'bold',
    'axes.titlepad': 12,
    'axes.labelsize': 11,
})

PRIMARY = '#1565C0'
ACCENT = '#E53935'
MUTED = '#90CAF9'
SPECIAL = "#0F9DB6"

print('Connected to olist_db')
q("select current_database() as db")

Connected to olist_db

	db
0	olist_db

---

1. First look at the dataset

Before writing any analysis queries it is important to start by understanding the shape of what we are working with (row counts, date range, basic totals, etc.). You can catch a lot of problems early by looking at the data before assuming it's clean.

df_sizes = q("""
    select
        'customers' as tbl,
        count(*) as rows
    from customers
    union all
    select
        'orders',
        count(*)
    from orders
    union all
    select
        'order_items',
        count(*)
    from order_items
    union all
    select
        'order_payments',
        count(*)
    from order_payments
    union all
    select
        'order_reviews',
        count(*)
    from order_reviews
    union all
    select
        'sellers',
        count(*)
    from sellers
    union all
    select
        'products',
        count(*)
    from products
    order by rows desc
""")

dp = df_sizes.sort_values('rows')
fig, ax = plt.subplots(figsize = (10, 4))
sns.barplot(data = dp, x = 'rows', y = 'tbl', color = SPECIAL, ax = ax)
for i, v in enumerate(dp['rows']):
    ax.text(v, i, f' {int(v):,}', va = 'center', fontsize = 10)
ax.set_title('Dataset Rows per Table')
ax.set_xlabel('Row Count')
ax.set_ylabel('') # Removes the y axis 'tbl' label
ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:,.0f}'))
ax.set_xlim(0, dp['rows'].max() * 1.2)
plt.tight_layout()
plt.show()

stats = q("""
    select
        min(order_purchase_timestamp) as first_order,
        max(order_purchase_timestamp) as last_order,
        count(distinct order_id) as total_orders,
        count(distinct customer_id) as unique_customers
    from orders
""")

rev = q("""
    select 
        round(sum(payment_value), 0) as total_revenue 
    from order_payments
""")

print(f"Date range: {stats['first_order'][0]} to {stats['last_order'][0]}")
print(f"Total orders: {int(stats['total_orders'][0]):,}")
print(f"Customers: {int(stats['unique_customers'][0]):,}")
print(f"Total revenue: R${int(rev['total_revenue'][0]):,}") 

Date range: 2016-09-04 21:15:19 to 2018-10-17 17:30:18
Total orders: 99,441
Customers: 99,441
Total revenue: R$16,008,872

Data Quality Check

Part of that initial look is checking for obvious problems before they break something downstream. Things I specifically wanted to verify is impossible timestamps, orders with no items or payment record, and whether the price data had any obvious outliers.

# delivered before purchased
bad_dates = q("""
    select
        count(*) as impossible_deliveries
    from orders
    where order_delivered_customer_date < order_purchase_timestamp
""")

# orders with no line items at all
no_items = q("""
    select
        count(*) as orders_without_items
    from orders o
    left join order_items oi on oi.order_id = o.order_id
    where oi.order_id is null
""")

# non cancelled orders with no payment record
no_payment = q("""
    select
        count(*) as orders_without_payment
    from orders o
    left join order_payments op on op.order_id = o.order_id
    where op.order_id is null
      and o.order_status not in ('canceled', 'unavailable')
""")

# products missing a category name
null_cat = q("""
    select
        count(*) as products_missing_category
    from products
    where product_category_name is null
""")

# price range and zero price items
price_stats = q("""
    select
        round(min(price), 2) as min_price,
        round(avg(price), 2) as avg_price,
        round(max(price), 2) as max_price,
        count(*) filter (where price = 0) as zero_price_items
    from order_items
""")

print('Check for anomalies:')
print(f"Impossible deliveries (delivered < purchased): {int(bad_dates['impossible_deliveries'][0])}")
print(f"Orders with no line items: {int(no_items['orders_without_items'][0])}")
print(f"Non-cancelled orders with no payment: {int(no_payment['orders_without_payment'][0])}")
print(f"Products missing category name: {int(null_cat['products_missing_category'][0])}")

print(f"\nItem prices:")
print(f"- min R${price_stats['min_price'][0]}")
print(f"- avg R${price_stats['avg_price'][0]}")
print(f"- max R${price_stats['max_price'][0]}")
print(f"- zero price items {int(price_stats['zero_price_items'][0])}")

Check for anomalies:
Impossible deliveries (delivered < purchased): 0
Orders with no line items: 775
Non-cancelled orders with no payment: 1
Products missing category name: 610

Item prices:
- min R$0.85
- avg R$120.65
- max R$6735.0
- zero price items 0

---

2. Revenue and Growth

First thing to know was whether the business is actually growing or not. The raw month-over-month numbers bounce around a lot, so I added a 3-month rolling average alongside the monthly bars to make the underlying trend more readable.

The biggest finding is that Nov 2017 dominates the whole picture. Revenue that month is much higher than average, almost certainly because of Black Friday. We can also see that the ramp up starts in October, which could matter a lot for planning strategy in advance. Any future marketing work should treat October as the start of the campaign window month instead of November.

Outside the Nov spike, the global trend is positive but much more reserved than the Q4 numbers may suggest. If we strip out November, what remains is steady growth across most months, not exponential increase. The business wasn't growing at a Black Friday pace during the entire year, and that matters for setting expectations.

The cumulative view confirms a mostly consistent acceleration through the year, but the real jump is concentrated in Q4. One thing the charts raise but can't answer is whether the growth is coming from more orders or from higher average order values. More orders means acquisition is working. Higher AOV (average order value) means customers are buying more per visit. The split isn't visible in this view and the two cases call for completely different responses.

df_rev = q("""
    select
        date_trunc('month', o.order_purchase_timestamp)::date as month,
        round(sum(oi.price), 2) as revenue
    from orders o
    join order_items oi on oi.order_id = o.order_id
    where o.order_purchase_timestamp is not null
      and date_trunc('month', o.order_purchase_timestamp) < date_trunc('month', (
          select max(order_purchase_timestamp) from orders
      ))
    group by 1
    order by 1
""")

df_rev['month'] = pd.to_datetime(df_rev['month'])
df_rev['month_lbl'] = df_rev['month'].dt.strftime('%Y-%m')
df_rev['mom_pct'] = df_rev['revenue'].pct_change() * 100
df_rev['direction'] = df_rev['mom_pct'].fillna(0).apply(lambda v: 'down' if v < 0 else 'up')
df_rev['rolling_3m'] = df_rev['revenue'].rolling(3, min_periods=1).mean()

fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(13, 9), sharex=True,
                                gridspec_kw={'height_ratios': [2, 1]})
fig.suptitle('Monthly Revenue and Growth', fontsize=16, fontweight='bold', y=1.01)

sns.lineplot(data=df_rev, x='month_lbl', y='revenue', color=PRIMARY,
             linewidth=2.5, marker='o', markersize=6, ax=ax1, label='Monthly revenue')
sns.lineplot(data=df_rev, x='month_lbl', y='rolling_3m', color=ACCENT,
             linewidth=1.8, linestyle='--', ax=ax1, label='3-month rolling avg')
ax1.fill_between(df_rev['month_lbl'], df_rev['revenue'], alpha=0.12, color=PRIMARY)
ax1.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"R${x/1e3:.0f}k"))
ax1.set_title('Monthly Revenue')
ax1.set_ylabel('Revenue (R$)')
ax1.legend(fontsize=9)

clip_hi = 150
df_rev['mom_val'] = df_rev['mom_pct'].fillna(0)
sns.barplot(data=df_rev, x='month_lbl', y='mom_val', hue='direction',
            palette={'up': PRIMARY, 'down': ACCENT}, dodge=False,
            ax=ax2, legend=False)
ax2.axhline(0, color='black', linewidth=0.8)
ax2.set_ylim(-80, clip_hi)
for i, row in df_rev.reset_index(drop=True).iterrows():
    if row['mom_val'] > clip_hi:
        ax2.text(i, clip_hi - 5, f"+{row['mom_val']:.0f}%",
                 ha='center', va='top', fontsize=7, color='gray', style='italic')
ax2.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{x:+.0f}%"))
ax2.set_title('Month-over-Month Growth  (y-axis capped at +150%)')
ax2.set_ylabel('MoM %')
ax2.set_xlabel('')
ax2.set_xticklabels(ax2.get_xticklabels(), rotation=45, ha='right')
plt.tight_layout()
plt.show()

The dataset ends mid-2018 with 2018 revenue trending upward from where 2017 left off. Whether that growth continued or peaked after that point isn't visible here, which would be interesting to see with additional data.

---

3. Order Funnel

Not every order makes it to delivery. What is surprising is that more than 97% do reach delivered status, which makes the pipeline unusually healthy for a marketplace of this scale. Most multi-seller platforms lose meaningful volume to seller cancellations, out of stock issues and payment failures. OLIST doesn't appear to have that problem, at least not at a level that shows in overall numbers.

What this changes is the actual challenge. Because the pipeline is so clean, the business problem isn't order rescue. It's what happens after the first delivery. A marketplace that converts nearly every placed order but retains almost none of its buyers has a very different set of problems from one that's leaking orders before delivery.

df_f = q("""
    select
        order_status,
        count(*) as orders,
        round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
    from orders
    group by 1
    order by orders desc
""")

def fc(s):
    if s == 'delivered': 
        return PRIMARY
    if s in ('shipped', 'processing', 'approved', 'invoiced'): 
        return MUTED
    return ACCENT

palette = {s: fc(s) for s in df_f['order_status']}

fig, ax = plt.subplots(figsize=(10, 5))
sns.barplot(data=df_f, x='orders', y='order_status', hue='order_status',
            palette=palette, ax=ax, legend=False)
for i, (orders, pct) in enumerate(zip(df_f['orders'], df_f['pct'])):
    ax.text(orders + 300, i, f'{pct}%', va='center',
            fontsize=10, fontweight='bold')
ax.set_title('Order Status Funnel')
ax.set_xlabel('Number of Orders')
ax.set_ylabel('')
ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:,.0f}'))
ax.set_xlim(0, df_f['orders'].max() * 1.15)
plt.tight_layout()
plt.show()
df_f

	order_status	orders	pct
0	delivered	96478	97.0
1	shipped	1107	1.1
2	canceled	625	0.6
3	unavailable	609	0.6
4	invoiced	314	0.3
5	processing	301	0.3
6	created	5	0.0
7	approved	2	0.0

The remaining statuses are each negligable, but worth tracking over time. A spike in unavailable could indicate seller inventory problems and a spike in processing could point to payment or approval delays.

---

4. Geographic Performance

Brazil is a huge country with very uneven economic distribution, so it's worth seeing how revenue breaks down by state. Comparing total revenue against average order value separately makes sense because they tell different stories about where the growth opportunities actually are.

Sao Paulo accounts for roughly 40% of all revenue, which wasn't a surprise. But the average order value chart is more interesting. Several smaller states record higher per-order spend than SP. High volume and high ticket size don't always go together. MG and RJ show the most interesting profile, having meaningful order volume while having lower than SP per-order values, which is exactly the kind of market worth developing.

df_geo = q("""
    select
        c.customer_state as state,
        count(distinct o.order_id) as orders,
        round(sum(op.payment_value), 0) as revenue,
        round(avg(op.payment_value), 2) as avg_order_value
    from customers c
    join orders o on o.customer_id = c.customer_id
    join order_payments op on op.order_id = o.order_id
    group by 1
    order by revenue desc
    limit 15
""")

fig, axes = plt.subplots(1, 2, figsize=(16, 6))
fig.suptitle('Geographic Performance - Top 15 States', fontsize=15, fontweight='bold')

df_r = df_geo.sort_values('revenue', ascending=True)
sns.barplot(data=df_r, x='revenue', y='state', color=PRIMARY, ax=axes[0])
for i, v in enumerate(df_r['revenue']):
    axes[0].text(v, i, f' R${v/1e6:.1f}M', va='center', fontsize=9)
axes[0].set_title('Total Revenue')
axes[0].set_xlabel('Revenue')
axes[0].set_ylabel('')
axes[0].xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'R${x/1e6:.0f}M'))
axes[0].set_xlim(0, df_r['revenue'].max() * 1.22)

df_a = df_geo.sort_values('avg_order_value', ascending=True)
sns.barplot(data=df_a, x='avg_order_value', y='state', color='#1E88E5', ax=axes[1])
for i, v in enumerate(df_a['avg_order_value']):
    axes[1].text(v, i, f' R${v:.0f}', va='center', fontsize=9)
axes[1].set_title('Avg Order Value')
axes[1].set_xlabel('Avg Order Value')
axes[1].set_ylabel('')
axes[1].set_xlim(0, df_a['avg_order_value'].max() * 1.2)
plt.tight_layout()
plt.show()

Freight Cost Analysis

freight_value in the schema wasn't being used anywhere in the initial queries. Shipping cost as a percentage of item price is a real margin question, and it would be expected to vary a lot by state given Brazil's geography. Most sellers are concentrated in SP, so customers in the north have much longer supply chains.

What stands out is the scale of the difference. In some northern states (AM, PA, RR, AC) freight runs to 30-40% of the item price. That's effectively a hidden price increase for those customers without the list price changing, which has real implications for what categories those customers will and won't buy.

df_freight = q("""
    select
        c.customer_state as state,
        count(distinct o.order_id) as orders,
        round(avg(oi.price), 2) as avg_item_price,
        round(avg(oi.freight_value), 2) as avg_freight,
        round(100.0 * avg(oi.freight_value) / nullif(avg(oi.price), 0), 1) as freight_pct
    from orders o
    join customers c on c.customer_id = o.customer_id
    join order_items oi on oi.order_id = o.order_id
    where o.order_status = 'delivered'
    group by 1
    having count(distinct o.order_id) >= 100
    order by freight_pct desc
    limit 15
""")

df_fr = df_freight.sort_values('freight_pct', ascending=True)
avg_pct = float(df_freight['freight_pct'].mean())
colors = [ACCENT if float(v) >= avg_pct * 1.3 else PRIMARY for v in df_fr['freight_pct']]
palette = dict(zip(df_fr['state'], colors))

fig, ax = plt.subplots(figsize=(11, 7))
sns.barplot(data=df_fr, x='freight_pct', y='state',
            hue='state', palette=palette, ax=ax, legend=False)
for i, v in enumerate(df_fr['freight_pct']):
    ax.text(float(v), i, f' {v}%', va='center', fontsize=9)
ax.axvline(avg_pct, color='black', linestyle='--', linewidth=1, alpha=0.6,
           label=f'Average ({avg_pct:.0f}%)')
ax.set_title('Freight as % of Item Price by State  (top 15 highest)')
ax.set_xlabel('Freight / Item Price (%)')
ax.set_ylabel('')
ax.legend(fontsize=9)
plt.tight_layout()
plt.show()

Customers in remote states who still purchase despite freight costs of 30-40% of item price are most likely either buying something expensive enough that the cost doesn't matter, or have no local option. Expanding the seller base into MG, RS, or PR would bring average freight down for a lot of buyers without touching prices.

---

5. Customer Behaviour

Two things worth looking at is when the customers are actually shopping, and if they coming back?

Something worth mentioning: OLIST assigns a brand new customer_id to each individual order, meaning the same person gets a different ID every time they buy. Analysing repeat purchases directly on customer_id would show 0% retention for every single customer. The actual person-level identifier is customer_unique_id in the customers table, which stays consistent across a person's orders. This only became clear because the repeat rate came back at exactly 0.00% on a first pass, which was suspicious enough to dig into.

On days of the week, Mondays are the usually busiest day. That's counterintuitive if you'd expect weekend peaks, but it makes sense once you consider what's probably happening. Customers browse on Sunday evenings and convert early Monday. The chart shows a gradual decline across the rest of the week, with weekends noticeably quieter.

The repeat purchase rate is around 3%, and that number changes everything else in the analysis. Over 97% of customers place exactly one order and never return. In most healthy e-commerce businesses repeat purchase rates run 20-40%. A 3% figure isn't a bad quarter. It's just how this platform works. The cost of acquiring a customer is paid entirely by their first order. For over 97% of buyers, there is no second order to recover that cost.

df_dow = q("""
    select
        to_char(order_purchase_timestamp, 'Dy') as day,
        extract(dow from order_purchase_timestamp) as dow_num,
        count(*) as orders
    from orders
    where order_purchase_timestamp is not null
    group by 1, 2
    order by dow_num
""")

#! In OLIST, orders.customer_id is unique per order, meaning each order gets a fresh ID. 
#! The actual repeat customer identifier is customers.customer_unique_id.
df_rpt = q("""
    with co as (
        select
            c.customer_unique_id,
            count(*) as n
        from orders o
        join customers c on c.customer_id = o.customer_id
        group by 1
    )
    select
        count(*) as total,
        count(*) filter (where n > 1) as repeat_buyers,
        round(100.0 * count(*) filter (where n > 1) / count(*), 1) as repeat_rate
    from co
""")

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15, 5))
fig.suptitle('Customer Behaviour', fontsize=15, fontweight='bold')

df_dow_p = df_dow.assign(weekend=df_dow['dow_num'].isin([0, 6]))
sns.barplot(data=df_dow_p, x='day', y='orders', hue='weekend',
            palette={True: MUTED, False: PRIMARY}, dodge=False,
            ax=ax1, legend=False)
for i, v in enumerate(df_dow_p['orders']):
    ax1.text(i, v, f'{v:,.0f}', ha='center', va='bottom', fontsize=9)
ax1.set_title('Orders by Day of Week')
ax1.set_ylabel('Orders')
ax1.set_xlabel('')
ax1.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:,.0f}'))

total = int(df_rpt['total'][0])
repeat = int(df_rpt['repeat_buyers'][0])
rate = float(df_rpt['repeat_rate'][0])
# Donut: seaborn has no native pie, so use matplotlib for the wedges
ax2.pie([repeat, total - repeat], colors=[PRIMARY, MUTED], startangle=90,
        wedgeprops={'width': 0.5, 'edgecolor': 'white', 'linewidth': 2})
ax2.text(0, 0, f'{rate}%\nrepeat', ha='center', va='center',
         fontsize=16, fontweight='bold', color=PRIMARY)
ax2.set_title('Repeat Purchase Rate')
ax2.legend(['Repeat buyers', 'One-time buyers'], loc='lower center',
           ncol=2, fontsize=9)
plt.tight_layout()
plt.show()
print(f'Unique customers : {total:,}')
print(f'Repeat buyers : {repeat:,}  ({rate}%)')
print(f'One-time buyers : {total - repeat:,}  ({100 - rate:.1f}%)')

Unique customers : 96,096
Repeat buyers : 2,997  (3.1%)
One-time buyers : 93,099  (96.9%)

One thing worth keeping in mind is that the 3% figure almost certainly understates real repeat purchasing. Customers who signed up again with a different email won't be linked through customer_unique_id. The true number is probably a bit higher, though probably not by enough to change it by much.

---

6. Product Categories

Which categories are actually driving revenue? The raw data has category names in Portuguese, so there's a translation table join to get English names. Electronics seemed like the obvious leader, which turned out to be wrong.

Health and Beauty leads, and it's not particularly close. Watches/Clocks and Bed & Bath round out the top three. The translation join matters more than it might seem. Without it category names are opaque Portuguese strings that are usable for grouping but meaningless for anyone reading the output.

What's interesting about Health & Beauty leading is that it's the kind of thing people buy more than once. Customers should be coming back for refills. The fact that the platform's biggest category has natural repeat demand, and yet the overall repeat rate is 3%, is a real gap. Either H&B buyers on OLIST aren't buying consumables (they're picking up a one time gift), or there's nothing pulling them back when they run out.

df_cat = q("""
    with cat as (
        select
            p.product_category_name,
            round(sum(oi.price), 0) as revenue,
            count(*) as units_sold
        from order_items oi
        join products p on p.product_id = oi.product_id
        where p.product_category_name is not null
        group by 1
    )
    select
        coalesce(t.product_category_name_english, c.product_category_name) as category,
        c.revenue,
        c.units_sold
    from cat c
    left join product_category_translation t on t.product_category_name = c.product_category_name
    order by revenue desc
    limit 15
""")

dp = df_cat.sort_values('revenue', ascending=True)
n = len(dp)
grad = ([PRIMARY] * 5 + ['#1E88E5'] * 5 + ['#42A5F5'] * 5)[:n][::-1]
palette = dict(zip(dp['category'], grad))

fig, ax = plt.subplots(figsize=(11, 7))
sns.barplot(data=dp, x='revenue', y='category', hue='category',
            palette=palette, ax=ax, legend=False)
for i, v in enumerate(dp['revenue']):
    ax.text(v, i, f' R${v/1e3:.0f}k', va='center', fontsize=9)
ax.set_title('Top 15 Product Categories by Revenue')
ax.set_xlabel('Revenue (R$)')
ax.set_ylabel('')
ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'R${x/1e3:.0f}k'))
ax.set_xlim(0, dp['revenue'].max() * 1.22)
plt.tight_layout()
plt.show()

Whether a category's revenue comes from expensive items or high volume matters a lot for how you'd try to grow it. The two cases need completely different approaches, and the split isn't visible here.

---

7. Delivery Speed vs Customer Satisfaction

The question is whether delivery time actually affects review scores, or whether customers mostly rate based on the product itself. The answer turns out to be pretty unambiguous.

Orders delivered in 0-3 days average 4.46 stars. Orders taking 22+ days average 3.01 stars. That 1.45-star gap is the clearest relationship in the whole dataset. It holds across tens of thousands of orders and is large enough to actually do something with.

df_del = q("""
    select
        case
            when dd <= 3 then '0-3 days'
            when dd <= 7 then '4-7 days'
            when dd <= 14 then '8-14 days'
            when dd <= 21 then '15-21 days'
            else '22+ days'
        end as bucket,
        case
            when dd <= 3 then 1
            when dd <= 7 then 2
            when dd <= 14 then 3
            when dd <= 21 then 4
            else 5
        end as sk,
        count(*) as orders,
        round(avg(rev.review_score), 2) as avg_score
    from (
        select
            o.order_id,
            extract(day from (
                o.order_delivered_customer_date - o.order_purchase_timestamp
            )) as dd
        from orders o
        where o.order_delivered_customer_date is not null
    ) d
    join order_reviews rev on rev.order_id = d.order_id
    group by 1, 2
    order by 2
""")

fig, ax1 = plt.subplots(figsize=(11, 5))
ax2 = ax1.twinx()

sns.barplot(data=df_del, x='bucket', y='orders', color=MUTED, alpha=0.85,
            ax=ax1, label='# Orders')
df_del_idx = df_del.assign(_x=range(len(df_del)))
sns.lineplot(data=df_del_idx, x='_x', y='avg_score',
             color=ACCENT, marker='o', linewidth=2.5, markersize=9,
             ax=ax2, label='Avg Review Score', zorder=5)
for i, score in enumerate(df_del['avg_score']):
    ax2.text(i, float(score) + 0.1, str(score),
             ha='center', fontsize=10, color=ACCENT, fontweight='bold')

ax1.set_ylabel('Number of Orders', color=PRIMARY)
ax1.set_xlabel('')
ax2.set_ylabel('Avg Review Score', color=ACCENT)
ax2.set_ylim(1, 5.8)
ax2.grid(False)
ax1.set_title('Does Faster Delivery Lead to Better Reviews?')
ax1.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:,.0f}'))

l1, lb1 = ax1.get_legend_handles_labels()
l2, lb2 = ax2.get_legend_handles_labels()
if ax2.get_legend() is not None:
    ax2.get_legend().remove()
ax1.legend(l1 + l2, lb1 + lb2, loc='upper right')
plt.tight_layout()
plt.show()

Order Pipeline Breakdown

That correlation required an obvious follow up. Where is the time actually going? There's a big difference between a seller taking a long time to dispatch an item and a carrier taking a long time in transit. The fix looks completely different depending on which one it is.

Average time from purchase to dispatch is 2.8 days, while the average time in transit is 9.3 days. The bottleneck isn't sellers being slow, it's the carrier transit time, and sellers have no control over that. Pushing sellers to dispatch faster is the wrong fix since 77% of total delivery time sits with the carrier.

df_pipeline = q("""
    select
        round(avg(extract(epoch from (
            order_approved_at - order_purchase_timestamp
        )) / 3600), 1) as avg_hours_to_approval,
        round(avg(extract(epoch from (
            order_delivered_carrier_date - order_approved_at
        )) / 86400), 1) as avg_days_to_dispatch,
        round(avg(extract(epoch from (
            order_delivered_customer_date - order_delivered_carrier_date
        )) / 86400), 1) as avg_days_in_transit,
        round(avg(extract(epoch from (
            order_delivered_customer_date - order_purchase_timestamp
        )) / 86400), 1) as avg_total_days
    from orders
    where order_approved_at is not null
      and order_delivered_carrier_date is not null
      and order_delivered_customer_date is not null
""")

df_stages = q("""
    select
        c.customer_state as state,
        count(*) as orders,
        round(avg(extract(epoch from (
            o.order_delivered_carrier_date - o.order_approved_at
        )) / 86400), 1) as days_to_dispatch,
        round(avg(extract(epoch from (
            o.order_delivered_customer_date - o.order_delivered_carrier_date
        )) / 86400), 1) as days_in_transit
    from orders o
    join customers c on c.customer_id = o.customer_id
    where o.order_approved_at is not null
      and o.order_delivered_carrier_date is not null
      and o.order_delivered_customer_date is not null
    group by 1
    having count(*) >= 200
    order by (avg(extract(epoch from (
        o.order_delivered_customer_date - o.order_purchase_timestamp
    )) / 86400)) desc
    limit 15
""")

print('overall pipeline averages:')
print(f"  approval   : {df_pipeline['avg_hours_to_approval'][0]} hours")
print(f"  dispatch   : {df_pipeline['avg_days_to_dispatch'][0]} days")
print(f"  in transit : {df_pipeline['avg_days_in_transit'][0]} days")
print(f"  total      : {df_pipeline['avg_total_days'][0]} days")

df_stages = df_stages.sort_values('days_in_transit', ascending=True).copy()
df_stages['total'] = df_stages['days_to_dispatch'] + df_stages['days_in_transit']

fig, ax = plt.subplots(figsize=(11, 7))
# draw total bar first (becomes the transit portion in MUTED),
# then overlay dispatch portion in PRIMARY from 0
sns.barplot(data=df_stages, x='total', y='state', color=MUTED, ax=ax, label='In transit')
sns.barplot(data=df_stages, x='days_to_dispatch', y='state', color=PRIMARY, ax=ax, label='Dispatch')
ax.set_title('Delivery Pipeline by State — Dispatch vs Transit  (slowest states)')
ax.set_xlabel('Days')
ax.set_ylabel('')
ax.legend(loc='lower right', fontsize=9)
plt.tight_layout()
plt.show()

overall pipeline averages:
  approval   : 10.3 hours
  dispatch   : 2.8 days
  in transit : 9.3 days
  total      : 12.6 days

The states with the longest transit times in the pipeline chart almost certainly overlap with the states paying the highest freight costs from section 4. These are the same customers paying more and waiting longer.

---

8. Seller Performance

Marketplace-level averages hide a lot of variation across individual sellers. Looking past those averages shows who is actually performing and who is dragging them down.

A platform-wide average of 12 days delivery and a 4.1 review score looks fine on the surface. The scatter plot breaks that apart. Some sellers deliver in under a week with 4.5+ star ratings. Others are slow and poorly reviewed despite meaningful revenue. Bubble size shows revenue, which makes it easy to see how much a small number of sellers matter.

The bubble size is the part worth looking at closely. A handful of sellers account for most of the revenue. How those sellers perform shapes the experience for a large portion of all buyers. There are also mid-revenue sellers with excellent scores and fast delivery. They look like sellers doing everything right but not getting enough visibility yet.

df_s = q("""
    select
        s.seller_id,
        s.seller_state,
        round(sum(oi.price), 0) as revenue,
        round(avg(extract(day from (
            o.order_delivered_customer_date - o.order_purchase_timestamp
        ))), 1) as avg_delivery_days,
        round(avg(rev.review_score), 2) as avg_score,
        count(distinct oi.order_id) as orders
    from sellers s
    join order_items oi on oi.seller_id = s.seller_id
    join orders o on o.order_id = oi.order_id
    left join order_reviews rev on rev.order_id = o.order_id
    where o.order_delivered_customer_date is not null
    group by 1, 2
    having count(distinct oi.order_id) >= 30
""")

md_days = df_s['avg_delivery_days'].median()
md_score = df_s['avg_score'].median()

fig, ax = plt.subplots(figsize=(12, 7))
sns.scatterplot(
    data=df_s, x='avg_delivery_days', y='avg_score',
    size='revenue', sizes=(20, 620),
    hue='revenue', palette='Blues',
    alpha=0.7, edgecolor='#1565C0', linewidth=0.4,
    legend=False, ax=ax,
)
norm = plt.Normalize(df_s['revenue'].min(), df_s['revenue'].max())
sm = plt.cm.ScalarMappable(cmap='Blues', norm=norm)
sm.set_array([])
cbar = plt.colorbar(sm, ax=ax)
cbar.set_label('Revenue (R$)', fontsize=10)
cbar.ax.yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, _: f'R${x/1e3:.0f}k')
)
ax.axvline(md_days,  color=ACCENT,  linestyle='--', linewidth=1.2, alpha=0.7,
           label=f'Median delivery ({md_days:.0f} days)')
ax.axhline(md_score, color='green', linestyle='--', linewidth=1.2, alpha=0.7,
           label=f'Median score ({md_score:.1f})')
ax.set_xlabel('Avg Delivery Days')
ax.set_ylabel('Avg Review Score')
ax.set_title('Seller Performance: Delivery Speed vs Satisfaction  (bubble = revenue)')
ax.legend(fontsize=9)
plt.tight_layout()
plt.show()
print(f'Sellers shown: {len(df_s):,}  (min 30 orders each)')

Sellers shown: 627  (min 30 orders each)

One important detail on the data is that the seller performance here is measured on delivered orders only. Sellers with high cancellation rates appear cleaner than they actually are, since cancelled orders carry no delivery time or review score.

---

9. Customer Segmentation (RFM Analysis)

RFM (Recency, Frequency, Monetary) is a segmentation framework that comes up a lot in analytics. This is the first time building it from scratch. The idea is to score each customer 1-5 on three dimensions (how recently they bought, how often, and how much), then group them into segments based on those scores.

The same customer_id problem from section 5 comes up here, frequency counts based on customer_id make every customer look like a unique buyer. Everything is joined through customer_unique_id to get accurate counts.

The most important number in this section is not the Champion count. It's the At Risk revenue. At Risk is the largest segment by total revenue, having 23,572 customers contributing R$5.7M. Champions are fewer (14,229) but spend more per head (R$315 vs R$243). These customers have already bought here before, so re-engaging them is almost always cheaper than finding someone new.

#! We group by customer_unique_id (the real person ID) not customer_id
df_rfm = q("""
    with rfm_base as (
        select
            c.customer_unique_id,
            max(o.order_purchase_timestamp) as last_order,
            count(distinct o.order_id) as frequency,
            round(sum(op.payment_value), 2) as monetary
        from orders o
        join customers c on c.customer_id = o.customer_id
        join order_payments op on op.order_id = o.order_id
        group by 1
    ),
    scored as (
        select
            customer_unique_id,
            monetary,
            ntile(5) over (order by last_order desc) as r,
            ntile(5) over (order by frequency asc) as f,
            ntile(5) over (order by monetary asc) as m
        from rfm_base
    ),
    segmented as (
        select
            customer_unique_id,
            monetary,
            case
                when r >= 4 and f >= 4 and m >= 4 then 'Champions'
                when r >= 3 and f >= 3 then 'Loyal'
                when r >= 4 and f <= 2 then 'New Customers'
                when r <= 2 and f >= 3 then 'At Risk'
                when r <= 2 and f <= 2 then 'Lost'
                else 'Potential Loyalists'
            end as segment
        from scored
    )
    select
        segment,
        count(*) as customers,
        round(avg(monetary), 2) as avg_spend,
        round(sum(monetary), 0) as total_revenue
    from segmented
    group by 1
    order by total_revenue desc
""")

sc_map = {
    'Champions': PRIMARY, 'Loyal': '#1E88E5',
    'Potential Loyalists': '#42A5F5', 'New Customers': '#66BB6A',
    'At Risk': '#FFA726', 'Lost': ACCENT,
}

fig, axes = plt.subplots(1, 3, figsize=(18, 5))
fig.suptitle('Customer RFM Segmentation', fontsize=16, fontweight='bold')

sns.barplot(data=df_rfm, x='customers', y='segment', hue='segment',
            palette=sc_map, ax=axes[0], legend=False)
axes[0].set_title('Customers per Segment')
axes[0].set_xlabel('Customers')
axes[0].set_ylabel('')
axes[0].xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:,.0f}'))

sns.barplot(data=df_rfm, x='total_revenue', y='segment', hue='segment',
            palette=sc_map, ax=axes[1], legend=False)
axes[1].set_title('Total Revenue per Segment')
axes[1].set_xlabel('Revenue')
axes[1].set_ylabel('')
axes[1].xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'R${x/1e6:.1f}M'))

sns.barplot(data=df_rfm, x='avg_spend', y='segment', hue='segment',
            palette=sc_map, ax=axes[2], legend=False)
axes[2].set_title('Avg Spend per Customer')
axes[2].set_xlabel('Avg Spend')
axes[2].set_ylabel('')
axes[2].xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'R${x:,.0f}'))

plt.tight_layout()
plt.show()
df_rfm

	segment	customers	avg_spend	total_revenue
0	At Risk	23572	243.83	5747589.0
1	Champions	14229	315.73	4492570.0
2	Loyal	19856	183.30	3639656.0
3	New Customers	15834	55.93	885585.0
4	Lost	14866	55.18	820361.0
5	Potential Loyalists	7738	54.68	423110.0

RFM scoring uses the dataset's maximum purchase date as "today," so recency scores reflect activity relative to mid-2018. The segmentation can't be applied forward without re-running the scores against live data.

---

10. Cohort Retention Analysis

That 3% is one flat number across every customer in the dataset, it doesn't really say anything about whether newer or older buyers behave differently. Cohort analysis breaks it down by when customers first bought, so you are able to see whether newer cohorts hold up better than older ones or whether the drop off is consistent across all of them.

The numbers were expected to be low. The more interesting question is whether retention drops off as a cliff or decays slowly, which are two different problems entirely. Plotting each cohort as a retention curve makes that shape visible directly.

What we can see is that every cohort's retention curve follows the same shape. No cohort does noticeably better than any other, and a Kruskal-Wallis test across all cohorts and months (p = 0.52) gives no statistical evidence of a difference. The average retention curve drops from around 0.5% at month 1 to roughly 0.25% by month 3, then holds relatively flat through the rest of months. The problem isn't really ongoing decay, it's the drop right after the first purchase. That consistency changes the diagnosis entirely, if some cohorts had better retention you would look at what was different about them. The fact that none do rules that out. This isn't something that got worse at a specific point, it's just how the platform retains customers.

df_cohort = q("""
    with first_orders as (
        select
            c.customer_unique_id,
            date_trunc('month', min(o.order_purchase_timestamp)) as cohort_month
        from orders o
        join customers c on c.customer_id = o.customer_id
        group by 1
    ),
    all_orders as (
        select
            c.customer_unique_id,
            date_trunc('month', o.order_purchase_timestamp) as order_month
        from orders o
        join customers c on c.customer_id = o.customer_id
    ),
    cohort_activity as (
        select
            f.cohort_month,
            ao.order_month,
            count(distinct ao.customer_unique_id) as customers,
            (
                extract(year from age(ao.order_month, f.cohort_month)) * 12
                + extract(month from age(ao.order_month, f.cohort_month))
            ) as month_number
        from first_orders f
        join all_orders ao on ao.customer_unique_id = f.customer_unique_id
        group by 1, 2, 4
    )
    select
        cohort_month,
        month_number,
        customers,
        first_value(customers) over (
            partition by cohort_month
            order by month_number
        ) as cohort_size
    from cohort_activity
    where cohort_month between '2017-01-01' and '2018-06-01'
    order by 1, 2
""")

df_cohort['retention_rate'] = (
    df_cohort['customers'] / df_cohort['cohort_size'] * 100
).round(2)

#Kruskal-Wallis test to compare retention rates across cohorts 
groups = [group['retention_rate'].values
    for _, group in df_cohort[df_cohort['month_number'] > 0].groupby('cohort_month')]

stat, p = kruskal(*groups)
print(f"Kruskal-Wallis test (retention rates across all months and cohorts)")
print(f"H = {stat:.2f},  p = {p:.4f}")
print(f"No significant difference in retention across cohorts (p = {p:.4f})")


df_curves = df_cohort[df_cohort['month_number'] > 0].copy()
df_curves['cohort_label'] = df_curves['cohort_month'].astype(str).str[:7]
avg_curve = df_curves.groupby('month_number')['retention_rate'].mean().reset_index()

fig, ax = plt.subplots(figsize=(13, 6))

for cohort in df_curves['cohort_label'].unique():
    cohort_data = df_curves[df_curves['cohort_label'] == cohort].sort_values('month_number')
    ax.plot(
        cohort_data['month_number'],
        cohort_data['retention_rate'],
        color='#bbbbbb', linewidth=1.0, alpha=0.6
    )

ax.plot(
    avg_curve['month_number'],
    avg_curve['retention_rate'],
    color=PRIMARY, linewidth=2.5, label='Average across cohorts'
)

ax.set_xlabel('Months since first purchase', fontsize=11)
ax.set_ylabel('Retention %', fontsize=11)
ax.set_title(
    'Cohort Retention Curves - % of each cohort who purchased again',
    fontsize=13, fontweight='bold'
)
ax.set_xlim(1, 12)
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'{x:.1f}%'))
ax.legend(fontsize=9)
ax.grid(axis='y', linestyle='--', alpha=0.4)
plt.tight_layout()
plt.show() 

month_0 = df_cohort[df_cohort['month_number'] == 0]['customers'].sum()
month_1 = df_cohort[df_cohort['month_number'] == 1]['customers'].sum()
print(f'Total customers across cohorts (month 0) : {month_0:,}')
print(f'Returned in month 1 : {month_1:,}'
      f'({month_1 / month_0 * 100:.1f}% overall month-1 retention)')

Kruskal-Wallis test (retention rates across all months and cohorts)
H = 16.04,  p = 0.5212
No significant difference in retention across cohorts (p = 0.5212)

Total customers across cohorts (month 0) : 83,422
Returned in month 1 : 409(0.5% overall month-1 retention)

First month retention is most sensitive metric here. Getting it from 0.5% to even 1-2% would compoud significantly over time. The focus shouldn't be winning back customers who churned months ago, but reducing that initial drop off before month 3, when the curve flattens out.

---

Key Findings

#	Finding	Business Implication
1	Revenue peaked in November 2017	Black Friday dominates the revenue story. October is effectively the start of the season, not November, and any promotional planning that ignores that is already late
2	97%+ of orders reach delivered status	The fulfilment pipeline is unusually clean for a marketplace of this scale. The problem is not getting orders delivered, it is getting customers to place a second one
3	Sao Paulo accounts for ~40% of revenue	SP is mature and saturated. MG and RJ have real order volume but lower per-order values, which makes them the obvious next markets to develop rather than pushing harder in SP
4	Mondays are the busiest shopping day	Customers are browsing on Sunday evenings and converting overnight. Sunday promotions are probably more valuable than the order data alone suggests
5	Only ~3% of customers make a repeat purchase	The platform acquires customers it cannot retain. Retention drops to 0.25% by month 3 then holds flat across every cohort, which means this is not a recent problem and it is not getting worse. It has always been this way
6	Health & Beauty is the top revenue category	H&B is a consumables category. Customers should be coming back for refills. The fact that the platform's highest-revenue category has natural repeat demand and still shows near-zero retention points directly at a re-engagement gap
7	0-3 day delivery = 4.5 stars; 22+ days = 3.0 stars	A 1.5 star gap across delivery speed buckets is not a soft correlation. Faster delivery is one of the few logistics investments with a directly measurable return in customer satisfaction
8	Lost and At Risk RFM segments represent significant churned revenue	At Risk alone is 23,572 customers and R$5.7M in revenue from people who have already bought here. Winning them back is cheaper than finding someone new and the data to identify them already exists
9	No cohort retains customers better than any other	Every cohort follows the same curve. There is no golden period where the platform retained customers well, which rules out the idea that something specific broke. Retention has been consistently low from the start

---

Limitations

It is worth being upfront about what this analysis can and can't tell you:

• No marketing data. There's no record of ad spend, campaigns, or promotions. The November 2017 spike looks like Black Friday, but it could equally reflect a specific campaign.
• No inventory or supply side data. Product availability and stockouts aren't visible. A category with lower revenue might be supply constrained rather than demand constrained.
• Repeat purchase rate is likely understated. Joining through customer_unique_id only links orders from the same account. Customers who signed up again with a different email won't be connected, so the true repeat rate is probably a bit higher.
• Seller performance is measured on delivered orders only. Sellers with high cancellation rates look cleaner in the data than they actually are, since cancelled orders have no delivery time or review score attached.
• Dataset window ends in late 2018. Brazilian e-commerce has changed significantly since this data was collected.