7. Lighthouse

Level 3 · Reading a retention curve

From Priya Shah, Head of Data

“The board pack needs one retention chart instead of a wall of cohort numbers. Give me the share of customers who are still buying 1, 2, ... 8 quarters after their first order, and make sure the young cohorts don't distort it.”

Your goal

Booked orders have status "completed" or "refunded". For each customer, quarter 0 is the quarter of their first booked order; quarter q is q quarters later. A customer is active in quarter q if they have a booked order in it. Set result to a DataFrame with one row for each q from 1 to 8 and columns quarter (q), cohorts (how many cohort quarters have reached q by 2025Q4), customers (customers in those cohorts) and retention_pct (active customers in quarter q × 100 / customers, rounded to 1), sorted by quarter, default index.

  • Use the customers DataFrame
  • Use the orders DataFrame
  • Show exactly quarter cohorts customers retention_pct
Share of customers active, by quarters since first order
Share of customers active, by quarters since first order0%10%20%30%40%50%Cohorts that reached it1, Cohorts that reached it: 44.9%2, Cohorts that reached it: 46%3, Cohorts that reached it: 42.5%4, Cohorts that reached it: 40.9%5, Cohorts that reached it: 39.7%6, Cohorts that reached it: 41%7, Cohorts that reached it: 40.2%8, Cohorts that reached it: 36.2%Every customer (biased)1, Every customer (biased): 38.3%2, Every customer (biased): 35.5%3, Every customer (biased): 29.4%4, Every customer (biased): 25.9%5, Every customer (biased): 20.3%6, Every customer (biased): 18.4%7, Every customer (biased): 15.6%8, Every customer (biased): 12%Cohorts that reached itEvery customer (biased)12345678Quarters since first orderCustomers active (%)Cohorts that reached itEvery customer (biased)
Share of customers active, by quarters since first order0%10%20%30%40%50%Cohorts that reached it1, Cohorts that reached it: 44.9%2, Cohorts that reached it: 46%3, Cohorts that reached it: 42.5%4, Cohorts that reached it: 40.9%5, Cohorts that reached it: 39.7%6, Cohorts that reached it: 41%7, Cohorts that reached it: 40.2%8, Cohorts that reached it: 36.2%Every customer (biased)1, Every customer (biased): 38.3%2, Every customer (biased): 35.5%3, Every customer (biased): 29.4%4, Every customer (biased): 25.9%5, Every customer (biased): 20.3%6, Every customer (biased): 18.4%7, Every customer (biased): 15.6%8, Every customer (biased): 12%12345678Quarters since first orderCustomers active (%)Cohorts that reached itEvery customer (biased)

The lower line divides by every customer, including those whose quarter q has not happened yet.

Show the numbers
Quarters since first orderCohorts that reached itEvery customer (biased)
144.9%38.3%
246%35.5%
342.5%29.4%
440.9%25.9%
539.7%20.3%
641%18.4%
740.2%15.6%
836.2%12%

Your code

Python

Loading the editor…

    ResultPreview of customers · press Run to see yours
    customer_idfirst_namelast_nameemailcity
    1OwenHudsonowen.hudson@icloud.comSan Francisco
    2DouglasMendozadmendoza69@gmail.comAtlanta
    3HenryBrookshbrooks@lakesidecafe.comLos Angeles
    4BrianGrahambgraham@bluecedarhomes.comSeattle

    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.

    customers2,018 rows · 9 columns
    • customer_idinteger
    • first_namevarchar
    • last_namevarchar
    • emailvarchar
    • cityvarchar
    • statevarchar
    • signup_datedate
    • segmentvarchar
    • referral_sourcevarchar
    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
    orders12,000 rows · 7 columns
    • order_idinteger
    • customer_idinteger
    • order_datetimestamp
    • statusvarchar
    • channelvarchar
    • shipping_feedecimal
    • coupon_codevarchar
    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
    products120 rows · 8 columns
    • product_idinteger
    • namevarchar
    • categoryvarchar
    • subcategoryvarchar
    • unit_pricedecimal
    • unit_costdecimal
    • launched_ondate
    • is_activeboolean
    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
    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
    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
    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
    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
    spend_datechannelamount
    2023-01-01paid492.99
    2023-01-01email37.38
    2023-01-01social200.53
    2023-01-02paid709.13
    2023-01-02email64.13

    The lesson

    From a table to a curve

    A cohort table has one row per cohort and one column per period, which is too much for a board slide. A retention curve squeezes it into one line: for each number of quarters since the first order, what share of customers placed an order in that quarter?

    Quarter 0 is the first order itself, so it is always 100% and left off. Quarter 1 is the next quarter, quarter 2 the one after, and so on. A customer counts as active in a quarter if they have at least one booked order in it. They do not have to be active in every quarter in between, which is why the curve can rise from quarter 1 to quarter 2.

    Quarter numbers make the arithmetic easy

    Number the quarters in order, 2023Q1 = 0 up to 2025Q4 = 11:

    qi = (booked["order_date"].dt.year - 2023) * 4 + booked["order_date"].dt.quarter - 1

    A customer’s cohort is their smallest qi; how far each order is from it is plain subtraction:

    first = b.groupby("customer_id")["qi"].min().rename("c")
b = b.join(first, on="customer_id")
b["q"] = b["qi"] - b["c"]

    Right-censoring: only count who could have come back

    The 2025Q4 cohort has no quarter 1 yet. The 2025Q1 cohort has quarters 1 to 3 and nothing after. If you divide quarter-8 activity by every customer, those young customers sit in the denominator with no chance of being in the numerator, and the curve bends down for a reason that has nothing to do with customers leaving.

    The fix: for quarter q, the denominator is only the customers whose cohort is at least q quarters before the last quarter in the data:

    fair = first[first + q <= last_q]

    The chart shows both versions. The honest line flattens out near 40%. The biased line falls almost in a straight line, and every point of that fall is the calendar running out.

    Pooled, not averaged

    This curve pools customers: active customers across all fair cohorts divided by all customers in them. Averaging each cohort’s rate instead gives the 97-customer 2023Q3 cohort the same weight as the 181-customer 2023Q4 one. Both are defensible, but say which one you used, and the two will not match.

    Common mistakes

    • Every customer in the denominator. The curve sinks because time ran out, not customers.
    • Forgetting how thin the tail is. Quarter 8 rests on four cohorts. Show the base.
    • Reading the first cohort as new customers. 2023Q1 holds people who were regulars before the data begins, and it is in every point of the curve.

    Cookie settings

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