Lingering Issue Finder

---
name: lingering-issue-finder
description: "Find the support issues that never spike but never stop — persistent, low-per-agent contact drivers spread so thinly across your agents that nobody escalates them, while they permanently inflate the ticket rate. Screens the full population on share-of-day persistence metrics (rejecting event spikes and back-office queues), then enriches the top themes to produce theme -> underlying issue -> deflection opportunity, oriented at preventing the contact entirely (primary) and bot resolution before a human (secondary). Trigger on: \"lingering issues\", \"what's quietly driving ticket rate\", \"steady drumbeat\", \"day over day\", \"things that never escalate\", \"underlying issues we're missing\", \"baseline ticket drivers\", or a recurring request to reproduce this analysis. Distinct from a capacity-planning deep-dive on one known category, a chatbot-performance review, and an escalation finder (things that DID escalate) — this one hunts the opposite shape: the issues too diffuse to ever escalate."
---
# Lingering Issue Finder

Spikes take care of themselves. A surge event gets escalated, war-roomed, fixed. This
skill hunts the **opposite shape**: an issue producing a steady drumbeat of contacts every
single day, spread across so many agents that no individual agent sees enough of it to
escalate, permanently inflating the ticket rate.

Two-stage design:
- **Stage 1 — persistence screen.** Structured columns only, no enrichment, full
  population. Classifies every subcategory and rejects spikes and back-office queues.
- **Stage 2 — enrichment.** ~500 rows per surviving theme, producing *theme -> underlying
  issue -> deflection opportunity*.

Goals are **ordered**, and the report must reflect the order:
1. **Primary — prevent the contact from ever needing to happen.**
2. **Secondary — if it happens, let the chatbot/AI resolve it before a human does.**

If {{track_definitions}} covers two contact-rate measures (all contacts that touch the
company; contacts that reach a human), report both as **two parallel populations**.

## First run — make it yours

**Look first.** Before asking anything, call `list_data_sources` and `describe_table` on
the most relevant source. Report what you find: the candidate case/conversation table, its
date column, a category and subcategory column, an agent/assignee column, a comment-count
column, and any bot-outcome column. Note whether subcategory columns are array-typed
(grouping an array explodes into combination groups — query one label at a time). Note any
floor date before which bot-side fields are null. If the data lacks a persistence signal
(a per-day timestamp) or a way to distinguish human-touched from bot-handled contacts, say
so plainly and ask how to proceed.

**Then ask, one at a time, leading with what you found and proposing a default:**
1. **{{data_source}}** — confirm the case table and the specific columns for date, category,
   subcategory, agent, comment count and bot outcome, substituting the real names you found.
2. **{{scope_window}}** — how long a rolling window, ending on the last complete day? Drop
   the final partial day; assert your BETWEEN range returns the exact day count you expect.
3. **{{track_definitions}}** — one contact-rate measure or two (total-contact including bot,
   and human-touched)? If two, get the filter that defines each track and its own
   category/subcategory columns; parent category can differ per track for the same label.
4. **{{scope_exclusions}}** — which categories are out of scope, and which subcategories are
   carved back in despite an excluded parent? Express as `or_filters` (a NOT IN branch plus
   an IN carve-back branch) and assert the carve-backs are present.
5. **{{anti_targets}}** — labels to reject regardless of metrics (known spike labels, junk,
   duplicate, processing buckets). Watch for alternate spellings of the same label.
6. **{{persistence_thresholds}}** — the reference thresholds below or your own. Re-check that
   the detrended-CV cutoff still falls in a natural gap in the observed distribution.
7. **{{backoffice_cutoff}}** — below how many distinct agents per day is a label internal
   work, not customer contact?
8. **{{issue_taxonomy}}** — derive per-theme underlying-issue buckets from a pilot, or use a
   fixed taxonomy? The logic scores each theme's rows against its own bucket set.
9. **{{enrichment_budget}}** — rows per theme and total budget (stay under the sheet cap).
10. **{{output_destination}}** — a local HTML one-pager and metrics JSON, or present in chat.

