Level 1 · Frame the showroom question

Why framing comes first

“Where should the showroom go?” sounds like a question, but it hides several choices you have to make before writing a query. The checklist most analysts use is SMART:

• Specific: which customers, which places. Here: consumer customers, grouped by the metro they live in, with the state totals alongside.
• Measurable: which number ranks them. Completed revenue in 2025 says where the money is today; its change on 2024 says where it is heading. Buyers (people who ordered) say how many potential visitors there are.
• Achievable: can the data answer it? Partly. Every order links to a customer with a city and a state, but those columns were typed by people, so they need cleaning first. The data cannot tell you about cities where nobody has bought from us yet.
• Relevant: what decision does it feed? A shortlist of one or two metros for the board pack.
• Time-bound: 2024 and 2025, answer by the end of the month.

If you cannot write the question in one sentence that contains all five, it is not ready yet.

Which customers, which geography?

Two choices turn a lookup into an analysis:

• Consumers, not everyone. About one customer in seven is a business account. Business customers buy more and bigger, so they can dominate a state’s revenue, but they order online and have no reason to walk into a showroom. For a showroom, consumer revenue is the measure that fits. For a regional warehouse the answer would be different: there, every shipped dollar counts.
• Metros, not states. A showroom sits in one city. California’s revenue is spread across Los Angeles, San Francisco, San Diego, Sacramento and more; a store in Los Angeles cannot serve all of it. Rank metros, and keep the state table as context.

The data needs cleaning first

Customer locations are free text. The same state shows up as CA, ca, CA and CA, cities as New York, NEW YORK and new york, and 14 customers left the city blank. Some people also signed up twice with the same email in a different case. Any count you produce before cleaning is wrong in a way that is hard to see.

What the plan looks like

Step	What you do	Output
Check	Count raw and cleaned labels, missing cities and duplicate people	one row of counts
Analyse	Buyers and revenue by cleaned state, 2024 and 2025	a top-10 state table
Analyse	Consumer buyers and revenue by cleaned metro	a top-8 metro table
Recommend	A shortlist with numbers and caveats	three or four sentences

Interview note

“How would you choose where to open a store?” is a common case interview question. A strong answer names the measure (revenue or buyers from the customers the store would serve), the geography (the catchment area, not the state), the data cleaning it needs, and the limit: online sales show where existing customers are, not how many people would walk in.