Diagnose Low E-Commerce Satisfaction

Analyze 100k real orders to find why customers leave bad reviews.

Introduction

30 Second Summary

Think about the last time you left a bad review after an online purchase. Something went wrong, and you wanted the company to know. Now imagine you are on the other side, staring at thousands of those reviews and trying to figure out what keeps going wrong.

In this project, you will play the role of a data analyst investigating why a Brazilian e-commerce company is drowning in bad reviews. You will load 100,000 real orders, clean messy data, merge multiple tables together, and discover the root cause with clear visualizations and a business recommendation.

What You'll Build

You will run a single Python script that loads a real 100k-order dataset, reveals that delivery delays are the primary driver of bad reviews, and produces charts you can present to any stakeholder.

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

  • A complete analysis script that you run once to load, clean, and merge 100,000 real e-commerce orders across five related CSV files into one analysis-ready dataset.
  • A set of data visualizations proving that delivery delays are the strongest predictor of poor customer reviews, with specific product categories and regions highlighted.
  • A one-paragraph business recommendation backed by specific metrics that you can present in a job interview or portfolio walkthrough.
  • Secret Mission: Segment orders into on-time vs. late cohorts and calculate the revenue at risk from poor delivery performance.

Do I need to know Python already?

No prior Python experience is required. The project provides all the code you need. Familiarity with basic programming concepts is helpful, but you will learn pandas and matplotlib as you go.

Before We Start

Before we dive into any code, let's lock in what you're about to build and why it matters. Clarity on the end goal makes every step that follows more intentional.

Set Up Your Analysis Environment

Before you can analyze anything, you need a clean workspace with the right tools. Your analysis project needs a dedicated folder, the correct Python libraries, and the actual dataset to explore.

In this step, you will create a project folder with an isolated Python environment, install pandas and matplotlib, download the real e-commerce dataset from Kaggle, and confirm everything works by running your first script.

In this step, get ready to:
  • Create a project folder with a Python virtual environment and install pandas and matplotlib.
  • Download the Olist e-commerce dataset from Kaggle.
  • Create your analysis script and confirm the imports work.
Create your project folder and install libraries
  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Open the integrated terminal by pressing Ctrl+` (Windows/Linux) or Cmd+` (macOS).
  • Move to your Desktop and create your project folder by running these commands:
cd ~/Desktop
mkdir ecommerce-analysis
cd ecommerce-analysis
  • Confirm you are inside the new folder by running:
pwd

You should see a path ending in ecommerce-analysis.

  • Create a Python virtual environment and activate it by running:

macOS

python3 -m venv venv
source venv/bin/activate

You should see (venv) appear at the start of your terminal prompt.

Windows

python -m venv venv
venv\Scripts\activate

You should see (venv) appear at the start of your terminal prompt.

What is a virtual environment?

A virtual environment is an isolated Python installation just for this project. Libraries you install here stay separate from your system Python, so different projects can use different library versions without conflicts.

  • Install the two libraries your analysis needs by running:
pip install pandas matplotlib

You will see pip downloading and installing packages. It finishes with a line saying Successfully installed followed by the package names.

Seeing an error during install?

Make sure your virtual environment is active. You should see (venv) in your prompt. If not, re-run the activation command above.

If you see an error mentioning an unsupported Python version, pandas 3.0.5 requires Python 3.11 or higher. Check your version with python --version (or python3 --version on macOS) and upgrade if needed.

Help me troubleshoot this install error

Download the Olist dataset from Kaggle

The dataset you will analyze is a real collection of 100,000 e-commerce orders from a Brazilian company called Olist. It is split across 9 CSV files that you will join together later.

  • Open your browser and go to the Olist dataset page: https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce.
  • Sign in to your Kaggle account if you are not already signed in.
  • Click the Download button near the top of the page. This downloads a ZIP file (approximately 45 MB).
  • Create a data folder inside your project by running:
mkdir data
  • Locate the downloaded ZIP file (usually in your Downloads folder), extract it, and move all the CSV files into the data folder inside ecommerce-analysis.
  • Confirm the files are in place by running:
ls data

You should see 9 CSV files. Eight of them start with olist_ and one is called product_category_name_translation.csv.

Don't see 9 CSV files?

Some systems extract the ZIP into a nested subfolder. Check if there is an extra folder inside data and move the CSVs up one level so they sit directly inside data/.

Make sure you are signed in to Kaggle before clicking Download. The download requires an authenticated account.

Help me find my CSV files

Create your analysis script

Now you will create the Python file where all your analysis code will live.

  • In VS Code, click File in the top menu bar.
  • Click New File.
  • Save the file immediately as analysis.py in the ecommerce-analysis folder (not inside data).
  • Add the following imports to analysis.py:
import pandas as pd
import matplotlib.pyplot as plt

What do these imports do?

