Mini Project — Sales Performance Analysis

This is a guided mini-project that puts everything from the NumPy and Pandas session together. You'll work through a realistic data analysis task from raw CSV to an insights report.

Time: 45–60 minutes Deliverable: A Jupyter notebook with analysis and visualisations

---

Business Context

You're a data analyst at a mid-sized e-commerce company. The head of sales has asked for a monthly performance review covering:

1. Overall revenue trend
2. Top products and categories
3. Customer segmentation by spend
4. Regional performance
5. Key recommendations

You have two CSV files: orders.csv and customers.csv.

---

Step 1 — Load and Inspect

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

pd.set_option("display.float_format", "{:,.2f}".format)
pd.set_option("display.max_columns", 20)

# Load
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
customers = pd.read_csv("customers.csv")

# Inspect
print("=== ORDERS ===")
print(orders.shape)
print(orders.info())
print(orders.describe())
print(orders.isnull().sum())

print("\n=== CUSTOMERS ===")
print(customers.shape)
print(customers.head(3))

---

Step 2 — Clean and Enrich

# Calculate order total
orders["total"] = orders["quantity"] * orders["unit_price"]

# Apply discount if present
orders["net_total"] = orders["total"] * (1 - orders["discount_pct"].fillna(0) / 100)

# Extract date parts
orders["year"] = orders["order_date"].dt.year
orders["month"] = orders["order_date"].dt.month
orders["month_name"] = orders["order_date"].dt.strftime("%b")
orders["quarter"] = orders["order_date"].dt.quarter
orders["week"] = orders["order_date"].dt.isocalendar().week

# Join customer data
df = orders.merge(
    customers[["customer_id", "first_name", "last_name", "segment", "country", "city"]],
    on="customer_id",
    how="left"
)
df["full_name"] = df["first_name"] + " " + df["last_name"]

# Filter to completed orders for revenue analysis
completed = df[df["status"] == "completed"].copy()
print(f"Completed orders: {len(completed)} of {len(df)} ({len(completed)/len(df):.1%})")

---

Step 3 — Revenue Trend

# Monthly revenue
monthly = (
    completed
    .groupby(["year", "month", "month_name"])["net_total"]
    .sum()
    .reset_index()
    .sort_values(["year", "month"])
    .assign(
        mom_change=lambda df: df["net_total"].pct_change() * 100,
        cum_revenue=lambda df: df["net_total"].cumsum(),
    )
)

print(monthly[["month_name", "net_total", "mom_change"]].to_string(index=False))

# Plot
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 5))

ax1.bar(monthly["month_name"], monthly["net_total"] / 1000, color="#0D9488", alpha=0.8)
ax1.set_title("Monthly Revenue (£ thousands)")
ax1.set_xlabel("Month")
ax1.set_ylabel("Revenue (£k)")
ax1.grid(axis="y", alpha=0.3)

colors = ["#0D9488" if x >= 0 else "#EF4444" for x in monthly["mom_change"].fillna(0)]
ax2.bar(monthly["month_name"], monthly["mom_change"].fillna(0), color=colors, alpha=0.8)
ax2.axhline(y=0, color="black", linewidth=0.8)
ax2.set_title("Month-over-Month Revenue Change (%)")
ax2.set_xlabel("Month")
ax2.set_ylabel("Change (%)")

plt.tight_layout()
plt.savefig("revenue_trend.png", dpi=150, bbox_inches="tight")
plt.show()

---

Step 4 — Product and Category Analysis

# Revenue by category
category_perf = (
    completed
    .groupby("category")
    .agg(
        orders=("order_id", "count"),
        units_sold=("quantity", "sum"),
        revenue=("net_total", "sum"),
        avg_order=("net_total", "mean"),
    )
    .reset_index()
    .sort_values("revenue", ascending=False)
)
category_perf["revenue_share"] = category_perf["revenue"] / category_perf["revenue"].sum() * 100
print(category_perf.to_string(index=False))

# Top 10 products
top_products = (
    completed
    .groupby("product")["net_total"]
    .sum()
    .sort_values(ascending=False)
    .head(10)
    .reset_index()
)

fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 5))

ax1.barh(category_perf["category"][::-1], category_perf["revenue"][::-1] / 1000, color="#0D9488")
ax1.set_title("Revenue by Category (£k)")
ax1.set_xlabel("Revenue (£k)")

ax2.barh(top_products["product"][::-1], top_products["net_total"][::-1] / 1000, color="#F59E0B")
ax2.set_title("Top 10 Products by Revenue (£k)")
ax2.set_xlabel("Revenue (£k)")

plt.tight_layout()
plt.show()

---

Step 5 — Customer Segmentation

# Customer lifetime value
clv = (
    completed
    .groupby("customer_id")
    .agg(
        total_orders=("order_id", "count"),
        total_spend=("net_total", "sum"),
        avg_order=("net_total", "mean"),
        first_order=("order_date", "min"),
        last_order=("order_date", "max"),
    )
    .reset_index()
)

# Days since last order
as_of = completed["order_date"].max()
clv["days_since_last"] = (as_of - clv["last_order"]).dt.days

# Segment by spend
clv["value_segment"] = pd.cut(
    clv["total_spend"],
    bins=[0, 200, 500, 1000, float("inf")],
    labels=["Low", "Mid", "High", "Premium"],
    right=False
)

segment_summary = (
    clv
    .groupby("value_segment", observed=True)
    .agg(
        customers=("customer_id", "count"),
        avg_spend=("total_spend", "mean"),
        avg_orders=("total_orders", "mean"),
    )
    .reset_index()
)
print(segment_summary.to_string(index=False))

---

Step 6 — Key Metrics Summary

print("\n" + "=" * 50)
print("SALES PERFORMANCE SUMMARY")
print("=" * 50)

total_rev = completed["net_total"].sum()
total_orders = len(completed)
unique_customers = completed["customer_id"].nunique()
avg_order = completed["net_total"].mean()
best_month = monthly.loc[monthly["net_total"].idxmax(), "month_name"]
best_category = category_perf.iloc[0]["category"]

print(f"Total Revenue:       £{total_rev:>12,.2f}")
print(f"Total Orders:        {total_orders:>12,}")
print(f"Unique Customers:    {unique_customers:>12,}")
print(f"Avg Order Value:     £{avg_order:>12,.2f}")
print(f"Best Month:          {best_month:>12}")
print(f"Top Category:        {best_category:>12}")
print("=" * 50)

---

Deliverables Checklist

• [ ] Monthly revenue trend chart (bar + MoM change)
• [ ] Category and product performance table
• [ ] Customer segmentation breakdown
• [ ] Summary metrics block
• [ ] Three actionable insights based on the data

Insight template:

"The data shows [observation]. This suggests [interpretation]. I recommend [action]."

---

← Data Analysis Workflows · Next: Interview Questions →