Asking a question data can answer
Most analysis that disappoints started with a vague question. This lesson takes a real message, "East city sales are down. Why?", and turns it into a decision, a precise question, a metric with an exact definition, a fair comparison and a dataset, so the work that follows answers what was asked.
By the end you can
- Turn a vague request into the decision it feeds and a question someone else could answer
- Define a metric exactly: numerator, denominator, period, grain and filters
- Break sales into transactions × average basket, and slice it by store, category, channel and time
- Choose a like-for-like comparison and write your hypotheses before you touch the data
- Write a one-page analysis plan and the SQL that splits two periods into their drivers
The everyday picture
You walk into a clinic and say, "I feel unwell." A good doctor does not order every test in the building. She asks questions first. Where does it hurt? Since when? Worse than usual, compared with what?
Then she measures something with a fixed method. Your temperature is taken the same way every time, in the same units, so today's number can be compared with a normal one. Before she orders a blood test, she already has a short list of likely causes, and she picks each test to rule one of them in or out. All of it serves a decision: rest at home, a prescription, or a referral to a specialist.
Data work runs the same way. "Sales are down" is "I feel unwell". In the first hour your job is to find out where it hurts, measure it the same way twice, list the likely causes and choose the tests. The analysis comes after that.
From a vague ask to a decision and a question
At 9:05 on Monday 19 October 2026, Mangrove Market's head of stores sends the data team a message: "East city sales are down. Why?" You are the new analyst working with Tomás, Mangrove's data analyst, and it lands on your desk. This message runs through the whole course. By the last lesson you will answer it on one page.
It is tempting to open the sales export and start making charts. Wait an hour. The message hides at least four questions. Down compared with what? Down by how much? Which sales: the tills in the stores, the app, or both? And what will the head of stores do with the answer?
Start with the last one, because it sets how precise and how fast you need to be. Ask, "What will you decide with this?" Typical answers: send the regional team into some stores this week, change a price or a promotion, raise a problem with the distribution centre (DC), or do nothing because the dip is normal. Each decision needs a different cut of the data. If the head of stores will act store by store, a city total is useless and you need the answer by store.
Then write the question so that someone else could answer it without asking you anything. A good question names five things: the metric, the place, the period, the comparison and the cut (what you will split it by).
Vague: Why are East city sales down?
Precise: In the 4 weeks to Sunday 18 October 2026, how much did till sales
in East city's 54 stores fall against a fair comparison period,
and which stores, categories and hours of the day account for
most of the fall?The precise version can be answered with a table. The vague one invites a debate. Notice that it does not ask "why" directly. Data rarely tells you why on its own. It tells you where and when, and "where and when" points you to the cause, which you then confirm by talking to the people in those stores.
Quick check
The head of stores' message arrives. What is the most useful first reply?
The reply that asks for the comparison and the decision tells you what to measure and how to cut it. A dashboard of everything is fast to promise and hard to read, and asking for the export starts the work before you know what you are looking for.
Define the metric exactly
A metric is a number calculated from a written definition. "Sales" sounds obvious until two people calculate it. One includes app orders and the other does not. One counts by the date of the sale and the other by the date finance posted it. Both are right by their own rules, and the head of stores gets two numbers. Write the definition down before you calculate anything. It has five parts.
- Numerator: what you add up. Here, the amount the customer paid at the till, after discounts. Say whether tax is in and whether returns are subtracted.
- Denominator: what you divide by, if the metric is a ratio. Average basket is sales ÷ transactions. A total such as weekly sales has no denominator, but "sales per store" divides by the number of stores open.
- Period: the first and last moment, and the time zone. Use whole weeks, Monday to Sunday, so every weekday appears the same number of times in each period. A 4-week period has exactly four Mondays and four Saturdays.
- Grain: what one row of your result stands for, such as one row per store per week. Pull the finest grain the decision might need. You can add days up into weeks later, but you can never split a weekly total back into days or hours.
- Filters: which rows are in and which are out. East city only; stores only, not DCs; till sales only, because app orders are a separate metric with their own story.
Here is the definition card Tomás and you write for the East city question. Keep it next to every number you report.
## Metric: weekly till sales, East city
- Numerator: sum of sale.total_cents (paid at the till, after discounts,
tax included, returns not subtracted)
- Denominator: none for the total; average basket = sales / transactions
- Period: Monday 00:00 to Sunday 23:59, store local time, whole weeks
- Grain: pull one row per sale; report one row per store per week
- Filters: site.city = 'East' and site.kind = 'store'; app orders excluded
- Source: sale joined to site
- Owner: TomásFinance has its own net sales number, with returns and tax taken off, and that is fine. Two definitions can live side by side as long as each report says which one it uses. Agreeing on one definition across a whole company is the subject of Metrics everyone agrees on in the dashboards course.
One more detail matters in SQL. Write the period as a half-open range: from the first day, up to but not including the day after the last day. sold_at >= '2026-09-21' AND sold_at < '2026-10-19' keeps a sale at 21:45 on Sunday 18 October. sold_at <= '2026-10-18' silently drops it, because '2026-10-18' means midnight at the start of that Sunday.
Quick check
The head of stores may want to know which hours of the day lost sales. Which grain should you pull?
You can always add up from a fine grain to a coarse one. A weekly total has lost the hours for good, so if the hour of day might matter, pull the sales with their timestamps.
Break the number into drivers
A total going down tells you very little. Break it into the pieces that multiply together to make it. A driver tree does this on paper. For store sales the first split is the most useful one in retail:
sales = transactions × average basket
A typical Mangrove store:
about 1,250 transactions a day × 7 days = about 8,750 a week
8,750 × about $11 average basket = about $95,000 a week
East city: 54 such stores = about $5.1M a weekEach branch splits again. Transactions are shoppers × visits per shopper. The basket is items per basket × average item price. The two branches point at different causes. Fewer transactions means fewer people came in: a new competitor, a closed road, bad weather, or empty shelves that sent regulars elsewhere. A smaller basket means people came but bought less: gaps on the shelf for things they wanted, a promotion that ended, or a move to cheaper brands.
Changes in the drivers multiply too. Suppose a store's transactions fall 4% and its basket falls 2%. Its sales are now 0.96 × 0.98 = 0.941 of what they were, a fall of 5.9%. For small changes the percentages roughly add up, which makes a quick mental check easy: a 6% fall in sales with flat transactions must be almost all basket.
Then slice. Split each box by store (all 54), category (fresh produce, bakery, dairy, drinks, household and so on), channel (till or app) and time (week, weekday, hour). A fall that sits in a few slices points at something local. A fall spread evenly across every store and category points at something city-wide, such as the calendar, the weather or the economy.
Choose slices that do not overlap and together cover everything, so that the pieces add back up to the total. Consultants call this MECE. If your 54 store rows add up to more than the city total, something is counted twice.
Quick check
A store's sales fell 10% in a month. Its number of transactions did not change. Where does the driver tree tell you to look first?
Sales = transactions × basket. If transactions are flat, the whole fall is in the basket, so people came as often and bought less. Footfall would show up as fewer transactions.
Compare like for like, and write hypotheses first
A change only means something against a fair comparison. Tomás pulls the headline in five minutes: East city's store sales averaged about $5.13M a week in the 4 weeks to Sunday 18 October, against about $5.46M a week in the 4 weeks before. That is a fall of about $330,000 a week, or about 6%. Before anyone acts on the 6%, check whether the two periods are comparable. Two things usually make them unfair.
The calendar. Festival weeks, month-end salary days, public holidays, school terms and the weather season all move grocery sales. Many festivals (Eid, Diwali, Lunar New Year) follow a lunar calendar and fall in different weeks each year, so look at the actual dates in both periods. The usual fix is to compare with the same weeks last year, shifted by 52 weeks (364 days), so that a Monday is compared with a Monday.
The stores. If a store opened, closed or shut for a refit during either period, the total moves for reasons that have nothing to do with shoppers. A like-for-like comparison keeps only the stores that traded for the whole of both periods. Report new and closed stores on their own line.
Add a control: the other five cities over the same weeks. If every city fell, look for something they share. If only East city fell, look for something local to it.
Now, before you open the data, write your hypotheses: a short list of possible causes, each with the evidence you would expect if it were true and the data that would show it. This matters more than it sounds. With 54 stores, 20 categories and 28 days, some number will always look odd, and an analyst without a list builds a story around whatever odd number appears first. A list turns the analysis into a set of tests, and it shows the head of stores that you considered more than one cause. You will run these tests later in this course, in Cleaning data in a spreadsheet and Pivot tables and summaries.
Quick check
A new East city store opened on 1 October. How should it be treated in the like-for-like comparison?
The new store did not trade in the earlier period, so including it, with or without zeros, makes the city look better than its existing stores really did. Like-for-like keeps only stores open for the whole of both periods; new stores are reported beside it.
Do it
Write a one-page analysis plan for the East city question, then take a first look at the drivers in a spreadsheet.
Write down the ask and the decision
Copy the request word for word, with who asked and when. Under it, write the decision it feeds and the deadline. If you do not know them, ask before you start.
Write the precise question
Name the metric, the place, the period, the comparison and the cut. Read it to someone else: could they answer it without asking you anything?
Define the metric and the comparison
Fill in the five parts of the definition. Choose the comparison: same weeks last year, same stores, plus the other cities as a control.
List hypotheses, data and checks
Write five to eight possible causes with the test for each. List the tables or exports you need and the checks you will run on them before you trust a number.
Markdown# Analysis plan: East city store sales Asked by: head of stores, Monday 19 October 2026, 9:05 Ask: "East city sales are down. Why?" Decision: send the regional team into specific stores next week, or not Deadline: Thursday 22 October Question: In the 4 weeks to Sun 18 Oct 2026, how much did till sales in East city's stores fall against the same weeks last year (like-for-like), and which stores, categories and hours account for most of the fall? Metric: weekly till sales (definition card attached) Drivers: transactions x average basket Compare: same 4 weeks last year, like-for-like stores; other five cities as a control Hypotheses (test for each): 1. Calendar: festival week in the earlier window (same weeks last year) 2. Store changes (stores with under 28 trading days) 3. Missing till data (rows per store per day) 4. Fewer shoppers in some stores (transactions by store) 5. Availability (sales by category and hour; out-of-stocks) 6. Prices and promotions (basket and item price by category) 7. Shift to the app (East city app orders) Data: sale, sale_line, article, site; out-of-stock report Checks: row counts per store per day; total within 1% of the finance weekly report; slices add back to the total Output: one page with two charts, answer firstTake a first look at the drivers in a spreadsheet
With a store-level export (columns store_id, store_name, date, sales, transactions),
SUMIFSadds up the rows that match conditions. It works the same in Excel and Google Sheets. Put the period's first day in H1 and the day after its last day in H2, then:OutputSales: =SUMIFS(D:D, C:C, ">="&H1, C:C, "<"&H2) Transactions: =SUMIFS(E:E, C:C, ">="&H1, C:C, "<"&H2) Basket: =sales cell / transactions cell Column C is date, D is sales, E is transactions. ">="&H1 means "on or after the date in H1"; "<"&H2 means "before the date in H2" (the half-open range).Use a store-level export for transactions. In an export with one row per store, day and category, a basket with bread and milk appears in two category rows, so adding up transactions across categories counts it twice. Pivot tables, which do this for every store at once, come in Pivot tables and summaries.
Bring it to your team
Ask these before anyone starts an analysis, and again when the first number comes back.
- What decision will this answer feed, who makes it, and by when?
- Compared with what? Are both periods whole weeks with the same calendar events?
- Which sales: till, app or both; gross or net; by sale date or posting date?
- Are these the same stores in both periods?
- Is the change in transactions, in the basket, or both?
- What were our hypotheses before we looked, and which ones have we ruled out?
Red flags
- A percentage change with no comparison period named.
- A period that compares a festival month with a normal one.
- "Sales" with no definition card, or two reports with different numbers and no explanation.
- A cause announced after looking at one chart, with no other hypotheses considered.
- An average basket for the city computed as an average of store averages.
Practice
Split two periods into transactions and basket
The table site(id, name, city, kind) lists stores and DCs. The table sale(id, site_id, sold_at, total_cents) has one row per checkout transaction, with sold_at in store local time and the amount in cents. Compare East city stores in two periods: before is Monday 24 August to Sunday 20 September 2026, and after is Monday 21 September to Sunday 18 October 2026, both days included. Return one row per period with the columns period, transactions (number of sales), sales_cents and avg_basket_cents (sales ÷ transactions, rounded to 1 decimal). Keep only East city stores, and use a half-open date range so a late Sunday sale is not lost.
CREATE TABLE site (id int PRIMARY KEY, name text NOT NULL, city text NOT NULL, kind text NOT NULL); CREATE TABLE sale (id int PRIMARY KEY, site_id int NOT NULL, sold_at timestamp NOT NULL, total_cents int NOT NULL); INSERT INTO site VALUES (1, 'East Riverside', 'East', 'store'), (2, 'East Market Road', 'East', 'store'), (3, 'North Hill', 'North', 'store'), (4, 'East DC', 'East', 'dc'); INSERT INTO sale VALUES (1, 1, '2026-08-24 07:10', 1200), (2, 2, '2026-08-25 08:30', 950), (3, 1, '2026-08-30 18:05', 1300), (4, 2, '2026-09-02 07:45', 1050), (5, 1, '2026-09-07 12:20', 1000), (6, 2, '2026-09-12 09:15', 1150), (7, 1, '2026-09-15 07:30', 1100), (8, 2, '2026-09-20 20:40', 1050), (9, 1, '2026-09-21 15:10', 1000), (10, 2, '2026-09-24 16:00', 1100), (11, 1, '2026-09-29 14:30', 950), (12, 2, '2026-10-03 11:05', 1200), (13, 1, '2026-10-08 17:20', 1000), (14, 2, '2026-10-13 15:40', 1050), (15, 1, '2026-10-18 21:45', 1050), (16, 3, '2026-09-01 10:00', 2500), (17, 3, '2026-10-01 10:00', 2600), (18, 1, '2026-10-19 08:00', 1300), (19, 2, '2026-08-23 19:00', 900);
-- East city store sales: before vs after, split into drivers.
-- The CASE puts each sale in a period. Still to do:
-- 1. JOIN site so you can keep only East city stores
-- (site.city = 'East' AND site.kind = 'store').
-- 2. Keep only the 8 weeks: sold_at from '2026-08-24'
-- up to, but not including, '2026-10-19'.
-- 3. Add avg_basket_cents: sales_cents / transactions, rounded to 1 decimal.
-- Tip: round(sum(total_cents)::numeric / count(*), 1)
SELECT CASE WHEN sold_at < '2026-09-21' THEN 'before' ELSE 'after' END AS period,
count(*) AS transactions,
sum(total_cents) AS sales_cents
FROM sale
GROUP BY 1;[{"period": "before", "transactions": 8, "sales_cents": 8800, "avg_basket_cents": 1100.0}, {"period": "after", "transactions": 7, "sales_cents": 7350, "avg_basket_cents": 1050.0}]
Show a solution
SELECT CASE WHEN s.sold_at < '2026-09-21' THEN 'before' ELSE 'after' END AS period,
count(*) AS transactions, -- one row = one checkout
sum(s.total_cents) AS sales_cents,
round(sum(s.total_cents)::numeric / count(*), 1) AS avg_basket_cents -- ratio of sums
FROM sale s
JOIN site st ON st.id = s.site_id -- to know each sale's city and kind
WHERE st.city = 'East' AND st.kind = 'store' -- the filters from the definition
AND s.sold_at >= '2026-08-24' -- half-open range: first day included
AND s.sold_at < '2026-10-19' -- keeps Sunday 21:45, drops Monday 19th
GROUP BY 1;Sales fell from 8,800 to 7,350 cents (about 16.5%). Transactions fell from 8 to 7 (12.5%) and the basket from 1,100 to 1,050 cents (about 4.5%): 0.875 × 0.955 = 0.835, which matches. The starter also counted the North store, the sale on Sunday 23 August and the sale on Monday 19 October. Writing sold_at <= '2026-10-18' would have lost the 21:45 sale on the last Sunday.
Turn a vague ask into a plan
Imran, the product manager for the Partner app, forwards a message from the head of wholesale: "Partner shops are ordering less. Can you look into it?" Mangrove has about 1,800 partner shops and about 4,000 partner orders a week. Write the top of an analysis plan: the decision you think it feeds (and the question you would ask to confirm it), a precise question, a metric definition with all five parts, a comparison, and at least five hypotheses with a test for each.
- You name a decision and the question you would send back to confirm it
- The question names a metric, a place, a period, a comparison and a cut
- The metric has a numerator, a denominator if it is a ratio, a period, a grain and filters
- The comparison handles the calendar and keeps the same shops in both periods
- At least five hypotheses, each with a test that could rule it out, including one about the data itself
Show a model answer
Decision: probably whether to send sales reps to specific shops or change wholesale prices. I would ask: "Will you act on particular shops or areas, or on pricing for everyone?"
Question: In the 4 weeks to Sunday 18 October 2026, how did weekly partner order value change against the same weeks last year, for shops active in both periods, and which areas, shop sizes and categories account for most of the change?
Metric: weekly partner order value = sum of order totals in cents for partner orders placed Monday to Sunday (numerator; app and phone orders both included, cancelled orders excluded), grain one row per shop per week, filters: shops that ordered at least once in both periods. Also orders per shop and average order value (order value ÷ orders) as drivers.
Comparison: same 4 weeks last year aligned by weekday; festival dates checked in both years.
Hypotheses: (1) calendar or festival timing: compare with last year; (2) fewer shops ordering (churn): count active shops; (3) same shops, smaller orders: average order value by shop; (4) orders moved from phone to app and some are missing from the data: compare both channels and check row counts; (5) a price change made some lines less attractive: order value by category before and after; (6) delivery problems made shops order elsewhere: late or short deliveries by area.
Check yourself
South city's app orders in the first week of November are 12% higher than in the last week of October. The app team credits a new home screen launched on 1 November. Which comparison would you ask for first?
The first week of a month often includes salary days, so a jump from late October to early November can happen with no change to the app. Last year's same weeks handle the calendar, and the other cities act as a control. A 12-month line chart shows history but does not separate the launch from the calendar, and rerunning the same number checks the arithmetic, not the comparison.
A store's weekly sales fell from about $95,000 to about $90,250, about 5%. Transactions fell from 8,750 to about 8,310, also about 5%. The average basket stayed at about $10.86. Which hypothesis should you test first?
Sales = transactions × basket. The basket did not move, so the whole fall is in transactions: fewer visits. The other three options would all show up as a smaller basket, which the data rules out.
Two analysts report East city sales for the same week from the same database: $5.13M and $4.71M. Neither found a bug in their own query. What should you do first?
Two correct queries that disagree almost always have different definitions, for example one subtracts returns and tax, or counts by posting date. Comparing the definitions finds the difference and tells you which number fits the question. Averaging or picking one hides the problem, and a late load is possible but rarely explains a gap this size.
Flashcards
Key takeaways
- Start from the decision the answer will feed, then write a question that names the metric, place, period, comparison and cut.
- Define every metric in five parts (numerator, denominator, period, grain, filters) and keep the definition next to the number.
- Break sales into transactions × average basket and slice by store, category, channel and time; the slices must add back to the total.
- Compare like for like: same stores, whole weeks, the same weeks last year, and other cities as a control.
- Write hypotheses with a test for each before you query anything, and put it all in a one-page analysis plan with a deadline.
Sources and further reading
- Same-store sales, Wikipedia (like-for-like or comparable store sales)
- MECE principle, Wikipedia
- SUMIFS function, Microsoft Support
- Aggregate functions, PostgreSQL documentation
- Date/time types, PostgreSQL documentation