Retention Risk Radar

# Retention Risk Radar

Turn support conversations into two things a CX leader can hand to other teams: a ranked list of individual accounts showing real signs of leaving or disengaging, and a ranked list of friction cohorts — groups of accounts hitting the same fixable problem, usually the more valuable of the two because fixing one root cause protects every account in that cohort at once.

## First run — make it yours

Before doing any work, interview the user one short question at a time, in this skill's own language, then restate the filled-in bindings for confirmation before running. On later runs, reuse the established bindings unless the user asks to change them.

1. **Business model** — "Is your product subscription/membership (a cancel event exists) or transactional/usage-based (no cancel event, so risk means declining usage)?" This drives {{business_model}} and which exit-language examples to emphasize.
2. **Analysis window** — "What trailing time window should we analyze — long enough to catch recurrence but short enough that 'at risk' means currently?" Sets {{analysis_window}} (default trailing 90 days).
3. **Scope filter** — "Should we scope to a particular product area, account tier, or channel, or analyze everything?" Sets {{scope_filter}}; if none, analyze all.
4. **Usage data source** — "Is there an account-activity data source we should cross-reference to confirm behavioral decline, and if so what does 'activity' mean for your business?" Sets {{usage_data_source}}; if none, proceed signal-only.
5. **Output destination** — "Where should the deliverables go — build both the Google Sheet and the HTML dashboard, or just one?" Sets {{output_destination}}.
6. **CRM link pattern** — "What CRM/helpdesk did this ticket data come from, and what is the conversation link format so we can link every example account back to its real ticket?" Sets {{crm_link_pattern}}; confirm the exact URL shape for the current environment each run.

## The risk rule (transparent and rule-based)

An account is flagged **at risk** if any of:
1. At least one ticket carries **explicit exit-or-competitor language** (subscription framing e.g. "canceling my account"; transactional framing e.g. moving funds/business elsewhere, naming a competitor, "done with this platform" — both count).
2. At least two tickets carry **implicit disengagement language** ("I probably won't use this much anymore").
3. At least two tickets share the **same unresolved friction theme** and are both frustrated-or-worse in tone.

This is deliberately conservative to avoid false positives. `scripts/compute_retention_risk.py` applies this rule; never hand-flag an account or hand-tally a cohort.

For {{business_model}} = transactional/usage-based there is no cancellation event, so "at risk" means declining engagement — rule 1 includes moving assets/business elsewhere and competitor mentions, and usage cross-referencing (below) confirms decline.

## Which issue is actually driving churn

A theme is a **churn driver** only if BOTH: it touches at least 5 accounts, AND its flagged rate is at least 15 percentage points above the overall flagged rate this run (the lift). Each driver also gets a trend read — `emerging`, `worsening`, `steady`, `improving`, or `contained` — based on whether accounts newly hit the theme more in the second half of the window than the first. Below a 14-day window this degrades to `insufficient_window`. An emerging or worsening driver is the single most actionable line in the report. The script computes both; never hand-derive either.

## Analysis engine

Rippit MCP tools: `list_data_sources`, `describe_table`, `describe_column`, `aggregate_table`, `create_worksheet`, `enrich_worksheet` + `get_enrich_status`, `get_report_guide`. Delivery uses whichever Google Workspace MCP connector (Drive/Docs/Sheets) is available — check the live tool list each session. If none is reachable, fall back to the in-chat report plus downloadable CSVs.

## Step 1 — Find the tables

1. `list_data_sources`, then `describe_table` on the ticket/conversation table. Resolve the account id column, a created/updated date column, and any existing sentiment/CSAT column — fresh each session.
2. If {{usage_data_source}} was named, `describe_table` it and note its account-id column and activity metric now.
3. If no activity table exists, say so plainly and proceed on conversation signals alone — output clearly labeled signal-only.

## Step 2 — Enrichment (one batched call)

1. `create_worksheet` scoped to {{analysis_window}} and {{scope_filter}}.
2. `enrich_worksheet`, one batched call per row:
   - `risk_signal_type` (enum): `explicit-exit-or-competitor-language`, `implicit-disengagement-language`, `frustrated-but-no-exit-language`, `no-issue-sentiment-neutral-or-positive`, `unclear`. Include BOTH subscription-style and transactional-style examples regardless of {{business_model}}.
   - `friction_theme` (unspecified_enum): the underlying root cause, independent of tone.
   - `representative_quote` (string): the customer's own short phrase evidencing the call (empty if neutral/unclear).
3. Pilot on ~50-75 rows first if the population is large, spot-check, then run the rest.

## Step 3 — Roll up, flag risk, flag churn drivers

