E-Commerce Analysis with SQL and Power BI

Build a data pipeline with PostgreSQL, Python, and Power BI on real e-commerce data.

Introduction

30 Second Summary

Every time you order something online, you generate data. Multiply that by millions of customers, and you have a goldmine of insights waiting to be uncovered.

In this project, you will build a complete data analysis pipeline on real e-commerce data. You will spin up a database, write advanced SQL queries, create Python visualizations, and build an interactive dashboard in Power BI.

What You'll Build

A multi-tool analytics pipeline that processes 100k+ real Brazilian e-commerce orders across 9 relational tables, from raw data in PostgreSQL all the way to interactive business intelligence in Power BI.

By the end of this project, you'll have:

  • A Docker Compose setup running PostgreSQL with persistent data and 9 loaded tables.
  • Advanced SQL queries using CTEs and window functions that extract monthly revenue trends, top product categories, and delivery performance metrics.
  • A Python analysis script that connects to PostgreSQL, transforms data with pandas, and produces publication-quality charts with matplotlib.
  • An interactive Power BI dashboard connected live to your database showing sales trends, geographic breakdowns, and product performance.
  • Secret Mission: Build an RFM customer segmentation using SQL window functions and visualize the segments in Power BI.

Are there any prerequisites?

You should have basic familiarity with Python and SQL. You do not need prior experience with Docker, Power BI, or advanced SQL features like window functions.

Before We Start

Professional data analysts rarely work with a single tool. They pull data from databases, transform it with code, and present it through dashboards.

In this step, you will set up your project folder, install the Python packages you need, and download the real e-commerce dataset you will analyze throughout this project.

In this step, get ready to:
  • Create a project directory with a Python virtual environment.
  • Install Python packages for data analysis and database connectivity.
  • Download the Olist Brazilian E-Commerce dataset from Kaggle.
Create a project directory and Python virtual environment

Every data project needs a clean workspace. A virtual environment keeps your Python packages isolated from other projects on your machine.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir olist-analytics
cd olist-analytics
  • Create a data subdirectory where you will store CSV files later:
mkdir data
  • Confirm the folders exist by running:
ls

You should see data listed in the output.

  • Create a Python virtual environment by running this command:

macOS

python3 -m venv venv

Windows

python -m venv venv

What is a virtual environment?

A virtual environment is an isolated Python installation inside your project folder. Packages you install here stay separate from your system Python, preventing version conflicts across projects.

  • Activate the virtual environment:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

You should see (venv) appear at the start of your terminal prompt. This confirms you are working inside the virtual environment.

Don't see (venv) in your prompt?

On Windows, you may need to allow script execution first. Run Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned in PowerShell, then try the activate command again.

On macOS, make sure you typed source before the path. Without it, the command will not work.

Help me activate my Python virtual environment.

Install Python dependencies

Your project needs four packages: one for data manipulation, one for connecting to PostgreSQL, one for managing database connections, and one for creating charts.

  • Install all four packages by running this command:
pip install pandas==3.0.3 psycopg2-binary==2.9.12 sqlalchemy==2.0.49 matplotlib==3.10.9

What do these packages do?

  • pandas is the core data analysis library. It provides DataFrames for working with tabular data.
  • psycopg2-binary is the PostgreSQL database driver. It lets Python talk to your database.
  • sqlalchemy manages database connections and provides a consistent interface for running queries.
  • matplotlib is the charting library. You will use it to create visualizations of your analysis.

You should see output ending with Successfully installed followed by the package names. This confirms all four packages are ready.

Seeing a permission error or install failure?

Make sure (venv) appears in your terminal prompt. If it does not, you are installing into your system Python instead of the virtual environment. Activate the venv first, then retry the install command.

Help me fix my pip install error.

Download the Olist dataset from Kaggle

The Olist Brazilian E-Commerce dataset contains over 100,000 real orders from a Brazilian marketplace. It spans 9 relational CSV files covering orders, customers, products, sellers, payments, reviews, and more.

  • Open the dataset page in your browser: Brazilian E-Commerce Public Dataset by Olist.
  • Sign in to your Kaggle account if you are not already signed in.
  • Click the Download button near the top of the page. This downloads a single ZIP file containing all 9 CSV files.
  • Locate the downloaded ZIP file (usually in your Downloads folder).
  • Extract (unzip) the contents.
  • Move all 9 CSV files into the data folder inside your olist-analytics project directory.

Which 9 CSV files should I have?

  • olist_customers_dataset.csv
  • olist_geolocation_dataset.csv
  • olist_order_items_dataset.csv
  • olist_order_payments_dataset.csv
  • olist_order_reviews_dataset.csv
  • olist_orders_dataset.csv
  • olist_products_dataset.csv
  • olist_sellers_dataset.csv
  • product_category_name_translation.csv
  • Verify the files are in the right place by running this command from your olist-analytics folder:
ls data

You should see all 9 CSV files listed in the output.

Seeing fewer than 9 files?

Some operating systems create a nested folder when extracting ZIP files. Check if there is an extra subfolder inside data and move the CSV files up one level if needed.

Also confirm you downloaded from the correct dataset page. The URL should be kaggle.com/datasets/olistbr/brazilian-ecommerce.

Help me find my downloaded CSV files.

Your environment is ready and loaded with real data. Next up, you will launch a PostgreSQL database using Docker and define the schema for all 9 tables.

Launch PostgreSQL with Docker Compose

Your Python environment is ready and the Olist dataset is waiting in your project's data/ folder. Now you need a proper database to store 100k+ orders across 9 related tables.

In this step, you'll use Docker Compose to spin up a PostgreSQL database with a single command. Docker Compose captures your entire database configuration in a file, making it reproducible and shareable.

In this step, get ready to:
  • Create a Docker Compose configuration for PostgreSQL 18.
  • Define the database schema with 9 tables matching the Olist dataset.
  • Start the database container and verify connectivity.
Create the Docker Compose file

Docker Compose lets you define multi-container applications in a single YAML file. For this project, you only need one service: a PostgreSQL database.

  • In VS Code, create a new file called compose.yaml in the root of your project folder (the same level as your data/ directory).
  • Add the following configuration:
