Skip to Content
實戰案例CRM：商機與互動

CRM：商機漏斗與最近互動

情境

ACME 的業務主管想在同一個聊天室看客戶、聯絡人、商機與互動紀錄。業務只維護每筆商機的金額、階段與成交機率；客戶層級的未結案金額、總商機數、已結案金額與最近互動日期都由系統即時計算。這樣一來，前端不必在每個畫面重做聚合，聊天室分析員也會讀到與 API 相同的數字。

資料模型

  • Accounts：Name、Industry
    • incoming rollups：Open Pipeline、Deal Count、Total Amount、Closed Amount、Last Activity
  • Contacts：Name、Email、Account → Accounts (one)、Account Name lookup
  • Deals：Name、Amount、Stage、Probability、Account → Accounts (one)
    • Weighted = [Amount] * [Probability]
  • Activities：Subject、Date、Deal → Deals (one)、Account → Accounts (one)
Contacts ── Account ─▶ Accounts ◀─ Account ─ Deals
                                  ▲              │
                                  │              └─ Weighted formula
Activities ── Deal ─▶ Deals       │
     └────── Account ─────────────┘

Note Activities 同時連到 Deal 與 Account 是刻意的去正規化。incoming rollup 只走單一 hop；把 Account 直接寫在活動上，Last Activity 才能保持簡單、可預測。

用一份 IaC 文件建起整套系統

下面這份文件一次 apply 就把整個情境立起來：依相依順序排列的四張表、本頁承諾的每一個計算欄位、讓漏斗不能跳關的規則、各角色會打開的檢視、把業務與客服與買方分開的授權，以及在成交當下通知客戶的自動化。把它貼進業務聊天室的 IaC 工作台，先 plan，再 apply。

套用到正式環境前有四處必須改成你自己的環境：兩個部門 token $dept:Sales 與 $dept:Support、買方的 $smc:line:… client token，以及最後兩行種子 record。其餘都是文件內部自行解析的 ref。

