Queries · q405 · Support Operations Manager · week 2, night 3
“Did the delivery/where-is-my-order slowness at Marrowby & Vane and Studio Vrij spike in a specific period (e.g. November-December, matching a known carrier breakdown) or run elevated all year?”
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 2
Subject: Re: Helpdesk Operating Review — great start, here's where to push next
Ben,
This is exactly the kind of baseline I wanted — and the speed-to-CSAT finding alone is worth putting in front of Daniel as-is. A few things stood out that I want sharpened before we present or act on them.
What surprised me / needs explaining:
Questions for next week, in priority order:
Thanks — this is shaping up well. Let's make next week about turning the Marrowby & Vane / Studio Vrij question into something actionable.
Mia
The analyst · week 2, night 3, request 2 of 3
Big lead from last night: Report 1 shows Studio Vrij's delivery-tagged tickets average 10,482 minutes and where-is-my-order tickets 10,801 minutes — roughly 6x Cragsley's 1,697/1,819 minutes for the exact same tags — and these two tags are 57-59% of Studio Vrij's entire ticket volume. Marrowby & Vane shows the same pattern at ~4x Cragsley's pace. That's a huge, tag-specific, isolated gap that looks like a fulfillment/carrier problem bleeding into support, not an agent-skill or staffing problem — and it lines up with what the COO and VP CX teams posted this week (a carrier breakdown, and delivery delays driving 'where's my order' tickets). I want to chase that directly tonight by looking at these two tenants' actual shipment/carrier performance. Report 2 (first-response vs post-response split) turned out less diagnostic than hoped: the 50/50 split between first-response delay and post-response delay is nearly identical across all three tenants, so it doesn't distinguish 'slow to start' from 'slow to finish' — both phases just run slower end-to-end for the flagged merchants, consistent with an external (carrier) cause rather than an internal process-stage cause. Report 3 (KB engagement) came back all zeros — the guessed path patterns don't exist in this data, which tells us nothing except that we need to find the real signal before trying again, so I'm spending one slot on that reconnaissance rather than guessing blind a second time.
The engineer · 2 dry runs on the empty schema, then the submission
WITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, to_char(date_trunc('month', tt.created_at), 'YYYY-MM') AS month_label, COUNT(*) AS ticket_count, ROUND(AVG(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at))/60) FILTER (WHERE tt.resolved_at IS NOT NULL)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*), 1) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name, month_label
WITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'),breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution')SELECT te.name AS tenant_name, to_char(date_trunc('month', tt.created_at), 'YYYY-MM') AS month_label, strftime(date_trunc('month', tt.created_at), '%Y-%m') AS month_label, COUNT(*) AS ticket_count, ROUND(AVG(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at))/60) FILTER (WHERE tt.resolved_at IS NOT NULL)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*), 1) AS resolution_breach_rateFROM tagged_tickets ttJOIN tenants te ON te.id = tt.tenant_idLEFT JOIN breaches b ON b.ticket_id = tt.ticket_idGROUP BY te.name, date_trunc('month', tt.created_at)ORDER BY te.name, month_label
“Query compiles and returns the expected columns. Submitting.”
“Submitted. Summary of what the report does: - Grain: one row per (tenant, month) for 2025, limited to Marrowby & Vane, Studio Vrij, and Cragsley Outfitters. - Filter: tickets created in 2025 that carry a 'delivery' or 'where-is-my-order' tag (deduplicated so a ticket with both tags counts once). - Metrics: ticket count, average resolution time in minutes (over tickets that have resolved), and the resolution-SLA breach rate as a percentage. - Note flagged to the analyst: unresolved tickets are excluded from the average-minutes calculation (no resolution time exists yet) but remain in the ticket count and breach-rate denominator.”
As Ben Carter saw it, from the small dataset the agents worked on
tenant_name | month_label | ticket_count | avg_resolution_minutes | resolution_breach_rate --------------------+-------------+--------------+------------------------+----------------------- Cragsley Outfitters | 2025-02 | 1 | 314.0 | 0.0 Cragsley Outfitters | 2025-03 | 9 | 1051.0 | 0.0 Cragsley Outfitters | 2025-04 | 3 | 636.0 | 0.0 Cragsley Outfitters | 2025-05 | 4 | 1224.0 | 0.0 Cragsley Outfitters | 2025-06 | 6 | 1338.0 | 0.0 Cragsley Outfitters | 2025-07 | 16 | 1529.0 | 6.3 Cragsley Outfitters | 2025-08 | 5 | 1994.0 | 40.0 Cragsley Outfitters | 2025-09 | 8 | 508.0 | 0.0 Cragsley Outfitters | 2025-10 | 11 | 2876.0 | 45.5 Cragsley Outfitters | 2025-11 | 10 | 2032.0 | 20.0 Cragsley Outfitters | 2025-12 | 24 | 2081.0 | 33.3 Marrowby & Vane | 2025-02 | 1 | 240.0 | 0.0 Marrowby & Vane | 2025-03 | 3 | 346.0 | 0.0 Marrowby & Vane | 2025-04 | 2 | 799.0 | 0.0 Marrowby & Vane | 2025-05 | 2 | 642.0 | 0.0 Marrowby & Vane | 2025-06 | 4 | 1137.0 | 0.0 Marrowby & Vane | 2025-07 | 2 | 797.0 | 0.0 Marrowby & Vane | 2025-08 | 1 | 1196.0 | 0.0 Marrowby & Vane | 2025-09 | 3 | 398.0 | 0.0 Marrowby & Vane | 2025-10 | 6 | 933.0 | 0.0 Marrowby & Vane | 2025-11 | 34 | 9533.0 | 61.8 Marrowby & Vane | 2025-12 | 7 | 19274.0 | 100.0 Studio Vrij | 2025-03 | 2 | 987.0 | 0.0 Studio Vrij | 2025-04 | 4 | 957.0 | 0.0 Studio Vrij | 2025-05 | 54 | 8528.0 | 51.9 Studio Vrij | 2025-06 | 18 | 29549.0 | 100.0 Studio Vrij | 2025-07 | 2 | 8763.0 | 100.0 Studio Vrij | 2025-09 | 1 | 83.0 | 0.0 Studio Vrij | 2025-10 | 9 | 669.0 | 0.0 Studio Vrij | 2025-12 | 8 | 680.0 | 0.0 (30 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
WITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at SELECT DISTINCT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'),breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution')SELECT te.name AS tenant_name, strftime(date_trunc('month', tt.created_at), '%Y-%m') AS month_label, {{year_month(date_trunc('month', tt.created_at))}} AS month_label, COUNT(*) AS ticket_count, ROUND(AVG(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at))/60) FILTER (WHERE tt.resolved_at IS NOT NULL)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*), 1) AS resolution_breach_rate AVG({{seconds_between(tt.created_at, tt.resolved_at)}}/60) FILTER (WHERE tt.resolved_at IS NOT NULL) AS avg_resolution_minutes, 100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*) AS resolution_breach_rateFROM tagged_tickets ttJOIN tenants te ON te.id = tt.tenant_idLEFT JOIN breaches b ON b.ticket_id = tt.ticket_idGROUP BY te.name, date_trunc('month', tt.created_at)ORDER BY te.name, month_labelORDER BY te.name NULLS LAST, month_label NULLS LAST
WITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, strftime(date_trunc('month', tt.created_at), '%Y-%m') AS month_label, COUNT(*) AS ticket_count, AVG(CAST(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) AS NUMERIC(18,6))/60) FILTER (WHERE tt.resolved_at IS NOT NULL) AS avg_resolution_minutes, 100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name NULLS LAST, month_label NULLS LAST
WITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, to_char(date_trunc('month', tt.created_at), 'YYYY-MM') AS month_label, COUNT(*) AS ticket_count, AVG(CAST(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) AS NUMERIC(18,6))/60) FILTER (WHERE tt.resolved_at IS NOT NULL) AS avg_resolution_minutes, 100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name NULLS LAST, month_label NULLS LAST
WITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, to_char(CAST(date_trunc('month', tt.created_at) AS timestamp), 'YYYY-MM') AS month_label, COUNT(*) AS ticket_count, AVG(CAST(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) AS NUMERIC(18,6))/60) FILTER (WHERE tt.resolved_at IS NOT NULL) AS avg_resolution_minutes, 100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name NULLS LAST, month_label NULLS LAST
WITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, formatDateTime(date_trunc('month', tt.created_at), '%Y-%m') AS month_label, COUNT(*) AS ticket_count, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tt.created_at, tt.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/60) FILTER (WHERE tt.resolved_at IS NOT NULL) AS avg_resolution_minutes, 100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name NULLS LAST, month_label NULLS LAST
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: Monthly delivery-topic ticket volume and speed, flagged merchants vs. Cragsley Question: Did the delivery/where-is-my-order slowness at Marrowby & Vane and Studio Vrij spike in a specific period (e.g. November-December, matching a known carrier breakdown) or run elevated all year? Grain: one row per (tenant, month) for 2025, restricted to Marrowby & Vane, Studio Vrij, and Cragsley Outfitters, and to tickets tagged 'delivery' or 'where-is-my-order' Columns: - tenant_name - month_label (YYYY-MM) - ticket_count - avg_resolution_minutes - resolution_breach_rate Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31, restricted to the three named tenants and to tickets with a tag of 'delivery' or 'where-is-my-order' in ticket_tags_map (count once per ticket even if it has both tags). Source tables: tickets, ticket_tags_map, support_tags, sla_breaches (breach_kind='resolution'). Order by tenant_name, then month_label ascending. Round avg_resolution_minutes to whole numbers, rate to 1 decimal percent. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, to_char(date_trunc('month', tt.created_at), 'YYYY-MM') AS month_label, COUNT(*) AS ticket_count, ROUND(AVG(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at))/60) FILTER (WHERE tt.resolved_at IS NOT NULL)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*), 1) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name, month_label
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runWITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, strftime(date_trunc('month', tt.created_at), '%Y-%m') AS month_label, COUNT(*) AS ticket_count, ROUND(AVG(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at))/60) FILTER (WHERE tt.resolved_at IS NOT NULL)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*), 1) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name, month_label
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, month_label, ticket_count, avg_resolution_minutes, resolution_breach_rate
submitWITH tagged_tickets AS ( SELECT DISTINCT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id AND ttm.tenant_id = t.tenant_id JOIN support_tags st ON st.id = ttm.support_tag_id AND st.tenant_id = t.tenant_id JOIN tenants te ON te.id = t.tenant_id WHERE te.name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') AND st.name IN ('delivery','where-is-my-order') AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT te.name AS tenant_name, strftime(date_trunc('month', tt.created_at), '%Y-%m') AS month_label, COUNT(*) AS ticket_count, ROUND(AVG(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at))/60) FILTER (WHERE tt.resolved_at IS NOT NULL)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(*) FILTER (WHERE b.ticket_id IS NOT NULL) / COUNT(*), 1) AS resolution_breach_rate FROM tagged_tickets tt JOIN tenants te ON te.id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY te.name, date_trunc('month', tt.created_at) ORDER BY te.name, month_label
Submitted to tonight's batch. Your work on this request is done.