pandas (imported as pd) is the core library for loading and manipulating tabular data. You will use it to read CSVs, merge tables, filter rows, and calculate statistics.

matplotlib.pyplot (imported as plt) is Python's standard charting library. You will use it to create bar charts that visualize your findings.

  • Save analysis.py.

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.py with both import lines.

ⓧ I'd like to double check the full code

Your analysis.py should contain exactly this:

import pandas as pd
import matplotlib.pyplot as plt

Before you run this, what do you think will happen? The script only imports two libraries, so if everything is installed correctly you should see no output at all.

  • Verify your imports work by running this command in your terminal:
python -c "import pandas; import matplotlib; print(pandas.__version__)"

You should see a version number printed (such as 3.0.5) with no errors. This confirms both libraries are installed and importable.

Seeing a ModuleNotFoundError?

Make sure your virtual environment is still active. You should see (venv) in your terminal prompt. If it disappeared, re-run the activation command from earlier.

On macOS, you may need to use python3 instead of python if your system defaults to an older version outside the venv.

Help me fix this ModuleNotFoundError

Your environment is ready. You have a project folder, the right libraries, and a real 100,000-order dataset waiting to be explored. Next up, you will load the data and see just how many customers are leaving bad reviews.

Load the Data and See the Problem

Your analysis environment is ready with pandas and matplotlib installed and the dataset waiting in your data/ folder.

Before you can diagnose anything, you need to see the data and understand the scale of the problem. Loading the reviews table first reveals THAT there is a customer satisfaction problem, but you will quickly discover you cannot determine WHY from reviews alone.

In this step, get ready to:
  • Load the orders and reviews datasets and examine their size.
  • Discover that roughly 1 in 4 orders received a poor review score.
  • Create a baseline bar chart of the review score distribution.
Load the orders and reviews data

The Olist dataset is split across 9 CSV files, each representing a different table. You will start by loading just two: orders and reviews.

  • In analysis.py, find the existing imports at the top of the file.
  • Add import os below the matplotlib import, then add the data-loading code below it by copying the following:
import os

# Create output folder
os.makedirs("output", exist_ok=True)

# --- STEP 2: Load data and see the problem ---

orders = pd.read_csv("data/olist_orders_dataset.csv")
reviews = pd.read_csv("data/olist_order_reviews_dataset.csv")

print("=== STEP 2: Understanding the Problem ===")
print(f"Orders shape: {orders.shape}")
print(f"Reviews shape: {reviews.shape}")

What does this code do?

os.makedirs("output", exist_ok=True) creates a folder called output/ for saving charts later. The exist_ok=True argument means it will not throw an error if the folder already exists.

pd.read_csv() loads each CSV file into a DataFrame, which is pandas' main data structure for tabular data. Printing .shape tells you how many rows and columns each table has.

  • Save analysis.py.
  • Run your script by entering this command in the terminal:
python analysis.py

You should see the shapes of both DataFrames printed. Orders has around 99,441 rows with 8 columns, and reviews has around 99,224 rows with 7 columns.

Seeing a FileNotFoundError?

This means Python cannot find the CSV files. Make sure your data/ subfolder contains the extracted Olist CSV files and that you are running the script from inside your ecommerce-analysis folder.

help me fix a FileNotFoundError when loading CSVs with pandas

Investigate the review scores

You know the data loaded successfully. Now try to answer the business question using just these two tables: why are customers leaving bad reviews?

  • Add the following code below the print(f"Reviews shape: {reviews.shape}") line:
# Review score distribution
score_dist = reviews["review_score"].value_counts(normalize=True).sort_index()
print("\nReview Score Distribution:")
print(score_dist)

low_reviews = reviews[reviews["review_score"] <= 2].shape[0] / reviews.shape[0]
print(f"\nPercentage of 1-2 star reviews: {low_reviews:.1%}")
print("Cannot determine root cause from reviews alone - comments are in Portuguese")
print("and we have no product or delivery detail in these two tables.\n")

What does this code do?

.value_counts(normalize=True) counts how many times each review score (1 through 5) appears and converts the counts to percentages. .sort_index() orders them from 1 to 5.

The low_reviews calculation filters to only rows where the score is 1 or 2, counts them, and divides by total rows to get the percentage of dissatisfied customers.

Before you run this, think about what the reviews and orders tables contain. Do you think you can determine WHY customers leave bad reviews from just these two tables?

  • Save analysis.py and run it again:
python analysis.py

You will see that roughly 25% of all orders received a 1 or 2-star review. That is the problem confirmed.

But the script also prints that it cannot determine the root cause. The review comments are in Portuguese, and neither table contains product details or delivery timing.

Why can't we find the cause yet?

This is exactly how real data analysis works. Your first data pull is almost never enough to answer the business question.

