# Access Request: NexusCRM read access for PQL — the mirror we already depend on

**Requested by:** Heidi Liu (Growth, heidi.liu@pingcap.com) · **Date raised:** 2026-08-26
**Channel:** Helix self-service BQ request (per Shuai Feng, 2026-08-26) · **cc:** Vince Yao, Xiaopeng Zhu, Ni Jie, Yujie Xie
**Status:** ⚠️ **DRAFTED — NOT YET FILED.**
**Blocks:** PQL→SQL conversion measurement, follow-up-state visibility, and the dormant/unworked
release rules in [`../apac/apac-operating-mechanism-2026-08-26.md`](../apac/apac-operating-mechanism-2026-08-26.md) §2

---

## 0. The one-sentence version

**The APAC PQL pipeline already reads NexusCRM through BigQuery every night — Growth is the only
party in that loop without access to it**, so no rule and no query can consume follow-up state, and
every conversion number in the monthly review is assembled by hand.

**Scope note (2026-08-26):** per-record CRM reading is *not* blocked — the read-only OAuth app works
today as the signed-in user and pulled 2,620 Leads for the current P0/P1 queue. This request is
specifically for the **batch mirror**, which is what cohort analysis and a nightly join need. Do not
let "Heidi can already read NexusCRM" be a reason to close it, and do not let this request be a
reason to stop reading CRM in the meantime.

## 1. Why this is not a new integration

`apac_lambda_tasks` has read these five objects nightly since the Effective Sales Owner Sync
shipped, and writes the result into our own database:

```
gcp-digital-bi.nexuscrm_final.nexuscrm_org_c
gcp-digital-bi.nexuscrm_final.nexuscrm_account
gcp-digital-bi.nexuscrm_final.nexuscrm_lead
gcp-digital-bi.nexuscrm_final.nexuscrm_opportunity
gcp-digital-bi.nexuscrm_final.nexuscrm_user      -- required: Account/Lead/Opp carry only OwnerId
```

Source: `pingcap-support/apac_lambda_tasks/EFFECTIVE-SALES-OWNER-DESIGN.md` §2.1. Last successful
sync 2026-08-26 00:30 UTC, rule version `active_account_then_open_opportunity_v2`.

So the data path, the join keys and the security review already exist. **What is missing is a read
grant for the Growth side.**

Verified 2026-08-26 — three attempts, all denied, so this is not a misconfiguration on our end:

| Identity | Job project | Result |
|---|---|---|
| `sa-plg-growth@gcp-martech-infra` | `gcp-digital-bi` | `Access Denied: bigquery.jobs.create` |
| `heidi.liu@pingcap.com` (personal, freshly re-authenticated) | `gcp-digital-bi` | `Access Denied: bigquery.jobs.create` |
| `heidi.liu@pingcap.com` | `gcp-martech-infra` (where she *does* hold jobUser), reading the table cross-project | `Access Denied: Table gcp-digital-bi:nexuscrm_final.nexuscrm_lead — user does not have permission to query table` |

The third row is the decisive one: running the job from a project we own still fails, because the
**dataset itself** has never been shared with us. The same test against
`model_dbaas_billing.dws_cloud_tenant_daily_billing_consumption_source_cost` fails identically — so
§4 below is not a separate problem, it is the same missing grant on a second dataset.

Personal credentials are therefore not a workaround, and no amount of re-authentication changes it.
The only per-record fallback we have is the NexusCRM OAuth app (read-only, acts as the signed-in
user), which is capped at `LIMIT 200` per SOQL query with a ~1h token and no `LAST_N_DAYS` — fine
for spot-checking one account, useless for cohorts.

## 2. What is actually broken without it

The Lambda persists only what it needs for owner routing — one row per tenant per sync in
`apac_effective_sales_owner_snapshot`, covering **971 of 7,788 tenants (12.5%)**, and carrying
owner/stage but **no Lead Status, no LeadSource, no CreatedDate, no activity history**.

Three consequences, all live today:

1. **Follow-up state never reaches the model.** SDRs record follow-up as NexusCRM Lead Status, by
   hand — readable one account at a time, never joined to scoring.
   `apac_active_tenants.track_step` is `0` for all 7,788 rows and `track_update_time` has never been
   written. We cannot query "was this tenant worked?" — so P0/P1 tenure release, the 14-day
   unworked-hand-off rule, and any queue-hygiene rule cannot be built.
2. **PQL→SQL conversion cannot be measured.** The monthly review's "2 opportunities + 2 win-backs"
   is counted by a person reading cards. There is no query that returns it, no cohort view, and no
   way to compare regions or months on the same basis.
