Weekly Customer Pulse

---
name: weekly-customer-pulse
description: "Weekly tiered friction and frustration report for your existing (non-onboarding) customers: recurring themes, repeat issues and week-over-week trends split by the customer tiers you define, with watch lists for priority tiers and an in-page AI explorer. Reads support conversations through the Rippit connector, classifies and reconciles every ticket in code, and publishes one shareable HTML page that republishes to a stable URL each week. Use when a team wants a repeatable read on what existing customers keep contacting support about, which themes frustrate them most, and which high-value customers need a human, without high-value accounts being drowned out by overall volume."
---
# Weekly Customer Pulse

Produce a weekly read on what your **existing customers** (past the tenure cutoff you set) contact support about, which issues they keep facing, which themes frustrate them most, and how that moves week over week. The report is split by **customer tier** so high-value customers are never drowned out by volume. Delivered as one shareable HTML artifact, republished in place each week.

Existing customers have no onboarding funnel, so themes replace stages. The page is built around conversations and per-customer repeat issues.

## First run — make it yours

Before asking anything, LOOK at what is actually connected:

1. Call `list_data_sources` and list what the user has. Pick the source that looks like support conversations and `describe_table` it. Report the columns that could satisfy each parameter: a customer identifier, a signup/tenure timestamp, a tier or level field, a channel/source-type field, a comment-count field, a topic category, and the data coverage window.
2. If the connected data is missing something fundamental (no identifier, no tenure field, no tier field), say so plainly now 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:

- **{{data_source}}** — "Your workspace has one source that looks like your support conversations table; use that as the ticket source, and should I resolve its column ids fresh each run?"
- **{{tenure_cutoff}}** — "This skill reports on existing (non-onboarding) customers. The author used a tenure of more than 30 days at ticket time. What cutoff defines an existing customer for you?"
- **{{customer_tiers}}** — "How should I group customers into tiers, and from which column? The author used three tiers mapped from a level attribute. What are your tier bands and their source field?"
- **{{theme_categories}}** — "What theme categories should each ticket be classified into? The author used a set like withdrawals, payments, account access and service complaints for a gaming operator. What are yours?"
- **{{ignored_request_type}}** — "Is there a request type you want counted but excluded from all rankings (the author excluded free-reward asks)? If so, how is it defined?"
- **{{email_rule}}** — "The author tracked chats first and kept an email ticket only when the same customer raised the same theme in chat that week. Keep that chat-first rule, ignore email, or include everything?"
- **{{spike_thresholds}}** — "What thresholds define a spike worth flagging? The author used at least 50 percent above the prior 4-week mean and at least 5 customers (2 for priority tiers). Keep these or change them?"
- **{{output_url}}** — "Should I publish the report to a new stable URL and republish there each week, or write to an existing report link you already share?"

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.

## Defaults

- Week: the last complete ISO week, Monday 00:00 to Sunday 23:59:59. Assume UTC for timestamps and say so.
- Existing customer: tenure at ticket time greater than {{tenure_cutoff}}.
- Source: the table chosen as {{data_source}}. Resolve all column ids fresh with `describe_table` before use.
- Scope: customer-initiated tickets only. Drop admin/agent-initiated rows using the source-type column found in discovery.
- Tiers: parse the tier field from {{customer_tiers}} and map each ticket into a tier band. Show any "no tier" or special sub-group as visible sub-rows inside the lowest tier. Never hide a data gap inside a tier.
- Tier is recorded per ticket. A customer's tier for the week is the highest tier among their tickets; tier panels count that customer's ranked tickets under that tier. State this on the page and show how many tickets were re-homed.
- {{ignored_request_type}} is ignored in every ranking. On the customers view it may be shown behind an off-by-default toggle. If no such type is defined, skip this.
- Email handling: follow {{email_rule}}. If chat-first, keep an email ticket only when the same customer raised the same theme in chat that week, tag it "also by email", and drop/count email-only issues in the reconciliation.
- Privacy: the page shows external ids, backoffice/customer ids, brand and tier, never emails or phone numbers. Quotes have money amounts, emails, phone numbers and every number removed. Drop quotes that look like sign-offs (they may carry a first name) and tell the user to skim them.

