This project explores the Olist e-commerce dataset using SQL. It includes data modeling, business analysis, and insight generation through structured queries.
The goal is to uncover patterns in customer behavior, seller performance, product sales, and delivery logistics.
Olist is a Brazilian online marketplace.
The dataset contains detailed information on orders, customers, products, sellers, payments, and delivery logistics.
- Source: Olist Brazilian E-commerce on Kaggle
- Size: 100k+ orders, 9 datasets
- Time Span: Orders placed between 2016 and 2018
| Table | Rows | Key Columns |
|---|---|---|
| customers | 99,441 | customer_id, customer_unique_id |
| orders | 99,441 | order_id, customer_id |
| order_items | 112,650 | order_id, product_id, seller_id |
| products | 3,129 | product_id, product_category_name |
| sellers | 3,099 | seller_id |
| payments | 103,886 | order_id, payment_type |
| product_category_name | 71 | product_category_name |
| geolocation | 1,000,163 | geolocation_zip_code_prefix |
- Database Setup & Modeling: Create MySQL tables and load CSV data.
- Data Cleaning & Integration: Resolve duplicates, handle missing/invalid values, and join tables.
- Exploratory Analysis: Use SQL to explore sales, delivery performance, and customer behavior.
- Business Insights: Answer key questions that drive marketing, logistics, and product decisions.
- What is the average delivery time per customer state?
- Which product categories generate the most revenue?
- Who are the top 5 customers by total spend?
- Which sellers bring in the most revenue?
- Which states have the highest order volume and how fast are deliveries there?
- What product categories are most frequently purchased?
- What payment methods do customers prefer?
All analysis queries are stored in sql/olist_analysis.sql.
Each query includes a brief comment describing its purpose and the business insight it delivers.
Customers in Roraima (RR), Amapá (AP), and Amazonas (AM) experience the longest delivery times, averaging between 26 to 29 days. These delays suggest persistent logistical challenges in Brazil’s northern regions, likely due to distance from urban hubs and limited infrastructure.
The beleza_saude category generated the highest revenue with 9,670 units sold and R$1.26M in total sales. Other leading categories include:
- relogios_presentes: 5,991 units, R$1.21M
- cama_mesa_banho: 1,115 units, R$1.04M
- esporte_lazer: 8,641 units, R$0.99M
Despite fewer units sold, categories like cama_mesa_banho and relogios_presentes earned high revenue through premium pricing.
The top customer made a single purchase worth R$13,664. The next four spent between R$6.9K and R$7.5K across one or two orders each. These customers represent an opportunity for personalized offers or loyalty programs.
The highest-grossing seller earned R$249.6K from 1,132 orders. The next four sellers generated between R$182K and R$240K, with order counts ranging from 336 to 1,806. This shows that both volume and pricing strategy contribute to seller performance.
- São Paulo (SP): 40,495 orders with an average delivery of 8.7 days
- Rio de Janeiro (RJ): 12,353 orders, 15.2 days
- Minas Gerais (MG): 11,355 orders, 11.9 days
- Rio Grande do Sul (RS): 5,344 orders, 15.2 days
- Paraná (PR): 4,923 orders, 11.9 days
São Paulo stands out for its combination of high order volume and fast delivery, reflecting strong supply chain efficiency.
- cama_mesa_banho - 11,115 orders
- beleza_saude – 9,670 orders
- esporte_lazer – 8,641 orders
- movies_decoracao – 8,334 orders
- informatica_acessorios – 7,827 orders
- utilidades_domesticas – 6,964 orders
- relogios_presentes – 5,991 orders
These numbers suggest that consumers prioritize home essentials, personal care, and entertainment-related products.
- Credit card: 76,795 orders (73.9%)
- Boleto: 19,784 (19.0%)
- Voucher: 5,775 (5.6%)
- Debit card: 1,529 (1.5%)
This indicates that most customers rely on credit-based payments, which can inform future marketing, promotions, or installment strategies.
This SQL analysis of the Olist dataset reveals clear patterns in customer behavior, product performance, and logistical dynamics across Brazil:
- Delivery inefficiencies are most pronounced in northern states like Roraima and Amapá, indicating the need for improved infrastructure or regional fulfillment strategies.
- High-value categories and customers drive a significant portion of revenue, showing opportunities for personalized marketing and retention efforts.
- São Paulo leads in both order volume and delivery efficiency, making it a benchmark for supply chain performance in other states.
- Consumer preferences lean heavily toward credit card payments and essential or lifestyle-related products.