Restate the filled-in bindings for confirmation, then run. On later runs, reuse the
established bindings unless asked to change them.

## The three rules that make this work

1. **Never use raw counts. Share-normalize everything.** Queues grow; on raw counts a
   lingerer and an event spike barely separate, but on share-of-day they separate sharply.
2. **Query array-typed subcategories one label at a time.** Grouping an array explodes into
   combination groups; a naive top-N badly undercounts. Use the IN any-element reading
   consistently.
3. **Freeze the thresholds and the taxonomy.** The gate is calibrated. Do not re-tune it to
   make a run look better; a threshold change invalidates every run-over-run delta. If a
   change is genuinely needed, note the version bump in the report.

## Stage 1 — the persistence screen

Per label, from daily counts:
- `mean_share` — mean of (daily count / daily in-scope track total) x 100.
- `detrended_cv` — **the smoothness gate.** Fit a least-squares line to the daily share
  series, then stdev(residuals) / mean_share. Do NOT gate on raw `share_cv` or `burstiness`
  — they double-count trend and mistake a smooth riser for a spike.
- `max_day_pct` — largest single day / whole-window volume. Only treat single-day dominance
  as an event when the level came back **down** (share_drift < ~1.15). A spike reverts; a
  riser does not.
- `day_coverage`, `tickets_per_agent_per_day`, `share_drift`, `weekend_lift`.

Also carry descriptive columns into the report: parent category (from the same track as the
rows), `n`, `distinct_agents`, `agents_exposed` (never the divisor), `avg_public_comments`
(a cost signal, not a persistence signal), and paired bot volume/containment. Compute all
measures for one label in a single `aggregate_table` call.

**The gate — classify, then rank by volume** (first match wins), using {{persistence_thresholds}}:
1. Intermittent (reject): day_coverage < 1.0.
2. Event-driven (reject): detrended_cv >= high cutoff (~0.35).
3. Event-driven (reject): max_day_pct >= ~10% AND share_drift < ~1.15.
4. Worsening lingerer (top priority): passes 1–3, detrended_cv <= low cutoff (~0.25),
   mean_share above floor, share_drift >= ~1.15.
5. Weekend lingerer: same, weekend_lift >= ~1.3.
6. Lingering: same, neither flag.
7. Borderline: detrended_cv in the middle band — surface, never silently drop.
- Back-office (route out, report separately): median agents/day <= {{backoffice_cutoff}}.

Reject the labels in {{anti_targets}} regardless of metrics. Rank surviving lingerers by
mean_share and take the top ~10. Every number comes from a **visible Python snippet via
Bash** over daily grids — never mental math.

**Manual overrides.** The screen is a filter, not an oracle. Two legitimate override paths,
both labelled as overrides in the report:
1. *Borderline promotion.* A detrended_cv in the middle band is choppy, not event-driven.
   Promote as-is, badge it, print its dcv.
2. *Stratified sampling of an event-driven label.* If a rejected label plausibly has a floor
   under its surge, split the window: `spike day := daily share > 1.3 x the label's median
   daily share` (share, never raw count); base stratum := all other days. Build two
   worksheets, enrich both, and compare the underlying-issue mix on quiet vs surge days.

## Steps

**Step 0 — Ground truth.** `list_data_sources` -> `describe_table`. Confirm the table,
columns, and any bot-field floor date. Resolve the window to the last complete day and state
it in one line. Run the scope-filter reconciliation (excluded categories + carve-backs must
sum to the filtered total).

**Step 1 — Run the screen.** Build the candidate universe per track (top ~40 by volume),
pull per-label daily series one label at a time plus the daily track denominator, compute
the metric table in Python, print the full ranked classified table.

**Step 2 — Select and confirm.** Show the selected lingerers, the borderline band, the
rejected spikes with their detrended_cv and max_day_pct, and the back-office queues routed
out. **Confirm the lineup before spending on enrichment.** Expect edits; that is the gate
working. When a rejected label is named, run the base/spike split before agreeing it has an
underlying component.