3. **The digest agent re-fetches per-tenant over the API instead.** Its NexusCRM token lasts ~1h
   with no refresh, SOQL is capped at `LIMIT 200` with no `LAST_N_DAYS`, and on `needs_auth` the
   daily card silently degrades to no-CRM mode. A batch mirror removes that fragility for the whole
   loop, not just for analysis.

## 3. What we are asking for — three items, in priority order

### 3.1 Read access to `gcp-digital-bi.nexuscrm_final.*` (the ask that unblocks everything)

| | |
|---|---|
| Grantee | `heidi.liu@pingcap.com`, plus `sa-plg-growth@gcp-martech-infra.iam.gserviceaccount.com` for scheduled jobs |
| Scope | `roles/bigquery.dataViewer` on dataset `nexuscrm_final`, `roles/bigquery.jobUser` on the project |
| Objects | the five listed in §1 — no others needed |
| Access mode | read-only, query-in-place; no export of PII to any repo or spreadsheet |
| Region scope | APAC first; EMEA/LATAM use the same objects, so a global grant avoids repeating this |

Fields we actually need (naming the fields, not `SELECT *`, so the grant can be narrowed):

| Object | Fields | Used for |
|---|---|---|
| `nexuscrm_lead` | `Id, Email, Company, Status, LeadSource, CreatedDate, ConvertedDate, ConvertedAccountId, OwnerId` | **follow-up state** (`Status`), the domain→account bridge, first-touch dating |
| `nexuscrm_account` | `Id, Name, Website, OwnerId, CreatedDate, Type` | fit/identity resolution, net-new vs existing |
| `nexuscrm_opportunity` | `Id, AccountId, StageName, Amount / ARR_Amount_USD__c, CloseDate, CreatedDate, LastModifiedDate, OwnerId` | PQL→SQL conversion, win-back and churn-risk classification |
| `nexuscrm_user` | `Id, Name, Email, IsActive` | resolve `OwnerId`; "no active owner" is our unclaimed signal |
| `nexuscrm_org_c` | tenant↔org↔account mapping keys | join product tenants to CRM without domain guessing |

**Acceptance check** — the grant is working when this returns rows:

```sql
SELECT l.Status, COUNT(*) AS leads, MAX(l.CreatedDate) AS latest
FROM `gcp-digital-bi.nexuscrm_final.nexuscrm_lead` l
WHERE LOWER(SPLIT(l.Email,'@')[SAFE_OFFSET(1)]) IN ('trickle.so','kickassproducts.com.au')
GROUP BY l.Status;
```

### 3.2 A way to *identify* PQL leads in NexusCRM — the list view is the easy half

Building a saved list view is self-service; **the blocker is that there is no field that marks a
lead as PQL-sourced.** Measured 2026-08-26 across the 117 domains behind the current P0/P1 queue:
`LeadSource = 'Cloud Sign-Up'` exists but appears on only **19 of 117 domains (16%)**. The rest
arrive as `List` (817), `Direct Traffic` (705), `Physical Event` (496), `Paid Advertising` (114)
— indistinguishable from any other marketing lead. A view built on `LeadSource` today would show
one sixth of the queue and silently drop the rest.

So the ask has two halves, and only the first needs anyone else:

- **Needs a CRM admin:** a durable PQL marker — either a checkbox/date field on Lead (`PQL Flagged`,
  `PQL First Scored`) written by the pipeline, or a rule that product signups reliably land as
  `LeadSource = 'Cloud Sign-Up'`. Without one, "which leads came from PQL?" is unanswerable in CRM.
- **We can do ourselves, today:** a personal list view on whatever proxy exists, to stop SDRs
  locating accounts one at a time. Worth doing immediately, with the 16% caveat understood."

### 3.3 The return path is a *read*, not a new write

**APAC's follow-up already writes straight into NexusCRM** — the SDR edits Lead Status, and that is
the system of record. Nothing about that needs to change, and nobody needs a new write credential
pointed at a CRM.

What is missing is only the read back into scoring: the nightly sync that already queries these
objects should also map Lead Status onto `<region>_active_tenants.track_step` +
`track_update_time` (values in [`writeback-status-by-region.md`](writeback-status-by-region.md)).
One extra column write inside a job that is already running, using a credential engineering already
holds.

Verified 2026-08-26 that the source field is real and populated — across the P0/P1 queue's leads,
Status carries `OPEN`, `Working`, `Nurture`, `UNQUALIFIED`, `Open Opportunity`, `Qualified`,
`Meeting Completed`, `Lost Opportunity`. That is a usable follow-up state machine, not a
free-text field.

