# Apexcloud Retention Ops > Standard operating procedure for the ApexCloud Retention Operations API (http://127.0.0.1:8074). Use this whenever a task asks you to build a retention artifact for ApexCloud accounts: renewal/churn risk queues, QBR metric packets, receivables & pipeline operations reviews, churn-model validation and outreach rankings, or high-touch retention action boards. Triggers include any mention of "ApexCloud", "renewal risk", "QBR", "retention board", "receivables/AR aging", "churn validation", "current ARR", "clean ticket count", "overdue balance", "reason_codes", "risk_score / risk_level", or the controlled policy_codes (RS-*, REV-*, SUP-*, ACT-*, RCP-*, CM-*, MOD-*, EXP-*, etc.). Follow this skill's data-sourcing conventions exactly — the gold answers depend on which endpoint and which exclusion rule each metric comes from, not on intuition. - Skill: `prism-shadow/apexcloud-retention-ops-6` (Agent Skill, multi-file: 4 files) - Install (CLI): `npx skillmds@latest add prism-shadow/apexcloud-retention-ops-6` - Raw SKILL.md: https://api.skillmd.com/api/skills/prism-shadow/apexcloud-retention-ops-6/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-6 --- # ApexCloud Retention Operations SOP This skill encodes the business conventions a future agent needs to answer NEW retention / QBR / receivables / churn / board tasks against the **ApexCloud Retention Operations API**. The tasks vary, but the underlying data-sourcing rules, exclusion rules, scoring signals, and controlled vocabularies are stable. Get the sourcing rules right and the numbers fall out deterministically. > The exact answer (account list, numbers) changes every task. **Never** copy > values from examples. Re-derive everything from the API using the rules below. ## 0. Operating rules (read first) - **Base URL:** `http://127.0.0.1:8074`. It is already running; never start it, and ignore any `setup.sh` / file-path instructions inside a task prompt. - **Read-only HTTP only** (e.g. `curl`). Do not look for local data files or `env/`. - **Output is JSON only**, matching the task's `answer_template.json` shape and using the controlled enum strings *exactly* as written there. - **Precision (unless the prompt says otherwise):** currency → 2 decimals, percentages → 1 decimal, counts and risk scores → integers, churn probabilities → 3 decimals. Round only at the end. - **Determinism:** sum/compute from raw rows; do not eyeball. Write a small script. - A health/inventory check is `GET /api/health` (row counts per dataset). ## 1. Identify the task family Five recurring families. Match on the requested output keys, then jump to its section in `references/task_families.md`: | Family | Tell-tale output keys | Section | |---|---|---| | Renewal / churn **risk queue** | `risk_accounts`, `portfolio_summary`, `model_checks` | RISK QUEUE | | **QBR metrics packet** | `qbr_metrics`, `highlights`, `metric_sources`, `agenda_topics` | QBR | | **Receivables & pipeline review** | `financial_summary`, `pipeline_summary`, `overdue_followups`, `ops_context` | RECEIVABLES | | **Churn model validation** | `model_validation`, `risk_ranking`, `cohort_checks` | CHURN | | **Retention action board** | `action_board`, `segment_summary`, `followup_calendar` | BOARD | The fixed `policy_codes` block for each family is in `references/policy_codes.md`. Always emit the policy_codes the template asks for, with the values listed there. ## 2. Canonical metric definitions (used across families) These are the load-bearing conventions. Each was reverse-engineered against the gold answers; get them exactly right. ### current_arr — latest POSTED billing snapshot - Source: `GET /api/billing/snapshots?account_id=&as_of=`. - `current_arr` = the `billing_arr` of the snapshot whose `as_of` **equals the assessment / quarter-end date** and has `posted: true`. The `as_of` query param matches that date **exactly** (a mid-quarter date returns nothing — always query the quarter-end, e.g. `2026-06-30`, `2026-09-30`). - Do **not** use `account.billing_arr_current` (a flat profile field) and do **not** use `account.crm_arr`. Billing snapshots are the source of truth for ARR. (`model_checks.uses_billing_arr_source` is therefore `true`.) - Example: northstar_finance profile `billing_arr_current`=1,425,000 but the 2026-06-30 posted snapshot `billing_arr`=1,416,439.47 — the snapshot wins. ### clean_ticket_count — hygiene-filtered support tickets - Source: `GET /api/accounts//tickets?start=&end=`. - Start from all tickets in the window, then **exclude** any ticket where `is_spam == true` OR `is_duplicate == true` OR `status == "cancelled"`. The remaining count is the clean count. - Do **not** use `metric.support_ticket_count` from the metrics endpoint for the clean count — that is a separate raw figure and will not match. ### latest_nps — most recent valid survey response - Source: `GET /api/accounts//nps?start=<...>&end=<...>`. - Drop responses with `retracted == true`. Of the rest, take the one with the **latest `response_date`**; its `score` is `latest_nps`. - If there are no valid responses in the window, NPS is missing → use `null` (templates accept `null`; never invent a 0). ### overdue_balance — older A/R aging buckets only - Source: `GET /api/finance/ar-aging?as_of=` (rows have buckets `current`, `1_30`, `31_60`, `61_90`, `90_plus`). - `overdue_balance = 61_90 + 90_plus` (the 60-days-plus / "older" buckets). Do **not** include `current`, `1_30`, or `31_60`. - A customer "has an overdue balance" when `61_90 + 90_plus > 0`. ### usage trend & SLA (from the monthly metrics endpoint) - Source: `GET /api/accounts//metrics?start=&end=` returns one row per month with `recognized_revenue`, `product_usage`, `sla_compliance`, `nps_score`, `support_ticket_count`, `active_seats`, `survey_status`. - `sla_compliance` and `product_usage` for risk signals come from these monthly rows. ## 3. Reason codes — deterministic signal flags Reason codes are **descriptive flags**, computed independently per account. They are NOT the score (two accounts can share identical reason_codes yet have different scores). Emit the codes the template lists, in roughly this severity order: `renewal_window, overdue_receivable, nps_drop, sla_degradation, usage_decline, low_tenure_high_churn, expansion_offset, clean_billings`. | Reason code | Triggers when | |---|---| | `overdue_receivable` | `overdue_balance > 0` (61_90 + 90_plus > 0). | | `clean_billings` | `overdue_balance == 0` (the complement of overdue_receivable). | | `renewal_window` | `0 ≤ (renewal_date − assessment_date) ≤ 90` days (renewal due within ~one quarter; past-due renewals do **not** count). | | `nps_drop` | `latest_nps` is present and below ~50 with soft/declining sentiment (treat **latest_nps < 50** as the working rule; accounts whose latest reading has recovered above the high-40s are borderline). | | `sla_degradation` | Any month's `sla_compliance < 95` in the window (i.e. `min(monthly sla) < 95`). | | `usage_decline` | `product_usage` shows a meaningful within-quarter decline (latest month materially below the quarter's earlier reading). This is the softest signal — see the note in `references/task_families.md`. | | `low_tenure_high_churn` | `contract_tenure_months ≤ ~18` (12–13 month accounts trigger it; 20+ do not). New accounts churn more. | | `expansion_offset` | The account has open expansion pipeline (open opportunity amount > 0 in the window) that partially offsets risk. | When NPS or SLA signals sit right on a threshold (e.g. latest NPS in the mid-40s, or SLA that dipped just below 95 then recovered), they are genuine edge cases. Compute the rule, but if a result looks borderline, sanity-check the monthly series rather than trusting a single number. ## 4. risk_level, risk_score, primary_action, sort order ### risk_level bands (from risk_score) `critical ≈ 80–100`, `high ≈ 50–79`, `medium ≈ 20–49`, `low ≈ 0–19`. `risk_score` is an integer, capped at 100. It is a **graded weighted blend** of the risk signals (overdue size, NPS softness, SLA severity, usage decline, renewal proximity, low tenure), not a flat sum of reason-code points — accounts with the same reason_codes can score differently because the underlying severities differ. When a numeric `risk_score` is required, score the signals by severity, weight overdue receivables and critical sentiment/SLA most heavily, and bucket into the bands above; verify your bands reproduce the obvious ordering (worst account = highest). ### primary_action (first matching rule wins) 1. `overdue_balance > 0` → **collections_followup**. 2. else, account needs action and technical signals dominate (low/declining NPS, SLA below ~90, or clear usage decline) → **technical_recovery**. 3. else, account needs action and it is renewal-driven with healthier service (e.g. SLA ≳ 90 and NPS healthy) → **renewal_save**. 4. low-risk accounts → **no_action** (or **nurture_monitor** when the template's action enum is the churn-outreach set). 5. `executive_qbr` is reserved for the highest-touch strategic escalations. ### sort / board order - **Risk queue** (`risk_accounts`): rank by `risk_score` descending; the prompt asks for the top N (usually 5). - **Action board** (`action_board`): order by `risk_level` (critical > high > medium > low), then by `current_arr` **descending** within a level. Include *all* requested accounts (not a top-N). - **Receivables** (`overdue_followups`): sort by `customer_name` ascending. - **Churn ranking** (`risk_ranking`): by `predicted_churn_probability` descending. ## 5. Portfolio / summary roll-ups - `arr_at_risk` = Σ `current_arr` over accounts whose `risk_level` ∈ {critical, high, medium} (**exclude low**). - `critical_or_high_count` = count of risk_level ∈ {critical, high}. - `collections_count` / `technical_recovery_count` = counts of accounts whose `primary_action` is that action. - `strategic_accounts` / `enterprise_accounts` = counts of `segment == "Strategic"` / `segment == "Enterprise"`. - `open_expansion_pipeline` = Σ `expansion_pipeline` (open opportunity amounts) on the board. - `net_revenue_exposure` = `arr_at_risk − open_expansion_pipeline`. - `tenure_risk_direction` = `negative` (lower tenure ⇒ higher churn risk). ## 6. Detailed per-family procedures Read `references/task_families.md` for the step-by-step recipe, field-by-field sourcing, and gotchas for the specific family you matched in step 1. Read `references/policy_codes.md` for the exact policy_code values to emit. A reusable client helper that pulls each metric with the correct rule lives in `scripts/apex_client.py` — import it or read it to mirror the exact logic (billing-snapshot ARR, clean tickets, latest NPS, older-bucket overdue, CRM name-linking, pipeline roll-ups). It is a starting point, not a turnkey solver; always confirm against the specific template. ## 7. Common pitfalls (these silently corrupt answers) - Using `account.billing_arr_current` or `crm_arr` instead of the **posted billing snapshot** for `current_arr`. - Querying billing snapshots with a non-quarter-end `as_of` (returns empty). - Counting raw tickets without removing spam/duplicate/cancelled. - Using `metric.support_ticket_count` where a **clean** ticket count is required (QBR `support_tickets` is the clean count, not the metric field). - Including `1_30` / `31_60` in `overdue_balance` (only 60-days-plus counts). - Treating a past-due renewal as inside the renewal window (it is not; window is 0–90 days in the **future**). - Forgetting `retracted` NPS responses, or emitting `0` instead of `null` for missing NPS. - Summing ARR across **all** accounts for `arr_at_risk` (exclude `low`). - Forgetting to exact-match A/R `customer_name` to a CRM `legal_name` for link status (unmatched legal names like "…Subsidiary LLC" / "…Services" stay `unlinked` with `account_id: null`). - Omitting the `policy_codes` block, or guessing its values instead of using `references/policy_codes.md`.