Where to open the first showroom

Harbor & Pine has sold online since day one. Now the board is asking about a first physical showroom, and Catherine wants to know where it should go. Questions like this sound like a lookup, “which state sells the most?”, but the answer depends on how you clean the location labels, which customers you count, and whether you rank states or the cities a showroom actually serves.

This case is the whole job, the way you would do it at work:

1. Frame the question so it can be answered: which customers, which measure, which period, at which level of geography, and what decision it feeds.
2. Check the data: the state and city columns were typed by customers, so the same place appears under several spellings, and some people signed up twice.
3. Analyse in SQL, pandas or Excel, whichever you prefer: completed revenue and buyers by cleaned state, then consumer revenue by metro.
4. Recommend: pick the conclusion you would send, with the numbers and the caveats.

The case opens when you finish any one of the cleaning chapters it builds on: SQL chapter 10, pandas chapter 8 or Excel chapter 5. It uses TRIM, UPPER and COUNT(DISTINCT ...) (or the .str methods and nunique in pandas, or UPPER, TRIM and SUMIF on a prepared sheet).