**Step 3 — Pilot the transcript path.** One ~300-row pilot per enriched track with a
`root_cause_raw` string column; check the "no usable detail" rate. The pilot's free-text
rate and the full run's fixed-enum `not enough info` rate measure different things — expect
the enum's refusal rate to be several times the pilot's, and do not read it as a regression.
**Keep every sheet; report on the answerable subset** (rows − not_enough_info), print the
denominator on each card, and itemise every sheet over the bar in the caveats with its cause.
Only pass short, front-loaded values as enrichment context — a long transcript is silently
truncated (~250 chars) before the customer speaks; use `read_conversations` for full bodies.
Enrich only the human-touched track: a bot chat that fails to contain becomes a case, so the
failures are already there, and case rows carry the preceding bot exchange natively. The
total-contact track supplies volume and containment from a structural outcome column over
the full population — never sample what a structural column already counts.

**Step 4 — Enrich: one worksheet per theme (per stratum), ~{{enrichment_budget}} rows each.**
`create_worksheet` with `where subcategory IN ["<label>"]` so each theme gets guaranteed
rows; a pooled worksheet lets the largest theme dominate and truncates arbitrarily at the
cap. A stratified theme gets two worksheets (base + spike) at a slightly lower row count.
One `enrich_worksheet` call per sheet — columns are free, rows are the cost. Each theme gets
its own `underlying_issue` enum, frozen after the pilot, from {{issue_taxonomy}}. Tag
generously: `issue_is_dk_defect` (system/process defect · working as designed · customer
error · third-party · unclear), `preventable_upstream` (product fix · comms/education ·
policy · inherent · unclear), `prevention_specifics`; `self_serve_answerable`, `bot_blocker`
(needs account data bot lacks · needs an action bot can't take · needs human judgment · bot
could do this today, not built · customer refused bot · n/a), `deflection_lever`; plus free
over-tags (`repeat_contact`, `first_contact_resolved`, `customer_sentiment`, `comp_requested`,
`financial_impact_bucket`, `named_product_surface`, `representative_quote` <=20 words,
`tagging_confidence`). Poll `get_enrich_status` to done. Carry returned column **ids** forward.

**Step 5 — Verify.** Spot-check 10–15 rows with `read_conversations`, weighted to the
defect claim. Sweep every trend-bearing column for stray off-enum labels and fold them.

**Step 6 — Quantify, then validate in chat.** Per theme: underlying-issue distribution
scaled to population, preventable_upstream rollup, self_serve_answerable x bot_blocker, and
containment from the total-contact track. **Population-weight every rollup; never pool raw
sample counts** — sampling is deliberately non-proportional, so pooling over-weights small
themes. Round only at display. State which denominator every percentage uses on the same
line: answerable (rows − not enough info) for underlying-issue share, determinate (rows −
unclear) for preventable, determinable (rows − no bot visible − not enough info) for bot.
Scope the bot number per theme, then population-weight. Surface it all in chat with the math
and let the stakeholder adjust before any HTML.

**Step 7 — One-pager.** `get_report_guide`, then a self-contained HTML file (no CDN, inline
CSS, CSS-only bars). Sections: headline -> **"Start here: the five areas to attack"** (above
every table, written in Simplified Technical English, one idea per sentence, active voice,
~20 words max) -> KPI tiles -> **why these aren't escalating** (the tickets/agent/day chart)
-> the persistence profile table -> per-theme underlying issues -> prevention opportunities
(primary) -> bot-deflection scoped to the answerable subset (secondary) -> what we correctly
ignored (the spikes, with burstiness shown) -> methodology & caveats.