1. `read_table` the enriched worksheet in pages to a local CSV (`account_id,ticket_id,ticket_date,risk_signal_type,friction_theme,representative_quote`).
2. Run `scripts/compute_retention_risk.py` against it — applies the account-level rule, picks each flagged account's strongest quote, rolls themes into cohorts, flags churn drivers (size + lift), reads each driver's trend. Never hand-derive.
3. Cross-check the script's `risk_signal_type` counts against an `aggregate_table` groupBy over the same slice; if they diverge, fix the pull, not the rule.
4. If the population spans <14 days, report `insufficient_window` for every theme.

## Step 4 — Cross-reference usage data (only if {{usage_data_source}} was found + wanted)

1. Pick a pre-window of equal length immediately before {{analysis_window}}; use the analysis window as the comparison window.
2. `aggregate_table` the activity metric grouped by account id, once per window; join into `account_id,pre_activity,post_activity`.
3. Re-run the script with `--usage`; it computes percent change per flagged account and marks `confirmed-by-usage-decline` (default -30%) vs `signal-only`.
4. Never present a signal-only flag with the same confidence as a confirmed one.

## Step 5 — Deliver the report

Call `get_report_guide` first regardless of destination. Build the deliverables named in {{output_destination}} (default both):

### Google Sheet
Build one Google Sheet titled with the window. Tabs: **Summary** (window, scope, usage availability + threshold, totals, flagged counts by confidence tier, top churn driver, and separately the top emerging/worsening driver); **At-Risk Accounts** (account id, ticket count, dominant theme, matched rule(s), confidence tier, quote, most recent ticket date, ticket link column — sorted confirmed-tier first then by ticket count); **At-Risk Cohorts** (theme, accounts touched/flagged, flagged rate, lift, churn-driver flag, trend, explicit-exit accounts, 2-3 example quotes with links — sorted drivers first then by accounts flagged); **Usage Confirmation** (only if usage used). If no Google Workspace connector is reachable, fall back to the in-chat report plus downloadable CSVs.

### Interactive HTML dashboard (executive briefing)
A single self-contained `.html` file, embedded JSON + vanilla JS, no framework/build/server/external requests. Its job is insights and actions absorbable in under a minute — no full 920-row table, no sortable grid. Sections: header (3-4 lines), hero KPI row (accounts analyzed, flagged with rate and confirmed/signal-only split, cohorts found, churn-driver cohorts), **Key Insights & Recommended Actions** (3-6 cards each pairing a grounded numeric finding with ONE labeled action — Quick win / Medium lift / Structural), **At-Risk Cohorts collapsed by default** via `<details>`/`<summary>` (collapsed shows theme, flagged/touched, rate, lift, driver/near-miss badge, trend badge; expanded shows 2-3 example accounts with quotes and CRM links), NO account or usage tables (point to the Sheet), footer (Sheet link, worksheet link, snapshot note, privacy note), light+dark theme via `prefers-color-scheme`.

**CRM linking:** for every example account shown, build a link from its `ticket_id` (or `representative_ticket_id` / `most_recent_ticket_id`) to the real conversation using {{crm_link_pattern}}, confirming the exact link shape for the current environment each run. Never show a bare account id or quote with no way to verify it.

**Save location:** default `~/Downloads/retention-risk-radar-<window-end-date>.html` unless the user names a different location; state the exact path back.

**Privacy:** this file contains real account ids, quotes, and friction detail. Fine to hand to leadership or a deck, but keep to the intended internal audience — don't publish it publicly or upload it elsewhere without explicit user say-so.

### Wrap-up
In the chat reply give the short version regardless of surfaces built — total flagged, the confirmed/signal-only split, the top churn driver (and whether emerging/worsening), plus the Sheet link and HTML path. Note explicitly that a signal-only flag is a lead for outreach, not a confirmed loss. Link the underlying worksheet.

## Principles
- The risk rule is transparent and rule-based on purpose.
- Ticket volume alone is never a risk signal — only tone and recurrence.
- "Churn" means declining usage for transactional products, not just cancellation.
- Never hand-compute the risk flag or cohort rollup — use the script.
- A cohort is often more valuable than the account list.
- "Touches many accounts" and "drives churn" are different claims — require lift AND size.
- The trend read is a heuristic; below 14 days report `insufficient_window`.
- Behavioral confirmation beats sentiment alone when available; keep signal-only and confirmed visibly separate.
- The Google Workspace connector is environment-dependent; confirm reachability and fall back rather than producing nothing.
- The HTML dashboard is a briefing, not a data export — if it needs a sort control or a big table, that belongs in the Sheet.
- Never show an example account with no way to verify it.