SQL to Power BI: E-Commerce Analytics

Build an end-to-end e-commerce analytics project with SQL, Python, and Power BI.

Introduction

30 Second Summary

Every online purchase generates data. Behind the scenes, data analysts use that data to answer questions like "which customers are most valuable?" and "where are deliveries falling behind?"

In this project, you will build an end-to-end e-commerce analytics project using 100K+ real orders from a Brazilian marketplace. You'll load the data into a SQL database, segment customers by value, and present your findings in a Power BI dashboard.

What You'll Build

An analytics portfolio project that takes raw e-commerce CSV data through SQL querying, Python-based customer segmentation, and interactive Power BI dashboarding.

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

  • A local SQLite database with 9 relational tables loaded from real Kaggle e-commerce data.
  • SQL scripts demonstrating business-grade queries with JOINs, CTEs, and window functions.
  • A Python notebook with RFM customer segmentation analysis and visualizations built with matplotlib and seaborn.
  • A polished multi-page Power BI dashboard telling a business story about revenue, customer segments, and payment trends.
  • Secret Mission: Add DAX measures for month-over-month revenue growth and a delivery performance dashboard page.

Are there any prerequisites?

You'll need basic Python syntax knowledge and basic SQL syntax knowledge before starting. You'll also need a free Kaggle account to download the dataset.

Before We Start

Every data analysis project starts with a clean workspace. A well-organized folder structure, isolated Python environment, and the right dataset will make your SQL queries, notebooks, and dashboards come together without friction.

In this step, you'll build the foundation for your e-commerce analytics project. You'll create a folder structure, install your Python packages, and download 100K+ real orders from a Brazilian marketplace.

In this step, get ready to:
  • Create the project folder structure with subfolders for SQL, data, and output.
  • Set up a Python virtual environment and install analysis packages.
  • Download the Olist e-commerce dataset from Kaggle.
Create the project folder structure

Your project needs separate folders for raw data, SQL scripts, Python notebooks, and generated output. Setting this up now keeps everything organized as the project grows.

  • Open your terminal by pressing Cmd+Space (macOS) or the Windows key (Windows), typing Terminal (macOS) or PowerShell (Windows), and pressing Enter.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir ecommerce-analytics
cd ecommerce-analytics
  • Create the subfolders for your project by running:
mkdir data sql output output/charts notebooks
  • Confirm the folders exist by running:
ls

You should see data, sql, output, and notebooks listed in the output.

Don't see the folders?

Make sure you ran the mkdir command from inside the ecommerce-analytics folder. Run pwd to check your current location.

If you're on Windows and ls doesn't work, try dir instead.

Help me figure out what went wrong

Set up the Python environment and install packages

A virtual environment isolates your project's packages from the rest of your system. This prevents version conflicts between different Python projects on your machine.

  • Create a virtual environment by running:
python3 -m venv venv

What does this command do?

python3 -m venv venv tells Python to create a new virtual environment in a folder called venv. All packages you install next will live inside this folder instead of your global Python installation.

  • Activate the virtual environment by running the command for your operating system:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

You should see (venv) appear at the beginning of your terminal prompt. This confirms your virtual environment is active.

Not seeing (venv) in your prompt?

On Windows, if you get an execution policy error, run Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned in PowerShell first, then try the activate command again.

On macOS, make sure you typed source before the path. Without it, the command won't activate the environment.

Help me troubleshoot this

Now you need a requirements.txt file to define which packages your project uses. This makes your project reproducible for anyone else who clones it later.

  • Create a requirements.txt file in your project root by running:
touch requirements.txt
  • Open requirements.txt in a text editor and paste the following content:
pandas==3.0.3
matplotlib==3.11.0
seaborn==0.13.2
notebook==7.6.0

What are these packages?

  • pandas is your main data manipulation library for loading, filtering, and transforming tabular data.
  • matplotlib and seaborn create charts and visualizations from your analysis results.
  • notebook gives you Jupyter Notebook, an interactive environment for running Python code in cells.
  • Save requirements.txt.
  • Install all four packages by running:
pip install -r requirements.txt

This takes a minute or two. When it finishes, you should see a final line that says Successfully installed followed by the package names.

Install failing or timing out?

Make sure your virtual environment is active (you should see (venv) in your prompt). If you run pip install without the venv active, you might hit permission errors.

If you see a network timeout, check your internet connection and try again.

Help me fix this error

✔️ Awesome, I've got everything!

Great. Double check you've saved requirements.txt and that the install completed without errors.

ⓧ I'd like to double check the full code

Here's what your requirements.txt should contain:

pandas==3.0.3
matplotlib==3.11.0
seaborn==0.13.2
notebook==7.6.0
Download the Olist dataset from Kaggle

The Olist Brazilian E-Commerce dataset contains 100K+ real orders across 9 relational tables. This is the data you'll query with SQL and visualize in Power BI throughout the project.

  • Open this link in your browser: https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce.
  • Click the Download button on the right side of the page.
  • Sign in to your Kaggle account if prompted.
  • A ZIP file will download to your computer.
  • Extract (unzip) the downloaded file.
  • Move all 9 CSV files into the data/ folder inside your ecommerce-analytics project.

What are the 9 CSV files?

The dataset contains these files that you should see in your data/ folder:

  • olist_customers_dataset.csv (customer info and location).
  • olist_orders_dataset.csv (order timestamps and status).
  • olist_order_items_dataset.csv (items in each order with prices).
  • olist_order_payments_dataset.csv (payment method and value).
  • olist_order_reviews_dataset.csv (customer reviews and scores).
  • olist_products_dataset.csv (product details).
  • olist_sellers_dataset.csv (seller info).
  • olist_geolocation_dataset.csv (zip code coordinates).
  • product_category_name_translation.csv (Portuguese to English category names).
  • Back in your terminal, confirm the files are in place by running:
ls data/

You should see all 9 CSV files listed.

Not seeing 9 files in data/?

Some systems extract the ZIP into a nested subfolder. Check if there's an extra folder inside data/ and move the CSVs up one level if needed.

Make sure you moved the CSV files (not the ZIP file itself) into the data/ folder.

Help me find my CSV files

  • Open your project in VS Code or Cursor by pressing Cmd+Space (macOS) or the Windows key (Windows), typing the editor name, and pressing Enter.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to Desktop and select the ecommerce-analytics folder, then click Open.

You should see your folder structure in the sidebar: data/, sql/, output/, notebooks/, and venv/.

Your workspace is ready. Next up, you'll write a Python script to load all 9 CSV files into a local SQLite database so you can query them with SQL.

Set Up the SQLite Database

Your environment is ready and the Olist CSV files are sitting in your data/ folder. Now it's time to turn that raw data into something you can query with SQL.

SQL queries can't run against CSV files directly. You need a database. In this step, you'll write a Python script that loads all 9 Olist CSV files into a local SQLite database. This single database file becomes the foundation for every analysis query you'll write in the rest of the project.

In this step, get ready to:
  • Create a Python script that maps each CSV file to a database table.
  • Load all 9 CSV files into a SQLite database.
  • Verify the database by querying table row counts.
Create the database loader script

Python includes a built-in sqlite3 module that lets you create and query SQLite databases without installing anything extra. Your script will read each CSV file, create a matching table, and insert all the rows.

  • Create a new file called load_to_sqlite.py in your project folder (the same level as the data/ folder).
  • Add the imports and configuration that map table names to CSV filenames by pasting this code:
