Promo Code Issue Dashboards — daily check

---
name: promo-code-issue-dashboards
description: "Daily check of your support conversations tagged with the promo- or bonus-issue categories you define. Reads what each customer said, extracts the promo/bonus code they mentioned, judges whether the code actually failed and why, ranks the codes with the most failed conversations, and publishes one dashboard per promotion showing common failure reasons, resolutions, a split by your account grouping, and drill-down examples. Use whenever the user asks for the daily promo check, which bonus codes are failing, why a promo isn't working, a per-promotion issue dashboard, or promo issues today — even when they don't name the source or the tags. Runs over your chosen lookback window and updates the same dashboards each run."
---
# Promo Code Issue Dashboards — daily check

Finds the promo codes customers say are not working, explains why, and keeps one dashboard per promotion up to date. The job runs once per your chosen window; the first run should use a longer lookback to backfill.

Classifications are based on **what the customer says and what happened in the chat**. Agent tags and bot tags are not evidence.

## First run — make it yours

**Look first.** Before asking anything, inspect what the user actually has connected:
- Call `list_data_sources` and list what is available.
- `describe_table` on the one or two tables most relevant to tagged support conversations. Note the columns that could satisfy each parameter: a conversation/external id, a `last_seen` or similar timestamp, a tags column (confirm whether it holds array values), a brand/account-grouping column, and full transcript text.
- If no table carries conversation tags or transcript text, 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 and proposing a default:**
1. `{{data_source}}` — e.g. "Your workspace has one conversation source and its tags column holds array values — should I read promo-issue conversations from there?"
2. `{{issue_tags}}` — "Which tags mark a promo or bonus issue? The author's version scoped three tags covering 'broken', 'questions' and 'customer mistake'. What are yours?"
3. `{{account_grouping}}` — "I found a brand column that looks like an array — which field should I use to split results by your account grouping?"
4. `{{lookback_window}}` — "How far back should each run look? The author ran it daily over the last 1 day, with a 30-day first-run backfill. Keep that?"
5. `{{top_codes}}` — "How many of the top failing codes should get their own dashboard each run?"

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

## Step 0 — Settings

| Setting | Value |
|---|---|
| Table | `{{data_source}}` (conversations with tags, account grouping, transcript) |
| Tags in scope | `{{issue_tags}}` — matched as array element membership |
| Window | `{{lookback_window}}` on the timestamp column; longer lookback on first run to backfill |
| Top codes | `{{top_codes}}` |
| Registry | a local `registry.json` mapping each code to its dashboard URL so daily runs update the same page |

## Step 1 — Count the window

Tags are arrays, so use `IN` (element membership), never `=` on the tags column.

```
aggregate_table(
  tableId = {{data_source}},
  select  = [{columnId: "external_id", agg: "count_distinct", alias: "n"}],
  where   = [
    {columnId: "tags", operator: "IN", value: {{issue_tags}}},
    {columnId: "last_seen", period: {{lookback_window}}}
  ]
)
```

If the count is 0, write the registry and report "no promo-tagged conversations in the window". Do not create worksheets or dashboards. Low tagged volume is normal.

## Step 2 — Persist the slice as a worksheet

```
create_worksheet(
  tableId = {{data_source}},
  title   = "Promo issues – <YYYY-MM-DD>",
  where   = <same where as Step 1>
)
```

Omit `columnIds` so the worksheet keeps the full transcript fields. Confirm rows landed with `read_table(limit=5)` before enriching; enrichment only processes rows present when it runs.

## Step 3 — Enrich: promo code, failure, resolution

One `enrich_worksheet` call fills all columns in one pass per row.

