ApexCloud Retention Operations — reusable rules and verified conventions for producing renewal-risk, QBR, receivables/pipeline, churn-model, and retention-board answers from the data API.
Installation
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
ApexCloud Retention Operations — reusable rules and verified conventions for producing renewal-risk, QBR, receivables/pipeline, churn-model, and retention-board answers from the data API.
Reusable rules for answering the five task archetypes served by the ApexCloud Retention Operations API. Every rule below was checked against the train judge; conventions marked VERIFIED produced correct deterministic fields (train_003 reached 1.0; train_002 deterministic block correct). Items marked BEST-GUESS could not be fully recovered from scalar feedback and should be applied with the stated logic.
Environment & API
Base URL: read from ENV_URL.txt (data API). Judge path from JUDGE_PATH.txt. All endpoints are read-only JSON.
current_arr = billing_arr from the posted billing snapshot whose as_of == the assessment date. Snapshots are quarterly; the assessment date (e.g. 2026-06-30) always matches a quarter-end as_of.
Do NOT use account.crm_arr or account.billing_arr_current (the account's billing_arr_current equals the LATEST/2026-12-31 snapshot, not the assessment-date one). Example: globex_north assessment 2026-06-30 → snapshot billing_arr=1,176,600.70 (NOT billing_arr_current=1,188,000.00).
Clean ticket count = /api/accounts/<id>/tickets rows filtered to the analysis period (created_date within [start, end]), EXCLUDING is_spam=true, is_duplicate=true, and status="cancelled". Ticket statuses observed: open, closed, cancelled.
This applies to BOTH the clean_ticket_count field (risk archetypes) AND the support_tickets field (QBR archetype): use the cleaned /tickets count, NOT metrics.support_ticket_count (which is the raw count including spam/dup/cancelled). Example: globex 2026-06 raw=3, clean=1. (/exports/account_metric_extract.csv exposes a clean_ticket_count column that equals the cleaned endpoint count — use it to cross-check.)
SLA: sla_compliance (1 dp) comes from the account metrics endpoint per month; source label sla_report.
3. NPS VERIFIED
Latest valid NPS in the analysis period: from /api/accounts/<id>/nps, drop retracted=true and score == -1 (invalid), then take the latest by response_date within [start, end].
Per-month nps_score in the metrics endpoint already equals the latest valid /nps response for that month — either source works. Source label nps_survey.
4. Overdue receivables VERIFIED
overdue_balance = ar_aging.61_90 + ar_aging.90_plus for the as_of date given in the prompt (the "older aging buckets"). 1_30 and 31_60 are current, NOT overdue.
A/R rows are keyed by customer_name (legal name); match to accounts by legal_name.
5. CRM / A-R matching VERIFIED
Link an A/R customer_name to a CRM account by exactlegal_name equality only. link_status="linked", account_id=<id>.
Subsidiaries / name variants do NOT link (account_id = null, link_status="unlinked"). Verified unlinked examples: "Globex North Subsidiary LLC" (vs "Globex North Holdings LLC"), "Valence Payment Services Canada" (vs "...LLC"), "North Star Finance Services" (vs "Northstar Finance Group Inc."), "Quartz Insurance Claims Ltd.", "Riverbend Bank Foundation". Aliases in account_aliases are NOT used for linking.
overdue_followups sorted by customer_name ascending.
6. Pipeline VERIFIED
Window = opportunities whose close_date falls in the analysis date range. "Region: all regions" → do NOT pass region=all (returns empty); use the unfiltered /api/opportunities list and filter by close_date client-side.
Closed Won → won; Closed Lost → lost; all other stages (Discovery, Prospecting, Negotiation, Proposal / state="open") → open.
won_revenue = sum amount of won; open_pipeline = sum amount of open; win_rate_pct = won/(won+lost)*100 (1 dp). top_open_product_line = product line with the largest total open amount.
7. HR & event ops context VERIFIED
HR "all regions": region=all (and region=na/emea/apac/latam) return EMPTY; region=North America is also rejected by the filter. Query /api/hr/summary?quarter=YYYY-QN with NO region to get all 4 regional rows, then sum across the 4 regions (North America, EMEA, APAC, LATAM). hr_headcount = sum headcount; unpaid_claims_total = sum unpaid_claims_amount.
Event: /api/events/performance?event=<id>&quarter=YYYY-QN returns one row; use event_orders and event_revenue directly (NOT completed_orders/product_revenue).
Sort rule (from spec): score desc, current_arr desc, account_id asc. The tiebreakers (arr desc, account_id asc) imply the score is a small integer that ties frequently — use count of applicable risk-increasing reason codes as the score (do NOT count expansion_offset or clean_billings, which are reducing/informational).
reason-code triggers (verified thresholds, apply per account over the analysis period):
renewal_window — renewal_date within [assessment−30d, assessment+90d] OR lifecycle_status == "renewal_risk".
expansion_offset — open expansion opportunities exist in the period (sum > 0); informational, reduces effective risk.
clean_billings — no overdue and healthy billings (informational).
risk_level thresholds (BEST-GUESS, could not fully verify): critical ≥ 4 reasons, high = 3, medium = 2, low ≤ 1. (Alternative: critical ≥ 5.) Demote by one level when expansion_offset applies and the account is borderline.
CAUTION: the exact integer risk_score and the precise set/level per account could NOT be recovered from scalar judge feedback (train_001/train_005 stayed low). Treat the ranking+levels as the least-certain part; the deterministic per-account fields (current_arr, overdue_balance, latest_nps, clean_ticket_count, expansion_pipeline) ARE reliably computable.
Due-date calendar is given verbatim in board prompts (e.g. collections=2026-07-15, technical=2026-07-18, renewal_save=2026-07-22, executive_qbr=2026-07-29, nurture_monitor=2026-08-05). next_touch_due_date = the date for the account's chosen primary_action; followup_calendar echoes the full mapping. For receivables-only tasks the follow-up due date is the single date stated in the prompt (e.g. 2026-10-15).
NOTE: changing primary_action/reason_codes did NOT move the judge score on the block-scored board archetype (train_005); the board appears scored as a block requiring correct order+levels+actions+reasons simultaneously. Prioritize getting the ORDER and risk_levels right.
QBR archetype soft fields (review_owner, needs_technical_signoff, agenda_topics) are best-guess: review_owner likely customer_success for healthy accounts / solutions_engineering when a technical SLA dip (<95) warrants signoff; needs_technical_signoff true if any month SLA < 95; agenda_topics = 4 ordered from {partnership_overview, q2_metrics, performance_highlights, q3_initiatives, technical_recovery, commercial_expansion}. Changing agenda did not move the QBR score.
OneHotEncoder(drop='first') is REQUIRED — drop=None yields 91.7% (wrong band). Do not use drop='if_binary'.
predicted_churn_probability = predict_proba(...)[:,1] to 3 dp. With default sklearn settings the top-5 candidate probabilities (example set) are stable to 3 dp. Rank candidates by probability desc.
outreach_action / reason_code per ranked candidate (BEST-GUESS, principled but unverified): compute each feature's contribution to the log-odds (coef × transformed_value) and label the dominant risk-increasing contributor: InvoicePastDue_Yes→overdue_receivable/collections_followup; low tenure→low_tenure_high_churn/renewal_save; SupportTickets90d→sla_degradation/technical_recovery; UsageTrendPct→usage_decline/technical_recovery; NPSLast→nps_drop/nurture_monitor.
IMPLEMENTATION PITFALL: the contribution array is computed in the candidates DataFrame's ORIGINAL row order; if you sort candidates by probability before assigning reasons, you must carry the per-row contributions with the rows (merge back on customer_id) — otherwise reasons are assigned to the wrong accounts.
cohort_checks: past_due_shortlist_count = count of top-5 with InvoicePastDue=='Yes'; low_tenure_shortlist_count = count of top-5 with tenure < 24; average_probability_top5 = mean of the 5 probabilities (3 dp, computed from full precision not the rounded values).
11. Net revenue exposure & segment summary (board archetype) BEST-GUESS
strategic_accounts / enterprise_accounts = counts by segment among the reviewed account list.
arr_at_risk = sum of current_arr for accounts whose risk_level is critical or high.
open_expansion_pipeline = sum of open expansion opportunity amount (close_date in period, stage not Closed Won/Lost) across the reviewed accounts = sum of per-account expansion_pipeline.
net_revenue_exposure = BEST-GUESS: arr_at_risk − open_expansion_pipeline (expansion offsets at-risk ARR). Alternative tested (arr_at_risk alone) gave no score delta because the segment block is scored jointly with the board — verify on a passing board first. Another plausible formula: arr_at_risk + total_overdue − open_expansion_pipeline.
policy_codes are NOT score-invariant: they encode the specific convention/rule applied, and the correct option CAN be recovered and matters (train_003 went 0.85 → 1.00 solely by swapping to the correct policy-code set). Each field offers 3 pipe-options (e.g. RS-2|RS-6|RS-9); pick the one matching the rule you actually applied.
Verified correct set for the receivables/pipeline archetype (train_003): receivable_trigger_code=RCP-7 (overdue = 61_90+90_plus), crm_match_code=CM-5 (exact legal_name, subsidiaries unlinked), pipeline_window_code=PW-6 (close_date window), followup_scope_code=FS-4 (all overdue clients). In train_003 the correct option was the 2nd of each triple.
For other archetypes the correct option is rule-dependent (not always the 2nd). State the RULE each code represents and pick the matching option:
Because the judge returns only a scalar and policy blocks are scored jointly, do NOT spend many rounds guessing codes blindly; apply the rule-matching logic above and only verify if a near-complete answer still has a residual gap.
Using billing_arr_current or crm_arr instead of the assessment-date billing snapshot.
Using raw metrics.support_ticket_count instead of the cleaned /tickets count.
Counting 1_30/31_60 as overdue; only 61_90+90_plus are overdue.
Linking subsidiaries/aliases to accounts (only exact legal_name links).
Passing region=all to HR/opportunities (returns empty) — use no region filter and sum/filter client-side.
Using OneHotEncoder(drop=None) for the churn model (wrong accuracy band); must be drop='first'.
Rounding average_probability_top5 from already-rounded probabilities instead of full precision.
Assigning churn-model feature contributions to the wrong candidate after sorting by probability (merge contributions on customer_id before/after sort).
Forgetting policy_codes encode rules and ARE recoverable (not invariant) — match the option to the rule applied.
