Analyze E-Commerce Data with Python

Clean, analyze, and visualize real e-commerce data to deliver business recommendations.

Introduction

30 Second Summary

Every online store sits on a goldmine of data about what customers buy, when they buy it, and how long deliveries take. The challenge is turning that raw data into clear recommendations that help the business grow.

In this project, you will analyze a real-world e-commerce dataset of 100,000+ orders using Python to uncover revenue trends, identify underperforming product categories, and deliver prioritized business recommendations in a professional notebook format.

What You'll Build

A complete end-to-end business analysis Jupyter Notebook that answers the question: "Which product categories should the company prioritize for growth, and where are delivery delays hurting revenue?"

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

  • A cleaned and merged dataset built from multiple CSV files, ready for analysis.
  • A set of data visualizations showing monthly revenue trends and product category performance.
  • A professional executive summary with 3 data-backed business recommendations that a stakeholder could act on.
  • Secret Mission: Build a customer segmentation analysis using RFM scoring to identify high-value vs. at-risk customers.

Are there any prerequisites?

Basic familiarity with Excel and spreadsheets is helpful so you understand concepts like rows, columns, and filtering. No Python experience is required.

Set Up Your Python Analysis Environment

Before you can analyze data, you need the right tools installed. Python has a rich ecosystem of data analysis libraries that handle everything from loading spreadsheets to creating charts.

In this step, you will install four Python packages, create a project folder, launch Jupyter Notebook in your browser, and download a real e-commerce dataset from Kaggle.

In this step, get ready to:
  • Install the Python analysis libraries (pandas, matplotlib, seaborn, Jupyter Notebook).
  • Create a project folder and launch Jupyter Notebook.
  • Download the Brazilian E-Commerce dataset from Kaggle.
Install the Python analysis libraries

You need four packages for this project. pandas handles data loading and manipulation. matplotlib and seaborn create charts. The notebook package gives you the Jupyter Notebook interface.

  • Open a terminal in VS Code by clicking Terminal in the top menu bar, then clicking New Terminal.
  • Install all four packages by running this command:
pip install pandas==2.3.3 matplotlib==3.10.9 seaborn==0.13.2 notebook

What does this command do?

The pip install command downloads and installs Python packages from the Python Package Index.

The == after each package name pins it to an exact version. This ensures your results match the project instructions.

  • Wait for the installation to finish. You will see a line that says Successfully installed followed by the package names.
  • Confirm pandas installed correctly by running:
pip show pandas
  • Look for the Version line in the output. It should say 2.3.3.

Seeing a permission error or command not found?

  • If you see a permission error, try running pip install --user pandas==2.3.3 matplotlib==3.10.9 seaborn==0.13.2 notebook instead.
  • If pip is not recognized, try pip3 instead. On some systems, Python 3's package manager is named pip3.

Help me fix a pip install error for pandas, matplotlib, seaborn, or notebook.

Create your project folder and launch Jupyter Notebook

Jupyter Notebook is a browser-based tool that lets you write code, run it, and see results all in one document. This is the standard deliverable format for data analysis work.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir ecommerce-analysis
cd ecommerce-analysis
  • Confirm the folder exists by running:
ls
  • The folder is empty, so you will see no output. That is expected.
  • Launch Jupyter Notebook by running:
jupyter notebook

What happens when you run this?

Your terminal starts the Jupyter server and opens a new browser tab showing the Jupyter dashboard. The terminal will look busy with log messages. Do not close this terminal window.

If your browser does not open automatically, look in the terminal for a URL that starts with http://localhost:8888 and paste it into your browser.

  • In the Jupyter dashboard in your browser, click the New button at the top right of the file list.
  • Select Python 3 from the dropdown.
  • A new notebook opens in a new tab with the name Untitled at the top.
  • Click the notebook name Untitled at the top of the page to open a rename dialog.
  • Type ecommerce_analysis and press Enter to confirm.

Your notebook is now named ecommerce_analysis.ipynb and saved inside the ecommerce-analysis folder.

  • In the first cell of your notebook, paste the following code to import your libraries and configure chart settings:
# Cell 1 - Imports and Setup
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Set visual style
sns.set_style("whitegrid")
plt.rcParams["figure.figsize"] = (12, 6)

What does this code do?

  • import pandas as pd loads the pandas library and gives it the shorthand pd so you can type less.
  • import matplotlib.pyplot as plt loads the charting library.
  • import seaborn as sns loads seaborn, which adds polished styles to matplotlib charts.
  • sns.set_style("whitegrid") applies a clean grid background to every chart you create.
  • plt.rcParams["figure.figsize"] = (12, 6) sets a default chart size so your visualizations are easy to read.
  • Run the cell by pressing Shift+Enter.
  • If no errors appear, your libraries are installed and working correctly.

Seeing a ModuleNotFoundError?

This means the packages installed to a different Python than the one Jupyter is using. Close the Jupyter browser tab, press Ctrl+C in the terminal to stop the server, then run:

pip install pandas==2.3.3 matplotlib==3.10.9 seaborn==0.13.2

Restart Jupyter with jupyter notebook and try the import cell again.

Help me fix a ModuleNotFoundError in Jupyter Notebook.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook by pressing Ctrl+S (Windows) or Cmd+S (macOS).

ⓧ I'd like to double check the full code

Here is the complete code that should be in your notebook cell:

# Cell 1 - Imports and Setup
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Set visual style
sns.set_style("whitegrid")
plt.rcParams["figure.figsize"] = (12, 6)
Download the dataset from Kaggle

