Level 2 · Check and compare coupon orders in pandas

What you are checking, and why

The coupon lives in two places: the code on orders["coupon_code"] and the discount on every line in order_items["discount_pct"]. Every number in this case is built from the second, so first prove it agrees with the first:

lines = order_items.merge(orders[["order_id", "coupon_code"]], on="order_id")
lines["coupon"] = lines["coupon_code"].fillna("none")
lines.groupby("coupon")["discount_pct"].agg(["min", "max"])

If a code’s minimum and maximum are equal, all its lines carry the same discount. Look for three other things in the same result: a code written two ways (welcome10 next to WELCOME10), orders without a code that still carry a discount, and how many orders have no code at all. The fillna("none") matters: groupby drops NaN keys unless you tell it otherwise.

What each coupon cost

Step 2 rolls the lines up to one row per order first, because a coupon is an order-level choice: the average order value must divide by orders, not by lines.

order_totals = lines.groupby(["order_id", "coupon"], as_index=False)[["gross", "net"]].sum()

Then group order_totals by coupon. Revenue is the net sum, AOV is net per order, the discount cost is gross minus net, and the share divides each coupon’s net by the grand total g["net"].sum(), the same total for every row.

Like for like: the same days

Every coupon order averages less than an order without a code. That sounds damning, but the seasonal codes run in particular weeks, and Black Friday baskets are smaller across the board. The fair comparison puts each coupon next to the orders placed on the same days without a code.

coupon_days lists the days each seasonal code was used. Merging it with order_totals on day gives every order placed on those days, once per coupon. Because both tables have a coupon column, suffixes=("", "_order") keeps the coupon being studied as coupon and names the order’s own code coupon_order. Two boolean filters then split the rows into that coupon’s orders and the orders without a code.

WELCOME10 and FREESHIP are left out of this step because they run every day: for them, “the same days” means the whole year.

Reading the result

coupon	coupon_basket	coupon_aov	no_coupon_aov
BFCM25	536.54	402.41	483.06
HOLIDAY20	482.84	386.27	479.56
SPRING15	634.59	539.40	574.96
SUMMER15	671.75	570.98	658.07

The basket before the discount is about the same as, or a little above, a normal order on those days. So the codes did not make people buy much more; they mostly paid less for a similar basket.

Pitfalls

• .dt.date gives Python date objects; .dt.normalize() keeps a datetime column that merges fast.
• Filtering quantity > 0 matters: three coupon lines have a zero or negative quantity.
• FREESHIP’s cost is shipping, which is not in these totals. Mention it rather than reporting $0.