Subcategory Root Cause & Top Paths to Fix

---
name: rippit-category-drilldown
description: "Build a repeatable drill-down of one support category or subcategory from conversations read through Rippit: ask which category to dive into, discover and freeze a 2-4 level taxonomy (for example product line -> stage -> what went wrong -> root cause) with a who-fixes-it owner on every row, enrich a baseline, then refresh it weekly or for any window. Output is one local HTML page (one tab per run, a biggest-paths-to-fix table, one-day spike flags, and a new-and-rising check), plus a metrics JSON with gates and deltas. Same frozen prompts every run, so a change is a real change and not a new prompt. Use when someone asks \"what's inside <category>\", \"drill into <subcategory>\", \"root causes of <category>\", \"who should fix <category> contacts\", \"weekly <category> drill-down\", or \"what's new or rising in <category>\"."
---
# Category Drill-down

Takes one category of support contacts (a category, subcategory, queue, tag or intent the user already has) and answers three questions about it, the same way every time:

1. **What is inside it**, as a tree: 2-4 levels, broadest first, down to a root cause.
2. **Who could fix each part**: every row carries a who-fixes-it bar (owner groups the reader recognises).
3. **What changed**: one tab per run, deltas against the last run and the baseline, and a new-and-rising check that flags a new issue or a new named program, policy or product the week it appears.

**Its only value is consistency.** The prompts are frozen after setup and loaded from a file every run, so a week-on-week move is a real move and not a reworded prompt. The rules below exist to protect that.

## First run — make it yours

**Look first, before asking anything.** Inspect what the user actually has connected:
1. `list_data_sources` to list available sources.
2. `describe_table` on the most relevant one(s); read the **Data coverage** block for the sync window, and note which columns hold a category/subcategory/queue/tag/intent (enum or low-cardinality text), a timestamp, an id/external-id column, and any channel/bot field. If no category-like column exists, say so plainly and ask how to proceed rather than running the interview into a dead end.

**Then ask, one question at a time, leading with what you found:**
- {{data_source}}: "Your workspace has these connected sources — which table holds the support contacts you want to drill into?"
- {{category_selection}}: show the top values of the category-like columns you found and ask "Which category or subcategory do you want to drill into (one value, several, or a parent plus children)?"
- {{baseline_window}}: "What window should the baseline cover?" (default: the last 4 complete weeks)
- {{week_starts_on}}: "Which weekday should weekly runs start on?" (default Monday)
- {{channel_scope}}: if the table has a channel/bot field, "Include bot-only conversations, or only ones a person worked?"
- {{taxonomy_levels}}: after the discovery pass, propose 2-4 levels broadest-first down to root cause. The author's example used product line -> stage -> what went wrong -> root cause. Ask "What levels fit your category?"
- {{owner_groups}}: "Which 3-5 owner groups (functions that could have prevented the contact) should the who-fixes-it bar roll up into?"
- {{output_destination}}: "Where should the local output folder live?" (default `~/rippit-drilldowns/<category-slug>/`)

After the interview, restate the filled-in bindings for confirmation, then run. On later runs, reuse the established bindings unless the user asks to change them.

## Files

| Path | What |
|---|---|
| `templates/taxonomy-template.json` | Starting point for a new category: every key the scripts read, with notes. Filled in with a fictional example |
| `scripts/taxonomy.py` | `check`, `pilot`, `freeze`, `payload`, `where`, `discovery`, `hash`. Builds every enrich payload from the taxonomy file |
| `scripts/grab.py` | Saves a tool result to a file verbatim from the local session transcript, so cuts are never retyped |
| `scripts/metrics.py` | Shares, per-level-1 scorecards, gates and deltas for one run -> `metrics.json` |
| `scripts/emerging.py` | New and rising check against every earlier run -> `emerging.json` + an HTML section |
| `scripts/drill.py` | The drill-down for one run: a tab (`--tab-dir`) or a standalone page (`--out`) |
| `scripts/tabs.py` | Puts every saved tab into one page. Only assembles, never recomputes |
| `scripts/retest.py` | Same-rows retest agreement, written into the taxonomy outside its hash |
| `scripts/common.py`, `page.css`, `drill.js` | Shared helpers and page styling |
| `examples/make_demo.py` | The whole pipeline on synthetic data, no Rippit calls. Run it once to see the output |