You will analyze real order data from a Brazilian e-commerce marketplace called Olist. The dataset contains over 100,000 orders spanning 2016 to 2018.

  • Open your browser and go to the Olist dataset on Kaggle.
  • If you do not have a Kaggle account, click Register and create a free account. You can sign up with your Google account for the fastest setup.
  • Once signed in, click the Download button near the top of the dataset page.
  • A zip file downloads to your computer. Find it in your Downloads folder.
  • Extract (unzip) all the CSV files from the zip into your ecommerce-analysis folder on your Desktop.

How do I extract the zip file?

  • On macOS, double-click the zip file to extract it, then drag the CSV files into the ecommerce-analysis folder.
  • On Windows, right-click the zip file, select Extract All, then copy the CSV files into the ecommerce-analysis folder.
  • Open a new terminal in VS Code (your first terminal is still running Jupyter). Click Terminal in the top menu, then New Terminal.
  • Navigate to your project folder and list the files by running:
cd ~/Desktop/ecommerce-analysis
ls
  • You should see several CSV files in the output, including olist_orders_dataset.csv, olist_order_items_dataset.csv, and olist_products_dataset.csv.

Don't see the CSV files?

  • Make sure you extracted the zip file and did not just copy the zip itself into the folder.
  • Some zip files extract into a subfolder. Check if there is a folder named archive inside ecommerce-analysis. If so, move the CSV files up one level into ecommerce-analysis directly.
  • Confirm the files are in the right place by running ls again after moving them.

Help me get the Kaggle dataset CSV files into my project folder.

Your analysis environment is ready. Next up, you will load these CSV files into pandas, merge them together, and clean the data so it is ready for analysis.

Load and Clean the Data

Your analysis environment is ready and your notebook has its imports configured. Now it's time to bring in the raw data and shape it into something you can actually analyze.

The Olist dataset is split across multiple CSV files, just like tables in a real database. You'll load three of these files, merge them into a single dataset, and clean out any rows that would skew your analysis.

In this step, get ready to:
  • Load three CSV files and merge them into a unified DataFrame.
  • Clean the data by converting dates, removing cancelled orders, and handling missing values.
  • Create new columns for revenue and time-based grouping.
Load and merge the CSV files

The Olist dataset stores orders, order items, and products in separate CSV files. To analyze them together, you need to merge them using shared columns.

This is the same concept as a JOIN in SQL. You connect tables through keys that appear in both.

  • Load the three core CSV files into pandas DataFrames by adding this code in a new cell below Cell 1:
# Cell 2 - Load Data
orders = pd.read_csv("olist_orders_dataset.csv")
order_items = pd.read_csv("olist_order_items_dataset.csv")
products = pd.read_csv("olist_products_dataset.csv")

print(f"Orders: {len(orders)} rows")
print(f"Order Items: {len(order_items)} rows")
print(f"Products: {len(products)} rows")

What does this code do?

  • pd.read_csv() reads each CSV file from your project folder into a DataFrame (a table-like structure in pandas).
  • Each DataFrame holds one "table" of data: orders (timestamps, statuses), order items (prices, product links), and products (category names).
  • The print statements confirm how many rows loaded from each file, so you can spot problems early.
  • Run the cell by pressing Shift+Enter.
  • You should see three lines of output showing the row count for each DataFrame (Orders, Order Items, and Products).

Seeing a FileNotFoundError?

This means pandas cannot find the CSV files. Make sure you extracted the Kaggle zip file directly into your ecommerce-analysis folder (not into a subfolder inside it).

Check that the filenames match exactly. The file should be named olist_orders_dataset.csv, not something like olist_orders_dataset (1).csv.

Help me fix my FileNotFoundError when loading CSV files in Jupyter

  • Merge the three DataFrames into one unified dataset by adding this code in a new cell:
# Cell 3 - Merge DataFrames
df = order_items.merge(orders, on="order_id", how="inner")
df = df.merge(products, on="product_id", how="inner")

print(f"Merged dataset: {len(df)} rows, {len(df.columns)} columns")
print(f"\nColumns: {list(df.columns)}")

What does this code do?

The .merge() method connects DataFrames through a shared column, just like a SQL JOIN. The first merge links order items to their parent orders using order_id. The second merge links those results to product details using product_id.

The how="inner" parameter means only rows that have a match in both tables are kept. Any orphaned rows are dropped.

  • Run the cell by pressing Shift+Enter.
  • You should see the merged row count and a full list of column names from all three source files.

Seeing zero rows in the merged dataset?

This usually means the merge key column names don't match between DataFrames. Double check that you used on="order_id" for the first merge and on="product_id" for the second.

Also confirm that Cell 2 ran successfully before Cell 3. Cells must run in order.

Help me debug my pandas merge that's returning zero rows

Clean the data

Raw data almost always has issues that would distort your analysis. Cancelled orders shouldn't count toward revenue. Missing category names make grouping impossible. Text-formatted dates can't be used for time-series calculations.

  • Clean the merged dataset by adding this code in a new cell:
# Cell 4 - Clean Data
rows_before = len(df)

# Convert date columns to datetime
df["order_purchase_timestamp"] = pd.to_datetime(df["order_purchase_timestamp"])
df["order_delivered_customer_date"] = pd.to_datetime(df["order_delivered_customer_date"])
df["order_estimated_delivery_date"] = pd.to_datetime(df["order_estimated_delivery_date"])

# Remove cancelled orders
df = df[df["order_status"] != "canceled"]

# Remove rows with missing product category
df = df.dropna(subset=["product_category_name"])

rows_after = len(df)
print(f"Rows before cleaning: {rows_before}")
print(f"Rows after cleaning: {rows_after}")
print(f"Rows removed: {rows_before - rows_after}")