The orders table has timestamps and status codes, but no product or logistics info. The reviews table has scores and Portuguese comments you cannot easily parse. You need to merge in additional tables to find what drives those low scores.

Create a baseline visualization

A chart makes the problem impossible to ignore. You will create a bar chart showing the review score distribution and save it as your first output file.

  • Add the following code at the bottom of analysis.py, below the print statements you just added:
# Baseline visualization
fig, ax = plt.subplots(figsize=(8, 5))
score_counts = reviews["review_score"].value_counts().sort_index()
ax.bar(score_counts.index, score_counts.values, color=["#d32f2f", "#f57c00", "#fbc02d", "#7cb342", "#388e3c"])
ax.set_xlabel("Review Score")
ax.set_ylabel("Number of Orders")
ax.set_title("Customer Review Score Distribution")
ax.set_xticks([1, 2, 3, 4, 5])
plt.tight_layout()
plt.savefig("output/review_distribution.png", dpi=150)
plt.close()
print("Saved: output/review_distribution.png")

What does this visualization code do?

plt.subplots() creates a figure and axes object. The figsize=(8, 5) sets the chart to 8 inches wide and 5 inches tall.

Each bar gets a colour from red (1-star) through green (5-star) to make the distribution instantly readable. plt.savefig() writes the chart to a PNG file in your output/ folder. plt.close() frees memory by closing the figure after saving.

What do you expect to see when you run the script now? Think about whether a new file will appear on disk.

  • Save analysis.py and run it:
python analysis.py

You should see all previous output plus a new line: Saved: output/review_distribution.png.

  • Confirm the chart file was created by running:
ls output/

You should see review_distribution.png listed. Open it from your file explorer to see the colour-coded bar chart showing 5-star reviews as the tallest bar, with a significant chunk of 1-star reviews.

Chart file missing or empty?

Make sure plt.savefig() comes before plt.close() in your code. If you close the figure first, the save writes an empty file.

Also check that the output/ folder exists. The os.makedirs line near the top of your script should create it automatically.

help me debug why my matplotlib chart is not saving to disk

✔️ Awesome, I've got everything!

Great. Make sure you have saved analysis.py before moving on.

ⓧ I'd like to double check the full code

Your analysis.py should contain these sections in order. Scroll up through your file and confirm each one is present:

  • Imports: pandas, matplotlib.pyplot, and os.
  • Output folder creation with os.makedirs("output", exist_ok=True).
  • Loading orders and reviews DataFrames from CSV, with shape prints.
  • Review score distribution with .value_counts(normalize=True), low review percentage, and the "Cannot determine root cause" message.
  • Bar chart creation saved to output/review_distribution.png.

You have confirmed the problem: roughly 1 in 4 customers left a poor review. But two tables are not enough to explain why. Next, you will merge five tables together and clean the messy data to uncover what is actually driving those low scores.

Merge and Clean the Multi-Table Dataset

In the previous step, you proved that roughly 1 in 4 orders gets a bad review. But you also hit a wall: with only orders and reviews loaded, there is no way to explain why customers are unhappy.

The answer lives across multiple tables. Delivery timestamps, product categories, and pricing data are spread across separate CSV files that need to be joined together. This step is where you merge five related tables into one analysis-ready DataFrame and deal with real-world messiness: null dates, Portuguese category names, and records that need filtering.

In this step, get ready to:
  • Load and merge five related CSV tables into a single DataFrame.
  • Clean the merged data by converting dates, filtering records, and translating categories.
  • Calculate a delivery delay column that reveals whether orders arrived early or late.
Load the remaining CSVs and merge them together

The Olist dataset stores different types of information in separate files. Order items (prices, products) live in one CSV. Product details (category names) live in another. A translation table maps Portuguese category names to English.

You need to load these three additional files and join them all together with your existing orders and reviews DataFrames using pandas merge operations.

  • In analysis.py, scroll to the bottom of the file (after the line that says print("Saved: output/review_distribution.png")).
  • Add the following code to load the remaining CSVs and merge everything step by step:
# --- STEP 3: Merge and clean the multi-table dataset ---

print("\n=== STEP 3: Merging and Cleaning Data ===")

order_items = pd.read_csv("data/olist_order_items_dataset.csv")
products = pd.read_csv("data/olist_products_dataset.csv")
category_translation = pd.read_csv("data/product_category_name_translation.csv")

# Merge step by step
df = orders.merge(reviews[["order_id", "review_score"]], on="order_id", how="inner")
df = df.merge(order_items[["order_id", "product_id", "seller_id", "price", "freight_value"]], on="order_id", how="inner")
df = df.merge(products[["product_id", "product_category_name"]], on="product_id", how="left")
df = df.merge(category_translation, on="product_category_name", how="left")

print(f"Merged DataFrame shape: {df.shape}")

What does this code do?