import sqlite3
import csv
import os

DB_PATH = "olist.db"
DATA_DIR = "data"

CSV_FILES = {
    "customers": "olist_customers_dataset.csv",
    "orders": "olist_orders_dataset.csv",
    "order_items": "olist_order_items_dataset.csv",
    "order_payments": "olist_order_payments_dataset.csv",
    "order_reviews": "olist_order_reviews_dataset.csv",
    "products": "olist_products_dataset.csv",
    "sellers": "olist_sellers_dataset.csv",
    "geolocation": "olist_geolocation_dataset.csv",
    "category_translation": "product_category_name_translation.csv",
}

What does this code do?

The CSV_FILES dictionary is the heart of this configuration. Each key is the table name you want in the database, and each value is the matching CSV filename in your data/ folder.

The sqlite3, csv, and os modules are all part of Python's standard library. No extra packages needed.

  • Below the CSV_FILES dictionary, add the function that loads a single CSV file into a database table:

def load_csv_to_table(cursor, table_name, csv_path):
    """Load a CSV file into a SQLite table, creating the table from headers."""
    with open(csv_path, "r", encoding="utf-8") as f:
        reader = csv.reader(f)
        headers = next(reader)
        col_list = ", ".join(headers)
        placeholders = ", ".join("?" * len(headers))

        cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
        cursor.execute(
            f"CREATE TABLE {table_name} ({col_list})"
        )

        rows = []
        for row in reader:
            if len(row) == len(headers):
                rows.append(row)

        cursor.executemany(
            f"INSERT INTO {table_name} ({col_list}) VALUES ({placeholders})",
            rows,
        )
    return len(rows)

What does this function do?

  • It reads the first row of the CSV to get column headers, then uses those headers to dynamically create a table with matching column names.
  • The if len(row) == len(headers) check skips any malformed rows where the column count doesn't match the header.
  • It uses executemany to insert all rows in one batch, which is much faster than inserting one row at a time.
  • Below the load_csv_to_table function, add the main function and entry point that ties everything together:

def main():
    if os.path.exists(DB_PATH):
        os.remove(DB_PATH)

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    print("Loading Olist dataset into SQLite...")
    print("-" * 40)

    for table_name, csv_file in CSV_FILES.items():
        csv_path = os.path.join(DATA_DIR, csv_file)
        if not os.path.exists(csv_path):
            print(f"  SKIP: {csv_file} not found")
            continue
        row_count = load_csv_to_table(cursor, table_name, csv_path)
        print(f"  {table_name}: {row_count:,} rows loaded")

    conn.commit()
    conn.close()
    print("-" * 40)
    print(f"Database created: {DB_PATH}")


if __name__ == "__main__":
    main()

What does the main function do?

  • It deletes any existing olist.db file first so you always start fresh.
  • It loops through every entry in CSV_FILES, calling load_csv_to_table for each one and printing how many rows were loaded.
  • The conn.commit() call saves all changes to disk at the end. Without it, the data would be lost when the script finishes.
  • Save load_to_sqlite.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

import sqlite3
import csv
import os

DB_PATH = "olist.db"
DATA_DIR = "data"

CSV_FILES = {
    "customers": "olist_customers_dataset.csv",
    "orders": "olist_orders_dataset.csv",
    "order_items": "olist_order_items_dataset.csv",
    "order_payments": "olist_order_payments_dataset.csv",
    "order_reviews": "olist_order_reviews_dataset.csv",
    "products": "olist_products_dataset.csv",
    "sellers": "olist_sellers_dataset.csv",
    "geolocation": "olist_geolocation_dataset.csv",
    "category_translation": "product_category_name_translation.csv",
}


def load_csv_to_table(cursor, table_name, csv_path):
    """Load a CSV file into a SQLite table, creating the table from headers."""
    with open(csv_path, "r", encoding="utf-8") as f:
        reader = csv.reader(f)
        headers = next(reader)
        col_list = ", ".join(headers)
        placeholders = ", ".join("?" * len(headers))

        cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
        cursor.execute(
            f"CREATE TABLE {table_name} ({col_list})"
        )

        rows = []
        for row in reader:
            if len(row) == len(headers):
                rows.append(row)

        cursor.executemany(
            f"INSERT INTO {table_name} ({col_list}) VALUES ({placeholders})",
            rows,
        )
    return len(rows)


def main():
    if os.path.exists(DB_PATH):
        os.remove(DB_PATH)

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    print("Loading Olist dataset into SQLite...")
    print("-" * 40)

    for table_name, csv_file in CSV_FILES.items():
        csv_path = os.path.join(DATA_DIR, csv_file)
        if not os.path.exists(csv_path):
            print(f"  SKIP: {csv_file} not found")
            continue
        row_count = load_csv_to_table(cursor, table_name, csv_path)
        print(f"  {table_name}: {row_count:,} rows loaded")

    conn.commit()
    conn.close()
    print("-" * 40)
    print(f"Database created: {DB_PATH}")


if __name__ == "__main__":
    main()
Run the script and verify the database

With the script saved, you can now load all 100K+ rows from the Olist dataset into your SQLite database in one command.

  • Run the loader script by executing this command in your terminal:
python load_to_sqlite.py

The script takes about 10-20 seconds to finish. You should see output listing each table name followed by a row count.

What should I see?

Your terminal output should look similar to this:

Loading Olist dataset into SQLite...
----------------------------------------
  customers: 99,441 rows loaded
  orders: 99,441 rows loaded
  order_items: 112,650 rows loaded
  order_payments: 103,886 rows loaded
  order_reviews: 99,224 rows loaded
  products: 32,951 rows loaded
  sellers: 3,095 rows loaded
  geolocation: 1,000,163 rows loaded
  category_translation: 71 rows loaded
----------------------------------------
Database created: olist.db

Your exact row counts may differ slightly depending on how the CSV parser handles edge cases, but they should be close to these numbers.

  • Verify that the database is queryable by running a quick SELECT COUNT on the orders table:
python -c "import sqlite3; conn = sqlite3.connect('olist.db'); print('Orders:', conn.execute('SELECT COUNT(*) FROM orders').fetchone()[0])"
  • You should see Orders: 99441 printed (or a number very close to it).
  • Confirm the olist.db file exists by running:
ls olist.db

You should see olist.db listed in the output.

Seeing SKIP messages or missing tables?

  • If you see SKIP: olist_orders_dataset.csv not found, your CSV files are not in the data/ folder. Make sure you extracted the Kaggle download directly into data/ without an extra nested folder.
  • If you see a UnicodeDecodeError, confirm your CSV files aren't corrupted by re-downloading from Kaggle.
  • If the script runs but reports 0 rows for a table, the CSV file may be empty or missing its header row.

Still stuck? Help me troubleshoot my load_to_sqlite.py script.

Why SQLite instead of MySQL or PostgreSQL?

SQLite is built into Python's standard library. There's no server to install, no credentials to configure, and no ongoing cost. Your entire database lives in a single file (olist.db).

It still supports the SQL features that matter for DA/BI interviews: multi-table JOINs, CTEs, and window functions. For a portfolio project with 100K rows, SQLite is the perfect fit.

Your SQLite database is ready with over 100K orders across 9 relational tables. Next up, you'll write SQL queries that join these tables together to uncover revenue trends and delivery performance insights.

Write SQL Revenue and Delivery Queries

Your database is loaded with over 100K real e-commerce orders. Now it's time to ask business questions.