What does this code do?

  • pd.to_datetime() converts text-formatted dates into proper datetime objects. This lets you do math with dates later (like calculating delivery delays).
  • df[df["order_status"] != "canceled"] filters the DataFrame to keep only non-cancelled orders. Cancelled orders never generated real revenue.
  • .dropna(subset=["product_category_name"]) removes rows where the product category is missing. You need category names for the performance analysis later.
  • Run the cell by pressing Shift+Enter.
  • You should see three lines showing the row count before cleaning, after cleaning, and how many rows were removed.

Seeing a KeyError for a column name?

This means the column name in your code doesn't match what's in the DataFrame. Column names are case-sensitive. Make sure you typed order_purchase_timestamp exactly as shown.

If you re-ran Cell 4 without re-running Cell 3 first, the DataFrame might already be filtered. Re-run cells 2, 3, and 4 in order.

Help me fix a KeyError when cleaning my DataFrame

Create revenue and time columns

The raw dataset stores price and freight_value as separate columns. For revenue analysis, you need a single revenue figure per order item. You also need a time column for grouping orders by month.

  • Add the revenue and time columns by adding this code in a new cell:
# Cell 5 - Create Revenue and Time Columns
df["revenue"] = df["price"] + df["freight_value"]
df["year_month"] = df["order_purchase_timestamp"].dt.to_period("M")

print(f"Revenue column - min: R${df['revenue'].min():.2f}, max: R${df['revenue'].max():.2f}")
print(f"Date range: {df['year_month'].min()} to {df['year_month'].max()}")

What does this code do?

  • df["revenue"] creates a new column that sums the product price and shipping cost. This gives you the total revenue each order item generated.
  • .dt.to_period("M") extracts just the year and month from each timestamp. This lets you group thousands of orders into monthly buckets for trend analysis.
  • Run the cell by pressing Shift+Enter.
  • You should see the minimum and maximum revenue values (in R$) and the date range of your dataset.
  • Save your notebook by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Seeing NaN values in your revenue column?

If either price or freight_value contains missing values, the sum produces NaN. Run df[["price", "freight_value"]].isnull().sum() to check for nulls in these columns.

Help me fix NaN values appearing in my revenue calculation

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook.

ⓧ I'd like to double check the full code

Here is the full notebook code from Cell 1 through Cell 5:

# Cell 1 - Imports and Setup
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Set visual style
sns.set_style("whitegrid")
plt.rcParams["figure.figsize"] = (12, 6)

# Cell 2 - Load Data
orders = pd.read_csv("olist_orders_dataset.csv")
order_items = pd.read_csv("olist_order_items_dataset.csv")
products = pd.read_csv("olist_products_dataset.csv")

print(f"Orders: {len(orders)} rows")
print(f"Order Items: {len(order_items)} rows")
print(f"Products: {len(products)} rows")

# Cell 3 - Merge DataFrames
df = order_items.merge(orders, on="order_id", how="inner")
df = df.merge(products, on="product_id", how="inner")

print(f"Merged dataset: {len(df)} rows, {len(df.columns)} columns")
print(f"\nColumns: {list(df.columns)}")

# Cell 4 - Clean Data
rows_before = len(df)

# Convert date columns to datetime
df["order_purchase_timestamp"] = pd.to_datetime(df["order_purchase_timestamp"])
df["order_delivered_customer_date"] = pd.to_datetime(df["order_delivered_customer_date"])
df["order_estimated_delivery_date"] = pd.to_datetime(df["order_estimated_delivery_date"])

# Remove cancelled orders
df = df[df["order_status"] != "canceled"]

# Remove rows with missing product category
df = df.dropna(subset=["product_category_name"])

rows_after = len(df)
print(f"Rows before cleaning: {rows_before}")
print(f"Rows after cleaning: {rows_after}")
print(f"Rows removed: {rows_before - rows_after}")

# Cell 5 - Create Revenue and Time Columns
df["revenue"] = df["price"] + df["freight_value"]
df["year_month"] = df["order_purchase_timestamp"].dt.to_period("M")

print(f"Revenue column - min: R${df['revenue'].min():.2f}, max: R${df['revenue'].max():.2f}")
print(f"Date range: {df['year_month'].min()} to {df['year_month'].max()}")

Your data is loaded, merged, and cleaned. You have a single DataFrame with a revenue column and monthly time buckets ready to go. Next up, you'll visualize the revenue trends and uncover which product categories drive the most business.

Analyze Revenue Trends and Category Performance

Your data is clean, merged, and ready to explore. You have a single DataFrame with revenue calculated for each order and a time column for monthly grouping.

Now it's time to turn that raw data into visual insights that answer real business questions. In this step, you'll use matplotlib to create charts showing revenue growth patterns and product category performance.

In this step, get ready to:
  • Create a monthly revenue line chart showing growth over time.
  • Visualize the top and bottom product categories by total revenue.
  • Build a summary table identifying categories with concentration risk.
Create a monthly revenue trend chart

Every good analysis starts with a business question. Before building a chart, you'll frame the question it answers so that any stakeholder reading your notebook understands the purpose immediately.

  • In your Jupyter Notebook, create a new Markdown cell below Cell 5. You can do this by clicking the + button in the toolbar, then changing the cell type dropdown from Code to Markdown.
  • Type the following into the Markdown cell:
## Monthly Revenue Trend
**Business Question:** How has revenue grown over time, and are there seasonal patterns?
  • Press Shift+Enter to run the Markdown cell. It renders as formatted text.

Why start with a business question?

In professional data analysis, every chart needs context. Writing the business question above your visualization tells stakeholders why this chart exists and what decision it supports. This is a core skill in data storytelling.

  • Create a new code cell below the Markdown cell and paste the following code to generate a monthly revenue line chart:
# Cell 7 - Monthly Revenue Line Chart
monthly_revenue = df.groupby("year_month")["revenue"].sum()

