CLV Opportunity Studio: how it all connects

Section one

The one-page map

Two lanes and one bottleneck. Everything upstream of the canonical tables is run by a named operator from a terminal. Everything downstream is automatic and reads through a single assembly function. Nothing crosses that line by accident, because no route under apps/web imports the import, mapping or derivation modules, and a verifier pins that at source level.

Section two

The data journey

Three staged commands, three separate approvals, and a ledger in the middle that has to balance to zero. Approving a live raw import is not approving mapping, and approving mapping is not approving the numbers a client is about to read.

Stage one: what the raw import writes

The command names a client id, a shop domain, the families to fetch and an order window. It writes one raw table per family and one batch row per family, and it reports per family the batch id, status and requested, imported, failed and skipped counts, plus pages read and Shopify request counts so a run truncated against the page cap shows itself.

Preflight is the part that stops the mistakes. It checks that the client row exists, that exactly one store row carries the requested .myshopify.com domain, that the connection is recorded as connected with a verified credential, and that the credential reference resolves. The order window is required for the customers and orders families, and a window wider than 31 days needs --wide-window-approved on top, because a first run uses the smallest window that still proves the path.

--notes="..." records a short operator note against the run: the primary batch in stage one, the mapping record in stage two, the run record in stage three. Use it to name the call or the approval the run belongs to, so the record in the database says why a run happened and not only that it did.

Stage two: what the ledger proves

The mapping plan prints the review ledger, and the ledger is the honesty mechanism of the whole pipeline. Read it in this order.

1. Unaccounted rows must be zero. Unaccounted is rows read that the ledger cannot place in any other column: not mapped, not written, not skipped for a stated reason, not orphaned. Zero means every raw row is accounted for by name. Anything other than zero is an unexplained gap, and the tool exits nonzero on it. Do not apply on a nonzero unaccounted count.
2. Skips, with the mapper's own reason. Every skip carries the reason the mapper gave rather than a reason inferred afterwards. Read each reason out loud and decide whether it is expected for this store.
3. Orphans. One orphan is one unresolved child-to-parent link, so an order line whose product and whose order both fail to resolve counts twice. On a seven-day order window some orphans are expected, because a customer or product created before the window was never imported.
4. The six link-quality counts. These are what Data Health later reports as customer and product link quality.
5. The revenue tie-out. Orders, gross revenue, discounts and refunds, raw against canonical, with the difference. This should tie exactly, because both sides are the same rows counted twice. A difference here is a mapping fault, not a boundary effect.
6. The issue codes. Any code present goes into the Issues table with a severity.

--mapping-approved is you stating that the raw counts have been compared against Shopify and that mapping is approved for this client and this store. Without it, --apply refuses.

Stage three: what derivation builds

Derivation turns canonical rows into every derived snapshot table: the weekly scoreboard series, the LTV driver rows behind the best-and-worst money read, the cohort tables and the rest. --as-of is the deterministic anchor and defaults to today; set it explicitly so a re-run later in the week reproduces the same numbers. --tables=<list> narrows the run; a first run uses all of them, in write order.

It reports per table the input rows, rows built, written, inserted, updated and skipped with a reason, then the weekly-series decision, then seven story invariants with pass or miss and the measured value, then the run summary.

The weekly-series bootstrap rule

This is the one rule in the pipeline most likely to catch you out on a call, so it is worth stating precisely.

The rule

The derivation job seeds metric_weekly_snapshots only for a client that has no weekly rows at all, which is the state straight after a first import. Every later run reports the series as left alone, because the Monday cron owns the live series and two writers with two definitions produce a scoreboard that contradicts itself week to week.

The trap: a client can hold weekly rows that carry no information. Wisdom Foods holds five metric_weekly_snapshots rows written by the Monday cron on 2026-07-18, all with a null value, verdict needs_data and sample size zero, at snapshot date 2026-07-13. While those five rows exist the bootstrap skips, the historical weekly series is never written, and the client's scoreboard trend stays empty even though the canonical data behind it is real.

The recommendation on the call is to delete exactly those five rows by id, scoped to the client, so the bootstrap sees a client with no weekly rows and writes the true series. The client_id predicate stays even though the ids are unique, five ids and no more, and the expected result is exactly five rows removed. If the count comes back as anything other than five, stop and review rather than run it again. Record in the approval record whether it was approved, who approved it, the five ids and the row count removed.

If you decline, stage three still runs and the weekly series is reported as left alone. That is a caveat to carry into the client-facing gate, not a failure.