In this step, you'll write SQL queries that answer two questions hiring managers care about: how is revenue trending month-over-month, and where are deliveries falling behind? These are the exact queries a DA/BI intern runs in their first sprint.

In this step, get ready to:
  • Write a revenue analysis SQL file with multi-table JOINs and date grouping.
  • Write a delivery performance SQL file with CASE WHEN logic and date arithmetic.
  • Run both queries from Python and export results as CSVs.
Write the monthly revenue trend query

Every e-commerce business tracks revenue over time. Your first query joins the orders and order_items tables to calculate monthly totals.

  • Create a new file called 01_revenue_analysis.sql inside the sql/ folder.
  • Add the monthly revenue trend query by pasting this code:
-- Monthly Revenue Trend
SELECT
    strftime('%Y-%m', o.order_purchase_timestamp) AS order_month,
    COUNT(DISTINCT o.order_id) AS total_orders,
    ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue,
    ROUND(AVG(oi.price + oi.freight_value), 2) AS avg_order_value
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
GROUP BY order_month
ORDER BY order_month;

What does this query do?

This query uses INNER JOIN to combine order data with item-level pricing. The strftime('%Y-%m', ...) function extracts the year and month from each purchase timestamp so you can group revenue by month.

The WHERE o.order_status = 'delivered' filter ensures you only count orders that actually completed. Revenue is calculated as price + freight_value to capture the full amount the customer paid.

  • Save sql/01_revenue_analysis.sql.
  • Confirm the file exists by running:
ls sql/

You should see 01_revenue_analysis.sql in the output.

Don't see the file?

Make sure you saved the file inside the sql/ folder, not the project root. The folder should already exist from Step 1.

Help me troubleshoot this

Now add the second query to the same file. This one analyzes which payment methods customers prefer.

  • In sql/01_revenue_analysis.sql, add a blank line after the last semicolon, then paste this query below:
-- Payment Method Breakdown
SELECT
    op.payment_type,
    COUNT(*) AS transaction_count,
    ROUND(SUM(op.payment_value), 2) AS total_value,
    ROUND(AVG(op.payment_value), 2) AS avg_value
FROM order_payments op
INNER JOIN orders o ON op.order_id = o.order_id
WHERE o.order_status = 'delivered'
GROUP BY op.payment_type
ORDER BY total_value DESC;

What does this query do?

This query joins order_payments with orders to break down transaction volume and value by payment type (credit card, boleto, voucher, debit card). The ORDER BY total_value DESC shows the most popular payment methods first.

  • Save sql/01_revenue_analysis.sql.

You won't see runtime output yet. The next substep creates a Python runner that executes these queries.

✔️ Awesome, I've got everything!

Great. Double check you've saved the file with both queries before moving on.

ⓧ I'd like to double check the full code

-- Monthly Revenue Trend
SELECT
    strftime('%Y-%m', o.order_purchase_timestamp) AS order_month,
    COUNT(DISTINCT o.order_id) AS total_orders,
    ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue,
    ROUND(AVG(oi.price + oi.freight_value), 2) AS avg_order_value
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_status = 'delivered'
GROUP BY order_month
ORDER BY order_month;

-- Payment Method Breakdown
SELECT
    op.payment_type,
    COUNT(*) AS transaction_count,
    ROUND(SUM(op.payment_value), 2) AS total_value,
    ROUND(AVG(op.payment_value), 2) AS avg_value
FROM order_payments op
INNER JOIN orders o ON op.order_id = o.order_id
WHERE o.order_status = 'delivered'
GROUP BY op.payment_type
ORDER BY total_value DESC;
Write the delivery performance query

Late deliveries erode customer trust. This query measures how many orders arrived after the estimated delivery date, broken down by Brazilian state.

  • Create a new file called 02_delivery_analysis.sql inside the sql/ folder.
  • Paste this delivery performance query:
-- Delivery Performance by State
SELECT
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(
        CASE
            WHEN julianday(o.order_delivered_customer_date) > julianday(o.order_estimated_delivery_date)
            THEN 1
            ELSE 0
        END
    ) AS late_deliveries,
    ROUND(
        100.0 * SUM(
            CASE
                WHEN julianday(o.order_delivered_customer_date) > julianday(o.order_estimated_delivery_date)
                THEN 1
                ELSE 0
            END
        ) / COUNT(DISTINCT o.order_id),
        2
    ) AS late_delivery_pct,
    ROUND(
        AVG(julianday(o.order_delivered_customer_date) - julianday(o.order_purchase_timestamp)),
        1
    ) AS avg_delivery_days
FROM orders o
INNER 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 total_orders >= 100
ORDER BY late_delivery_pct DESC;

What does this query do?

The julianday() function converts dates into numbers so you can compare them with simple math. When order_delivered_customer_date is greater than order_estimated_delivery_date, the order was late.

The CASE WHEN logic counts each late delivery as 1 and each on-time delivery as 0. Dividing by total orders gives the late delivery percentage per state.

The HAVING total_orders >= 100 filter removes states with too few orders to be statistically meaningful.

  • Save sql/02_delivery_analysis.sql.
  • Confirm both SQL files exist by running:
ls sql/

You should see 01_revenue_analysis.sql and 02_delivery_analysis.sql listed.

Seeing only one file?

Check that both files are saved inside the sql/ folder. If you accidentally created them in the project root, move them into sql/ using your editor's file explorer.

Help me fix my file locations

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

-- Delivery Performance by State
SELECT
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(
        CASE
            WHEN julianday(o.order_delivered_customer_date) > julianday(o.order_estimated_delivery_date)
            THEN 1
            ELSE 0
        END
    ) AS late_deliveries,
    ROUND(
        100.0 * SUM(
            CASE
                WHEN julianday(o.order_delivered_customer_date) > julianday(o.order_estimated_delivery_date)
                THEN 1
                ELSE 0
            END
        ) / COUNT(DISTINCT o.order_id),
        2
    ) AS late_delivery_pct,
    ROUND(
        AVG(julianday(o.order_delivered_customer_date) - julianday(o.order_purchase_timestamp)),
        1
    ) AS avg_delivery_days
FROM orders o
INNER 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 total_orders >= 100
ORDER BY late_delivery_pct DESC;
Run the queries and export results as CSVs

SQL files on their own don't produce output. You need a Python script that reads each .sql file, executes it against your SQLite database, and saves the results as CSVs for Power BI.

  • Create a new file called run_sql_analysis.py in your project root (next to load_to_sqlite.py).
  • Add the helper function that executes a query and saves it to CSV:
import sqlite3
import os

DB_PATH = "olist.db"
OUTPUT_DIR = "output"


def run_query_to_csv(conn, sql_path, query_name, output_filename):
    """Execute a SQL query and save results to CSV."""
    with open(sql_path, "r", encoding="utf-8") as f:
        sql_content = f.read()

    queries = sql_content.split(";")

    cursor = conn.cursor()
    for query in queries:
        query = query.strip()
        if not query or query.startswith("--"):
            continue
        try:
            cursor.execute(query)
            if cursor.description:
                headers = [desc[0] for desc in cursor.description]
                rows = cursor.fetchall()

                output_path = os.path.join(OUTPUT_DIR, output_filename)
                with open(output_path, "w", encoding="utf-8", newline="") as out:
                    import csv
                    writer = csv.writer(out)
                    writer.writerow(headers)
                    writer.writerows(rows)

                print(f"  {query_name}: {len(rows)} rows -> {output_filename}")
                return
        except Exception as e:
            print(f"  Error in {query_name}: {e}")