fig, ax = plt.subplots(figsize=(14, 6))
monthly_revenue.plot(ax=ax, linewidth=2, color="#2563eb")
ax.set_title("Monthly Revenue Trend - Olist E-Commerce", fontsize=14, fontweight="bold")
ax.set_xlabel("Month", fontsize=11)
ax.set_ylabel("Revenue (R$)", fontsize=11)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))
plt.tight_layout()
plt.show()

What does this code do?

  • df.groupby("year_month")["revenue"].sum() groups all rows by their month and totals the revenue for each period. This creates a time series.
  • monthly_revenue.plot() draws the grouped data as a line chart, making trends immediately visible.
  • ax.yaxis.set_major_formatter() formats large numbers with the R$ currency prefix and comma separators so the y-axis is readable.
  • Press Shift+Enter to run the cell.
  • You should see a line chart showing monthly revenue from 2016 to 2018 with a clear upward growth trend.

Not seeing a chart?

  • Make sure you ran Cells 1 through 5 first. The chart depends on the df DataFrame and its year_month column from Cell 5.
  • Check that you ran the Imports cell (Cell 1) at the start of your session. If you restarted the kernel, you need to re-run all cells from the top.

Why isn't my matplotlib chart showing in Jupyter?

Visualize top and bottom product categories

Revenue trends tell you how the business is growing overall. Product category breakdowns tell you where that growth is coming from.

  • Create a new code cell and paste the following code to generate side-by-side bar charts of the best and worst performing categories:
# Cell 8 - Top 10 Categories by Revenue
category_revenue = df.groupby("product_category_name")["revenue"].sum().sort_values(ascending=False)

fig, axes = plt.subplots(1, 2, figsize=(16, 6))

# Top 10
category_revenue.head(10).sort_values().plot(
    kind="barh", ax=axes[0], color="#2563eb"
)
axes[0].set_title("Top 10 Product Categories by Revenue", fontsize=12, fontweight="bold")
axes[0].set_xlabel("Total Revenue (R$)")
axes[0].xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))

# Bottom 5
category_revenue.tail(5).sort_values().plot(
    kind="barh", ax=axes[1], color="#dc2626"
)
axes[1].set_title("Bottom 5 Product Categories by Revenue", fontsize=12, fontweight="bold")
axes[1].set_xlabel("Total Revenue (R$)")
axes[1].xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))

plt.tight_layout()
plt.show()

What does this code do?

  • df.groupby("product_category_name")["revenue"].sum().sort_values(ascending=False) totals revenue per category and ranks them from highest to lowest.
  • .head(10) and .tail(5) grab the top performers and worst performers respectively.
  • plt.subplots(1, 2) creates two side-by-side subplots so you can compare winners and losers at a glance.
  • Horizontal bar charts (kind="barh") are used because category names are long. Horizontal bars give the text room to display without overlapping.
  • Press Shift+Enter to run the cell.
  • You should see two horizontal bar charts side by side. The left chart shows the top 10 categories in blue, and the right chart shows the bottom 5 categories in red.
  • Create a new Markdown cell below Cell 8. Write 1-2 sentences interpreting what the charts reveal about the business. For example, note which categories dominate and how much of a gap there is between top and bottom performers.

Category names look cut off or overlapping?

  • Make sure your figsize is set to (16, 6). A smaller figure squeezes the labels together.
  • Confirm you included plt.tight_layout() before plt.show(). This adjusts spacing automatically to prevent overlaps.

Why are my bar chart labels overlapping in matplotlib?

Build a category performance summary table

Charts are great for spotting patterns, but a summary table lets you dig deeper. A category might appear high-revenue because of many small orders, or because of a few extremely large ones. That distinction matters for business risk.

  • Create a new code cell and paste the following to calculate revenue, order count, and average order value per category:
# Cell 9 - Category Performance Summary Table
category_summary = df.groupby("product_category_name").agg(
    total_revenue=("revenue", "sum"),
    order_count=("order_id", "nunique"),
    avg_order_value=("revenue", "mean")
).sort_values("total_revenue", ascending=False)

print("=== Category Performance Summary (Top 15) ===")
print(category_summary.head(15).to_string())

What does this code do?

  • .agg() calculates multiple statistics at once for each category: total revenue, unique order count, and average order value.
  • "nunique" counts distinct order IDs so that repeat items within the same order are not double-counted.
  • Categories with high total revenue but low order count rely on a few large purchases. If those customers leave, revenue drops sharply. This is concentration risk.
  • Press Shift+Enter to run the cell.
  • You should see a table of the top 15 categories sorted by total revenue, with columns for total_revenue, order_count, and avg_order_value.
  • Create a new Markdown cell below Cell 9. Write 1-2 sentences noting which categories have high revenue but low order count, and explain why this represents a business risk.

Look for the mismatch

Compare total_revenue against order_count. A category ranked high in revenue but with relatively few orders depends on large, infrequent purchases. If those customers churn, that revenue disappears. Call this out in your markdown interpretation.

Seeing a KeyError or empty table?

  • Verify your column names match exactly. The aggregation references revenue and order_id. These should already exist from Cells 3 and 5.
  • If you restarted the Jupyter kernel, re-run all cells from Cell 1 through Cell 5 first.

Help me fix a KeyError in my pandas groupby aggregation.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook by pressing Ctrl+S (or Cmd+S on macOS).

ⓧ I'd like to double check the full code

Here is the complete notebook code from Cell 1 through Cell 9. Compare your cells against this:

# Cell 1 - Imports and Setup
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Set visual style
sns.set_style("whitegrid")
plt.rcParams["figure.figsize"] = (12, 6)

