Data-Driven Sales Analysis — Brazilian E-Commerce (Olist)

Overview

This project is an end-to-end data analysis of the Olist Brazilian E-Commerce dataset, covering data cleaning, transformation, and visualization to uncover sales, customer, seller, and delivery performance insights for a multi-category online marketplace operating across Brazil.

The workflow follows a standard analytics pipeline:

1. Raw data ingestion — original Olist datasets (orders, customers, sellers, products, payments, reviews, geolocation).
2. Data cleaning (SQL) — duplicate removal, standardization, null/blank handling, and column-level corrections performed in PostgreSQL.
3. Data modeling & visualization (Power BI) — cleaned tables loaded into a relational data model with calculated measures (DAX) and visualized across an 11-page interactive report.

Dataset Information

The project uses the Olist Store public e-commerce dataset, which contains real (anonymized) order data from a Brazilian marketplace. Key tables include:

Table	Description
olist_orders	Order-level data — status, purchase/delivery/approval timestamps
olist_order_items	Line-item detail per order — price, freight value, shipping limit date, seller/product reference
olist_order_payments	Payment type, installments, and payment value per order
olist_order_reviews	Customer review scores and comments
olist_customers	Customer ID and location (city/state/zip)
olist_sellers	Seller ID and location (city/state/zip)
olist_products	Product category and physical attributes
olist_geolocation	Zip code to latitude/longitude mapping
category_name / brazilian_states	Supporting lookup/reference tables

Cleaning steps applied (per table, in PostgreSQL):

• Removing duplicate records
• Standardizing text fields (e.g., TRIM() on ID columns, unaccent extension for accented Portuguese text)
• Identifying and handling null or blank values (e.g., price/freight checks, missing shipping dates)
• Removing unnecessary columns
• Deriving additional fields where useful (e.g., extracting a clean shipping_date from the shipping_limit_date timestamp)

Each *_clean.sql script creates a corresponding _cleaned table using CREATE TABLE ... (LIKE ... INCLUDING ALL) so the cleaning logic is fully auditable and the raw data remains untouched.

Key Insights

The Power BI report (Brazil_Ecommerce_Analysis.pbix) is organized into the following pages, each surfacing a specific area of the business:

• Sales Overview (I, II, III) — total revenue, order volume, sales trends over time, month-over-month price growth, top product categories by revenue (treemap/donut/pie views), and a pivot-table breakdown by category/state.
• Sellers — seller performance distribution, average seller value, and the relationship between seller volume and revenue (scatter/waterfall views).
• Customers — customer distribution by state, order frequency trends, and customer retention rate.
• Orders — order trend over time and order-level detail table.
• Categories — category-wise sales trend (area chart) and a category/state pivot breakdown, including a measure for the number of unique categories sold.
• Delivery Status — order funnel by delivery status (e.g., delivered, shipped, canceled) and delivery-time related KPIs.
• Payments — payment value and volume trends by payment type/installments.
• Review Score & Price Category — relationship between product price tier and customer review scores.
• Insights — geographic (map-based) view of orders/customers/sellers across Brazil with summary KPI cards and trend line.

Core calculated metrics (DAX measures):

• Average_Order_Value — average revenue per order
• Average_Seller_Value — average revenue generated per seller
• price MoM% — month-over-month change in sales price/revenue
• customer_retention — repeat customer rate by state
• Unique Categories — count of distinct product categories sold
• Average payment value (price + freight) per product category

These metrics collectively highlight which product categories and states drive the most revenue, how seller and customer behavior trends over time, and how delivery and payment experience correlate with customer satisfaction (review scores).

Tools and Technologies Used

• PostgreSQL — data cleaning, transformation, and standardization (CREATE EXTENSION unaccent, table creation, null/duplicate handling)
• SQL — all cleaning logic written as auditable scripts per source table (customers, geolocations, order items, order payments, orders, products, sellers)
• Power BI — data modeling, DAX measures, and interactive multi-page dashboard
• Power BI Custom Visuals — Radar Chart, Tornado Chart, Timeline, Advanced Pie/Donut, Multiple Axes Chart, TBATS forecasting visual
• DAX — calculated measures for order value, seller value, retention, and growth metrics

Project Structure

├── SQL Queries/
│   ├── olist_customers_clean.sql
│   ├── olist_geolocations_clean.sql
│   ├── olist_order_items_clean.sql
│   ├── olist_order_payments_clean.sql
│   ├── olist_orders_cleaned.sql
│   ├── olist_products_both.sql
│   └── olist_sellers_clean.sql
├── cleaned/                  # Cleaned output tables/exports
├── raw/                       # Original raw Olist dataset files
└── Brazil_Ecommerce_Analysis.pbix   # Power BI report

How to Use

1. Load the raw Olist CSV files into a PostgreSQL database.
2. Run the cleaning scripts in SQL Queries/ to generate the _cleaned versions of each table.
3. Open Brazil_Ecommerce_Analysis.pbix in Power BI Desktop and refresh the data connection to point to your cleaned PostgreSQL tables.
4. Explore the report pages (Sales, Sellers, Customers, Orders, Categories, Delivery Status, Payments, Insights) to review the analysis.