What does this function do?

The function reads a .sql file, splits it on semicolons to handle files with multiple queries, then executes each query. When a query returns results (cursor.description is not None), it writes the column headers and rows to a CSV file.

It saves only the FIRST query that returns results, then returns. This design works because each SQL file puts its primary analysis query first.

  • Below the run_query_to_csv function, add the main function and entry point:
def main():
    os.makedirs(OUTPUT_DIR, exist_ok=True)

    conn = sqlite3.connect(DB_PATH)
    print("Running SQL analysis...")
    print("-" * 40)

    run_query_to_csv(conn, "sql/01_revenue_analysis.sql", "Monthly Revenue", "monthly_revenue.csv")
    run_query_to_csv(conn, "sql/02_delivery_analysis.sql", "Delivery by State", "delivery_by_state.csv")
    run_query_to_csv(conn, "sql/03_rfm_segmentation.sql", "RFM Segments", "rfm_segments.csv")

    conn.close()
    print("-" * 40)
    print("Analysis complete. Results saved to output/")


if __name__ == "__main__":
    main()

What does this code do?

The main() function creates the output/ directory if it doesn't exist, connects to your database, then runs each SQL file in sequence. Each call saves the first result set to a named CSV.

The third line references sql/03_rfm_segmentation.sql which you haven't created yet. That's intentional. You'll create it in the next step, then re-run this same script.

  • Save run_sql_analysis.py.
  • Run the script to execute your SQL queries and generate CSVs:
python run_sql_analysis.py