# Cell 2 - Load Data
orders = pd.read_csv("olist_orders_dataset.csv")
order_items = pd.read_csv("olist_order_items_dataset.csv")
products = pd.read_csv("olist_products_dataset.csv")

print(f"Orders: {len(orders)} rows")
print(f"Order Items: {len(order_items)} rows")
print(f"Products: {len(products)} rows")

# Cell 3 - Merge DataFrames
df = order_items.merge(orders, on="order_id", how="inner")
df = df.merge(products, on="product_id", how="inner")

print(f"Merged dataset: {len(df)} rows, {len(df.columns)} columns")
print(f"\nColumns: {list(df.columns)}")

# Cell 4 - Clean Data
rows_before = len(df)

# Convert date columns to datetime
df["order_purchase_timestamp"] = pd.to_datetime(df["order_purchase_timestamp"])
df["order_delivered_customer_date"] = pd.to_datetime(df["order_delivered_customer_date"])
df["order_estimated_delivery_date"] = pd.to_datetime(df["order_estimated_delivery_date"])

# Remove cancelled orders
df = df[df["order_status"] != "canceled"]

# Remove rows with missing product category
df = df.dropna(subset=["product_category_name"])

rows_after = len(df)
print(f"Rows before cleaning: {rows_before}")
print(f"Rows after cleaning: {rows_after}")
print(f"Rows removed: {rows_before - rows_after}")

# Cell 5 - Create Revenue and Time Columns
df["revenue"] = df["price"] + df["freight_value"]
df["year_month"] = df["order_purchase_timestamp"].dt.to_period("M")

print(f"Revenue column - min: R${df['revenue'].min():.2f}, max: R${df['revenue'].max():.2f}")
print(f"Date range: {df['year_month'].min()} to {df['year_month'].max()}")

# Cell 6 - Markdown: Revenue Trend Analysis
# (In Jupyter, this would be a Markdown cell)
# ## Monthly Revenue Trend
# **Business Question:** How has revenue grown over time, and are there seasonal patterns?

# Cell 7 - Monthly Revenue Line Chart
monthly_revenue = df.groupby("year_month")["revenue"].sum()

fig, ax = plt.subplots(figsize=(14, 6))
monthly_revenue.plot(ax=ax, linewidth=2, color="#2563eb")
ax.set_title("Monthly Revenue Trend - Olist E-Commerce", fontsize=14, fontweight="bold")
ax.set_xlabel("Month", fontsize=11)
ax.set_ylabel("Revenue (R$)", fontsize=11)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))
plt.tight_layout()
plt.show()

# Cell 8 - Top 10 Categories by Revenue
category_revenue = df.groupby("product_category_name")["revenue"].sum().sort_values(ascending=False)

fig, axes = plt.subplots(1, 2, figsize=(16, 6))

# Top 10
category_revenue.head(10).sort_values().plot(
    kind="barh", ax=axes[0], color="#2563eb"
)
axes[0].set_title("Top 10 Product Categories by Revenue", fontsize=12, fontweight="bold")
axes[0].set_xlabel("Total Revenue (R$)")
axes[0].xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))

# Bottom 5
category_revenue.tail(5).sort_values().plot(
    kind="barh", ax=axes[1], color="#dc2626"
)
axes[1].set_title("Bottom 5 Product Categories by Revenue", fontsize=12, fontweight="bold")
axes[1].set_xlabel("Total Revenue (R$)")
axes[1].xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))

plt.tight_layout()
plt.show()

# Cell 9 - Category Performance Summary Table
category_summary = df.groupby("product_category_name").agg(
    total_revenue=("revenue", "sum"),
    order_count=("order_id", "nunique"),
    avg_order_value=("revenue", "mean")
).sort_values("total_revenue", ascending=False)

print("=== Category Performance Summary (Top 15) ===")
print(category_summary.head(15).to_string())

You now have a clear picture of revenue trends and category performance. Next, you'll dig into delivery delays to find where fulfillment problems are hurting the highest-revenue categories.

Analyze Delivery Performance and Write Recommendations

In the last step, you identified which product categories drive the most revenue and which ones carry concentration risk. Revenue charts tell you what sells, but they don't reveal where operations are failing.

A high-revenue category that consistently delivers late is a ticking time bomb for customer churn. In this step, you'll calculate delivery delays, cross-reference them with revenue data, and write an executive summary that a stakeholder could act on immediately.

In this step, get ready to:
  • Calculate delivery delays and visualize the worst-performing categories.
  • Cross-reference delivery delays with revenue to find high-priority problems.
  • Write an executive summary with 3 data-backed business recommendations.
Calculate delivery delays and visualize late delivery rates

The Olist dataset includes both the estimated delivery date (what the customer was promised) and the actual delivery date. By comparing these two dates, you can calculate exactly how many days late each order arrived.

  • Create a new cell in your notebook (Cell 10) by clicking the + button in the toolbar.
  • Paste the following code to calculate delivery delays, aggregate late delivery rates by category, and plot the results:
# Cell 10 - Delivery Delay Analysis
# Calculate delivery delay in days
df["delivery_delay_days"] = (
    df["order_delivered_customer_date"] - df["order_estimated_delivery_date"]
).dt.days

# Flag late deliveries
df["is_late"] = df["delivery_delay_days"] > 0

# Late delivery rate by category
late_by_category = df.groupby("product_category_name").agg(
    total_orders=("order_id", "count"),
    late_orders=("is_late", "sum")
)
late_by_category["late_rate"] = late_by_category["late_orders"] / late_by_category["total_orders"]

# Filter to categories with at least 50 orders for statistical relevance
late_by_category = late_by_category[late_by_category["total_orders"] >= 50]
late_by_category = late_by_category.sort_values("late_rate", ascending=False)