Scripts need Python 3.9+ and nothing outside the standard library. Run them with `python3 <skill dir>/scripts/<name>.py`.

## The rules — do not break them

1. **Prompts are loaded, never typed.** Every `enrich_worksheet` payload comes from `taxonomy.py payload`. Never paraphrase an instruction or reorder, rename or add an enum value by hand. Once frozen, the taxonomy file is never edited; a change is a copy to `-v2.json` with a new hash, and v1 and v2 numbers only compare on unchanged columns.
2. **Cuts are saved, never retyped.** Save every `aggregate_table` / `read_table` result to its file with `grab.py` (or another verbatim method). Every number on the page comes from a saved cut.
3. **Compare shares, not counts, against a sampled run.** A baseline over 10,000 rows is a sample; weekly runs are usually a census. Counts compare week to week; against a sample, only percentages do.
4. **No cause without a transcript read in this run.** A spike, a new issue or a new name gets "what it is" only from conversations read with `read_conversations` in this run. Otherwise it says "not read yet", or "read N, cause not visible". A one-week jump that goes away is an **"unexplained one-week spike"** unless someone confirms the cause. Never write "possible incident" or guess.
5. **One counting rule.** The population is `taxonomy -> population.where`, filled in by `taxonomy.py where`. It never changes between runs. If the user wants a different population, that is a new taxonomy.
6. **Nothing leaves the machine.** The output is a local HTML file. Don't upload, post or share it unless the user asks.

---

## Mode 1 — Setup (first time for {{category_selection}})

Run this once per category. It takes one short discovery run, a pilot and a baseline. Ask the interview questions above with `AskUserQuestion` where it helps, and keep going once they're answered.

### S1. Pick the category

1. `list_data_sources` (use `search` if they named the dataset) -> pick {{data_source}} with them.
2. `describe_table`. Read the **Data coverage** block: the sync window is what exists. If the window they want starts before it, say so.
3. Find the columns that hold a category, subcategory, queue, tag, topic or intent (enum or low-cardinality text). For each candidate, `aggregate_table` count by it (top 25, `orderBy` count desc).
4. Show the top values and confirm {{category_selection}}, {{baseline_window}}, {{week_starts_on}}, {{channel_scope}} and {{output_destination}} (default `~/rippit-drilldowns/<category-slug>/`).
5. Count the population by day for the window. If the last day is well under a normal day, it hasn't finished syncing: end the window a day earlier. If the count is over 10,000, the baseline needs a sample rule (S5).

### S2. Discovery pass (about 300 rows)

1. `create_worksheet` with the category filter and the window, `sampleRows: 300`, title `<Category> — discovery`. `create_worksheet` sampling works; `enrich_worksheet sampleRows` is ignored (see Tool notes).
2. Count the worksheet until it matches (rows hydrate after the call returns).
3. `taxonomy.py discovery --category "<category>" [--segment "<what level 1 might be: product line, plan, region>"] --out discovery.json` -> pass it to `enrich_worksheet`. It asks five free-text questions per row: topic, problem, cause, who could fix it, and whether the contact fits the category at all.
4. `read_table` the discovery columns (all rows, paged) and save them with `grab.py`.

### S3. Propose the taxonomy

From the discovery answers, draft `taxonomy-<slug>-v1.json` from the template, writing the levels against {{taxonomy_levels}} and the owner column against {{owner_groups}}:

