Analyze E-Commerce Sales with Python

Explore 100k real orders to uncover revenue and delivery insights.

Introduction

30 Second Summary

Every time you buy something online, a row of data is born. Multiply that by 100,000 orders and you have the raw material that companies use to decide which products to promote, which warehouses to expand, and which delivery problems to fix.

In this project, you will analyze a real dataset of 100,000 e-commerce orders from Brazil's Olist marketplace. You will clean messy data, merge multiple files into one analysis-ready table, build publication-quality charts, and write a business recommendation backed by numbers.

What You'll Build

When you run your final notebook, three polished charts appear alongside a printed summary of key findings that you could walk a hiring manager through in under two minutes.

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

  • A horizontal bar chart that lets you identify the top revenue-generating product categories at a glance.
  • A box plot revealing the clear relationship between late deliveries and low customer review scores.
  • A data-backed business recommendation in the SCQA format, ready to anchor a portfolio or walk through in an interview.
  • A reusable EDA template (load, clean, merge, analyze, visualize, recommend) that works for any tabular dataset.
  • Secret Mission: Add a monthly revenue time-series chart to spot seasonal trends.

Are there any prerequisites?

You should be comfortable with basic Python (variables, loops, functions) and have a general understanding of what CSV files are. No prior experience with pandas or data visualization libraries is required.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Your Environment

You are about to analyze 100,000 real e-commerce orders. Before you can load a single row of data, you need a project folder, the raw data files, and the right Python libraries installed.

In this step, you will create the project structure, download the dataset from Kaggle, and install pandas, matplotlib, and seaborn. You will finish by running your first Jupyter notebook cell to confirm everything works.

In this step, get ready to:
  • Create a project folder with data/ and outputs/ subdirectories.
  • Install pandas, seaborn, and matplotlib.
  • Create a Jupyter notebook and verify imports run without errors.
Create the project folder and download the data