## Standing rules

- Warn before Rippit write actions (create_worksheet, enrich_worksheet) and before publishing. The page is shared and contains customer identifiers, so say so in chat and on the page.
- Do not make up data. Every number is computed in Python code, never by hand, and asserted to reconcile.
- No em dashes in anything written for the user.
- Counts are customers or tickets that contacted support, never rates of the customer base. The active base per tier is not in this dataset. Say so on the page.
- Never sample priority tiers. Do not sample the lowest tier either unless the user approves after being told the volume.
- For a test or first run, tell the user the plan and volume first, then proceed.

## Step 1: Look first

`describe_table` on {{data_source}} and report the columns used: customer identifier, signup/tenure, tier/level, brand, team assigned, source type, public comment count, topic category, and the data coverage window.

Signup columns may hold mixed formats (epoch seconds and date strings). Parse both. Rows with a missing or unparseable signup have unknown tenure: exclude them from the cohort, count them, and show the count in the reconciliation. Never default them to zero.

## Step 2: Count the week, then pull it

1. `aggregate_table` the week's ticket count, by day and by tier, against the 10,000-row worksheet cap.
2. `read_table` the week with a narrow projection: identifier, timestamp, signup, tier, team assigned, source type, public comment count, brand, topic category. Known Rippit quirks: page in small time windows and dedupe by external id (offset paging without a sort returns duplicates and gaps); comparison operators on array columns can 500, so use `LIKE` prefixes in `or_filters` or filter locally; large tool outputs are saved to files, merge and dedupe with Python; use `read_conversations` only for small checks and print only truncated, cleaned, non-PII fields.
3. Compute tenure and tier locally in Python. Print the tenure distribution, tier distribution, no-tier count and unknown-tenure count and check them before enriching.
4. Customer key for counting: identifier, falling back to backoffice id, then external id. The key is never shown. Note how many tickets fell back so you can flag possible splits.
5. Split into worksheets under the cap by tier and day. Since tenure often cannot be filtered server-side, build filters from tier `LIKE` prefixes and signup-epoch prefixes, accept a small boundary band, enrich with a tight `where`, and drop extras in the join. Check worksheet row count against the cohort before enriching.

## Step 3: Greeting-only chats

A conversation-source ticket with a low public comment count and no team assigned is usually an automated greeting. Run a small pilot for the lowest tier and read it before deciding the exact rule. Exclude greeting-only chats for the lowest tier only if the pilot confirms it; report the excluded count. Do NOT apply this exclusion to priority tiers.

Before enriching, show the user: volume per tier and per day against the cap and split plan; tenure, tier, no-tier and unknown-tenure counts; the pilot result.

## Step 4: Enrich (one batched call per worksheet)

All columns in one `enrich_worksheet` call. Every enum needs an explicit catch-all. Poll `get_enrich_status` until done. Count any failed row as its own reconciliation line.

- `theme` (enum over {{theme_categories}} plus Other and, if defined, {{ignored_request_type}} and a "No customer request" value).
- `issue` (string): the specific problem in 12 words or fewer, consistent wording across customers.
- `frustration` (enum): calm, mildly frustrated, frustrated, angry, unclear. High bar for angry.
- `escalation_signal` (multi enum): none, closure-request, self-exclusion-request, legal-threat, social-media-threat, regulator-complaint, explicit-give-up, wants-account-manager.
- `outcome` (enum): resolved-in-thread, told-to-wait, handed-to-another-team, no-resolution-given, unclear.
- `fix_owner` (enum): Product/Engineering, Third-party provider, Policy/Eligibility, Payments/Finance process, Support handling, Customer confusion, unclear.
- `named_party` (string): provider, vendor or partner named, or empty.
- `evidence_quote` (string): the customer's own words. No amounts, emails, phone numbers or ids.

