# Revenue Intelligence — data, SOP and playbook design

*As of 2026-09-24 · written for the CTO and the engineering team, in response to the CTO's three observations on the lead-analysis pipeline. Numbers were read from the live `petav3` database on 24 Sep 2026.*

## Summary

The CTO's three observations are right, and the codebase already has more of the machinery than it looks: the analysis pipeline extracts typed fields, and there are three stage/priority systems. What is missing is narrower:

1. a layer that binds what the AI extracts to OUR ids (projects, banks, units);
2. records that force the sales team to capture reasons and loan outcomes as codes;
3. a written playbook that turns a lead's state into the next required step, which the AI then executes rather than invents.

This doc proposes three phases, cheapest first: **A** fix data capture (days), **B** the Next Best Action playbook (the rule table below is for the CTO and the sales team to fill), **C** entity linking of projects and other key facts in every reading.

Decisions needed from the CTO: which "7 stages" the playbook should build on, the fixed list of lost/cancel reasons, and who owns the playbook rules (see *Decisions needed*).

## What exists today

The pipeline is already three layers deep, and every layer stores structured output. The full walkthrough, with live models, prompts and cost per step, is on the site at `/manage/leads/analysis-guide`.

```mermaid
flowchart LR
  P[Profile enrichment] --> S[Summary + 客户档案]
  C1[WhatsApp] --> O[Overall]
  C2[Zoom meetings] --> O
  C3[Webinars] --> O
  C4[Calls / AI calls / Showroom] --> O
  C5[Sales records] --> O
  C6[Portal] --> O
  O --> S
  R[(CRM records)] --> V[Readiness verdict]
  O --> V
```

Each channel is read once into a JSON reading; Overall reads the readings; Summary reads Overall plus the Profile; the readiness verdict is the AI's final state held to the rule that green needs a CRM record.

| What | Where | Structured? | Live numbers |
| --- | --- | --- | --- |
| Channel readings (8 channels + Overall + Summary) | `lead_channel_insights`, one row per lead per channel | Yes: JSON per schema class, with cited line ids | 10 channel ids in use |
| Readiness evidence per area | inside each reading, 20 typed `field_key`s (`need.budget` as min/max/currency, `loan.reported_mode`, `cash.reported_amount`, `property_fit.reported_shortlist`…) | Yes, typed values; **no system ids** by design (prompt: "a specific property record needs a system reference only staff can attach") | — |
| Deals per project | `engagements` (lead × project) | Yes | 539 engagements, all with a project; **17 of 53 projects linked to the catalogue** |
| Lost reasons | `engagements.lost_reason`, required at Lost | Free text | 211 lost; **188 = "imported from legacy, no reason"**; "loan rejected" spelled 4 ways in the other 23 |
| Booking cancel reason | `bookings.cancellation_reason` | Dropped Aug 2026 | NULL on all 204 cancelled bookings at the time |
| Loan facts | `bookings.loan_margin/amount/tenure/rate`; WhoPay report eligibility | Yes, but only the final numbers | no bank / applied / approved / rejected record |
| Properties the lead owns | `lead_properties` (from the Financial Report) | Yes | **0 rows** |
| Lead status | `LeadStages`: Registered → Learning → In contact → Member → Prospect → Booked → Client → Repeat → Loyal (+ Quiet, Dropped) in 5 bands | Rule-based | — |
| Priority call lists | `AttentionFlags`: 14 rule-based flags in 4 groups (money on the table / we owe them / worth a call / check first) | Rule-based | — |
| Advisor journey | 8-stage Advisory Journey (OPEN → PROFILE → PROBLEM → PRINCIPLE → PROOF → PICK → PROCEED → FARM), 13-item scorecard per recorded conversation | AI-scored | — |
| AI Sales Coach | chat on the lead's Discussion tab, grounded in a server snapshot, read-only | Advisory | — |

Transcripts: 144 of 180 Zoom transcripts come from Zoom's own transcription, 25 from Gemini, 11 from Deepgram; phone calls are Deepgram with model + language only (no keyterms).

## Gap analysis

### 1 · Data labelling and entity linking

Extraction is not the gap; binding is. Readings already carry typed evidence, but nothing maps a project name in a transcript (or in a reading) to a `catalog_projects` id, and nothing tags a meeting with the project the advisor was pitching.

- "Sutera" transcribed as "The Era" is a transcription error. 80% of Zoom transcripts come from Zoom's own transcription, whose vocabulary we cannot set, so the fix must happen after transcription: give the reading prompt the catalogue names as facts, and resolve aliases in code.
- Phone calls go through Deepgram with no keyterms; that path can be improved at the source.
- 36 of 53 sales projects are not linked to the catalogue, so even the deals that are structured do not resolve to a clean id.
- The one structured home for owned properties (`lead_properties`) has never been filled.

### 2 · SOP data capture

The fields exist; the data does not, because nothing forces a code. 188 of 211 lost deals carry the legacy placeholder; the rest are free text ("loan rejected", "loan reject", "dsr burst", "only 70% margin"). The booking cancel-reason column was removed because it was never filled. There is no record of a loan application (bank, applied, approved, rejected and why) — only the final margin and amount on the booking.

