Keyboard Shortcuts N Next post
P Previous post
S Save / unsave
R Read aloud
T Toggle theme
/ Focus search
Esc Close panels
🔥
Ready to read...
advanced dashboards Data Analytics Course Phase 9 — Real Projects projects Python SQL

End-to-End Project — Sales Analysis Dashboard

Reviewed & accurate
AI Summary

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

  1. Question — restate and clarify (see lesson 36)
  2. Data — load and inspect
  3. Cleaning — handle missing values, duplicates, types (see lesson 27)
  4. Analysis — SQL or pandas to compute metrics
  5. Visualization — charts that reveal patterns (see lesson 33)
  6. Insight — what the data tells us
  7. 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_iddatestoreproduct_categoryproduct_namequantityunit_priceamount
10012026-07-03MumbaiLaptopThinkPad X119500095000
10022026-07-04ChennaiPhonePixel 8265000130000
10032026-07-05KolkataAccessoryWireless Mouse512006000
........................

(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 with df.drop_duplicates(subset=["order_id"]).
  • date stored as text → convert with pd.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:

  1. Mumbai leads in revenue (₹21.5L), driven by Laptop sales (₹14L of the total). Mumbai's AOV is highest at ₹25,294.
  2. Kolkata is the weakest store (₹14.5L), with a low AOV (₹18,590). It sells disproportionately more Accessories and Warranties, fewer Laptops.
  3. 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

ElementDetail
FindingKolkata'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.
RecommendationReposition Laptop display to the front; train 2 staff on Laptop demos; run a "Laptop Festival" promo in October.
Expected outcomeKolkata Laptop revenue up 50% in Q4 (₹4L → ₹6L); total store revenue up ~14%.
Cost₹50,000 (display rework + staff training + promo materials).
Success measureKolkata Q4 Laptop revenue ≥ ₹6L.

Recommendation 2 — Increase warranty attach rate

ElementDetail
FindingWarranty 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.
RecommendationAdd 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 outcomeAttach 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 measureQ4 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. 1 min — the question. Restate the ask and your translation. Confirm scope.
  2. 2 min — the data. What you used, what you cleaned, any caveats.
  3. 4 min — the findings. Three insights, one per slide. Lead with the chart; explain in 30 seconds; move on.
  4. 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

  1. Skipping the brief and diving into data. You end up answering the wrong question.
  2. Not cleaning the data. A duplicate row or a missing-value bug silently changes totals.
  3. Showing every chart you made. Stakeholders want findings, not your work-in-progress.
  4. Recommendations without cost or success measure. These are slogans, not recommendations.
  5. Forgetting to compare Q3 to Q2. Without the comparison, "Mumbai leads" is meaningless — leads compared to what?

Practical Exercise (45–60 minutes)

  1. Generate a synthetic 240-row sales dataset using Python. (Use random with realistic product prices and store allocation.)
  2. Run the cleaning pipeline above. Document any issues you find and how you fixed them.
  3. Compute the by-store, by-category, and store × category tables.
  4. Build the 4-chart dashboard.
  5. Write a 1-page brief: question, data, findings (3 insights), recommendations (2 actions with cost + expected outcome + success measure).
  6. 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.
Course continuity
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.

Test Your Knowledge
How did you find this?

Comments

Join the discussion! Sign in with your Google or Blogger account, or comment as Anonymous - no account needed. For quick questions, also reach me on Telegram @cytestch.

Comments