Each .merge() call joins two tables on a shared column, like linking rows in a spreadsheet by a common ID.

  • orders + reviews on order_id connects each order to its review score.
  • + order_items on order_id brings in pricing and product references.
  • + products on product_id adds the Portuguese category name.
  • + category_translation on product_category_name maps each Portuguese name to its English equivalent.

Using how="inner" keeps only rows that match in both tables. Using how="left" keeps all rows from the left table even if there is no match on the right (those get null values).

  • Save analysis.py and run it in your terminal:
python analysis.py

You should see the Step 2 output followed by a new line showing the merged DataFrame shape. It will be over 100,000 rows with many columns.

Seeing a FileNotFoundError?

  • Make sure your data folder contains the CSV files directly (not nested inside another subfolder from the ZIP extraction).
  • The translation file is called product_category_name_translation.csv without the olist_ prefix. Double check the filename matches exactly.

Help me fix a FileNotFoundError when loading my CSV files in pandas.

Clean the merged data

Real-world data is messy. Your merged DataFrame has date columns stored as plain text, orders that were never delivered, rows with missing delivery dates, and category names stuck in Portuguese.

  • Add the following cleaning code directly below the print(f"Merged DataFrame shape: {df.shape}") line:
# Convert dates
date_cols = ["order_purchase_timestamp", "order_delivered_customer_date", "order_estimated_delivery_date"]
for col in date_cols:
    df[col] = pd.to_datetime(df[col])

# Filter to delivered orders only
df = df[df["order_status"] == "delivered"]
print(f"After filtering to delivered orders: {df.shape[0]} rows")

# Drop rows with null delivery dates
null_delivery_count = df["order_delivered_customer_date"].isna().sum()
df = df.dropna(subset=["order_delivered_customer_date"])
print(f"Dropped {null_delivery_count} rows with null delivery dates")

# Fill missing category translations
df["product_category_name_english"] = df["product_category_name_english"].fillna("other")

What does this cleaning do?

  • pd.to_datetime() converts text strings like "2017-10-02 10:56:33" into proper datetime objects you can do math with.
  • Filtering to order_status == "delivered" removes cancelled or in-transit orders that would skew your delivery analysis.
  • Dropping nulls in the delivery date column removes orders where the delivery was never recorded.
  • Filling missing English translations with "other" prevents errors when you group by category later.
  • Save analysis.py and run it again:
python analysis.py

You should now see two new lines printed: the number of rows after filtering to delivered orders, and the count of rows dropped due to null delivery dates.

Seeing a KeyError on a column name?

  • Check that your merge code from the previous chunk is correct. A misspelled column name in the merge means that column never makes it into df.
  • Make sure the date column names match exactly: order_purchase_timestamp, order_delivered_customer_date, order_estimated_delivery_date.

Help me debug a KeyError in my pandas DataFrame.

Calculate delivery delay

Each order has an estimated delivery date and an actual delivery date. The difference between these two dates tells you whether the order arrived early (negative number) or late (positive number).

  • Add this code below the fillna("other") line to create the delivery delay column and print its summary statistics:
# Calculate delivery delay
df["delivery_delay_days"] = (df["order_delivered_customer_date"] - df["order_estimated_delivery_date"]).dt.days

print(f"\nDelivery delay summary:")
print(df["delivery_delay_days"].describe())

What does this code do?

Subtracting two datetime columns gives a timedelta. The .dt.days accessor converts that timedelta into a plain integer (number of days). A positive value means the order arrived late. A negative value means it arrived early.

Before you run this, what do you expect the average delivery delay to be? Do you think most orders arrive early or late?

  • Save analysis.py and run it:
python analysis.py

In the delivery delay summary, you should see that the mean is slightly negative (most orders arrive a few days early on average). But look at the max value and the 75th percentile. Many orders have significant positive delays.

Why do many orders show positive delay?

Even though the average delivery is slightly early, a large chunk of orders arrive significantly past their estimated date. These late orders are the ones most likely to generate bad reviews. In the next step, you will prove this correlation with data.

Seeing NaN values in the summary?

  • This means some null delivery dates slipped through. Make sure your dropna(subset=["order_delivered_customer_date"]) line comes before the delay calculation.
  • Confirm that your date conversion loop runs before the delay calculation. The subtraction only works on datetime columns, not text strings.

Help me fix NaN values in my delivery delay calculation.

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.py and that your terminal output shows the merged shape, filtered row count, dropped nulls count, and the delivery delay summary stats.

ⓧ I'd like to double check the full code

Your analysis.py file is now longer than a quick glance can verify. Scroll through your file and confirm these sections appear in order:

  • Imports and output folder creation at the top (import pandas as pd, import matplotlib.pyplot as plt, import os).
  • Step 2 section: loading orders and reviews, printing the review distribution, and saving the bar chart.
  • Step 3 load and merge: loading order_items, products, and category_translation, then four .merge() calls building up df.
  • Step 3 cleaning: date conversion loop, filter to delivered, drop null delivery dates, fill missing categories.
  • Step 3 delivery delay: calculating delivery_delay_days and printing .describe().

