GoExplore is a growing camping and hiking supplier. Their only data scientist left without notice. CEO Sabah handed us a single spreadsheet and two weeks to build a working analytics setup from scratch.
① Expansion: GoExplore wants to open in 4 new European countries. Which market sizes should they expect?
② Retailer Performance: Do specialty stores (like Golf Shops) perform differently than general stores (like Sports Stores)?
The spreadsheet contains 4 separate sheets — think of them as 4 tables that can be connected to each other:
| Sheet name | What it contains | Key columns |
|---|---|---|
| retailers | Every store that sells GoExplore products — their name, type, and country | Retailer code, Name, Type, Country |
| products | Every product GoExplore sells — category, brand, cost, and price | Product number, Product line, Unit cost, Unit price |
| daily_sales | Every single transaction — 149,257 rows from January 2015 to July 2018. This is the main data source. | Retailer code, Product number, Date, Quantity, Unit sale price, Order method code |
| methods | How each order was placed — by phone, fax, web, etc. | Order method code, Order method type |
There is no "Revenue" column in the data. We have to calculate it ourselves for every row: Quantity × Unit sale price. All our KPIs are built on this calculation.
A KPI (Key Performance Indicator) is a number that answers a specific business question. Each of our 5 KPIs directly addresses something Sabah asked for.
Which of our existing 21 markets generates the most revenue? This is the starting point for everything — before we can estimate what Norway or Poland might be worth, we need to know what countries like Sweden or Germany actually earn for GoExplore today.
Germany has 84 million people and Sweden has 10 million — so of course Germany earns more in total. But that doesn't mean Germany is a "better" market per person. Revenue per capita (per person) removes that size bias and shows how much GoExplore earns per inhabitant in each country. We then use that number to estimate what the 4 new markets could be worth.
We need population and GDP (Gross Domestic Product — the total economic output of a country) figures for our 4 target markets. These come from public databases and will be added to our Google Sheet manually:
| Target country | Population | GDP (Gross Domestic Product) | Best comparison market we already have |
|---|---|---|---|
| Norway | 5.5 million | $580 billion USD | Sweden (similar size, same region) |
| Czech Republic | 10.8 million | $330 billion USD | Austria (similar size, Central Europe) |
| Poland | 36.8 million | $860 billion USD | Germany / France (larger markets) |
| Portugal | 10.4 million | $270 billion USD | Spain (same peninsula, similar culture) |
When a Golf Shop places an order, how much is it worth on average? And how does that compare to a general Sports Store? AOV (Average Order Value) tells us the typical size of one order per store type — so we can see if specialty stores spend more per order than general ones.
The data doesn't have a column called "Order ID". So we treat each unique combination of Date + Retailer as one order. This is a reasonable assumption but has a limitation — see below.
Because we don't have a real Order ID, we're grouping by Date + Retailer as a stand-in. If a retailer placed two separate orders on the same day, our query would count them as one. This slightly underestimates the true number of orders and makes our AOV a bit higher than reality. We know this and will flag it during the presentation.
How often does a typical store of each type order from GoExplore? A Golf Shop might order less frequently than a general Sports Store — but each order might be worth more. This KPI measures how active each store type is as a buyer. It also helps Sabah understand how many stores he'd need in a new country to hit a revenue target.
Do Golf Shops and Eyewear Stores sell more expensive products than general Outdoors Shops? This KPI looks at the average price of individual items sold per store type. If specialty stores consistently sell higher-priced products, that explains why their AOV (KPI 3) might be higher — it's not just that they buy more, they buy pricier things.
Suggested by Kelvin as natural extensions once the 5 core KPIs are in place.
Which products sell poorly but generate a high profit per unit? These are the best candidates for a targeted promotion — pushing a product that already earns well per sale but just needs more visibility is smarter than promoting a bestseller that sells itself anyway.
Is GoExplore actually growing? We have data from January 2015 to July 2018 — enough to see year-on-year trends and seasonal patterns. This tells Sabah whether the business is accelerating, plateauing, or dipping at certain times of year.
A world map in Data Studio that colours each country by revenue — darker means more revenue. This makes the expansion story immediately visible: Sabah can see at a glance which regions are strong and where the 4 new target countries sit relative to existing markets.
Add a new chart → select Geo Chart → set Country as the dimension and Total Revenue as the metric. Data Studio draws the map automatically. No SQL needed at this step.
We can't know exactly how much GoExplore will earn in Norway or Poland — they've never operated there. But we can make a data-driven estimate using countries we already know. The logic is simple: if Sweden earns X per person, and Norway has a similar population and GDP, then Norway should earn roughly the same per person.
| New target market | Why this comparison market? | Comparison market in our data |
|---|---|---|
| Norway | Same region, similar wealth and outdoor culture | Sweden + Denmark |
| Czech Republic | Similar size, Central European market | Austria |
| Poland | Much larger — needs bigger reference markets | Germany + France |
| Portugal | Same peninsula, similar consumer culture | Spain |
We work entirely inside Google's ecosystem. Think of it as an assembly line — raw data goes in one end, live dashboard comes out the other.
Unit sale price is higher than Unit price (this would be a data error)Retailer_code and Product_number