Back
Case study
SQL + Python

Olist E-Commerce Sales Analysis

Where is the business doing well, and where is it losing money? Here's how I answered that for a Brazilian marketplace, step by step, from messy raw data to findings the team can act on.

Role
Data Analyst
Data
~100k orders
Output
11 SQL queries
Stack
Step 01

The question

Olist is a Brazilian online marketplace that connects small sellers with buyers all over the country. I pictured myself as a data analyst there, with my manager asking “How is the business doing, and what should we fix first?”

To answer that, I used Olist's public data on Kaggle: about 100,000 real orders from 2016 to 2018, split across seven tables.

Orders99,441
Order items112,650
Payments103,886
Customer reviews99,224
Customers99,441
Products32,951
Sellers3,095
Step 02

Breaking it down

One number can't answer a question that big. So before writing any code, I broke it into six smaller questions, each one small enough to answer with a single SQL query.

Is the business growing?Revenue and orders per month
What sells?Revenue and items sold per category
Where are the customers, and does distance matter?Orders, delivery days and freight per state
Do late deliveries really hurt?Review score by how late the order arrived
Do customers come back?Number of orders per customer
Do a few sellers bring in most of the money?How much each group of sellers earns (top 10%, next 10%, …)

I used SQL (SQLite) for the analysis itself, and Python (pandas, Matplotlib) to load the data, double-check the results and draw the charts.

Step 03

Cleaning the data

Raw data is almost never ready to use. Before analyzing anything, I looked for problems that could quietly make the numbers wrong. This card from the dashboard counts what I found:

Data checks

found before analysis
  • Orders not delivered2,963
  • Repeat buyers hidden by customer ID3,345
  • Orders without any item775
  • Products without a category610
  • Orders with 2+ reviews547
  • Payment ≠ items + freight249
  • Delivered, but no delivery date8

And this is what I decided to do about each one:

2,963 orders were never delivered (shipped, canceled, unavailable…)Left out of revenue and delivery numbers
8 delivered orders have no delivery dateLeft out, since delivery time can't be calculated
775 orders have no itemsThey drop out on their own when I join the items table
547 orders have more than one reviewKept only the latest review per order
A new customer_id is created for every orderUsed customer_unique_id to recognize the same person. Otherwise, 3,345 repeat buyers disappear.
610 products have no categoryLabeled them “unknown” so they still count
249 orders where payments don't match items + freight (by more than R$1)Calculated revenue from item prices, and only used payments for the payment mix
2016 and late 2018 are incompleteMonthly trends only cover Jan 2017 to Aug 2018
What counts as “late”?Arriving after the promised date. Arriving on the day itself isn't late.

I wrote each decision once, as a SQL view, so all 11 queries use the exact same clean data. Here's the main one:

-- Only orders that really reached the customer.
-- Everything about revenue and delivery is based on this view.
CREATE VIEW delivered_orders AS
SELECT o.order_id,
       c.customer_unique_id,  -- the real person (customer_id changes per order)
       c.customer_state,
       strftime('%Y-%m', o.order_purchase_timestamp) AS purchase_month,
       julianday(o.order_delivered_customer_date)
         - julianday(o.order_purchase_timestamp)     AS delivery_days,
       -- arriving on the promised day is NOT late
       CASE WHEN date(o.order_delivered_customer_date)
                 > date(o.order_estimated_delivery_date)
            THEN 1 ELSE 0 END                        AS is_late
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL;
Step 04

Double-checking

A wrong number is worse than no number. So I calculated the main numbers a second time in pandas, straight from the raw files. If SQL and pandas didn't match, the script would stop with an error.

Delivered orders96,470 vs. 96,470 ✓
RevenueR$13,220,248.93 vs. R$13,220,248.93 ✓
Late deliveries6.80% vs. 6.77% ✓ (rounding)
Repeat customers3.00% vs. 3.00% ✓

All four matched, so I could trust the numbers in the next step.

Step 05

Reading the dashboard