Your data is now clean, merged, and ready to reveal the truth. In the next step, you will group by review score and prove that delivery delays are the root cause of customer dissatisfaction.

Analyze the Root Cause

In the previous step you merged five tables into one clean DataFrame with a delivery_delay_days column. You can see that the data has both positive values (late deliveries) and negative values (early deliveries).

Now comes the detective work. You have a hypothesis: delivery delays drive bad reviews. In this step, you will test that hypothesis by grouping the data in different ways and quantifying the impact.

In this step, get ready to:
  • Calculate average delivery delay for each review score to reveal the pattern.
  • Identify the worst-performing product categories and states.
  • Quantify how much worse the bad-review rate is for late orders vs. on-time orders.
Group delivery delay by review score

If late deliveries really cause bad reviews, then 1-star orders should have a much higher average delay than 5-star orders. A single groupby will reveal whether this pattern exists.

  • In analysis.py, find the last line that says print(df["delivery_delay_days"].describe()). Add the following code below it:
# --- STEP 4: Analyze the root cause ---

print("\n=== STEP 4: Root Cause Analysis ===")

# Average delay by review score
delay_by_score = df.groupby("review_score")["delivery_delay_days"].mean()
print("\nAverage delivery delay (days) by review score:")
print(delay_by_score)

What does this code do?

The .groupby("review_score") splits all orders into groups (one group per star rating: 1, 2, 3, 4, 5). Then .mean() calculates the average delivery delay within each group.

If the hypothesis is correct, you should see negative delay (early delivery) for 5-star reviews and positive delay (late delivery) for 1-star reviews.

  • Save analysis.py.
  • Run the script in your terminal:
python analysis.py
  • Scroll to the Step 4 output. You should see a table showing review scores 1 through 5, each with an average delay value. Notice the clear staircase: 5-star reviews have a negative average (delivered early), while 1-star reviews have a large positive average (delivered significantly late).

Not seeing Step 4 output?

Make sure your new code is placed after the Step 3 code, not inside it. The print("\n=== STEP 4: Root Cause Analysis ===") line should come after the line that prints the delivery delay summary.

If you see an error about delivery_delay_days not existing, check that the Step 3 code above it is still intact and calculating that column.

Help me debug my Step 4 groupby code.

Find the worst-performing categories and states

Knowing that late deliveries drive bad reviews is useful. But leadership needs to know WHERE to focus logistics improvements. Grouping by product category and customer state reveals which segments suffer the most.

  • In analysis.py, add the following code below the print(delay_by_score) line to find the worst product categories:
# Worst product categories
category_stats = df.groupby("product_category_name_english").agg(
    avg_delay=("delivery_delay_days", "mean"),
    avg_score=("review_score", "mean"),
    order_count=("order_id", "count")
).reset_index()

category_stats = category_stats[category_stats["order_count"] >= 50]
worst_categories = category_stats.sort_values("avg_score").head(10)
print("\nTop 10 worst categories by average review score:")
print(worst_categories[["product_category_name_english", "avg_delay", "avg_score", "order_count"]].to_string(index=False))

What does this code do?

The .agg() method lets you calculate multiple statistics at once for each category: average delay, average review score, and order count.

Filtering to categories with 50+ orders prevents tiny categories from skewing the results. A category with only 2 orders and 1 bad review would misleadingly top the list.

  • Save analysis.py and run it:
python analysis.py
  • In the output, you should now see a table of 10 product categories sorted by worst average review score. Notice that the categories with the worst scores also tend to have high average delivery delays.

Seeing a KeyError?

If you see an error referencing product_category_name_english, make sure the Step 3 merge with category_translation is still intact above. That merge creates this column.

Help me fix my category analysis code.

  • Now add the state analysis code directly below the category code you just added:
# Worst states
state_stats = df.groupby("customer_state").agg(
    avg_delay=("delivery_delay_days", "mean"),
    avg_score=("review_score", "mean"),
    order_count=("order_id", "count")
).reset_index()
worst_states = state_stats.sort_values("avg_delay", ascending=False).head(5)
print("\nTop 5 states by average delivery delay:")
print(worst_states[["customer_state", "avg_delay", "avg_score", "order_count"]].to_string(index=False))

What does this code do?

This groups orders by the customer's state (Brazilian states like SP, RJ, MG) and finds which states have the longest average delivery delays. The ascending=False ensures the worst (slowest) states appear at the top.

  • Save analysis.py and run it:
python analysis.py
  • You should now see a table of 5 states with the highest average delivery delay. These are the geographic regions where logistics improvements would have the biggest impact.