# Plot top 10 categories by late delivery rate
fig, ax = plt.subplots(figsize=(12, 6))
late_by_category["late_rate"].head(10).sort_values().plot(
    kind="barh", ax=ax, color="#dc2626"
)
ax.set_title("Top 10 Categories by Late Delivery Rate", fontsize=12, fontweight="bold")
ax.set_xlabel("Late Delivery Rate (%)")
ax.xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"{x:.0%}"))
plt.tight_layout()
plt.show()

What does this code do?

This cell performs three distinct operations to surface delivery problems.

  • delivery_delay_days subtracts the estimated delivery date from the actual delivery date. A positive number means the order arrived late.
  • is_late is a boolean flag that marks any order with a delay greater than zero as late.
  • The filter for categories with at least 50 orders prevents tiny categories from skewing the chart. A category with 2 orders and 1 late delivery would show 50% late rate, which is misleading.
  • Run the cell by pressing Shift+Enter.

You should see a horizontal bar chart showing the top 10 product categories ranked by their percentage of late deliveries. The bars are red to signal a problem area.

Seeing an empty chart or NaN values?

  • Make sure you ran Cell 4 (the cleaning step) before this cell. That cell converts your date columns to datetime format, which is required for date subtraction.
  • If you see NaN in the delay column, some rows may have missing delivery dates (orders not yet delivered). These will be excluded automatically from the late rate calculation.

Still stuck? Help me debug my delivery delay calculation. I'm getting NaN values or an empty chart.

Cross-reference delivery delays with revenue

Knowing which categories have the most late deliveries is useful, but the real insight comes from combining that with revenue data. A category that has high late rates AND high revenue is the highest-priority fix.

  • Create a new cell (Cell 11) and paste the following code to join your revenue and delivery data, then identify the highest-priority problem categories:
# Cell 11 - Cross-Reference: High Revenue + High Delay
# Merge revenue and delay data
priority_analysis = category_summary.join(late_by_category[["late_rate"]])
priority_analysis = priority_analysis.dropna(subset=["late_rate"])

# Find categories in top 25% revenue AND top 25% late rate
revenue_threshold = priority_analysis["total_revenue"].quantile(0.75)
delay_threshold = priority_analysis["late_rate"].quantile(0.75)

high_priority = priority_analysis[
    (priority_analysis["total_revenue"] >= revenue_threshold) &
    (priority_analysis["late_rate"] >= delay_threshold)
]

print("=== HIGH PRIORITY: High Revenue + High Late Delivery Rate ===\n")
print(high_priority[["total_revenue", "order_count", "late_rate"]].to_string())
print(f"\nThese {len(high_priority)} categories have both high revenue and delivery issues.")
print("Improving fulfillment here would have the biggest business impact.")

What does this code do?

  • category_summary.join(late_by_category[["late_rate"]]) combines your revenue summary from the previous step with the late delivery rates you just calculated. Both DataFrames are indexed by product category, so pandas matches them automatically.
  • .quantile(0.75) finds the 75th percentile threshold. Categories above this value in BOTH revenue and late rate are the ones where fixing delivery problems protects the most money.
  • Run the cell by pressing Shift+Enter.

You should see a printed table listing 2-3 categories that appear in the top 25% for both revenue and late delivery rate. These are your highest-priority problem areas.

Why is this the key business insight?

A stakeholder does not want a list of 70 categories sorted by lateness. They want to know: "Where should we spend money to get the biggest return?" By filtering to categories that are both high-revenue and high-delay, you are answering that question directly.

Seeing an empty result?

  • Make sure you ran Cell 9 (which creates category_summary) and Cell 10 (which creates late_by_category) before running this cell.
  • If the table is empty, both DataFrames exist but no categories meet both thresholds simultaneously. Try printing revenue_threshold and delay_threshold to see what the cutoff values are.

Need more help? Help me debug my cross-reference analysis. The high_priority DataFrame is empty.

Write your executive summary

The executive summary is the most important cell in your notebook. Everything you have done so far (cleaning, charting, cross-referencing) exists to support this one deliverable. A non-technical stakeholder should be able to read this cell alone and understand what to do next.

  • Create a new cell (Cell 12). Change the cell type to Markdown using the dropdown in the toolbar (it currently says "Code").
  • Paste the following template into the Markdown cell, then replace the placeholder values in brackets with the actual numbers from your analysis output:
## Executive Summary

### Business Question
Which product categories should the company prioritize for growth,
and where are delivery delays hurting revenue?

### Key Findings
1. **Revenue Growth**: Monthly revenue grew significantly from 2017 to 2018,
   with seasonal peaks in [specific months from your data].
2. **Category Concentration**: The top 5 categories account for approximately
   [X]% of total revenue, creating concentration risk.
3. **Delivery Impact**: [X] high-revenue categories have late delivery rates
   above [X]%, likely impacting customer satisfaction and repeat purchases.

### Recommendations (Prioritized by Business Impact)
1. **Fix fulfillment in [Category]** - This high-revenue category has a [X]%
   late delivery rate. Improving logistics here protects the largest revenue stream.
2. **Diversify revenue sources** - Invest in growing mid-tier categories to
   reduce dependency on the top 5 categories.
3. **Investigate seasonal patterns** - Revenue dips in [specific months]
   suggest an opportunity for targeted promotions.

### Suggested Next Steps
- Conduct customer segmentation (RFM analysis) to identify at-risk high-value customers
- Analyze seller performance to identify which sellers cause the most delivery delays
- Build a delivery prediction model to set more accurate delivery estimates

How do I fill in the placeholders?

