Queries · q205 · Support Operations Manager · week 1, night 4
“Which knowledge base articles get used, and are the ones we have actually rated as helpful by customers?”
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 4, request 1 of 3
Good news first: the breach_kind audit confirms only two codes exist (first_response: 878 rows, resolution: 474 rows) matching our assumption almost exactly, so the 37.7%/20.3% headline breach rates from night 1 are solid — I can give Daniel that number with confidence. The channel/assignment report was mostly a null result: only 12 tickets platform-wide are currently unassigned (vs ~2,300 total), so 'sitting unassigned' isn't a scale driver of slowness even though those 12 do breach badly — and channel spreads resolution time by only ~12% (2626-2940 min), far smaller than the 3x spread we saw across merchants. So channel/assignment are minor levers; merchant identity remains the dominant driver, which I'll state plainly in the memo. The macro report shows real concentration (top 20 of 600 macros carry ~27% of lifetime usage) but also a red flag I need to note rather than resolve: lifetime macro usage (37,230) dwarfs even several years of 2025-scale agent reply volume (4,279), so I can't respond with a clean 'reliance %' — I'll flag that as a data caveat, not a finding. For this last night I'm closing out the remaining asks: KB article effectiveness (priority 4b), ticket volume/speed by topic (Daniel's 'certain topics' question, and it also tells us where KB investment should go), and CSAT against resolution speed (priority 5, directly testing whether satisfaction tracks the SLA/speed picture).
The engineer · 1 dry run on the empty schema, then the submission
WITH published AS ( SELECT ka.id, ka.title, kc.name AS category_name, ka.view_count, ka.helpful_count, ka.not_helpful_count FROM knowledge_articles ka LEFT JOIN knowledge_categories kc ON kc.id = ka.knowledge_category_id WHERE ka.is_published = true AND ka.deleted_at IS NULL ), agg AS ( SELECT COUNT(*) AS total_published_articles, SUM(helpful_count) AS total_helpful, SUM(helpful_count + not_helpful_count) AS total_votes FROM published ) SELECT p.title, p.category_name AS knowledge_category_name, p.view_count, p.helpful_count, p.not_helpful_count, CASE WHEN (p.helpful_count + p.not_helpful_count) = 0 THEN NULL ELSE ROUND(p.helpful_count::numeric / (p.helpful_count + p.not_helpful_count), 4) END AS helpful_rate, a.total_published_articles, ROUND(a.total_helpful::numeric / NULLIF(a.total_votes,0), 4) AS platform_avg_helpful_rate FROM published p CROSS JOIN agg a ORDER BY p.view_count DESC, p.id ASC LIMIT 20
“Query compiles cleanly with the expected 8 columns. Submitting.”
“Submitted. Summary:
- Grain: one row per published knowledge article (top 20 by lifetime view_count), platform-wide across all tenants.
- Columns: title, category name, view_count, helpful_count, not_helpful_count, helpful_rate (null when no votes), plus the two constants — total published articles in the library and the platform average helpful rate — repeated on every row so Support Ops can visually flag any top-20 article whose helpful_rate sits well below the platform average.
- Note to analyst: since no merchant/tenant was specified, the ranking and averages are computed across the whole platform; flagged in the submit note in case they actually want it scoped to one tenant or with a tenant label added (titles aren't guaranteed unique across tenants).”
As Ben Carter saw it, from the small dataset the agents worked on
title | knowledge_category_name | view_count | helpful_count | not_helpful_count | helpful_rate | total_published_articles | platform_avg_helpful_rate -------------------------------------+-------------------------+------------+---------------+-------------------+--------------+--------------------------+-------------------------- Managing email preferences | Account | 25117 | 400 | 98 | 0.8032 | 1709 | 0.7556 When will I get my refund? | Returns & Refunds | 22126 | 309 | 49 | 0.8631 | 1709 | 0.7556 Size guide | Products | 17245 | 351 | 138 | 0.7178 | 1709 | 0.7556 Size guide | Products | 15590 | 219 | 108 | 0.6697 | 1709 | 0.7556 My item arrived damaged | Returns & Refunds | 14406 | 173 | 45 | 0.7936 | 1709 | 0.7556 Size guide | Products | 12492 | 341 | 62 | 0.8462 | 1709 | 0.7556 Managing email preferences | Account | 11859 | 251 | 54 | 0.823 | 1709 | 0.7556 My parcel is late - what now? | Orders & Delivery | 11546 | 126 | 48 | 0.7241 | 1709 | 0.7556 Managing email preferences | Account | 10673 | 375 | 117 | 0.7622 | 1709 | 0.7556 Size guide | Products | 10577 | 141 | 31 | 0.8198 | 1709 | 0.7556 Size guide | Products | 10094 | 161 | 76 | 0.6793 | 1709 | 0.7556 How can I track my order? | Orders & Delivery | 9541 | 326 | 144 | 0.6936 | 1709 | 0.7556 Do you ship internationally? | Orders & Delivery | 9024 | 353 | 70 | 0.8345 | 1709 | 0.7556 How do I return an item? | Returns & Refunds | 8935 | 100 | 13 | 0.885 | 1709 | 0.7556 When will I get my refund? | Returns & Refunds | 8751 | 118 | 26 | 0.8194 | 1709 | 0.7556 How do I return an item? | Returns & Refunds | 8688 | 87 | 33 | 0.725 | 1709 | 0.7556 How can I track my order? | Orders & Delivery | 8549 | 247 | 41 | 0.8576 | 1709 | 0.7556 How do I reset my password? | Account | 8216 | 121 | 55 | 0.6875 | 1709 | 0.7556 Which payment methods do you accept? | Payments | 7719 | 93 | 35 | 0.7266 | 1709 | 0.7556 My parcel is late - what now? | Orders & Delivery | 7532 | 267 | 78 | 0.7739 | 1709 | 0.7556 (20 rows)
Changed only where the benchmark needs the same result on every system
WITH published AS ( SELECT ka.id, ka.title, kc.name AS category_name, ka.view_count, ka.helpful_count, ka.not_helpful_count SELECT ka.id AS id, ka.title AS title, kc.name AS category_name, ka.view_count AS view_count, ka.helpful_count AS helpful_count, ka.not_helpful_count AS not_helpful_count FROM knowledge_articles ka LEFT JOIN knowledge_categories kc ON kc.id = ka.knowledge_category_id WHERE ka.is_published = true AND ka.deleted_at IS NULL),agg AS ( SELECT COUNT(*) AS total_published_articles, SUM(helpful_count) AS total_helpful, SUM(helpful_count + not_helpful_count) AS total_votes FROM published)SELECT p.title, p.title AS title, p.category_name AS knowledge_category_name, p.view_count, p.helpful_count, p.not_helpful_count, p.view_count AS view_count, p.helpful_count AS helpful_count, p.not_helpful_count AS not_helpful_count, CASE WHEN (p.helpful_count + p.not_helpful_count) = 0 THEN NULL ELSE ROUND(p.helpful_count::numeric / (p.helpful_count + p.not_helpful_count), 4) ELSE CAST(p.helpful_count AS numeric(18,6)) / (p.helpful_count + p.not_helpful_count) END AS helpful_rate, a.total_published_articles, ROUND(a.total_helpful::numeric / NULLIF(a.total_votes,0), 4) AS platform_avg_helpful_rate a.total_published_articles AS total_published_articles, CAST(a.total_helpful AS numeric(18,6)) / NULLIF(a.total_votes,0) AS platform_avg_helpful_rateFROM published pCROSS JOIN agg aORDER BY p.view_count DESC, p.id ASCLIMIT 20ORDER BY p.view_count DESC NULLS FIRST, p.id ASC NULLS LAST{{fetch_first(20)}}
WITH published AS ( SELECT ka.id AS id, ka.title AS title, kc.name AS category_name, ka.view_count AS view_count, ka.helpful_count AS helpful_count, ka.not_helpful_count AS not_helpful_count FROM knowledge_articles ka LEFT JOIN knowledge_categories kc ON kc.id = ka.knowledge_category_id WHERE ka.is_published = true AND ka.deleted_at IS NULL ), agg AS ( SELECT COUNT(*) AS total_published_articles, SUM(helpful_count) AS total_helpful, SUM(helpful_count + not_helpful_count) AS total_votes FROM published ) SELECT p.title AS title, p.category_name AS knowledge_category_name, p.view_count AS view_count, p.helpful_count AS helpful_count, p.not_helpful_count AS not_helpful_count, CASE WHEN (p.helpful_count + p.not_helpful_count) = 0 THEN NULL ELSE CAST(p.helpful_count AS numeric(18,6)) / (p.helpful_count + p.not_helpful_count) END AS helpful_rate, a.total_published_articles AS total_published_articles, CAST(a.total_helpful AS numeric(18,6)) / NULLIF(a.total_votes,0) AS platform_avg_helpful_rate FROM published p CROSS JOIN agg a ORDER BY p.view_count DESC NULLS FIRST, p.id ASC NULLS LAST FETCH FIRST 20 ROWS ONLY
WITH published AS ( SELECT ka.id AS id, ka.title AS title, kc.name AS category_name, ka.view_count AS view_count, ka.helpful_count AS helpful_count, ka.not_helpful_count AS not_helpful_count FROM knowledge_articles ka LEFT JOIN knowledge_categories kc ON kc.id = ka.knowledge_category_id WHERE ka.is_published = true AND ka.deleted_at IS NULL ), agg AS ( SELECT COUNT(*) AS total_published_articles, SUM(helpful_count) AS total_helpful, SUM(helpful_count + not_helpful_count) AS total_votes FROM published ) SELECT p.title AS title, p.category_name AS knowledge_category_name, p.view_count AS view_count, p.helpful_count AS helpful_count, p.not_helpful_count AS not_helpful_count, CASE WHEN (p.helpful_count + p.not_helpful_count) = 0 THEN NULL ELSE CAST(p.helpful_count AS numeric(18,6)) / (p.helpful_count + p.not_helpful_count) END AS helpful_rate, a.total_published_articles AS total_published_articles, CAST(a.total_helpful AS numeric(18,6)) / NULLIF(a.total_votes,0) AS platform_avg_helpful_rate FROM published p CROSS JOIN agg a ORDER BY p.view_count DESC NULLS FIRST, p.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: Knowledge base article effectiveness Question: Which knowledge base articles get used, and are the ones we have actually rated as helpful by customers? Grain: one row per published knowledge article, restricted to the top 20 by view_count Columns: - title - knowledge_category_name - view_count - helpful_count - not_helpful_count - helpful_rate (helpful_count / (helpful_count + not_helpful_count), null if no feedback) - total_published_articles (constant: count of knowledge_articles with is_published = true and deleted_at is null) - platform_avg_helpful_rate (constant: sum(helpful_count) / sum(helpful_count + not_helpful_count) across all published articles) Details: Source tables: knowledge_articles (is_published = true, deleted_at is null), knowledge_categories. No date window needed since view_count/helpful_count are lifetime counters on the article. Order by view_count descending, top 20 rows; note the total number of published articles in the library so we can see concentration vs. the long tail, and flag any of the top 20 with a helpful_rate notably below the platform average. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH published AS ( SELECT ka.id, ka.title, kc.name AS category_name, ka.view_count, ka.helpful_count, ka.not_helpful_count FROM knowledge_articles ka LEFT JOIN knowledge_categories kc ON kc.id = ka.knowledge_category_id WHERE ka.is_published = true AND ka.deleted_at IS NULL ), agg AS ( SELECT COUNT(*) AS total_published_articles, SUM(helpful_count) AS total_helpful, SUM(helpful_count + not_helpful_count) AS total_votes FROM published ) SELECT p.title, p.category_name AS knowledge_category_name, p.view_count, p.helpful_count, p.not_helpful_count, CASE WHEN (p.helpful_count + p.not_helpful_count) = 0 THEN NULL ELSE ROUND(p.helpful_count::numeric / (p.helpful_count + p.not_helpful_count), 4) END AS helpful_rate, a.total_published_articles, ROUND(a.total_helpful::numeric / NULLIF(a.total_votes,0), 4) AS platform_avg_helpful_rate FROM published p CROSS JOIN agg a ORDER BY p.view_count DESC, p.id ASC LIMIT 20
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: title, knowledge_category_name, view_count, helpful_count, not_helpful_count, helpful_rate, total_published_articles, platform_avg_helpful_rate
submitWITH published AS ( SELECT ka.id, ka.title, kc.name AS category_name, ka.view_count, ka.helpful_count, ka.not_helpful_count FROM knowledge_articles ka LEFT JOIN knowledge_categories kc ON kc.id = ka.knowledge_category_id WHERE ka.is_published = true AND ka.deleted_at IS NULL ), agg AS ( SELECT COUNT(*) AS total_published_articles, SUM(helpful_count) AS total_helpful, SUM(helpful_count + not_helpful_count) AS total_votes FROM published ) SELECT p.title, p.category_name AS knowledge_category_name, p.view_count, p.helpful_count, p.not_helpful_count, CASE WHEN (p.helpful_count + p.not_helpful_count) = 0 THEN NULL ELSE ROUND(p.helpful_count::numeric / (p.helpful_count + p.not_helpful_count), 4) END AS helpful_rate, a.total_published_articles, ROUND(a.total_helpful::numeric / NULLIF(a.total_votes,0), 4) AS platform_avg_helpful_rate FROM published p CROSS JOIN agg a ORDER BY p.view_count DESC, p.id ASC LIMIT 20
Submitted to tonight's batch. Your work on this request is done.