E-Commerce Customer Analytics Pipeline
Segment 100k real customers with SQL, RFM analysis, and Python.
Introduction
30 Second Summary
Every online store collects thousands of orders, but few ever turn that raw data into decisions. Knowing which customers to invest in and which are slipping away is the difference between growing revenue and losing it.
In this project, you will build a customer analytics pipeline using Python and SQL. You will load 100,000+ real e-commerce orders into a database, uncover business trends, and segment customers into actionable groups.
What You'll Build
An end-to-end analytics pipeline that loads the Brazilian E-Commerce Public Dataset by Olist into a SQLite database, runs business-driven SQL queries, and performs RFM (Recency, Frequency, Monetary) customer segmentation with publication-quality visualizations.
By the end of this project, you'll have:
- A SQLite database loaded with 6 tables of real e-commerce data covering 100,000+ orders.
- SQL queries analyzing revenue trends, delivery performance, and payment patterns across multiple joined tables.
- RFM customer segmentation with scored customer groups, 4 publication-quality visualizations, and a GitHub-ready repository with a professional README.
- Secret Mission: Build a monthly cohort retention heatmap revealing how customer repurchase behavior changes over time.
Are there any prerequisites?
This project assumes you are comfortable with Python and pandas. No prior SQL or database experience is needed.
Before We Start
Before We Start...
Set Up Python, Packages, and Project Structure
Every analytics project starts the same way: getting your tools ready, your data downloaded, and your workspace organized. In this project, you will load real e-commerce data into a database and analyze it with Python.
This step gets the foundation in place. You will confirm Python is installed, set up a virtual environment, download the Olist dataset from Kaggle, and load it into a SQLite database.
In this step, get ready to:
- Check Python and install required packages.
- Download the Olist dataset and set up the project structure.
- Create and run the database loading script.
Check Python and install required packages
This project uses pandas, matplotlib, and seaborn for data analysis and visualization. These packages require Python 3.11 or higher.
- 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.
- Check your Python version by running this command:
macOS
python3 --versionWindows
python --versionYou should see a version number printed in the terminal.
✔️ I see version 3.11 or higher
Python is ready. Move on to the next instruction below.
ⓧ I see an older version
Your Python version is below 3.11. The packages in this project require 3.11 or higher.
- Download and install Python 3.11 or later from the official Python website.
- On macOS, the system may still default to an older Python. After installing, use python3.11 or python3.12 explicitly, or use a version manager like pyenv to set the default.
- Run the version check command again to confirm you see 3.11 or higher.
macOS still showing the old version?
macOS ships with its own Python that may not update when you install a new version. Try running python3.11 --version or python3.12 --version to use the newly installed version directly.
help me set up the correct Python version on my machine
ⓧ Command not found
Python is not installed yet.
- Download and install Python 3.11 or later from the official Python website.
- On Windows, check the Add Python to PATH box during installation.
- Close and reopen your terminal after installing.
- Run the version check command again to confirm Python is available.
Still not working after install?
If the command is still not found after installing and reopening your terminal, Python may not be on your system PATH.
help me troubleshoot Python not being recognized in my terminal
- Move to your Desktop by running this command:
cd ~/Desktop- Create your project folder and move into it by running these commands:
mkdir ecommerce-customer-analytics
cd ecommerce-customer-analyticsA virtual environment isolates this project's packages from the rest of your system. This keeps your project clean and reproducible.
- Create and activate a virtual environment by running these commands:
macOS
python3 -m venv venv
source venv/bin/activateWindows
python -m venv venv
venv\Scripts\activateWhat is a virtual environment?
A virtual environment creates a self-contained folder (venv/) that holds its own copy of Python and any packages you install. When you activate it, your terminal's python and pip commands point to this folder instead of your system Python.
You should see (venv) at the beginning of your terminal prompt. This confirms the virtual environment is active.
Don't see (venv) in your prompt?
Make sure you ran the activation command (not just the creation command). On macOS, check that you typed source venv/bin/activate with a space after source. On Windows, ensure you used venv\Scripts\activate with backslashes.
help me activate my Python virtual environment
- Install the required packages by running:
pip install pandas matplotlib seabornYou should see download and installation progress for all three packages. The install is complete when you see the final Successfully installed line.
Installation failing?
Make sure your virtual environment is active (you see (venv) in your prompt). If you get permission errors, do not use sudo. Deactivate and recreate the virtual environment instead.
help me fix package installation errors in my virtual environment
- Verify that sqlite3 is available by running:
python -c "import sqlite3; print(sqlite3.sqlite_version)"You should see a version number printed (e.g. 3.45.1 or similar). This confirms SQLite is built into your Python installation and ready to use.
Why no pip install for sqlite3?
The sqlite3 module ships with Python's standard library. Every Python installation includes it, so there is nothing extra to install. This is one of the reasons SQLite is a great choice for portfolio projects. Anyone cloning your repository can run it instantly.
Download the dataset and set up the project structure
This project uses a real-world dataset from a Brazilian e-commerce company called Olist. It contains 100,000 real orders across 9 interconnected tables.
Why use a real dataset instead of synthetic data?
Hiring managers want to see that you can handle messy, real-world data. The Olist dataset has genuine quality issues like missing values, inconsistent statuses, and null timestamps. Documenting how you clean these issues is exactly what sets a strong portfolio apart.
- Go to the Olist dataset page on Kaggle.
- Sign in with a free Kaggle account (create one if you do not have one).
- Click the Download button to download the dataset as a ZIP file.
- Extract the ZIP file. You will get 9 CSV files.
- Create a data/ folder inside your ecommerce-customer-analytics project folder by running:
mkdir data- Move all 9 extracted CSV files into the data/ folder. You can drag and drop them using Finder (macOS) or File Explorer (Windows).
What files should I see?
Your data/ folder should contain these 9 CSV files:
- olist_customers_dataset.csv (99,441 rows)
- olist_orders_dataset.csv (99,441 rows)
- olist_order_items_dataset.csv (112,650 rows)
- olist_order_payments_dataset.csv (103,886 rows)
- olist_order_reviews_dataset.csv (99,224 rows)
- olist_products_dataset.csv (32,951 rows)
- olist_sellers_dataset.csv (3,095 rows)
- olist_geolocation_dataset.csv (1,000,163 rows)
- product_category_name_translation.csv (71 rows)
- Confirm the CSV files are in the right place by running:
ls data/You should see all 9 CSV files listed in the output.
- Create the outputs/ folder (for saving visualizations later) and the project files by running:
mkdir outputs
touch load_database.py analysis.py README.mdWhat is each file for?
- load_database.py loads the CSV data into a SQLite database.
- analysis.py will hold all your SQL queries, RFM segmentation, and visualizations.
- README.md is your project's documentation for GitHub.
- outputs/ will store the PNG chart files your analysis generates.
- Confirm all files and folders exist by running:
lsYou should see data, outputs, venv, load_database.py, analysis.py, and README.md in the output.
Missing a file or folder?
Double-check that you ran the mkdir and touch commands from inside the ecommerce-customer-analytics folder. Run pwd to confirm your current directory.
help me check that my project folder structure is correct
- Open VS Code by pressing Cmd+Space (macOS) or the Windows key (Windows), typing VS Code, and pressing Enter.
- Click File in the top menu bar.
- Click Open Folder.
- Navigate to your Desktop and select the ecommerce-customer-analytics folder, then click Open.
Create the database loading script
You have 9 CSV files, but your analysis will only use 6 of them. The geolocation table has over a million rows and is not needed for customer analytics. The sellers and products tables are supplementary.
Your loading script will read the 6 relevant CSVs with pandas and write them into a single SQLite database file called olist.db.
- In VS Code, open load_database.py from the Explorer sidebar on the left.
- Copy the following code into load_database.py:
import pandas as pd
import sqlite3
import os
def load_olist_database():
"""Load Olist CSV files into a SQLite database."""
db_path = "olist.db"
data_dir = "data"
# Remove existing database so we start fresh each run
if os.path.exists(db_path):
os.remove(db_path)
print(f"Removed existing {db_path}")
# Create a new SQLite database connection
conn = sqlite3.connect(db_path)
# Map each table name to its CSV file
tables = {
"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",
"category_translation": "product_category_name_translation.csv",
}
# Load each CSV into the database as a table
for table_name, csv_file in tables.items():
file_path = os.path.join(data_dir, csv_file)
if not os.path.exists(file_path):
print(f"WARNING: {file_path} not found. Skipping.")
continue
df = pd.read_csv(file_path)
df.to_sql(table_name, conn, if_exists="replace", index=False)
print(f"Loaded {table_name}: {len(df)} rows, {len(df.columns)} columns")
# Verify which tables were created
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables_in_db = cursor.fetchall()
print(f"\nTables in database: {[t[0] for t in tables_in_db]}")
conn.close()
print(f"\nDatabase saved to {db_path}")
if __name__ == "__main__":
load_olist_database()What does this code do?
- The tables dictionary maps short table names (like customers) to their CSV filenames. This keeps the database table names clean and readable.
- pd.read_csv() reads each CSV file into a pandas DataFrame. Then df.to_sql() writes that DataFrame directly into the SQLite database as a table.
- The script removes any existing olist.db file before loading. This means you can safely re-run the script at any time to get a fresh database.
- The verification query at the end confirms all 6 tables were created successfully.
- Save load_database.py by pressing Cmd+S (macOS) or Ctrl+S (Windows).
- Switch to your terminal and run the script:
python load_database.pyYou should see output listing each table and its row count, followed by a list of all 6 table names in the database.
What should I see?
The output should look similar to this:
Loaded customers: 99441 rows, 5 columns
Loaded orders: 99441 rows, 8 columns
Loaded order_items: 112650 rows, 7 columns
Loaded order_payments: 103886 rows, 5 columns
Loaded order_reviews: 99224 rows, 7 columns
Loaded category_translation: 71 rows, 2 columns
Tables in database: ['customers', 'orders', 'order_items', 'order_payments', 'order_reviews', 'category_translation']
Database saved to olist.dbIf any table shows WARNING instead, the corresponding CSV file is not in the data/ folder. Double-check that you moved all the files from the extracted ZIP.
Seeing a ModuleNotFoundError?
If you see an error about pandas not being found, your virtual environment may not be active. Check for (venv) at the start of your terminal prompt. If it is missing, run the activation command again before retrying.
help me fix ModuleNotFoundError when running my database loading script
- Verify the tables were loaded correctly by running this quick SQL check:
python -c "import sqlite3; conn = sqlite3.connect('olist.db'); print([t[0] for t in conn.execute('SELECT name FROM sqlite_master WHERE type=\'table\';').fetchall()]); conn.close()"You should see the list ['customers', 'orders', 'order_items', 'order_payments', 'order_reviews', 'category_translation'] printed in your terminal.
✔️ Awesome, I've got everything!
Great. Double check you have saved load_database.py and that olist.db exists in your project folder.
ⓧ I'd like to double check the full code
Here is the complete load_database.py for comparison:
import pandas as pd
import sqlite3
import os
def load_olist_database():
"""Load Olist CSV files into a SQLite database."""
db_path = "olist.db"
data_dir = "data"
if os.path.exists(db_path):
os.remove(db_path)
print(f"Removed existing {db_path}")
conn = sqlite3.connect(db_path)
tables = {
"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",
"category_translation": "product_category_name_translation.csv",
}
for table_name, csv_file in tables.items():
file_path = os.path.join(data_dir, csv_file)
if not os.path.exists(file_path):
print(f"WARNING: {file_path} not found. Skipping.")
continue
df = pd.read_csv(file_path)
df.to_sql(table_name, conn, if_exists="replace", index=False)
print(f"Loaded {table_name}: {len(df)} rows, {len(df.columns)} columns")
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables_in_db = cursor.fetchall()
print(f"\nTables in database: {[t[0] for t in tables_in_db]}")
conn.close()
print(f"\nDatabase saved to {db_path}")
if __name__ == "__main__":
load_olist_database()Your database is loaded and ready to query. Next up, you will clean this data with SQL to prepare it for analysis.
Clean the Data with SQL
Your database is loaded with 100k+ rows of real e-commerce data across six tables.
Real-world data is messy. Orders might be cancelled, timestamps could be missing, and not every row is useful for analysis. Before writing any business queries, you need to understand what's in your data and decide what to keep. In this step, you'll write SQL queries that explore data quality and document your cleaning decisions.
In this step, get ready to:
- Set up the analysis script with imports and a database connection helper.
- Write SQL queries to inspect row counts and null values across all tables.
- Document your cleaning decisions and run the script to see a data quality summary.
Set up the analysis script
All of your analysis code will live in a single file called analysis.py. The first thing it needs is imports for every library you'll use throughout the project, plus a helper function that connects to your database.
- In VS Code, open analysis.py from the Explorer sidebar. The file is currently empty.
- Add the imports and database connection helper by copying this code:
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
import os
import datetime as dt
# Set consistent chart styling and create output folder
sns.set_theme(style="whitegrid")
os.makedirs("outputs", exist_ok=True)
def get_connection():
"""Connect to the Olist SQLite database."""
return sqlite3.connect("olist.db")What does this code do?
- pandas, matplotlib, and seaborn are the data analysis and visualization libraries you installed in Step 1.
- sqlite3 is built into Python's standard library. It lets you run SQL queries against your olist.db database file.
- sns.set_theme(style="whitegrid") applies a clean chart style to every visualization you'll create later.
- get_connection() is a small helper so you don't repeat the database path everywhere.
You won't see any output yet. The next chunk adds the function that actually queries the database.
Explore the data and document quality issues
Data cleaning starts with understanding what you have. You need to know how many rows each table contains, which orders are usable, and where null values might break your analysis.
- In analysis.py, add the explore_and_clean function below get_connection() by copying this code:
def explore_and_clean(conn):
"""Explore data quality and create cleaned dataset."""
print("=" * 60)
print("DATA EXPLORATION AND CLEANING")
print("=" * 60)
# Check row counts for each of the 5 main tables
tables = ["customers", "orders", "order_items", "order_payments", "order_reviews"]
for table in tables:
df = pd.read_sql_query(f"SELECT COUNT(*) as row_count FROM {table}", conn)
print(f"{table}: {df['row_count'].iloc[0]} rows")
# Count total orders vs delivered orders
total_orders = pd.read_sql_query("SELECT COUNT(*) as cnt FROM orders", conn)["cnt"].iloc[0]
delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_status = 'delivered'", conn
)["cnt"].iloc[0]
print(f"\nTotal orders: {total_orders}")
print(f"Delivered orders: {delivered} ({delivered / total_orders * 100:.1f}%)")
print(f"Non-delivered orders removed: {total_orders - delivered}")
# Check for null timestamps that would break date calculations
null_approved = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_approved_at IS NULL", conn
)["cnt"].iloc[0]
print(f"Orders with null approval date: {null_approved}")
null_delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders "
"WHERE order_status = 'delivered' AND order_delivered_customer_date IS NULL",
conn,
)["cnt"].iloc[0]
print(f"Delivered orders with null delivery date: {null_delivered}")
# Document the cleaning decision for reproducibility
print("\nCleaning decision: Keep only delivered orders with non-null delivery dates.")
print("Rationale: Only completed orders have full lifecycle data for analysis.")
return deliveredWhat does this function do?
This function runs four categories of SQL queries to build a complete picture of your data quality.
- Row count loop: runs SELECT COUNT(*) on each table so you can verify data loaded correctly from Step 1.
- Order status check: compares total orders against delivered orders. Only delivered orders have complete lifecycle data (purchase, approval, delivery dates).
- Null timestamp checks: counts null values in order_approved_at and order_delivered_customer_date. Null dates would break delivery time calculations later.
- Cleaning decision: prints the rule ("keep only delivered orders with non-null delivery dates") and the rationale. Documenting this in code is what hiring managers scan for in portfolio projects.
You won't see output yet. The next substep adds the main() function that actually calls explore_and_clean.
Why document cleaning decisions in code?
Anyone reviewing your project (including future you) needs to know why rows were removed. Printing the cleaning rationale directly in the output makes your analysis reproducible.
This is the difference between a school assignment and a professional portfolio piece. Real analysts always document their assumptions.
Write and run the cleaning queries
Your exploration function is ready. Now you need a main() function that opens the database connection, runs the exploration, and closes the connection when finished.
- Add the main() function and entry point at the bottom of analysis.py, below explore_and_clean:
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
conn.close()
if __name__ == "__main__":
main()What does this code do?
- main() is the entry point for the entire analysis pipeline. Right now it only calls explore_and_clean. You'll add more function calls here as you build out the analysis in later steps.
- conn.close() ensures the database connection is properly released when the script finishes.
- if __name__ == "__main__" makes the script runnable from the terminal while still allowing you to import functions from it later.
- Save analysis.py.
- Run the script in your terminal by running this command:
python analysis.py- You should see a data quality summary printed in your terminal. It starts with a row count for each table, then shows how many orders are delivered vs non-delivered, and ends with the cleaning decision.
What should I see?
Your terminal output should show row counts for all 5 tables (customers, orders, order_items, order_payments, order_reviews). Then it should show the total number of orders, the number of delivered orders with a percentage, and the count of non-delivered orders removed.
At the bottom, you should see two null-check counts followed by the cleaning decision: "Keep only delivered orders with non-null delivery dates."
Seeing an error when you run the script?
- If you see ModuleNotFoundError: No module named 'pandas', your virtual environment might not be activated. Run source venv/bin/activate (macOS) or venv\Scripts\activate (Windows) and try again.
- If you see OperationalError: no such table: customers, the database file is missing. Make sure olist.db exists in the same folder as analysis.py. Re-run python load_database.py if needed.
- Check that your indentation is consistent. Python requires all code inside a function to be indented by the same amount.
Help me debug an error when running analysis.py
✔️ Awesome, I've got everything!
Great. Double check you've saved analysis.py.
ⓧ I'd like to double check the full code
Here is the complete analysis.py file so far. Make sure your code matches this exactly.
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
import os
import datetime as dt
sns.set_theme(style="whitegrid")
os.makedirs("outputs", exist_ok=True)
def get_connection():
"""Connect to the Olist SQLite database."""
return sqlite3.connect("olist.db")
def explore_and_clean(conn):
"""Explore data quality and create cleaned dataset."""
print("=" * 60)
print("DATA EXPLORATION AND CLEANING")
print("=" * 60)
tables = ["customers", "orders", "order_items", "order_payments", "order_reviews"]
for table in tables:
df = pd.read_sql_query(f"SELECT COUNT(*) as row_count FROM {table}", conn)
print(f"{table}: {df['row_count'].iloc[0]} rows")
total_orders = pd.read_sql_query("SELECT COUNT(*) as cnt FROM orders", conn)["cnt"].iloc[0]
delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_status = 'delivered'", conn
)["cnt"].iloc[0]
print(f"\nTotal orders: {total_orders}")
print(f"Delivered orders: {delivered} ({delivered / total_orders * 100:.1f}%)")
print(f"Non-delivered orders removed: {total_orders - delivered}")
null_approved = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_approved_at IS NULL", conn
)["cnt"].iloc[0]
print(f"Orders with null approval date: {null_approved}")
null_delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders "
"WHERE order_status = 'delivered' AND order_delivered_customer_date IS NULL",
conn,
)["cnt"].iloc[0]
print(f"Delivered orders with null delivery date: {null_delivered}")
print("\nCleaning decision: Keep only delivered orders with non-null delivery dates.")
print("Rationale: Only completed orders have full lifecycle data for analysis.")
return delivered
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
conn.close()
if __name__ == "__main__":
main()Your data quality report is printed and your cleaning rule is documented. Next up, you'll write business-driven SQL queries that use this cleaning logic to analyze revenue, delivery performance, and payment trends.
Run Business SQL Queries
In the last step, you cleaned the Olist dataset by filtering to delivered orders and documenting every decision. Your database now holds only the data you can trust for analysis.
Now it's time to ask business questions. In this step, you'll write SQL queries that join multiple tables to analyze revenue trends, payment methods, and delivery performance across Brazil.
In this step, get ready to:
- Write revenue analysis queries using JOINs and aggregations.
- Write delivery performance queries using date arithmetic.
- Run the queries and review key findings.
Write the revenue analysis function
A clean dataset is only useful if you ask the right questions. The first set of queries focuses on revenue: how much money is coming in each month, which payment methods customers prefer, and which product categories drive the most sales.
- In analysis.py, find the end of the explore_and_clean(conn) function (the return delivered line).
- Add the following run_revenue_analysis function below it:
def run_revenue_analysis(conn):
"""Run revenue-focused SQL queries."""
print("\n" + "=" * 60)
print("REVENUE ANALYSIS")
print("=" * 60)
monthly_revenue_query = """
SELECT
strftime('%Y-%m', o.order_purchase_timestamp) AS year_month,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY year_month
ORDER BY year_month
"""
monthly_revenue = pd.read_sql_query(monthly_revenue_query, conn)
print(f"\nMonthly revenue data: {len(monthly_revenue)} months")
print(f"Total revenue: R${monthly_revenue['total_revenue'].sum():,.2f}")
payment_type_query = """
SELECT
op.payment_type,
COUNT(*) AS transaction_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue,
ROUND(AVG(op.payment_value), 2) AS avg_value
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY op.payment_type
ORDER BY total_revenue DESC
"""
payment_types = pd.read_sql_query(payment_type_query, conn)
print("\nRevenue by payment type:")
print(payment_types.to_string(index=False))
top_categories_query = """
SELECT
COALESCE(ct.product_category_name_english, 'Unknown') AS category,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN category_translation ct
ON oi.product_id IN (
SELECT product_id FROM order_items
)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY category
ORDER BY total_revenue DESC
LIMIT 10
"""
return monthly_revenue, payment_typesWhat does this function do?
This function runs three SQL queries against your cleaned data, each answering a different business question.
- monthly_revenue_query groups total revenue by year-month using strftime('%Y-%m', ...). It JOINs the orders and order_payments tables so each order is matched to its payment value.
- payment_type_query breaks down revenue and transaction count by payment method (credit card, boleto, voucher, debit card). This tells you how customers prefer to pay.
- top_categories_query identifies the top 10 product categories by revenue. It uses a LEFT JOIN to the category_translation table to get English names instead of Portuguese.
Every query filters to order_status = 'delivered' and non-null delivery dates, matching the cleaning decisions you documented in the previous step.
What is a JOIN?
A JOIN combines rows from two tables based on a matching column. For example, orders JOIN order_payments ON o.order_id = op.order_id pairs each order with its payment record.
A LEFT JOIN keeps all rows from the left table even if there's no match in the right table. The COALESCE(..., 'Unknown') fills in a fallback value for any unmatched rows.
You won't see a change yet. The next chunk wires this function into main() so you can run it.
Write the delivery performance function
Revenue tells you how much money is coming in. Delivery performance tells you whether customers are happy getting their orders. These two dimensions together paint a much richer picture for stakeholders.
- In analysis.py, find the end of the run_revenue_analysis function you just added (the return monthly_revenue, payment_types line).
- Add the following run_delivery_analysis function below it:
def run_delivery_analysis(conn):
"""Run delivery performance SQL queries."""
print("\n" + "=" * 60)
print("DELIVERY PERFORMANCE ANALYSIS")
print("=" * 60)
delivery_by_state_query = """
SELECT
c.customer_state,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(AVG(
JULIANDAY(o.order_delivered_customer_date) -
JULIANDAY(o.order_purchase_timestamp)
), 1) AS avg_delivery_days
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 order_count >= 100
ORDER BY avg_delivery_days DESC
"""
delivery_by_state = pd.read_sql_query(delivery_by_state_query, conn)
print(f"\nDelivery time by state ({len(delivery_by_state)} states with 100+ orders):")
print(delivery_by_state.head(10).to_string(index=False))
ontime_query = """
SELECT
CASE
WHEN order_delivered_customer_date <= order_estimated_delivery_date
THEN 'On Time'
ELSE 'Late'
END AS delivery_status,
COUNT(*) AS order_count
FROM orders
WHERE order_status = 'delivered'
AND order_delivered_customer_date IS NOT NULL
AND order_estimated_delivery_date IS NOT NULL
GROUP BY delivery_status
"""
ontime = pd.read_sql_query(ontime_query, conn)
total = ontime["order_count"].sum()
for _, row in ontime.iterrows():
pct = row["order_count"] / total * 100
print(f"{row['delivery_status']}: {row['order_count']} orders ({pct:.1f}%)")
return delivery_by_state, ontimeWhat does this function do?
- delivery_by_state_query calculates average delivery time per Brazilian state. It uses JULIANDAY() to convert dates into numbers so you can subtract them and get the difference in days. The HAVING order_count >= 100 filter drops states with too few orders to be statistically meaningful.
- ontime_query uses a CASE WHEN expression to label each order as "On Time" or "Late" by comparing the actual delivery date to the estimated delivery date. The loop at the bottom prints each category with its percentage.
What is JULIANDAY?
JULIANDAY() is a SQLite function that converts a date string into a floating-point number representing the number of days since a fixed reference point. Subtracting two JULIANDAY() values gives you the exact number of days between two dates.
You won't see a change yet. The next chunk updates main() to call both new functions.
Wire up main and run the analysis
Both query functions are defined but nothing calls them yet. You need to update main() to run them and store their return values as pandas DataFrames.
- Scroll down to the main() function at the bottom of analysis.py.
- Find this code:
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
conn.close()- Replace the entire main() function with this updated version:
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
monthly_revenue, payment_types = run_revenue_analysis(conn)
delivery_by_state, ontime = run_delivery_analysis(conn)
conn.close()What changed?
Two new lines call your revenue and delivery functions, storing each return value as a pair of DataFrames. These variables (monthly_revenue, payment_types, delivery_by_state, ontime) will be used in a later step to generate visualizations.
- Save analysis.py.
- Run the script by entering this command in your terminal:
python analysis.py- You should see the cleaning summary from Step 2, followed by new sections: a REVENUE ANALYSIS block showing monthly revenue data and a payment type breakdown, then a DELIVERY PERFORMANCE ANALYSIS block showing delivery times by state and on-time vs late percentages.
Seeing a NameError or OperationalError?
- If you see NameError: name 'run_revenue_analysis' is not defined, make sure the new functions are defined above main() in the file. Python reads top to bottom, so main() can only call functions that are already defined.
- If you see an OperationalError mentioning a missing table, make sure olist.db exists in the same folder as analysis.py. Re-run load_database.py if needed.
- Check that your SQL strings use triple quotes (""") and that each query's column names match the table schema exactly.
Help me debug my SQL query errors in analysis.py
✔️ Awesome, I've got everything!
Great. Double check you've saved analysis.py and that both the revenue and delivery sections printed to your terminal.
ⓧ I'd like to double check the full code
Here is the full analysis.py at this point. Compare your file against it:
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
import os
import datetime as dt
sns.set_theme(style="whitegrid")
os.makedirs("outputs", exist_ok=True)
def get_connection():
"""Connect to the Olist SQLite database."""
return sqlite3.connect("olist.db")
def explore_and_clean(conn):
"""Explore data quality and create cleaned dataset."""
print("=" * 60)
print("DATA EXPLORATION AND CLEANING")
print("=" * 60)
tables = ["customers", "orders", "order_items", "order_payments", "order_reviews"]
for table in tables:
df = pd.read_sql_query(f"SELECT COUNT(*) as row_count FROM {table}", conn)
print(f"{table}: {df['row_count'].iloc[0]} rows")
total_orders = pd.read_sql_query("SELECT COUNT(*) as cnt FROM orders", conn)["cnt"].iloc[0]
delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_status = 'delivered'", conn
)["cnt"].iloc[0]
print(f"\nTotal orders: {total_orders}")
print(f"Delivered orders: {delivered} ({delivered / total_orders * 100:.1f}%)")
print(f"Non-delivered orders removed: {total_orders - delivered}")
null_approved = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_approved_at IS NULL", conn
)["cnt"].iloc[0]
print(f"Orders with null approval date: {null_approved}")
null_delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders "
"WHERE order_status = 'delivered' AND order_delivered_customer_date IS NULL",
conn,
)["cnt"].iloc[0]
print(f"Delivered orders with null delivery date: {null_delivered}")
print("\nCleaning decision: Keep only delivered orders with non-null delivery dates.")
print("Rationale: Only completed orders have full lifecycle data for analysis.")
return delivered
def run_revenue_analysis(conn):
"""Run revenue-focused SQL queries."""
print("\n" + "=" * 60)
print("REVENUE ANALYSIS")
print("=" * 60)
monthly_revenue_query = """
SELECT
strftime('%Y-%m', o.order_purchase_timestamp) AS year_month,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY year_month
ORDER BY year_month
"""
monthly_revenue = pd.read_sql_query(monthly_revenue_query, conn)
print(f"\nMonthly revenue data: {len(monthly_revenue)} months")
print(f"Total revenue: R${monthly_revenue['total_revenue'].sum():,.2f}")
payment_type_query = """
SELECT
op.payment_type,
COUNT(*) AS transaction_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue,
ROUND(AVG(op.payment_value), 2) AS avg_value
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY op.payment_type
ORDER BY total_revenue DESC
"""
payment_types = pd.read_sql_query(payment_type_query, conn)
print("\nRevenue by payment type:")
print(payment_types.to_string(index=False))
top_categories_query = """
SELECT
COALESCE(ct.product_category_name_english, 'Unknown') AS category,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN category_translation ct
ON oi.product_id IN (
SELECT product_id FROM order_items
)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY category
ORDER BY total_revenue DESC
LIMIT 10
"""
return monthly_revenue, payment_types
def run_delivery_analysis(conn):
"""Run delivery performance SQL queries."""
print("\n" + "=" * 60)
print("DELIVERY PERFORMANCE ANALYSIS")
print("=" * 60)
delivery_by_state_query = """
SELECT
c.customer_state,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(AVG(
JULIANDAY(o.order_delivered_customer_date) -
JULIANDAY(o.order_purchase_timestamp)
), 1) AS avg_delivery_days
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 order_count >= 100
ORDER BY avg_delivery_days DESC
"""
delivery_by_state = pd.read_sql_query(delivery_by_state_query, conn)
print(f"\nDelivery time by state ({len(delivery_by_state)} states with 100+ orders):")
print(delivery_by_state.head(10).to_string(index=False))
ontime_query = """
SELECT
CASE
WHEN order_delivered_customer_date <= order_estimated_delivery_date
THEN 'On Time'
ELSE 'Late'
END AS delivery_status,
COUNT(*) AS order_count
FROM orders
WHERE order_status = 'delivered'
AND order_delivered_customer_date IS NOT NULL
AND order_estimated_delivery_date IS NOT NULL
GROUP BY delivery_status
"""
ontime = pd.read_sql_query(ontime_query, conn)
total = ontime["order_count"].sum()
for _, row in ontime.iterrows():
pct = row["order_count"] / total * 100
print(f"{row['delivery_status']}: {row['order_count']} orders ({pct:.1f}%)")
return delivery_by_state, ontime
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
monthly_revenue, payment_types = run_revenue_analysis(conn)
delivery_by_state, ontime = run_delivery_analysis(conn)
conn.close()
if __name__ == "__main__":
main()Your queries have surfaced revenue trends, payment breakdowns, and delivery performance across Brazil. Next up, you'll use these DataFrames to perform RFM customer segmentation and score every customer in the database.
Perform RFM Customer Segmentation
Your analysis script can now pull revenue trends and delivery performance from the database. Those queries answer important business questions, but they treat every customer the same.
Not all customers are equal. Some buy frequently and spend big. Others placed one order months ago and never came back. RFM segmentation is a technique that scores each customer on three dimensions (Recency, Frequency, Monetary) and groups them into actionable segments like Champions, At Risk, and Lost.
In this step, get ready to:
- Calculate RFM values per customer using a CTE-based SQL query.
- Score and segment customers into actionable groups.
- Generate segment summary statistics showing the business value of each group.
Calculate RFM values per customer
RFM analysis measures three things about each customer: how recently they purchased (Recency), how often they purchased (Frequency), and how much they spent (Monetary). Together, these three numbers tell you more about customer value than any single metric can.
Why customer_unique_id instead of customer_id?
The Olist dataset has a quirk. The same real-world customer can appear with different customer_id values across separate orders. The customer_unique_id field is the true identifier that links a person across all their orders.
Using customer_id would undercount repeat purchases and make every customer look like a one-time buyer.
- In analysis.py, find the main() function. Add the following new function directly above it:
def run_rfm_analysis(conn):
"""Perform RFM customer segmentation."""
print("\n" + "=" * 60)
print("RFM CUSTOMER SEGMENTATION")
print("=" * 60)
# SQL query using CTEs to compute recency, frequency, monetary per customer
rfm_query = """
WITH customer_orders AS (
SELECT
c.customer_unique_id,
o.order_id,
o.order_purchase_timestamp,
op.payment_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
),
snapshot AS (
SELECT DATE(MAX(order_purchase_timestamp), '+1 day') AS snapshot_date
FROM customer_orders
)
SELECT
co.customer_unique_id,
CAST(JULIANDAY(s.snapshot_date) - JULIANDAY(MAX(co.order_purchase_timestamp)) AS INTEGER) AS recency,
COUNT(DISTINCT co.order_id) AS frequency,
ROUND(SUM(co.payment_value), 2) AS monetary
FROM customer_orders co, snapshot s
GROUP BY co.customer_unique_id
HAVING monetary > 0
"""
rfm = pd.read_sql_query(rfm_query, conn)
print(f"\nTotal unique customers: {len(rfm)}")
print(f"Recency range: {rfm['recency'].min()} - {rfm['recency'].max()} days")
print(f"Frequency range: {rfm['frequency'].min()} - {rfm['frequency'].max()} orders")
print(f"Monetary range: R${rfm['monetary'].min():.2f} - R${rfm['monetary'].max():.2f}")What does this query do?
This SQL query uses two CTEs (Common Table Expressions) to build the RFM values step by step.
- customer_orders joins orders, customers, and payments into one combined view, filtered to delivered orders only.
- snapshot sets the reference date to one day after the last order in the dataset. Recency is measured from this date.
- The final SELECT groups by customer_unique_id and calculates recency (days since last purchase), frequency (distinct order count), and monetary (total spend).
- HAVING monetary > 0 filters out any customers with zero total spend.
You won't see a change yet. The next substep adds the scoring logic that turns these raw values into actionable segments.
Score and segment customers
Raw recency, frequency, and monetary values are hard to compare directly. A customer with recency of 30 days and monetary of R$500 could be great or terrible depending on the dataset's distribution.
Quintile scoring solves this by ranking each customer relative to all others and assigning a score from 1 (lowest) to 5 (highest). Then conditional logic maps those scores into named segments.
- In analysis.py, add the following code directly below the print(f"Monetary range:...") line you just added, still inside the run_rfm_analysis function:
# Score each RFM dimension into quintiles (1-5)
# Lower recency = more recent = better, so labels are reversed
rfm["R_Score"] = pd.qcut(
rfm["recency"].rank(method="first", ascending=True),
q=5,
labels=[5, 4, 3, 2, 1],
).astype(int)
# Higher frequency = better, so labels go 1 to 5
rfm["F_Score"] = pd.qcut(
rfm["frequency"].rank(method="first", ascending=True),
q=5,
labels=[1, 2, 3, 4, 5],
).astype(int)
# Higher monetary = better, so labels go 1 to 5
rfm["M_Score"] = pd.qcut(
rfm["monetary"].rank(method="first", ascending=True),
q=5,
labels=[1, 2, 3, 4, 5],
).astype(int)
# Map score combinations to named segments
def assign_segment(row):
r, f, m = row["R_Score"], row["F_Score"], row["M_Score"]
if r >= 4 and f >= 4 and m >= 4:
return "Champions"
if r >= 3 and f >= 3:
return "Loyal Customers"
if m >= 4:
return "Big Spenders"
if r <= 2 and f >= 3:
return "At Risk"
if r <= 2 and f <= 2:
return "Lost"
return "Potential"
rfm["Segment"] = rfm.apply(assign_segment, axis=1)What does this code do?
pd.qcut divides customers into 5 equal-sized bins (quintiles). The .rank(method="first") call is a workaround for tied values. Many customers share the same frequency (like 1 order), so pd.qcut on raw values would fail because it cannot split ties evenly into bins. Ranking first gives every row a unique position.
For recency, the labels are reversed ([5, 4, 3, 2, 1]) because lower recency means a more recent purchase, which is better. For frequency and monetary, the labels go [1, 2, 3, 4, 5] because higher values are better.
The assign_segment function uses conditional logic to label each customer. The order of the conditions matters. Champions are checked first (all three scores high), then Loyal Customers, Big Spenders, At Risk, and Lost. Anyone who does not match gets labeled Potential.
You won't see a change yet. The next substep adds the summary statistics and connects the function to main().
Generate segment summary statistics
Segment labels on their own are not enough for a business stakeholder. You need summary numbers that show the customer count, average spending, and total revenue per segment. This is what turns a technical analysis into an actionable recommendation.
- In analysis.py, add the following code directly below the rfm["Segment"] = rfm.apply(assign_segment, axis=1) line, still inside the run_rfm_analysis function:
# Compute summary statistics per segment
segment_summary = (
rfm.groupby("Segment")
.agg(
customers=("customer_unique_id", "count"),
avg_recency=("recency", "mean"),
avg_frequency=("frequency", "mean"),
avg_monetary=("monetary", "mean"),
total_revenue=("monetary", "sum"),
)
.round(2)
.sort_values("total_revenue", ascending=False)
)
print("\nSegment Summary:")
print(segment_summary.to_string())
# Print percentage breakdown for each segment
total_customers = len(rfm)
total_revenue = rfm["monetary"].sum()
for segment_name, row in segment_summary.iterrows():
cust_pct = row["customers"] / total_customers * 100
rev_pct = row["total_revenue"] / total_revenue * 100
print(f"{segment_name}: {row['customers']} customers ({cust_pct:.1f}%), "
f"R${row['total_revenue']:,.2f} revenue ({rev_pct:.1f}%)")
return rfm, segment_summaryWhat does this code do?
The groupby("Segment") groups all customers by their segment label. The .agg() call computes five metrics for each group: customer count, average recency, average frequency, average monetary value, and total revenue.
The print loop calculates what percentage of total customers and total revenue each segment represents. This is the key insight for stakeholders. A small percentage of Champions might drive a large share of revenue.
Now you need to connect this function to your pipeline. The main() function needs to call run_rfm_analysis and store its results.
- In the main() function, find this line:
delivery_by_state, ontime = run_delivery_analysis(conn)
conn.close()- Add the run_rfm_analysis call between the delivery analysis line and conn.close(), so the block looks like this:
delivery_by_state, ontime = run_delivery_analysis(conn)
rfm, segment_summary = run_rfm_analysis(conn)
conn.close()- Save analysis.py.
- Run the script by entering this command in the terminal:
python analysis.py- Scroll down in the terminal output past the cleaning, revenue, and delivery sections. You should see a new RFM CUSTOMER SEGMENTATION section showing the total unique customers, recency/frequency/monetary ranges, a segment summary table, and a per-segment percentage breakdown.
What should I see?
The segment summary table lists each segment with its customer count, average recency, average frequency, average monetary value, and total revenue. The Champions segment should have the highest average monetary value. The Lost segment should have the highest average recency (most days since last purchase).
The percentage breakdown below the table shows how many customers and how much revenue each segment accounts for. This is the insight that makes the analysis valuable to a stakeholder.
Seeing a ValueError from qcut?
If you see an error about bin edges not being unique, double-check that you are calling .rank(method="first", ascending=True) before passing the values to pd.qcut. The ranking step ensures every value is unique, which prevents the bin-edge error.
Also verify the indentation of all the new code. Every line inside run_rfm_analysis should be indented by 4 spaces. The assign_segment function is nested inside run_rfm_analysis, so its body should be indented by 8 spaces.
Help me debug my RFM scoring in analysis.py
✔️ Awesome, I've got everything!
Great. Double check you have saved analysis.py.
ⓧ I'd like to double check the full code
Here is the full analysis.py file up to this point. Compare yours against it:
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
import os
import datetime as dt
sns.set_theme(style="whitegrid")
os.makedirs("outputs", exist_ok=True)
def get_connection():
"""Connect to the Olist SQLite database."""
return sqlite3.connect("olist.db")
def explore_and_clean(conn):
"""Explore data quality and create cleaned dataset."""
print("=" * 60)
print("DATA EXPLORATION AND CLEANING")
print("=" * 60)
tables = ["customers", "orders", "order_items", "order_payments", "order_reviews"]
for table in tables:
df = pd.read_sql_query(f"SELECT COUNT(*) as row_count FROM {table}", conn)
print(f"{table}: {df['row_count'].iloc[0]} rows")
total_orders = pd.read_sql_query("SELECT COUNT(*) as cnt FROM orders", conn)["cnt"].iloc[0]
delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_status = 'delivered'", conn
)["cnt"].iloc[0]
print(f"\nTotal orders: {total_orders}")
print(f"Delivered orders: {delivered} ({delivered / total_orders * 100:.1f}%)")
print(f"Non-delivered orders removed: {total_orders - delivered}")
null_approved = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_approved_at IS NULL", conn
)["cnt"].iloc[0]
print(f"Orders with null approval date: {null_approved}")
null_delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders "
"WHERE order_status = 'delivered' AND order_delivered_customer_date IS NULL",
conn,
)["cnt"].iloc[0]
print(f"Delivered orders with null delivery date: {null_delivered}")
print("\nCleaning decision: Keep only delivered orders with non-null delivery dates.")
print("Rationale: Only completed orders have full lifecycle data for analysis.")
return delivered
def run_revenue_analysis(conn):
"""Run revenue-focused SQL queries."""
print("\n" + "=" * 60)
print("REVENUE ANALYSIS")
print("=" * 60)
monthly_revenue_query = """
SELECT
strftime('%Y-%m', o.order_purchase_timestamp) AS year_month,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY year_month
ORDER BY year_month
"""
monthly_revenue = pd.read_sql_query(monthly_revenue_query, conn)
print(f"\nMonthly revenue data: {len(monthly_revenue)} months")
print(f"Total revenue: R${monthly_revenue['total_revenue'].sum():,.2f}")
payment_type_query = """
SELECT
op.payment_type,
COUNT(*) AS transaction_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue,
ROUND(AVG(op.payment_value), 2) AS avg_value
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY op.payment_type
ORDER BY total_revenue DESC
"""
payment_types = pd.read_sql_query(payment_type_query, conn)
print("\nRevenue by payment type:")
print(payment_types.to_string(index=False))
top_categories_query = """
SELECT
COALESCE(ct.product_category_name_english, 'Unknown') AS category,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN category_translation ct
ON oi.product_id IN (
SELECT product_id FROM order_items
)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY category
ORDER BY total_revenue DESC
LIMIT 10
"""
return monthly_revenue, payment_types
def run_delivery_analysis(conn):
"""Run delivery performance SQL queries."""
print("\n" + "=" * 60)
print("DELIVERY PERFORMANCE ANALYSIS")
print("=" * 60)
delivery_by_state_query = """
SELECT
c.customer_state,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(AVG(
JULIANDAY(o.order_delivered_customer_date) -
JULIANDAY(o.order_purchase_timestamp)
), 1) AS avg_delivery_days
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 order_count >= 100
ORDER BY avg_delivery_days DESC
"""
delivery_by_state = pd.read_sql_query(delivery_by_state_query, conn)
print(f"\nDelivery time by state ({len(delivery_by_state)} states with 100+ orders):")
print(delivery_by_state.head(10).to_string(index=False))
ontime_query = """
SELECT
CASE
WHEN order_delivered_customer_date <= order_estimated_delivery_date
THEN 'On Time'
ELSE 'Late'
END AS delivery_status,
COUNT(*) AS order_count
FROM orders
WHERE order_status = 'delivered'
AND order_delivered_customer_date IS NOT NULL
AND order_estimated_delivery_date IS NOT NULL
GROUP BY delivery_status
"""
ontime = pd.read_sql_query(ontime_query, conn)
total = ontime["order_count"].sum()
for _, row in ontime.iterrows():
pct = row["order_count"] / total * 100
print(f"{row['delivery_status']}: {row['order_count']} orders ({pct:.1f}%)")
return delivery_by_state, ontime
def run_rfm_analysis(conn):
"""Perform RFM customer segmentation."""
print("\n" + "=" * 60)
print("RFM CUSTOMER SEGMENTATION")
print("=" * 60)
rfm_query = """
WITH customer_orders AS (
SELECT
c.customer_unique_id,
o.order_id,
o.order_purchase_timestamp,
op.payment_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
),
snapshot AS (
SELECT DATE(MAX(order_purchase_timestamp), '+1 day') AS snapshot_date
FROM customer_orders
)
SELECT
co.customer_unique_id,
CAST(JULIANDAY(s.snapshot_date) - JULIANDAY(MAX(co.order_purchase_timestamp)) AS INTEGER) AS recency,
COUNT(DISTINCT co.order_id) AS frequency,
ROUND(SUM(co.payment_value), 2) AS monetary
FROM customer_orders co, snapshot s
GROUP BY co.customer_unique_id
HAVING monetary > 0
"""
rfm = pd.read_sql_query(rfm_query, conn)
print(f"\nTotal unique customers: {len(rfm)}")
print(f"Recency range: {rfm['recency'].min()} - {rfm['recency'].max()} days")
print(f"Frequency range: {rfm['frequency'].min()} - {rfm['frequency'].max()} orders")
print(f"Monetary range: R${rfm['monetary'].min():.2f} - R${rfm['monetary'].max():.2f}")
rfm["R_Score"] = pd.qcut(
rfm["recency"].rank(method="first", ascending=True),
q=5,
labels=[5, 4, 3, 2, 1],
).astype(int)
rfm["F_Score"] = pd.qcut(
rfm["frequency"].rank(method="first", ascending=True),
q=5,
labels=[1, 2, 3, 4, 5],
).astype(int)
rfm["M_Score"] = pd.qcut(
rfm["monetary"].rank(method="first", ascending=True),
q=5,
labels=[1, 2, 3, 4, 5],
).astype(int)
def assign_segment(row):
r, f, m = row["R_Score"], row["F_Score"], row["M_Score"]
if r >= 4 and f >= 4 and m >= 4:
return "Champions"
if r >= 3 and f >= 3:
return "Loyal Customers"
if m >= 4:
return "Big Spenders"
if r <= 2 and f >= 3:
return "At Risk"
if r <= 2 and f <= 2:
return "Lost"
return "Potential"
rfm["Segment"] = rfm.apply(assign_segment, axis=1)
segment_summary = (
rfm.groupby("Segment")
.agg(
customers=("customer_unique_id", "count"),
avg_recency=("recency", "mean"),
avg_frequency=("frequency", "mean"),
avg_monetary=("monetary", "mean"),
total_revenue=("monetary", "sum"),
)
.round(2)
.sort_values("total_revenue", ascending=False)
)
print("\nSegment Summary:")
print(segment_summary.to_string())
total_customers = len(rfm)
total_revenue = rfm["monetary"].sum()
for segment_name, row in segment_summary.iterrows():
cust_pct = row["customers"] / total_customers * 100
rev_pct = row["total_revenue"] / total_revenue * 100
print(f"{segment_name}: {row['customers']} customers ({cust_pct:.1f}%), "
f"R${row['total_revenue']:,.2f} revenue ({rev_pct:.1f}%)")
return rfm, segment_summary
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
monthly_revenue, payment_types = run_revenue_analysis(conn)
delivery_by_state, ontime = run_delivery_analysis(conn)
rfm, segment_summary = run_rfm_analysis(conn)
conn.close()
if __name__ == "__main__":
main()Your customers are now scored and segmented. Next up, you will turn these numbers into publication-quality visualizations and write a stakeholder-facing README that communicates your findings.
Visualize Findings and Finalize the Repository
Your analysis script now cleans data, runs business queries, and segments customers. The numbers are sitting in DataFrames and printed to the terminal, but a hiring manager will never read raw terminal output.
In this step, you will turn those DataFrames into publication-quality charts, write a professional README that tells the story behind the data, and package the whole project so anyone can clone and run it.
In this step, get ready to:
- Create 4 charts visualizing revenue, delivery, and customer segments.
- Write a stakeholder-facing README with findings and recommendations.
- Prepare the repository for GitHub with a .gitignore and requirements.txt.
Create 4 publication-quality charts
Charts are how data projects communicate with stakeholders. A well-labeled chart conveys a finding in seconds, while a table of numbers takes minutes to parse.
- In analysis.py, scroll to the bottom of the file and find the main() function.
- Add the following create_visualizations function directly above main():
def create_visualizations(monthly_revenue, delivery_by_state, rfm, segment_summary):
"""Create and save publication-quality charts."""
print("\n" + "=" * 60)
print("CREATING VISUALIZATIONS")
print("=" * 60)
# Chart 1: Monthly revenue trend (line chart)
fig, ax = plt.subplots(figsize=(12, 5))
ax.plot(
range(len(monthly_revenue)),
monthly_revenue["total_revenue"],
marker="o",
linewidth=2,
color="#2196F3",
)
ax.set_xlabel("Month")
ax.set_ylabel("Revenue (R$)")
ax.set_title("Monthly Revenue Trend - Olist E-Commerce")
tick_positions = range(0, len(monthly_revenue), 3)
ax.set_xticks(tick_positions)
ax.set_xticklabels(
monthly_revenue["year_month"].iloc[list(tick_positions)], rotation=45
)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))
plt.tight_layout()
plt.savefig("outputs/monthly_revenue_trend.png", dpi=150)
plt.close()
print("Saved: outputs/monthly_revenue_trend.png")
# Chart 2: Delivery performance by state (horizontal bar)
top_states = delivery_by_state.head(10)
fig, ax = plt.subplots(figsize=(10, 6))
bars = ax.barh(
top_states["customer_state"],
top_states["avg_delivery_days"],
color="#FF9800",
)
ax.set_xlabel("Average Delivery Days")
ax.set_ylabel("Customer State")
ax.set_title("Average Delivery Time by State (Top 10 Slowest)")
ax.invert_yaxis()
for bar, val in zip(bars, top_states["avg_delivery_days"]):
ax.text(bar.get_width() + 0.3, bar.get_y() + bar.get_height() / 2,
f"{val:.1f}d", va="center", fontsize=9)
plt.tight_layout()
plt.savefig("outputs/delivery_by_state.png", dpi=150)
plt.close()
print("Saved: outputs/delivery_by_state.png")
# Chart 3: RFM segment distribution (customer count per segment)
segment_counts = rfm["Segment"].value_counts().sort_values(ascending=True)
fig, ax = plt.subplots(figsize=(10, 6))
colors = {
"Champions": "#4CAF50",
"Loyal Customers": "#2196F3",
"Big Spenders": "#9C27B0",
"Potential": "#FF9800",
"At Risk": "#FF5722",
"Lost": "#9E9E9E",
}
bar_colors = [colors.get(seg, "#607D8B") for seg in segment_counts.index]
ax.barh(segment_counts.index, segment_counts.values, color=bar_colors)
ax.set_xlabel("Number of Customers")
ax.set_title("Customer Distribution by RFM Segment")
for i, val in enumerate(segment_counts.values):
ax.text(val + 100, i, str(val), va="center", fontsize=9)
plt.tight_layout()
plt.savefig("outputs/rfm_segment_distribution.png", dpi=150)
plt.close()
print("Saved: outputs/rfm_segment_distribution.png")
# Chart 4: Revenue contribution by customer segment
fig, ax = plt.subplots(figsize=(10, 6))
revenue_by_segment = segment_summary["total_revenue"].sort_values(ascending=True)
seg_colors = [colors.get(seg, "#607D8B") for seg in revenue_by_segment.index]
ax.barh(revenue_by_segment.index, revenue_by_segment.values, color=seg_colors)
ax.set_xlabel("Total Revenue (R$)")
ax.set_title("Revenue Contribution by Customer Segment")
ax.xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))
plt.tight_layout()
plt.savefig("outputs/revenue_by_segment.png", dpi=150)
plt.close()
print("Saved: outputs/revenue_by_segment.png")What does this function do?
This function creates 4 charts from the DataFrames your analysis functions return:
- Chart 1 is a line chart showing monthly revenue over time. It uses plt.FuncFormatter to display currency values on the y-axis.
- Chart 2 is a horizontal bar chart ranking the 10 states with the slowest delivery times.
- Charts 3 and 4 are horizontal bar charts showing customer count and total revenue per RFM segment. They share a colors dictionary so each segment gets a consistent color across both charts.
Every chart is saved to the outputs/ folder as a PNG at 150 DPI. After saving, plt.close() frees the figure from memory.
Now you need to update main() to call this new function and print a completion summary.
- In analysis.py, find the existing main() function.
- Find this line:
rfm, segment_summary = run_rfm_analysis(conn)- Replace the entire main() function with this updated version that calls create_visualizations and prints a completion summary:
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
monthly_revenue, payment_types = run_revenue_analysis(conn)
delivery_by_state, ontime = run_delivery_analysis(conn)
rfm, segment_summary = run_rfm_analysis(conn)
create_visualizations(monthly_revenue, delivery_by_state, rfm, segment_summary)
conn.close()
print("\n" + "=" * 60)
print("ANALYSIS COMPLETE")
print("=" * 60)
print("All visualizations saved to outputs/ folder.")
print("Next: Write your README with findings and recommendations.")
if __name__ == "__main__":
main()What changed in main?
Two additions: the create_visualizations() call passes all four DataFrames to the new function, and five print() lines at the end confirm the pipeline ran successfully.
- Save analysis.py.
- Run the complete analysis pipeline by running this command:
python analysis.py- Check the outputs/ folder by running:
ls outputs/You should see 4 PNG files listed: monthly_revenue_trend.png, delivery_by_state.png, rfm_segment_distribution.png, and revenue_by_segment.png.
Not seeing 4 PNG files?
- Check the terminal output for error messages. A NameError means create_visualizations is not defined. Make sure the function is placed above main() in the file.
- If you see a TypeError, confirm that main() passes all four arguments to create_visualizations().
- Make sure you saved analysis.py before running the script.
Still stuck? Help me debug why my charts are not being saved to the outputs folder.
✔️ Awesome, I've got everything!
Great. Double check you have saved analysis.py before moving on.
ⓧ I'd like to double check the full code
Here is the complete analysis.py at this point:
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import seaborn as sns
import os
import datetime as dt
sns.set_theme(style="whitegrid")
os.makedirs("outputs", exist_ok=True)
def get_connection():
"""Connect to the Olist SQLite database."""
return sqlite3.connect("olist.db")
def explore_and_clean(conn):
"""Explore data quality and create cleaned dataset."""
print("=" * 60)
print("DATA EXPLORATION AND CLEANING")
print("=" * 60)
tables = ["customers", "orders", "order_items", "order_payments", "order_reviews"]
for table in tables:
df = pd.read_sql_query(f"SELECT COUNT(*) as row_count FROM {table}", conn)
print(f"{table}: {df['row_count'].iloc[0]} rows")
total_orders = pd.read_sql_query("SELECT COUNT(*) as cnt FROM orders", conn)["cnt"].iloc[0]
delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_status = 'delivered'", conn
)["cnt"].iloc[0]
print(f"\nTotal orders: {total_orders}")
print(f"Delivered orders: {delivered} ({delivered / total_orders * 100:.1f}%)")
print(f"Non-delivered orders removed: {total_orders - delivered}")
null_approved = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders WHERE order_approved_at IS NULL", conn
)["cnt"].iloc[0]
print(f"Orders with null approval date: {null_approved}")
null_delivered = pd.read_sql_query(
"SELECT COUNT(*) as cnt FROM orders "
"WHERE order_status = 'delivered' AND order_delivered_customer_date IS NULL",
conn,
)["cnt"].iloc[0]
print(f"Delivered orders with null delivery date: {null_delivered}")
print("\nCleaning decision: Keep only delivered orders with non-null delivery dates.")
print("Rationale: Only completed orders have full lifecycle data for analysis.")
return delivered
def run_revenue_analysis(conn):
"""Run revenue-focused SQL queries."""
print("\n" + "=" * 60)
print("REVENUE ANALYSIS")
print("=" * 60)
monthly_revenue_query = """
SELECT
strftime('%Y-%m', o.order_purchase_timestamp) AS year_month,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY year_month
ORDER BY year_month
"""
monthly_revenue = pd.read_sql_query(monthly_revenue_query, conn)
print(f"\nMonthly revenue data: {len(monthly_revenue)} months")
print(f"Total revenue: R${monthly_revenue['total_revenue'].sum():,.2f}")
payment_type_query = """
SELECT
op.payment_type,
COUNT(*) AS transaction_count,
ROUND(SUM(op.payment_value), 2) AS total_revenue,
ROUND(AVG(op.payment_value), 2) AS avg_value
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY op.payment_type
ORDER BY total_revenue DESC
"""
payment_types = pd.read_sql_query(payment_type_query, conn)
print("\nRevenue by payment type:")
print(payment_types.to_string(index=False))
top_categories_query = """
SELECT
COALESCE(ct.product_category_name_english, 'Unknown') AS category,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(SUM(oi.price + oi.freight_value), 2) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN category_translation ct
ON oi.product_id IN (
SELECT product_id FROM order_items
)
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
GROUP BY category
ORDER BY total_revenue DESC
LIMIT 10
"""
return monthly_revenue, payment_types
def run_delivery_analysis(conn):
"""Run delivery performance SQL queries."""
print("\n" + "=" * 60)
print("DELIVERY PERFORMANCE ANALYSIS")
print("=" * 60)
delivery_by_state_query = """
SELECT
c.customer_state,
COUNT(DISTINCT o.order_id) AS order_count,
ROUND(AVG(
JULIANDAY(o.order_delivered_customer_date) -
JULIANDAY(o.order_purchase_timestamp)
), 1) AS avg_delivery_days
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 order_count >= 100
ORDER BY avg_delivery_days DESC
"""
delivery_by_state = pd.read_sql_query(delivery_by_state_query, conn)
print(f"\nDelivery time by state ({len(delivery_by_state)} states with 100+ orders):")
print(delivery_by_state.head(10).to_string(index=False))
ontime_query = """
SELECT
CASE
WHEN order_delivered_customer_date <= order_estimated_delivery_date
THEN 'On Time'
ELSE 'Late'
END AS delivery_status,
COUNT(*) AS order_count
FROM orders
WHERE order_status = 'delivered'
AND order_delivered_customer_date IS NOT NULL
AND order_estimated_delivery_date IS NOT NULL
GROUP BY delivery_status
"""
ontime = pd.read_sql_query(ontime_query, conn)
total = ontime["order_count"].sum()
for _, row in ontime.iterrows():
pct = row["order_count"] / total * 100
print(f"{row['delivery_status']}: {row['order_count']} orders ({pct:.1f}%)")
return delivery_by_state, ontime
def run_rfm_analysis(conn):
"""Perform RFM customer segmentation."""
print("\n" + "=" * 60)
print("RFM CUSTOMER SEGMENTATION")
print("=" * 60)
rfm_query = """
WITH customer_orders AS (
SELECT
c.customer_unique_id,
o.order_id,
o.order_purchase_timestamp,
op.payment_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_payments op ON o.order_id = op.order_id
WHERE o.order_status = 'delivered'
AND o.order_delivered_customer_date IS NOT NULL
),
snapshot AS (
SELECT DATE(MAX(order_purchase_timestamp), '+1 day') AS snapshot_date
FROM customer_orders
)
SELECT
co.customer_unique_id,
CAST(JULIANDAY(s.snapshot_date) - JULIANDAY(MAX(co.order_purchase_timestamp)) AS INTEGER) AS recency,
COUNT(DISTINCT co.order_id) AS frequency,
ROUND(SUM(co.payment_value), 2) AS monetary
FROM customer_orders co, snapshot s
GROUP BY co.customer_unique_id
HAVING monetary > 0
"""
rfm = pd.read_sql_query(rfm_query, conn)
print(f"\nTotal unique customers: {len(rfm)}")
print(f"Recency range: {rfm['recency'].min()} - {rfm['recency'].max()} days")
print(f"Frequency range: {rfm['frequency'].min()} - {rfm['frequency'].max()} orders")
print(f"Monetary range: R${rfm['monetary'].min():.2f} - R${rfm['monetary'].max():.2f}")
rfm["R_Score"] = pd.qcut(
rfm["recency"].rank(method="first", ascending=True),
q=5,
labels=[5, 4, 3, 2, 1],
).astype(int)
rfm["F_Score"] = pd.qcut(
rfm["frequency"].rank(method="first", ascending=True),
q=5,
labels=[1, 2, 3, 4, 5],
).astype(int)
rfm["M_Score"] = pd.qcut(
rfm["monetary"].rank(method="first", ascending=True),
q=5,
labels=[1, 2, 3, 4, 5],
).astype(int)
def assign_segment(row):
r, f, m = row["R_Score"], row["F_Score"], row["M_Score"]
if r >= 4 and f >= 4 and m >= 4:
return "Champions"
if r >= 3 and f >= 3:
return "Loyal Customers"
if m >= 4:
return "Big Spenders"
if r <= 2 and f >= 3:
return "At Risk"
if r <= 2 and f <= 2:
return "Lost"
return "Potential"
rfm["Segment"] = rfm.apply(assign_segment, axis=1)
segment_summary = (
rfm.groupby("Segment")
.agg(
customers=("customer_unique_id", "count"),
avg_recency=("recency", "mean"),
avg_frequency=("frequency", "mean"),
avg_monetary=("monetary", "mean"),
total_revenue=("monetary", "sum"),
)
.round(2)
.sort_values("total_revenue", ascending=False)
)
print("\nSegment Summary:")
print(segment_summary.to_string())
total_customers = len(rfm)
total_revenue = rfm["monetary"].sum()
for segment_name, row in segment_summary.iterrows():
cust_pct = row["customers"] / total_customers * 100
rev_pct = row["total_revenue"] / total_revenue * 100
print(f"{segment_name}: {row['customers']} customers ({cust_pct:.1f}%), "
f"R${row['total_revenue']:,.2f} revenue ({rev_pct:.1f}%)")
return rfm, segment_summary
def create_visualizations(monthly_revenue, delivery_by_state, rfm, segment_summary):
"""Create and save publication-quality charts."""
print("\n" + "=" * 60)
print("CREATING VISUALIZATIONS")
print("=" * 60)
fig, ax = plt.subplots(figsize=(12, 5))
ax.plot(
range(len(monthly_revenue)),
monthly_revenue["total_revenue"],
marker="o",
linewidth=2,
color="#2196F3",
)
ax.set_xlabel("Month")
ax.set_ylabel("Revenue (R$)")
ax.set_title("Monthly Revenue Trend - Olist E-Commerce")
tick_positions = range(0, len(monthly_revenue), 3)
ax.set_xticks(tick_positions)
ax.set_xticklabels(
monthly_revenue["year_month"].iloc[list(tick_positions)], rotation=45
)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))
plt.tight_layout()
plt.savefig("outputs/monthly_revenue_trend.png", dpi=150)
plt.close()
print("Saved: outputs/monthly_revenue_trend.png")
top_states = delivery_by_state.head(10)
fig, ax = plt.subplots(figsize=(10, 6))
bars = ax.barh(
top_states["customer_state"],
top_states["avg_delivery_days"],
color="#FF9800",
)
ax.set_xlabel("Average Delivery Days")
ax.set_ylabel("Customer State")
ax.set_title("Average Delivery Time by State (Top 10 Slowest)")
ax.invert_yaxis()
for bar, val in zip(bars, top_states["avg_delivery_days"]):
ax.text(bar.get_width() + 0.3, bar.get_y() + bar.get_height() / 2,
f"{val:.1f}d", va="center", fontsize=9)
plt.tight_layout()
plt.savefig("outputs/delivery_by_state.png", dpi=150)
plt.close()
print("Saved: outputs/delivery_by_state.png")
segment_counts = rfm["Segment"].value_counts().sort_values(ascending=True)
fig, ax = plt.subplots(figsize=(10, 6))
colors = {
"Champions": "#4CAF50",
"Loyal Customers": "#2196F3",
"Big Spenders": "#9C27B0",
"Potential": "#FF9800",
"At Risk": "#FF5722",
"Lost": "#9E9E9E",
}
bar_colors = [colors.get(seg, "#607D8B") for seg in segment_counts.index]
ax.barh(segment_counts.index, segment_counts.values, color=bar_colors)
ax.set_xlabel("Number of Customers")
ax.set_title("Customer Distribution by RFM Segment")
for i, val in enumerate(segment_counts.values):
ax.text(val + 100, i, str(val), va="center", fontsize=9)
plt.tight_layout()
plt.savefig("outputs/rfm_segment_distribution.png", dpi=150)
plt.close()
print("Saved: outputs/rfm_segment_distribution.png")
fig, ax = plt.subplots(figsize=(10, 6))
revenue_by_segment = segment_summary["total_revenue"].sort_values(ascending=True)
seg_colors = [colors.get(seg, "#607D8B") for seg in revenue_by_segment.index]
ax.barh(revenue_by_segment.index, revenue_by_segment.values, color=seg_colors)
ax.set_xlabel("Total Revenue (R$)")
ax.set_title("Revenue Contribution by Customer Segment")
ax.xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))
plt.tight_layout()
plt.savefig("outputs/revenue_by_segment.png", dpi=150)
plt.close()
print("Saved: outputs/revenue_by_segment.png")
def main():
"""Run the complete analysis pipeline."""
conn = get_connection()
explore_and_clean(conn)
monthly_revenue, payment_types = run_revenue_analysis(conn)
delivery_by_state, ontime = run_delivery_analysis(conn)
rfm, segment_summary = run_rfm_analysis(conn)
create_visualizations(monthly_revenue, delivery_by_state, rfm, segment_summary)
conn.close()
print("\n" + "=" * 60)
print("ANALYSIS COMPLETE")
print("=" * 60)
print("All visualizations saved to outputs/ folder.")
print("Next: Write your README with findings and recommendations.")
if __name__ == "__main__":
main()Write the business memo README
A README is the first thing a hiring manager sees when they open your repository. It should tell the story of your project in plain language so a non-technical reader understands the problem, methodology, and findings within 5 minutes.
- Open README.md in VS Code.
- Copy the following markdown into the file:
# E-Commerce Customer Analytics Pipeline
## Problem Statement
Understanding customer behavior is critical for e-commerce businesses to optimize marketing spend, improve retention, and increase revenue. This project analyzes 100,000+ real orders from a Brazilian e-commerce marketplace to uncover revenue trends, delivery performance patterns, and customer segments.
## Data Source
This project uses the [Brazilian E-Commerce Public Dataset by Olist](https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce), containing approximately 100k orders placed between 2016 and 2018 across 9 interconnected CSV files.
**Tables used in this analysis:**
- `customers` (99,441 rows) - Customer demographics and location
- `orders` (99,441 rows) - Order status and timestamps
- `order_items` (112,650 rows) - Products per order with pricing
- `order_payments` (103,886 rows) - Payment method and values
- `order_reviews` (99,224 rows) - Customer review scores
- `category_translation` (71 rows) - Product category English translations
## Methodology
1. **Data Loading**: Loaded 6 CSV files into a SQLite database using Python's built-in `sqlite3` module and `pandas`.
2. **Data Cleaning**: Filtered to delivered orders only with non-null delivery dates. Documented all cleaning decisions with rationale.
3. **Revenue Analysis**: SQL queries with JOINs and aggregations to analyze monthly revenue trends, payment type breakdown, and top product categories.
4. **Delivery Analysis**: Computed average delivery times by state and on-time delivery rates using date arithmetic in SQLite.
5. **RFM Segmentation**: Calculated Recency, Frequency, and Monetary values per customer using SQL CTEs, then scored and segmented customers in Python using quintile-based scoring.
6. **Visualization**: Created 4 publication-quality charts using matplotlib and seaborn.
## Key Findings
<!-- Update the numbers below with your actual analysis output -->
- **Revenue Trend**: Monthly revenue showed strong growth from late 2016 through mid-2018, with seasonal peaks.
- **Payment Methods**: Credit card is the dominant payment method, followed by boleto (bank slip).
- **Delivery Performance**: Average delivery times vary significantly by state. Northern and northeastern states experience longer delivery windows compared to southern states.
- **Customer Segments**: Champions (highest RFM scores) represent a small percentage of customers but contribute a disproportionately large share of total revenue.
## Business Recommendations
1. **Invest in Champion retention**: Champions generate outsized revenue. Implement loyalty programs and exclusive offers to keep them engaged.
2. **Re-engage At Risk customers**: These customers previously purchased frequently but have not returned recently. Targeted discount codes and personalized emails can recover this segment before they become Lost.
3. **Improve northern state logistics**: States with the longest delivery times correlate with lower order volumes. Partnering with regional carriers or exploring distribution centers in underserved areas could unlock growth.
## Limitations
- The dataset covers 2016-2018 and may not reflect current market conditions.
- Product category analysis is limited because the translation table does not cover all categories.
- RFM analysis treats each `customer_unique_id` as a single customer, but some may represent shared accounts.
- Monetary values are in Brazilian Reais (R$) and not adjusted for inflation.
## How to Reproduce
1. Clone this repository.
2. Download the [Olist dataset from Kaggle](https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce) and place the CSV files in a `data/` folder.
3. Create and activate a virtual environment:
```bash
python -m venv venv
source venv/bin/activate # macOS/Linux
venv\Scripts\activate # Windows
```
4. Install dependencies:
```bash
pip install -r requirements.txt
```
5. Load the database:
```bash
python load_database.py
```
6. Run the analysis:
```bash
python analysis.py
```
7. Charts are saved to the `outputs/` folder.What makes this README effective?
The structure follows a pattern hiring managers recognize: Problem, Data, Method, Findings, Recommendations, Limitations. Each section serves a specific audience.
Key Findings are written in plain language so a product manager can understand them without reading code. Business Recommendations tie directly to the RFM segments you built, showing you can translate analysis into action.
Personalize your numbers
Scroll back through your terminal output from running analysis.py. Replace the generic language in Key Findings with your actual percentages and counts. For example, replace the Champions finding with the exact customer percentage and revenue share from your RFM segment summary.
- Save README.md.
Prepare the repository for GitHub
Before pushing to GitHub, you need to exclude files that do not belong in version control. The CSV data files are too large for GitHub, the venv/ folder is environment-specific, and olist.db is a generated artifact.
- Create a .gitignore file in your ecommerce-customer-analytics folder by running:
touch .gitignore- Open .gitignore in VS Code and paste the following:
venv/
data/
olist.dbWhat does .gitignore do?
Any file or folder listed in .gitignore is excluded from Git tracking. This keeps your repository clean and prevents large data files or environment-specific folders from bloating the repo.
- Save .gitignore.
Next, you need a requirements.txt so anyone cloning your repo can install the exact same package versions you used.
- Generate requirements.txt from your current virtual environment by running:
pip freeze > requirements.txtWhat does pip freeze do?
The pip freeze command lists every installed package and its exact version in your virtual environment. Redirecting it to requirements.txt creates a file that someone else can use with pip install -r requirements.txt to replicate your environment.
- Verify requirements.txt was created by running:
cat requirements.txtYou should see a list of packages with pinned versions, including pandas, matplotlib, and seaborn.
requirements.txt is empty?
- Make sure your virtual environment is activated. You should see (venv) at the beginning of your terminal prompt.
- If the virtual environment is not active, activate it and run pip freeze > requirements.txt again.
Still stuck? Help me figure out why pip freeze is not listing my packages.
- Confirm your final project structure by running:
lsYou should see analysis.py, load_database.py, README.md, requirements.txt, .gitignore, and the data/, outputs/, and venv/ folders.
Secret mission
Cohort Retention Analysis
Your RFM segmentation shows who your best customers are right now. But how well does the business actually retain new customers over time? Build a monthly cohort retention analysis with a heatmap visualization to find out.
Clean Up Your Resources
Clean Up Your Resources
This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources on disk, pause them to come back later, or delete them entirely.
Resources you used:
- olist.db SQLite database file (local, no ongoing cost).
- PNG chart files in the outputs/ folder.
- Python virtual environment in the venv/ folder.
Keep everything running
No action needed. Choose this if you are still actively building or plan to extend the project soon.
- All files live inside the ecommerce-customer-analytics folder on your Desktop. There are no cloud resources and no ongoing charges.
- Your virtual environment, database, scripts, and chart outputs will stay on disk exactly as they are.
Pause - I'll come back to this later
Deactivate the virtual environment to free up your terminal session. All files stay on disk so you can pick up where you left off.
- Deactivate the virtual environment by running:
deactivate- When you are ready to come back, navigate to the ecommerce-customer-analytics folder and reactivate the environment:
macOS
cd ~/Desktop/ecommerce-customer-analytics
source venv/bin/activateWindows
cd ~\Desktop\ecommerce-customer-analytics
venv\Scripts\activateDelete - I don't want to use this again
Remove the entire project folder to reclaim disk space. This deletes your scripts, database, virtual environment, downloaded CSV files, and chart outputs.
- If your virtual environment is still active, deactivate it first by running:
deactivate- Delete the entire project folder by running the command for your operating system:
macOS
cd ~/Desktop
rm -rf ecommerce-customer-analyticsWindows
cd ~\Desktop
rmdir /s /q ecommerce-customer-analytics- Confirm the folder is gone by running:
ls ~/DesktopYou should no longer see ecommerce-customer-analytics in the output.
Nice Work!
Nice Work!
Nice work! You just built a complete customer analytics pipeline, taking raw e-commerce data all the way to stakeholder-ready insights and publication-quality visualizations.
You've learned how to:
- Built a SQLite database from 6 real-world e-commerce tables, cleaned messy data with SQL, and documented every data quality decision for reproducibility.
- Wrote business-driven queries using JOINs, CTEs, and date functions to analyze revenue, delivery, and payments, then performed RFM customer segmentation in Python to score and label customers into actionable segments.
- Created 4 publication-quality visualizations and a stakeholder-facing README with a problem statement, methodology, key findings, and business recommendations.
- Secret Mission: Built a cohort retention heatmap revealing how customer repurchase behavior changes over time.
Ready to quiz yourself?