Scroll back through your notebook outputs to find the real numbers. Replace every bracketed placeholder with a specific value from your analysis.

  • For seasonal peaks, look at your monthly revenue line chart (Cell 7) and note which months show spikes or dips.
  • For category concentration, use the category_summary table from Cell 9. Sum the top 5 categories' revenue and divide by total revenue.
  • For delivery impact numbers, use the high_priority output from Cell 11.
  • Run the Markdown cell by pressing Shift+Enter to render it as formatted text.

You should see a cleanly formatted summary with bold headings, numbered findings, and a bulleted list of next steps. This is the deliverable a hiring manager or stakeholder would read.

Make it specific to stand out

Generic recommendations like "improve delivery" are weak. Strong recommendations name the specific category, cite the exact late rate percentage, and estimate the revenue at stake. The more specific your numbers, the more credible your analysis looks in a portfolio.

Markdown cell not rendering?

  • Make sure the cell type dropdown says Markdown, not Code. If it says Code, the markdown syntax will print as plain text instead of rendering as formatted headings and bold.
  • If the bold text is not rendering, check that you have two asterisks on each side of the bolded phrase with no spaces between the asterisks and the text.

Still having trouble? Help me get my Markdown cell to render properly in Jupyter Notebook.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook with all 12 cells (Cells 1-12) completed and run.

ⓧ I'd like to double check the full code

Here is the complete notebook from Cell 1 through Cell 12. Compare yours against this reference:

# Cell 1 - Imports and Setup
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Set visual style
sns.set_style("whitegrid")
plt.rcParams["figure.figsize"] = (12, 6)

# Cell 2 - Load Data
orders = pd.read_csv("olist_orders_dataset.csv")
order_items = pd.read_csv("olist_order_items_dataset.csv")
products = pd.read_csv("olist_products_dataset.csv")

print(f"Orders: {len(orders)} rows")
print(f"Order Items: {len(order_items)} rows")
print(f"Products: {len(products)} rows")

# Cell 3 - Merge DataFrames
df = order_items.merge(orders, on="order_id", how="inner")
df = df.merge(products, on="product_id", how="inner")

print(f"Merged dataset: {len(df)} rows, {len(df.columns)} columns")
print(f"\nColumns: {list(df.columns)}")

# Cell 4 - Clean Data
rows_before = len(df)

# Convert date columns to datetime
df["order_purchase_timestamp"] = pd.to_datetime(df["order_purchase_timestamp"])
df["order_delivered_customer_date"] = pd.to_datetime(df["order_delivered_customer_date"])
df["order_estimated_delivery_date"] = pd.to_datetime(df["order_estimated_delivery_date"])

# Remove cancelled orders
df = df[df["order_status"] != "canceled"]

# Remove rows with missing product category
df = df.dropna(subset=["product_category_name"])

rows_after = len(df)
print(f"Rows before cleaning: {rows_before}")
print(f"Rows after cleaning: {rows_after}")
print(f"Rows removed: {rows_before - rows_after}")

# Cell 5 - Create Revenue and Time Columns
df["revenue"] = df["price"] + df["freight_value"]
df["year_month"] = df["order_purchase_timestamp"].dt.to_period("M")

print(f"Revenue column - min: R${df['revenue'].min():.2f}, max: R${df['revenue'].max():.2f}")
print(f"Date range: {df['year_month'].min()} to {df['year_month'].max()}")

# Cell 6 - Markdown: Revenue Trend Analysis
# (In Jupyter, this would be a Markdown cell)
# ## Monthly Revenue Trend
# **Business Question:** How has revenue grown over time, and are there seasonal patterns?

# Cell 7 - Monthly Revenue Line Chart
monthly_revenue = df.groupby("year_month")["revenue"].sum()

fig, ax = plt.subplots(figsize=(14, 6))
monthly_revenue.plot(ax=ax, linewidth=2, color="#2563eb")
ax.set_title("Monthly Revenue Trend - Olist E-Commerce", fontsize=14, fontweight="bold")
ax.set_xlabel("Month", fontsize=11)
ax.set_ylabel("Revenue (R$)", fontsize=11)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))
plt.tight_layout()
plt.show()

# Cell 8 - Top 10 Categories by Revenue
category_revenue = df.groupby("product_category_name")["revenue"].sum().sort_values(ascending=False)

fig, axes = plt.subplots(1, 2, figsize=(16, 6))

# Top 10
category_revenue.head(10).sort_values().plot(
    kind="barh", ax=axes[0], color="#2563eb"
)
axes[0].set_title("Top 10 Product Categories by Revenue", fontsize=12, fontweight="bold")
axes[0].set_xlabel("Total Revenue (R$)")
axes[0].xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))

# Bottom 5
category_revenue.tail(5).sort_values().plot(
    kind="barh", ax=axes[1], color="#dc2626"
)
axes[1].set_title("Bottom 5 Product Categories by Revenue", fontsize=12, fontweight="bold")
axes[1].set_xlabel("Total Revenue (R$)")
axes[1].xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"R${x:,.0f}"))

plt.tight_layout()
plt.show()

# Cell 9 - Category Performance Summary Table
category_summary = df.groupby("product_category_name").agg(
    total_revenue=("revenue", "sum"),
    order_count=("order_id", "nunique"),
    avg_order_value=("revenue", "mean")
).sort_values("total_revenue", ascending=False)

print("=== Category Performance Summary (Top 15) ===\n")
print(category_summary.head(15).to_string())

# Cell 10 - Delivery Delay Analysis
# Calculate delivery delay in days
df["delivery_delay_days"] = (
    df["order_delivered_customer_date"] - df["order_estimated_delivery_date"]
).dt.days

# Flag late deliveries
df["is_late"] = df["delivery_delay_days"] > 0

# Late delivery rate by category
late_by_category = df.groupby("product_category_name").agg(
    total_orders=("order_id", "count"),
    late_orders=("is_late", "sum")
)
late_by_category["late_rate"] = late_by_category["late_orders"] / late_by_category["total_orders"]

