Queries · q402 · Support Operations Manager · week 2, night 2
“For Marrowby & Vane, Studio Vrij, and Cragsley Outfitters, does the slowness happen before an agent first replies (pickup delay) or after the first reply, while the ticket is still being worked (handling/back-and-forth delay)?”
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 2, request 2 of 3
Two of Mia's four questions are now answered without needing more queries: tag coverage is 100% (every 2025 ticket carries at least one tag — Report 3 only returned a 'Tagged' row, no 'Untagged' row exists), so last week's topic breakdown isn't a skewed sample. And the macro question is closed too, just not the way we'd like: all 5 top macros show zero matching rows in change_history for usage_count changes (Report 2) — there is no audit trail behind that counter at all, so I'm recommending we drop it from reporting entirely rather than chase it further. On the staffing-vs-complexity question, Report 1 actually argues against under-staffing: Cragsley runs the highest tickets-per-agent (50.0) of the group yet has the lowest resolution time and breach rate, while Marrowby & Vane (46.2/agent) and Studio Vrij (41.5/agent) are slower despite lighter per-agent load — and messages-per-ticket/priority-mix don't explain it either (Studio Vrij actually has a lower urgent/high share than Cragsley). So tonight I want to test the next two hypotheses: (1) it's a specific topic (especially delivery/where-is-my-order, which the COO and VP CX teams flagged as carrier-driven this week) concentrated or uniquely slow at these two merchants, and (2) the delay is back-loaded (tickets drag after the first reply, consistent with waiting on an external process like shipping) rather than front-loaded (agents slow to even start). Third slot starts Mia's Q4 (KB deflection) since we have two more nights after this.
The engineer · 1 dry run on the empty schema, then the submission
select t.name as tenant_name, count(*) as ticket_count, round(avg(extract(epoch from (tk.first_response_at - tk.created_at))/60)::numeric) as avg_first_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.first_response_at))/60)::numeric) as avg_post_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.created_at))/60)::numeric) as avg_resolution_minutes from tickets tk join tenants t on t.id = tk.tenant_id where t.name in ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') and tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' and tk.first_response_at is not null and tk.resolved_at is not null group by t.name order by avg_post_response_minutes desc, tenant_name
“Query compiles and matches the requested grain/columns.”
“Submitted. The report returns one row per tenant (Marrowby & Vane, Studio Vrij, Cragsley Outfitters) with ticket count and the three average minute metrics, ordered by avg_post_response_minutes descending as requested.”
As Ben Carter saw it, from the small dataset the agents worked on
tenant_name | ticket_count | avg_first_response_minutes | avg_post_response_minutes | avg_resolution_minutes --------------------+--------------+----------------------------+---------------------------+----------------------- Studio Vrij | 165 | 3638 | 3866 | 7504 Marrowby & Vane | 216 | 2515 | 2581 | 5096 Cragsley Outfitters | 394 | 1216 | 1133 | 2348 (3 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
select t.name as tenant_name, count(*) as ticket_count, round(avg(extract(epoch from (tk.first_response_at - tk.created_at))/60)::numeric) as avg_first_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.first_response_at))/60)::numeric) as avg_post_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.created_at))/60)::numeric) as avg_resolution_minutes avg({{seconds_between(tk.created_at, tk.first_response_at)}}/60) as avg_first_response_minutes, avg({{seconds_between(tk.first_response_at, tk.resolved_at)}}/60) as avg_post_response_minutes, avg({{seconds_between(tk.created_at, tk.resolved_at)}}/60) as avg_resolution_minutesfrom tickets tkjoin tenants t on t.id = tk.tenant_idwhere t.name in ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') and tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' and tk.first_response_at is not null and tk.resolved_at is not nullgroup by t.nameorder by avg_post_response_minutes desc, tenant_nameorder by avg_post_response_minutes desc NULLS FIRST, tenant_name NULLS LAST
select t.name as tenant_name, count(*) as ticket_count, avg(CAST(EXTRACT(EPOCH FROM (tk.first_response_at - tk.created_at)) AS NUMERIC(18,6))/60) as avg_first_response_minutes, avg(CAST(EXTRACT(EPOCH FROM (tk.resolved_at - tk.first_response_at)) AS NUMERIC(18,6))/60) as avg_post_response_minutes, avg(CAST(EXTRACT(EPOCH FROM (tk.resolved_at - tk.created_at)) AS NUMERIC(18,6))/60) as avg_resolution_minutes from tickets tk join tenants t on t.id = tk.tenant_id where t.name in ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') and tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' and tk.first_response_at is not null and tk.resolved_at is not null group by t.name order by avg_post_response_minutes desc NULLS FIRST, tenant_name NULLS LAST
select t.name as tenant_name, count(*) as ticket_count, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tk.created_at, tk.first_response_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/60) as avg_first_response_minutes, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tk.first_response_at, tk.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/60) as avg_post_response_minutes, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tk.created_at, tk.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/60) as avg_resolution_minutes from tickets tk join tenants t on t.id = tk.tenant_id where t.name in ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') and tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' and tk.first_response_at is not null and tk.resolved_at is not null group by t.name order by avg_post_response_minutes desc NULLS FIRST, tenant_name 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: Where the delay accumulates: first-response lag vs. post-response handling time Question: For Marrowby & Vane, Studio Vrij, and Cragsley Outfitters, does the slowness happen before an agent first replies (pickup delay) or after the first reply, while the ticket is still being worked (handling/back-and-forth delay)? Grain: one row per tenant, restricted to Marrowby & Vane, Studio Vrij, and Cragsley Outfitters Columns: - tenant_name - ticket_count (2025, resolved tickets with both first_response_at and resolved_at) - avg_first_response_minutes (first_response_at - created_at) - avg_post_response_minutes (resolved_at - first_response_at) - avg_resolution_minutes (resolved_at - created_at, as a check that the two components sum correctly) Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31, restricted to tenant_id for the three named tenants, and to tickets with both first_response_at and resolved_at not null. Source table: tickets. Order by avg_post_response_minutes descending. Round all minute figures to whole numbers. Goal: if avg_post_response_minutes is disproportionately large for Marrowby & Vane / Studio Vrij relative to Cragsley, the delay is happening while the ticket is already being worked (consistent with waiting on something external, e.g. a shipment) rather than agents being slow to pick it up. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect t.name as tenant_name, count(*) as ticket_count, round(avg(extract(epoch from (tk.first_response_at - tk.created_at))/60)::numeric) as avg_first_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.first_response_at))/60)::numeric) as avg_post_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.created_at))/60)::numeric) as avg_resolution_minutes from tickets tk join tenants t on t.id = tk.tenant_id where t.name in ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') and tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' and tk.first_response_at is not null and tk.resolved_at is not null group by t.name order by avg_post_response_minutes desc, tenant_name
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, ticket_count, avg_first_response_minutes, avg_post_response_minutes, avg_resolution_minutes
submitselect t.name as tenant_name, count(*) as ticket_count, round(avg(extract(epoch from (tk.first_response_at - tk.created_at))/60)::numeric) as avg_first_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.first_response_at))/60)::numeric) as avg_post_response_minutes, round(avg(extract(epoch from (tk.resolved_at - tk.created_at))/60)::numeric) as avg_resolution_minutes from tickets tk join tenants t on t.id = tk.tenant_id where t.name in ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') and tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' and tk.first_response_at is not null and tk.resolved_at is not null group by t.name order by avg_post_response_minutes desc, tenant_name
Submitted to tonight's batch. Your work on this request is done.