Why Faridabad Students Are Choosing Data Analytics in 2026

-- A real analyst query: "Which customers are we about to lose?"
-- Not a dashboard query. A diagnostic query, which is most of the job.

WITH
-- 1. Customer activity over the last 90 days
customer_activity AS (
  SELECT
    c.customer_id,
    c.signup_date,
    c.plan_type,
    c.monthly_value,
    MAX(o.order_date)                       AS last_order_date,
    COUNT(o.order_id)                       AS orders_90d,
    SUM(o.amount)                           AS revenue_90d
  FROM customers c
  LEFT JOIN orders o
    ON o.customer_id = c.customer_id
   AND o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  WHERE c.status = 'active'
  GROUP BY 1, 2, 3, 4
),

-- 2. Compute how many days since the last order
scored AS (
  SELECT
    *,
    DATE_DIFF(CURRENT_DATE(), last_order_date, DAY) AS days_since_order,
    CASE
      WHEN orders_90d = 0                                   THEN 'dormant'
      WHEN DATE_DIFF(CURRENT_DATE(), last_order_date, DAY) > 45 THEN 'at_risk'
      WHEN DATE_DIFF(CURRENT_DATE(), last_order_date, DAY) > 21 THEN 'cooling'
      ELSE 'active'
    END AS engagement_status
  FROM customer_activity
)

-- 3. Show at-risk customers by plan type, sorted by revenue
SELECT
  plan_type,
  engagement_status,
  COUNT(*)                                  AS customers,
  ROUND(SUM(monthly_value), 0)              AS monthly_revenue_at_risk,
  ROUND(AVG(days_since_order), 1)           AS avg_days_since_order
FROM scored
WHERE engagement_status IN ('at_risk', 'dormant')
GROUP BY 1, 2
ORDER BY monthly_revenue_at_risk DESC;

-- What a good analyst does with this:
-- - The output shows which plans have the most revenue at risk.
-- - The analyst then asks: "Is this driven by a recent price change?
--   A feature removal? Seasonality?"
-- - They cross-check with product usage data and support tickets.
-- - The query is the easy part. The follow-up questions are the job.
--
-- This is what Faridabad companies mean when they say
-- "we need analysts, not just SQL writers".

# sales_analysis.py — a real Pandas analysis, not a notebook demo
# This is what a business analyst at a Faridabad MSME would build weekly.

import pandas as pd

# 1. Load raw exports (usually CSV from the ERP or POS system)
orders = pd.read_csv("orders.csv", parse_dates=["order_date"])
customers = pd.read_csv("customers.csv", parse_dates=["signup_date"])

# 2. Clean — fix data quality issues that every real dataset has
orders["amount"] = pd.to_numeric(orders["amount"], errors="coerce")
orders = orders.dropna(subset=["amount", "order_date", "customer_id"])
orders = orders[orders["amount"] >= 0]
orders = orders.drop_duplicates(subset=["order_id"])

# 3. Filter — only the last quarter
last_quarter = orders[
    orders["order_date"] >= (pd.Timestamp.today() - pd.Timedelta(days=90))
]

# 4. Join with customer segment
df = last_quarter.merge(
    customers[["customer_id", "city", "segment", "signup_date"]],
    on="customer_id",
    how="left",
)

# 5. Aggregate — revenue by city and segment
summary = (
    df.groupby(["city", "segment"])
      .agg(
          orders=("order_id", "nunique"),
          revenue=("amount", "sum"),
          avg_order_value=("amount", "mean"),
      )
      .round(2)
      .reset_index()
)

# 6. Derive a business metric — % of revenue from repeat customers
repeat_customers = (
    df.groupby("customer_id")
      .filter(lambda x: len(x) >= 2)
)
repeat_revenue_share = round(
    repeat_customers["amount"].sum() / df["amount"].sum() * 100, 1
)

# 7. Answer a business question
top_city = summary.sort_values("revenue", ascending=False).iloc[0]

print(f"Top city: {top_city['city']} ({top_city['segment']})")
print(f"Revenue last quarter: {top_city['revenue']:,.0f}")
print(f"Repeat customer share: {repeat_revenue_share}%")

# What this shows a hiring manager:
# - You can load, clean, and validate real data.
# - You can join tables without breaking them.
# - You can build business metrics (repeat revenue share).
# - You can answer a specific question, not just make a chart.
#
# Why this matters for Faridabad students:
# - Many local MSMEs and manufacturing firms still run on Excel.
# - A student who can do this replaces 20 Excel files with one script.
# - That skill is worth more than a degree in most local companies.

# Dashboard Design — the difference between a chart and a decision
# Most "dashboards" in portfolios are charts without a question.
# Real dashboards answer a specific question for a specific person.

## Dashboard Brief

**Audience:** Head of Sales at a Faridabad-based industrial supplies company
**Question they need answered every Monday morning:**
  "Which customers, regions, and products should I focus on this week?"

**Decisions this dashboard needs to drive:**
1. Which 10 customers to call first (high value, low engagement)
2. Which 3 regions to push more inventory into
3. Which 5 SKUs to discount or discontinue

## What to include — and what to exclude

### Include (each with the specific decision it drives)
- **Top 10 at-risk customers by revenue**
  - Decision: make these calls this week
- **Revenue by region vs last month (with delta)**
  - Decision: allocate stock and sales attention
- **SKU performance — top and bottom performers**
  - Decision: discount or discontinue the bottom 5
- **Repeat purchase rate trend**
  - Decision: is retention improving or falling?

### Exclude
- Any chart the sales head cannot act on
- Pie charts (hard to compare)
- Number counters with no context
- Anything that is not in the brief

## Design principles for this dashboard

1. **One question per section.** Do not crowd.
2. **Every chart has a caption with the answer in one line.**
   - Example: "Revenue is up 8% vs last month, driven by North."
3. **Every metric has a comparison.** Show last month, target, or trend.
4. **Green / amber / red only when it means something.** Not decoration.
5. **Drill-down where it matters.** Click a customer to see their orders.
6. **Updated automatically.** If it requires manual refresh, it will not be used.

## Metrics that would actually appear

| Metric | Source | Update | Target |
|--------|--------|--------|--------|
| Weekly revenue | orders | daily | ₹12L/week |
| At-risk customers | RFM scoring | weekly | < 10% of base |
| Repeat purchase rate | orders | monthly | > 35% |
| Top 10 customers MRR | subscriptions | daily | — |
| Region revenue delta | orders | weekly | +5% MoM |

# Why this matters:
# - A dashboard without a decision is a decoration.
# - The question, the audience, and the decisions come first.
# - The visuals are the last thing you design.
# - Faridabad hiring managers in business analyst roles want to see
#   this thinking — not the number of charts you can produce.