# Filter to categories with at least 50 orders for statistical relevance
late_by_category = late_by_category[late_by_category["total_orders"] >= 50]
late_by_category = late_by_category.sort_values("late_rate", ascending=False)

# Plot top 10 categories by late delivery rate
fig, ax = plt.subplots(figsize=(12, 6))
late_by_category["late_rate"].head(10).sort_values().plot(
    kind="barh", ax=ax, color="#dc2626"
)
ax.set_title("Top 10 Categories by Late Delivery Rate", fontsize=12, fontweight="bold")
ax.set_xlabel("Late Delivery Rate (%)")
ax.xaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f"{x:.0%}"))
plt.tight_layout()
plt.show()

# Cell 11 - Cross-Reference: High Revenue + High Delay
# Merge revenue and delay data
priority_analysis = category_summary.join(late_by_category[["late_rate"]])
priority_analysis = priority_analysis.dropna(subset=["late_rate"])

# Find categories in top 25% revenue AND top 25% late rate
revenue_threshold = priority_analysis["total_revenue"].quantile(0.75)
delay_threshold = priority_analysis["late_rate"].quantile(0.75)

high_priority = priority_analysis[
    (priority_analysis["total_revenue"] >= revenue_threshold) &
    (priority_analysis["late_rate"] >= delay_threshold)
]

print("=== HIGH PRIORITY: High Revenue + High Late Delivery Rate ===\n")
print(high_priority[["total_revenue", "order_count", "late_rate"]].to_string())
print(f"\nThese {len(high_priority)} categories have both high revenue and delivery issues.")
print("Improving fulfillment here would have the biggest business impact.")

# Cell 12 - Executive Summary (Markdown cell in Jupyter)
# ## Executive Summary
#
# ### Business Question
# Which product categories should the company prioritize for growth,
# and where are delivery delays hurting revenue?
#
# ### Key Findings
# 1. **Revenue Growth**: Monthly revenue grew significantly from 2017 to 2018,
#    with seasonal peaks in [specific months from your data].
# 2. **Category Concentration**: The top 5 categories account for approximately
#    [X]% of total revenue, creating concentration risk.
# 3. **Delivery Impact**: [X] high-revenue categories have late delivery rates
#    above [X]%, likely impacting customer satisfaction and repeat purchases.
#
# ### Recommendations (Prioritized by Business Impact)
# 1. **Fix fulfillment in [Category]** - This high-revenue category has a [X]%
#    late delivery rate. Improving logistics here protects the largest revenue stream.
# 2. **Diversify revenue sources** - Invest in growing mid-tier categories to
#    reduce dependency on the top 5 categories.
# 3. **Investigate seasonal patterns** - Revenue dips in [specific months]
#    suggest an opportunity for targeted promotions.
#
# ### Suggested Next Steps
# - Conduct customer segmentation (RFM analysis) to identify at-risk high-value customers
# - Analyze seller performance to identify which sellers cause the most delivery delays
# - Build a delivery prediction model to set more accurate delivery estimates

Secret mission

Customer Segmentation with RFM Analysis

Your executive summary recommended customer segmentation as a next step. In this bonus challenge, you will build that analysis yourself using RFM scoring, a technique used by real marketing and analytics teams to identify high-value customers and spot those about to churn.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • Jupyter Notebook server (running process in your terminal).
  • The ecommerce-analysis folder on your Desktop (contains your notebook and CSV data files).
  • Python packages: pandas, matplotlib, seaborn, notebook.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep the notebook as a portfolio piece.

  • Your notebook and data stay in the ecommerce-analysis folder on your Desktop.
  • The Jupyter server continues running in your terminal. You can access it anytime by opening http://localhost:8888 in your browser.
  • The installed Python packages remain available for future projects.

Pause - I'll come back to this later

Shut down the Jupyter server to free up your terminal. Your files stay on disk for when you return.

  • Switch to the terminal where Jupyter is running.
  • Press Ctrl+C to stop the Jupyter server. Confirm with y if prompted.
  • Your notebook and CSV files remain in the ecommerce-analysis folder.
  • To resume later, open a terminal, navigate to your project folder, and run jupyter notebook again.

Delete - I don't want to use this again

Remove all project files and uninstall the packages you installed for this project.

  • Switch to the terminal where Jupyter is running and press Ctrl+C to stop the server. Confirm with y if prompted.
  • Delete the project folder by running this command (macOS/Linux):
rm -rf ~/Desktop/ecommerce-analysis

On Windows, run this instead:

rmdir /s /q "$env:USERPROFILE\Desktop\ecommerce-analysis"
  • Uninstall the Python packages by running:
pip uninstall pandas matplotlib seaborn notebook -y
  • Confirm the folder is gone by running ls ~/Desktop (macOS/Linux) or dir %USERPROFILE%\Desktop (Windows). You should no longer see ecommerce-analysis in the listing.

Nice Work!

Nice Work!

You just completed an end-to-end business analysis using real-world e-commerce data. Your Jupyter Notebook takes raw CSV files, cleans and merges them, visualizes key trends, and delivers actionable recommendations that a stakeholder could act on today.

You've learned how to:

  • Load, merge, and clean a real-world dataset of 100,000+ orders using pandas, transforming messy CSVs into analysis-ready DataFrames.
  • Analyze revenue trends and product category performance with data visualizations built in matplotlib and seaborn.
  • Cross-reference delivery delays with revenue data and write a professional executive summary with 3 prioritized, data-backed business recommendations.
  • Secret Mission: Segmented customers using RFM analysis (Recency, Frequency, Monetary) and created targeted recommendations for each customer segment.

Ready to quiz yourself?