Not seeing state data?

If the customer_state column is missing, it may be because the original olist_orders_dataset.csv does not contain it directly. Check that the orders DataFrame merged correctly in Step 3. The customer_state column comes from the customers table through the orders table.

Help me debug the state groupby.

Quantify the late delivery impact

You can see the pattern in averages, but stakeholders need a single compelling number. Splitting orders into "late" and "on-time" cohorts and comparing their bad-review rates gives you that number.

  • Add the following code below the state analysis in analysis.py:
# Late delivery impact
late_orders = df[df["delivery_delay_days"] > 0]
ontime_orders = df[df["delivery_delay_days"] <= 0]

late_bad_review_rate = (late_orders["review_score"] <= 2).mean()
ontime_bad_review_rate = (ontime_orders["review_score"] <= 2).mean()

print(f"\nBad review rate (1-2 stars) for LATE orders: {late_bad_review_rate:.1%}")
print(f"\nBad review rate (1-2 stars) for ON-TIME orders: {ontime_bad_review_rate:.1%}")

What does this code do?

This splits all orders into two groups: late (delivered after the estimated date) and on-time (delivered on or before the estimated date).

For each group, it calculates what percentage received a 1 or 2-star review. The .mean() on a boolean Series gives you the proportion of True values, which is the bad-review rate.

  • Save analysis.py.

Before you run this, think about what you expect. Given everything you have seen so far about delivery delays and review scores, how large do you think the gap will be between late and on-time bad-review rates?

  • Run the full script:
python analysis.py

Scroll to the bottom of the output. You should see that the bad-review rate for late orders is dramatically higher than for on-time orders. This is your smoking gun: late delivery is the strongest predictor of customer dissatisfaction.

What should I see?

The full Step 4 output shows: average delivery delay per review score (5-star reviews have negative delay meaning early delivery, 1-star reviews have large positive delay meaning significantly late), the 10 worst product categories, the 5 worst states, and the late vs. on-time bad-review rates with a clear gap between them.

Numbers look unexpected?

If your late-order bad-review rate seems low, check that you are filtering delivery_delay_days > 0 (strictly greater than zero, not greater than or equal). Orders delivered exactly on the estimated date count as on-time.

If you see NaN for either rate, it likely means one of the filtered DataFrames is empty. Run print(late_orders.shape) to check that both cohorts contain rows.

Help me debug my late delivery rate calculation.

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.py with all four analysis blocks in place.

ⓧ I'd like to double check the full code

Your analysis.py file is now over 30 lines. Scroll through it and confirm these sections appear in order:

  • Imports and output folder creation at the top.
  • Step 2 block: loading orders and reviews, printing the distribution, saving review_distribution.png.
  • Step 3 block: loading remaining CSVs, merging, converting dates, filtering, dropping nulls, filling categories, calculating delivery_delay_days.
  • Step 4 block: delay_by_score groupby, category_stats and worst_categories, state_stats and worst_states, then late_orders and ontime_orders with bad-review rate prints.

You have now proven the root cause: delivery delays are the primary driver of bad customer reviews. You know which categories and states are worst. Next, you will turn these findings into clear visualizations and a written business recommendation that stakeholders can act on.

Visualize Findings and Write Your Recommendation

In the previous step, you proved that delivery delays are the strongest predictor of bad reviews. You identified the worst categories, the worst states, and quantified the gap between late and on-time orders.

Numbers in a terminal are great for you, but stakeholders need charts and a clear recommendation. This step turns your analysis into the deliverables you would actually hand to leadership: publication-ready visualizations and a one-paragraph business takeaway.

In this step, get ready to:
  • Create a bar chart proving that late deliveries cause bad reviews.
  • Create a horizontal bar chart showing the worst product categories by delivery delay.
  • Print a data-backed business recommendation with specific metrics.
Create the delay vs. score chart

Your first visualization is the "smoking gun" of your analysis. It shows, at a glance, that customers who receive their orders late leave dramatically worse reviews.

  • In analysis.py, scroll to the bottom of the file (after the last print statement about on-time orders).
  • Add this code to create a bar chart of average delivery delay per review score:
# --- STEP 5: Visualize and recommend ---

print("\n=== STEP 5: Visualizations and Recommendation ===")

# Chart 1: Delay vs Score
fig, ax = plt.subplots(figsize=(8, 5))
ax.bar(delay_by_score.index, delay_by_score.values, color=["#d32f2f", "#f57c00", "#fbc02d", "#7cb342", "#388e3c"])
ax.set_xlabel("Review Score")
ax.set_ylabel("Average Delivery Delay (Days)")
ax.set_title("Average Delivery Delay by Review Score")
ax.axhline(y=0, color="gray", linestyle="--", linewidth=0.8)
ax.set_xticks([1, 2, 3, 4, 5])
plt.tight_layout()
plt.savefig("output/delay_vs_score.png", dpi=150)
plt.close()
print("Saved: output/delay_vs_score.png")