Where the Wisdom Foods first run actually stands

Nothing has been imported. The call sheet is written and copy-pasteable, and section A is fully open. These are the blockers, and the first four make stage one refuse.

The token is the one item that is not yours: it comes from Marie. Everything else on that list is a hand you have not yet put on the keyboard. Two independent things are outstanding at once, so do not treat "waiting on Marie" as the only reason the first run has not happened.

Section three

One truth by construction

The email, the scoreboard, the moves board, the monthly report and the analyst cannot disagree, and the reason is architectural rather than editorial. There is one place each shared thing is decided, and a verifier that fails the build if a surface starts deciding it privately.

The verifier that holds it: verify-digest-parity

scripts/verify-digest-parity.mjs is the one to name in a room. It pins every consumer of the weekly context to that module at source level and checks the ordering, so a future edit that quietly builds its own ranked list fails the run rather than shipping a fork.

Behind it sit 143 verifier scripts under scripts/verify-*.mjs, run by scripts/run-verifiers.mjs through npm run verify. They are not unit tests of arithmetic. A large share of them pin copy strings and source-level structure: that a rule sentence is generated and not duplicated, that a banned word appears in no client-facing string, that a surface imports the canonical module rather than querying directly. That is why a copy change is an engineering change here.

Section four

Every rule, exactly

These are the certified wordings. Where a sentence is quoted here it is the sentence in the code and in the client copy, and changing it means changing a generator and a verifier. Learn the derivations underneath, because the derivation is what survives the follow-up question.

1. The band rule

Certified wording

"On track means within {t}% of target. Watch means {t}% to {2t}% off. Off track means more than {2t}% off."

"Ahead means more than {t}% past target."

Default t is 5, so the bands are 5 and 10, not 5 and 15. Per-target tolerance is possible: a client can set a wider band on one target, and when bands differ a mixed-bands clause renders, saying that the wider target reads against its own band the same way. The sentence quotes the tightest band in force.

Five pills exist: ON TRACK, AHEAD, WATCH, OFF TRACK, NEEDS DATA. The sentence is generated by bandRuleSentence(tolerance) from the tolerance actually in force, never hardcoded, and it states all four verdict words because stating only the on-track half left the other three undefined on the surfaces that hand those words out. A hero line can take the first clause alone via onTrackOnly, off the same tolerance, so the short line and the fold can never disagree.

It is stated on-page in the "How these numbers are computed" folds and the tile folds, and it is readable in the fold on touch devices, which is the accessibility requirement that stopped the rule living only in a hover.

2. The two-family overlap haircut

Certified wording, pinned in code and in a verifier

"The 25 percent overlap haircut applies to a roll-up if and only if two or more of its plays sit in the same overlap family, either the monthly intake family (second-bottle-countdown, reorder-reminder, running-late, winback-window, rescue-list) or the prospecting budget family (budget-shift, spend-pullback); otherwise the roll-up is the plain sum."

Every roll-up's prose branches on haircutApplied, and there are exactly two branches:

Haircut branch

"These plays work the same customers, so the combined figure carries a 25 percent haircut."

Plain-sum branch

"These moves work separate audiences, so the combined figure is a plain sum."

Page-local noun variants exist and are deliberate: "the total" on retention and acquisition, "the combined range" on moves. Every combined dollar carries the "modelled, before testing" chip.

Why it matters: before this rule the haircut applied to every roll-up, so a page showing one reminder play beside a founder-owned discount kill quietly shaved 25 percent off two figures that share no customer at all. A play in no family is additive. Two plays in different families are additive too. The function returns haircutApplied and overlapFamilyCount so page prose can stop claiming a haircut that did not happen.

3. Move of the week: the selection order

Certified wording

"Worst off-track measure by ANNUAL dollar gap picks a matched play from the ranked list; if none matches, the highest-value ready move leads. A Watch measure takes the headline ONLY in a week where nothing at all is off track."

Read as the code runs it:

1. Take the measurable metrics, meaning those with both a value and a target.
2. Among those off track, sort by annual dollar gap descending and take the first. Only if that set is empty, do the same among those on watch.
3. For that headline metric, walk its preference list of play keys in order and take the first play present in the ranked list. That is the move of the week, and matchesMetric is true.
4. If nothing in the preference list is present, the highest-value ready move leads and matchesMetric is false. A fallback move's dollars must never be quoted as the price of fixing the metric.