The dashboard on the home page sums up the whole analysis. Below is every card from it: what it shows, how to read it, and what I learned.

The six KPI cards

RevenueR$13.2M
Down3.3%vs Jul 2018
Orders96,470
Up3.2%vs Jul 2018
Avg. order valueR$137
Down6.3%vs Jul 2018
Avg. review4.16 / 5
Stablevs Jul 2018
Avg. delivery12.6 days
Down1.3 daysvs Jul 2018
Late deliveries6.8%
Up2.8 ptsvs Jul 2018

How to read a KPI card: the big number covers the whole period (Jan 2017 to Aug 2018). The badge compares the last month with the one before (Aug vs. Jul 2018): green means better for the business, red means worse, grey means flat. The line shows the trend month by month.

Revenue · R$13.2MMoney from item prices on delivered orders. Freight isn't included.
Orders · 96,470Orders that reached the customer, from 93,350 different people.
Avg. order value · R$137Revenue divided by orders. It stayed between R$124 and R$149 every month.
Avg. review · 4.16 / 5The latest review on each delivered order, from 95,824 reviews.
Avg. delivery · 12.6 daysDays from purchase to delivery. August looks faster partly because orders still in transit aren't counted yet.
Late deliveries · 6.8%Orders that arrived after the promised date. The spikes (Nov 2017, Feb–Mar 2018) line up with the dips in review score.

Monthly revenue

Monthly revenue

BRL
R$1MR$500k0
Jan 2017Jan 2018Aug 2018
What it shows
Revenue from delivered orders each month, from January 2017 to August 2018.
How to read it
Each point on the line is one month. Higher means more sales. The dot marks the best month.
What it tells us
Revenue grew about 9× in 2017, from R$112k in January to a peak of R$988k in November. In 2018 it stayed flat at around R$826k–978k a month. The average order value barely changed, so the growth came from more orders, not bigger ones.
Why it matters
The fast growth is over, and revenue has been slowly dropping since May 2018. Future growth has to come from keeping customers and fixing the weak spots. The November spike is probably Black Friday, but I haven't checked that in the data.

Review vs. delivery

Review vs. delivery

avg. score / 5
4.3
3.3
2.1
1.7
On time1–3d late4–7d late8+d late
What it shows
The average review score (1 to 5), grouped by how late the order arrived compared with the promised date.
How to read it
Each bar is one delay group. A taller bar means happier customers.
What it tells us
Arriving just 1–3 days late drops the score from 4.29 to 3.29, and the share of 1-star reviews goes up almost 4× (6.6% → 25.1%). When an order is 8+ days late, the score falls to 1.70, and 70% of those reviews are 1-star.
Why it matters
This is the clearest pattern in the data. Only 6.8% of orders arrive late, but they cause a big part of the bad reviews. Fixing delivery is the fastest way to protect ratings.

Delivery time by state

Delivery time by state

avg. days
  • São Paulo (SP)8.8
  • Minas Gerais (MG)12.0
  • Rio de Janeiro (RJ)15.3
  • Bahia (BA)19.3
  • Pará (PA)23.8
  • Amazonas (AM)26.4
  • Roraima (RR)29.4
What it shows
Average days from purchase to delivery for a range of states, from fastest to slowest.
How to read it
A longer bar means a slower delivery.
What it tells us
São Paulo, with 42% of all orders, gets parcels in 8.8 days. Northern states wait about three times longer: Pará 23.8, Amazonas 26.4, Roraima 29.4 days. They also pay more for shipping: freight is 13.9% of revenue in São Paulo but 24–28% in some remote states.
Why it matters
Late delivery is mostly about distance. To fix it, Olist could make deals with couriers in those regions, or give customers more realistic delivery dates.

Top categories

Top categories

% of revenue
  • Health & beauty9.3%
  • Watches & gifts8.8%
  • Bed, bath & table7.7%
  • Sports & leisure7.2%
  • Computers6.7%