You should see output showing rows saved for the first two queries, followed by an error for the third (since that SQL file doesn't exist yet).

What should I see?

Your terminal should print something like:

  • Monthly Revenue: ~23 rows saved to monthly_revenue.csv.
  • Delivery by State: ~17 rows saved to delivery_by_state.csv.
  • A FileNotFoundError for sql/03_rfm_segmentation.sql. This is expected. You'll create that file in the next step.
  • Verify your CSV files were created by running:
ls output/

You should see monthly_revenue.csv and delivery_by_state.csv listed.

Not seeing any CSV files?

Make sure olist.db exists in your project root. The script connects to this file directly. If it's missing, re-run python load_to_sqlite.py from Step 1.

Also check that your SQL files are saved in the sql/ folder with the exact filenames 01_revenue_analysis.sql and 02_delivery_analysis.sql.

Help me debug my missing CSV files

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

import sqlite3
import os

DB_PATH = "olist.db"
OUTPUT_DIR = "output"


def run_query_to_csv(conn, sql_path, query_name, output_filename):
    """Execute a SQL query and save results to CSV."""
    with open(sql_path, "r", encoding="utf-8") as f:
        sql_content = f.read()

    queries = sql_content.split(";")

    cursor = conn.cursor()
    for query in queries:
        query = query.strip()
        if not query or query.startswith("--"):
            continue
        try:
            cursor.execute(query)
            if cursor.description:
                headers = [desc[0] for desc in cursor.description]
                rows = cursor.fetchall()

                output_path = os.path.join(OUTPUT_DIR, output_filename)
                with open(output_path, "w", encoding="utf-8", newline="") as out:
                    import csv
                    writer = csv.writer(out)
                    writer.writerow(headers)
                    writer.writerows(rows)

                print(f"  {query_name}: {len(rows)} rows -> {output_filename}")
                return
        except Exception as e:
            print(f"  Error in {query_name}: {e}")


def main():
    os.makedirs(OUTPUT_DIR, exist_ok=True)

    conn = sqlite3.connect(DB_PATH)
    print("Running SQL analysis...")
    print("-" * 40)

    run_query_to_csv(conn, "sql/01_revenue_analysis.sql", "Monthly Revenue", "monthly_revenue.csv")
    run_query_to_csv(conn, "sql/02_delivery_analysis.sql", "Delivery by State", "delivery_by_state.csv")
    run_query_to_csv(conn, "sql/03_rfm_segmentation.sql", "RFM Segments", "rfm_segments.csv")

    conn.close()
    print("-" * 40)
    print("Analysis complete. Results saved to output/")


if __name__ == "__main__":
    main()

Your SQL queries are working and the CSVs are ready. Next up, you'll write an RFM segmentation query that scores every customer by how recently, frequently, and generously they shop.

Build the RFM Customer Segmentation Query

Your revenue and delivery queries give you aggregate business metrics. Now you need to answer a more strategic question: which customers are your most valuable, and which are slipping away?

RFM segmentation is a data-driven framework that scores every customer on three dimensions: how recently they purchased (Recency), how often (Frequency), and how much they spent (Monetary). In this step, you'll write a single SQL query that calculates all three metrics, scores customers into quartiles, and assigns business-friendly segment labels.

In this step, get ready to:
  • Write a CTE that calculates Recency, Frequency, and Monetary per customer.
  • Apply NTILE(4) window functions to score each dimension on a 1-4 scale.
  • Assign segment labels and export results to CSV.
Write the customer metrics CTE

The foundation of RFM is calculating three raw metrics per customer. You'll use a CTE (Common Table Expression) to compute these in a readable, reusable way.

  • Create a new file called sql/03_rfm_segmentation.sql in your sql/ folder.
  • Add the following SQL to start building your RFM query:
-- RFM Customer Segmentation
WITH customer_metrics AS (
    SELECT
        c.customer_unique_id,
        CAST(
            julianday('2018-10-01') - julianday(MAX(o.order_purchase_timestamp))
            AS INTEGER
        ) AS recency,
        COUNT(DISTINCT o.order_id) AS frequency,
        ROUND(SUM(oi.price + oi.freight_value), 2) AS monetary
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id
),

What does this CTE do?

  • customer_unique_id groups by the deduplicated customer identifier. A single person can have multiple customer_id entries if they checked out from different addresses.
  • Recency measures days between each customer's last purchase and a fixed reference date (2018-10-01, just after the dataset ends). Lower recency means a more recent buyer.
  • Frequency counts distinct orders per customer.
  • Monetary sums the total spend (item price plus freight) across all orders.

Why customer_unique_id instead of customer_id?

The Olist dataset assigns a new customer_id each time a person places an order from a different address. The customer_unique_id field links these together so you get one RFM profile per real person, not per checkout session.

You won't see output yet. The next substep completes the query so you can run it.

Add scoring and segment labels

Raw RFM numbers are hard to act on. A customer with recency=45 and monetary=312.50 doesn't tell a marketing team what to do. Scoring each dimension into quartiles (1-4) and mapping combinations to named segments turns the data into actionable groups.

  • In sql/03_rfm_segmentation.sql, add the following SQL directly below the trailing comma of the customer_metrics CTE:
rfm_scores AS (
    SELECT
        customer_unique_id,
        recency,
        frequency,
        monetary,
        NTILE(4) OVER (ORDER BY recency DESC) AS r_score,
        NTILE(4) OVER (ORDER BY frequency ASC) AS f_score,
        NTILE(4) OVER (ORDER BY monetary ASC) AS m_score
    FROM customer_metrics
)
SELECT
    customer_unique_id,
    recency,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    CASE
        WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 2 AND m_score >= 2 THEN 'Loyal Customers'
        WHEN r_score >= 3 AND f_score <= 2 THEN 'New Customers'
        WHEN r_score = 2 AND f_score >= 2 THEN 'At Risk'
        WHEN r_score = 1 AND f_score >= 2 THEN 'Lost High-Value'
        WHEN r_score <= 2 AND f_score = 1 AND m_score >= 3 THEN 'Big Spenders At Risk'
        ELSE 'Lost'
    END AS segment
FROM rfm_scores
ORDER BY monetary DESC;

What does this code do?

The rfm_scores CTE uses NTILE(4) window functions to divide customers into four equal-sized groups (quartiles) for each metric. A score of 4 means "top quartile" for that dimension.

  • ORDER BY recency DESC gives the highest r_score to customers with the lowest recency (most recent buyers).
  • ORDER BY frequency ASC and ORDER BY monetary ASC give the highest scores to the most frequent and highest-spending customers.
  • The final CASE WHEN block maps score combinations to business-friendly labels like "Champions" and "At Risk" that a marketing team can act on.
  • Save sql/03_rfm_segmentation.sql.

✔️ 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

-- RFM Customer Segmentation
WITH customer_metrics AS (
    SELECT
        c.customer_unique_id,
        CAST(
            julianday('2018-10-01') - julianday(MAX(o.order_purchase_timestamp))
            AS INTEGER
        ) AS recency,
        COUNT(DISTINCT o.order_id) AS frequency,
        ROUND(SUM(oi.price + oi.freight_value), 2) AS monetary
    FROM customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY c.customer_unique_id
),
rfm_scores AS (
    SELECT
        customer_unique_id,
        recency,
        frequency,
        monetary,
        NTILE(4) OVER (ORDER BY recency DESC) AS r_score,
        NTILE(4) OVER (ORDER BY frequency ASC) AS f_score,
        NTILE(4) OVER (ORDER BY monetary ASC) AS m_score
    FROM customer_metrics
)
SELECT
    customer_unique_id,
    recency,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    CASE
        WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 2 AND m_score >= 2 THEN 'Loyal Customers'
        WHEN r_score >= 3 AND f_score <= 2 THEN 'New Customers'
        WHEN r_score = 2 AND f_score >= 2 THEN 'At Risk'
        WHEN r_score = 1 AND f_score >= 2 THEN 'Lost High-Value'
        WHEN r_score <= 2 AND f_score = 1 AND m_score >= 3 THEN 'Big Spenders At Risk'
        ELSE 'Lost'
    END AS segment
FROM rfm_scores
ORDER BY monetary DESC;
Execute the RFM query and verify results

Your run_sql_analysis.py script already includes a line that reads sql/03_rfm_segmentation.sql and exports results to output/rfm_segments.csv. Now that the SQL file exists, the script can run the full analysis.

  • Run the analysis script by entering this command in your terminal:
python run_sql_analysis.py
  • You should see output showing that the RFM Segments query produced rows saved to rfm_segments.csv.

What should I see?

The terminal output shows all three queries running. The RFM Segments line should report around 96,000+ rows exported. This matches the number of unique customers in the dataset.

  • Verify the output by checking the columns in output/rfm_segments.csv. Open the file in your editor and confirm it contains columns: customer_unique_id, recency, frequency, monetary, r_score, f_score, m_score, and segment.

How do I know the segments are correct?

The Olist dataset is a marketplace where most customers buy only once. This means the majority of customers end up in the "Lost" or "New Customers" segments with a frequency of 1.

A known characteristic of this dataset is that the "At Risk" segment contributes the highest total revenue. These are repeat buyers who haven't purchased recently. The repeat purchase rate is approximately 3%.

Seeing an error when running the script?

  • If you see "Error in RFM Segments", check that your SQL file has no typos in the CTE names. The comma after the closing parenthesis of customer_metrics is required to chain the second CTE.
  • Make sure the file is saved as sql/03_rfm_segmentation.sql (not in the project root). The script looks for it in the sql/ folder.
  • Confirm the query ends with a single semicolon. Multiple semicolons would cause the script to split the query incorrectly.

Help me debug my RFM segmentation SQL query

You now have every customer classified into a named segment based on their purchasing behavior. Next up, you'll bring these segments to life with Python visualizations that reveal patterns no spreadsheet can show at a glance.

Analyze and Visualize in a Jupyter Notebook

You have raw CSV results sitting in your output/ folder. Numbers in spreadsheets answer questions, but charts tell stories.

In this step, you will create a Jupyter notebook that visualizes customer segments, revenue trends, and payment patterns. These visualizations will later feed into your Power BI dashboard and README.

In this step, get ready to:
  • Build RFM segment charts (bar chart, revenue breakdown, heatmap).
  • Query the SQLite database directly from Python for revenue and payment visuals.
  • Export all charts as PNGs and create a summary metrics DataFrame.
Create the notebook and visualize RFM segments

Jupyter notebooks let you run Python code in individual cells and see output immediately below each one. You will create a notebook inside a notebooks/ folder and build your visualizations cell by cell.

  • Create the notebooks folder and the output/charts folder by running these commands in your terminal:
mkdir notebooks
mkdir -p output/charts
  • Launch Jupyter Notebook by running:
jupyter notebook

What happens when you run this?

Jupyter starts a local server and opens a file browser in your web browser. You will use this browser interface to create and run notebook cells.

Your terminal stays occupied while Jupyter runs. Leave it open and work in the browser tab instead.

  • In the Jupyter file browser, click into the notebooks folder.
  • Click New in the top right, then select Python 3 to create a new notebook.
  • Click the title at the top (it says "Untitled") and rename it to analysis.

Your notebook is now ready at notebooks/analysis.ipynb. Each code block below is one notebook cell.

  • Add this code as the first cell in your notebook, then press Shift+Enter to run it:
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import sqlite3
import os

# Load the RFM segmentation results from the previous step
rfm = pd.read_csv("../output/rfm_segments.csv")

# Create the charts output directory if it doesn't exist
os.makedirs("../output/charts", exist_ok=True)

print(f"Loaded {len(rfm)} customers")
print(rfm["segment"].value_counts())

What does this cell do?

This imports your data libraries (pandas for data manipulation, matplotlib and seaborn for charts) and loads the RFM CSV you created in the previous step.

The relative path ../output/ goes one folder up from notebooks/ to reach your project root.

  • Confirm you see a count of customers and a breakdown of segments in the output below the cell.
  • Add a new cell and paste this code to create a bar chart of customer count by segment:
# Bar chart: number of customers in each RFM segment
segment_counts = rfm["segment"].value_counts()

fig, ax = plt.subplots(figsize=(10, 6))
segment_counts.plot(kind="bar", ax=ax, color="steelblue")
ax.set_title("Customer Count by RFM Segment")
ax.set_xlabel("Segment")
ax.set_ylabel("Number of Customers")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig("../output/charts/segment_customer_count.png", dpi=150)
plt.show()

What does this chart show?

This bar chart shows how many customers fall into each RFM segment. You will likely see the "Lost" and "New Customers" segments dominate, which is typical for a marketplace where most buyers purchase only once.

  • Run the cell with Shift+Enter. You should see a bar chart with segment names on the x-axis and customer counts on the y-axis.
  • Add a new cell and paste this code for the revenue by segment chart:
# Bar chart: total revenue contribution per segment
segment_revenue = rfm.groupby("segment")["monetary"].sum().sort_values(ascending=False)

fig, ax = plt.subplots(figsize=(10, 6))
segment_revenue.plot(kind="bar", ax=ax, color="darkorange")
ax.set_title("Total Revenue by RFM Segment")
ax.set_xlabel("Segment")
ax.set_ylabel("Revenue (R$)")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig("../output/charts/segment_revenue.png", dpi=150)
plt.show()

Why does this matter for a business audience?

Revenue by segment reveals where the money actually comes from. The "At Risk" segment often contributes the highest total revenue in this dataset. That insight drives the business recommendation to invest in retention campaigns for at-risk customers.

  • Run the cell. You should see a bar chart with segments ordered by total revenue, with "At Risk" likely showing the highest bar.
  • Add a new cell and paste this code for the RFM heatmap:
# Heatmap: average monetary value across R-score and F-score combinations
heatmap_data = rfm.pivot_table(
    values="monetary", index="r_score", columns="f_score", aggfunc="mean"
)

fig, ax = plt.subplots(figsize=(8, 6))
sns.heatmap(heatmap_data, annot=True, fmt=".0f", cmap="YlOrRd", ax=ax)
ax.set_title("Average Monetary Value by R and F Scores")
ax.set_xlabel("Frequency Score")
ax.set_ylabel("Recency Score")
plt.tight_layout()
plt.savefig("../output/charts/rfm_heatmap.png", dpi=150)
plt.show()

How to read this heatmap?

Each cell shows the average spend for customers with that combination of recency score (rows) and frequency score (columns). The darker/warmer colors indicate higher-value customers.

Customers in the top-right corner (high recency, high frequency) are your Champions. Customers in the bottom-left (low recency, low frequency) are Lost.

  • Run the cell. You should see a 4x4 color-coded grid with numbers showing average spend in each R/F combination.

Chart not displaying?

If you see a blank output or a text-based figure description, make sure you are running the notebook in a browser (not a terminal-only mode). The plt.show() call renders the chart inline in Jupyter.

If you get a "FileNotFoundError" for the CSV, check that your notebook is saved inside the notebooks/ folder. The ../output/ path expects to go one level up.

Help me debug my Jupyter notebook chart display issue.

Add revenue and payment charts from the database

Your RFM charts used a pre-exported CSV. Now you will query the SQLite database directly from the notebook using pandas read_sql_query. This demonstrates a common analyst workflow where you pull fresh data on demand.

  • Add a new cell and paste this code for the monthly revenue trend line chart:
# Connect to SQLite and query monthly revenue directly
conn = sqlite3.connect("../olist.db")

monthly_revenue = pd.read_sql_query("""
    SELECT
        strftime('%Y-%m', o.order_purchase_timestamp) AS order_month,
        ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY order_month
    ORDER BY order_month
""", conn)

# Line chart: monthly revenue trend
fig, ax = plt.subplots(figsize=(12, 5))
ax.plot(monthly_revenue["order_month"], monthly_revenue["total_revenue"],
        marker="o", color="teal", linewidth=2)
ax.set_title("Monthly Revenue Trend (2016-2018)")
ax.set_xlabel("Month")
ax.set_ylabel("Revenue (R$)")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig("../output/charts/monthly_revenue_trend.png", dpi=150)
plt.show()

What does this cell do?

Instead of reading from a CSV, pd.read_sql_query() sends a SQL query directly to your olist.db database and returns the results as a DataFrame. This is the same query logic from your 01_revenue_analysis.sql file.

The line chart shows revenue growth over time. You should see a clear upward trend from 2016 through mid-2018.

  • Run the cell. You should see a line chart rising from left to right with data points at each month.
  • Add a new cell and paste this code for the payment method breakdown pie chart:
# Payment method breakdown from the database
payments = pd.read_sql_query("""
    SELECT
        op.payment_type,
        ROUND(SUM(op.payment_value), 2) AS total_value
    FROM order_payments op
    INNER JOIN orders o ON op.order_id = o.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY op.payment_type
    ORDER BY total_value DESC
""", conn)

# Close the database connection
conn.close()

# Pie chart: payment method share
fig, ax = plt.subplots(figsize=(8, 8))
ax.pie(payments["total_value"], labels=payments["payment_type"],
       autopct="%1.1f%%", startangle=90)
ax.set_title("Payment Method Breakdown")
plt.tight_layout()
plt.savefig("../output/charts/payment_breakdown.png", dpi=150)
plt.show()

What does this chart reveal?

The pie chart shows how customers pay. In Brazilian e-commerce, credit cards dominate. This insight matters for business recommendations about payment processing partnerships and checkout UX.

  • Run the cell. You should see a pie chart with credit card taking the largest slice.

Getting a database connection error?

If you see an "OperationalError: unable to open database file", the path ../olist.db is not resolving correctly. Make sure your notebook is saved inside notebooks/ (one level down from the project root where olist.db lives).

If you accidentally closed the connection in a previous cell and re-ran this one, add conn = sqlite3.connect("../olist.db") at the top of this cell and re-run it.

Help me fix my SQLite connection error in Jupyter.

Export charts and create a business metrics summary

Each cell above already saved its chart to output/charts/. Now you will create a summary DataFrame that calculates the key business metrics for your README.

  • Add a final cell and paste this code to calculate your summary metrics:
# Summary business metrics for the README
total_revenue = rfm["monetary"].sum()
total_customers = len(rfm)
avg_order_value = rfm["monetary"].mean()

# Repeat purchase rate: customers who ordered more than once
repeat_customers = len(rfm[rfm["frequency"] > 1])
repeat_purchase_rate = round(100 * repeat_customers / total_customers, 1)

# Create a summary DataFrame
summary = pd.DataFrame({
    "Metric": [
        "Total Revenue (R$)",
        "Total Customers",
        "Avg Order Value (R$)",
        "Repeat Purchase Rate (%)"
    ],
    "Value": [
        f"{total_revenue:,.2f}",
        f"{total_customers:,}",
        f"{avg_order_value:.2f}",
        f"{repeat_purchase_rate}%"
    ]
})

print(summary.to_string(index=False))
print(f"\nCharts saved to: ../output/charts/")
print(f"Files: {os.listdir('../output/charts/')}")

What does the repeat purchase rate tell you?

The repeat purchase rate should be approximately 3%. This is a known characteristic of the Olist marketplace dataset where most buyers are one-time purchasers. It is a real business insight, not a data error.

A ~3% repeat rate tells a hiring manager you understand that marketplaces behave differently from subscription businesses. It shows you can interpret data in context.

  • Run the cell. You should see a table with four metrics and a list of 5 PNG files in the charts folder.
  • Save your notebook by pressing Ctrl+S (or Cmd+S on macOS).
  • Confirm the exported chart files exist by switching back to your terminal (open a new terminal tab if needed) and running:
ls output/charts/

You should see segment_customer_count.png, segment_revenue.png, rfm_heatmap.png, monthly_revenue_trend.png, and payment_breakdown.png listed.

Missing chart files in output/charts/?

If the folder is empty or missing files, go back to the notebook and re-run all cells in order. Each chart cell includes a plt.savefig() call that writes the PNG.

Make sure your terminal is in the project root folder (one level above notebooks/) when running ls output/charts/.

Help me figure out why my chart PNGs are missing.

✔️ Awesome, I've got everything!

Great. Make sure your notebook is saved before moving on.

ⓧ I'd like to double check the full code

Here are all the notebook cells in order. Each section below is one cell in your notebooks/analysis.ipynb notebook:

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import sqlite3
import os

# Load the RFM segmentation results from the previous step
rfm = pd.read_csv("../output/rfm_segments.csv")

# Create the charts output directory if it doesn't exist
os.makedirs("../output/charts", exist_ok=True)

print(f"Loaded {len(rfm)} customers")
print(rfm["segment"].value_counts())
# Bar chart: number of customers in each RFM segment
segment_counts = rfm["segment"].value_counts()

fig, ax = plt.subplots(figsize=(10, 6))
segment_counts.plot(kind="bar", ax=ax, color="steelblue")
ax.set_title("Customer Count by RFM Segment")
ax.set_xlabel("Segment")
ax.set_ylabel("Number of Customers")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig("../output/charts/segment_customer_count.png", dpi=150)
plt.show()
# Bar chart: total revenue contribution per segment
segment_revenue = rfm.groupby("segment")["monetary"].sum().sort_values(ascending=False)

fig, ax = plt.subplots(figsize=(10, 6))
segment_revenue.plot(kind="bar", ax=ax, color="darkorange")
ax.set_title("Total Revenue by RFM Segment")
ax.set_xlabel("Segment")
ax.set_ylabel("Revenue (R$)")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig("../output/charts/segment_revenue.png", dpi=150)
plt.show()
# Heatmap: average monetary value across R-score and F-score combinations
heatmap_data = rfm.pivot_table(
    values="monetary", index="r_score", columns="f_score", aggfunc="mean"
)

fig, ax = plt.subplots(figsize=(8, 6))
sns.heatmap(heatmap_data, annot=True, fmt=".0f", cmap="YlOrRd", ax=ax)
ax.set_title("Average Monetary Value by R and F Scores")
ax.set_xlabel("Frequency Score")
ax.set_ylabel("Recency Score")
plt.tight_layout()
plt.savefig("../output/charts/rfm_heatmap.png", dpi=150)
plt.show()
# Connect to SQLite and query monthly revenue directly
conn = sqlite3.connect("../olist.db")

monthly_revenue = pd.read_sql_query("""
    SELECT
        strftime('%Y-%m', o.order_purchase_timestamp) AS order_month,
        ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY order_month
    ORDER BY order_month
""", conn)

# Line chart: monthly revenue trend
fig, ax = plt.subplots(figsize=(12, 5))
ax.plot(monthly_revenue["order_month"], monthly_revenue["total_revenue"],
        marker="o", color="teal", linewidth=2)
ax.set_title("Monthly Revenue Trend (2016-2018)")
ax.set_xlabel("Month")
ax.set_ylabel("Revenue (R$)")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig("../output/charts/monthly_revenue_trend.png", dpi=150)
plt.show()
# Payment method breakdown from the database
payments = pd.read_sql_query("""
    SELECT
        op.payment_type,
        ROUND(SUM(op.payment_value), 2) AS total_value
    FROM order_payments op
    INNER JOIN orders o ON op.order_id = o.order_id
    WHERE o.order_status = 'delivered'
    GROUP BY op.payment_type
    ORDER BY total_value DESC
""", conn)

# Close the database connection
conn.close()

# Pie chart: payment method share
fig, ax = plt.subplots(figsize=(8, 8))
ax.pie(payments["total_value"], labels=payments["payment_type"],
       autopct="%1.1f%%", startangle=90)
ax.set_title("Payment Method Breakdown")
plt.tight_layout()
plt.savefig("../output/charts/payment_breakdown.png", dpi=150)
plt.show()
# Summary business metrics for the README
total_revenue = rfm["monetary"].sum()
total_customers = len(rfm)
avg_order_value = rfm["monetary"].mean()

# Repeat purchase rate: customers who ordered more than once
repeat_customers = len(rfm[rfm["frequency"] > 1])
repeat_purchase_rate = round(100 * repeat_customers / total_customers, 1)

# Create a summary DataFrame
summary = pd.DataFrame({
    "Metric": [
        "Total Revenue (R$)",
        "Total Customers",
        "Avg Order Value (R$)",
        "Repeat Purchase Rate (%)"
    ],
    "Value": [
        f"{total_revenue:,.2f}",
        f"{total_customers:,}",
        f"{avg_order_value:.2f}",
        f"{repeat_purchase_rate}%"
    ]
})

print(summary.to_string(index=False))
print(f"\nCharts saved to: ../output/charts/")
print(f"Files: {os.listdir('../output/charts/')}")

Your notebook is complete with 5 visualizations and a metrics summary. Next up, you will bring these insights into Power BI to build an interactive dashboard that tells a business story.

Build the Power BI Dashboard

Your Python analysis is complete, with RFM segments, revenue trends, and visualizations all saved as files. Now it's time to turn those outputs into an interactive dashboard that a stakeholder can explore without touching code.

Power BI Desktop lets you import CSV files, define relationships between tables, and build polished visuals with drag-and-drop. You'll build a two-page dashboard: one for sales overview and one for customer segments.

In this step, get ready to:
  • Import your analysis CSVs into Power BI Desktop and set up table relationships.
  • Build a Sales Overview page with revenue cards, trend lines, and payment breakdown.
  • Build a Customer Segments page with RFM visuals and export for GitHub.
Import data sources and set up relationships

Power BI needs data loaded into its model before you can build visuals. You'll import three CSV files that represent different slices of your analysis.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • On the Home tab, click Get Data.
  • Select Text/CSV from the list.
  • Navigate to your project's output/ folder and select monthly_revenue.csv.
  • Click Open, then click Load in the preview window to import the data.
  • Repeat the Get Data process for output/rfm_segments.csv.
  • Repeat once more for data/olist_order_payments_dataset.csv (the raw payments file from your Kaggle download).

Why import the raw payments CSV?

Your monthly_revenue.csv already has aggregated revenue data, but it doesn't break down by payment method.

The raw olist_order_payments_dataset.csv has a payment_type column you need for the Donut chart on Page 1.

After loading all three files, you should see monthly_revenue, rfm_segments, and olist_order_payments_dataset listed in the Fields pane on the right side.

Now set up relationships between the tables so filters propagate correctly across your visuals.

  • Click the Model view icon on the left side of the Power BI Desktop window (it looks like a diagram with connected boxes).
  • You should see your three tables displayed as cards.
  • Drag the order_month column from the monthly_revenue table onto any date-related column if Power BI suggests a relationship. If no auto-relationship is detected, that is fine for this project.

What is Model view?

Model view shows all tables, columns, and relationships in your data model. You can drag a column from one table to a matching column in another table to create a relationship.

For this project, the three CSVs are largely independent (revenue, segments, payments), so Power BI won't auto-detect relationships. That's expected. Each table powers different visuals on your dashboard.

  • Switch back to Report view by clicking the top icon on the left sidebar (the chart/page icon).

Don't see your tables in the Fields pane?

Make sure you clicked Load (not Transform Data) in the data preview window. If you accidentally opened Power Query Editor, click Close & Apply on the Home tab to load your data into the model.

help me load CSV data into Power BI Desktop

Build Page 1: Sales Overview

Page 1 gives an executive-level snapshot of the business: total revenue, how revenue grew over time, and how customers pay.

  • In Report view, right-click the page tab at the bottom (it says Page 1) and select Rename Page.
  • Type Sales Overview and press Enter.

Start with a headline number so viewers immediately see total revenue.

  • In the Visualizations pane on the right, click the Card visual icon (a rectangle with a number).
  • From the Fields pane, expand monthly_revenue and drag total_revenue into the Card's Fields well.
  • The card now displays the sum of all monthly revenue values. Position it at the top-left of the canvas.

Next, add a line chart to show the revenue growth trend from 2016 to 2018.

  • Click an empty area on the canvas to deselect the Card.
  • In the Visualizations pane, click the Line chart icon.
  • Drag order_month from monthly_revenue to the X-axis well.
  • Drag total_revenue to the Y-axis well.
  • Resize the chart to fill the middle area of the page. You should see an upward trend line.

Add a Donut chart to show the split between payment methods.

  • Click an empty area on the canvas.
  • Select the Donut chart icon in the Visualizations pane.
  • From the olist_order_payments_dataset table, drag payment_type to the Legend well.
  • Drag payment_value to the Values well.
  • Position the Donut chart to the right of the Line chart.

Finally, add a Slicer so viewers can filter by time period.

  • Click an empty area on the canvas.
  • Select the Slicer icon in the Visualizations pane.
  • Drag order_month from monthly_revenue to the Slicer's Field well.
  • Position the Slicer at the top-right of the page.

Apply a consistent color theme to give the dashboard a professional look.

  • Click the View tab in the ribbon.
  • Click Themes and choose a theme you like (e.g., a darker theme for contrast). This applies consistent colors across all visuals.

Click a value in the Slicer to filter the page. The Card and Line chart should update to show only the selected time period.

Slicer not filtering the Line chart?

Both visuals must use data from the same table (monthly_revenue) for the Slicer to filter them. If you accidentally dragged a field from a different table into the Slicer, the filter won't propagate.

Delete the Slicer and re-add it, making sure you drag order_month from the monthly_revenue table specifically.

help me fix my Power BI slicer not filtering other visuals

Build Page 2: Customer Segments

Page 2 tells the customer story. It shows how many customers fall into each RFM segment and how much revenue each segment contributes.

  • Click the + icon next to the Sales Overview tab at the bottom to add a new page.
  • Right-click the new page tab and rename it to Customer Segments.

Add a Clustered bar chart to show how many customers are in each segment.

  • Select the Clustered bar chart icon in the Visualizations pane.
  • From the rfm_segments table, drag segment to the Y-axis well.
  • Drag customer_unique_id to the X-axis well.
  • Power BI will default to counting distinct values. Confirm the X-axis shows Count of customer_unique_id (click the dropdown arrow next to the field name in the well to change the aggregation if needed).
  • Position this chart on the left half of the page.

Add a Treemap to show revenue contribution by segment.

  • Click an empty area, then select the Treemap icon in the Visualizations pane.
  • Drag segment to the Category well.
  • Drag monetary to the Values well.
  • Position the Treemap on the upper-right area of the page.

Add a Table visual to show the average metrics per segment.

  • Click an empty area, then select the Table icon in the Visualizations pane.
  • Drag segment, monetary, and recency from rfm_segments into the Columns well.
  • Click the dropdown arrow on monetary in the Columns well and change the aggregation to Average.
  • Do the same for recency to show average recency per segment.
  • Position the Table below the Treemap.

What should you see on Page 2?

The bar chart shows the "Lost" segment has the highest customer count (most buyers only purchased once on this marketplace).

The Treemap shows the "At Risk" segment contributes the highest total revenue. This is a known characteristic of the Olist dataset.

Save your work and export for GitHub.

  • Press Ctrl+S (Windows) or Cmd+S (macOS).
  • Save the file as olist_dashboard.pbix in your project folder.
  • To export screenshots for GitHub: navigate to each page, then use File > Export > Export to PDF.
  • Alternatively, take screenshots of each page using your OS screenshot tool (Cmd+Shift+4 on macOS, Win+Shift+S on Windows) and save them in your project's output/ folder.

Screenshots vs PDF export

PDF export captures all pages in one file, which is convenient. Screenshots give you individual images you can embed in your GitHub README for a more polished portfolio presentation.

Switch between your two pages and click different Slicer values or bar segments. All visuals on the active page should respond to your selections.

Visuals not responding to clicks?

Make sure you're clicking in Report view (not Model view or Data view). Also check that the visual you're clicking uses data from the same table as the visual you expect to update.

help me debug why my Power BI visuals are not cross-filtering

Your portfolio dashboard is complete. You've taken raw e-commerce data from SQL queries all the way to an interactive executive dashboard. Next up, the Secret Mission challenges you to add DAX measures and a third dashboard page.

Secret mission

Add DAX Measures and a Delivery Performance Page

Your dashboard tells a solid story about revenue and customer segments. But executives want to see month-over-month trends at a glance and understand delivery performance by state. In this secret mission, you will create DAX measures for dynamic revenue calculations and build a third dashboard page focused on delivery logistics.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your files as they are, share them on GitHub, or delete them entirely.

Resources you used:

  • olist.db SQLite database file (~80MB, local, no cost).
  • data/ folder containing 9 Kaggle CSV files.
  • output/ folder containing analysis CSVs and chart PNGs.
  • Power BI .pbix file with your 3-page dashboard.
  • Python virtual environment with installed packages (pandas, matplotlib, seaborn, notebook).
  • notebooks/analysis.ipynb Jupyter notebook.

Keep everything running

No action needed. Choose this if you plan to keep building on this project or want everything accessible for your portfolio.

  • All files stay on your local machine at no cost.
  • Your olist.db file is about 80MB. The data/ folder with raw CSVs adds another ~120MB.
  • Your Python virtual environment stays active for future analysis work.

Pause - I'll come back to this later

Shut down running processes to free up memory while keeping all your project files intact.

  • If Jupyter Notebook is still running in your terminal, press Ctrl+C to stop it.
  • Close Power BI Desktop if it is open.
  • Deactivate your Python virtual environment by running:
deactivate

All your files remain on disk. When you return, reactivate your virtual environment and pick up where you left off.

Delete - I don't want to use this again

Remove all project files from your machine. Make sure you have pushed any code or screenshots you want to keep to GitHub before deleting.

  • If you only need the SQL scripts and screenshots for your portfolio, delete the large data files by running:
rm -rf data/
rm olist.db

This removes the raw CSVs and the SQLite database, freeing ~200MB of disk space while keeping your code and chart images.

  • To delete everything including your code, navigate to the parent folder (your Desktop) and remove the entire project folder:
cd ~/Desktop
rm -rf ecommerce-analytics

Before you delete

If you want to share this project on GitHub, push your code first. Exclude the data/ folder, olist.db, and the .pbix file from your repo if they exceed GitHub's file size limit. Include screenshots of your dashboard instead.

Nice Work!

Nice Work!

You just built a complete e-commerce customer analytics project from raw data to executive dashboard. This is the exact workflow that data analyst and business intelligence interns perform on the job.

You've learned how to:

  • Build a local SQLite database from 9 CSV files and write business-grade SQL with multi-table JOINs, CTEs, CASE WHEN, and NTILE window functions.
  • Perform RFM customer segmentation to identify Champions, Loyal, At Risk, and Lost customer groups, then visualize the results with matplotlib and seaborn.
  • Build a multi-page Power BI dashboard with interactive slicers, cards, and chart visuals that tells a business story about revenue, customer segments, and payment trends.
  • Secret Mission: Added DAX measures for month-over-month revenue growth and running totals, plus a delivery performance dashboard page with geographic analysis.

Ready to quiz yourself?