Project: Sales Case Study, Question to Dashboard

Hi, we are starting to plan the festive season for 2026. Can you tell me
how the festive season went last year? I need to decide where to put stock
and marketing money, and whether the 10% festive discount is worth it.

Metric:      Revenue (after discount, before GST), orders and average order value
Definition:  Festive season = October and November 2025; "rest" = the other 10 months.
             Exact duplicate rows and rows with blank or invalid quantity removed.
             Bulk orders (more than 10 units) reported separately.
Time period: January to December 2025
Segments:    Category, city, discount level
Decisions:   1. Which categories and cities get festive stock and marketing budget?
             2. Should we keep, cut or test the festive discount?

How did the festive season perform, and what should we do in 2026?
  How much bigger is a festive month than a normal month?     -> SQL, Step 3
  Which categories grow most in the festive season?            -> SQL, Step 4
  Which cities grow most?                                      -> pandas, Step 5
  Did bigger discounts bring bigger baskets?                   -> pandas, Step 6

import sqlite3
import pandas as pd

raw = pd.read_csv("sales.csv")
log = [("raw rows", len(raw))]
df = raw.drop_duplicates()
log.append(("after removing exact duplicates", len(df)))
df = df[df["quantity"] > 0].copy()
log.append(("after removing blank or invalid quantity", len(df)))

df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
df["month"] = df["order_date"].str[:7]
df["season"] = df["month"].str[5:].isin(["10", "11"]).map({True: "Festive", False: "Rest"})
df["bulk_order"] = (df["quantity"] > 10).astype(int)

assert df["order_id"].is_unique, "order IDs repeat"
assert df["city"].nunique() == 10, "unexpected city spelling"
for step, rows in log:
    print(f"{step:42} {rows:5}")

con = sqlite3.connect(":memory:")
df.to_sql("sales", con, index=False)
print(pd.read_sql_query("SELECT season, COUNT(*) AS orders, SUM(bulk_order) AS bulk FROM sales GROUP BY season", con))

import sqlite3
import pandas as pd

df = pd.read_csv("sales.csv").drop_duplicates()
df = df[df["quantity"] > 0].copy()
df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
df["month"] = df["order_date"].str[:7]
df["season"] = df["month"].str[5:].isin(["10", "11"]).map({True: "Festive", False: "Rest"})
df["bulk_order"] = (df["quantity"] > 10).astype(int)
con = sqlite3.connect(":memory:")
df.to_sql("sales", con, index=False)

query = """
SELECT season,
       COUNT(DISTINCT month) AS months,
       ROUND(SUM(revenue) / COUNT(DISTINCT month)) AS revenue_per_month,
       ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT month)) AS orders_per_month,
       ROUND(SUM(revenue) / COUNT(*)) AS aov,
       ROUND(AVG(discount), 1) AS avg_discount_pct
FROM sales
WHERE bulk_order = 0
GROUP BY season
ORDER BY season;
"""
print(pd.read_sql_query(query, con).to_string(index=False))

import sqlite3
import pandas as pd

df = pd.read_csv("sales.csv").drop_duplicates()
df = df[df["quantity"] > 0].copy()
df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
df["season"] = df["order_date"].str[5:7].isin(["10", "11"]).map({True: "Festive", False: "Rest"})
df["bulk_order"] = (df["quantity"] > 10).astype(int)
con = sqlite3.connect(":memory:")
df.to_sql("sales", con, index=False)

query = """
WITH per_category AS (
    SELECT category,
           SUM(CASE WHEN season = 'Festive' THEN revenue ELSE 0 END) / 2.0 AS festive_pm,
           SUM(CASE WHEN season = 'Rest' THEN revenue ELSE 0 END) / 10.0 AS rest_pm
    FROM sales
    WHERE bulk_order = 0
    GROUP BY category
)
SELECT category,
       ROUND(festive_pm) AS festive_per_month,
       ROUND(rest_pm) AS normal_per_month,
       ROUND((festive_pm / rest_pm - 1) * 100) AS uplift_pct,
       ROUND(festive_pm * 100.0 / SUM(festive_pm) OVER (), 1) AS festive_share_pct,
       RANK() OVER (ORDER BY festive_pm DESC) AS festive_rank
FROM per_category
ORDER BY festive_rank;
"""
print(pd.read_sql_query(query, con).to_string(index=False))

import pandas as pd

df = pd.read_csv("sales.csv").drop_duplicates()
df = df[(df["quantity"] > 0) & (df["quantity"] <= 10)].copy()
df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
df["season"] = df["order_date"].str[5:7].isin(["10", "11"]).map({True: "Festive", False: "Rest"})

t = df.pivot_table(index="city", columns="season", values="revenue", aggfunc="sum")
t["festive_pm"] = t["Festive"] / 2
t["normal_pm"] = t["Rest"] / 10
t["uplift_pct"] = (t["festive_pm"] / t["normal_pm"] - 1) * 100
t["festive_share_pct"] = t["festive_pm"] / t["festive_pm"].sum() * 100
print(t[["festive_pm", "normal_pm", "uplift_pct", "festive_share_pct"]]
      .sort_values("festive_pm", ascending=False).round(0).astype(int))