Group the plays into ~five *areas* (units of decision) ranked by contacts removed; build area
sizes from the plays' own (theme, label) pairs deduplicated within the area, and assert the
prevention areas' pairs are pairwise disjoint before printing their sum as coverage. Render
the bot ceiling as **two numbers — today, and with account-data access** — since exactly one
blocker value ("needs account-specific data the bot lacks") is an architecture choice rather
than a fact about the work. Rank the prevention section as numbered *plays* (named fixes
built from specific underlying-issue buckets that may span themes); print the buckets under
each play, say they are not additive, and compute every number in prose (never typed).

**Every screened label must appear with an explicit disposition** — SELECTED (clean pass ·
borderline override · stratified), borderline-not-selected, REJECTED (with reason), or
deselected by the stakeholder. Assert the counts sum to the label universe. Render a
persistence profile table for selected labels sorted by n descending, with a matching
rejected-spikes table using identical columns directly below. Persist the full metric table
and a headline block to a metrics JSON per {{output_destination}}.

## Traps (each cost real calls to find)

- The transcript column name may lack a `searchable_` prefix — verify against describe_table.
- Two record types can share one row space (bot chat vs human case) — always split on the
  interaction-type column.
- There may be **no join key** between a bot interaction and the case it spawned — treat the
  two rates as parallel populations measured identically, never a per-record funnel.
- Only part of bot-chat rows carry a subcategory — the total-contact denominator must be
  subcategory-non-null rows, or every share is deflated.
- Grouping an Array(String) column explodes into combination groups — query one label at a
  time; NOT IN on an array excludes if any element matches, and empty arrays pass it.
- The scope filter is an OR with a carve-back, not a plain exclusion — a flat NOT IN silently
  drops carved-back subcategories.
- Always drop the final partial day (it restates upward). `BETWEEN` cuts date_to at midnight
  and drops the final day — pass end + 1 day and assert the day count.
- Bot-outcome/attribute fields may be chat-only (null on case rows) with a floor date.
- `pivot_table` and `where` on a nonexistent column silently return wrong/all rows — verify
  every column id; be suspicious of a filter returning exactly the unfiltered count.
- Use `max`, not `avg`, to prove a column is empty. `read_table` truncates strings at 255
  chars — never diagnose content from it; use `read_conversations`.
- Never draw a plain random sample from an event-driven label — split on share, base vs spike.
- The unscoped per-label series and the scoped worksheets produce two different totals — never
  mix them in one arithmetic statement; footnote the delta.
- `sampleRows` sometimes returns one short — read the count back and scale on the delivered
  number. Assert every transcribed distribution sums to its row count.
- The representative-quote column leaks bot scripts and agent recaps — hand-pick quotes that
  ship; never present raw output as customer voice.
- A near-empty lever (agent tooling, policy change) is a finding, not a bug — keep the option
  so its emptiness stays evidence.
- A superlative is a claim: sort the dict before writing it. A vague quantifier hides an
  arithmetic obligation — compute the coverage or cut the clause.

## Verification checklist

- Metric sanity: known injury/settlement/event spikes must be rejected; expect the top
  volume driver to be a genuine lingerer. A high-containment label ranks high on volume, low
  on opportunity.
- Scope: carve-backs present, other excluded subcategories absent, no back-office queue in the
  top 10, reconciliation adds up.
- Population reconciliation: per-label daily counts sum to the window total.
- Persistence profile complete for selected and rejected labels, every column populated.
- Stratified overrides: base + spike sum to the label total; spike days are dates; base
  stratum large enough to enrich.
- Enrichment quality: pilot no-usable-detail <=8% per track; expect the frozen enum's refusal
  rate higher and handle per Step 3.
- Arithmetic: every persistence row satisfies cases/agents/days == printed rate; every
  distribution sums to its row count; disposition counts sum to the label universe; no count
  or percentage typed by hand in the HTML.
- Stage-2 shape: prevention should outrank bot deflection by a wide margin; if the bot ceiling
  exceeds preventable share, check for pooled raw counts.

## Run log

Keep per-run baselines (window, scoped n, top lingerer, overrides, traps found) so deltas
mean something. The metrics JSON holds the full labelled table with dispositions.