Leads

What this replaced

A Google Sheet with 1,486 rows (486 real), where the pipeline state lived in cell colour:

Nothing can query a fill colour. No integration can write one. And the reason a lead died lived in a free-text remark — "brokie", "b2b", "trash lead" — so the useful question ("what kind of lead are we wasting money on?") could not be asked at all.

The split

The sheet keeps facts true at capture time. Name, company, phone, email, budget band, business type, team size, campaign, ad set, ad, platform. Written once, never edited. This is what the post-lead integrations will populate.

The portal owns everything that changes. Stage, pickup, appointment, show-up, deal value, owner, remarks, disqualify reason. Jun Kai was colour-coding because he had nowhere else to put state.

Fixes applied during the import, not inherited

• Services/Team Size held two different things — "B2C" and "1 - 5". Split into business_type and team_size.
• Appointment Date held deal values ("7k++", "4-5k", "8.1k+"). Routed to deal_value_*. Both the parsed cents and the original string are kept: ranges take the lower bound, and the raw text survives so nobody has to trust the parse.
• 12 phone numbers were #ERROR!. Now null, which is honest, rather than a string that sorts like a number.
• Budget band $,000 - $10,000 (27 rows) → $8,000 - $10,000.
• Platform fb/facebook/ig → facebook/instagram.
• Disqualify taxonomy derived from the sixty remarks actually written: no_budget (brokie, startup), wrong_fit (b2b, not suitable), low_quality (invalid, trash lead, repeat leads), poor_prospect, price_shopper, lost_to_competitor. Dated remarks ("After CNY", "EOM") are scheduling, not rejection, and are left alone.

Two honesty rules on the economics page

An untracked month is not a free month. Our own ad account reaches back only to June 2026 while the book starts in December 2025. Those months show untracked, never $0, because a zero would invent a cost per lead of nothing across half the history and make the tracked months look worse by comparison.

Cost per lead divides by the leads the spend produced. The sheet holds 12 leads for June where Meta reports 4, and 88 for July where Meta reports 79 — the extras came from an older ad account the system user cannot read. Both counts are shown and the disagreement is highlighted rather than reconciled away. August agrees exactly, which is the reassurance that the pipeline is sound.

Still to come

• Writing the new sheet format back requires the Sheets API enabled on the GCP project and spreadsheets added to the domain-wide delegation grant. Reading works today through Drive export.
• LTV per closed client, once finance is connected — the join is leads.client_id.