*(A scoped `UPDATE (track_step, track_update_time)` ops account per region remains the fallback if
the sync route is refused — the APAC and LATAM analyst accounts are `SELECT`-only today. It is
strictly worse: it puts a DB credential in a human's hands to record something the CRM already
knows.)*

## 4. Related, same channel, please bundle

`gcp-digital-bi.model_dbaas_billing.dws_cloud_tenant_daily_billing_consumption_source_cost`
(named by Vince, 2026-08-26) — daily tenant-grain billing. It removes the manually-screenshotted
self-pay table entirely. ⚠️ **Corrected 2026-08-26: this dataset is an upgrade, not the unblock.**
A tenant-grain view of self-serve spend is already reachable with existing grants, and the join to
the scoring tables works today. Ship on what we have; take this dataset for the cleaner daily
source-cost grain when it arrives. Same dataset owner, same request flow — one request, two
datasets.

## 5. What we stop doing once this lands

- Stop hand-counting conversions for the monthly review; the numbers come from a query.
- Stop maintaining the self-pay table by screenshot.
- Stop letting the daily card degrade to no-CRM mode on token expiry.
- Start being able to ship the tenure-release rules — they need "was it worked?", which is exactly
  what Lead Status carries.

---

## 附：可直接貼到飛書的中文版

> **需求：開通 NexusCRM 在 BigQuery 的只讀權限（`gcp-digital-bi.nexuscrm_final`）**
>
> 背景：APAC 的 PQL Lambda（Yujie）每晚已經在讀這個 dataset 的 5 張表
> （`nexuscrm_org_c / account / lead / opportunity / user`）來算 Effective Sales Owner，
> 最近一次成功同步是 08-26 00:30。也就是說資料通路、join key、安全審核都已經存在，
> 只差 Growth 這邊沒有權限。
>
> 申請對象：`heidi.liu@pingcap.com` ＋ `sa-plg-growth@gcp-martech-infra.iam.gserviceaccount.com`
> 權限：dataset `nexuscrm_final` 的 `bigquery.dataViewer` ＋ 專案層的 `bigquery.jobUser`（只讀）
>
> 08-26 已實測三種身分全部被拒（service account 直查、個人帳號直查、個人帳號從
> `gcp-martech-infra` 跨專案查），最後一種證明是 **dataset 從未授權給我們**，不是設定問題；
> 帳單表 `model_dbaas_billing` 測試結果一模一樣。所以用個人權限繞不過去。
>
> 為什麼需要：
> 1. **跟進狀態我們看不到。** SDR 的跟進是手改 NexusCRM Lead Status，而我們資料庫裡的
>    `track_step` 7,788 筆全是 0、`track_update_time` 從來沒被寫過。所以「這個租戶有沒有人跟」
>    無法用 SQL 回答，休眠退位、P0 交出去 14 天無進展退位這些規則都做不了。
> 2. **PQL→商機的轉化率沒辦法算。** 月報裡的「2 個商機 + 2 個 win-back」是人看卡片數出來的，
>    沒有任何一條 query 能複現，也沒法跨區跨月比較。
> 3. Lambda 現在只把 owner 相關欄位落地，覆蓋 971 / 7,788 個租戶（12.5%），
>    而且不含 Lead Status / LeadSource / CreatedDate。
>
> 另外兩件事，一起提：
> - **需要一個能標記「這是 PQL 來的 lead」的欄位。** list view 我們自己建就行，難的是沒有欄位可篩：
>   08-26 實測，目前 P0/P1 佇列的 117 個域名裡，只有 19 個（16%）帶
>   `LeadSource = 'Cloud Sign-Up'`，其餘是 List / Direct Traffic / Physical Event，跟一般行銷
>   線索分不開。希望能在 Lead 上加一個 PQL 標記欄位（checkbox 或首次評分日期），或讓產品註冊
>   穩定落成 `Cloud Sign-Up`。
> - **跟進狀態的回流是「讀」不是「寫」。** APAC 現在就是直接寫進 NexusCRM（SDR 改 Lead Status），
>   這條不用動；缺的是每晚那個已經在查這些物件的同步任務，順手把 Lead Status 映射成
>   `track_step` + `track_update_time`。用的是工程已經有的憑證，不需要給任何人新的寫權限。
>
> 順帶同一個申請一起開：
> `gcp-digital-bi.model_dbaas_billing.dws_cloud_tenant_daily_billing_consumption_source_cost`
> （Vince 08-26 提供）。APAC 17 個 self-pay 租戶有 15 個是 P2、對日報完全隱身，
> 而 `has_credit_card` 有 91.3% 是 NULL，庫內沒有可用的代理欄位 —— 有了帳單表就不用再靠人工截圖維護。