Module 8: Complete Retail Analysis Capstone · Lecture 30 of 32
Begin a complete portfolio project for the fictional retailer. Freeze the business brief, inspect every planted defect, and produce an audit trail before calculating the headline KPIs.
What you will learn
- Write a bounded analysis brief with explicit exclusions.
- Reconcile raw, duplicate, rejected, and accepted rows.
- Create a source-review table without editing the raw export.
Understand the problem
The project brief
The fictional manager asks: what does the accepted January–March 2026 order export show about net merchandise revenue, channel mix, monthly change, and repeat purchasing among identified customers? The decision is which questions deserve better data or further investigation. It is not a request to estimate real market demand, profit, advertising return, or causal growth.
Acceptance criteria
Deliver an immutable input copy, source hashes, a quality audit, cleaned and validated order data, channel and monthly summary tables, a cohort calculation, a readable chart, a decision memo, and tests. All additive summaries must reconcile to one accepted-order revenue total. Every rate must name its denominator. Newer cohorts must not receive invented future retention values.
Investigate the planted defects
The raw file contains 244 rows. Two are exact redeliveries. Three fail validity: one original order has an unparseable price, one extra record has an invalid timestamp, and one extra record has zero units. A missing customer identity is retained as anonymous, and channel whitespace or case is normalized. These are distinct policies, not one global dropna operation.
Keep an evidence log
The review table below identifies the three known validity failures with human-readable reasons. The shared cleaner additionally checks status vocabulary, integer domains, discount bounds, refund bounds, and conflicting order keys. A production quarantine system should retain all applicable failure reasons for every record, source location, and resolution status. This small review table is an inspectable capstone artifact for the supplied fixture.
Run the worked example
Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 30 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.
from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT
import json
raw = load_raw()
unique = raw.drop_duplicates().copy()
price = pd.to_numeric(unique['unit_price_paise'], errors='coerce')
units = pd.to_numeric(unique['units'], errors='coerce')
dates = pd.to_datetime(unique['ordered_at'], errors='coerce', utc=True)
reasons = pd.Series('', index=unique.index, dtype='string')
reasons.loc[price.isna()] += 'invalid price; '
reasons.loc[dates.isna()] += 'invalid timestamp; '
reasons.loc[units.isna() | units.le(0)] += 'invalid units; '
review = unique.loc[reasons.ne('')].assign(reason=reasons.loc[reasons.ne('')])
orders, audit = clean_orders(raw)
review.to_csv(OUT / 'capstone-quarantine-review.csv', index=False)
(OUT / 'capstone-audit.json').write_text(json.dumps(audit, indent=2), encoding='utf-8')
print(review[['order_id', 'reason']].to_string(index=False))
print('Row reconciliation:', audit)
assert len(review) == audit['rejected_rows'] == 3
assert audit['input_rows'] == audit['exact_duplicates'] + audit['rejected_rows'] + len(orders)
Verified output
order_id reason
O0007 invalid price;
XBADDATE invalid timestamp;
XBADUNITS invalid units;
Row reconciliation: {'input_rows': 244, 'exact_duplicates': 2, 'rejected_rows': 3, 'accepted_rows': 239}
Walk through the result
The review uses the deduplicated raw population so a repeated invalid delivery would not inflate the number of distinct rejected records. It retains original source fields plus a reason. The audit equation accounts for all raw rows, while the shared cleaner supplies the accepted analytical table. The anonymous checkout does not appear in the validity review because its missing identity is allowed by the declared revenue contract.
Common mistakes and diagnosis
Do not start with the chart and retrofit cleaning rules to improve it. Do not remove a defect from the source CSV after noticing it. Do not describe all rejected records as missing values; the zero-unit record is present and numeric but invalid. If a real export has additional failure classes, extend the review logic rather than assuming the three teaching examples exhaust data-quality risks.
Practice assignment
Write the brief in your own words, including the reporting timezone, grain, revenue formula, AOV denominator, and three unsupported claims. Verify that every rejected key appears in your review and every accepted key is unique. Commit the audit before moving to the next capstone lecture.
Worked solution and explanation
Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.
assert orders['order_id'].is_unique
rejected_keys = set(review['order_id'])
assert rejected_keys == {'O0007', 'XBADDATE', 'XBADUNITS'}
assert rejected_keys.isdisjoint(set(orders['order_id']))
assert int(orders['customer_id'].isna().sum()) == 1
Check your understanding
Why must the brief be written before exploring channel rankings?
Answer guide
It fixes the population, metric, and decision boundary before a visually appealing pattern can influence them. Exploration can generate new hypotheses, but changing the headline question after seeing results should be disclosed. The brief also makes exclusions and unsupported claims reviewable before the report appears persuasive.
Reference and next step
Download the complete course kit: datasets, all examples, solutions, and capstone.
Return to the full Data Analysis with Python and pandas curriculum.
