1. Lighthouse

Level 5 · What a new customer costs

The request

From Jordan Reyes, Head of Marketing

“Finance keeps asking what a new customer costs us. Can you give me CAC per year: everything we spent on marketing divided by the customers we actually acquired?”

Your goal

Set result to a DataFrame with one row per year (2023, 2024, 2025) and the columns year, spend (total marketing_spend.amount that year, all channels, rounded to 2), new_customers (customers whose first booked order falls in that year) and cac (spend / new_customers, rounded to 2, from the unrounded spend). Booked orders are built in the starter. Sort by year.

  • Use the orders DataFramenot done yet
  • Use the marketing_spend DataFramenot done yet
  • Show exactly year spend new_customers cacnot done yet

Your code

Python

Loading the editor…

    ResultPreview of orders · press Run to see yours
    Preview of orders
    order_idcustomer_idorder_datestatuschannel
    10011252023-01-01 20:51:19completedweb
    10021562023-01-02 07:58:46completedweb
    1003222023-01-02 10:25:27completedmarketplace
    1004482023-01-02 11:21:51completedmarketplace

    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.

    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
    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
    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
    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
    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
    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

    The lesson

    The cost side of a customer

    Customer acquisition cost (CAC) is what it costs to win one new customer:

    CAC = acquisition spend in a period / new customers acquired in that period

    LTV says what a customer is worth; CAC says what one costs. A business that pays more to acquire a customer than the customer is worth shrinks with every sale.

    Blended CAC

    The simplest version divides all marketing spend by all new customers. That is blended CAC. At Harbor & Pine:

    #    year      spend  new_customers     cac
    # 0  2023  382190.45            574  665.84
    # 1  2024  441894.83            525  841.70
    # 2  2025  510641.32            641  796.63

    The numerator and the denominator must cover the same period, and the denominator is new customers: people whose first booked order falls in the year. Customers who were already buying were not acquired by this year’s spend.

    Why not paid-only CAC?

    Paid search is $340,778.33 of the 2025 spend. It is tempting to divide it by the customers who came “from ads”. But Harbor & Pine only knows a customer’s channel from a signup question: 47 new customers in 2025 answered google_ads, and 209 left it blank. Divide paid spend by 47 and paid CAC comes out at $7,250.60, which is mostly a measurement of who fills in forms. Channel-level CAC needs proper attribution (chapter 3); until then, blended CAC is the defensible number, labelled as blended.

    What blended CAC hides

    • Spend that is not acquisition. Email goes mostly to existing customers. Including it raises CAC; excluding it requires a judgement you should write down.
    • Organic customers. Friends and search bring customers for free, which lowers blended CAC and makes paid media look cheaper than it is.
    • Timing. Spend in December can bring customers in January. Annual periods soften this; monthly CAC is noisy for this reason.
    • The first year of data. 2023 counts long-time customers who first show up in the data as new, so its CAC is too low.

    Interview note

    When asked how you would calculate CAC, give the formula, then say which spend you included, how you defined a new customer and whether it is blended or per channel. Interviewers listen for the definitions more than the division.

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