{"kind":"header","version":1,"system":"acme-crm","description":"ACME sales CRM: accounts, contacts, deals, activities"}
{"kind":"table","ref":"accounts","spec":{"name":"Accounts","key":"name","description":"Customer companies"}}
{"kind":"column","table":"accounts","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"column","table":"accounts","ref":"industry","spec":{"name":"Industry","type":"string"}}
{"kind":"table","ref":"contacts","spec":{"name":"Contacts","key":"email"}}
{"kind":"column","table":"contacts","ref":"email","spec":{"name":"Email","type":"string","required":true}}
{"kind":"column","table":"contacts","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"column","table":"contacts","ref":"account","spec":{"name":"Account","type":"link","target":"accounts","cardinality":"one"}}
{"kind":"column","table":"contacts","ref":"account_name","spec":{"name":"Account Name","type":"lookup","link_field":"account","target_column":"name"}}
{"kind":"table","ref":"deals","spec":{"name":"Deals","key":"name"}}
{"kind":"column","table":"deals","ref":"name","spec":{"name":"Name","type":"string","required":true}}
{"kind":"column","table":"deals","ref":"amount","spec":{"name":"Amount","type":"float"}}
{"kind":"column","table":"deals","ref":"probability","spec":{"name":"Probability","type":"float"}}
{"kind":"column","table":"deals","ref":"stage","spec":{"name":"Stage","type":"select","options":["lead","qualified","negotiation","closed","lost"]}}
{"kind":"column","table":"deals","ref":"close_date","spec":{"name":"Close Date","type":"date"}}
{"kind":"column","table":"deals","ref":"account","spec":{"name":"Account","type":"link","target":"accounts","cardinality":"one"}}
{"kind":"column","table":"deals","ref":"buyer_chat","spec":{"name":"Buyer Chat","type":"social_client","description":"The chat identity this deal is negotiated through"}}
{"kind":"column","table":"deals","ref":"weighted","spec":{"name":"Weighted","type":"formula","expression":"[amount] * [probability]"}}
{"kind":"table","ref":"activities","spec":{"name":"Activities","key":"subject"}}
{"kind":"column","table":"activities","ref":"subject","spec":{"name":"Subject","type":"string","required":true}}
{"kind":"column","table":"activities","ref":"date","spec":{"name":"Date","type":"date"}}
{"kind":"column","table":"activities","ref":"deal","spec":{"name":"Deal","type":"link","target":"deals","cardinality":"one"}}
{"kind":"column","table":"activities","ref":"account","spec":{"name":"Account","type":"link","target":"accounts","cardinality":"one"}}
{"kind":"column","table":"accounts","ref":"open_pipeline","spec":{"name":"Open Pipeline","type":"rollup","direction":"incoming","source":"deals","match":{"account":"$self"},"aggregation":"sum","target_column":"amount","filter":[{"column":"stage","op":"neq","value":"closed"},{"column":"stage","op":"neq","value":"lost"}]}}
{"kind":"column","table":"accounts","ref":"deal_count","spec":{"name":"Deal Count","type":"rollup","direction":"incoming","source":"deals","match":{"account":"$self"},"aggregation":"count"}}
{"kind":"column","table":"accounts","ref":"total_amount","spec":{"name":"Total Amount","type":"rollup","direction":"incoming","source":"deals","match":{"account":"$self"},"aggregation":"sum","target_column":"amount"}}
{"kind":"column","table":"accounts","ref":"closed_amount","spec":{"name":"Closed Amount","type":"rollup","direction":"incoming","source":"deals","match":{"account":"$self"},"aggregation":"sum","target_column":"amount","filter":[{"column":"stage","op":"eq","value":"closed"}]}}
{"kind":"column","table":"accounts","ref":"last_activity","spec":{"name":"Last Activity","type":"rollup","direction":"incoming","source":"activities","match":{"account":"$self"},"aggregation":"max","target_column":"date"}}
{"kind":"rule","table":"accounts","ref":"unique_account_name","spec":{"type":"unique","name":"Account name is unique","columns":["name"]}}
{"kind":"rule","table":"deals","ref":"stage_flow","spec":{"type":"transition","name":"The pipeline cannot skip a stage","column":"stage","pairs":[[null,"lead"],["lead","qualified"],["qualified","negotiation"],["negotiation","closed"],["negotiation","lost"],["lost","qualified"]]}}
{"kind":"rule","table":"deals","ref":"closed_needs_date","spec":{"type":"require","name":"A finished deal records its close date","column":"close_date","when":[{"column":"stage","op":"in","value":["closed","lost"]}]}}
{"kind":"trigger","table":"deals","ref":"tell_buyer_when_won","spec":{"name":"Tell the buyer when the deal closes","on":"updated","transition":{"column":"stage","from":"negotiation","to":"closed"},"actions":[{"type":"send_channel_message","recipient":{"column":"buyer_chat"},"message":"感謝您 — $row.name 已完成簽約，您的專屬業務本週會與您聯繫。"},{"type":"notify","message":"商機成交：$row.name，金額 $row.amount"}]}}
{"kind":"view","table":"deals","ref":"open_deals","spec":{"name":"Open deals","is_shared":true,"config":{"stored_filters":[{"column":"stage","op":"in","value":["lead","qualified","negotiation"]}],"sort_by":"amount","sort_order":"desc","columns":["name","account","amount","stage","probability","weighted","close_date"]}}}
{"kind":"view","table":"deals","ref":"buyer_deals","spec":{"name":"What the buyer sees","is_shared":true,"config":{"sort_by":"close_date","sort_order":"asc","columns":["name","stage","close_date"]}}}
{"kind":"view","table":"accounts","ref":"pipeline_board","spec":{"name":"Pipeline by account","is_shared":true,"config":{"sort_by":"name","sort_order":"asc","columns":["name","industry","open_pipeline","deal_count","total_amount","closed_amount","last_activity"]}}}
{"kind":"grant","table":"deals","principal":{"type":"department","id":"$dept:Sales"},"spec":{"can_read":"all","can_insert":true,"can_edit":"all"}}
{"kind":"grant","table":"deals","principal":{"type":"department","id":"$dept:Support"},"spec":{"can_read":"all","can_insert":false,"can_edit":"none","visible_columns":["name","account","stage","close_date","buyer_chat"]}}
{"kind":"grant","table":"deals","principal":{"type":"client","id":"$smc:line:U8c1d3e4f5a6b7c8d"},"spec":{"can_read":"filtered","can_insert":false,"can_edit":"none","visible_columns":["name","stage","close_date"],"read_filter":{"and":[{"column":"buyer_chat","op":"eq","value":"$me"}]}}}
{"kind":"client_access","table":"deals","spec":{"passphrase_enabled":false}}
{"kind":"record","table":"accounts","data":{"name":"Globex","industry":"Manufacturing"},"on_drift":"skip"}
{"kind":"record","table":"deals","data":{"name":"Globex Expansion","amount":250.0,"probability":0.6,"stage":"negotiation","account":["Globex"],"buyer_chat":"$smc:line:U8c1d3e4f5a6b7c8d"},"on_drift":"update"}

值得多看兩眼的幾行：

  • deals.buyer_chat 是 social_client 欄位，也是三種 principal 欄位型別之一（裸 id 的那一種）：寫入時給一個 raw client id，讀出來是 {id, platform, name}，只能做等值比較，任何介面都不能用它排序。
  • 最後一行 record 用可攜的 $smc:line:U8c1d3e4f5a6b7c8d token 而不是資料庫 id 來種這個 cell，這正是同一份文件能同時套用在測試與正式環境的原因，參見可攜身分 token。
  • deals.stage_flow 是 transition 規則，用「舊值→新值」白名單擋住有人把 lead 直接拉到 closed；它不檢查建立時的初始值，所以種子商機可以直接從 negotiation 開始。
  • deals.closed_needs_date 是帶 when 的 require 規則，只有在商機進入終局階段時才強制填入結案日期。
  • deals.tell_buyer_when_won 在 negotiation → closed 這個轉換上觸發，用 send_channel_message 推訊息給 buyer_chat 指到的那位 client，訊息中的 $row.name 由觸發當下的資料列算出。
  • 它的第二個 action 是 notify：在這張聊天室層級的表上會預設送到表本身的聊天室；同樣的 action 放在部門或公司層級的表上，沒有明確的 chatroom_id 就會在儲存時被拒絕。
  • $dept:Sales 授權可以寫入漏斗，$dept:Support 只能讀，而且透過 visible_columns 白名單，永遠看不到 amount、probability 與 weighted。
  • client 授權是買方自己的通道：can_read: "filtered" 搭配 {"column":"buyer_chat","op":"eq","value":"$me"}，剛好只看得到 chat 身分屬於自己的商機；也因為值比較永遠不會命中空 cell，完全沒有 buyer chat 的商機不會外洩。
  • client_access 本身沒有 visible_columns，欄位白名單只存在於 grant。這裡宣告 passphrase_enabled: false，等於在文件裡明講：這張表不接受用通關密語打開。

Warning send_channel_message 的觸及範圍比這個聊天室大。在 column 模式下，收件 client 只需要屬於表所屬公司的任一聊天室，不必是這個聊天室，所以這個 action 可以送訊息給公司內任何聊天室的任何 client。它也不受寫入者的表 ACL 管制：任何能存下觸發這條 trigger 的資料列的人，都能讓一則面向客戶的訊息送出去。只把它指向你的寫入者無法隨意改動的欄位。

產品流程

1. 以 IaC 建立四張表與計算欄位

套用上方那份文件。真正要顧好的是宣告順序：先 Accounts、Deals 與兩者的 link，每個 account rollup 都排在它所聚合的來源欄位之後。

把完整文件依序送到 iac.plan 與 iac.apply。plan 不寫入資料；apply 要帶回應中的 SHA-256 plan_hash，並傳送完全相同的文件。

2. 建立客戶、商機與互動

先建立 Globex account，取得其 record ID，再將該 ID 寫入 Deal 的 one-link：

POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/22222222-2222-4222-8222-222222222222/records
 
{
  "data": { "Name": "Globex", "Industry": "Manufacturing" },
  "created_by_ai": false
}
POST /private/module/custom_tables/chatroom/11111111-1111-4111-8111-111111111111/tables/33333333-3333-4333-8333-333333333333/records
 
{
  "data": {
    "Name": "Globex Expansion",
    "Amount": 250.0,
    "Stage": "negotiation",
    "Probability": 0.6,
    "Account": ["44444444-4444-4444-8444-444444444444"]
  },
  "created_by_ai": false
}

Activities 同時帶 Deal 與 Account，讓最近互動可直接 roll up：

{
  "data": {
    "Subject": "Contract signed",
    "Date": "2026-06-01",
    "Deal": ["55555555-5555-4555-8555-555555555555"],
    "Account": ["44444444-4444-4444-8444-444444444444"]
  },
  "created_by_ai": false
}

3. 搜尋商機並讀取 account rollups

搜尋條件使用 settings.column_mapping 中的內部 key。以下 records.search 會找出 closed 商機，並讓公式 Weighted 一起出現在回傳資料列：

{
  "filters": {
    "col_66666666_6666_4666_8666_666666666666": "closed"
  },
  "sort_by": "col_77777777_7777_4777_8777_777777777777",
  "sort_order": "desc",
  "limit": 50
}

再用 records.list 讀 Accounts。Globex 會顯示 Open Pipeline 350.0、Deal Count 3、Total Amount 750.0、Closed Amount 400.0 與 Last Activity 2026-06-01。

4. 從聊天室推進商機階段

前端把業務的自然語言要求送到歷程型聊天室：

POST /private/chatrooms/chat/ai/11111111-1111-4111-8111-111111111111
Content-Type: application/json
 
{
  "chat_type": "ai",
  "message": "請把商機「Umbrella Rollout」的 Stage 更新為 closed。"
}

若助理要求確認，下一個 turn 送出 確認。更新完成後，Umbrella 的 Closed Amount 變成 280.0，Open Pipeline 變成 50.0，Total Amount 仍是 330.0。

使用者會看到什麼

業務在 account 詳情頁直接看到一致的漏斗數字；Contacts 顯示 Account Name lookup；缺少 Probability 的商機，其 Weighted 會是 null 而不是零。聊天室裡可以問「Globex 未結案金額是多少？」或「哪個 account 的商機最多？」；經確認的階段更新會立刻反映到 account rollups。

變化與下一步

試試看

先在 IaC 工作台送出四表模型的 plan，再用 API Playground建立 Globex 與三筆商機，觀察 account rollups 如何隨 Stage 更新。

Last updated on
PMS：專案與工時預約：牙科診所