Queries · q204 · Support Operations Manager · week 1, night 3
“How much do agents actually lean on canned-reply macros, and is usage concentrated in a few macros or spread across the library?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Daniel Achterberg, VP Customer Experience
Review how the helpdesk runs: queues and agent groups, use of macros and knowledge articles, SLA targets and breaches, and what makes tickets slow.
The manager, at the start of week 1
Subject: Helpdesk operating review — what I need this week
Hi Ben,
Daniel and I need a baseline read on how the helpdesk actually runs, so we can walk into our first leadership review with a clear picture instead of impressions. This feeds our decisions on how we staff/structure agent groups, where we invest in macros and knowledge base content, and whether our current SLA targets are realistic. Given you only get a handful of reports a night, here's what matters most, roughly in priority order:
No need to go deep on all five — pick what fits in the nightly slots and flag anything surprising even if I didn't ask directly. Keep it at a level Daniel and I can act on quickly; we'll dig deeper live once we see the numbers.
Thanks, Mia
Subject: A few things I'd like your review to cover
Hi Ben,
As you dig into the helpdesk review this week, a few questions from my side:
If you find anything that points to a specific merchant or category driving a disproportionate share of the pain, flag it even if it's outside these three.
Thanks, Daniel
The analyst · week 1, night 3, request 3 of 3
Before trusting any more SLA numbers, I need to close the risk two separate engineers flagged independently: breach_kind is a free-text column with no enumerated codes, and both guessed 'first_response'/'resolution'. If that guess is wrong (or there's a third kind, or duplicate breach rows per ticket), every breach rate I've reported so far — including the 37.7%/20.3% headline — could be off. That's slot 1 tonight, cheap but non-negotiable before Friday's memo. Report 1 from tonight's merchant cut also showed something worth flagging: our three biggest merchants (Cragsley, Marrowby & Vane, Studio Vrij, 34% of volume) all post first-response breach rates near 55-60%, well above the smaller tail — and Studio Vrij/Marrowby & Vane also have the worst resolution times/breach rates among the top 10, despite having fewer agents than Cragsley. That's a concrete 'specific merchant driving disproportionate pain' candidate for Daniel, but I want the breach-code validation done before I state it as fact. With the other two slots I'm moving to priority #3 (what makes a ticket slow — channel and unassigned status, which both leaders asked about explicitly) and starting priority #4 (macro reliance), since night 4 is the last chance to cover KB effectiveness and CSAT.
The engineer · 1 dry run on the empty schema, then the submission
WITH macro_totals AS ( SELECT COUNT(*) AS total_macro_count, SUM(usage_count) AS total_macro_usage_sum FROM macros WHERE deleted_at IS NULL ), agent_reply_totals AS ( SELECT COUNT(*) AS total_agent_reply_messages_2025 FROM ticket_messages WHERE is_public = true AND author_agent_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' ) SELECT m.title AS macro_title, m.is_shared, m.usage_count, ROUND(100.0 * m.usage_count / NULLIF(mt.total_macro_usage_sum, 0), 2) AS pct_of_total_macro_usage, mt.total_macro_count, mt.total_macro_usage_sum, art.total_agent_reply_messages_2025 FROM macros m CROSS JOIN macro_totals mt CROSS JOIN agent_reply_totals art WHERE m.deleted_at IS NULL ORDER BY m.usage_count DESC, m.title ASC LIMIT 20
“Compiles cleanly with the expected columns. Submitting.”
“Submitted. Summary of what the report does:
- Grain: one row per macro, top 20 by usage_count.
- Columns: macro title, is_shared flag, lifetime usage_count, its share of total usage, plus three constants repeated on every row — total non-deleted macros in the library, total lifetime usage across all macros, and 2025 agent reply message volume as a rough reliance denominator.
- Caveat flagged in the submit note: usage_count is an all-time counter (no date filter exists on it), while the agent-reply denominator is 2025-only — so the ratio is a directional reliance signal, not an apples-to-apples rate. Also noted that this is platform-wide across all tenants, since the question was about overall concentration vs. spread in the macro library.”
As Ben Carter saw it, from the small dataset the agents worked on
macro_title | is_shared | usage_count | pct_of_total_macro_usage | total_macro_count | total_macro_usage_sum | total_agent_reply_messages_2025 ---------------------+-----------+-------------+--------------------------+-------------------+-----------------------+-------------------------------- Refund processed | true | 1840 | 4.94 | 600 | 37230 | 4279 Refund processed | true | 730 | 1.96 | 600 | 37230 | 4279 Refund processed | true | 697 | 1.87 | 600 | 37230 | 4279 Where is my order | true | 651 | 1.75 | 600 | 37230 | 4279 Return instructions | true | 647 | 1.74 | 600 | 37230 | 4279 Damaged item apology | true | 518 | 1.39 | 600 | 37230 | 4279 Payment declined | true | 467 | 1.25 | 600 | 37230 | 4279 Damaged item apology | true | 452 | 1.21 | 600 | 37230 | 4279 Damaged item apology | true | 414 | 1.11 | 600 | 37230 | 4279 Refund processed | true | 397 | 1.07 | 600 | 37230 | 4279 Refund processed | true | 385 | 1.03 | 600 | 37230 | 4279 Payment declined | true | 383 | 1.03 | 600 | 37230 | 4279 Return instructions | true | 369 | 0.99 | 600 | 37230 | 4279 Where is my order | true | 349 | 0.94 | 600 | 37230 | 4279 Damaged item apology | true | 336 | 0.9 | 600 | 37230 | 4279 Where is my order | true | 304 | 0.82 | 600 | 37230 | 4279 Payment declined | true | 295 | 0.79 | 600 | 37230 | 4279 Damaged item apology | true | 294 | 0.79 | 600 | 37230 | 4279 Damaged item apology | true | 261 | 0.7 | 600 | 37230 | 4279 Return instructions | true | 259 | 0.7 | 600 | 37230 | 4279 (20 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
WITH macro_totals AS ( SELECT COUNT(*) AS total_macro_count, SUM(usage_count) AS total_macro_usage_sum FROM macros WHERE deleted_at IS NULL),agent_reply_totals AS ( SELECT COUNT(*) AS total_agent_reply_messages_2025 FROM ticket_messages WHERE is_public = true AND author_agent_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01')SELECT m.title AS macro_title, m.is_shared, m.usage_count, ROUND(100.0 * m.usage_count / NULLIF(mt.total_macro_usage_sum, 0), 2) AS pct_of_total_macro_usage, mt.total_macro_count, mt.total_macro_usage_sum, art.total_agent_reply_messages_2025 m.is_shared AS is_shared, m.usage_count AS usage_count, 100.0 * m.usage_count / NULLIF(mt.total_macro_usage_sum, 0) AS pct_of_total_macro_usage, mt.total_macro_count AS total_macro_count, mt.total_macro_usage_sum AS total_macro_usage_sum, art.total_agent_reply_messages_2025 AS total_agent_reply_messages_2025FROM macros mCROSS JOIN macro_totals mtCROSS JOIN agent_reply_totals artWHERE m.deleted_at IS NULLORDER BY m.usage_count DESC, m.title ASCLIMIT 20ORDER BY m.usage_count DESC NULLS FIRST, m.title ASC NULLS LAST, m.id ASC NULLS LAST{{fetch_first(20)}}
WITH macro_totals AS ( SELECT COUNT(*) AS total_macro_count, SUM(usage_count) AS total_macro_usage_sum FROM macros WHERE deleted_at IS NULL ), agent_reply_totals AS ( SELECT COUNT(*) AS total_agent_reply_messages_2025 FROM ticket_messages WHERE is_public = true AND author_agent_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' ) SELECT m.title AS macro_title, m.is_shared AS is_shared, m.usage_count AS usage_count, 100.0 * m.usage_count / NULLIF(mt.total_macro_usage_sum, 0) AS pct_of_total_macro_usage, mt.total_macro_count AS total_macro_count, mt.total_macro_usage_sum AS total_macro_usage_sum, art.total_agent_reply_messages_2025 AS total_agent_reply_messages_2025 FROM macros m CROSS JOIN macro_totals mt CROSS JOIN agent_reply_totals art WHERE m.deleted_at IS NULL ORDER BY m.usage_count DESC NULLS FIRST, m.title ASC NULLS LAST, m.id ASC NULLS LAST FETCH FIRST 20 ROWS ONLY
WITH macro_totals AS ( SELECT COUNT(*) AS total_macro_count, SUM(usage_count) AS total_macro_usage_sum FROM macros WHERE deleted_at IS NULL ), agent_reply_totals AS ( SELECT COUNT(*) AS total_agent_reply_messages_2025 FROM ticket_messages WHERE is_public = true AND author_agent_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' ) SELECT m.title AS macro_title, m.is_shared AS is_shared, m.usage_count AS usage_count, 100.0 * m.usage_count / NULLIF(mt.total_macro_usage_sum, 0) AS pct_of_total_macro_usage, mt.total_macro_count AS total_macro_count, mt.total_macro_usage_sum AS total_macro_usage_sum, art.total_agent_reply_messages_2025 AS total_agent_reply_messages_2025 FROM macros m CROSS JOIN macro_totals mt CROSS JOIN agent_reply_totals art WHERE m.deleted_at IS NULL ORDER BY m.usage_count DESC NULLS FIRST, m.title ASC NULLS LAST, m.id ASC NULLS LAST LIMIT 20
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Ben Carter (analyst for the Support Operations Manager): Title: Macro usage overview Question: How much do agents actually lean on canned-reply macros, and is usage concentrated in a few macros or spread across the library? Grain: one row per macro, restricted to the top 20 by usage_count Columns: - macro_title - is_shared - usage_count - pct_of_total_macro_usage (this macro's usage_count / sum of usage_count across all macros) - total_macro_count (constant: count of all non-deleted macros) - total_macro_usage_sum (constant: sum of usage_count across all macros) - total_agent_reply_messages_2025 (constant: count of ticket_messages with is_public = true and author_agent_id not null, created_at in 2025, as a rough denominator for how many agent replies exist platform-wide) Details: Source tables: macros (deleted_at is null), ticket_messages for the context denominator. macros.usage_count is a running lifetime counter (no date filter available on it), so note in the report that usage_count is all-time, not 2025-only, while total_agent_reply_messages_2025 is scoped to 2025 — the ratio is a rough reliance signal, not exact. Order by usage_count descending, top 20 rows; note the total number of macros in the library so we can see how many are barely used. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH macro_totals AS ( SELECT COUNT(*) AS total_macro_count, SUM(usage_count) AS total_macro_usage_sum FROM macros WHERE deleted_at IS NULL ), agent_reply_totals AS ( SELECT COUNT(*) AS total_agent_reply_messages_2025 FROM ticket_messages WHERE is_public = true AND author_agent_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' ) SELECT m.title AS macro_title, m.is_shared, m.usage_count, ROUND(100.0 * m.usage_count / NULLIF(mt.total_macro_usage_sum, 0), 2) AS pct_of_total_macro_usage, mt.total_macro_count, mt.total_macro_usage_sum, art.total_agent_reply_messages_2025 FROM macros m CROSS JOIN macro_totals mt CROSS JOIN agent_reply_totals art WHERE m.deleted_at IS NULL ORDER BY m.usage_count DESC, m.title ASC LIMIT 20
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: macro_title, is_shared, usage_count, pct_of_total_macro_usage, total_macro_count, total_macro_usage_sum, total_agent_reply_messages_2025
submitWITH macro_totals AS ( SELECT COUNT(*) AS total_macro_count, SUM(usage_count) AS total_macro_usage_sum FROM macros WHERE deleted_at IS NULL ), agent_reply_totals AS ( SELECT COUNT(*) AS total_agent_reply_messages_2025 FROM ticket_messages WHERE is_public = true AND author_agent_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' ) SELECT m.title AS macro_title, m.is_shared, m.usage_count, ROUND(100.0 * m.usage_count / NULLIF(mt.total_macro_usage_sum, 0), 2) AS pct_of_total_macro_usage, mt.total_macro_count, mt.total_macro_usage_sum, art.total_agent_reply_messages_2025 FROM macros m CROSS JOIN macro_totals mt CROSS JOIN agent_reply_totals art WHERE m.deleted_at IS NULL ORDER BY m.usage_count DESC, m.title ASC LIMIT 20
Submitted to tonight's batch. Your work on this request is done.