The request

From Thomas Whitaker, Head of Merchandising
Your goal
From costed (built in the starter: valid lines of booked 2025 orders with a known unit cost, with net and cogs), set result to a DataFrame with one row per category and columns category, sales_share_pct (the category's net sales × 100 / all net sales), margin_share_pct (its gross margin × 100 / all gross margin) and gap_pts (margin_share_pct − sales_share_pct), all rounded to 2 (compute gap_pts from the unrounded shares), sorted by gap_pts from high to low.
- Use the
productsDataFramenot done yet - Use the
ordersDataFramenot done yet - Show exactly
categorysales_share_pctmargin_share_pctgap_ptsnot done yet
Your code
Loading the editor…
Reference solution
| product_id | name | category | subcategory | unit_price |
|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 |
Mote checks your result
Press Run. Each row that matches the goal becomes a firefly in your lantern.
What Thomas gets, and why
DataFrames
Loaded for you as pandas DataFrames, plus pd and np.
products120 rows · 8 columns
product_idintegernamevarcharcategoryvarcharsubcategoryvarcharunit_pricedecimalunit_costdecimallaunched_ondateis_activeboolean
| product_id | name | category | subcategory | unit_price | unit_cost | launched_on | is_active |
|---|---|---|---|---|---|---|---|
| 101 | Vale Velvet Loveseat | Furniture | Sofas | 1659.00 | NaN | 2023-09-11 | true |
| 102 | Alder Oak Coffee Table | Furniture | Tables | 919.00 | 503.50 | 2024-05-16 | true |
| 103 | Orchard Rattan Dining Chair | Furniture | Chairs | 789.00 | 429.87 | 2023-05-27 | true |
| 104 | Upland Oak Bookcase | Furniture | Storage | 429.00 | 214.70 | 2022-12-20 | true |
| 105 | Avery Leather Bed Frame | Furniture | Beds | 449.00 | 232.70 | 2021-08-31 | true |
orders12,000 rows · 7 columns
order_idintegercustomer_idintegerorder_datetimestampstatusvarcharchannelvarcharshipping_feedecimalcoupon_codevarchar
| order_id | customer_id | order_date | status | channel | shipping_fee | coupon_code |
|---|---|---|---|---|---|---|
| 1001 | 125 | 2023-01-01 20:51:19 | completed | web | 0.00 | NaN |
| 1002 | 156 | 2023-01-02 07:58:46 | completed | web | 0.00 | NaN |
| 1003 | 22 | 2023-01-02 10:25:27 | completed | marketplace | 4.99 | WELCOME10 |
| 1004 | 48 | 2023-01-02 11:21:51 | completed | marketplace | 4.99 | WELCOME10 |
| 1005 | 82 | 2023-01-02 11:43:36 | completed | web | 0.00 | WELCOME10 |
order_items30,338 rows · 6 columns
order_item_idintegerorder_idintegerproduct_idintegerquantityintegerunit_pricedecimaldiscount_pctdecimal
| order_item_id | order_id | product_id | quantity | unit_price | discount_pct |
|---|---|---|---|---|---|
| 1 | 1001 | 121 | 9 | 31.99 | 0.00 |
| 2 | 1001 | 154 | 3 | 11.99 | 0.00 |
| 3 | 1002 | 154 | 4 | 11.99 | 0.00 |
| 4 | 1002 | 194 | 1 | 149.00 | 0.00 |
| 5 | 1002 | 193 | 2 | 19.99 | 0.00 |
customers2,018 rows · 9 columns
customer_idintegerfirst_namevarcharlast_namevarcharemailvarcharcityvarcharstatevarcharsignup_datedatesegmentvarcharreferral_sourcevarchar
| customer_id | first_name | last_name | city | state | signup_date | segment | referral_source | |
|---|---|---|---|---|---|---|---|---|
| 1 | Owen | Hudson | owen.hudson@icloud.com | San Francisco | CA | 2022-06-02 | consumer | search |
| 2 | Douglas | Mendoza | dmendoza69@gmail.com | Atlanta | GA | 2022-06-02 | consumer | NaN |
| 3 | Henry | Brooks | hbrooks@lakesidecafe.com | Los Angeles | CA | 2022-06-04 | business | NaN |
| 4 | Brian | Graham | bgraham@bluecedarhomes.com | Seattle | WA | 2022-06-06 | business | |
| 5 | Kyle | Jimenez | kjimenez27@yahoo.com | Indianapolis | IN | 2022-06-07 | consumer | NaN |
returns1,673 rows · 5 columns
return_idintegerorder_item_idintegerreturned_ondatereasonvarcharrefund_amountdecimal
| return_id | order_item_id | returned_on | reason | refund_amount |
|---|---|---|---|---|
| 1 | 14 | 2023-01-10 | damaged | 83.69 |
| 2 | 42 | 2023-01-10 | wrong_item | 19.99 |
| 3 | 105 | 2023-01-14 | wrong_item | 10.99 |
| 4 | 24 | 2023-01-16 | changed_mind | 26.99 |
| 5 | 126 | 2023-01-16 | late | 20.99 |
employees60 rows · 8 columns
employee_idintegerfirst_namevarcharlast_namevarchardepartmentvarchartitlevarcharmanager_idintegerhire_datedatesalaryinteger
| employee_id | first_name | last_name | department | title | manager_id | hire_date | salary |
|---|---|---|---|---|---|---|---|
| 1 | Catherine | Hale | Executive | Chief Executive Officer | NaN | 2016-03-01 | 285000 |
| 2 | Robert | Kim | Executive | Chief Operating Officer | 1 | 2017-01-09 | 235000 |
| 3 | Megan | Collins | Sales | Head of Sales | 1 | 2018-04-16 | 172000 |
| 4 | Chloe | Gutierrez | Sales | Account Executive | 3 | 2020-01-21 | 72000 |
| 5 | Frank | Bell | Sales | Account Executive | 3 | 2023-05-21 | 73000 |
web_sessions40,000 rows · 7 columns
session_idintegercustomer_idintegerstarted_attimestampdevicevarcharsourcevarcharpages_viewedintegerconvertedboolean
| session_id | customer_id | started_at | device | source | pages_viewed | converted |
|---|---|---|---|---|---|---|
| 1 | NaN | 2023-01-01 23:02:24 | mobile | paid | 1 | false |
| 2 | 18 | 2023-01-01 15:07:49 | desktop | paid | 1 | false |
| 3 | NaN | 2023-01-01 16:55:46 | desktop | direct | 7 | false |
| 4 | NaN | 2023-01-01 12:43:28 | mobile | organic | 3 | false |
| 5 | NaN | 2023-01-01 13:06:21 | mobile | social | 2 | false |
marketing_spend3,276 rows · 3 columns
spend_datedatechannelvarcharamountdecimal
| spend_date | channel | amount |
|---|---|---|
| 2023-01-01 | paid | 492.99 |
| 2023-01-01 | 37.38 | |
| 2023-01-01 | social | 200.53 |
| 2023-01-02 | paid | 709.13 |
| 2023-01-02 | 64.13 |
The lesson
Two shares of the same pie
A mix is how a total splits across groups. Revenue mix is each category’s share of sales; margin mix is its share of gross margin. When every category had the same margin %, the two mixes would be identical. They differ exactly where margins differ:
- A category with an above-average margin % has a bigger share of margin than of sales.
- A category with a below-average margin % has a smaller one.
The gap between the two shares, in percentage points, is a quick way to see who carries the profit.
Shares use the grand total
cat = costed.groupby("category")[["net", "margin"]].sum()
cat["net"] * 100 / cat["net"].sum() # sales share, adds up to 100
cat["margin"] * 100 / cat["margin"].sum() # margin share, adds up to 100
Each share divides a category’s sum by the store-wide sum of the same column. Dividing margin by the category’s own sales would give its margin %, which is a different number with a different question behind it.
Percentage points, not percent
The gap is a difference of two percentages, so it is measured in percentage points. A category going from a 23% share to a 21% share lost two points, which is a 9% relative drop. Write “pts” for the first and ”%” for the second; mixing them up is one of the most common errors on slides.
What to do with the gap
A gap is not a verdict. Outdoor’s lower margin share comes with the largest sales, and dropping it would lose margin dollars. The gap tells merchandising where an extra dollar of budget earns more: in the categories that keep more of each sale, as long as demand is there. Pair it with the margin dollars from level 1 before recommending anything.
Common mistakes
- Shares that do not add to 100. A sign the denominator was the group, not the total.
- Rounding before subtracting. Two rounded shares can make a gap of 0.01 that is really 0.004.
- Ranking categories by sales share alone. It describes size, not value.
Interview note
“How would you allocate a budget across product lines?” Start with both mixes. Showing that sales share and margin share disagree, and by how much, is a strong first answer.
Lantern lit!
Practised: mixshare of totalgross margingroupbytransform
- Solved
- No hints
- First run
