# SupportInsights — Schema

> Synthetic customer support tickets at full ticket grain, 12 months ending 2026-05-31.

## Tables

### `pods.csv` (3 rows)

| Column | Type | Notes |
| --- | --- | --- |
| `pod_id` | string | Primary key (`POD-AMER`, `POD-EMEA`, `POD-APAC`). |
| `pod_name` | string | Display name. |
| `timezone` | string | IANA timezone for the pod. |
| `planned_agent_count` | integer | Headcount target. |

### `agents.csv` (47 rows)

| Column | Type | Notes |
| --- | --- | --- |
| `agent_id` | string | Primary key. Format `A1NN`. |
| `pod_id` | string | FK → `pods.pod_id`. |
| `tier` | string | `front-line`, `senior`, or `principal`. |
| `hire_month` | date | First-of-month hire date. |

### `categories.csv` (10 rows)

| Column | Type | Notes |
| --- | --- | --- |
| `category_id` | string | Primary key. |
| `category_name` | string | Display name. |
| `sla_first_response_hours` | integer | Target. |
| `sla_resolution_hours` | integer | Target. |
| `definition_change_note` | string | **Known imperfection**: 2 categories (`CAT-FEAT`, `CAT-PROD`) had their definitions changed on 2025-09-01. Pre-change tickets are retroactively recategorized. Notes are populated only on affected rows. |

### `articles.csv` (60 rows)

| Column | Type | Notes |
| --- | --- | --- |
| `article_id` | string | Primary key. Format `KB-NNNN`. |
| `title` | string | Synthetic title. |
| `category_id` | string | FK → `categories.category_id`. |
| `last_reviewed_date` | date | When the article was last refreshed. |
| `word_count` | integer | Article length. |

### `tickets.csv` (~25,400 rows)

| Column | Type | Notes |
| --- | --- | --- |
| `ticket_id` | string | Primary key. |
| `created_date` | date | When the ticket arrived. |
| `category_id` | string | FK → `categories.category_id`. |
| `severity` | string | `low`, `medium`, `high`, `critical`. |
| `channel` | string | `chat`, `email`, `phone`, `in-product`. |
| `pod_id` | string | FK → `pods.pod_id`. |
| `agent_id` | string | FK → `agents.agent_id`. |
| `agent_tier` | string | Denormalized from `agents` (reflects real warehouse design). |
| `resolution` | string | `solved`, `workaround`, `duplicate`, `not-reproducible`, `open`. |
| `resolved_date` | date or empty | Empty for open tickets. |
| `resolution_hours` | decimal | Time from create to resolved. |
| `sla_breached` | boolean | `true` if `resolution_hours > sla_resolution_hours`. |
| `csat_score` | integer (1-5) or empty | **Known imperfection**: ~6% null among resolved (customer didn't respond). |
| `article_referenced` | string or empty | FK → `articles.article_id`. Empty if no article referenced. |
| `article_resolved_ticket` | boolean or empty | Empty when no article was referenced. **Known imperfection**: ~5% `false` (article-quality issue). |
| `no_article_found_tag` | boolean | `true` if agent tagged the ticket as "no relevant article exists." |

## Suggested chapter exercises

- **Chapter 4 (chart selection)**: Visualize ticket volume × category × pod. Compare small multiples vs heatmap vs stacked area.
- **Chapter 10 (anomaly detection)**: Detect the APAC pod's Q2 2026 staffing-shortage signature (resolution-hours penalty). Tune sensitivity.
- **Chapter 10 (key influencers)**: What predicts a CSAT score below 3?
- **Chapter 11 (prep for AI)**: Reconcile the CAT-FEAT / CAT-PROD definition change. Author the AI instructions that explain the recategorization to Copilot.
- **Chapter 13 (when AI misleads)**: Ask Copilot for "the worst-performing category by resolution time." Watch whether it surfaces or hides the CAT-FEAT recategorization that inflates the recent average.

## Reproducibility

Generated by `scripts/datasets/generate-supportinsights.js`. Source seed: `0x5499C0E1`.