### 3 · Sales journey playbook

There are stage and priority systems, but no rule that says "in this state, this is the next required step". Overall and Summary produce a next action, but the model chooses it from the conversation; the company's SOP is not an input. The Advisory Journey scores how an advisor ran one conversation; the readiness areas score how ready a customer is. Neither is a lifecycle playbook, and neither drives the AI's next-action field.

## Phase A — data capture (quick wins, about a week)

Four changes that start accumulating clean data immediately and need no AI work.

| # | Change | Where | Why now |
| --- | --- | --- | --- |
| A1 | Link the 36 unlinked sales projects to their catalogue development | `projects.catalog_project_id` (existing pairing UI on the project page) | Every deal, booking and unit then resolves to one id; the playbook's "has a main project" test becomes reliable |
| A2 | Lost / cancel reason as a fixed code + optional note, required on the status change | `Engagement::LOST_REASONS` constant, `LostReasonModal` becomes a select; migrate the 23 free-text reasons by hand | Reasons become countable ("loan rejected" × 4 spellings today); the playbook can react to them (a DSR rejection routes to the banker, a price objection to a cheaper unit) |
| A3 | Loan application record: bank, product, amount applied, approved amount, margin, decision date, outcome code, rejection reason code | new `loan_applications` table under the booking / engagement, written from the Pipeline tab | Today only the final margin survives; the reason a bank said no is the most useful fact for the next sale and is never recorded |
| A4 | Owned properties become part of the Financial Report SOP: the advisor enters each property (type, value, loan, rent) before the report is marked done | `lead_properties` (exists, empty); a required step on the FPA tab | "Current investor's property" as structured rows instead of a webinar poll answer; feeds Portfolio, CLV and the playbook |

Proposed lost / cancel reason codes (for the sales team to confirm): loan rejected — DSR · loan rejected — CCRIS/CTOS · margin too low · instalment too high · price / no budget · family or spouse against · chose another project (which) · chose another agency · went quiet (MIA) · personal circumstances · other (note required).

## Phase B — Next Best Action playbook

The playbook is a rule table computed from CRM records, in code, the same way the 14 Pay Attention flags are computed today. The AI never decides which step is due; it receives the step as a fact and writes it specifically for this person (who to call, which unit, what to say). That keeps the SOP deterministic, auditable and cheap, and it gives the sales team one place to change the strategy without touching a prompt.

```mermaid
flowchart LR
  R[(CRM records:<br/>consent, report, engagements,<br/>webinars, loan, readiness)] --> E[Playbook engine<br/>first matching rule]
  E --> L[Leads list column<br/>+ Summary card]
  E --> O[Overall / Summary prompt<br/>PLAYBOOK STEP DUE]
  O --> A[AI writes the step<br/>concretely, never changes it]
```

How it plugs in:

1. `Src\Lead\Support\Playbook` holds the rules (state test → step, owner role, channel, due-in days) with the same batched-query pattern as `AttentionFlags`, so it costs one query per rule for a page of leads.
2. Each lead gets `playbook_step` cached beside `lead_readiness` and shown as a column in the Leads list and a card on the Summary tab.
3. The Overall and Summary prompts receive `PLAYBOOK STEP DUE: {step, why, owner}` in THREAD FACTS; `next_move` / `next_best_actions[0]` must execute that step. Contradicting it is a prompt violation, caught by the normaliser.
4. Priority order = the table order; the first rule that matches wins. "Nurture" is a rule like any other (e.g. no live conversation in 90 days → send the monthly market note).

Rule table — the four the CTO gave, then blank rows for the sales team. Tests must be things a record can prove.

| # | When (records) | Step due | Owner | Channel | Due in |
| --- | --- | --- | --- | --- | --- |
| 1 | New lead, no signed consent / no Financial Report | Get the Financial Report done | Analyst | WhatsApp | 3 days |
| 2 | Report done, no engagement (no main project) yet | Match inventory, recommend the best-suited project, schedule a 1-1 Zoom | Caller | Phone / WhatsApp | 5 days |
| 3 | Loan eligibility known (report), never attended a webinar | Reach out: invite to the next webinar, warm the relationship | Caller | Phone | 7 days |
| 4 | Loan eligibility known AND attended a webinar | Reach out to ask unit preference (type, floor, budget) and move to a shortlist | Closer | Phone | 3 days |
| 5 | Lost with reason = loan rejected (A2) | Route to a banker review, re-approach with a lower-margin option | Closer | Phone | 14 days |
| 6 | Booked 30+ days, SPA not signed | Chase SPA | Closer | Phone | now |
| 7 | No live conversation in 90 days, still a member | Nurture: monthly market note, ask one question | Analyst | WhatsApp | monthly |
| 8 | | | | | |
| 9 | | | | | |
| 10 | | | | | |

Rows 5–7 are suggestions; 6 already exists as a Pay Attention flag and would move here. The sales team should confirm owners (Analyst / Caller / Closer are the existing Follow Up roles), channels and due days.

## Phase C — entity linking in the readings