- **Levels (2-4, broadest first).** Level 1 is usually what the business already slices by (product line, plan, vertical, region), and the last level is usually root cause. A level can be an existing source column (`"native": true`) instead of an enriched one, which costs nothing and can't drift.
- **Values.** 5-15 per level, each with a one-line definition that says where the line sits, written from what discovery actually found (quote their words where you can). Every level and the owner need a catch-all ("Not enough info", "Other / unclear"); `taxonomy.py check` fails without one. Give level 1 an out-of-scope value too ("Not a <category> contact"), because discovery always finds some mis-filed contacts.
- **Root cause as branches.** When the last level depends on its parent, write it as `branches`: a map of parent value -> causes, one definition per cause. It runs as a second enrich call with the parent as context, as one flat enum; at analysis time any cause outside its parent's list counts as `other` (a *fold*), and the fold rate is a gate. Don't use `overrides` for this (see Tool notes).
- **Owner.** One enriched column naming the function that could have prevented the contact, grouped into the 3-5 groups in {{owner_groups}}. The page shows groups, not single values, unless the retest supports them.
- **Names (optional).** A free-text column for the named program, plan, promo, feature or policy the customer mentions. That is what lets the new-and-rising check spot a brand-new name.
- **A few report columns (optional).** Sentiment, resolved-in-conversation and so on; `metrics.py` reports every enum or boolean column. At most 25 columns per call.

Show the user the levels, values and owner groups as a short table, with the discovery count behind each value. Ask them to approve or edit. Their words win on names; your job is making every line between values clear enough that two runs put the same contact in the same place.

### S4. Pilot and freeze

1. `taxonomy.py check T`, then `taxonomy.py pilot T --out pilot.json`.
2. A new worksheet of about 200 other rows (`sampleRows: 200`), then enrich it with `taxonomy.py payload pilot.json --pilot --call 1` (and `--call 2 --cols <pilot cols.json>` once call 1 is done).
3. Save `cut_tree.json` for the pilot (see Cuts) and run `metrics.py` on it. Look at: catch-all share per level (target <= 15%), the fold rate (<= 8%), named root cause (>= 85%), and any value under 1%. Read 5 conversations from the biggest catch-all to see what it is missing.
4. Edit the draft and pilot again if needed (at most twice). Then `taxonomy.py freeze T --by "<approver>"`. If no prompt changed since the pilot, the hash is the same and the pilot rows used exactly the frozen prompts.

### S5. Baseline

