Root-cause case interview for a regional sales dip

The prompt: "You are an analyst at a packaged-foods company. Revenue in the western Uttar Pradesh region fell from ₹28.5 lakh in July to ₹25.9 lakh in August, about 9%. Other regions were flat. Find out why."

Tool one: the driver tree

Draw the tree before you ask for data. Revenue in a distribution business breaks down like this:

Revenue
├── Number of active outlets (retailers who ordered)
│   ├── Outlets covered by sales beat plans
│   └── Share of covered outlets that placed an order
└── Revenue per active outlet
    ├── Units per order x orders per outlet
    └── Realised price per unit
        ├── List price
        ├── Schemes and discounts
        └── Product mix (premium vs value packs)

Saying "I'll check coverage, ordering rate, order size and realised price, in that order" gives the interviewer a map. It also shows why "sales are down" by itself says nothing: each branch implies a different owner and a different fix.

Interviewer's first follow-up: "Why start with outlets?" Because it is the cheapest to check and the most common cause of a sudden regional drop — a distributor dispute, a salesperson leaving, or a beat plan change can remove hundreds of outlets overnight.

Tool two: the price-volume-mix bridge

Suppose the data show outlet counts barely moved. The next question is whether fewer units were sold, or the same units at a lower price, or a shift towards cheaper packs. A bridge answers it with arithmetic.

import pandas as pd

# Synthetic monthly data for one region, by product
df = pd.DataFrame({
    "product":   ["Premium 500g", "Value 200g", "Family 1kg"],
    "units_jul": [20000, 30000, 8000],
    "price_jul": [60.0, 25.0, 112.5],
    "units_aug": [15000, 34000, 8000],
    "price_aug": [60.0, 25.0, 105.0],
})

rev_jul = (df.units_jul * df.price_jul).sum()
rev_aug = (df.units_aug * df.price_aug).sum()

total_jul_units = df.units_jul.sum()
total_aug_units = df.units_aug.sum()
avg_price_jul = rev_jul / total_jul_units

# Volume: change in total units, valued at July's average price
volume = (total_aug_units - total_jul_units) * avg_price_jul
# Mix: change in product shares, valued at July prices
share_jul = df.units_jul / total_jul_units
share_aug = df.units_aug / total_aug_units
mix = total_aug_units * ((share_aug - share_jul) * df.price_jul).sum()
# Price: change in price on August units
price = (df.units_aug * (df.price_aug - df.price_jul)).sum()

print(f"July revenue   Rs {rev_jul:,.0f}")
print(f"Volume effect  Rs {volume:,.0f}")
print(f"Mix effect     Rs {mix:,.0f}")
print(f"Price effect   Rs {price:,.0f}")
print(f"August revenue Rs {rev_aug:,.0f}  (check: {rev_jul + volume + mix + price:,.0f})")

In this synthetic data, total units fell slightly, but most of the loss is mix — buyers switched from the premium pack to the value pack — plus a price effect from a lower realised price on the family pack. Volume alone would have told you almost nothing.

Follow-up: "The three effects depend on the order you calculate them. Does that matter?" Yes: different bridge conventions assign the interaction terms differently. Say which convention you used and keep it consistent month to month. The total always reconciles; it is the split that is a modelling choice.

Tool three: the hypothesis log

Write each hypothesis with the evidence that would confirm or reject it, then check them in order of likelihood times ease.

Follow-up: "How do you prioritise?" Start with hypotheses you can test from existing data in under an hour (outlets, stock, scheme records, last year's pattern), then move to ones that need field input.

Questions interviewers add in this case

"Premium stock-outs explain the mix shift. How do you prove it?" Compare outlets served by warehouses that ran out with those that did not. If premium share fell only where stock ran out, that is strong evidence; if it fell everywhere, look elsewhere.

"The scheme reduced the realised price. Was it a mistake?" Not necessarily. Compare incremental units against the discount cost. If family-pack units stayed flat while the price fell, the scheme gave money away; here units did not rise, so the scheme looks unprofitable in August — but check whether it was designed to protect share against a competitor.

"What would you report to the regional head?" "August revenue fell 9%. The largest part is a shift from premium to value packs, which coincides with premium stock-outs at two warehouses; the second part is the family-pack scheme, which cut the realised price without adding volume. Outlet coverage is unchanged. Next steps: supply to confirm stock-out dates, and a decision on whether to continue the scheme."

"What if nothing in the data explains it?" Say so, list what has been ruled out, and propose the field checks. An honest "not yet explained, here is what's excluded" is a better answer than forcing a cause.

What to rehearse

Draw the driver tree for three different businesses — a food-delivery platform, a bank's credit-card spend, a SaaS subscription — without looking anything up. If you can produce each tree in two minutes, most root-cause cases become a matter of walking the branches.

The same decomposition logic applies to user metrics, as the DAU drop case shows, and to top-down estimates, as in the guesstimate round plan. The Data Analyst programme uses sales and distribution datasets for exactly this kind of bridge-and-hypothesis work.