What does this code do?

This creates a bar chart where each bar represents a review score (1 through 5) and its height shows the average number of days that delivery was late.

  • delay_by_score is the grouped average you calculated in Step 4. Each bar pulls directly from that Series.
  • ax.axhline(y=0) draws a dashed line at zero. Bars above it mean late delivery. Bars below mean early delivery.
  • plt.close() frees memory after saving. Without it, matplotlib keeps figures open in the background.
  • Save analysis.py.
  • Run your script to generate the chart:
python analysis.py
  • In the terminal output, look for the line Saved: output/delay_vs_score.png.
  • Open the output folder in the VS Code Explorer sidebar. You should now see delay_vs_score.png alongside the existing review_distribution.png.
  • Click delay_vs_score.png to preview it. You should see bars that slope downward from left to right: 1-star reviews have the tallest bar (highest delay), and 5-star reviews dip below the dashed zero line (early delivery).

Don't see the chart file?

  • Make sure you saved analysis.py before running it. Unsaved edits stay in the editor only.
  • Check that the new code is pasted after the Step 4 section, not inside it. If it lands inside the late_bad_review_rate calculation, indentation errors can silently skip your chart code.
  • Look for any NameError in the terminal. If you see one mentioning delay_by_score, it means the Step 4 code above it has a problem.

Help me debug why my delay_vs_score chart isn't saving.

Create the category performance chart

Your second chart answers a different question: WHERE should logistics improvements focus? A horizontal bar chart makes long category names readable without rotating text.

  • In analysis.py, add this code directly below the line print("Saved: output/delay_vs_score.png"):
# Chart 2: Worst categories
fig, ax = plt.subplots(figsize=(10, 6))
worst_categories_sorted = worst_categories.sort_values("avg_delay", ascending=True)
ax.barh(worst_categories_sorted["product_category_name_english"], worst_categories_sorted["avg_delay"], color="#d32f2f")
ax.set_xlabel("Average Delivery Delay (Days)")
ax.set_title("Top 10 Worst Product Categories by Delivery Delay")
plt.tight_layout()
plt.savefig("output/category_performance.png", dpi=150)
plt.close()
print("Saved: output/category_performance.png")

What does this code do?

  • worst_categories_sorted re-sorts the 10 worst categories by delay (ascending) so the longest bar appears at the top of the horizontal chart.
  • ax.barh() creates horizontal bars. This is better than vertical bars when your labels are long text strings like product category names.
  • The wider figure size (10, 6) gives the category names room to render without overlapping.
  • Save analysis.py and run the script again:
python analysis.py
  • In the terminal output, confirm you see Saved: output/category_performance.png.
  • Click category_performance.png in the Explorer sidebar to preview it. You should see horizontal red bars with product category names on the y-axis and delay in days on the x-axis.

Chart looks empty or labels are cut off?

  • If the chart has no bars, check that worst_categories is not empty. The Step 4 filter requires categories with at least 50 orders. If that variable is missing, the Step 4 code has an issue.
  • If category names are clipped on the left edge, make sure plt.tight_layout() is present before plt.savefig(). It adjusts margins automatically.

Help me fix my horizontal bar chart layout.

Write the business recommendation

Charts show the pattern, but a written recommendation tells leadership exactly what to do about it. This is the paragraph you would paste into a stakeholder email or present in an interview.

  • In analysis.py, add this code below the line print("Saved: output/category_performance.png"):
# Business recommendation
top_state = worst_states.iloc[0]["customer_state"]
top_category = worst_categories.sort_values("avg_delay", ascending=False).iloc[0]["product_category_name_english"]
late_order_count = late_orders.shape[0]

print(f"""
RECOMMENDATION: {late_bad_review_rate:.0%} of orders delivered late received 1-2 star
reviews, compared to {ontime_bad_review_rate:.0%} for on-time orders. Late delivery is
the strongest predictor of customer dissatisfaction. Prioritize reducing delivery
times in state '{top_state}' and for '{top_category}' products, which together
represent the highest concentration of delays. {late_order_count:,} orders were
delivered late in this dataset, each one a risk of losing a customer permanently.
""")

What does this code do?

  • top_state pulls the state with the worst average delivery delay from the worst_states DataFrame you built in Step 4.
  • top_category re-sorts the worst categories by delay (descending) and grabs the single worst one.
  • The f-string uses format specifiers like :.0% (percentage with no decimals) and :, (thousands separator) to make the numbers stakeholder-friendly.

Why write a recommendation programmatically?

Hardcoding the numbers would be faster, but pulling them from variables means the recommendation updates automatically if the data changes. In a real job, you would rerun this script monthly and the recommendation would always reflect the latest figures.

  • Save analysis.py.

