Project: sales analysis for a sweets chain

Let us put the workflow together on a realistic case. Madhuram Sweets (a fictional chain) has 12 stores across Jaipur, Indore and Nagpur. The owner asks: "Which products and stores should we focus on for the Diwali season, and how much stock should we plan?"

Step 1: frame the question

We narrow it to three answerable questions:

1. Which categories drove revenue and gross margin in last year's festive period (Oct–Nov)?
2. How does each store's festive uplift compare with its normal weeks?
3. What daily demand should we plan for the top 10 products?

Step 2: load and check the data

The point-of-sale export has one row per bill line: bill_id, date, store, city, product, category, qty_kg, price_per_kg, cost_per_kg.

import pandas as pd

sales = pd.read_csv("pos_lines.csv", parse_dates=["date"], dayfirst=True)
print(sales.shape, sales["date"].min(), sales["date"].max())
print(sales.isna().sum())
print(sales.duplicated(["bill_id", "product"]).sum())

sales = sales.drop_duplicates(["bill_id", "product"])
sales = sales[(sales["qty_kg"] > 0) & (sales["price_per_kg"] > 0)]

Check for zero or negative quantities (returns entered as sales), mixed city spellings and missing cost prices.

Step 3: engineer useful features

Raw columns rarely answer the question directly. Create features that match how the business thinks:

sales = sales.assign(
    revenue=lambda d: d["qty_kg"] * d["price_per_kg"],
    margin=lambda d: d["qty_kg"] * (d["price_per_kg"] - d["cost_per_kg"]),
    weekday=lambda d: d["date"].dt.day_name(),
    festive=lambda d: d["date"].between("2025-10-01", "2025-11-30"),
)

Other helpful features: bill size band, whether the bill contains a gift box, and days before Diwali.

Step 4: analyse

by_cat = (sales.groupby(["festive", "category"])
               .agg(revenue=("revenue", "sum"), margin=("margin", "sum"))
               .assign(margin_pct=lambda d: (d["margin"] / d["revenue"] * 100).round(1)))

daily = sales.groupby(["store", "festive", sales["date"].dt.date])["revenue"].sum()
uplift = (daily.groupby(level=["store", "festive"]).mean()
               .unstack("festive")
               .rename(columns={False: "normal", True: "festive"})
               .assign(uplift=lambda d: d["festive"] / d["normal"]))
print(uplift.sort_values("uplift", ascending=False))

Suppose the results look like this:

Category	Festive revenue share	Margin %
Dry-fruit sweets	34%	41%
Milk sweets	29%	28%
Gift boxes	22%	46%
Namkeen	15%	33%

Step 5: plan demand

For the top products, take average daily kilograms sold during last year's festive period, adjust for this year's growth and add a safety buffer. Milk sweets have a short shelf life, so a smaller buffer and a daily re-order is better than one large batch.

Step 6: recommend

A good final answer is short and specific:

• Push gift boxes and dry-fruit sweets: highest margin and strong festive share.
• Two Indore stores show a 3x festive uplift; add counter staff and a pre-order desk there.
• Plan milk-sweet production daily from the demand table, with a 10% buffer.

Key takeaways

• Frame the question first; it decides which features and analyses matter.
• Cleaning and feature engineering usually take more time than the analysis itself.
• Always connect numbers back to an action someone can take.