# Apexcloud Retention Ops > Operating procedure for solving ApexCloud Retention Operations tasks against the read-only HTTP API at http://127.0.0.1:8074. Use this skill whenever a task asks you to build a renewal risk queue, a retention action board, a QBR metrics packet, a receivables / pipeline operations review, or a churn-model validation & outreach ranking for ApexCloud accounts — or any time the prompt mentions current ARR, billing snapshots, clean ticket counts, latest NPS, overdue A/R aging buckets, risk scoring, reason codes, primary actions, or the four-letter policy_codes (RS-/REV-/SUP-/ACT-/RCP-/CM-/PW-/FS-/MOD-/PRB-/DEP-/OUT-/BORD-/EXP-/CAL-). These tasks share fixed business conventions that are easy to get wrong; follow this skill instead of guessing the data sources, exclusion rules, thresholds, or enums. - Skill: `prism-shadow/apexcloud-retention-ops-8` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add prism-shadow/apexcloud-retention-ops-8` - Raw SKILL.md: https://api.skillmd.com/api/skills/prism-shadow/apexcloud-retention-ops-8/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: DevOps & Infra - Author: prism-shadow (https://skillmd.com/u/prism-shadow) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/prism-shadow/apexcloud-retention-ops-8 --- # ApexCloud Retention Operations SOP This skill captures the **business conventions** behind the ApexCloud Retention Operations task family. The graders are deterministic: the exact data source, the exclusion rule, the threshold, the enum spelling, the sort order, and the rounding all matter. Getting the *narrative* right is not enough — reproduce the *rules*. All data comes from the read-only HTTP API. The base URL is given in the prompt / environment (typically `http://127.0.0.1:8074`). Use `curl` + a JSON parser (`python3 -c` / `jq`). Never read local files, `env/`, or task-group source — only the API. The service is already running; ignore any "start command" in the prompt. ## Workflow 1. **Identify the task family** (see table below) and read its answer template to lock in the exact output keys, enum vocabularies, and rounding. 2. **Pull each metric from its canonical source** using the rules in "Canonical metric definitions". Do not substitute one source for another (e.g. the account record's `billing_arr_current` is NOT the current ARR; the metrics endpoint's `support_ticket_count` is NOT the clean ticket count). 3. **Apply the exclusion / threshold rules** exactly. 4. **Derive risk signals, reason codes, level, and action** per "Risk model". 5. **Sort / rank** per the family's rule, then format with the required precision and the correct `policy_codes`. 6. Return **only JSON** matching the template. No prose, no code fences. `references/api_and_rules.md` has endpoint-by-endpoint field maps, worked numeric examples, and the per-family `policy_codes` cheat-sheet. Read it when you need the exact shape of a response or to confirm a threshold. ## Task families and their policy_codes | Family | Trigger words | Top-level keys | Fixed policy_codes | |---|---|---|---| | Renewal Risk Queue | "renewal risk queue", "top N ranked by risk" | `risk_accounts`, `portfolio_summary`, `model_checks`, `policy_codes` | RS-6, REV-4, SUP-8, ACT-5 | | Retention Action Board | "action board", "operating review", "retention board order" | `action_board`, `segment_summary`, `followup_calendar`, `policy_codes` | RS-6, REV-4, SUP-8, ACT-5, BORD-4, EXP-6, CAL-5 | | QBR Metrics Packet | "QBR", "metrics packet", "quarterly business review" | `qbr_metrics`, `highlights`, `metric_sources`, `review_plan`, `agenda_topics` | (no policy_codes block; uses `metric_sources` enums) | | Receivables & Pipeline Review | "receivables", "operations review", "A/R", "pipeline" | `financial_summary`, `pipeline_summary`, `overdue_followups`, `ops_context`, `policy_codes` | RCP-7, CM-5, PW-6, FS-4 | | Churn Validation & Ranking | "churn model", "validation", "outreach ranking", "candidates.csv" | `model_validation`, `risk_ranking`, `cohort_checks`, `model_policy_codes` | MOD-7, PRB-4, DEP-5, OUT-2 | These code values recur across tasks and are the **same every time** for the same family. The answer template offers three options per code (e.g. `RS-2|RS-6|RS-9`); pick the value listed above. They encode the policy you are following (RS-6 = the six-signal risk model, REV-4 = billing-snapshot ARR source, SUP-8 = spam+dup+cancelled ticket hygiene, ACT-5 = the action-priority ladder, etc.). When in doubt, match the exact option from the template that corresponds to the rule you actually applied. ## Canonical metric definitions These are shared across families. The graders test the *source*, not just the value. ### Current ARR → billing snapshot (NOT the account record, NOT CRM) - Endpoint: `GET /api/billing/snapshots?account_id=&as_of=`. - `current_arr` = the `billing_arr` of the latest **posted** snapshot with `as_of <= the assessment/as-of date`. The `&as_of=` query param already returns that single snapshot; otherwise pick the max `as_of` not after the date yourself. - Do **not** use the account's `billing_arr_current` (a rounded plan figure) or `crm_arr`. Snapshots are the posted source of truth and differ by thousands. - `model_checks.uses_billing_arr_source` = `true`. `arr_source_code` = `REV-4`. ### Clean support ticket count → tickets endpoint, hygiene-filtered - Endpoint: `GET /api/accounts//tickets?start=YYYY-MM-DD&end=YYYY-MM-DD`. - A ticket is **clean** only if ALL hold: `is_spam == false` AND `is_duplicate == false` AND `status != "cancelled"` (valid statuses: open, closed, cancelled). Count by `created_date` inside the window. - Do **not** use the metrics endpoint's `support_ticket_count` (that is the raw count including spam/dup/cancelled). `support_hygiene_code` = `SUP-8`. ### Latest NPS → nps endpoint, retracted excluded, most recent in window - Endpoint: `GET /api/accounts//nps?start=YYYY-MM-DD&end=YYYY-MM-DD`. - Drop responses with `retracted == true`. Among the rest in the window, take the one with the **latest `response_date`**; its `score` is `latest_nps`. - If no valid response exists in the window, NPS is missing → emit `null` (QBR uses `null` for months/highlights with no completed survey). ### Overdue balance → A/R aging, OLDER buckets only - Endpoint: `GET /api/finance/ar-aging?as_of=YYYY-MM-DD` (optional `®ion=`). - `overdue_balance = bucket["61_90"] + bucket["90_plus"]`. The "older aging buckets" phrasing means 61+ days. **Exclude** `current`, `1_30`, and `31_60`. - Match a row to an account via its `aging_id`: real CRM accounts have `AR--`; rows shaped `AR-noise-...` are non-CRM ("unlinked"). If no matching aging row, overdue = `0.00`. ### Monthly metrics → metrics endpoint - Endpoint: `GET /api/accounts//metrics?start=YYYY-MM&end=YYYY-MM` (note: months, not dates). Fields per month: `recognized_revenue`, `support_ticket_count` (raw), `sla_compliance`, `nps_score`, `product_usage`, `active_seats`, `survey_status`. ## Risk model (RS-6) — reason codes, level, action Compute these per account over the analysis window using the canonical sources above. The six **negative** signals and two **positive** signals each map to a reason code. ### Reason-code triggers (verified) - `renewal_window` — `renewal_date` is in the future and within ~90 days of the as-of date (i.e. `0 < days_to_renewal <= 90`). Past-due renewals do NOT fire this. - `overdue_receivable` — `overdue_balance > 0` (the 61_90+90_plus rule). - `nps_drop` — `latest_nps < 40` **OR** the NPS fell sharply within the window (`first_valid_score − latest_valid_score >= ~15`). A low absolute score OR a steep decline both count. - `sla_degradation` — **any** clean ticket has `first_response_sla_met == false` OR `resolution_sla_met == false`. (Driven by ticket SLA flags, NOT the metrics `sla_compliance` field. An account fires this even at high metric SLA if it has one missed ticket; an account with zero misses does not fire it.) - `usage_decline` — the minimum monthly `product_usage` in the window is low (`min(product_usage) < ~65`). This is an absolute floor, not a month-over-month delta. - `low_tenure_high_churn` — `contract_tenure_months < 18`. (Confirmed by the churn cohort: tenure 12/13/7 fire; 20 does not.) - `expansion_offset` (positive) — the account has open expansion pipeline in the window (`sum of open opportunity amounts > 0`). It is a *mitigating* note placed last. - `clean_billings` (positive) — receivables are clean (`overdue_balance == 0`) and the account has no receivable problem to flag. Used as the positive note for low-risk accounts that have no open-expansion story. Order reason_codes with **negatives first** (a natural order such as renewal_window, overdue_receivable, nps_drop, sla_degradation, usage_decline, low_tenure_high_churn), then positives (expansion_offset / clean_billings) last. An account can carry both a positive note and negatives. ### Risk level bands (by integer risk_score, rounded to nearest 5) - `critical`: score >= 80 - `high`: 50–79 - `medium`: 30–49 - `low`: < 30 `risk_score` is a weighted blend of the triggered signals scaled by their severity (overdue size, how low NPS/usage are, how soon renewal is, tenure). It is reported as an integer (multiples of 5 in practice) and **clamped to 0–100**. Two accounts with the same reason-code *set* can still get slightly different scores because severity is continuous — so derive the score from the underlying magnitudes, not from a fixed points-per-code table. What the grader checks most reliably is the **ordering and the level band**, so make sure stronger-signal accounts outrank weaker ones. ### Primary action ladder (ACT-5) — first match wins 1. `overdue_receivable` present → `collections_followup`. 2. else technical health is weak (`sla_degradation` and/or `usage_decline`, especially low `product_usage`) → `technical_recovery`. 3. else `renewal_window` present with otherwise healthy technical signals → `renewal_save`. 4. else high/critical with no clearer driver → `executive_qbr`. 5. else low risk → `no_action` (action board) or `nurture_monitor` (queues that prefer a monitoring touch). Pick the enum the template offers. When two accounts share reason codes but differ in raw severity (e.g. one has weak `product_usage`), let the stronger technical problem pull toward `technical_recovery` and the healthier one toward `renewal_save`. Tie-break ranking by `risk_score` desc, then by `current_arr` desc. ### tenure_risk_direction Lower tenure → higher risk, so `model_checks.tenure_risk_direction = "negative"` (also the churn `tenure_coefficient_direction`). ## Family-specific assembly notes ### Renewal Risk Queue - Score every reviewed account; return the **top N (usually 5) by risk_score desc** (tie-break `current_arr` desc). Each item: rank, account_id, risk_score, risk_level, primary_action, current_arr, latest_nps, clean_ticket_count, overdue_balance, reason_codes. - `portfolio_summary.accounts_reviewed` = count of ALL reviewed accounts (not just the top 5). `critical_or_high_count` = number of returned accounts at critical/high. `arr_at_risk` = **sum of current_arr for the critical+high accounts only**. `collections_count` / `technical_recovery_count` = counts of those primary_actions among the returned accounts. ### Retention Action Board - Return **all** requested accounts in board order: rank by risk severity (critical→high→medium→low), then within ties by current_arr desc (BORD-4). Low / no_action accounts get `next_touch_due_date: null`. - `expansion_pipeline` = sum of that account's open Q2 opportunity amounts (0.0 if none). - `next_touch_due_date` comes from the prompt's per-action calendar (CAL-5); echo that same map into `followup_calendar`. - `segment_summary`: `strategic_accounts` / `enterprise_accounts` = counts by the account `segment` field. `arr_at_risk` = **sum of current_arr for all non-low / non-no_action accounts** (i.e. medium+high+critical). `open_expansion_pipeline` = total expansion_pipeline across the board. `net_revenue_exposure = arr_at_risk − open_expansion_pipeline` (EXP-6). - (Note the deliberate difference vs the Renewal Queue: the queue's `arr_at_risk` is critical+high only; the board's is everything except low/no_action.) ### QBR Metrics Packet - Per month build: `revenue = recognized_revenue` (metrics); `support_tickets = clean ticket count` (tickets endpoint, hygiene-filtered — NOT the metrics raw count); `sla_compliance_pct` = % of that month's **clean tickets** whose `first_response_sla_met` is true (1 decimal); `nps_score` = latest valid NPS in that month, else `null`. - `metric_sources` are **fixed labels** describing provenance, not literal endpoints: `revenue: "crm_closed_won"`, `support_tickets: "support_export"`, `sla_compliance: "sla_report"`, `nps: "nps_survey"`. - `highlights`: average_revenue (mean of monthly revenue), peak revenue month/value, max SLA month/value, peak NPS month/value (ignore null months), and `ticket_trend`: `improving` if clean tickets trend down, `worsening` if up, else `flat`. - `review_plan.review_owner`: `customer_success` by default; `solutions_engineering` if the quarter has notable technical/SLA recovery work; `finance_ops` if the story is billing/receivables-driven. `review_due_date` and `needs_technical_signoff` follow the prompt; signoff is generally `false` unless a severe/sustained technical breach is present. - `agenda_topics`: exactly four, **ordered**, from the allowed set. Lead with `partnership_overview`, then `q2_metrics`; include `technical_recovery` when there was an SLA dip and `commercial_expansion` when there is open expansion pipeline; close with `q3_initiatives`. ### Receivables & Pipeline Review - Start from A/R rows with overdue > 0 (61_90+90_plus). `overdue_client_count` and `overdue_total` cover ALL such rows (CRM and noise). For each, `link_status` = `linked` if its `aging_id` is `AR--...` for a real account (set `account_id`), else `unlinked` (`account_id: null`). `linked_followup_count` / `unlinked_followup_count` split them. `primary_action` is always `collections_followup`; `due_date` is the prompt's follow-up date. **Sort overdue_followups by `customer_name` ascending.** - `pipeline_summary` from `GET /api/opportunities?start=&end=` over the quarter: `won` = `stage == "Closed Won"`, `lost` = `stage == "Closed Lost"`, `open` = `state == "open"`. `won_revenue` / `open_pipeline` = summed `amount`. `win_rate_pct = won / (won + lost) * 100` (1 decimal). `top_open_product_line` = the `product_line` with the largest open `amount` total. - `ops_context`: `hr_headcount` = sum of `headcount` across the requested HR regions; `unpaid_claims_total` = sum of `unpaid_claims_amount`; `event_orders` / `event_revenue` read directly from the event-performance row. ### Churn Validation & Ranking - Use the CSV exports: `/exports/churn/train.csv`, `validation.csv`, `candidates.csv`. `training_rows` / `validation_rows` = data row counts (180 / 60 in the seed but recount). `feature_count` = columns minus `customer_id` and the `Churn` target (= 19 in the seed). Fit a simple classifier on train, score validation for `accuracy_pct` (1 decimal) and map to `accuracy_band` (below_70 / 70_to_79 / 80_to_89 / 90_plus). `tenure_coefficient_direction` = `negative`. - Rank only the named candidates by `predicted_churn_probability` (3 decimals), return top 5. `outreach_action` / `reason_code` mirror the action ladder on the CSV features: `InvoicePastDue == Yes` → `collections_followup` / `overdue_receivable`; else `tenure < 18` → `renewal_save` / `low_tenure_high_churn`; else `nurture_monitor` / `clean_billings` (technical_recovery / sla_degradation when SLA/ usage features are the dominant problem). - `cohort_checks` are computed over the **returned top-5**, not all candidates: `past_due_shortlist_count` = top-5 with PastDue, `low_tenure_shortlist_count` = top-5 with tenure < 18, `average_probability_top5` = mean of the 5 probabilities. ## Common pitfalls - Using `billing_arr_current` / `crm_arr` instead of the **posted billing snapshot** for current ARR. - Using metrics `support_ticket_count` instead of the **hygiene-filtered** tickets (must exclude spam, duplicates, AND cancelled). - Summing all aging buckets for overdue instead of only **61_90 + 90_plus**. - Taking the highest or first NPS instead of the **latest non-retracted** in window. - Deriving `sla_degradation` from the metrics `sla_compliance` percentage instead of from per-ticket SLA-met flags. - Treating `usage_decline` as a slope when it is an **absolute usage floor**. - Forgetting that `arr_at_risk` is defined differently for the queue (critical+high) vs the board (everything except low/no_action). - Computing churn `cohort_checks` over all candidates instead of the top-5. - Wrong sort: receivables sort by `customer_name` asc; risk queue/board sort by severity then ARR. - Emitting prose, code fences, or extra keys. Return only the template JSON with the right precision (currency 2dp, percentages 1dp, counts/scores integers) and the correct fixed `policy_codes`.