Before you run the script, predict: what percentage of late orders do you think received bad reviews, compared to on-time orders?

  • Run the full script one final time:
python analysis.py

Scroll to the bottom of the terminal output. You should see a formatted RECOMMENDATION paragraph with real percentages, a state code, a category name, and a comma-separated order count.

  • Confirm that three PNG files now exist in your output folder by running:
ls output/

You should see review_distribution.png, delay_vs_score.png, and category_performance.png listed.

Seeing a KeyError or empty recommendation?

  • A KeyError: 'customer_state' means worst_states was not built correctly in Step 4. Check that you grouped on customer_state (not state).
  • If the recommendation prints but shows nan% instead of a real number, the late_bad_review_rate variable is empty. Confirm that late_orders has rows by checking the printed count from Step 4.
  • If you see only 2 files in output/, look for an error between the two chart sections. A typo in the chart 2 code would stop execution before the third file is created.

Help me debug my business recommendation output.

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.py and that all three PNG files are visible in your output folder.

ⓧ I'd like to double check the full code

Scroll through your analysis.py and confirm these sections are present in order:

  • The Step 2 section (loading orders and reviews, saving review_distribution.png).
  • The Step 3 section (merging five tables, cleaning dates, calculating delivery_delay_days).
  • The Step 4 section (grouping by score, finding worst categories and states, calculating late vs. on-time rates).
  • The Step 5 section you just added: the # --- STEP 5: Visualize and recommend --- comment, Chart 1 (delay vs score), Chart 2 (worst categories), and the business recommendation print.

If any section is missing or out of order, scroll up to the relevant chunk in this step and re-paste it into the correct position.

You now have a complete analysis: three visualizations proving your findings and a written recommendation that any stakeholder can act on. Next up, the Secret Mission challenges you to put a dollar figure on the problem.

Secret mission

Quantify Revenue at Risk from Late Deliveries

Your analysis proves delivery delays cause bad reviews. But leadership signs off on logistics investments when they see dollar signs. Can you calculate exactly how much revenue is at risk from late deliveries and make your recommendation impossible to ignore?

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 your machine, free up disk space by removing the dataset, or delete the project folder entirely.

Resources you used:

  • The ecommerce-analysis project folder on your Desktop.
  • The venv virtual environment folder (contains pandas and matplotlib).
  • The data subfolder with the Olist dataset (~120MB across 9 CSV files).
  • The output subfolder with your generated PNG charts.

Keep everything running

No action needed. Choose this if you plan to push this project to GitHub as a portfolio piece or want to continue refining your analysis.

  • Your project folder stays on your Desktop, ready to share.
  • Consider adding a README.md documenting your business question, approach, and findings before pushing to GitHub.
  • The venv folder takes up some disk space but means you can rerun analysis.py anytime without reinstalling libraries.

Pause - I'll come back to this later

Delete the virtual environment to reclaim disk space. You can recreate it anytime with a single install command.

  • In your terminal, make sure you are inside the ecommerce-analysis folder.
  • Deactivate your virtual environment by running:
deactivate
  • Delete the venv folder by running:
rm -rf venv
  • On Windows, run this instead:
rmdir /s /q venv
  • When you come back, recreate the environment by running python -m venv venv, activating it, then running pip install pandas matplotlib.
  • Your analysis.py, data folder, and output folder remain intact.

Delete - I don't want to use this again

Remove the entire project folder and all its contents. This deletes your script, dataset, charts, and virtual environment.

  • In your terminal, deactivate the virtual environment first:
deactivate
  • Navigate to your Desktop by running:
cd ~/Desktop
  • Delete the entire project folder by running:
rm -rf ecommerce-analysis
  • On Windows, run this instead:
rmdir /s /q ecommerce-analysis
  • Confirm the folder is gone by running:
ls
  • You should no longer see ecommerce-analysis in the output.

Nice Work!

Nice Work!

You just completed a full data analysis workflow from raw CSVs to a business recommendation backed by real numbers. You diagnosed why customers leave bad reviews in a 100,000-order e-commerce dataset and delivered the answer with clear visualizations.

You've learned how to:

  • Load, clean, and merge five related CSV tables into one analysis-ready DataFrame using pandas, handling null values, filtering rows, and translating foreign-language categories.
  • Perform exploratory data analysis to discover that delivery delays are the strongest predictor of poor customer reviews, grouping by review score, product category, and state.
  • Create publication-ready visualizations with matplotlib and write a data-backed business recommendation with specific metrics that you could present in an interview.
  • Secret Mission: Quantified the revenue at risk from late deliveries by segmenting orders into on-time vs. late cohorts and calculating the dollar amount tied to dissatisfied customers.

Ready to quiz yourself?