Repeat-Contact Analysis

# Repeat-Contact Analysis

## First run — make it yours

Before doing any work, interview the user one question at a time in the 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. **Analysis window** — "What analysis window should I look at? It needs to be long enough for repeat chains to close but recent enough to be actionable (default trailing 60 days)." → {{analysis_window}}
2. **Scope filter** — "Do you want to scope the analysis to a particular issue type, channel, or agent group, or run across everything?" → {{scope_filter}} (optional; if set, filter to it)
3. **Repeat window** — "Within how many days of each other should two tickets about the same issue count as a repeat contact rather than a fresh problem (default 14)?" → {{repeat_window_days}}
4. **AHT** — "Do you have an average handle time in minutes? It's the only way to convert avoidable tickets into hours saved — leave blank to report ticket counts only." → {{aht_minutes}} (optional; never invent one)
5. **Bot exclusion** — "Should bot-only conversations be excluded? A bot handoff followed by a human ticket is a deflection question, not first-contact rework (default: exclude)." → {{exclude_bot_only}}
6. **Output destination** — "Where should the deliverables land? I always produce a working Sheet and a narrative Doc — which Google Workspace destination should I write them to?" → {{output_destination}}

Produce one thing: a **ranked list of fixes, each with the number of tickets it would have prevented** — ordered by avoidable volume, addressed to the team that owns each fix.

The unit of analysis is a **contact chain**: one customer, one underlying issue, two or more tickets. The first ticket in a chain is legitimate volume. Every ticket *after* it is what this skill explains and eliminates.

## The chain rule

Two tickets from the same customer join the same chain when both hold: (1) they concern the **same underlying issue**, not merely the same category; (2) they fall within **{{repeat_window_days}}** of each other. Chain length is the ticket count; **follow-on tickets = chain length − 1** — the only candidates for avoidable volume.

## Not every repeat is a failure (the crux)

Calling every follow-on avoidable produces a number nobody believes. Three classes are **not** first-contact failures: legitimately multi-step processes, a correct answer the customer rejected, and a new development on an existing issue.

Genuine failure classes (only their follow-ons enter avoidable volume):

| Failure class | Meaning | Usually fixed by |
|---|---|---|
| `wrong-or-incomplete-information` | Answer inaccurate or partial | Coaching / KB |
| `closed-before-resolution` | Closed/snoozed while issue live | Coaching / process |
| `no-followthrough-on-dependency` | Handed off, then dropped | Process / dependency SLA |
| `customer-asked-to-repeat-themselves` | Made to resupply info | Tooling |
| `fix-attempted-but-failed` | Remedy applied, didn't work | Product / tooling |
| `answer-not-understood` | Correct, not comprehensible | KB / macro rewrite |

Non-failure classes — `legitimate-multi-step-process`, `correct-answer-customer-rejected`, `new-development-on-same-issue`, `unclear` — are counted and reported on their own line, never folded into avoidable volume.

## "High volume" vs "high repeat rate"

The driver flag is computed at the **issue-category** level, which has real denominators from `aggregate_table`. Enrichment-derived `issue_signature` has no window-wide denominator, so clusters rank by **absolute avoidable volume** and inherit their parent category's rate, lift, and driver flag as labeled context. An issue category is a **repeat driver** only if BOTH: at least **{{min_chains}} chains** (default 5), and repeat rate at least **{{lift_threshold}} above the overall window rate** (default +10 pts), where repeat rate = follow-on tickets ÷ total tickets.

`scripts/repeat_contact.py` computes chains, rollups, the driver flag, and the trend read. Never hand-tally any of them.

## Analysis engine

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

## Step 1 — Confirm scope

- **Analysis window**: {{analysis_window}} (long enough to contain {{repeat_window_days}} twice or the trend read degrades).
- **Scope filter**: {{scope_filter}} if set.
- **AHT**: {{aht_minutes}} in minutes if supplied — never invent one; without it, report ticket counts only.
- **Bot-only conversations**: {{exclude_bot_only}}.

## Step 2 — Find the table and resolve columns fresh

1. `list_data_sources`, then `describe_table`. Read the Data coverage block; if the window reaches before the sync floor, say so and analyze only covered data.
2. Resolve, never reuse: customer id, created-at, issue category and sub-issue columns, ticket status, any resolution-acceptance/CSAT column.
3. Note the customer id column's type. If it is an **array** column, a row can carry a list — take the first non-empty value per row and say so, rather than silently dropping multi-value rows.
4. `aggregate_table` a plain row count and compare it against the 10,000-row worksheet cap now.

## Step 3 — Detect chains BEFORE enriching

