All lessons

Module 9: Capstone and interviews · Lesson 9.1 · about 120 min

Capstone: a retail analysis notebook

Answer a real business question end to end with pandas: check and join four tables, compare the first half of 2026 with 2025 by store, category, product and customer, test what discounts really do, and publish the notebook on GitHub as a portfolio piece.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 9 · about 2 hours · uses all of the course. You’ll write a complete analysis notebook and publish it on GitHub, the piece employers ask to see when they say “show me some Python”.

A portfolio notebook answers a business question end to end: check the data, join it safely, give the headline, find where it comes from, and say what to do.

In everyday terms: a doctor’s report: tests checked, the main finding first, the evidence for it, and a clear next step, not a list of every measurement taken.

1. The brief

Made-up chain, made-up data. A retail chain has six stores in four provinces and a web shop. The head of retail asks:

“Revenue is up in the first half of 2026, but not in every store. Where is the growth coming from, where are we slipping, and what should we do in the second half?”

Download: the starter notebook and the four tables: orders.csv (9,717 order lines, Jan 2025 to Jun 2026), products.csv, stores.csv, customers.csv. Open the notebook in Jupyter, VS Code or Google Colab, or work in the boxes on this page.

2. Check, then join

Pythonorders.csv, products.csv, stores.csv

validate='many_to_one' makes pandas stop if a lookup table has a repeated key (Lesson 5.3). The assert is your safety net.

3. The headline

Pythonorders.csv, products.csv

Revenue is up about 10 %, driven by more orders and more customers, while the average order got slightly smaller. That is already a sentence for the top of the notebook.

Brief
The question and who will read the answer.
Checks
Row counts, keys, totals reconciled before any chart.
Headline
One sentence with a number: "Revenue grew 12 %, mostly from WEB".
Drill-down
Which store, product or segment drives it.
Physical meaning
Growth splits into parts that add up: each store's change in revenue sums to the total change. Showing those parts is what turns "revenue grew" into "here is where to invest".

“More charts make a better notebook.” Reviewers read the first screen. One headline, three supporting charts and a recommendation beat twenty unexplained plots.

Watch it happen, one step at a time:

Check. Lines 12,480; order_id unique per line; revenue reconciles with finance.
Join. Stores and products with validate='many_to_one'; row count unchanged.
Headline. Revenue up from 410k to 460k: +12.2 %.
Where?
storelast yearthis yearchange
S011501522
S02140138-2
WEB12017050
(thousands)
Recommendation. WEB makes all of the growth: invest there, and find out why S02 slipped.

4. Your turn: where is the growth?

Your turnorders.csv, stores.csv

Two stores shrank (Harbourview, the biggest store, and Online) while Cedar Falls grew by more than a third. Your notebook should ask why: product mix, fewer customers, smaller baskets? Split each store’s change into customers × orders per customer × AOV to find out.

5. Your turn: what do discounts do?

Your turnorders.csv, products.csv

6. The rest of the notebook

The starter notebook walks you through the remaining steps. Each has a lesson behind it:

  1. Monthly trend with year over year growth: season or real change? (Lesson 7.4)
  2. Products: Pareto and ABC; is any A product shrinking? (Lesson 8.3)
  3. Customers: RFM segments and how much revenue sits in At risk; a retention table (Lessons 8.4 and 8.5).
  4. Recommendations: three actions, each with the number behind it, what it could be worth and how to test it (an A/B test, Statistics course Module 7).
  5. Limits: what this data cannot say (no footfall, no competitor prices, made-up data).

7. Publish it on GitHub

  1. Make a repository called retail-analysis-pandas with data/, the notebook, and a README.md.
  2. Run the notebook top to bottom (Kernel > Restart and run all) so every output is fresh, then commit it. GitHub shows notebooks with their charts.
  3. The README in five lines: the question, the answer (three bullets with numbers), the methods used, how to run it, and “data is made up, from the fibub pandas course”.
  4. Pin the repository on your GitHub profile and link it from your CV under Projects.

What a strong notebook shows: the answer first; checks before results; one clean table built with checked joins; like-for-like comparisons; ratios rebuilt from totals; a few clear charts with titles that state the finding; short markdown between code cells; and recommendations that someone could act on on Monday.

How it works

Principle: most business questions break into where (stores, regions), what (products, categories), who (customers) and when (trend, season). Splitting a change into its parts tells you where to look:

revenue=customers×orderscustomer×revenueorder

If revenue rose 10 % with 12 % more customers and a 2 % smaller basket, the growth is reach, not spending, and the actions are different from the other way round.

8. Practice

  1. Split the chain’s H1 change into customers, orders per customer and AOV.
  2. Which category grew fastest? Is it a high or low margin category?
  3. Write the three-bullet answer for the top of the notebook.
Answers
  1. From section 3: customers 855 → 961 (+12.4 %), orders per customer 2.00 → 2.01 (about the same), AOV 39.5 → 38.6 (−2.4 %).
  2. Grocery, about +35 %. Merge cost and compare margin % by category; groceries usually carry thinner margins.
  3. For example: “Revenue +10 % (H1 2026 vs 2025), from 12 % more customers; baskets 2 % smaller. Harbourview (−4 %) and Online (−1 %) shrank while Cedar Falls grew 38 %. Discounted lines sell about the same number of units at 10 points less margin: test smaller discounts.”

Try it on your own data: CSV profiler for the first look and merge CSV to join two files without code.

New words: notebook, like for like, revenue decomposition, README, portfolio piece.

  • Start with the brief and the checks, not the charts.
  • Lead with one headline number.
  • Split the change into parts that add up.
  • End with what to do and what to check next.

Check yourself

1What should the first cell under the title of your notebook say?
2Revenue is up 10 %, but two stores fell. What makes this a useful finding?
3Discounted lines sell 1.58 units on average against 1.56 without a discount, at a 30 % margin instead of 40 %. A fair first conclusion?

Sources

  • Knaflic, C. N. (2015), Storytelling with Data, Wiley
  • McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.