```
enrich_worksheet(
  worksheetId = <id>,
  enrichments = [
    {name: "Promo Code", outputType: {type: "string"}, instructions: "Read the full conversation. Return the bonus or promo code the CUSTOMER typed, entered, or asked about, exactly as written but UPPERCASE with spaces trimmed. If the customer names a promotion without a code, return the name prefixed with 'PROMO: '. Return 'none' if nothing is named."},
    {name: "Promo Not Working?", outputType: {type: "enum", values: ["Yes","No","Unclear"]}, instructions: "Yes = the customer says the code/promotion did not apply, credit, or was rejected/unavailable, and the chat supports it. No = question only, or it worked. Unclear = not enough to tell."},
    {name: "Failure Reason", outputType: {type: "enum", values: [<the failure-reason categories you define, plus 'Promo question only, no failure', 'Other', 'unknown'>]}, instructions: "Pick the single best reason the code did not work, based on what the customer describes and facts in the chat. Use the 'question only' value when nothing failed, 'unknown' when the chat does not say."},
    {name: "Resolution", outputType: {type: "enum", values: [<the resolution categories you define, plus 'unknown'>]}, instructions: "How did the chat end for the promo problem? Judge from the agent's actions and messages, not the bot's."},
    {name: "Issue Summary", outputType: {type: "string"}, instructions: "One sentence under 25 words stating the promo problem and what the customer experienced. No names or personal data."}
  ]
)
```

Poll `get_enrich_status(worksheetId, jobId)` until `done: true`, then use the returned column ids (not display names) in every later call.

## Step 4 — Rank the codes that are not working

Use only rows where `Promo Not Working?` = `Yes`; exclude `none`, `Unclear` and empty codes.

```
aggregate_table(
  tableId = <worksheetId>,
  where   = [{columnId: <promoNotWorkingId>, operator: "=", value: "<Yes>"}],
  select  = [{columnId: "external_id", agg: "count_distinct", alias: "failed_chats"}],
  groupBy = [{columnId: <promoCodeId>}],
  orderBy = [{alias: "failed_chats", direction: "desc"}],
  limit   = 50
)
```

Merge obvious duplicates (trailing spaces, `PROMO:`-prefixed vs bare names). Keep the top `{{top_codes}}`; make dashboards for all if fewer exist. Flag any code with only one chat — one chat is a signal, not a trend.

## Step 5 — Breakdowns for each top code

For each top code, run these cuts on the worksheet:
1. Failure Reason counts (bar)
2. Resolution counts (donut)
3. Account-grouping split using `{{account_grouping}}` (if it is an array, use `arrayExpansions`)
4. Daily trend, only if the window is more than one day
5. Drill-down rows via `read_table` for that code, with id, timestamp, `{{account_grouping}}`, Failure Reason, Resolution and Issue Summary, sorted newest first

Do not compute percentages by hand — get counts from the tool, divide in code if needed.

## Step 6 — One dashboard per promotion

1. Load `registry.json` (create with `{}` on first run). Look up the code.
2. Quickstart the dashboard artifact (`Artifact(action="quickstart", intent="other")`), publish with its `type_url` and title `Promo issues – <CODE>`, fill with Step 5 data. Reuse the registry URL when a dashboard already exists for the code.
3. Each dashboard shows: header (code, window dates, failed chats, total promo chats), failure reasons (bar), resolutions (donut), account-grouping split, a short "What to check" list from the top two failure reasons, and a drill-down table with links back to the source conversation.
4. Save the URL and today's date in `registry.json` under the code.

Do one dashboard per promotion. Do not merge codes into one page.

## Step 7 — Report

Reply in the chat with: window, number of promo-tagged chats and number not working; the top codes with failed-chat counts and dominant failure reason; a link to each dashboard; and anything unclear (unknown reasons, likely typos, single-chat codes).

## Notes

- Expect low volume and many zero-count days; say so instead of inventing a trend.
- No dedicated code field is expected — the code lives in the transcript, which is why enrichment reads it.
- A timestamp can be recent for a chat first seen long ago, so always count distinct conversation ids.
- To run this daily, set up a scheduled task after the first successful run, and ask the user first.