Spot-check about 20 rows per tier against transcripts (cleaned snippets only). Report it as an informal check, not an accuracy rate.

## Step 5: Roll up in code

Read enriched worksheets back, join to the cohort, drop rows outside the cohort, and cross-check theme counts against `aggregate_table`. Then compute and assert:

- Ticket reconciliation per tier: tickets in the week, minus unknown tenure, minus new customers, minus greeting-only, equals analysed; minus {{ignored_request_type}}, minus no customer request, minus enrichment failed, equals ranked; minus email-only issues, equals tickets in the views. Per-tier columns must sum to the total.
- Customer reconciliation: customers with a ticket, analysed, kept in the views.
- Per customer and issue: ticket count, whether any was by email, whether any was frustrated, last outcome and fix owner, give-up, severe signal, latest ticket id, plus the full list of that customer's analysed tickets for an all-tickets popup.
- Issue grouping: group similar issues within each theme in code (token-set similarity, label by most common wording) and say the groups are approximate.
- Frustrated: frustrated or angry, or any escalation signal other than none. Stuck: 2+ tickets on the same issue/theme this week with the last not resolved-in-thread. Give-up: explicit-give-up. Escalated: outcome handed-to-another-team.
- A customer in two themes appears in each theme list but once in totals. Label customer counts and entry counts differently.
- Top issues per theme with customers, outcome mix, fix owner mix and 2 quotes (frustrated first).
- {{ignored_request_type}} is counted per tier but never enters any ranking.

## Step 6: Weekly memory and trends

A new conversation has no memory, so the artifact stores snapshots. Use the `db` capability. After the first publish, write one document per ISO week into a `snapshots` collection: tier x theme counts (tickets, customers, frustrated, stuck, give-ups). For carry-over, store only truncated SHA-256 hashes of customer ids per tier and theme, never emails or raw ids.

With prior snapshots, show: week-over-week change per theme and tier and against the prior 4-week mean; emerging themes absent from the previous 4 weeks; carry-over customers raising the same theme again; spikes per {{spike_thresholds}} (shown on the page and changeable by the user); and the same named party or issue across many customers in one week flagged as a possible incident. Without prior snapshots, say trends start next week and offer to backfill.

## Step 7: Deliver

Build one self-contained HTML page with data embedded as JSON, build the DOM with textContent (never innerHTML with data), run it once in a headless browser to catch errors, then publish declaring `sample` and `db`. Publish to {{output_url}} and republish to the same URL each week so the link stays stable. Give the security warning before publishing.

Page structure as tabs: (1) Overview with tier cards, scope line, privacy banner, collapsed reconciliation and a trends note; (2) Issues with tier/theme/issue dropdowns, summary, outcome and fix-owner mix, two quotes and a scrolling per-customer table; (3) Customers with one row per customer and filters (tier, theme, issue text, flag, repeaters-only default on, include {{ignored_request_type}} default off); (4) Priority-tier watch list of every priority-tier customer who was frustrated, stuck, escalated or gave up, sorted by severity, with a "needs a human" filter; (5) Ask Claude with dropdowns and a follow-up box using the `sample` capability, sending only aggregated counts and cleaned quotes; (6) Data and method, including a "Not in this dataset" section and limits.

Every table header sorts; long lists sit in fixed-height scrolling boxes; the All-tickets button opens a popup listing every ticket for that customer this week (date UTC, channel, theme and issue, mood, outcome, and a deep link if your helpdesk supports one).

In chat, lead with the top frustrated theme per tier, any priority-tier case needing a human, the biggest movers (or that trends start next week), the privacy note, the numbers the data lacks, and any step that was unclear.

## Principles

- High tiers are never ignored: separate rankings, no sampling, full watch lists.
- Theme and frustration are separate columns.
- Never present a count as a rate. Never call a tier "worst" from counts alone.
- Reconcile every number or say which number is missing.
- Prefer chat evidence of repeated issues over email volume, and say how email was treated.
- A customer stuck in two themes appears in each theme list but once in the customer totals.