journey analyst

A fifteen-person customer-journey research group had six-plus years of monthly site-analytics exports for lexus.com — tens of thousands of rows — and no time to pivot them by hand. API access to the analytics platform had just been declined, so manual exports were the permanent feeding mechanism. The evening before, a colleague had shipped the team's first conversational prototype as a consumer-platform custom assistant: it proved the idea and the platform throttled it — knowledge files capped at 250 rows, lossy spreadsheet conversion, a nine-file manual refresh ritual, a share link instead of company infrastructure.

Same thread, same day: a leader asked the group to pause AI components of the research pending governance review. That is the real context for what got built — a governed, internal, auditable tool was the answer to a governance concern, not just a capacity one.

The obvious ask was “make the assistant bigger.” The reframe was to stop letting the model touch the numbers at all. Python and SQL are the calculator; the model is the strategist-translator; the human is the final checkpoint. The one rule — no number reaches the user unless it came out of a tool result — is enforced in layers rather than prompted: a read-only database role, a statement validator, the prompt contract, and an independent critic from a different vendor. The prototype's workarounds (row shrinking, loading hacks, re-upload rituals) were declared obsolete and deliberately not ported; the app's reason to exist is that they're gone. And the product refuses whole classes of question the monthly aggregate data cannot support — user-level sequences, cohort retention, causal claims about a rollout — because refusal is a feature, and the critic passes refusals.

The role is the security boundary; the validator is defense in depth. The SQL tool authenticates as a dedicated Postgres role with SELECT on one schema and read-only transactions by default, its own small pool, a 15-second statement timeout, and a 5,000-row cap. On top: strip strings and comments, require a single statement starting with SELECT or WITH, reject a long list of forbidden keywords (including ones hidden in string literals). Every result carries provenance — tables, SQL, the stated period, the cap, the data level — persisted with the message.

Two models, and the critic can't compute. One vendor's model runs the function-calling loop; a different vendor's model audits every analytical answer against a nine-rule prohibited-claims contract — after a deterministic numeric pre-scan that requires every figure in the draft to match a tool value as-is, scaled by 100, rounded at the answer's own precision, or as a difference of two tool values. One automatic revision cycle; persistent failure ships with a visible banner; a critic outage ships the answer flagged, never blocked. A “second opinion” button sends the question, the answer, and the computed tool results — no database access — to the other model for interpretation, not recomputation. The chart tool can only draw rows the model already received; it never queries.

The registry is the contract. A describe_datasets tool serves the dataset registry from the database — grain, coverage, filters, formulas, limitations, partial-year flags, expected versus actual rows — and the prompt tells the model to call it first and trust it over memory, so schema knowledge can't drift as data versions publish.

Cross-fenced lanes. When the media data lake arrived as a second lane, the journey SQL tool and the media SQL tool were built to reject each other's tables; the only sanctioned join is one published comparison view. Attribution math is structurally impossible, not just prompted away. Web context comes from a 21-domain allowlist enforced server-side — results off the list are dropped before the model sees them — and renders only in a labeled external block.

Upload raw analytics workbooks → hash and auto-detect the report type → derive a dozen tables in pandas, golden-pinned against the prior generation's extracts computed from the same raw files (counts exact, rates to four decimals) → stage in a per-run schema → a validation report: non-empty checks, month continuity, coverage window, and a calibrated single-month spike scan tuned to catch a known tracking anomaly without flagging seasonal ramps → an explicit publish that runs as one transaction: swap the live tables, update the registry, re-grant the read-only role. Readers see old or new, never half. Rollback is publishing an older validated run; staged schemas are retained for exactly that.

The quiet engineering: taxonomy renames across the years are handled by a config map with raw and canonical names both stored; zeros versus not-tracked are distinguished by per-section active ranges; in-progress years are flagged partial; the two pathing reports that disagree by a median of a few percent are reconciled in a citable table with one declared canonical. And the database password is resolved live from the secrets store at connect time with a short cache and invalidation on auth errors — the weekly rotation that bit the rest of the fleet was pre-empted here in code.

Built from the PRD through all five milestones in one day — eight commits between 10:43 and 15:07, about ten thousand lines, deployed to production the same afternoon — and still answering ten weeks later with zero further commits. The speed is real but it stood on a specified analytical contract: the formulas and the prohibited-claims rules pre-existed as the normative spec; the morning was spent writing the PRD. The prototype's mechanism was replaced outright: full-fidelity tables (the transitions table alone runs to ~139k rows) instead of 250-row files; refresh is an upload and a click.

The one downstream outcome the repo can verify: the same day, a separate lexus.com GEO recommendations package states its engagement figures were computed deterministically from the Journey Analyst pipeline tables, re-verified against source with exact matches — and inherits the analytical guardrails verbatim. The pipeline and the contract became reusable infrastructure within hours. Adoption by the research group itself isn't something the repo can claim, and the governance review it was built to satisfy is a human process; this page says neither more nor less.