# APAC PQL — the whole mechanism, and what the data says about the four open asks **Written 2026-08-26.** Trigger: the APAC monthly PLG/PQL review thread (Yan's 2026-07-27 → 08-26 summary) and Heidi's question in it — *"现在 APAC 这边整体的筛选和计算，并不都在 lambda task 里，还有 一部分在 Yan 的 agent 是不是？我想看到现在整体机制的全貌。"* Answer: **yes, and that is the single most important fact about APAC today.** APAC's PQL is two systems, owned by two people, with a hand-off that has no write-back. This file draws the whole picture, then tests each proposal from the thread against the live database. Every number below is from direct read-only query against APAC `regional_support` (`c5P1VZxiR27eA4p`, ap-southeast-1) on 2026-08-26, not from this repo's own docs. Queries are inlined so anyone can re-run them. --- ## 1. The full picture — two layers, one hand-off, no return path ``` ┌─ LAYER 1 — scoring ───────────────────────────────────────────────────────┐ │ pingcap-support/apac_lambda_tasks (owner: Yujie · AWS SAM, us-west-2) │ │ Dispatcher → SQS → Worker → SQS → Scoring │ │ writes: apac_active_tenants (score, priority, fit, qps, clusters) │ │ apac_tenant_score_snapshots (daily, derived score only) │ │ reads : DevOps / Billing / Clinic / HubSpot target list 19544 │ │ writes out: HubSpot Contact `apac_pql_priority_tier` ← live since 07-21 │ └───────────────────────────┬───────────────────────────────────────────────┘ │ SELECT-only (c5P1VZxiR27eA4p.readonly_user) ┌───────────────────────────▼─ LAYER 2 — operating ─────────────────────────┐ │ Yan's agent (yybaroq/plg-notification + Feishu Anycross + Cowork) │ │ re-ranks, filters, dedupes, narrates → daily Feishu digest cards │ │ its own rules, NOT in the official spec: │ │ · 🆕 new-tenant channel — 72h window + ICP filter, BYPASSES the score │ │ · 🧟 dormancy marker + one-line compression │ │ · blacklist (Bytek, 07-31) │ │ · R22 / R22b — read SDR follow-up from NexusCRM, dedupe handled cards │ │ · account-relationship routing (net-new / win-back / open-opp / SI) │ └───────────────────────────┬───────────────────────────────────────────────┘ │ digest → SDR ┌───────────────────────────▼───────────────────────────────────────────────┐ │ SDR acts, then edits **Lead Status in NexusCRM by hand** │ │ + a Feishu 多维表格 总表 that auto-nudges SDRs │ └───────────────────────────┬───────────────────────────────────────────────┘ │ R22 reads it back into Layer 2 only. ✗ Nothing returns to Layer 1's database. ``` **Direct answers to the questions asked in the thread:** | Question | Answer | |---|---| | Is the filtering/scoring all in the Lambda? | No. Scoring is; **prioritisation as SDRs experience it is not.** The 🆕 channel, dormancy compression, blacklist and dedupe are Layer 2 rules that exist only in Yan's agent. | | Where is follow-up write-back written? | **NexusCRM Lead Status, by hand, by the SDR.** Plus the Feishu 总表. | | Is there a timestamp? | Only whatever NexusCRM stamps on the record. Not in our dataset. | | Is the dataset reading it? | **No.** `apac_active_tenants.track_step` = `0` for all 7,788 rows and `track_update_time` is `NULL` for all 7,788 — never written since the table was created. The column exists; the analyst credential is `GRANT SELECT` only, so no one on the GTM side *can* write it. | | So is follow-up state visible in the data? | Not in **our** data. It is fully visible in NexusCRM, and readable from there (below). What is blind is any PQL→SQL measurement done from the scoring database alone. | | How do I read NexusCRM? | **Solved 2026-08-26 — read access works and needs no grant.** `node ~/.claude/skills/nexuscrm-connect/scripts/nexus_connect.mjs auth`, then `… query ""`; it runs read-only **as Heidi's own account**, so it returns what she sees in the UI. Proven at scale: 2,620 Leads / 72 Accounts / 229 Opportunities pulled for the whole P0+P1 queue ([audit](apac-pql-crm-signal-audit-2026-08-26.md)). Limits are per-record, not permission: SOQL `LIMIT 200`, no `LAST_N_DAYS`, ~24h token. What is still missing is a *marker field* for PQL-sourced leads and a saved list view — not access. | ```sql SELECT track_step, COUNT(*), MAX(track_update_time) FROM apac_active_tenants GROUP BY track_step; -- 0 | 7788 | NULL ``` **Read this the right way.** Follow-up *is* tracked, and as of 2026-08-26 we can read it — SDRs write Lead Status into NexusCRM by hand, and the read-only OAuth app returns it. The gap is not visibility for a human; it is that **the state never returns to the scoring database**, so no rule can consume it and no query can aggregate it. Never say "we cannot read APAC follow-up" — say "APAC follow-up lives in NexusCRM and does not flow back into the model." **The structural consequence, stated plainly:** Layer 1 does not know that a tenant was worked. Every improvement discussed in the thread — dormant demotion, 14-day P0 release, fast-riser detection — is a *state machine over follow-up state*, and the follow-up state lives in a system Layer 1 cannot see. The cross-region comparison is [`../universal/writeback-status-by-region.md`](../universal/writeback-status-by-region.md), whose "B. Follow-up state → DB: APAC 0.0%" row means *0% in our database* — APAC's follow-up is tracked, in NexusCRM. And note what it is **not**: HubSpot stays load-bearing regardless, because Ops triggers the outbound emails from HubSpot workflows filtered on `apac_pql_priority_tier`. NexusCRM holds follow-up state; HubSpot runs the sends. Neither replaces the other. ### A note on the Layer 2 status line in this repo [`execution/apac-scoring-model-divergence-note.md`](execution/apac-scoring-model-divergence-note.md) calls `plg-notification` an "unofficial shadow build" and says do not port its engineering. **That verdict stands for its code.** But this file adds the fact the note is missing: that shadow build is the layer producing the digest APAC leadership reads and praises, and it carries operating rules that exist nowhere else. "Not a source of truth for engineering" and "is, in practice, the operating system for APAC PLG" are both true at once. The R22/R22b dedupe rules and the 🆕 channel should be written into the official spec and owned by Layer 1 — not left to live only in an unofficial build. --- ## 2. 休眠租户占位 — measurable, and worse than the digest suggests Yan: *"top10 的席位由上游打分决定，休眠租户占一个位就挤掉一个真实信号"*, root fix needs gateway-side `active_days_28d`. Heidi: dormant tenants **and** P0s handed over with no progress for 14 days should both be released. The data supports acting now, without `active_days_28d`. Current population — 7,788 active tenants, 168 sales-owned: | Priority | n | QPS NULL | QPS = 0 | QPS ≥ 0.001 | card on file | created > 3 months ago | |---|---|---|---|---|---|---| | P0 | 43 | 18 | 18 | **7** | 8 | 31 | | P1 | 110 | 83 | 5 | **22** | 15 | 22 | **Only 7 of 43 P0 and 22 of 110 P1 show any live workload at all.** 84% of the P0 queue is static inventory. Tenure is the sharper number. Snapshot history runs 2026-07-13 → 08-26 (45 days, all we retain): ```sql WITH p AS (SELECT s.tenant_id, COUNT(*) d FROM apac_tenant_score_snapshots s JOIN apac_active_tenants t USING(tenant_id) WHERE t.priority='P0' AND s.priority='P0' GROUP BY s.tenant_id) SELECT CASE WHEN d>=40 THEN '40+' WHEN d>=30 THEN '30-39' WHEN d>=14 THEN '14-29' WHEN d>=7 THEN '7-13' ELSE '<7' END b, COUNT(*) FROM p GROUP BY b; ``` | Continuously P0 for | tenants | |---|---| | **40+ days (i.e. every day we have records for)** | **29** | | 30–39 days | 1 | | 14–29 days | 2 | | < 7 days | 11 | **29 of 43 P0 have been P0 for the entire retained history.** "来来回回就是那几个" is literally correct: two thirds of the P0 board has not turned over once in a month and a half. ### The cause is not only the scoring rules — it is rescan cadence ```sql SELECT CASE WHEN processed_at>='2026-08-19' THEN '<=7d' WHEN processed_at>='2026-08-12' THEN '8-14d' WHEN processed_at>='2026-07-27' THEN '15-30d' ELSE '>30d' END b, COUNT(*) FROM apac_active_tenants GROUP BY b; ``` | Last rescored | tenants | share | |---|---|---| | ≤ 7 days | 2,012 | 26% | | 8–14 days | 1,028 | 13% | | 15–30 days | 2,705 | 35% | | **> 30 days** | **2,043** | **26%** | 61% of the population carries a score at least two weeks old. A tenant that went dormant three weeks ago still holds the score it earned when it was live — no rule change fixes that, only cadence does. ### What to ship, in order 1. **Tenure release (no new data, no upstream dependency).** A tenant that has held P0 for ≥ N days with no `track_step`/CRM movement drops to P1. Purely local — the snapshot table already carries the evidence, as the query above shows. This is the mechanism Heidi asked for, and it works on dormancy and on unworked hand-offs at the same time. 2. **Liveness gate on the static P0 rules.** `cluster_count ≥ 50 → P0` fires regardless of QPS; it is the documented source of Example A (100 clusters, score 33, final P0). Layer 2 already patched this unilaterally on 2026-07-24 (P0 12 → 6). That decision belongs in the official model, made by the Layer 1 owner — see the divergence note §3. 3. **`active_days_28d` from the gateway** (request submitted 08-03, still pending) upgrades both rules from proxy to real. Worth chasing, but **it is not a blocker for 1 or 2.** ⚠️ **EMEA's decay patch is not portable as written.** [`../emea/emea-signal-model-v0-patch-2026-08-26.sql`](../emea/emea-signal-model-v0-patch-2026-08-26.sql) patch 3 ships event-silence decay off the append-only events table. **`apac_tenant_events` is empty — 0 rows.** The table exists and nothing has ever written to it. APAC's decay must be built on snapshot tenure (above) or on a newly populated events feed; do not assume the EMEA predicate drops in. --- ## 3. 成长期盲区 / "快速上升" 名单 — now shipped, and the source still cannot carry it Proposed as: use `score_snapshots` to compare each tenant's score over 7 days and surface fast risers, catching the day-3-to-day-30 tenants that are neither new nor above threshold. Ships downstream, no upstream change. **Status update 2026-08-27 — this shipped** as R23 in the enrichment task: rise of ≥15 points within ≤7 days, or a first crossing into P1, excluding today's top-10, recently enriched, blocklisted and suppressed tenants; capped at three card rows. So the measurement below is no longer a caution about a proposal — it is a **live limitation of a rule now running in production**, and it makes the unfiled snapshot request a dependency of something already deployed. **The idea is right; the source does not support it yet.** `apac_tenant_score_snapshots` today: 42,596 rows, 2026-07-13 → 08-26, 7,953 distinct tenants, **~980 rows/day against a 7,788-tenant population — 12.6% daily coverage.** It is a rotating scan, not a daily census. In the 7-day window 08-20 → 08-26: | Days present in the window | tenants | of which P0 / P1 / P2 | |---|---|---| | all 7 | **814** | 32 / 26 / 595 | | 2–6 | 11 | 0 / 1 / 10 | | exactly 1 | **1,202** | 0 / 1 / 1,201 | A 7-day delta needs ≥2 observations, so it is computable for **814 tenants — 10.4% of the population** — and those 814 are the already-visible core (every current P0 and most of P1 sits in it). The 1,202 tenants seen exactly once yield no delta at all, and they are 99.9% P2 — precisely the growth-stage blind spot the list is meant to illuminate. **As built, the fast-riser list systematically reports on the tenants we already watch and stays silent on the ones we cannot see.** That is not a reason to withdraw it — a tenth of the population is better than none — but its output must be read as "risers among tenants we already sample", never as "the risers". This is the same 快照 vs 全量 basis trap that produced a false "P1 doubled" reading earlier: snapshot counts are not comparable to full-table counts and must never be presented as population figures. **Two ways forward, both cheap:** - **Now, no dependency:** derive the rising signal from `apac_active_tenants` itself by persisting a daily copy of `(tenant_id, total_score, priority, tenant_avg_qps, cluster_count, processed_at)` on the GTM side. Full population, starts accumulating the day it runs. Note the ceiling: a tenant rescored monthly can only "rise" on the day it is rescored, so this measures rescans, not tenants. - **Real fix, already drafted:** raise snapshot coverage toward a daily census and add the raw signal columns — [`../latam/REQUEST_snapshot_signal_history.md`](../latam/REQUEST_snapshot_signal_history.md) asks for exactly this (its §2 makes the same point: coverage alone, without raw columns, is not enough — and raw columns alone, at ~9% coverage, are not either). **That request is still drafted locally and has not been filed with `pingcap-support`.** Filing it is the unblock for both this and the QPS time-series contract. --- ## 4. Self-pay 收入不可见 — the answer to "我们是否手动更新金额" is: no, don't Yan measured 17 APAC self-pay tenants, 15 of them P2, invisible to the digest, with the self-pay table maintained as manual screenshots by Heidi. His fallback was a hand-maintained Feishu table (domain + monthly spend), joined daily, force-listed with a 💰 marker — with the honest caveat that *"堵点是这张表需要有人持续更新，不是技术问题."* **Update 2026-08-26 — the blind spot is materially larger than the review reported, and the data needed to size it is already accessible.** A tenant-grain view of self-serve spend exists in the warehouse and is joinable to the scoring tables today; the great majority of paying APAC tenants score P2 and never reach the digest, and a further set sits in `apac_sales_owned_tenants` and is excluded from reporting entirely. **No new access grant is required to close this.** Figures and the exact join are held outside this repo — ask Growth. The in-database picture below is what made it look small: ```sql SELECT has_credit_card, COUNT(*) n, SUM(priority='P0'), SUM(priority='P1'), SUM(priority='P2') FROM apac_active_tenants GROUP BY has_credit_card; ``` | `has_credit_card` | tenants | P0 | P1 | P2 | |---|---|---|---|---| | 1 | 231 | 8 | 15 | **208 (90%)** | | 0 | 450 | 24 | 12 | 414 | | **NULL** | **7,107 (91.3%)** | 11 | 83 | 7,013 | Two findings: 90% of known card-on-file tenants sit in P2 with no commercial weight, and the field is **unresolved for 91% of the population** — so card-on-file is not even reliably known, let alone spend. All 231 card holders are `plan = on_demand`; there is no revenue column anywhere in the APAC schema. **Do not stand up the manual Feishu table.** The thread already produced the better path: Vince named `gcp-digital-bi.model_dbaas_billing.dws_cloud_tenant_daily_billing_consumption_source_cost`, and Shuai confirmed BQ access is a self-service **Helix** request (Xiaopeng needs the dataset name, which Vince supplied). That is daily tenant-grain billing at source — it replaces the screenshot table, the manual Feishu table, and the missing revenue column in one move, for all regions rather than APAC only. Sequence: **(a)** Yan files the Helix request for that dataset; **(b)** once granted, join on tenant/domain and force self-pay tenants onto the digest with 💰 regardless of score; **(c)** retire the manual table. Answering Siddon's *"3 这个通过 agent 能解决吗"* precisely: the *maintenance* is automatable the moment the access exists — what is not agent-solvable is the access grant itself. Until then, `has_credit_card = 1` is the only in-database proxy, and at 91% NULL it is a weak one. --- ## 5. 信号中断 08-11 → 08-21 — the loss is recoverable The monthly summary records the signal source down 9.6 days and August new-registration numbers consequently low. **The database was not down.** New tenants per day, `apac_active_tenants`: | 08-11 | 08-12 | 08-13 | 08-14 | 08-15 | 08-16 | 08-17 | 08-18 | 08-19 | 08-20 | 08-21 | |---|---|---|---|---|---|---|---|---|---|---| | 195 | 163 | 134 | 131 | 105 | 154 | 152 | 153 | 167 | 173 | 158 | Steady, and in line with 08-01 → 08-10 (104–222/day). The outage was in **Layer 2's feed**, not in Layer 1's ingestion — roughly **1,685 tenants** signed up during the window and simply never appeared in a digest card. Because the 🆕 channel is a 72-hour window, they aged out of it silently and are now in the growth-stage blind spot of §3. So: **August new-signup volume was normal; August new-signup *coverage* was not.** Do not report the month as a demand dip. The window is re-sweepable — a one-off backfill pass over `created_at BETWEEN '2026-08-11' AND '2026-08-21'` with the same ICP filter the 🆕 channel uses recovers whatever was missed. Worth doing before the trail goes cold, and worth an alert on Layer 2 feed freshness so the next gap is measured in hours. --- ## 6. Open items, with owners | # | Item | Blocked on | Owner | |---|---|---|---| | 1 | Tenure-based P0/P1 release (dormant + 14-day-no-progress) | nothing — snapshot tenure is sufficient | Layer 1 (Yujie), spec first | | 2 | Liveness gate on `cluster_count ≥ 50 → P0` | a decision, not data; Layer 2 already ships it unofficially | Layer 1 owner + APAC GTM | | 3 | Follow-up state back into the dataset (`track_step` + timestamp) | scoped `UPDATE(track_step, track_update_time)` account, or a bot action | engineering | | 4 | Saved PQL list/view in NexusCRM + a field that marks PQL-sourced leads | the view is self-service; the marker is not — `LeadSource='Cloud Sign-Up'` covers only 19 of 117 P0/P1 domains (16%) | Heidi (view) / CRM admin (field) | | 5 | File [`REQUEST_snapshot_signal_history.md`](../latam/REQUEST_snapshot_signal_history.md) with `pingcap-support` | still drafted locally | Growth (Heidi) | | 6 | Helix request for the BQ billing dataset | not yet filed | Yan | | 7 | `active_days_28d` from the gateway | upstream request 08-03, unanswered | upstream | | 8 | Backfill the 08-11 → 08-21 signup window into the digest | nothing | Yan | | 9 | Promote R22/R22b, 🆕 channel, blacklist and dormancy rules into the official spec | nothing | Layer 1 + Yan | **Status discipline:** everything above is `checked 2026-08-26` against the live DB. The scoring code that actually runs is in `pingcap-support/apac_lambda_tasks` — see [`execution/README.md`](execution/README.md). This repo is intent; that repo is truth.