What it shows
The five product categories that bring in the most revenue, as a share of all revenue.
How to read it
A longer bar means a bigger share.
What it tells us
Health & beauty, watches & gifts and bed, bath & table lead, and the top ten categories make up 62% of revenue. They earn in different ways: bed, bath & table sells the most items (10,953) at R$93 each, while watches & gifts earns more from about half as many items at R$199 each.
Why it matters
Revenue alone doesn't show how a category makes money: some sell lots of cheap items, others sell fewer expensive ones. That changes where you'd invest.

Lowest-rated categories

Lowest-rated categories

avg. score / 5
  • Office furniture3.52
  • Bed, bath & table3.92
  • Furniture & decor3.95
  • Computers3.99
  • Telephony3.99
What it shows
The categories with the lowest average review score, among those with at least 500 orders.
How to read it
Every bar is out of 5. A shorter bar means unhappier customers.
What it tells us
Office furniture is the weak spot at 3.52, well below the 4.16 average. Bed, bath & table shows up here too, even though it's one of the top earners.
Why it matters
These categories are the first place to look for product or seller problems.

Revenue by seller group

Revenue by seller group

% of revenue
67%
15%
Top 10%Bottom 10%
What it shows
All 2,970 sellers, split into ten equal groups by how much they sell, and how much of the total revenue each group brings in.
How to read it
The first bar is the top 10% of sellers, the last bar is the bottom 10%.
What it tells us
The top 10% of sellers bring in 67% of revenue, and the top 20% bring in 82%. The bottom half together adds just 3.3%. One of the ten biggest sellers (R$187k in sales) also has the lowest rating among them, 3.35.
Why it matters
If a few big sellers leave, Olist loses a lot of revenue. They deserve extra support, and big sellers with bad ratings need a closer look.

Payment mix

Payment mix

% of value
  • Credit card78.5%
  • Boleto18.0%
  • Voucher2.2%
  • Debit card1.3%

Card buyers split payments into 3.5 installments on average.

What it shows
How the value of all payments splits by payment method.
How to read it
One bar split by method, with each share in the list below it.
What it tells us
Credit cards carry 78.5% of all payment value, and card buyers split payments into 3.5 installments on average. Boleto, a Brazilian bank slip, is most of the rest at 18%.
Why it matters
Paying in installments is a big part of how these customers shop, so keep it in mind for any change to pricing or checkout.

Repeat customers

Repeat customers

% of customers
  • 1 order97.0%
  • 2 orders2.8%
  • 3+ orders0.2%

Only 2,801 of 93,350 customers ordered more than once.

What it shows
Customers grouped by how many delivered orders they placed.
How to read it
One bar split by group: one order, two orders, three or more.
What it tells us
97% of customers bought only once, 2.8% bought twice, and just 0.2% bought three times or more.
Why it matters
Retention is the biggest opportunity in the data: with almost everyone buying once, even a small rise in repeat orders is worth more than chasing new customers.
Step 06

Recommendations

Based on what I found, here's what I'd fix first, in order.

Fix delivery in remote states firstMissing the promised date by just 1–3 days costs about a star. Better courier deals or more realistic delivery dates would help.
Build a retention program97% of customers buy once. Test a voucher or email right after a good delivery experience.
Look after the top sellersThe top 10% bring in 67% of revenue. Give them account managers, and review the big sellers with low ratings.
Check high freight in remote regionsFreight above 24% of revenue may put buyers off there. That's a hypothesis to test; this data can't prove it.
Look into low-rated categoriesStart with office furniture: 3.52 on average, the lowest of any large category.
Step 07

What the data can't prove

Knowing what the data can't tell us is just as important as the findings.

It only shows patternsIt shows what happens together, not what causes what. For example, late orders might also come from different products, sellers or regions.
Revenue is item price onlyFreight is analyzed separately, so revenue looks smaller than total payments.
Some data wasn't used yetI didn't use the review text (it's in Portuguese) or the location data.
The last months are incompleteTrends stop at August 2018, and August delivery times look better than they were because orders in transit aren't counted.