Customers, promotions and the gaps

The whole rule is one function in the shared reader, and every retention number in the course comes from it. Three lines carry the norm: the cohort is the first-order period, k counts forward from it, and any cell whose period ends after the last complete period is set to NaN rather than left as a partial count.

Two other shapes get called retention and are not this. A trailing look-back window ("customers active in the last 90 days who were also active in the 90 before") recomputes every cell every day, so history moves under you. A rolling "day N or later" measure counts anyone who came back at any point after day N, so every past cell climbs each morning as late returners arrive. Both are useful for operations and neither is used for reporting, because a reported number has to mean the same thing next quarter as it did when it was signed off. A frozen cell can be put on a wall and it stays true.

Average the +1Q and +2Q cells of each cohort: the share who ordered again in the two quarters after acquisition. Plot it by acquisition quarter, key servers against the rest, completed cohorts only.

Key-server cohorts acquired in 2023 repeated at 28.8 percent on average; cohorts acquired in 2025 at 17.2. Other servers: 27.4 to 26.8, effectively flat. The customers who joined S07 and S08 in the two quarters after the cost decision still repeated at the old rate; the ones who joined from 2024Q3 did not. Whatever changed in the customer's experience took about two quarters to reach the acquisition cohorts, which is why a three-month window in mid-2024 would have shown nothing wrong with retention at all. a2 priced the margin loss; this is the retention loss that sits on top of it.

March 2024 changed fulfilment cost on S07 and S08. June 2024 cut the merchant-success team on the same servers, and a4 showed the merchant hazard nearly doubling. Both could explain customers repeating less. A customer whose merchant closed cannot reorder from it, so if the repeat-rate drop lives only among customers of merchants who later churned, the June cut is the lever. If the drop appears among customers of merchants who are still trading today, the experience itself changed - and that points at March.

29.2 to 17.7 for customers of surviving merchants; 27.4 to 15.5 for customers of merchants that later churned. The drop is in both, and almost identical in size. Merchant loss cannot be the main mechanism, because the customers whose merchants stayed open stopped coming back just as much. Something in the buying experience on S07 and S08 changed for everyone, and the timing fits the fulfilment shift. The June cut then compounded it by letting merchants leave faster. Two decisions, one drop, and the data can say which one is primary. What it cannot say is exactly what the customers experienced - delivery time, quality, price - because none of that is collected, which is a c3 row.

Among customers with a second order, the median gap from first to second is 118 days, and 65.2 percent of second orders arrive within 180 days. Two operational consequences follow. A quarterly cohort grain is the right granularity for this business: a monthly triangle would mostly show zeros at +1M and noise, because the typical customer does not return inside a month. And a store or customer "active in the last 90 days" definition sits just inside the median gap, so a quiet quarter is a real signal rather than a customer between orders.

a3 found customised merchant SKUs earn 8 to 10 margin points more than platform standard goods. The customer-side question is whether the people who buy from high-customisation merchants come back more. Group customers by their merchant's custom_share, apply the same frozen cohort function, and read the peak quarterly repeat rate within the first year for cohorts complete by 2025Q2.

Customers of low-customisation merchants peak at 28.1 percent; of high-customisation merchants at 33.9. Nearly six points, and monotonic through the middle band. Put next to a3's margin finding this is the one result on the platform that points at growth rather than repair: pushing merchants toward their own SKUs earns more per unit and keeps the customer longer. The chairman deck carries it with the same weight as the breaks, because a room that hears only what went wrong walks out defensive.

The customer dimension carries acq_channel for all five years, so promo-acquired customers can be compared with organic, referral and paid-social ones for the whole history. Two questions per channel: how big was the first basket, and did the customer ever order again. Customers acquired 2020-2024 only, so every one has had at least a year and a half to come back.

Promo-acquired customers: first basket 91.8 against 84.7 organic, ever-repeat 63.4 percent against 71.5. Referral and paid-social sit with organic on both. A promotion that buys a seven-unit larger first basket and loses eight points of repeat is buying baskets, not customers, and the c3 promo policy follows: judge acquisition promotions on the second order.

The finer question, which promotion type, can only be asked from 2025. promo_id has been stamped on order lines since 2020 but dim_promotion starts in January 2025, so every earlier id is an orphan. The comparison by type is run on the joinable window only, and the left panel shows the join coverage by year so nobody reads the right panel as five years of evidence.

fact_login runs from 2026-07-01 to 2026-09-07: 69 days. It can show a funnel. It cannot show a trend, a season, or anything about 2024, and the chart title says exactly that, in the chart, so the caveat travels with the picture when it is pasted into a deck without its paragraph.

Both server groups view on 99 percent of logins and add to cart on 45 percent. Then S07 and S08 convert 16.8 percent of logins to purchase against 26.9 elsewhere. The gap opens after the cart, which is where price, shipping cost and delivery promise appear - consistent with the cost story, and not proof of it, because 69 days of one summer cannot say what the funnel looked like before March 2024. The recommendation is not "fix the cart on S07"; it is "keep login history, so that in a year this chart has a before".

Monthly active identified customers decompose exactly: actives this month = new (first order ever) + retained (active last month too) + resurrected (active before, not last month). Churned, drawn below the axis, is everyone active last month who is not active now. The identity is arithmetic, so the four bars always sum to the active line, and a monthly review that opens with it cannot mistake a resurrection wave for growth or a churn wave for seasonality.

Guest lines are not in this chart, and the share of them has grown from 6.3 percent in 2020 to 11.9 in 2026. That is a growing slice of real orders with no customer behind them in any retention figure. Every customer chart in the course says "identified customers" in its title for that reason, and the guest identity gap is a c3 row with an owner.

The export is a dict built up cell by cell as each result is computed, then serialised once with json.dumps. Never build the file as a string: a stray comma or an unescaped quote produces a file that parses on Tuesday and not on Wednesday, and the page that reads it fails silently. Cast to plain float and int first, because numpy scalars do not serialise.

The fields, grouped by which page quotes them:

Five things the chairman would reasonably ask that no table in the company can answer, each already met somewhere in a1 to a5. None is dropped. Each becomes a recommendation in c3 with an owner, a first review date and the question it would unlock.

Read the table as ownership gaps before technology gaps. The promotion table started when a module launched because nobody owned it as a product; the event log does not exist because writing down what changed is nobody's job. c3 assigns the job.