import pandas as pd

df = pd.read_csv("sales.csv").drop_duplicates()
df = df[(df["quantity"] > 0) & (df["quantity"] <= 10)].copy()
df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
df["season"] = df["order_date"].str[5:7].isin(["10", "11"]).map({True: "Festive", False: "Rest"})

share = pd.crosstab(df["season"], df["discount"], normalize="index") * 100
print("Share of orders at each discount % (rows add to 100):")
print(share.round(0).astype(int))

festive = df[df["season"] == "Festive"]
by_level = festive.groupby("discount").agg(orders=("order_id", "count"),
                                           aov=("revenue", "mean"),
                                           avg_units=("quantity", "mean"))
print("\nFestive orders by discount level:")
print(by_level.round(1))

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("sales.csv").drop_duplicates()
df = df[(df["quantity"] > 0) & (df["quantity"] <= 10)].copy()
df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
df["month"] = df["order_date"].str[5:7]
df["season"] = df["month"].isin(["10", "11"]).map({True: "Festive", False: "Rest"})

monthly = df.groupby("month")["revenue"].sum() / 100000
colours = ["#0d9488" if m in ("10", "11") else "#cbd5e1" for m in monthly.index]
per_month = df.groupby(["category", "season"])["revenue"].sum().unstack()
uplift = ((per_month["Festive"] / 2) / (per_month["Rest"] / 10) - 1) * 100

fig, (left, right) = plt.subplots(1, 2, figsize=(12, 4))
left.bar(monthly.index, monthly.values, color=colours)
left.set_title("Revenue by month, 2025 (Rs lakh); festive months in teal")
right.barh(uplift.sort_values().index, uplift.sort_values().values, color="#0d9488")
right.axvline(0, color="#64748b", linewidth=1)
right.set_title("Festive uplift by category (%)")
plt.tight_layout()
plt.show()

print("Peak month:", monthly.idxmax(), " Highest uplift:", uplift.idxmax(), f"({uplift.max():.0f}%)")

To: Priya Nair, Head of Sales            From: Data Analytics
Subject: Festive season 2025 review and 2026 plan

ANSWER
A festive month (Oct-Nov) brought in nearly twice the revenue of a normal
month. Electronics, Fashion and Home & Kitchen drove the gain. Discounts
doubled but did not create bigger baskets, so we recommend testing a smaller
festive discount rather than repeating the same offer everywhere.

KEY FINDINGS (bulk orders excluded)
1. Festive month: about Rs 2.5 lakh revenue vs Rs 1.3 lakh in a normal month;
   orders per month up about 60%; AOV Rs 1,715 vs Rs 1,480.
2. Electronics and Fashion each roughly doubled and made up about 60% of
   festive revenue. Books shrank in the festive season.
3. Mumbai is the largest festive market and grew most (about 2.3 times a
   normal month). Kochi, Hyderabad and Lucknow grew strongly from smaller bases.
4. About two thirds of festive orders were discounted (just over a third
   in normal months). Units per order stayed at about 1.5 at every discount level.

RECOMMENDATIONS
1. Stock: plan festive stock at about twice normal levels for Electronics and
   Fashion, and well above normal for Home & Kitchen. No festive push for Books.
2. Marketing: weight budget towards Mumbai, Delhi and Hyderabad (largest
   festive revenue), and trial extra spend in Kochi and Lucknow (fast growth).
3. Discount: run an A/B test in October 2026, 10% versus 5%, on Electronics
   and Fashion, measuring conversion, revenue per visitor and margin.
4. Reporting: show bulk orders (2 in 2025, including Rs 51,300 of exam
   guides in Pune) separately from retail sales.

HOW WE KNOW, AND LIMITS
Source: sales.csv, 2025; 1,202 orders after removing 8 duplicates and 7
invalid rows. One year only, so we cannot separate festive effect from
yearly growth. No customer IDs, website traffic or product costs, so we
cannot measure new customers, conversion or margin.

+--------------------------------------------------------------------+
| Festive Season Tracker 2026     [Date range v] [City v] [Category v] |
+----------------+----------------+----------------+-----------------+
| Revenue        | Orders         | AOV            | Discount rate   |
| vs same day    | vs same day    | vs festive     | vs plan         |
| last year      | last year      | 2025 average   |                 |
+----------------+----------------+----------------+-----------------+
| Daily revenue: 2026 line vs 2025 line  | Revenue by category vs     |
| (same festive day)                     | stock plan (bar)           |
+----------------------------------------+----------------------------+
| City table: revenue, uplift vs normal month, share, flag if behind |
+--------------------------------------------------------------------+
| A/B test panel: 5% vs 10% discount, conversion and revenue/visitor |
+--------------------------------------------------------------------+
| Source: orders DB, refreshed hourly. Bulk orders (>10 units) shown |
| separately. Definitions: revenue after discount, before GST.       |
+--------------------------------------------------------------------+