Level 6 · Boss: the clean 2025 report

Clean, then count

Board numbers get compared with finance’s numbers, last quarter’s deck and the CEO’s memory. When they disagree, the first question is always “what did you include?”. A clean report answers that question before anyone asks: it removes known-bad rows, uses a stated definition of revenue, and counts real customers, not records.

Step 1: size the problem

Before excluding anything, measure it:

(order_items["quantity"] <= 0).sum()
order_items[order_items["quantity"] <= 0].head()

Zero and negative quantities are data-entry errors here (they’re never returned items). Knowing it’s 24 lines out of about 30,000 tells you the effect is small but real, and lets you write a one-line footnote: “24 order lines with invalid quantities excluded”.

Never fix bad rows by guessing (flipping negatives to positive, say) unless someone who owns the data confirms that’s the right correction.

Step 2: a clean monthly revenue series

The definition, written down first: line revenue after discount, valid quantities only, completed orders only, by month of the order date, 2025.

good = order_items[order_items["quantity"] > 0]
good = good.assign(line_revenue=good["quantity"] * good["unit_price"] * (1 - good["discount_pct"] / 100))

completed = orders.loc[orders["status"] == "completed", ["order_id", "order_date"]]
lines = good.merge(completed, on="order_id", how="inner", validate="many_to_one")

The inner merge does two jobs: it brings in the order date and drops lines from cancelled, refunded and pending orders. Then filter to 2025 and group by a month label:

lines_2025 = lines[lines["order_date"].dt.year == 2025]
lines_2025.assign(month=lines_2025["order_date"].dt.strftime("%Y-%m")).groupby("month", as_index=False)...

strftime("%Y-%m") gives text like '2025-11', which sorts correctly and reads well. Expect November and December to stand out: Black Friday, Cyber Monday and the holidays lift them well above every other month, while February is the quietest.

order_date includes a time, and that’s fine for month grouping. If you ever filter with <= "2025-12-31", remember that means midnight at the start of December 31, which drops that whole day. Use < "2026-01-01" or .dt.year == 2025 instead.

Step 3: real customers

deduped = (
    customers.assign(email_key=customers["email"].str.strip().str.lower())
    .sort_values("customer_id")
    .drop_duplicates(subset="email_key", keep="first")
)
deduped.groupby("segment", as_index=False).agg(n_customers=("customer_id", "count"))

Sorting by customer_id before dropping makes “keep the original signup” an explicit rule rather than an accident of file order. The deduplicated total should be exactly 18 lower than len(customers); check it.

Note this counts everyone who signed up. If the board wants active customers, that’s a different definition (customers with a completed order in 2025) and a different number. Say which one the slide shows.

What a good cleaning step looks like

• It’s measured: you know how many rows it touches.
• It’s explained: one sentence on why.
• It’s reproducible: code, not manual edits in a spreadsheet.
• It’s checked: totals before and after reconcile.

Common mistakes

• Filtering quantity after aggregating, when the bad lines are already inside the totals.
• Grouping by dt.month alone, which merges months from different years if the year filter is missing.
• Dropping duplicates without sorting, so a duplicate record might survive instead of the original.