1. Lighthouse

Level 3 · Revenue mix vs margin mix

The request

From Thomas Whitaker, Head of Merchandising

“We plan next year's buying budget by sales share. Laura says that's the wrong lens. For 2025, can you put each category's share of sales next to its share of gross margin so we can see where the two disagree?”

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 products DataFramenot done yet
  • Use the orders DataFramenot done yet
  • Show exactly category sales_share_pct margin_share_pct gap_ptsnot done yet

Your code

Python

Loading the editor…

    ResultPreview of products · press Run to see yours
    Preview of products
    product_idnamecategorysubcategoryunit_price
    101Vale Velvet LoveseatFurnitureSofas1659.00
    102Alder Oak Coffee TableFurnitureTables919.00
    103Orchard Rattan Dining ChairFurnitureChairs789.00
    104Upland Oak BookcaseFurnitureStorage429.00

    Mote checks your result

    Press Run. Each row that matches the goal becomes a firefly in your lantern.

    DataFrames

    Loaded for you as pandas DataFrames, plus pd and np.

    products120 rows · 8 columns
    • product_idinteger
    • namevarchar
    • categoryvarchar
    • subcategoryvarchar
    • unit_pricedecimal
    • unit_costdecimal
    • launched_ondate
    • is_activeboolean
    First 5 rows of products
    product_idnamecategorysubcategoryunit_priceunit_costlaunched_onis_active
    101Vale Velvet LoveseatFurnitureSofas1659.00NaN2023-09-11true
    102Alder Oak Coffee TableFurnitureTables919.00503.502024-05-16true
    103Orchard Rattan Dining ChairFurnitureChairs789.00429.872023-05-27true
    104Upland Oak BookcaseFurnitureStorage429.00214.702022-12-20true
    105Avery Leather Bed FrameFurnitureBeds449.00232.702021-08-31true
    orders12,000 rows · 7 columns
    • order_idinteger
    • customer_idinteger
    • order_datetimestamp
    • statusvarchar
    • channelvarchar
    • shipping_feedecimal
    • coupon_codevarchar
    First 5 rows of orders
    order_idcustomer_idorder_datestatuschannelshipping_feecoupon_code
    10011252023-01-01 20:51:19completedweb0.00NaN
    10021562023-01-02 07:58:46completedweb0.00NaN
    1003222023-01-02 10:25:27completedmarketplace4.99WELCOME10
    1004482023-01-02 11:21:51completedmarketplace4.99WELCOME10
    1005822023-01-02 11:43:36completedweb0.00WELCOME10
    order_items30,338 rows · 6 columns
    • order_item_idinteger
    • order_idinteger
    • product_idinteger
    • quantityinteger
    • unit_pricedecimal
    • discount_pctdecimal
    First 5 rows of order_items
    order_item_idorder_idproduct_idquantityunit_pricediscount_pct
    11001121931.990.00
    21001154311.990.00
    31002154411.990.00
    410021941149.000.00
    51002193219.990.00
    customers2,018 rows · 9 columns
    • customer_idinteger
    • first_namevarchar
    • last_namevarchar
    • emailvarchar
    • cityvarchar
    • statevarchar
    • signup_datedate
    • segmentvarchar
    • referral_sourcevarchar
    First 5 rows of customers
    customer_idfirst_namelast_nameemailcitystatesignup_datesegmentreferral_source
    1OwenHudsonowen.hudson@icloud.comSan FranciscoCA2022-06-02consumersearch
    2DouglasMendozadmendoza69@gmail.comAtlantaGA2022-06-02consumerNaN
    3HenryBrookshbrooks@lakesidecafe.comLos AngelesCA2022-06-04businessNaN
    4BrianGrahambgraham@bluecedarhomes.comSeattleWA2022-06-06businesspinterest
    5KyleJimenezkjimenez27@yahoo.comIndianapolisIN2022-06-07consumerNaN
    returns1,673 rows · 5 columns
    • return_idinteger
    • order_item_idinteger
    • returned_ondate
    • reasonvarchar
    • refund_amountdecimal
    First 5 rows of returns
    return_idorder_item_idreturned_onreasonrefund_amount
    1142023-01-10damaged83.69
    2422023-01-10wrong_item19.99
    31052023-01-14wrong_item10.99
    4242023-01-16changed_mind26.99
    51262023-01-16late20.99
    employees60 rows · 8 columns
    • employee_idinteger
    • first_namevarchar
    • last_namevarchar
    • departmentvarchar
    • titlevarchar
    • manager_idinteger
    • hire_datedate
    • salaryinteger
    First 5 rows of employees
    employee_idfirst_namelast_namedepartmenttitlemanager_idhire_datesalary
    1CatherineHaleExecutiveChief Executive OfficerNaN2016-03-01285000
    2RobertKimExecutiveChief Operating Officer12017-01-09235000
    3MeganCollinsSalesHead of Sales12018-04-16172000
    4ChloeGutierrezSalesAccount Executive32020-01-2172000
    5FrankBellSalesAccount Executive32023-05-2173000
    web_sessions40,000 rows · 7 columns
    • session_idinteger
    • customer_idinteger
    • started_attimestamp
    • devicevarchar
    • sourcevarchar
    • pages_viewedinteger
    • convertedboolean
    First 5 rows of web_sessions
    session_idcustomer_idstarted_atdevicesourcepages_viewedconverted
    1NaN2023-01-01 23:02:24mobilepaid1false
    2182023-01-01 15:07:49desktoppaid1false
    3NaN2023-01-01 16:55:46desktopdirect7false
    4NaN2023-01-01 12:43:28mobileorganic3false
    5NaN2023-01-01 13:06:21mobilesocial2false
    marketing_spend3,276 rows · 3 columns
    • spend_datedate
    • channelvarchar
    • amountdecimal
    First 5 rows of marketing_spend
    spend_datechannelamount
    2023-01-01paid492.99
    2023-01-01email37.38
    2023-01-01social200.53
    2023-01-02paid709.13
    2023-01-02email64.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.

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.