services:
  db:
    image: postgres:18
    container_name: olist-postgres
    environment:
      POSTGRES_PASSWORD: analytics123
      POSTGRES_DB: olist
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql
      - ./init-db:/docker-entrypoint-initdb.d

volumes:
  postgres_data:

What does this configuration do?

  • image: postgres:18 pulls the official PostgreSQL 18 Docker image.
  • POSTGRES_PASSWORD and POSTGRES_DB configure the database credentials and create a database named olist on first start.
  • ports: "5432:5432" maps the container's PostgreSQL port to your machine so Python and Power BI can connect via localhost.
  • postgres_data:/var/lib/postgresql creates a named volume so your data persists even if you restart the container.
  • ./init-db:/docker-entrypoint-initdb.d mounts a local folder for initialization scripts that run automatically on first start.
  • Save compose.yaml.

You won't see a change yet. The container won't start until you run it in substep 3.

Why Docker Compose instead of a plain install?

Installing PostgreSQL directly on your machine requires OS-specific configuration and can conflict with other versions. Docker Compose captures the entire setup in one file.

Anyone with Docker can run your project with a single command. This is how professional data teams share reproducible database environments.

Define the database schema

PostgreSQL needs to know the structure of your tables before you load data. You'll create a SQL script that defines all 9 tables matching the Olist CSV files.

  • Create a new folder called init-db inside your project directory.
  • Inside init-db/, create a file called 01_schema.sql.
  • Add the following SQL to define all 9 tables:
-- Olist E-Commerce Database Schema

