What You Will Learn
- How to run a complete analytics project end to end
- The 7-step pipeline: question → data → cleaning → analysis → visualization → insight → recommendation
- Full SQL and Python code you can run yourself
- How to present findings to a stakeholder
Why This Topic Matters
This lesson is the capstone. Everything you have learned — data types, SQL, pandas, statistics, visualization, business framing — comes together here. If you can complete this project, you can do real analyst work. The dataset is synthetic but realistic; the workflow is exactly what you would use on the job.
The Business Question
You are the analyst for a small electronics retailer with 3 stores (Mumbai, Chennai, Kolkata) selling 4 product categories (Laptop, Phone, Accessory, Warranty). Your manager asks:
"How did each store perform in Q3 2026, and where should we focus our attention in Q4?"
The 7-Step Pipeline
- Question — restate and clarify (see lesson 36)
- Data — load and inspect
- Cleaning — handle missing values, duplicates, types (see lesson 27)
- Analysis — SQL or pandas to compute metrics
- Visualization — charts that reveal patterns (see lesson 33)
- Insight — what the data tells us
- Recommendation — what to do about it (see lesson 38)
Step 1 — Restate the Question
You ask back: "What does 'performance' mean here?" The manager says: "Revenue, obviously, but also which products are selling and whether anything stands out — good or bad."
You translate to a measurable data question:
"For Q3 2026 (Jul–Sep), what is the total revenue, order count, and average order value by store and by product category, compared to Q2 2026? Which store-product combinations are outliers — either much better or much worse than expected?"
Step 2 — The Data
Synthetic dataset. ~240 orders across 3 stores and 4 product categories, July through September 2026.
| order_id | date | store | product_category | product_name | quantity | unit_price | amount |
|---|---|---|---|---|---|---|---|
| 1001 | 2026-07-03 | Mumbai | Laptop | ThinkPad X1 | 1 | 95000 | 95000 |
| 1002 | 2026-07-04 | Chennai | Phone | Pixel 8 | 2 | 65000 | 130000 |
| 1003 | 2026-07-05 | Kolkata | Accessory | Wireless Mouse | 5 | 1200 | 6000 |
| ... | ... | ... | ... | ... | ... | ... | ... |
(Full synthetic dataset of 240 rows is omitted for brevity. Generate your own with random but realistic values: 3 stores × 4 categories × ~20 orders each = 240.)
Step 3 — Loading and Cleaning
import pandas as pd
# Load
df = pd.read_csv("sales_q3_2026.csv")
# Inspect
print(df.shape) # (240, 8)
print(df.dtypes)
print(df.isna().sum()) # check missing
print(df.duplicated().sum()) # check duplicates
# Convert date
df["date"] = pd.to_datetime(df["date"])
# Verify amount = quantity * unit_price
df["computed_amount"] = df["quantity"] * df["unit_price"]
mismatches = df[df["amount"] != df["computed_amount"]]
print(f"Amount mismatches: {len(mismatches)}") # should be 0
# Add month column for grouping
df["month"] = df["date"].dt.to_period("M")
# Save cleaned
df.to_csv("sales_q3_clean.csv", index=False)
Typical cleaning findings (and fixes)
- ~5% of rows have missing
product_name→ fill with "Unknown" (or drop if critical). - 1 duplicate
order_id→ drop withdf.drop_duplicates(subset=["order_id"]). datestored as text → convert withpd.to_datetime.- 2 rows have
amount = 0→ investigate. Likely test orders — drop them.
Step 4 — Analysis (pandas version)
# Total revenue and order count for Q3
total_revenue = df["amount"].sum()
total_orders = df["order_id"].nunique()
avg_order_value = total_revenue / total_orders
print(f"Q3: ₹{total_revenue:,} revenue, {total_orders} orders, AOV ₹{avg_order_value:,.0f}")
# By store
by_store = df.groupby("store").agg(
revenue=("amount", "sum"),
orders=("order_id", "nunique"),
aov=("amount", "mean")
).round(0)
print(by_store)
# By category
by_category = df.groupby("product_category")["amount"].agg(["sum", "count", "mean"])
print(by_category)
# Store × Category (the key table)
store_cat = df.groupby(["store", "product_category"])["amount"].sum().unstack()
print(store_cat)
# Month-over-month trend
monthly = df.groupby("month")["amount"].sum()
print(monthly.pct_change() * 100) # MoM growth %
Example output (illustrative numbers)
By store:
revenue orders aov
store
Chennai 1850000 82 22561
Kolkata 1450000 78 18590
Mumbai 2150000 85 25294
By category:
sum count mean
product_category
Accessory 320000 60 5333
Laptop 3200000 60 53333
Phone 1650000 55 30000
Warranty 280000 55 5091
Step 4 (alt) — The SQL Version
-- Total revenue, orders, AOV for Q3
SELECT
SUM(amount) AS total_revenue,
COUNT(DISTINCT order_id) AS total_orders,
SUM(amount) / COUNT(DISTINCT order_id) AS aov
FROM sales
WHERE date >= '2026-07-01' AND date < '2026-10-01';
-- By store
SELECT store,
SUM(amount) AS revenue,
COUNT(DISTINCT order_id) AS orders,
AVG(amount) AS aov
FROM sales
WHERE date >= '2026-07-01' AND date < '2026-10-01'
GROUP BY store
ORDER BY revenue DESC;
-- Store × Category (with a CTE for readability)
WITH q3 AS (
SELECT * FROM sales
WHERE date >= '2026-07-01' AND date < '2026-10-01'
)
SELECT store, product_category, SUM(amount) AS revenue
FROM q3
GROUP BY store, product_category
ORDER BY store, revenue DESC;
Step 5 — Visualization
import matplotlib.pyplot as plt
fig, axes = plt.subplots(2, 2, figsize=(12, 9))
# Top-left: Revenue by store (bar)
by_store["revenue"].plot(kind="bar", ax=axes[0,0], color="steelblue")
axes[0,0].set_title("Q3 Revenue by Store")
axes[0,0].set_ylabel("Revenue (₹)")
# Top-right: Revenue by category (bar)
by_category["sum"].plot(kind="bar", ax=axes[0,1], color="darkorange")
axes[0,1].set_title("Q3 Revenue by Category")
axes[0,1].set_ylabel("Revenue (₹)")
# Bottom-left: Monthly trend (line)
monthly.plot(kind="line", ax=axes[1,0], marker="o", color="darkgreen")
axes[1,0].set_title("Q3 Monthly Revenue")
axes[1,0].set_ylabel("Revenue (₹)")
axes[1,0].grid(True, alpha=0.3)
# Bottom-right: Store × Category (horizontal stacked bar)
store_cat.plot(kind="barh", stacked=True, ax=axes[1,1])
axes[1,1].set_title("Revenue: Store × Category")
axes[1,1].set_xlabel("Revenue (₹)")
plt.tight_layout()
plt.savefig("sales_q3_dashboard.png", dpi=120, bbox_inches="tight")
plt.show()
Step 6 — Insights
From the analysis, three findings stand out:
- Mumbai leads in revenue (₹21.5L), driven by Laptop sales (₹14L of the total). Mumbai's AOV is highest at ₹25,294.
- Kolkata is the weakest store (₹14.5L), with a low AOV (₹18,590). It sells disproportionately more Accessories and Warranties, fewer Laptops.
- Warranty revenue is tiny (₹2.8L across all stores) — only ~10% of Phone revenue, despite every Phone being warranty-eligible. This is an attach-rate problem.
Step 7 — Recommendations
Using the framework from lesson 38:
Recommendation 1 — Fix Kolkata's product mix
| Element | Detail |
|---|---|
| Finding | Kolkata's revenue is 32% below Mumbai's, driven by low Laptop sales (₹4L vs Mumbai's ₹14L). |
| Cause (hypothesis) | Kolkata's store layout puts Laptops in the back; floor staff are trained mainly on Accessories. |
| Recommendation | Reposition Laptop display to the front; train 2 staff on Laptop demos; run a "Laptop Festival" promo in October. |
| Expected outcome | Kolkata Laptop revenue up 50% in Q4 (₹4L → ₹6L); total store revenue up ~14%. |
| Cost | ₹50,000 (display rework + staff training + promo materials). |
| Success measure | Kolkata Q4 Laptop revenue ≥ ₹6L. |
Recommendation 2 — Increase warranty attach rate
| Element | Detail |
|---|---|
| Finding | Warranty revenue is ₹2.8L vs Phone revenue of ₹16.5L. Attach rate is ~17% (industry benchmark is 30–40%). |
| Cause (hypothesis) | Sales staff do not actively offer warranty at checkout. |
| Recommendation | Add a mandatory warranty prompt in the POS system; train staff to offer warranty with every Phone sale; offer 10% discount on warranty if purchased with the Phone. |
| Expected outcome | Attach rate up from 17% to 30% in Q4. Warranty revenue up ~75% (₹2.8L → ₹4.9L). |
| Cost | ₹20,000 (POS update + training). Discount cost ~₹1.5L/quarter, more than covered by added revenue. |
| Success measure | Q4 warranty attach rate ≥ 30%. |
Recommendation 3 — Investigate Mumbai's Phone sales dip in August
Monthly trend shows Mumbai's Phone revenue dipped 22% in August vs July, then recovered. Investigate: was there a stockout? A competitor launch? A pricing error? If unknown, run the investigation workflow from lesson 38 before recommending a fix.
Presenting to the Stakeholder
Structure your 10-minute presentation as follows:
- 1 min — the question. Restate the ask and your translation. Confirm scope.
- 2 min — the data. What you used, what you cleaned, any caveats.
- 4 min — the findings. Three insights, one per slide. Lead with the chart; explain in 30 seconds; move on.
- 3 min — the recommendations. Two specific actions, each with expected outcome and cost. End with the investigation item.
Do not show your code. Do not show every chart. Show the few that drive the recommendation.
Common Mistakes
- Skipping the brief and diving into data. You end up answering the wrong question.
- Not cleaning the data. A duplicate row or a missing-value bug silently changes totals.
- Showing every chart you made. Stakeholders want findings, not your work-in-progress.
- Recommendations without cost or success measure. These are slogans, not recommendations.
- Forgetting to compare Q3 to Q2. Without the comparison, "Mumbai leads" is meaningless — leads compared to what?
Practical Exercise (45–60 minutes)
- Generate a synthetic 240-row sales dataset using Python. (Use
randomwith realistic product prices and store allocation.) - Run the cleaning pipeline above. Document any issues you find and how you fixed them.
- Compute the by-store, by-category, and store × category tables.
- Build the 4-chart dashboard.
- Write a 1-page brief: question, data, findings (3 insights), recommendations (2 actions with cost + expected outcome + success measure).
- Show your brief to a friend who is not an analyst. Can they understand it? If not, simplify.
Mini Challenge
Repeat the entire project with one variation: add a Q2 2026 dataset (another 240 rows) and compare Q3 vs Q2 by store and category. Which store grew fastest? Which category declined? Does that change your recommendations?
Key Takeaways
- The 7-step pipeline: question → data → cleaning → analysis → visualization → insight → recommendation.
- Always restate the question and confirm scope before touching data.
- Clean before analyzing. Even one duplicate row changes totals.
- Every recommendation needs: action, expected outcome, cost, success measure.
- Present 3 findings and 2 recommendations in 10 minutes. Cut everything else.
Previously learned: Phases 1–8 covered every tool and concept used in this project.
Today: You applied them end to end on a realistic sales dataset.
Next: Lesson 40 — the final project — applies the same pipeline to a customer behavior dataset, with a focus on segmentation and retention.
FAQ
Can I use this project in a job interview?
Yes — adapt it to a real (or realistic) dataset you have access to. Employers love end-to-end projects because they show you can do the whole pipeline, not just write SQL. Add a 1-page summary and a GitHub repo with the code.
How long should a real project like this take?
For a 240-row dataset, 3–4 hours total: 30 min on the brief, 1 hour on cleaning, 1 hour on analysis, 30 min on visualization, 30 min on the recommendation write-up, 30 min on presentation prep. For a 100k-row dataset, expect 1–2 days.
Comments
Comments
Post a Comment