1. **Tell the user the cost before enriching:** the row count, and that each row is enriched once per call. Wait for their OK.
2. `taxonomy.py where T --from <first day> --to <last day> [--sample]`. `--sample` adds `sample_rule` when the window is over 10,000 rows. The sample rule must be deterministic (for example the id's last digit), never the tool's random sample, so a re-run draws the same rows.
3. `create_worksheet` with that `where`, title `<Category> — baseline <first>-<last>`. Count until it matches.
4. Enrich call 1 from `taxonomy.py payload T --call 1` with `createScaffoldIfMissing: true`. Save the returned `{name: id}` pairs to `runs/<key>/cols.json` (ids change every run). Poll `get_enrich_status` until `done: true`. Count `Processing failed` rows and re-run them.
5. Call 2, if any: `taxonomy.py payload T --call 2 --cols runs/<key>/cols.json`, no `overrides`, no scaffold flag. Add its ids to `cols.json`. Poll until done.
6. Save the cuts (below), write `meta.json` with `kind: "baseline"`, and run R4-R7.

### S6. Retest and spot check

1. **Retest:** a second worksheet of the same ~300 rows (filter the baseline's ids with `IN`), enriched with the same frozen payloads. `read_table` both (id + every level, owner and report column; page and save with `grab.py`), then `retest.py --tax T --a 'a-*.json' --a-cols runs/<base>/cols.json --b 'b-*.json' --b-cols cols-b.json --id <external id column> --write`. Under 80% on a column: report it only in rollup, or tighten it in a v2. Agreement is a floor on noise, not proof of validity.
2. **Spot check:** `read_conversations` on 10 rows across the biggest paths. Note each miss in `validation.spot_check` in your notes. Don't re-tune in the middle of a series.

---

## Mode 2 — Refresh (weekly, or any window)

State the window in one line first. A weekly run covers one full week starting on {{week_starts_on}}; run it the day after it ends or later, once the last day has synced. A **custom window** runs the same steps with `kind: "custom"`, writes a standalone page with `drill.py --out`, and never joins the weekly history.

### R1. Count and check the window is complete (free)
Population count by day with `taxonomy.py where`. If the last day is under ~60% of the same weekday in earlier weeks, it hasn't finished syncing: stop and say so. Over 10,000 rows -> `--sample`, and say it's a sample.

### R2. Worksheet
`create_worksheet` with that `where`, title `<Category> — <kind> <first>-<last>`. Count until it matches R1. **Tell the user the row count and the enrich cost; wait for their OK** unless they've approved runs of this size.

### R3. Enrich from the frozen file
Same as S5 steps 4-5: `taxonomy.py payload`, save `cols.json`, poll, re-run failed rows. Check the file's hash first (`taxonomy.py check T`): if it no longer matches, someone edited the frozen file. Stop.

### R4. Cuts
Save each cut to the run folder with `grab.py` (paged results: `--append` for every page after the first). Every cut uses `select: [{columnId: <id column>, agg: count, alias: "n"}]` and `limit: 10000`, on the run's worksheet, with ids from `cols.json`.

| File | groupBy | Needed by |
|---|---|---|
| `cut_tree.json` | every level column + the owner column | drill, metrics |
| `cut_tree_day.json` | every level column + the date column with `dateTrunc: day` | drill (spikes), emerging |
| `cut_names_day.json` | names raw column (+ canonical column, if any) + the date column by day | emerging (names) |
| `cuts/<Column>.json` | one reported column | metrics |
| `cuts/<Column>__by_l1.json` | level 1 + one reported column (optional) | metrics scorecards |

Then `meta.json` with `key`, `label`, `kind`, `first_day`, `last_day`, `pop`, `sample`, `worksheet`, `taxonomy_hash`. Keys: `base-<last day>`, `wk-<last day>`, `custom-<first>-<last>`. Run folders live in `{{output_destination}}/runs/` and are the history the next run compares against: **never delete or rebuild an old one.**

### R5. Metrics and gates
`metrics.py --tax T --run runs/<key> [--prev runs/<last week>] --baseline runs/<base>`. It asserts every distribution sums to the sample, then prints the gates and every move.

| Gate | Target | If it fails |
|---|---|---|
| Catch-all share per level | <= 15% | read 5 of them; report as is, and if it persists, fix it in a v2 |
| Processing failed | <= 2% | re-run those rows before any cut |
| Branch folds | <= 8% | the flat root-cause prompt is drifting; flag it |
| Named root cause | >= 85% | read 5 `Other / unclear` rows |
| Unexpected enum values | none | fold each into its intended value, footnote it |

**A move is real only if it is at least 2 points and at least 30 rows** (overridable in `gates`, set before the first run). Say "flat" otherwise. When the two runs have different taxonomy hashes, only unchanged columns compare; say which.

### R6. New and rising (weekly runs)
`emerging.py --tax T --runs {{output_destination}}/runs --out runs/<key>/emerging.json --html-out runs/<key>/emerging.html`. It scores this week against every earlier week and prints each flag.

| Status | Test |
|---|---|
| New | share >= 4x its highest share in any earlier week |
| Jump | share >= 2x its earlier average |
| Climbing | up three weeks running and >= 1.5x its average |
| every flag | >= 30 contacts **and** Poisson z >= 3 against the earlier average |
| Back to normal | flagged last week, now under 1.5x its average or under half of last week |
| One-day pill | over half of the flagged contacts came on one day |

These rules are pre-registered: set any change in `emerging_rules` **before** the first weekly run, and log any later change as a rule change. Units are every level but the last; the last level is reported as the driver. It also lists off-list names (>= 50 mentions two weeks running) as candidates for the canonical list; never add one yourself.

**"What it is":** for each shown flag (up to 6 issues and 6 names), `read_table` 3-5 of this run's rows in that cell to get their external ids, then `read_conversations`. Write one line per flag to `runs/<key>/notes.json`, naming a cause only if a transcript you read shows it. Re-run emerging.py with `--notes runs/<key>/notes.json`.

### R7. The page
1. Tab: `drill.py --tax T --run runs/<key> --tab-dir {{output_destination}}/tabs [--emerging runs/<key>/emerging.json --emerging-html runs/<key>/emerging.html] [--logo <png or svg>]`. It prints coverage, named rate, folds, excluded counts and the 12 paths. A path with over half its contacts on one day is greyed as a **one-day spike**.
2. Page: `tabs.py --tax T --tabs-dir {{output_destination}}/tabs --out {{output_destination}}/<slug>-drilldown.html`. The newest week opens first; the baseline is always a tab. It checks balanced HTML and runs a PII scan (emails, phone numbers, long digit strings). **A PII hit stops the handover:** find the value and remove it at the source, never by editing the HTML.
3. Open the page (or screenshot it with a headless browser) and look at it before handing over.
4. Custom window: `drill.py --tax T --run runs/<key> --out {{output_destination}}/<slug>-<first>-<last>.html`.

### R8. Handover
Tell the user where the HTML file is, then a short readout: the window and size (sample or census), the biggest paths, the moves that passed the rule, each new or rising flag with its "what it is" line, any failed gate, and what the owner groups say. Every number comes from the files just written.

---

## Tool notes (Rippit)

Things that have broken runs before. Check them rather than assume.
- **`unspecified_enum` fails the whole enrich job.** Use `enum` with values, or `string`. `taxonomy.py check` blocks it.
- **Enrich `sampleRows` is ignored**: it enriches the whole worksheet. Size the worksheet with `create_worksheet` `sampleRows` instead, and check the worksheet's count before enriching.
- **Hydration lags.** A new worksheet can be empty or short for a while; count it until it matches.
- **`create_worksheet` samples can skew.** Compare the sample's day and level-1 mix with the population; use a deterministic id rule for anything you'll re-run.
- **Don't trust a "random" or bucket column** as a sampling key without checking its distribution first.
- **`BETWEEN` treats `date_to` as exclusive.** `taxonomy.py where` passes the day after the last day.
- **Context columns are cut to about 170 characters.** Put what matters at the start, or leave it to the transcript.
- **`pivot_table` silently ignores a bad column id.** Resolve ids with `aggregate_table` first; these scripts only use `aggregate_table`.
- **`overrides` (per-parent prompts) has failed with HTTP 500** and created nothing. Branches run as one flat enum plus the fold rule.
- **`read_conversations` takes the external id** (or the internal ticket id), not a worksheet row id.
- **Multi-select arrays come back in varying order.** metrics.py and retest.py sort them.
- **Soft labels are unstable.** That is why the retest exists, and why an unvalidated column goes in a footnote, not a headline.
- **A column id changes every run.** Always read ids from that run's `cols.json`.

## Output folder

```
{{output_destination}}/
  taxonomy-<slug>-v1.json      frozen; never edited
  runs/<key>/                  meta.json, cols.json, cut_*.json, cuts/, metrics.json, emerging.*, notes.json
  tabs/<key>.html + .json      one per run; never rebuilt
  <slug>-drilldown.html        the page (one tab per run)
```

To see it all before touching real data: `python3 examples/make_demo.py --out /tmp/cd_demo` and open `/tmp/cd_demo/drilldown.html`.