All five dollar gaps are on one annual basis. This ranking was previously distorted by mixed bases and is fixed. The ranked list itself is buildTopMoves: lifecycle plays plus acquisition plays, in-flight keys excluded, sorted by the high end of the dollar range, cut at five.

4. The cohort projection sentence, and the phrase that was banned

Certified wording, locked across six surfaces

"Twelve months of intake at that cohort's rate is worth about {$X}."

Revenue fold

"Held for a year, that drop is about {$X}."

Timing fold

"...worth about {$X} a year, modelled on the reminder flow before testing."

Never "of that gap." The reason is arithmetic, not taste. The figure is a projection: it takes the worst cohort's rate and asks what twelve months of intake at that rate would be worth. It is not a share of anything. The worst cohort's rate multiplied by twelve months of intake can exceed the whole annual gap, because the gap is computed against the blended rate across all cohorts while the projection runs the worst rate across a full year of volume. A sentence saying "of that gap" would then claim a share above 100 percent, and a CEO who checks the arithmetic finds it in about ten seconds.

Say it as a projection out loud too: "if every month looked like your worst cohort, a year of intake at that rate is worth about this much." That is defensible. "That cohort is costing you X percent of the gap" is not.

5. The holdout, and the difference in differences

The brief specifies a 10 percent holdout. This is real measurement, not a label.

When a holdout was run and recorded

Badge: vs holdout. Note: "Read against the holdout the brief specified."

When no holdout was recorded

Badge: vs baseline. Note: "Movement is observed alongside the play, not proven by it."

Those two strings are the only two, held in one module as READOUT_NOTE_HOLDOUT and READOUT_NOTE_OBSERVED, selected by one function, readoutNoteFor(holdoutMeasured). The board, Data Health and the monthly report cannot phrase the same readout differently.

The mechanics, stated exactly:

• A readout is holdout-measured only when both holdout points are readable, the held-back group's value at the play's baseline point and at the latest point. A holdout_pct on the row says a holdout was specified; only the two figures let anything claim the movement was read against it.
• When both are readable, observedDelta becomes the difference of the two movements, treated minus holdout. The held-back group's movement is subtracted before any verdict is formed.
• A dead-even readout therefore reads flat. If the market moved and the holdout moved with it, the play gets no credit for it.
• Every surface reads observedDelta, so all of them inherit the subtraction by construction. The win verdict and the app's single celebration are judged on the subtracted figure, through the same steady-movement rule the scoreboard uses.

6. The decline path

Certified wording

Menu item: "Not this week". Confirm: "Take this move off the list? Next Monday brings the next-highest move instead."

Dated notice: "{title} is off the list until {date}; next Monday brings the next-highest move."

A declined move leaves the board, a visible dated notice renders, and the play is excluded from the Monday ranking for 90 days. A no is a decision, not a forever, and 90 days matches the readout cadence, so the quarterly re-look puts the move back in front of the person who passed on it once the numbers behind it have moved. Nothing re-asks a question that was answered last Monday.

Drag can never decline. Only the menu can. A destructive decision needs an explicit confirm, and a drag gesture is not an explicit confirm.

7. The reorder clock

This is per client, not a house constant. The product reads the client's own replenishment_cycle_days when it is set, and defaults to 30.

The strip states which zone the median lands in, in text and in the aria label, not by colour alone.

8. The materiality floor

15 percent. A best-or-worst money gap only moves budget once it clears 15 percent of the baseline; below that the honest verdict is hold. It is one exported constant, MATERIALITY_FLOOR = 0.15, shared between the play builders and the acquisition surface so a play and a row can never disagree on materiality. A gap that fails the floor prices at nothing and never ranks.

9. The sample floor

n at least 25. HEADLINE_SAMPLE_FLOOR = 25. A cohort or segment below it cannot carry a headline; it renders as NEEDS DATA or as a limited-evidence row. This is also a hard gate in the CEO audit: a currency or numeric render that can show an n under 25 as a headline is a fail. On a seven-day first import most things will sit under the floor, and the app saying NEEDS DATA is it working, not it broken.

10. Margin versus revenue labelling

The two bases are never mixed silently and never unlabelled. The canonical read reports its own basis, and the phrasing follows it.

• Default gross margin is 0.65 where a margin figure is modelled.
• Play dollars and the cohort bleed read "in margin", or "in margin after product costs" in longer form.
• Cohort realised value and the acquisition money rows read "in revenue before costs", and the acquisition copy says so explicitly when the panel above it is a margin figure: the money rows read in revenue before costs, so they run higher.
• When any cohort in the blend is read in revenue, the whole blend becomes a revenue figure and the label changes with it. The two figures differ by design, and any surface that shows both says which is which.

