Business acumen · Intermediate
"Why are sales down?" Framing before querying
Why it matters
The most common request an analyst receives is vague: "sales are down, can you look into it?". Juniors open SQL immediately and return three days later with twenty charts. Seniors spend twenty minutes framing, check the likely causes in order, and answer the same day. Case interviews at product companies and consultancies test exactly this.
The concept
Work through four steps before writing a query:
Clarify the question. Which sales metric, compared with what, over which period, for which segment? "Down 12% week over week in Bengaluru on the app" is a question; "sales are down" is not.
Check the data first. Is it real? Tracking breaks, pipelines lag, definitions change. Rule these out before looking for business causes.
Decompose. Split the metric into its tree (customers times orders per customer times order value) and by segments (region, channel, platform, new vs returning). Find where the drop concentrates.
Hypothesise internal and external causes. Internal: releases, pricing, stock-outs, marketing changes, outages. External: seasonality, holidays, competitors, weather, regulation.
Then state an answer with confidence and the next step.
Worked example: a real drop in the taxi data
Daily trips fell from 97,804 on Saturday 4 January 2025 to 79,624 on Sunday 5 January, a drop of 18,180 trips (19%). Walk the framing:
Clarify. Metric: completed yellow taxi trips per day, all of New York City. Comparison: previous day.
Data check. Is the Sunday file complete? Compare the hourly pattern with other Sundays: if the drop is spread across all hours rather than a missing block of hours, it is probably real.
Decompose. Which boroughs, which zones, which hours? Airport trips behave differently from Midtown trips.
Hypothesise. The obvious one is the weekly cycle: Sundays are usually lower. Compare Sunday 5 January with Sunday 12 January, not with Saturday. Also check for weather events and the first days of the congestion pricing programme, which began in January 2025 and appears in the data as
cbd_congestion_fee.
The likely answer: "Mostly the normal Sunday dip; comparing like for like, the change is small. We will confirm once the next two Sundays are in." A day-over-day comparison was the wrong frame from the start.
Common mistakes
Answering a different question. Confirm the metric and comparison before you start.
Skipping the data check. A broken tracking event looks exactly like a business collapse.
Boiling the ocean. Twenty charts are not an answer. Test the most likely hypotheses first.
Stopping at "what" without "so what". End with what the stakeholder should do or watch next.
Interview angle
Case prompts like "DAU dropped 10% yesterday, what do you do?" are graded on structure. Say the four steps out loud, ask clarifying questions, then go segment by segment. Mention data checks first; many candidates forget them.
Practice task
Frame "average order value fell 8% in November" for the Online Retail data. List five hypotheses in the order you would test them.
Pick any public company's quarterly result with a revenue decline. Write a half-page framing: what you would check and why.