Level 2 · Build the monthly review in pandas

What you are checking, and why

A month is ready to report only when its orders have settled. Orders arrive as pending and become completed, cancelled or refunded over the following days. A cross-tabulation of month against status shows where the open orders sit:

counts = pd.crosstab(o["month"], o["status"])

November has 2 pending orders out of 715; December has 94 out of 777. That is the evidence behind reporting November.

One table per source

The KPIs come from three tables at three grains: order lines, returns and web sessions. The safe pattern is to bring each to one row per month, indexed by month, then join them:

sales = lines.groupby("month").agg(revenue=("revenue", "sum"), orders=("order_id", "nunique"), ...)
refunds = returns.groupby(returns["returned_on"].dt.strftime("%Y-%m"))["refund_amount"].sum()
kpi = sales.join(refunds.rename("refunds")).join(web)

New buyers need one more step. “New” means the customer’s first completed order falls in the month. transform("min") puts each customer’s first order date on every one of their lines, and .where() keeps the customer_id only on lines in that first month, so nunique() of the result counts new buyers.

Reading the result

month	net_revenue	orders	avg_order_value	buyers	web_conversion_pct
2024-11	319,297.22	564	566.13	357	3.50
2025-10	174,599.43	333	524.32	261	2.33
2025-11	292,364.40	651	449.10	442	4.45

Against October everything is up, as it is every November. Against last year, volume grew and revenue fell, so the question becomes average order value. Step 3 answers it: seven bulk orders last November, one this November.

Explaining the mover

Step 3 works at the order level, because the size of an order is only known once its lines are added up. Group the lines by month and order, label each order with np.where, then group the orders by month and band. The result splits each November into a few large orders and many regular ones, which is the breakdown that turns “revenue fell” into a reason leadership can act on.

Pitfalls

• groupby on a computed Series (returns["returned_on"].dt.strftime(...)) works without adding a column first; the result is indexed by the month labels.
• Summing buyers across months double counts people who bought in both.
• .loc[["2024-11", ...]] raises a KeyError if a month is missing from one source; join keeps the sales months and leaves NaN where another source has none.