1. `read_table` with a narrow projection (ticket id, customer id, created-at, issue category, dominant sub-issue), paging via `pagination.nextOffset`. Write to a local CSV.
2. Run `scripts/repeat_contact.py --mode chains` — groups by customer, orders by date, joins consecutive tickets sharing a coarse issue key within the repeat window.
3. This pass is **recall-oriented**; enrichment confirms. Do not tighten it — a missed chain is unrecoverable, a false chain is cheaply discarded.
4. If the chain-member list exceeds 10,000, narrow the window or scope and re-run — **never sample it**.

## Step 4 — Enrich only chain members (one batched call)

1. `create_worksheet` filtered to the chain-member ticket ids.
2. `enrich_worksheet`, one batched call:
   - `repeats_earlier_contact` (enum): `yes-same-underlying-issue`, `no-different-issue`, `no-this-is-the-first-contact`, `unclear`.
   - `first_contact_failure_reason` (enum): the six genuine failure classes plus `legitimate-multi-step-process`, `correct-answer-customer-rejected`, `new-development-on-same-issue`, `unclear`. State plainly that a correct-but-unwelcome answer and a genuinely staged process are **not** failures.
   - `issue_signature` (unspecified_enum): the specific underlying problem, normalized across customers.
   - `fix_owner` (enum): `agent-coaching`, `kb-or-macro-content`, `process-or-dependency-sla`, `tooling`, `product`, `policy-comms`, `no-fix-needed`.
   - `evidence_quote` (string): the customer's own words showing they were back about the same thing (empty if not a repeat).
3. Pass the coarse issue key and prior ticket's date via `contextColumnIds`.
4. State the limitation honestly: the failure judgment comes from the follow-on transcript; where the customer doesn't re-narrate, `unclear` is the correct answer.
5. Poll `get_enrich_status` until `done: true`. Pilot on ~50-75 rows first if the population is large, spot-check the split, then run the rest.

## Step 5 — Roll up, flag drivers, quantify

1. `read_table` the enriched worksheet in pages to a local CSV.
2. `aggregate_table` total tickets per issue category for the window — the script needs real denominators; do not infer from the chain population (100% by definition).
3. Run `scripts/repeat_contact.py --mode rollup`, passing the enriched CSV, issue totals, and `--aht-minutes` only if {{aht_minutes}} was supplied.
4. Cross-check: the script's failure-class counts must match an `aggregate_table` groupBy on `first_contact_failure_reason`. A mismatch means the pull was scoped differently — fix the pull.

## Step 6 — Deliver

Call `get_report_guide` first. Ship **two deliverables every run, both required**, to {{output_destination}}:

1. **A Google Sheet** — the working fix list. Tabs: **Summary** (window, scope, tickets analyzed, chains, follow-ons, avoidable follow-ons and % of window volume, hours if AHT supplied, top fix, top emerging driver); **Fix List** (one row per issue signature × failure class: avoidable tickets, chains, customers affected, fix_owner, trend, 2-3 example quotes with ticket links, plus parent category's rate/lift/driver flag clearly labeled as the category's — sorted by avoidable tickets descending); **Issue Categories** (category-level driver view); **Not Avoidable** (non-failure classes with volumes); **Chains** (raw chain table).
2. **A Google Doc** — the narrative readout, even when only the analysis was asked for. Sections: findings-led title, metadata line, headline, the funnel (window → chain members → follow-ons → confirmed → avoidable), issue categories table with driver flag (say so plainly when nothing clears the bar), the top fix in customers' own words, what was deliberately excluded, limitations, recommended actions (Quick win / Medium lift / Structural, each tied to a number), source data links.

Build ticket links from the source helpdesk's URL shape; prefer workspace-agnostic forms and never carry over a workspace id from a previous run. Create the Doc by uploading **real HTML** with `contentMimeType: "text/html"`; do not HTML-escape the angle brackets of real markup. Verify after creating by exporting as `text/plain`; if broken, recreate and trash the bad copy. Build an HTML dashboard only if asked.

In the chat reply, lead with **avoidable follow-ons as a share of window volume** and the single top fix. Link the worksheet so they can read the raw conversations.

## Principles

- The deliverable is a fix list, not a repeat-rate metric.
- The first ticket in a chain is legitimate volume; only follow-ons can be avoidable.
- Not every repeat is a failure — report exclusions; the Not Avoidable tab is what makes the number credible.
- Always ship both the Sheet and the Doc.
- Detect chains before enriching; never sample the chain population — narrow the window instead.
- Recall first, precision second. Real denominators come from the full window.
- "High volume" is not "high repeat rate" — require both cohort size and lift.
- Never invent an AHT; never hand-compute chains, rollup, or lift.
- Coaching is the most over-assigned fix owner — check for a missing KB article, broken tool, or unanswered dependency first.
- The output names teams, not people.