Queries · q247 · VP Customer Experience · week 2, night 2
“Do Cragsley, Marrowby, and Studio Vrij get harder/more complex tickets than the rest of the book, or just more of them?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 2
Subject: Good progress — now I want to understand the "why" behind the hotspots
Mateo — this is exactly the kind of baseline I needed, thank you. Two things from your memo and Mia's notes are sitting with me:
First, the speed→satisfaction link is strong and now confirmed two ways (yours and Mia's numbers line up almost exactly), so I believe it. But I don't yet know if slowness causes the dissatisfaction or if the worst merchants are simply worse across the board (harder products, different shopper expectations, something else) and speed is just the symptom we can see. That matters a lot for what I recommend as the fix.
Second, Mia's finding that three merchants account for a third of all tickets and the worst response times lines up with what you found independently — that's a strong signal, not a coincidence, and it's where I want to spend our next slots.
Questions for this week:
Let's prioritize #1 and #2 this week — those feel most actionable. #3 and #4 can wait if slots are tight.
Thanks, Daniel
The analyst · week 2, night 2, request 1 of 3
Night 1 results: the staffing report shows Cragsley (50.0 tickets/agent), Marrowby (46.2) and Studio Vrij (41.5) are the three highest ratios in the entire qualifying book, well ahead of the next closest (Grind Collective, 40.7, then a drop to 34.3) - so understaffing-relative-to-volume looks like a real, differentiating factor, not noise (unassigned-ticket rates are near-zero everywhere, so it's not a triage problem). Channel mix, by contrast, looks like a dead end: the three hotspots' email/chat/phone split is unremarkable - several merchants with much heavier email reliance (e.g. Coastline at 52.4% email) actually had the best SLA performance last week, so I don't think channel explains the gap. The urgent-ticket report is the more worrying one: Marrowby (66.7%), Cragsley (66.0%) and Studio Vrij (63.6%) are bad, but several smaller merchants (Honeycomb Larder 100%, Sourdough Club 85.7%, Ampfeld Elektronik 83.3%) are worse - so urgent-SLA failure is NOT concentrated in the three hotspots, it's spread broadly across the book, and nearly every merchant's urgent miss rate is well above their own all-priority miss rate. That pattern (urgent specifically failing everywhere) makes me suspect the urgent SLA target itself may be set unrealistically tight platform-wide, rather than this being pure execution failure merchant-by-merchant - worth testing directly before concluding it's "everyone is bad at urgent tickets." Tonight I'm testing: (1) whether hotspot tickets are genuinely more complex (more back-and-forth) rather than just higher volume, (2) whether urgent SLA targets are achievable given typical actual resolution times, and (3) whether the hotspots' staffing gap has been widening over 2025 (useful for both the "why" question and, incidentally, for planning ahead of Nov/Dec later).
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS merchant_name, tk.ticket_count AS tickets_2025, round(coalesce(msg.total_messages, 0)::numeric / tk.ticket_count, 2) AS avg_messages_per_ticket, round(coalesce(msg.total_public, 0)::numeric / tk.ticket_count, 2) AS avg_public_messages_per_ticket, round(coalesce(tags.total_tags, 0)::numeric / tk.ticket_count, 2) AS avg_distinct_tags_per_ticket, count(*) OVER () AS qualifying_tenant_count FROM tenants t JOIN ( SELECT tenant_id, count(*) AS ticket_count FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk ON tk.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id, count(*) AS total_messages, count(*) FILTER (WHERE tm.is_public) AS total_public FROM ticket_messages tm JOIN tickets ti ON ti.id = tm.ticket_id AND ti.tenant_id = tm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) msg ON msg.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id, count(*) AS total_tags FROM ticket_tags_map ttm JOIN tickets ti ON ti.id = ttm.ticket_id AND ti.tenant_id = ttm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) tags ON tags.tenant_id = t.id WHERE tk.ticket_count >= 20 ORDER BY avg_messages_per_ticket DESC, merchant_name ASC LIMIT 50
“Query compiles and returns the expected columns. Submitting.”
“Submitted. Summary of what the report delivers:
- Grain: one row per tenant (merchant) with ≥20 tickets created in 2025.
- Columns: merchant name, tickets created in 2025, avg messages per ticket, avg public messages per ticket, avg distinct tags per ticket (all rounded to 2 decimals), plus a qualifying_tenant_count column carrying the total peer-set size for reference.
- Ordering: avg messages per ticket descending, tie-broken by merchant name — so Cragsley, Marrowby, and Studio Vrij will sit wherever their complexity proxy actually lands relative to the rest of the book, making it easy for Mateo to see if they're outliers or just high-volume.
- Note to analyst: tag counts are raw ticket_tags_map row counts per ticket (not deduplicated beyond the table's own unique constraint), and the qualifying-tenant count is repeated on every row since results are capped at 50.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
merchant_name | tickets_2025 | avg_messages_per_ticket | avg_public_messages_per_ticket | avg_distinct_tags_per_ticket | qualifying_tenant_count ------------------------------+--------------+-------------------------+--------------------------------+------------------------------+------------------------ Thistlecombe | 21 | 4.29 | 4.29 | 1.67 | 22 Kibblewick | 72 | 4.22 | 4.22 | 1.85 | 22 Ballycorran Industrial Supply | 44 | 4.14 | 4.14 | 1.66 | 22 Honeycomb Larder | 58 | 3.93 | 3.93 | 1.71 | 22 Ampfeld Elektronik | 56 | 3.82 | 3.82 | 1.54 | 22 Grind Collective | 122 | 3.82 | 3.82 | 1.93 | 22 Petalcourt Beauty | 119 | 3.76 | 3.76 | 1.77 | 22 Spice Route Box | 71 | 3.75 | 3.75 | 1.79 | 22 Studio Vrij | 166 | 3.71 | 3.71 | 1.64 | 22 Brennquell Kaffee | 137 | 3.7 | 3.7 | 1.43 | 22 Rue Lafayette Studio | 27 | 3.7 | 3.7 | 1.74 | 22 Cinder Peak Sports Co. | 43 | 3.58 | 3.58 | 1.56 | 22 Fennel & Flint | 67 | 3.58 | 3.58 | 1.75 | 22 Gratlinie Outdoor | 27 | 3.56 | 3.56 | 1.48 | 22 Kayak Corner | 20 | 3.5 | 3.5 | 1.85 | 22 Cragsley Outfitters | 400 | 3.46 | 3.46 | 1.36 | 22 Linden & Loom | 29 | 3.45 | 3.45 | 1.79 | 22 Wiesgrund Wohnen | 32 | 3.44 | 3.44 | 1.53 | 22 Sourdough Club | 47 | 3.4 | 3.4 | 1.81 | 22 Marrowby & Vane | 231 | 3.29 | 3.29 | 1.43 | 22 Coastline Interiors | 21 | 3.14 | 3.14 | 1.62 | 22 Oakhurst Tailoring | 27 | 3.07 | 3.07 | 1.59 | 22 (22 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS merchant_name, tk.ticket_count AS tickets_2025, round(coalesce(msg.total_messages, 0)::numeric / tk.ticket_count, 2) AS avg_messages_per_ticket, round(coalesce(msg.total_public, 0)::numeric / tk.ticket_count, 2) AS avg_public_messages_per_ticket, round(coalesce(tags.total_tags, 0)::numeric / tk.ticket_count, 2) AS avg_distinct_tags_per_ticket, CAST(coalesce(msg.total_messages, 0) AS numeric(18,6)) / tk.ticket_count AS avg_messages_per_ticket, CAST(coalesce(msg.total_public, 0) AS numeric(18,6)) / tk.ticket_count AS avg_public_messages_per_ticket, CAST(coalesce(tags.total_tags, 0) AS numeric(18,6)) / tk.ticket_count AS avg_distinct_tags_per_ticket, count(*) OVER () AS qualifying_tenant_countFROM tenants tJOIN ( SELECT tenant_id, count(*) AS ticket_count FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id) tk ON tk.tenant_id = t.idLEFT JOIN ( SELECT ti.tenant_id, SELECT ti.tenant_id AS tenant_id, count(*) AS total_messages, count(*) FILTER (WHERE tm.is_public) AS total_public FROM ticket_messages tm JOIN tickets ti ON ti.id = tm.ticket_id AND ti.tenant_id = tm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id) msg ON msg.tenant_id = t.idLEFT JOIN ( SELECT ti.tenant_id, SELECT ti.tenant_id AS tenant_id, count(*) AS total_tags FROM ticket_tags_map ttm JOIN tickets ti ON ti.id = ttm.ticket_id AND ti.tenant_id = ttm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id) tags ON tags.tenant_id = t.idWHERE tk.ticket_count >= 20ORDER BY avg_messages_per_ticket DESC, merchant_name ASCLIMIT 50ORDER BY avg_messages_per_ticket DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id ASC NULLS LAST{{fetch_first(50)}}
SELECT t.name AS merchant_name, tk.ticket_count AS tickets_2025, CAST(coalesce(msg.total_messages, 0) AS numeric(18,6)) / tk.ticket_count AS avg_messages_per_ticket, CAST(coalesce(msg.total_public, 0) AS numeric(18,6)) / tk.ticket_count AS avg_public_messages_per_ticket, CAST(coalesce(tags.total_tags, 0) AS numeric(18,6)) / tk.ticket_count AS avg_distinct_tags_per_ticket, count(*) OVER () AS qualifying_tenant_count FROM tenants t JOIN ( SELECT tenant_id, count(*) AS ticket_count FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk ON tk.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id AS tenant_id, count(*) AS total_messages, count(*) FILTER (WHERE tm.is_public) AS total_public FROM ticket_messages tm JOIN tickets ti ON ti.id = tm.ticket_id AND ti.tenant_id = tm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) msg ON msg.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id AS tenant_id, count(*) AS total_tags FROM ticket_tags_map ttm JOIN tickets ti ON ti.id = ttm.ticket_id AND ti.tenant_id = ttm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) tags ON tags.tenant_id = t.id WHERE tk.ticket_count >= 20 ORDER BY avg_messages_per_ticket DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS merchant_name, tk.ticket_count AS tickets_2025, CAST(coalesce(msg.total_messages, 0) AS numeric(18,6)) / tk.ticket_count AS avg_messages_per_ticket, CAST(coalesce(msg.total_public, 0) AS numeric(18,6)) / tk.ticket_count AS avg_public_messages_per_ticket, CAST(coalesce(tags.total_tags, 0) AS numeric(18,6)) / tk.ticket_count AS avg_distinct_tags_per_ticket, count(*) OVER () AS qualifying_tenant_count FROM tenants t JOIN ( SELECT tenant_id, count(*) AS ticket_count FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk ON tk.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id AS tenant_id, count(*) AS total_messages, count(*) FILTER (WHERE tm.is_public) AS total_public FROM ticket_messages tm JOIN tickets ti ON ti.id = tm.ticket_id AND ti.tenant_id = tm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) msg ON msg.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id AS tenant_id, count(*) AS total_tags FROM ticket_tags_map ttm JOIN tickets ti ON ti.id = ttm.ticket_id AND ti.tenant_id = ttm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) tags ON tags.tenant_id = t.id WHERE tk.ticket_count >= 20 ORDER BY avg_messages_per_ticket DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id ASC NULLS LAST LIMIT 50
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 Mateo Ruiz (analyst for the VP Customer Experience): Title: Ticket complexity proxy (messages and tags per ticket), by merchant Question: Do Cragsley, Marrowby, and Studio Vrij get harder/more complex tickets than the rest of the book, or just more of them? Grain: one row per tenant (merchant), among tenants with at least 20 tickets created in 2025 Columns: - merchant name - tickets created, 2025 - avg messages per ticket (all) - avg public (customer-visible) messages per ticket - avg distinct tags per ticket Details: Source: tickets joined to tenants, ticket_messages (count per ticket, and count where is_public=true), and ticket_tags_map (count distinct support_tag_id per ticket). Window: tickets.created_at 2025-01-01 through 2025-12-31. 'avg messages per ticket' = total ticket_messages rows for that tenant's 2025 tickets ÷ number of those tickets. 'avg public messages per ticket' = same but counting only is_public=true rows. 'avg distinct tags per ticket' = total ticket_tags_map rows for those tickets ÷ number of those tickets (a ticket with 0 tags counts as 0). Round all three averages to 2 decimals. Only include tenants with >=20 tickets created in 2025 (same threshold as last night's staffing/channel reports, for a consistent peer set). Order by avg messages per ticket descending, tie-break merchant name ascending. Cap at 50 rows; note total qualifying tenant count. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name AS merchant_name, tk.ticket_count AS tickets_2025, round(coalesce(msg.total_messages, 0)::numeric / tk.ticket_count, 2) AS avg_messages_per_ticket, round(coalesce(msg.total_public, 0)::numeric / tk.ticket_count, 2) AS avg_public_messages_per_ticket, round(coalesce(tags.total_tags, 0)::numeric / tk.ticket_count, 2) AS avg_distinct_tags_per_ticket, count(*) OVER () AS qualifying_tenant_count FROM tenants t JOIN ( SELECT tenant_id, count(*) AS ticket_count FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk ON tk.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id, count(*) AS total_messages, count(*) FILTER (WHERE tm.is_public) AS total_public FROM ticket_messages tm JOIN tickets ti ON ti.id = tm.ticket_id AND ti.tenant_id = tm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) msg ON msg.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id, count(*) AS total_tags FROM ticket_tags_map ttm JOIN tickets ti ON ti.id = ttm.ticket_id AND ti.tenant_id = ttm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) tags ON tags.tenant_id = t.id WHERE tk.ticket_count >= 20 ORDER BY avg_messages_per_ticket DESC, merchant_name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, tickets_2025, avg_messages_per_ticket, avg_public_messages_per_ticket, avg_distinct_tags_per_ticket, qualifying_tenant_count
submitSELECT t.name AS merchant_name, tk.ticket_count AS tickets_2025, round(coalesce(msg.total_messages, 0)::numeric / tk.ticket_count, 2) AS avg_messages_per_ticket, round(coalesce(msg.total_public, 0)::numeric / tk.ticket_count, 2) AS avg_public_messages_per_ticket, round(coalesce(tags.total_tags, 0)::numeric / tk.ticket_count, 2) AS avg_distinct_tags_per_ticket, count(*) OVER () AS qualifying_tenant_count FROM tenants t JOIN ( SELECT tenant_id, count(*) AS ticket_count FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk ON tk.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id, count(*) AS total_messages, count(*) FILTER (WHERE tm.is_public) AS total_public FROM ticket_messages tm JOIN tickets ti ON ti.id = tm.ticket_id AND ti.tenant_id = tm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) msg ON msg.tenant_id = t.id LEFT JOIN ( SELECT ti.tenant_id, count(*) AS total_tags FROM ticket_tags_map ttm JOIN tickets ti ON ti.id = ttm.ticket_id AND ti.tenant_id = ttm.tenant_id WHERE ti.created_at >= timestamp '2025-01-01' AND ti.created_at < timestamp '2026-01-01' GROUP BY ti.tenant_id ) tags ON tags.tenant_id = t.id WHERE tk.ticket_count >= 20 ORDER BY avg_messages_per_ticket DESC, merchant_name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.