The AI names things; the server binds them to our ids. That split keeps the rule the prompts already enforce (no invented system references) while giving analysis clean keys to join on.

| # | Change | How |
| --- | --- | --- |
| C1 | Catalogue names as facts in every reading | The channel prompts receive `CATALOGUE PROJECTS: ["Sutera KLCC", "Exsim CIQ", …]` (the projects this agency sells + this lead's engagements) in THREAD FACTS, with the instruction to normalise a misheard name to the closest listed one and keep the original in `as_said`. This is what corrects "The Era" → Sutera on Zoom transcripts, which we cannot fix at the source. |
| C2 | `projects_discussed` in every channel schema | `[{name, as_said, role: pitched \| asked \| owned \| booked \| rejected, message_id}]`, max 8, quotes verified like readiness evidence. Overall merges them across channels. |
| C3 | Server-side resolver to `catalog_project_id` | `ProjectNameResolver`: exact → alias table (`catalog_project_aliases`, staff-maintained: "The Era" → Sutera) → fuzzy match with a confidence; below the threshold the row is stored unresolved and the Summary shows "which project?" for a person to pick. Never trust an id from the model. |
| C4 | Meeting-level "main project" tag | `zoom_meetings.catalog_project_id` (and the same on calls), set by the advisor when the meeting is filed, pre-filled from C3's top pitched project. The human tag outranks the resolver. |
| C5 | Other key facts to the same standard | Banks (a fixed list) in `loan.reported_*`; units (`bookings.unit` → catalogue floor plan); owned properties from `property_fit.reported_*` proposed into `lead_properties` for staff to confirm. |
| C6 | Deepgram keyterms for phone calls | Pass the catalogue names + advisor names as `keyterm` on the Deepgram request (Nova-3), the only transcription path we control. |

With C2–C3 in place, a question like "how many leads heard the Sutera pitch and did not book" becomes a join on `catalog_project_id`, and the playbook's "no main project" test can also read pitched projects, not only opened deals.

## Decisions needed

- [ ] **Which "7 stages" is the playbook built on?** Candidates in the code: the 7 readiness areas (Need, Relationship, Understanding, Loan, Cash, Decision-maker, Property fit), the 8-stage Advisory Journey (OPEN → FARM), or the lead Status ladder (Registered → Loyal). The proposal above treats the playbook as its own rule table that reads all three, but the naming should match what the team was shown.
- [ ] **Confirm the lost / cancel reason codes** (Phase A, A2) and whether a note is required for "other".
- [ ] **Who owns the playbook rules** — proposed: the CTO with the sales lead; engineering implements a row as a rule, never invents one.
- [ ] **Owners and due days** for rules 1–4, and whether rules 5–7 stay.
- [ ] **Order**: A → B → C as proposed, or C1 (catalogue names in prompts, one day) pulled forward because it fixes the "The Era" class of error immediately.
- [ ] **Alias table ownership** (C3): who maintains "as said → project" mappings when the resolver asks.

Open question: the CTO's example "for those without a main project assigned yet, find our inventory and recommend the best suited project" needs an inventory source the playbook can read — today that is the catalogue plus the sales projects, with no availability data. If unit availability matters for the recommendation, that is a separate feed to define.

## Appendix — where the code lives

Repository `property-lab/petav3`, branch `dev-wk`. The Summary, dossier and pipeline work is commit `479e4139d`; the analysis guide page is on the server and not yet pushed.

| Piece | Location |
| --- | --- |
| Step-by-step guide with live models, prompts and cost | `/manage/leads/analysis-guide` · `Src\Lead\Support\AnalysisPipeline` |
| Every AI reading | `Src\Conversation\ChannelInsightsAnalyzer` (parts, redaction, JSON normalise, routed model) |
| Reading schemas | `Src\Conversation\ChannelInsights`, `WebinarInsights`, `MasterInsights`, `LeadSummary`; `Src\Lead\SalesInsights`, `PortalInsights` |
| Prompts | `resources/prompts/*.md`, registry in `config/ai_prompts.php` |
| Model routing | `config/ai.php` → `channel_insights.ensemble` (one member today: GLM-5.3 Flash via OpenRouter, BaseTen → Modal) |
| Endpoints | `POST /manage/leads/{uuid}/{channel}-insights`, `overall-insights`, `summary-insights`; `GET /manage/leads/{uuid}/record` |
| Stage / priority systems | `Src\Lead\Support\LeadStages`, `AttentionFlags`, `ReadinessColumns` + `ReadinessVerdict` |
| Advisor journey scoring | `resources/prompts/zoom_journey_scoring.md`, `Src\Zoom\ZoomConsultation::STAGES` |
| Stored readings / edits | `lead_channel_insights` (channels 1–10), `lead_dossier_edits`, `lead_enrichments`, `lead_properties`, `ai_requests` |
| Team docs | `docs/modules_handbook/shared/channel-insights/` (`readMe.md`, `summary.md`, `overall.md`) |

Source for the numbers above: the live `petav3` database on 24 Sep 2026 (`engagements`, `projects`, `lead_properties`, `zoom_meetings`, `call_recordings`, `ai_requests`).