Your analysis will live in a dedicated folder on your Desktop, with the raw CSV files in a data/ subfolder and any charts you save in an outputs/ subfolder.

  • 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+` (backtick key, both macOS and Windows).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and subfolders by running these commands:
mkdir olist-eda
cd olist-eda
mkdir data outputs
  • Confirm the folders exist by running:
ls

You should see data and outputs listed in the output.

Don't see both folders?

Make sure you ran all three commands in order. If you see only olist-eda in the output, you may still be on your Desktop. Run cd olist-eda first, then ls again.

Help me fix my project folder structure.

Now you need the raw data files. The Olist dataset is hosted on Kaggle as a collection of CSV files.

  • Open the dataset page in your browser: Brazilian E-Commerce Public Dataset by Olist.
  • Sign in to your Kaggle account if you are not already signed in.
  • Click the Download button near the top of the page to download the zip file.
  • Extract the zip file on your computer.
  • Copy these five CSV files into your olist-eda/data/ folder (on your Desktop):
  • olist_orders_dataset.csv
  • olist_order_items_dataset.csv
  • olist_products_dataset.csv
  • olist_order_reviews_dataset.csv
  • product_category_name_translation.csv

What about the other CSV files in the zip?

The Olist dataset contains 9 CSV files total. This project only uses the 5 files listed above. The geolocation, customers, sellers, and payments files are not needed for the revenue and delivery analysis.

  • Back in your VS Code terminal, verify the files are in place by running:
ls data/

You should see all five CSV file names listed in the output.

Don't see all five files?

Make sure you copied the files into the data/ subfolder inside olist-eda, not into olist-eda itself. The full path should be ~/Desktop/olist-eda/data/.

Help me find where my CSV files ended up.

Install the Python libraries

pandas handles data loading and manipulation. seaborn and matplotlib produce the charts. You will install specific versions of pandas and seaborn to ensure compatibility.

  • Check your Python version by running:
python --version

You should see Python 3.9 or higher in the output. pandas 2.2.3 requires Python 3.9 as a minimum.

Seeing a version below 3.9, or command not found?

On macOS, try python3 --version instead. If you see a version lower than 3.9, you need to install a newer Python from python.org before continuing.

Help me get the right Python version.

  • Install pandas, seaborn, and matplotlib by running:
pip install pandas==2.2.3 seaborn==0.13.2 matplotlib

What does this command do?

The == syntax pins pandas to version 2.2.3 and seaborn to 0.13.2. These versions are tested together. matplotlib is left unpinned so pip picks the latest version compatible with your Python.

  • Verify the install succeeded by running:
pip show pandas

Look for the line that says Version: 2.2.3. This confirms pandas installed at the correct version.

Seeing permission errors or pip not found?

If you get a permissions error, try adding --user to the end of the install command. On macOS, if pip is not found, try pip3 instead.

Help me fix my pip install.

Create your Jupyter notebook and verify imports

Your analysis will live in a Jupyter notebook file. VS Code renders .ipynb files as interactive notebooks thanks to the Jupyter extension you already have installed.

  • Create the notebook file by running:
touch analysis.ipynb
  • In the VS Code Explorer sidebar (left panel), click on analysis.ipynb to open it.

VS Code opens the file as an interactive notebook with an empty code cell ready for input.

  • Before you run the imports, what do you expect to happen if one of the libraries failed to install?
  • Type the following code into the first cell of your notebook:
# Cell 1: Imports
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
  • Run the cell by pressing Shift+Enter.

What does this code do?

These three imports load the libraries you just installed. pd, plt, and sns are standard shorthand aliases that the Python data community uses everywhere.

If any library failed to install, you would see a ModuleNotFoundError here. A clean run with no output means all three loaded successfully.

The cell should complete with no output and no errors. A small number appears to the left of the cell indicating it ran.

Seeing ModuleNotFoundError?

This means the notebook is using a different Python than the one you installed the libraries into. Click the kernel name in the top-right corner of the notebook and select the Python that matches the version you checked earlier.

Help me fix my notebook kernel.

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.ipynb with the imports cell already run.

ⓧ I'd like to double check the full code

Your first cell should contain exactly this:

# Cell 1: Imports
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

Your environment is ready and all three libraries are confirmed working. Next up, you will load the raw CSV files into pandas and get your first look at the data.

Load and Explore the Data

Your environment is set up with pandas, matplotlib, and seaborn ready to go. Now it's time to open the raw data and see what you're working with.

Before you can answer any business question, you need to understand your dataset. The Olist marketplace data is spread across five separate CSV files, and this step reveals why it cannot be analyzed as-is.

In this step, get ready to:
  • Load five CSV files into pandas DataFrames and confirm their sizes.
  • Inspect column types and spot data quality issues.
  • Discover the obstacle: categories in Portuguese and prices in a separate file.
Load the five CSV files into DataFrames

Each CSV file contains a different slice of the Olist e-commerce data: orders, line items, products, reviews, and category translations. Loading them all into DataFrames and printing their shapes tells you how much data you're working with.

  • In analysis.ipynb, create a new cell below your imports and paste the following code to load all five files:
# Load each CSV file into its own DataFrame
orders = pd.read_csv("data/olist_orders_dataset.csv")
order_items = pd.read_csv("data/olist_order_items_dataset.csv")
products = pd.read_csv("data/olist_products_dataset.csv")
reviews = pd.read_csv("data/olist_order_reviews_dataset.csv")
category_translation = pd.read_csv("data/product_category_name_translation.csv")

# Print dimensions to confirm each file loaded
print("Orders:", orders.shape)
print("Order items:", order_items.shape)
print("Products:", products.shape)
print("Reviews:", reviews.shape)
print("Category translation:", category_translation.shape)

What does this code do?

Each pd.read_csv() call reads one CSV file from your data/ folder and converts it into a DataFrame (a table with rows and columns).

The .shape property returns a tuple of (rows, columns). Printing it confirms the file loaded and shows how large each dataset is.

  • Run the cell by pressing Shift+Enter.
  • You should see five lines of output showing the shape of each DataFrame. The orders line should read Orders: (99441, 8).

Seeing a FileNotFoundError?

This means pandas cannot find the CSV files in the expected location. Make sure your five Kaggle CSV files are inside the data/ subfolder of your olist-eda project folder.

Check that the filenames match exactly (e.g. olist_orders_dataset.csv, not orders.csv). Help me fix a FileNotFoundError when loading CSV files

Inspect the orders DataFrame

Now that the data is loaded, you need to check what each column actually contains and whether pandas interpreted the types correctly.

  • Create a new cell below and run the following to inspect the orders table:
# Check column types and preview the first few rows
print(orders.info())
print(orders.head())
  • Run the cell with Shift+Enter.
  • Look at the Dtype column in the .info() output. You should see the timestamp columns (like order_purchase_timestamp and order_delivered_customer_date) listed as object type.

Why are dates showing as object type?

When pandas reads a CSV, it treats any column it cannot auto-detect as plain text, stored under the object dtype. Columns like order_purchase_timestamp contain date strings like 2017-10-02 10:56:33 but pandas has not parsed them into proper datetime values yet.

This matters because you cannot do date arithmetic (like calculating delivery time) on strings. You will fix this in the next step when you clean the data.

Peek at the product categories

The business question you want to answer is: which product categories drive the most revenue? To answer that, you need category names you can read.

  • Before you run this next cell, think: do you have everything you need in one place to determine the top revenue category? Where do prices live? Where do category names live?
  • Create a new cell and run this to see what the category data looks like:
# See what categories look like in the raw data
print(products["product_category_name"].value_counts().head(10))
  • Run the cell with Shift+Enter.

The output shows Portuguese category names like beleza_saude and informatica_acessorios.

Try to answer the question: which category generates the most revenue? You cannot, for three reasons.

Why can't you answer the business question yet?

  • The category names are in Portuguese, so you cannot interpret them without the translation table.
  • The products table has categories but no prices. Prices live in the order_items table.
  • The data is scattered across five files with no single row containing category, price, and review score together.

This is the core problem of real-world data analysis. The data arrives fragmented, and your job is to assemble it into something usable.

✔️ Awesome, I've got everything!

Great. Double check you have saved analysis.ipynb with all four cells (imports, load, info/head, category peek) run successfully.

ⓧ I'd like to double check the full code

Here is the full notebook code through this step. Each comment marks where a new cell begins:

# Cell 1: Imports
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Cell 2: Load data
orders = pd.read_csv("data/olist_orders_dataset.csv")
order_items = pd.read_csv("data/olist_order_items_dataset.csv")
products = pd.read_csv("data/olist_products_dataset.csv")
reviews = pd.read_csv("data/olist_order_reviews_dataset.csv")
category_translation = pd.read_csv("data/product_category_name_translation.csv")

print("Orders:", orders.shape)
print("Order items:", order_items.shape)
print("Products:", products.shape)
print("Reviews:", reviews.shape)
print("Category translation:", category_translation.shape)

# Cell 3: Explore orders
print(orders.info())
print(orders.head())

# Cell 4: Peek at categories (the obstacle)
print(products["product_category_name"].value_counts().head(10))

You have loaded 100,000 orders and discovered three blockers: string-typed dates, Portuguese category names, and information scattered across multiple files. In the next step, you will merge and clean everything into a single analysis-ready DataFrame.

Clean and Merge the Data

In the last step, you saw the problem clearly. Category names are in Portuguese, prices live in a different table than categories, and timestamps are stored as plain strings.

The answer is to merge all five DataFrames into one unified dataset, then clean it so every column has the right type and only usable rows remain. By the end of this step, you will have a single, analysis-ready DataFrame with English categories, proper dates, and new delivery-performance columns.

In this step, get ready to:
  • Merge five DataFrames into a single unified dataset using pandas join operations.
  • Clean the data by filtering, converting types, and engineering new columns.
  • Confirm the clean dataset is ready for analysis with English category names.
Merge the DataFrames into one dataset

Each of your five DataFrames holds a different piece of the puzzle. The order_items table has prices, the products table has category names, the category_translation table has English versions, and orders and reviews have timestamps and scores.

  • Create a new cell in your notebook (Cell 5) and chain four merge operations to connect them all by running this code:
# Join order_items with products
items_products = order_items.merge(
    products[["product_id", "product_category_name"]],
    on="product_id",
    how="left"
)

# Translate categories to English
items_products = items_products.merge(
    category_translation,
    on="product_category_name",
    how="left"
)

# Join with orders for timestamps and status
df = items_products.merge(
    orders[["order_id", "order_status", "order_purchase_timestamp",
            "order_delivered_customer_date", "order_estimated_delivery_date"]],
    on="order_id",
    how="left"
)

# Join with reviews
df = df.merge(
    reviews[["order_id", "review_score"]],
    on="order_id",
    how="left"
)

What does this code do?

Each .merge() call works like a SQL JOIN, connecting rows from two tables on a shared column.

  • The first merge connects order_items to products using product_id, so each line item now has its Portuguese category name.
  • The second merge attaches the English translation using product_category_name as the key.
  • The third merge brings in order timestamps and status using order_id.
  • The fourth merge adds customer review scores, also joined on order_id.

Using how="left" keeps every row from the left table even if no match exists on the right. Unmatched rows get NaN values, which you will handle in the cleaning step.

  • Run the cell.
  • In a new cell below, type df.shape and run it to confirm the merge worked.
df.shape

You should see approximately (115000+, 12) rows. The count is larger than the original 99,441 orders because some orders have multiple line items.

Seeing a NameError?

  • Make sure you ran Cells 1-4 first. The merge depends on the DataFrames you loaded earlier.
  • Check that your variable names match exactly: order_items, products, category_translation, orders, reviews.
  • help me debug a NameError in my pandas merge cell
Clean the merged DataFrame

The merged DataFrame has everything you need, but it also has noise. Orders that were never delivered, dates still stored as strings, and rows with missing translations would all skew your analysis.

  • Delete the df.shape check cell you just used (it was temporary). Then create a new Cell 6 and add the cleaning code by running:
# Filter to delivered orders only
df = df[df["order_status"] == "delivered"].copy()

# 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"])

# Calculate delivery metrics
df["delivery_days"] = (df["order_delivered_customer_date"] - df["order_purchase_timestamp"]).dt.days
df["delay_days"] = (df["order_delivered_customer_date"] - df["order_estimated_delivery_date"]).dt.days

# Drop rows with missing category translation
df = df.dropna(subset=["product_category_name_english"])

# Rename for convenience
df = df.rename(columns={"product_category_name_english": "category"})

print(f"Clean dataset: {df.shape[0]} rows, {df.shape[1]} columns")
print(f"\nMissing values:\n{df.isnull().sum()}")

What does this code do?

  • Filtering to order_status == "delivered" removes cancelled and processing orders that have no delivery data.
  • pd.to_datetime() converts the string timestamps into proper datetime objects so you can do date arithmetic.
  • delivery_days measures total days from purchase to delivery. delay_days measures how many days late (positive) or early (negative) the delivery was compared to the estimate.
  • dropna() removes the small number of products that have no English translation in the lookup table.
  • The final rename shortens product_category_name_english to just category so your later code is easier to read.
  • Run Cell 6.

You should see output showing approximately 95,000+ rows and 13 columns, followed by a count of missing values per column.

Seeing a KeyError on product_category_name_english?

  • This column only exists after the second merge in Cell 5 (the one with category_translation). Re-run Cell 5, then Cell 6.
  • If the column name looks different, run df.columns.tolist() to see what columns actually exist in your DataFrame.
  • help me fix a KeyError in my pandas cleaning cell
Verify the clean dataset

Before you run the next command, predict what language the category names will be in. Portuguese or English?

  • In a new cell, run this to check your top categories:
df["category"].value_counts().head(3)

You should see English category names like bed_bath_table, health_beauty, and sports_leisure. The merge and translation worked.

Why are the names in snake_case?

The Olist translation table uses underscores instead of spaces (bed_bath_table instead of "Bed, Bath & Table"). This is actually convenient for code since you can reference them without worrying about spaces or special characters.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook with Cells 1 through 6 present and all outputs visible.

ⓧ I'd like to double check the full code

Your notebook should now have 6 cells. Cells 1-4 are unchanged from the previous step. Here are the two new cells you added:

Cell 5:

items_products = order_items.merge(
    products[["product_id", "product_category_name"]],
    on="product_id",
    how="left"
)

items_products = items_products.merge(
    category_translation,
    on="product_category_name",
    how="left"
)

df = items_products.merge(
    orders[["order_id", "order_status", "order_purchase_timestamp",
            "order_delivered_customer_date", "order_estimated_delivery_date"]],
    on="order_id",
    how="left"
)

df = df.merge(
    reviews[["order_id", "review_score"]],
    on="order_id",
    how="left"
)

Cell 6:

df = df[df["order_status"] == "delivered"].copy()

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"])

df["delivery_days"] = (df["order_delivered_customer_date"] - df["order_purchase_timestamp"]).dt.days
df["delay_days"] = (df["order_delivered_customer_date"] - df["order_estimated_delivery_date"]).dt.days

df = df.dropna(subset=["product_category_name_english"])

df = df.rename(columns={"product_category_name_english": "category"})

print(f"Clean dataset: {df.shape[0]} rows, {df.shape[1]} columns")
print(f"\nMissing values:\n{df.isnull().sum()}")

You now have a single, analysis-ready DataFrame with English categories, proper dates, and delivery-performance metrics. Next up, you will use this clean dataset to build visualizations that reveal revenue patterns and delivery insights.

Analyze and Visualize

You now have a single, clean DataFrame with 95,000+ rows of e-commerce data ready for analysis. The hard data-wrangling work is done.

Clean data is only useful once you extract patterns from it. In this step, you will answer the original business question by aggregating revenue by category, comparing delivery performance to customer reviews, and saving three publication-quality charts to your outputs/ folder.

In this step, get ready to:
  • Create a horizontal bar chart showing the top 10 product categories by revenue.
  • Build a box plot revealing the relationship between delivery delays and review scores.
  • Chart order volume by category and compare it to the revenue chart.
Chart the top 10 categories by revenue

The first question to answer is: which product categories bring in the most money? A groupby aggregation sums revenue per category, and a horizontal bar chart makes the ranking instantly scannable.

  • Create a new cell in your notebook (Cell 7) and add the following code to group revenue by category and plot it:
revenue_by_category = df.groupby("category")["price"].sum().sort_values(ascending=False).head(10)

fig, ax = plt.subplots(figsize=(10, 6))
revenue_by_category.plot(kind="barh", ax=ax, color="steelblue")
ax.set_xlabel("Total Revenue (BRL)")
ax.set_ylabel("")
ax.set_title("Top 10 Product Categories by Revenue")
ax.invert_yaxis()
plt.tight_layout()
plt.savefig("outputs/revenue_by_category.png", dpi=150, bbox_inches="tight")
plt.show()

What does this code do?

The first line does all the heavy lifting. It groups rows by category, sums the price column for each group, sorts from highest to lowest, and keeps only the top 10.

The rest creates a matplotlib figure with a horizontal bar chart (kind="barh"). invert_yaxis() puts the highest-revenue category at the top. The chart is saved as a PNG and displayed inline.

  • Run Cell 7.

You should see a horizontal bar chart with steelblue bars. The longest bar (top category) should be a household-related category like bed_bath_table or health_beauty.

Chart not showing?

  • If you see FileNotFoundError for the save path, make sure your outputs/ folder exists. Run mkdir outputs in your terminal if needed.
  • If the chart appears blank, confirm that Cell 6 ran successfully and that df has a category column with English names.
  • Why isn't my revenue bar chart displaying in the notebook?
Visualize delivery delay vs. review score

Revenue tells you what sells, but customer satisfaction determines repeat business. A box plot comparing delivery delay to review score reveals whether late deliveries hurt ratings.

  • Create a new cell (Cell 8) and add this code to build a seaborn box plot:
fig, ax = plt.subplots(figsize=(8, 5))
sns.boxplot(data=df, x="review_score", y="delay_days", ax=ax, palette="RdYlGn")
ax.set_xlabel("Review Score")
ax.set_ylabel("Delivery Delay (days vs. estimated)")
ax.set_title("Delivery Delay vs. Customer Review Score")
ax.axhline(y=0, color="gray", linestyle="--", alpha=0.7)
plt.tight_layout()
plt.savefig("outputs/delay_vs_reviews.png", dpi=150, bbox_inches="tight")
plt.show()

What does this chart reveal?

Each box shows the distribution of delay_days for orders with that review score. Positive delay means the order arrived late. Negative means it arrived early.

The gray dashed line at y=0 marks on-time delivery. The RdYlGn palette colors low scores red and high scores green, making the pattern pop visually. You should see that 1-star reviews have much higher delay values than 5-star reviews.

  • Run Cell 8.

You should see a box plot with 5 columns (one per review score). The boxes for 1-star and 2-star reviews extend well above the dashed gray line, confirming that late deliveries correlate with low ratings.

Seeing a seaborn error?

  • If you see a TypeError about the palette, confirm you installed seaborn==0.13.2 in Step 1.
  • If the y-axis shows all zeros, check that your delay_days column was calculated correctly in Cell 6. Run df["delay_days"].describe() to verify it has a range of values.
  • Why is my seaborn box plot throwing an error or showing unexpected results?
Chart order volume by category

High revenue does not always mean high volume. Some categories earn big on fewer expensive orders. Comparing order count to revenue reveals which categories are truly popular versus which simply have higher price tags.

  • Create a new cell (Cell 9) and add this code to count unique orders per category and chart the top 10:
order_count = df.groupby("category")["order_id"].nunique().sort_values(ascending=False).head(10)

fig, ax = plt.subplots(figsize=(10, 6))
order_count.plot(kind="barh", ax=ax, color="darkorange")
ax.set_xlabel("Number of Orders")
ax.set_ylabel("")
ax.set_title("Top 10 Categories by Order Volume")
ax.invert_yaxis()
plt.tight_layout()
plt.savefig("outputs/orders_by_category.png", dpi=150, bbox_inches="tight")
plt.show()

What does this code do?

Instead of summing price, this groups by category and counts unique order_id values with .nunique(). This tells you how many separate orders each category received, regardless of the order's total value.

Compare this orange chart to the steelblue revenue chart above. Categories that appear in one but not the other tell an interesting story about pricing strategy.

  • Run Cell 9.

You should see an orange horizontal bar chart. Notice whether the top volume categories match the top revenue categories. Some high-volume categories like bed_bath_table appear in both, while others differ.

Chart looks identical to the revenue chart?

  • Make sure you used .nunique() (count unique orders) instead of .sum() (sum prices). The variable should be order_count, not revenue_by_category.
  • Confirm the bar color is darkorange. If it is steelblue, you may have re-run Cell 7 by accident.
  • Help me debug why my order volume chart looks wrong.

✔️ Awesome, I've got everything!

Great. Confirm you have saved your notebook with all three chart cells (Cells 7, 8, and 9) visible and producing output.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm these cells are present in order, each producing a chart:

  • Cell 7: Revenue by category. Groups by category, sums price, plots a steelblue horizontal bar chart, saves to outputs/revenue_by_category.png.
  • Cell 8: Delivery delay vs review score. Uses sns.boxplot with RdYlGn palette, saves to outputs/delay_vs_reviews.png.
  • Cell 9: Order volume by category. Groups by category, counts unique order_id values, plots a darkorange horizontal bar chart, saves to outputs/orders_by_category.png.
Verify your saved charts

Before you move on, predict: how many PNG files should be in your outputs/ folder right now?

  • Confirm your chart files were saved by running this in a new cell or in your terminal:
ls outputs/

You should see three files: revenue_by_category.png, delay_vs_reviews.png, and orders_by_category.png.

You have three charts that make the data patterns visible at a glance. Next up, you will translate these findings into a written business recommendation that ties the numbers to an actionable takeaway.

Tell the Business Story

Your charts are done. You have a revenue bar chart, a delivery delay box plot, and an order volume comparison saved in your outputs/ folder.

Charts without context are just decoration. Analysts get hired because they translate numbers into recommendations that non-technical stakeholders can act on. In this step, you will extract key statistics, write a business recommendation, and document your methodology so the notebook stands on its own.

In this step, get ready to:
  • Print a summary of key findings from your analysis.
  • Write a one-paragraph business recommendation in SCQA format.
  • Document your methodology so the notebook is self-contained.
Print key summary statistics

Before writing a recommendation, you need the exact numbers to cite. A summary cell pulls the key values out of your DataFrame so you can reference them with precision.

  • Create a new code cell below Cell 9 in your notebook.
  • Add the following code to print key findings from your analysis:
print("=== KEY FINDINGS ===")
print(f"\nTop revenue category: {revenue_by_category.index[0]} (BRL {revenue_by_category.iloc[0]:,.2f})")
print(f"Total categories analyzed: {df['category'].nunique()}")
print(f"Average delivery time: {df['delivery_days'].mean():.1f} days")
print(f"Orders delivered late: {(df['delay_days'] > 0).sum()} ({(df['delay_days'] > 0).mean()*100:.1f}%)")
print(f"\nAvg review score for on-time deliveries: {df[df['delay_days'] <= 0]['review_score'].mean():.2f}")
print(f"Avg review score for late deliveries: {df[df['delay_days'] > 0]['review_score'].mean():.2f}")

What does this code do?

Each print statement extracts one key metric from your cleaned DataFrame.

  • revenue_by_category.index[0] grabs the name of the top-earning category (it is already sorted from Cell 7).
  • df['delay_days'] > 0 isolates late orders (delivered after the estimated date).
  • The final two lines split reviews into on-time vs. late groups so you can compare satisfaction directly.

Before you run this cell, what do you expect the relationship to be between late deliveries and review scores?

  • Run the cell.

You should see a printed summary showing the top revenue category name and its BRL total, the total number of categories, average delivery time in days, the count and percentage of late orders, and a clear gap in average review score between on-time and late deliveries.

Seeing a NameError?

If you see NameError: name 'revenue_by_category' is not defined, it means Cell 7 has not been run in this session. Re-run all cells from the top by selecting the "Run All" button in your notebook toolbar.

If a print line shows NaN, check that Cell 6 ran successfully and that df has rows remaining after filtering.

Still stuck? Help me debug my summary statistics cell

Write a business recommendation

Numbers alone do not persuade. The SCQA framework (Situation, Complication, Question, Answer) is a standard consulting structure that turns data into a story a stakeholder can act on.

What is SCQA?

  • Situation: state a fact the audience already agrees with (what the data shows).
  • Complication: name the problem or tension the data reveals.
  • Question: the decision the stakeholder faces (implied or explicit).
  • Answer: your specific, data-backed recommendation.
  • Create a new cell below Cell 10.
  • Change the cell type to Markdown by clicking the cell type dropdown in the toolbar (it will say "Code" by default) and selecting Markdown.
  • Write a one-paragraph recommendation using the SCQA structure. Reference specific values from your Cell 10 output: the top category name and revenue, the percentage of late orders, and the review score gap.

Here is an example structure you can follow (replace the placeholder values with your actual numbers):

## Business Recommendation

Health and beauty is the top revenue category at BRL X. However, Y% of all
orders arrive late, and late orders score Z stars lower on average than on-time
orders. Reducing delivery delays in the top 3 revenue categories through
shipping priority would protect the review scores that drive repeat purchases.

Use your real numbers

Replace X, Y, and Z with the exact values Cell 10 printed. Citing precise numbers is what separates a data-backed recommendation from an opinion.

  • Run the markdown cell to render it. You should see your recommendation displayed as formatted text with a heading.
Document your methodology

A notebook without a methodology section forces the reader to reverse-engineer your decisions. Adding one makes the notebook self-contained and portfolio-ready.

  • Create another new cell below your recommendation.
  • Change the cell type to Markdown.
  • Write a methodology section that documents four things: which source files you used, how you merged them, what cleaning steps you applied, and what assumptions you made.

Here is an example you can adapt:

## Methodology

**Data sources:** 5 CSV files from the Olist Brazilian E-Commerce dataset
(orders, order_items, products, reviews, category_translation).

**Merge steps:** order_items joined to products on `product_id`, then to
category_translation on `product_category_name`, then to orders on `order_id`,
then to reviews on `order_id`.

**Cleaning:** Filtered to delivered orders only. Converted timestamp strings
to datetime. Calculated `delivery_days` and `delay_days`. Dropped rows with
missing category translations. Renamed English category column to `category`.

**Assumptions:** Only orders with status "delivered" are included. Orders
missing a category translation are excluded from analysis.
  • Run the markdown cell to render it.

You should see your methodology rendered with bold labels and formatted text. Your notebook now tells a complete story from raw data to actionable insight.

Markdown cell showing raw text instead of formatted output?

Make sure the cell type is set to Markdown (not Code) in the dropdown. If you run a markdown cell that is still set to Code, it will error or print raw text instead of rendering.

Need more help? Help me format my Jupyter markdown cell

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook.

ⓧ I'd like to double check the full code

Here is the complete code for Cell 10 (the only new code cell in this step):

print("=== KEY FINDINGS ===")
print(f"\nTop revenue category: {revenue_by_category.index[0]} (BRL {revenue_by_category.iloc[0]:,.2f})")
print(f"Total categories analyzed: {df['category'].nunique()}")
print(f"Average delivery time: {df['delivery_days'].mean():.1f} days")
print(f"Orders delivered late: {(df['delay_days'] > 0).sum()} ({(df['delay_days'] > 0).mean()*100:.1f}%)")
print(f"\nAvg review score for on-time deliveries: {df[df['delay_days'] <= 0]['review_score'].mean():.2f}")
print(f"Avg review score for late deliveries: {df[df['delay_days'] > 0]['review_score'].mean():.2f}")

The two markdown cells are your own writing. Confirm you have a Business Recommendation cell referencing your specific data values, and a Methodology cell documenting your source files, merge steps, cleaning steps, and assumptions.

Your notebook now tells a complete story. You started with messy, multi-file data and ended with charts, statistics, and a written recommendation that you could walk a hiring manager through in under two minutes.

Secret mission

Add a Monthly Revenue Time-Series Chart

Your bar charts show what happened across categories, but they hide when it happened. Add a time-series line chart that reveals monthly revenue trends from 2016 to 2018, and spot the seasonal patterns hiding in the data.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs or running services to worry about. Decide whether to keep your project folder for your portfolio, or delete it to free up disk space.

Resources you used:

  • The olist-eda project folder on your Desktop (contains CSV data files totaling ~45 MB, output PNG charts, and your notebook).
  • Python packages installed in your environment: pandas, seaborn, matplotlib.

Keep everything running

No action needed. Choose this if you want to keep your analysis notebook as a portfolio piece or continue experimenting with the data.

  • Your olist-eda folder stays on your Desktop. There are no running services and no ongoing costs.
  • Consider pushing your notebook to GitHub so hiring managers can see your EDA workflow.
  • The installed Python packages (pandas, seaborn, matplotlib) remain available for future projects.

Pause - I'll come back to this later

Nothing is actively running in this project. There are no servers, containers, or cloud services to pause.

  • Your project folder and installed packages remain on your machine at no cost.
  • When you come back, open analysis.ipynb in VS Code and run all cells from the top to reload the data into memory.

Delete - I don't want to use this again

Remove all project files and uninstall the Python packages to start completely fresh.

  • Delete the project folder by running this command in your terminal:

macOS

rm -rf ~/Desktop/olist-eda

Windows

rmdir /s /q %USERPROFILE%\Desktop\olist-eda
  • Uninstall the Python packages by running:
pip uninstall pandas seaborn matplotlib -y
  • Confirm the folder is gone by checking your Desktop. You should no longer see olist-eda listed.

Nice Work!

Nice Work!

Nice work! You just completed a full exploratory data analysis of 100,000 real e-commerce orders from the Brazilian Olist marketplace. You went from messy, multi-file raw data to polished visualizations and a written business recommendation.

You've learned how to:

  • Load, clean, and merge multiple DataFrames from 5 raw CSV files into a single analysis-ready dataset using pandas join operations.
  • Create publication-quality visualizations with matplotlib and seaborn that reveal revenue patterns and delivery performance.
  • Write a data-backed business recommendation in the SCQA format that translates numbers into actionable insight.
  • Secret Mission: Built a time-series chart revealing monthly revenue trends and seasonal patterns across two years of orders.

Ready to quiz yourself?