11. The price-freeze rule

The rule, as the code states it

"One priced-play truth on the board: a card shows the figure stored when the play was briefed, and only switches to today's pricing when the two disagree beyond rounding, saying so in the note."

The note is one of exactly two strings: "priced when briefed 14 July 2026" with the real date, or "repriced against this week's numbers". Without the freeze, a briefed play's number drifts every week as the inputs move, and a client who screenshotted the brief finds a different figure on the board. With it, the number a brief was approved at stays the number, and a genuine change announces itself.

12. One clock per surface

Each artefact carries exactly the time anchors it needs and no more. The monthly report carries two: the report month and one data-read line. A closed month that predates snapshot history says "Rebuilt from current data on 25 July 2026" rather than implying it was captured at the time. Every date a report shows is human-formatted, never a raw timestamp. The export adds only the generation date and the freshness dates, nothing else. Day 33 is the single reorder target named in the retention hero story, and the second-purchase tile marks its twin dollars as the same gap read as a rate rather than as a second opportunity.

Section five

Product versus promise

The section that keeps you honest in the room. The left column is enforced by arithmetic and by verifiers: it happens whether or not anyone remembers. The right column is Click Catalyst's word, and it happens because people do it. Never present a right-column item as a left-column guarantee.

Section six

The client conversation

The platform answers three questions and only three: am I on track or off track, why, and what do I do this week, worth how much, done by whom. Run the demo in that order and the software does the work.

Where to start a demo

Open on /home and stay on the verdict card for a full thirty seconds before scrolling. Do not narrate the page. Let them read the pill and the dollar figure, then ask "does that match what you thought?" Whatever they answer, you are now in a conversation about their business rather than a software tour.

1. Scoreboard verdict card. One verdict, one dollar figure. Open one why-fold, not five: the priced driver behind the worst tile.
2. This week's moves. The ranked board with the owner split. Point at the split, not the board: "two of these are ours, one is your paid team's, one is yours."
3. The one decision. Ask for exactly one yes. Then stop.
4. Only if asked: /retention for the cohort curves, /acquisition for payback, /reports/monthly for the paper version.

The one decision ritual

Every surface asks for exactly one yes, and it is the same yes on all of them: the Monday email's top block, the scoreboard hero, the moves board headline and the monthly report's single decision all name the same move, because they all read the same selectMoveOfTheWeek result. The ritual is:

1. Name the move, its dollar range and its owner in one sentence.
2. Ask for the yes.
3. On a yes, the brief goes out the same day and the play leaves next Monday's ranking.
4. On a no, use the menu, not the drag. "Not this week" is a real decision with a real 90-day consequence, and the client should see the dated notice appear.
5. Never ask for a second yes in the same conversation. A second ask turns a decision into a shopping list.

Answering the hard questions

The nav labels and page headings, so you never misname a page

Two things to hold in your head. The nav label and the heading differ on three of the six, so say the nav label when you are telling someone where to click and the heading when you are quoting what they will see. And Settings is not in the nav: it is reached from the "Connect ad spend in Settings" buttons and from tile links. Do not tell a client to click Settings in the sidebar.

Section seven

The operating calendar

The client's week is 3.5 minutes long. Everything else is yours.

The client's week

Total weekly ask: 3.5 minutes. That number is a design constraint, not a marketing line. Any change that adds a second decision or a second caveat sentence to a section is breaking it.

The agency's week and quarter

The standing engineering gates

Any future copy change, however small, clears all three of these before it ships. This is the part that is easy to forget when a client asks for one word changed on a call.

Section eight

Where everything lives

Repository paths, so nothing has to be searched for mid-call. All paths are relative to /Users/benjamin/Projects/clv-opportunity-studio.

Runbooks and the first run

The three operator tools

The code that holds the rules

Awaiting your hand

Skills and audits

Background context the two audit personas point at: docs/ceo-critique-2026-07-17.md, docs/master-plan/03-retention-operating-system.md (section 6 is the voice guide, section 1 the play library, section 3 the good bands, section 4 the dollar models) and docs/master-plan/02-interaction-catalogue.md (the motion system and copy law). The build history, including the incidents each rule came from, is in docs/build-log.md and the numbered docs/ceo-critique-*.md passes.

The two documents in your hands

No links out of this page. Every path above is a path in the repository or in this session's scratchpad, and the walkthrough's published URL is the one you already have rather than one guessed at here.