CREATE TABLE IF NOT EXISTS customers (
    customer_id VARCHAR(50) PRIMARY KEY,
    customer_unique_id VARCHAR(50),
    customer_zip_code_prefix VARCHAR(10),
    customer_city VARCHAR(100),
    customer_state VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS geolocation (
    geolocation_zip_code_prefix VARCHAR(10),
    geolocation_lat DOUBLE PRECISION,
    geolocation_lng DOUBLE PRECISION,
    geolocation_city VARCHAR(100),
    geolocation_state VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS sellers (
    seller_id VARCHAR(50) PRIMARY KEY,
    seller_zip_code_prefix VARCHAR(10),
    seller_city VARCHAR(100),
    seller_state VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS products (
    product_id VARCHAR(50) PRIMARY KEY,
    product_category_name VARCHAR(100),
    product_name_length NUMERIC,
    product_description_length NUMERIC,
    product_photos_qty NUMERIC,
    product_weight_g NUMERIC,
    product_length_cm NUMERIC,
    product_height_cm NUMERIC,
    product_width_cm NUMERIC
);

CREATE TABLE IF NOT EXISTS orders (
    order_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    order_status VARCHAR(20),
    order_purchase_timestamp TIMESTAMP,
    order_approved_at TIMESTAMP,
    order_delivered_carrier_date TIMESTAMP,
    order_delivered_customer_date TIMESTAMP,
    order_estimated_delivery_date TIMESTAMP
);

CREATE TABLE IF NOT EXISTS order_items (
    order_id VARCHAR(50),
    order_item_id INTEGER,
    product_id VARCHAR(50),
    seller_id VARCHAR(50),
    shipping_limit_date TIMESTAMP,
    price NUMERIC(10,2),
    freight_value NUMERIC(10,2),
    PRIMARY KEY (order_id, order_item_id)
);

CREATE TABLE IF NOT EXISTS order_payments (
    order_id VARCHAR(50),
    payment_sequential INTEGER,
    payment_type VARCHAR(30),
    payment_installments INTEGER,
    payment_value NUMERIC(10,2)
);

CREATE TABLE IF NOT EXISTS order_reviews (
    review_id VARCHAR(50),
    order_id VARCHAR(50),
    review_score INTEGER,
    review_comment_title TEXT,
    review_comment_message TEXT,
    review_creation_date TIMESTAMP,
    review_answer_timestamp TIMESTAMP
);

CREATE TABLE IF NOT EXISTS product_category_name_translation (
    product_category_name VARCHAR(100) PRIMARY KEY,
    product_category_name_english VARCHAR(100)
);

What does this schema define?

The schema defines three categories of tables that model the Olist e-commerce marketplace.

  • Entity tables (customers, sellers, products, geolocation) describe the people and things in the marketplace.
  • Transaction tables (orders, order_items, order_payments) capture what happened in each purchase.
  • Supporting tables (order_reviews, product_category_name_translation) add feedback and language translations.

The geolocation table has no primary key because the original dataset contains multiple coordinates per zip code prefix.

Why is the file named 01_schema.sql?

Scripts in /docker-entrypoint-initdb.d/ execute in alphabetical order on first container start. The 01_ prefix ensures this schema script runs first if you add more init scripts later.

  • Save init-db/01_schema.sql.

You won't see a change yet. These tables will be created automatically when the container starts for the first time.

✔️ Awesome, I've got everything!

Great. Double check you've saved both files before moving on.

ⓧ I'd like to double check the full code

Here is the full compose.yaml:

services:
  db:
    image: postgres:18
    container_name: olist-postgres
    environment:
      POSTGRES_PASSWORD: analytics123
      POSTGRES_DB: olist
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql
      - ./init-db:/docker-entrypoint-initdb.d

volumes:
  postgres_data:

And here is the full init-db/01_schema.sql:

-- Olist E-Commerce Database Schema

CREATE TABLE IF NOT EXISTS customers (
    customer_id VARCHAR(50) PRIMARY KEY,
    customer_unique_id VARCHAR(50),
    customer_zip_code_prefix VARCHAR(10),
    customer_city VARCHAR(100),
    customer_state VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS geolocation (
    geolocation_zip_code_prefix VARCHAR(10),
    geolocation_lat DOUBLE PRECISION,
    geolocation_lng DOUBLE PRECISION,
    geolocation_city VARCHAR(100),
    geolocation_state VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS sellers (
    seller_id VARCHAR(50) PRIMARY KEY,
    seller_zip_code_prefix VARCHAR(10),
    seller_city VARCHAR(100),
    seller_state VARCHAR(5)
);

CREATE TABLE IF NOT EXISTS products (
    product_id VARCHAR(50) PRIMARY KEY,
    product_category_name VARCHAR(100),
    product_name_length NUMERIC,
    product_description_length NUMERIC,
    product_photos_qty NUMERIC,
    product_weight_g NUMERIC,
    product_length_cm NUMERIC,
    product_height_cm NUMERIC,
    product_width_cm NUMERIC
);

CREATE TABLE IF NOT EXISTS orders (
    order_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    order_status VARCHAR(20),
    order_purchase_timestamp TIMESTAMP,
    order_approved_at TIMESTAMP,
    order_delivered_carrier_date TIMESTAMP,
    order_delivered_customer_date TIMESTAMP,
    order_estimated_delivery_date TIMESTAMP
);

CREATE TABLE IF NOT EXISTS order_items (
    order_id VARCHAR(50),
    order_item_id INTEGER,
    product_id VARCHAR(50),
    seller_id VARCHAR(50),
    shipping_limit_date TIMESTAMP,
    price NUMERIC(10,2),
    freight_value NUMERIC(10,2),
    PRIMARY KEY (order_id, order_item_id)
);

CREATE TABLE IF NOT EXISTS order_payments (
    order_id VARCHAR(50),
    payment_sequential INTEGER,
    payment_type VARCHAR(30),
    payment_installments INTEGER,
    payment_value NUMERIC(10,2)
);

CREATE TABLE IF NOT EXISTS order_reviews (
    review_id VARCHAR(50),
    order_id VARCHAR(50),
    review_score INTEGER,
    review_comment_title TEXT,
    review_comment_message TEXT,
    review_creation_date TIMESTAMP,
    review_answer_timestamp TIMESTAMP
);

CREATE TABLE IF NOT EXISTS product_category_name_translation (
    product_category_name VARCHAR(100) PRIMARY KEY,
    product_category_name_english VARCHAR(100)
);
Start the database and verify connectivity

With both files saved, you can now launch the PostgreSQL container. Docker will pull the image, create the database, and run your schema script automatically.

  • Make sure Docker Desktop is running on your machine.
  • In your VS Code terminal, start the container by running this command:
docker compose up -d

What does this command do?

docker compose up reads your compose.yaml and starts all defined services. The -d flag runs them in detached mode (in the background), so your terminal stays free.

  • Wait a few seconds for the container to initialize, then verify it is running:
docker compose ps
  • You should see olist-postgres listed with a status of running.
  • Verify the schema was created by running this command:
docker exec olist-postgres psql -U postgres -d olist -c '\dt'
  • You should see a list of 9 tables (customers, geolocation, orders, order_items, order_payments, order_reviews, products, product_category_name_translation, sellers) in the output.

Container not starting?

  • Make sure Docker Desktop is running. You should see the Docker whale icon in your system tray or menu bar.
  • If you see a port conflict error, another service is using port 5432. Stop the conflicting service or change the port mapping in compose.yaml to "5433:5432".
  • If you see the container but no tables in the \dt output, the init script may not have run. Run docker compose down -v to remove the container and volume, then run docker compose up -d again.

Help me troubleshoot my Docker Compose PostgreSQL container

Your PostgreSQL database is live and structured. Next up, you'll write a Python script to load all 9 CSV files into these empty tables.

Load Data into PostgreSQL

Your PostgreSQL database is running in Docker with 9 empty tables ready to accept data. The next step is getting your Olist e-commerce CSV files into those tables so you can query them with SQL.

You'll write a Python script that uses pandas to read CSV files and SQLAlchemy to connect to PostgreSQL. This automates the loading process so you can reload data anytime with a single command.

In this step, get ready to:
  • Write a Python script that loads all 9 CSV files into PostgreSQL.
  • Run the script and verify that data loaded correctly.
Write the data loading script

The loading script needs to connect to PostgreSQL, loop through all 9 CSV files, and insert each into its matching table. You'll build it in three chunks: configuration, loading logic, and verification.

  • Create a new file called load_data.py in the same folder as your compose.yaml.
  • Add the imports, database connection, and CSV-to-table mapping by pasting this code:
import pandas as pd
from sqlalchemy import create_engine
from pathlib import Path

# Database connection
DATABASE_URL = "postgresql+psycopg2://postgres:analytics123@localhost:5432/olist"
engine = create_engine(DATABASE_URL)

# Mapping of CSV files to table names
CSV_TABLE_MAP = {
    "olist_customers_dataset.csv": "customers",
    "olist_geolocation_dataset.csv": "geolocation",
    "olist_sellers_dataset.csv": "sellers",
    "olist_products_dataset.csv": "products",
    "olist_orders_dataset.csv": "orders",
    "olist_order_items_dataset.csv": "order_items",
    "olist_order_payments_dataset.csv": "order_payments",
    "olist_order_reviews_dataset.csv": "order_reviews",
    "product_category_name_translation.csv": "product_category_name_translation",
}

DATA_DIR = Path("data")

What does this code do?

  • DATABASE_URL tells SQLAlchemy how to connect: the driver (psycopg2), credentials, host, port, and database name.
  • CSV_TABLE_MAP pairs each CSV filename with the PostgreSQL table it should load into.
  • DATA_DIR points to the data/ folder where your downloaded CSV files live.

You won't see a change yet. The next chunk adds the function that uses this configuration.

  • Add the function that reads each CSV and loads it into PostgreSQL by pasting this code below what you just wrote:
def load_all_data():
    """Load all CSV files into PostgreSQL tables."""
    for csv_file, table_name in CSV_TABLE_MAP.items():
        file_path = DATA_DIR / csv_file
        if not file_path.exists():
            print(f"WARNING: {file_path} not found, skipping.")
            continue

        print(f"Loading {csv_file} into {table_name}...")
        df = pd.read_csv(file_path)
        df.to_sql(
            name=table_name,
            con=engine,
            if_exists="append",
            index=False,
            method="multi",
            chunksize=5000,
        )
        print(f"  Loaded {len(df)} rows into {table_name}")

What does this function do?

  • The function loops through CSV_TABLE_MAP and reads each CSV into a pandas DataFrame.
  • df.to_sql() writes the DataFrame directly to the matching PostgreSQL table.
  • if_exists="append" adds rows to the existing schema-defined tables without dropping them.
  • chunksize=5000 splits large files into batches so memory stays manageable.

You won't see a change yet. The next chunk adds verification and the entry point.

  • Add the verification function and entry point by pasting this code at the bottom of load_data.py:
def verify_data():
    """Verify row counts for all tables."""
    print("\nTable row counts:")
    print("-" * 40)
    for table_name in CSV_TABLE_MAP.values():
        count = pd.read_sql(f"SELECT COUNT(*) as cnt FROM {table_name}", engine)
        print(f"  {table_name}: {count['cnt'].iloc[0]:,} rows")


if __name__ == "__main__":
    load_all_data()
    verify_data()

What does this code do?

  • verify_data() queries each table's row count to confirm data loaded correctly.
  • if __name__ == "__main__": ensures the script runs both functions when you execute it directly from the terminal.
  • Save load_data.py.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete load_data.py file for comparison:

import pandas as pd
from sqlalchemy import create_engine
from pathlib import Path

# Database connection
DATABASE_URL = "postgresql+psycopg2://postgres:analytics123@localhost:5432/olist"
engine = create_engine(DATABASE_URL)

# Mapping of CSV files to table names
CSV_TABLE_MAP = {
    "olist_customers_dataset.csv": "customers",
    "olist_geolocation_dataset.csv": "geolocation",
    "olist_sellers_dataset.csv": "sellers",
    "olist_products_dataset.csv": "products",
    "olist_orders_dataset.csv": "orders",
    "olist_order_items_dataset.csv": "order_items",
    "olist_order_payments_dataset.csv": "order_payments",
    "olist_order_reviews_dataset.csv": "order_reviews",
    "product_category_name_translation.csv": "product_category_name_translation",
}

DATA_DIR = Path("data")


def load_all_data():
    """Load all CSV files into PostgreSQL tables."""
    for csv_file, table_name in CSV_TABLE_MAP.items():
        file_path = DATA_DIR / csv_file
        if not file_path.exists():
            print(f"WARNING: {file_path} not found, skipping.")
            continue

        print(f"Loading {csv_file} into {table_name}...")
        df = pd.read_csv(file_path)
        df.to_sql(
            name=table_name,
            con=engine,
            if_exists="append",
            index=False,
            method="multi",
            chunksize=5000,
        )
        print(f"  Loaded {len(df)} rows into {table_name}")

    print("\nAll data loaded successfully!")


def verify_data():
    """Verify row counts for all tables."""
    print("\nTable row counts:")
    print("-" * 40)
    for table_name in CSV_TABLE_MAP.values():
        count = pd.read_sql(f"SELECT COUNT(*) as cnt FROM {table_name}", engine)
        print(f"  {table_name}: {count['cnt'].iloc[0]:,} rows")


if __name__ == "__main__":
    load_all_data()
    verify_data()
Run the script and verify row counts

With the script written, it is time to load all 100,000+ orders into your database. This will take 30-60 seconds depending on your machine.

  • Run the data loading script by executing this command in your terminal:
python load_data.py

You should see each CSV file being loaded with its name and row count printed. At the end, a summary table shows the total rows in each PostgreSQL table.

What should I see?

The output starts with lines like Loading olist_customers_dataset.csv into customers... followed by row counts for each file.

After all files load, the verification section prints a table of all 9 table names with their row counts. The orders table should have around 99,000+ rows.

Seeing a connection refused error?

  • Make sure your Docker container is still running. Run docker compose ps to check the status of olist-postgres.
  • If the container stopped, start it again with docker compose up -d and wait a few seconds before retrying.
  • If you see a "duplicate key" error, it means you ran the script before and the data already exists. You can drop and recreate tables by running docker compose down -v followed by docker compose up -d, then run the script again.

Help me debug my load_data.py script

Your database is loaded with over 100,000 real e-commerce orders across 9 tables. Next up, you'll write advanced SQL queries to extract business insights from this data.

Write SQL Analysis Queries

Your database is loaded with 100k+ real e-commerce orders across 9 relational tables. Now it is time to extract business insights from that data using advanced SQL.

In this step, you will write three production-style queries that answer real business questions: How is revenue trending month-over-month? Which product categories drive the most sales? How well is the company delivering on time? Each query uses techniques that professional analysts rely on daily.

In this step, get ready to:
  • Write a CTE-based query with LAG() to calculate monthly revenue trends and month-over-month growth.
  • Write a query using RANK() to find the top product categories by revenue.
  • Write a query analyzing delivery performance by state using date arithmetic.
Write the monthly revenue query

A Common Table Expression (CTE) lets you define a temporary named result set that you reference in the main query. Combined with the LAG() window function, you can compare each month's revenue against the previous month to calculate growth.

  • Create a new file called analysis.sql in your project root folder (the same level as compose.yaml).
  • Add the following query to the file:
-- ============================================
-- QUERY 1: Monthly Revenue with MoM Growth
-- ============================================
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
        SUM(oi.price + oi.freight_value) AS revenue,
        COUNT(DISTINCT o.order_id) AS total_orders
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
)
SELECT
    month,
    revenue,
    total_orders,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 1
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

What does this query do?

  • The WITH monthly_revenue AS (...) block is a CTE that first groups all delivered orders by month and sums up revenue (price + freight).
  • LAG(revenue) OVER (ORDER BY month) is a window function that looks at the previous row's revenue value without collapsing rows.
  • The mom_growth_pct column calculates the percentage change from last month's revenue to this month's revenue.
  • Save analysis.sql.
  • Test Query 1 by piping the file into PostgreSQL inside your Docker container. Run this command:
docker exec -i olist-postgres psql -U postgres -d olist < analysis.sql

You should see a table of rows showing month, revenue, total_orders, prev_month_revenue, and mom_growth_pct columns. The first row's prev_month_revenue and mom_growth_pct will be NULL because there is no previous month for the earliest entry.

Seeing a connection error?

  • Make sure your Docker container is still running. Run docker ps and confirm you see olist-postgres in the list.
  • If the container stopped, restart it with docker compose up -d from your project folder.
  • Check that analysis.sql is in the same directory where you are running the command. Use ls to confirm the file is there.

Help me troubleshoot this connection error

Write the top categories query

The RANK() window function assigns a ranking position to each row based on a specified ordering. This query also uses COALESCE() to prefer the English category name translation and fall back to the original Portuguese name.

  • In analysis.sql, add the following query below Query 1:
-- ============================================
-- QUERY 2: Top Categories by Revenue with Rank
-- ============================================
WITH category_revenue AS (
    SELECT
        COALESCE(t.product_category_name_english, p.product_category_name) AS category,
        SUM(oi.price) AS total_revenue,
        COUNT(DISTINCT oi.order_id) AS order_count,
        AVG(oi.price) AS avg_price
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    LEFT JOIN product_category_name_translation t
        ON p.product_category_name = t.product_category_name
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
)
SELECT
    category,
    total_revenue,
    order_count,
    ROUND(avg_price, 2) AS avg_price,
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM category_revenue
ORDER BY revenue_rank
LIMIT 15;

What does this query do?

  • The CTE joins order_items, products, and the translation table to group revenue by product category.
  • LEFT JOIN on the translation table means categories without an English name still appear (they just show the Portuguese original).
  • RANK() OVER (ORDER BY total_revenue DESC) assigns position 1 to the highest-revenue category, 2 to the next, and so on.
  • LIMIT 15 keeps the output focused on the top performers.
  • Save analysis.sql and run the file again:
docker exec -i olist-postgres psql -U postgres -d olist < analysis.sql

You should now see two result sets. The second one shows 15 rows with category names, total_revenue, order_count, avg_price, and revenue_rank columns.

Seeing NULL category names?

  • Some products in the dataset have no category assigned. This is expected. The COALESCE function handles missing translations, but a NULL original category name will still appear as NULL.
  • If you see zero rows returned, check that your JOIN conditions match exactly. A typo in a table alias (oi, p, t, o) will cause the query to fail.

Help me debug my top categories query

Write the delivery performance query

This query uses date arithmetic to measure how long deliveries actually take compared to the estimated delivery date. The EXTRACT(DAY FROM ...) function pulls the number of days from the difference between two timestamps.

  • In analysis.sql, add the following query below Query 2:
-- ============================================
-- QUERY 3: Delivery Performance by State
-- ============================================
SELECT
    c.customer_state,
    COUNT(*) AS delivered_orders,
    ROUND(AVG(
        EXTRACT(DAY FROM (o.order_delivered_customer_date - o.order_purchase_timestamp))
    ), 1) AS avg_delivery_days,
    ROUND(AVG(
        EXTRACT(DAY FROM (o.order_estimated_delivery_date - o.order_delivered_customer_date))
    ), 1) AS avg_days_early,
    ROUND(
        SUM(CASE WHEN o.order_delivered_customer_date > o.order_estimated_delivery_date THEN 1 ELSE 0 END)::NUMERIC
        / COUNT(*) * 100, 1
    ) AS late_delivery_pct
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL
GROUP BY c.customer_state
HAVING COUNT(*) > 100
ORDER BY avg_delivery_days;

What does this query do?

  • avg_delivery_days calculates the average number of days between purchase and delivery for each state.
  • avg_days_early shows how many days ahead of the estimate orders arrive on average. A positive number means deliveries beat expectations.
  • late_delivery_pct uses a CASE expression to count late deliveries, then divides by total to get a percentage.
  • HAVING COUNT(*) > 100 filters out states with too few orders to produce meaningful averages.
  • Save analysis.sql and run the full file one more time:
docker exec -i olist-postgres psql -U postgres -d olist < analysis.sql

You should now see three result sets. The third shows Brazilian states ranked by average delivery time, with late delivery percentages for each.

Query returning zero rows?

  • Double check the WHERE clause includes both o.order_status = 'delivered' and o.order_delivered_customer_date IS NOT NULL. Missing the second condition can cause errors with NULL date arithmetic.
  • If you see a syntax error near ::NUMERIC, make sure there are no extra spaces between the END keyword and )::NUMERIC.

Help me debug my delivery performance query

✔️ Awesome, I've got everything!

Great. Make sure you have saved analysis.sql and all three queries return results without errors.

ⓧ I'd like to double check the full code

Here is the complete analysis.sql file with all three queries:

-- ============================================
-- QUERY 1: Monthly Revenue with MoM Growth
-- ============================================
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
        SUM(oi.price + oi.freight_value) AS revenue,
        COUNT(DISTINCT o.order_id) AS total_orders
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
)
SELECT
    month,
    revenue,
    total_orders,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 1
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

-- ============================================
-- QUERY 2: Top Categories by Revenue with Rank
-- ============================================
WITH category_revenue AS (
    SELECT
        COALESCE(t.product_category_name_english, p.product_category_name) AS category,
        SUM(oi.price) AS total_revenue,
        COUNT(DISTINCT oi.order_id) AS order_count,
        AVG(oi.price) AS avg_price
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    LEFT JOIN product_category_name_translation t
        ON p.product_category_name = t.product_category_name
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
)
SELECT
    category,
    total_revenue,
    order_count,
    ROUND(avg_price, 2) AS avg_price,
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM category_revenue
ORDER BY revenue_rank
LIMIT 15;

-- ============================================
-- QUERY 3: Delivery Performance by State
-- ============================================
SELECT
    c.customer_state,
    COUNT(*) AS delivered_orders,
    ROUND(AVG(
        EXTRACT(DAY FROM (o.order_delivered_customer_date - o.order_purchase_timestamp))
    ), 1) AS avg_delivery_days,
    ROUND(AVG(
        EXTRACT(DAY FROM (o.order_estimated_delivery_date - o.order_delivered_customer_date))
    ), 1) AS avg_days_early,
    ROUND(
        SUM(CASE WHEN o.order_delivered_customer_date > o.order_estimated_delivery_date THEN 1 ELSE 0 END)::NUMERIC
        / COUNT(*) * 100, 1
    ) AS late_delivery_pct
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL
GROUP BY c.customer_state
HAVING COUNT(*) > 100
ORDER BY avg_delivery_days;

You now have three advanced SQL queries extracting real business insights from your e-commerce data. Next up, you will connect Python to this same database and build visualizations from these results.

Analyze Data with Python and pandas

Your SQL queries are working and returning insights from PostgreSQL. Now it's time to bring Python into the picture.

In this step, you'll write a Python script that connects directly to your database, pulls data using pandas, and produces publication-quality charts with matplotlib. This is how professional data analysts automate their analysis workflows.

In this step, get ready to:
  • Connect to PostgreSQL from Python using SQLAlchemy and pull data into DataFrames.
  • Create matplotlib visualizations (line chart, bar chart, pie chart) and save them as PNG files.
Connect to PostgreSQL and analyze monthly revenue

SQLAlchemy provides a connection bridge between Python and your PostgreSQL database. Combined with pd.read_sql(), you can run SQL queries and get results back as pandas DataFrames ready for analysis.

  • Create a new file called analysis.py in your project folder (the same folder where load_data.py lives).
  • Add the imports and database connection by pasting this code:
import pandas as pd
import matplotlib.pyplot as plt
from sqlalchemy import create_engine

# Database connection
DATABASE_URL = "postgresql+psycopg2://postgres:analytics123@localhost:5432/olist"
engine = create_engine(DATABASE_URL)

What does this code do?

The DATABASE_URL string tells SQLAlchemy how to connect: which driver to use (psycopg2), the username, password, host, port, and database name.

create_engine() creates a reusable connection pool. Every query you run later uses this same engine object.

You won't see a change yet. The next chunk adds the first analysis that uses this connection.

  • Add the monthly revenue analysis below the engine setup in analysis.py:
# --- Analysis 1: Monthly Revenue Trend ---
query_revenue = """
SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
    SUM(oi.price + oi.freight_value) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
ORDER BY month
"""

df_revenue = pd.read_sql(query_revenue, engine)
df_revenue["month"] = pd.to_datetime(df_revenue["month"])

plt.figure(figsize=(12, 5))
plt.plot(df_revenue["month"], df_revenue["revenue"], marker="o", linewidth=2)
plt.title("Monthly Revenue Trend (Delivered Orders)", fontsize=14)
plt.xlabel("Month")
plt.ylabel("Revenue (BRL)")
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig("revenue_trend.png", dpi=150)
plt.close()
print("Saved: revenue_trend.png")

What does this code do?

  • query_revenue holds a SQL query that groups delivered orders by month and sums revenue (price + freight).
  • pd.read_sql() sends the query to PostgreSQL and returns the result as a DataFrame.
  • pd.to_datetime() converts the month column so matplotlib can plot it on a time axis.
  • plt.savefig() saves the chart as a PNG file at 150 DPI resolution. plt.close() frees memory so the next chart starts fresh.
  • Save analysis.py.
  • Run the script to test your first analysis by running this command:
python analysis.py
  • You should see Saved: revenue_trend.png printed in your terminal.
  • Confirm the file was created by running:
ls revenue_trend.png
  • You should see revenue_trend.png listed. Open it in your file explorer to see the monthly revenue line chart.

Seeing a connection error?

  • Make sure your Docker container is still running. Run docker compose up -d to restart it if needed.
  • Check that your virtual environment is activated. You should see (venv) at the start of your terminal prompt.
  • Verify the password in DATABASE_URL matches analytics123 exactly.

Help me debug my SQLAlchemy connection to PostgreSQL in Docker

Visualize categories and payment methods

Now that the pattern is established (SQL query, pd.read_sql(), matplotlib chart, save), you'll add two more analyses to the same script.

  • Add the top categories analysis below your revenue chart code in analysis.py:
# --- Analysis 2: Top 10 Product Categories ---
query_categories = """
SELECT
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    SUM(oi.price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'delivered'
GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
ORDER BY total_revenue DESC
LIMIT 10
"""

df_categories = pd.read_sql(query_categories, engine)

plt.figure(figsize=(10, 6))
plt.barh(df_categories["category"], df_categories["total_revenue"], color="steelblue")
plt.xlabel("Total Revenue (BRL)")
plt.title("Top 10 Product Categories by Revenue", fontsize=14)
plt.gca().invert_yaxis()
plt.tight_layout()
plt.savefig("top_categories.png", dpi=150)
plt.close()
print("Saved: top_categories.png")

What does this code do?

  • The query joins order_items, products, and the translation table to get English category names. COALESCE falls back to the Portuguese name if no translation exists.
  • plt.barh() creates a horizontal bar chart. invert_yaxis() puts the highest-revenue category at the top.

You won't see the new chart until you run the script again. The next chunk adds the final analysis.

  • Add the payment method analysis below the categories code in analysis.py:
# --- Analysis 3: Payment Method Distribution ---
query_payments = """
SELECT
    payment_type,
    COUNT(*) AS transaction_count,
    SUM(payment_value) AS total_value
FROM order_payments
GROUP BY payment_type
ORDER BY total_value DESC
"""

df_payments = pd.read_sql(query_payments, engine)

plt.figure(figsize=(8, 8))
plt.pie(
    df_payments["total_value"],
    labels=df_payments["payment_type"],
    autopct="%1.1f%%",
    startangle=90,
)
plt.title("Payment Method Distribution by Value", fontsize=14)
plt.tight_layout()
plt.savefig("payment_distribution.png", dpi=150)
plt.close()
print("Saved: payment_distribution.png")

print("\nAnalysis complete! Check the generated PNG files.")

What does this code do?

  • This query groups the order_payments table by payment_type and sums the total value for each method.
  • plt.pie() creates a pie chart showing each payment method's share of total revenue. autopct displays percentages on each slice.
  • Save analysis.py.
  • Run the complete script by running this command:
python analysis.py
  • You should see all three lines printed in your terminal:
Saved: revenue_trend.png
Saved: top_categories.png
Saved: payment_distribution.png

Analysis complete! Check the generated PNG files.
  • Confirm all three PNG files exist by running:
ls *.png
  • You should see payment_distribution.png, revenue_trend.png, and top_categories.png listed.

Seeing a ModuleNotFoundError?

  • Make sure your virtual environment is activated. You should see (venv) at the start of your terminal prompt.
  • If you see an error about matplotlib or sqlalchemy not being found, re-install them with pip install pandas psycopg2-binary sqlalchemy matplotlib.

Help me fix my Python import errors when running analysis.py

✔️ Awesome, I've got everything!

Great. Double check you've saved analysis.py and all three PNG files are in your project folder.

ⓧ I'd like to double check the full code

Here is the complete analysis.py file for comparison:

import pandas as pd
import matplotlib.pyplot as plt
from sqlalchemy import create_engine

# Database connection
DATABASE_URL = "postgresql+psycopg2://postgres:analytics123@localhost:5432/olist"
engine = create_engine(DATABASE_URL)

# --- Analysis 1: Monthly Revenue Trend ---
query_revenue = """
SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
    SUM(oi.price + oi.freight_value) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
ORDER BY month
"""

df_revenue = pd.read_sql(query_revenue, engine)
df_revenue["month"] = pd.to_datetime(df_revenue["month"])

plt.figure(figsize=(12, 5))
plt.plot(df_revenue["month"], df_revenue["revenue"], marker="o", linewidth=2)
plt.title("Monthly Revenue Trend (Delivered Orders)", fontsize=14)
plt.xlabel("Month")
plt.ylabel("Revenue (BRL)")
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig("revenue_trend.png", dpi=150)
plt.close()
print("Saved: revenue_trend.png")

# --- Analysis 2: Top 10 Product Categories ---
query_categories = """
SELECT
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    SUM(oi.price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status = 'delivered'
GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
ORDER BY total_revenue DESC
LIMIT 10
"""

df_categories = pd.read_sql(query_categories, engine)

plt.figure(figsize=(10, 6))
plt.barh(df_categories["category"], df_categories["total_revenue"], color="steelblue")
plt.xlabel("Total Revenue (BRL)")
plt.title("Top 10 Product Categories by Revenue", fontsize=14)
plt.gca().invert_yaxis()
plt.tight_layout()
plt.savefig("top_categories.png", dpi=150)
plt.close()
print("Saved: top_categories.png")

# --- Analysis 3: Payment Method Distribution ---
query_payments = """
SELECT
    payment_type,
    COUNT(*) AS transaction_count,
    SUM(payment_value) AS total_value
FROM order_payments
GROUP BY payment_type
ORDER BY total_value DESC
"""

df_payments = pd.read_sql(query_payments, engine)

plt.figure(figsize=(8, 8))
plt.pie(
    df_payments["total_value"],
    labels=df_payments["payment_type"],
    autopct="%1.1f%%",
    startangle=90,
)
plt.title("Payment Method Distribution by Value", fontsize=14)
plt.tight_layout()
plt.savefig("payment_distribution.png", dpi=150)
plt.close()
print("Saved: payment_distribution.png")

print("\nAnalysis complete! Check the generated PNG files.")

Your Python analysis script is complete and producing real charts from live database data. Next up, you'll connect Power BI directly to your PostgreSQL database and build an interactive dashboard.

Build the Power BI Dashboard

Your Python analysis produced static chart images. These are great for reports, but they can't answer follow-up questions like "which state drives the most revenue?" or "what happened in March specifically?"

In this step, you'll connect Power BI Desktop directly to your PostgreSQL database. This gives you an interactive dashboard where you can filter, drill down, and explore the data in real time.

In this step, get ready to:
  • Connect Power BI Desktop to your PostgreSQL database and import tables.
  • Set up relationships and create DAX measures for revenue metrics.
  • Build a dashboard with a line chart, map, bar chart, and summary card.
Connect Power BI to PostgreSQL

Power BI Desktop has a built-in PostgreSQL connector. This means you can pull data directly from your running Docker database without exporting CSVs or copying files.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • Once Power BI Desktop opens, click Get Data in the Home ribbon.
  • In the search box, type PostgreSQL.
  • Select PostgreSQL database from the results, then click Connect.

A connection dialog appears asking for your server and database details.

  • In the Server field, enter localhost:5432.
  • In the Database field, enter olist.
  • Select Import as the data connectivity mode.
  • Click OK.

Import vs DirectQuery

Import mode loads the data into Power BI's in-memory engine for fast interaction. DirectQuery sends queries live to the database each time you interact with a visual.

For this project, Import mode is the right choice. The dataset is small enough to fit in memory, and you get instant responses when filtering your dashboard.

Power BI now asks you to authenticate with the database.

  • Select the Database tab on the left side of the credentials dialog.
  • In the User name field, enter postgres.
  • In the Password field, enter analytics123.
  • Click Connect.

Seeing an encryption warning?

Power BI may show a message about the connection not being encrypted. This is fine for a local development database. Click OK to proceed.

The Navigator window appears, showing all the tables in your database.

  • Expand the olist database, then expand public to see your tables.
  • Select the checkboxes next to these 5 tables: orders, order_items, customers, products, order_payments.
  • Click Load.

Power BI imports the data. This may take a minute since the orders table has 100k+ rows.

Connection failed?

Make sure your Docker container is still running. Switch back to your terminal and run:

docker compose up -d

Also confirm you entered localhost:5432 as the server (not just localhost) and olist as the database name.

Help me troubleshoot my Power BI PostgreSQL connection.

Set up the data model and DAX measures

Power BI needs to know how your tables relate to each other. Without relationships, it can't correctly aggregate revenue across customers or products.

  • Click the Model view icon in the left sidebar (it looks like a diagram with connected boxes).

Power BI may have auto-detected some relationships. You need to verify these 4 relationships exist and create any that are missing.

  • Click Manage relationships in the Home ribbon.
  • Verify or create: orders[order_id] to order_items[order_id] (one-to-many).
  • Verify or create: orders[customer_id] to customers[customer_id] (many-to-one).
  • Verify or create: order_items[product_id] to products[product_id] (many-to-one).
  • Verify or create: orders[order_id] to order_payments[order_id] (one-to-many).
  • Click Close when all 4 relationships are confirmed.

How do I create a missing relationship?

In the Manage relationships dialog, click New. Select the first table and column, then the second table and column. Power BI auto-detects the cardinality (one-to-many). Click OK to save.

Now you'll create two DAX measures. DAX is Power BI's formula language for creating calculated metrics that respond to filters and slicers.

  • Switch to Report view by clicking the chart icon in the left sidebar.
  • In the Data pane on the right, right-click the order_items table.
  • Select New measure.
  • In the formula bar at the top, enter this DAX formula:
Total Revenue = SUMX(order_items, order_items[price] + order_items[freight_value])
  • Press Enter to confirm the measure.

What does this DAX measure do?

SUMX iterates over each row in the order_items table and adds price + freight_value for that row. The result is the total revenue including shipping costs. Unlike a simple SUM, SUMX lets you combine multiple columns in the calculation.

  • Right-click the orders table in the Data pane.
  • Select New measure.
  • Enter this DAX formula:
Average Order Value = DIVIDE([Total Revenue], DISTINCTCOUNT(orders[order_id]))
  • Press Enter to confirm.

What does Average Order Value measure?

DIVIDE safely divides Total Revenue by the count of distinct order IDs. Using DISTINCTCOUNT ensures each order is counted once, even if it has multiple line items. DIVIDE also handles division-by-zero gracefully by returning blank instead of an error.

You should now see Total Revenue listed under the order_items table and Average Order Value under the orders table in the Data pane. Both display with a calculator icon.

Measure not appearing?

Check that you typed the formula exactly, including the equals sign after the measure name. If you see an error in the formula bar, look for mismatched brackets or misspelled table names.

Help me fix my DAX measure formula in Power BI.

Build the dashboard visuals

With your data model and measures ready, you can now build an interactive dashboard page. You'll add four visuals that give a complete picture of the e-commerce business.

  • Make sure you're in Report view (chart icon in the left sidebar).
  • In the Visualizations pane on the right, click the Line chart icon.
  • Drag order_purchase_timestamp from the orders table to the X-axis well.
  • Drag Total Revenue to the Y-axis well.
  • Resize the chart to fill roughly the top-left quarter of the canvas.

Why use the timestamp directly?

Power BI automatically creates a date hierarchy from timestamp columns. It groups your data by Year, Quarter, Month, and Day. You can drill up or down through these levels by using the arrow icons on the chart header.

Next, add a map showing sales distribution across Brazilian states.

  • Click an empty area of the canvas to deselect the line chart.
  • In the Visualizations pane, click the Map icon (the globe).
  • Drag customer_state from the customers table to the Location well.
  • Drag Total Revenue to the Size well.
  • Position the map in the top-right area of the canvas.

Now add a bar chart showing the top product categories by revenue.

  • Click an empty area of the canvas.
  • In the Visualizations pane, click the Clustered bar chart icon.
  • Drag product_category_name from the products table to the Y-axis well.
  • Drag Total Revenue to the X-axis well.
  • Position the bar chart in the bottom-left area of the canvas.

Finally, add a card visual showing the total number of orders.

  • Click an empty area of the canvas.
  • In the Visualizations pane, click the Card icon.
  • Drag order_id from the orders table to the Fields well.
  • In the Fields well, click the dropdown arrow next to order_id and select Count (Distinct).
  • Position the card in the bottom-right area of the canvas.

Your dashboard now has four visuals. Click on any data point in one chart and watch the other visuals cross-filter to show the related data.

  • Save your report by pressing Ctrl+S (Windows) or Cmd+S (macOS).
  • Name the file olist-dashboard and save it to your project folder.

What should I see?

Your canvas should have four visuals arranged in a grid: a line chart showing revenue growing over time (top-left), a map with bubbles over Brazilian states (top-right), a bar chart of product categories sorted by revenue (bottom-left), and a card displaying the total distinct order count (bottom-right).

Try clicking a bar in the category chart. The line chart, map, and card all update to show only data for that category. This cross-filtering is automatic in Power BI.

Map showing wrong locations?

Power BI uses Bing Maps to resolve location names. Two-letter state codes (like SP, RJ) might be ambiguous. If bubbles appear in the wrong country, click the map visual, go to the Format pane, and set the Map settings > Country/region to Brazil.

Help me fix the map locations in my Power BI dashboard.

Your full analytics pipeline is now complete. Data flows from PostgreSQL through SQL and Python analysis into an interactive Power BI dashboard. Next up, tackle the Secret Mission to add customer segmentation to your toolkit.

Secret mission

Build an RFM Customer Segmentation

Your dashboard shows what happened in the business. But who are your best customers? In this secret mission, you'll build an RFM (Recency, Frequency, Monetary) analysis using SQL window functions to segment customers into actionable groups. Then you'll visualize those segments in Power BI to answer the question every business cares about: where does our revenue actually come from?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally using Docker, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Docker container (olist-postgres) running PostgreSQL with the Olist dataset loaded.
  • Docker named volume (postgres_data) storing the database data.
  • Local project files (Python scripts, SQL files, CSV data, generated PNG charts).
  • Python virtual environment inside the project folder.
  • Power BI report file (.pbix).

Keep everything running

No action needed. Choose this if you are still actively building on this project or want to keep the database available for future analysis.

  • The Docker container uses minimal resources while running. You can query the database anytime.
  • Your Python scripts and Power BI report are ready to use as-is.
  • To restart the container if Docker Desktop was restarted, navigate to your project folder and run docker compose up -d.

Pause - I'll come back to this later

Stop the Docker container to free up memory and CPU. The named volume preserves all your database data, so nothing is lost.

  • Open your terminal and navigate to the project folder where your compose.yaml lives.
  • Stop the container by running:
docker compose stop
  • When you are ready to resume, run docker compose up -d from the same folder to start it back up with all your data intact.

Delete - I don't want to use this again

Remove all Docker resources and local project files. This frees up all disk space used by the project.

  • Open your terminal and navigate to the project folder where your compose.yaml lives.
  • Remove the container and named volume by running:
docker compose down -v
  • Delete the project folder from your Desktop by running:
cd ~/Desktop
rm -rf ecommerce-pipeline

What about Windows?

On Windows, open File Explorer, navigate to your Desktop, right-click the project folder, and select Delete.

  • If you saved a Power BI report (.pbix file) outside the project folder, delete that file manually as well.

Nice Work!

Nice Work!

Nice work! You just built a complete end-to-end data analysis pipeline on real e-commerce data, from a relational database all the way through to an interactive dashboard.

Here's what you accomplished:

  • Deployed PostgreSQL in Docker using Docker Compose with persistent storage, and loaded 100k+ real e-commerce orders across 9 relational tables.
  • Wrote advanced SQL queries using CTEs, window functions, and date arithmetic to extract business insights like monthly revenue growth, top categories, and delivery performance.
  • Connected Python/pandas to PostgreSQL and created publication-quality visualizations, then built an interactive Power BI dashboard connected live to your database.
  • Segmented customers using RFM analysis with SQL window functions and visualized the segments in Power BI.

Ready to quiz yourself?