Queries · q404 · Support Operations Manager · week 2, night 3
“Do Marrowby & Vane and Studio Vrij rely on a slower or less reliable carrier than Cragsley Outfitters, which would explain their outsized delivery/where-is-my-order ticket resolution times?”
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 1 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 · 1 dry run on the empty schema, then the submission
SELECT t.name AS tenant_name, s.carrier, count(*) AS shipment_count, round(100.0 * count(*) / sum(count(*)) over (partition by t.name), 1) AS pct_of_tenant_shipments, round(avg(extract(epoch from (s.delivered_at - s.shipped_at)) / 86400.0) filter (where s.delivered_at is not null and s.shipped_at is not null), 1) AS avg_delivery_days, round(100.0 * count(*) filter (where s.delivered_at is not null and s.shipped_at is not null and s.delivered_at - s.shipped_at <= interval '3 days') / nullif(count(*) filter (where s.delivered_at is not null and s.shipped_at is not null), 0), 1) AS pct_delivered_within_3_days FROM shipments s JOIN tenants t ON t.id = s.tenant_id WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') AND s.shipped_at >= timestamp '2025-01-01' AND s.shipped_at < timestamp '2026-01-01' GROUP BY t.name, s.carrier ORDER BY t.name, shipment_count DESC
“Query compiles and matches the requested grain and columns.”
“Submitted. The report gives one row per (tenant, carrier) for Marrowby & Vane, Studio Vrij, and Cragsley Outfitters, covering 2025 shipments, with shipment counts, each carrier's share of that tenant's volume, average delivery days, and the share delivered within 3 days — ordered by tenant then by shipment volume so the dominant carrier per merchant is easy to spot and compare.”
As Ben Carter saw it, from the small dataset the agents worked on
tenant_name | carrier | shipment_count | pct_of_tenant_shipments | avg_delivery_days | pct_delivered_within_3_days --------------------+----------------------+----------------+-------------------------+-------------------+---------------------------- Cragsley Outfitters | Liberty Ground | 1634 | 46.9 | 2.3 | 80.3 Cragsley Outfitters | Trailhead Express | 1253 | 36.0 | 2.5 | 73.1 Cragsley Outfitters | Prairie Line Express | 511 | 14.7 | 2.6 | 70.4 Cragsley Outfitters | Landfall Freight | 85 | 2.4 | 4.3 | 24.1 Marrowby & Vane | Albion Parcel | 863 | 51.6 | 2.5 | 76.8 Marrowby & Vane | Regent Parcels | 260 | 15.6 | 2.3 | 76.7 Marrowby & Vane | Kennet Freight | 219 | 13.1 | 1.9 | 90.1 Marrowby & Vane | Landfall Freight | 192 | 11.5 | 6.4 | 9.0 Marrowby & Vane | Lowlands Express | 137 | 8.2 | 6.1 | 9.9 Studio Vrij | Lowlands Express | 347 | 58.1 | 2.5 | 71.0 Studio Vrij | Polder Post | 188 | 31.5 | 2.7 | 63.2 Studio Vrij | Landfall Freight | 62 | 10.4 | 4.3 | 18.3 (12 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, s.carrier, s.carrier AS carrier, count(*) AS shipment_count, round(100.0 * count(*) / sum(count(*)) over (partition by t.name), 1) AS pct_of_tenant_shipments, round(avg(extract(epoch from (s.delivered_at - s.shipped_at)) / 86400.0) filter (where s.delivered_at is not null and s.shipped_at is not null), 1) AS avg_delivery_days, round(100.0 * count(*) filter (where s.delivered_at is not null and s.shipped_at is not null and s.delivered_at - s.shipped_at <= interval '3 days') / nullif(count(*) filter (where s.delivered_at is not null and s.shipped_at is not null), 0), 1) AS pct_delivered_within_3_days 100.0 * count(*) / sum(count(*)) over (partition by t.name) AS pct_of_tenant_shipments, avg({{seconds_between(s.shipped_at, s.delivered_at)}} / 86400.0) filter (where s.delivered_at is not null and s.shipped_at is not null) AS avg_delivery_days, 100.0 * count(*) filter (where s.delivered_at is not null and s.shipped_at is not null and s.delivered_at - s.shipped_at <= interval '3 days') / nullif(count(*) filter (where s.delivered_at is not null and s.shipped_at is not null), 0) AS pct_delivered_within_3_daysFROM shipments sJOIN tenants t ON t.id = s.tenant_idWHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') AND s.shipped_at >= timestamp '2025-01-01' AND s.shipped_at < timestamp '2026-01-01'GROUP BY t.name, s.carrierORDER BY t.name, shipment_count DESCORDER BY t.name NULLS LAST, shipment_count DESC NULLS FIRST, s.carrier NULLS LAST
SELECT t.name AS tenant_name, s.carrier AS carrier, count(*) AS shipment_count, 100.0 * count(*) / sum(count(*)) over (partition by t.name) AS pct_of_tenant_shipments, avg(CAST(EXTRACT(EPOCH FROM (s.delivered_at - s.shipped_at)) AS NUMERIC(18,6)) / 86400.0) filter (where s.delivered_at is not null and s.shipped_at is not null) AS avg_delivery_days, 100.0 * count(*) filter (where s.delivered_at is not null and s.shipped_at is not null and s.delivered_at - s.shipped_at <= interval '3 days') / nullif(count(*) filter (where s.delivered_at is not null and s.shipped_at is not null), 0) AS pct_delivered_within_3_days FROM shipments s JOIN tenants t ON t.id = s.tenant_id WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') AND s.shipped_at >= timestamp '2025-01-01' AND s.shipped_at < timestamp '2026-01-01' GROUP BY t.name, s.carrier ORDER BY t.name NULLS LAST, shipment_count DESC NULLS FIRST, s.carrier NULLS LAST
SELECT t.name AS tenant_name, s.carrier AS carrier, count(*) AS shipment_count, 100.0 * count(*) / sum(count(*)) over (partition by t.name) AS pct_of_tenant_shipments, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', s.shipped_at, s.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0) filter (where s.delivered_at is not null and s.shipped_at is not null) AS avg_delivery_days, 100.0 * count(*) filter (where s.delivered_at is not null and s.shipped_at is not null and s.delivered_at - s.shipped_at <= interval '3 days') / nullif(count(*) filter (where s.delivered_at is not null and s.shipped_at is not null), 0) AS pct_delivered_within_3_days FROM shipments s JOIN tenants t ON t.id = s.tenant_id WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') AND s.shipped_at >= timestamp '2025-01-01' AND s.shipped_at < timestamp '2026-01-01' GROUP BY t.name, s.carrier ORDER BY t.name NULLS LAST, shipment_count DESC NULLS FIRST, s.carrier 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: Carrier mix and on-time delivery performance, flagged merchants vs. Cragsley Question: Do Marrowby & Vane and Studio Vrij rely on a slower or less reliable carrier than Cragsley Outfitters, which would explain their outsized delivery/where-is-my-order ticket resolution times? Grain: one row per (tenant, carrier), restricted to Marrowby & Vane, Studio Vrij, and Cragsley Outfitters Columns: - tenant_name - carrier - shipment_count (shipped_at in 2025) - pct_of_tenant_shipments - avg_delivery_days (delivered_at - shipped_at, for shipments with both timestamps) - pct_delivered_within_3_days Details: Window: shipments.shipped_at between 2025-01-01 and 2025-12-31, restricted to tenant_id for the three named tenants (shipments has tenant_id directly). Source table: shipments. Order by tenant_name, then shipment_count descending within tenant. Round pct columns to 1 decimal percent, avg_delivery_days to 1 decimal. Goal: see if Studio Vrij/Marrowby & Vane are concentrated on a carrier with materially worse on-time performance than whatever Cragsley mostly uses. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name AS tenant_name, s.carrier, count(*) AS shipment_count, round(100.0 * count(*) / sum(count(*)) over (partition by t.name), 1) AS pct_of_tenant_shipments, round(avg(extract(epoch from (s.delivered_at - s.shipped_at)) / 86400.0) filter (where s.delivered_at is not null and s.shipped_at is not null), 1) AS avg_delivery_days, round(100.0 * count(*) filter (where s.delivered_at is not null and s.shipped_at is not null and s.delivered_at - s.shipped_at <= interval '3 days') / nullif(count(*) filter (where s.delivered_at is not null and s.shipped_at is not null), 0), 1) AS pct_delivered_within_3_days FROM shipments s JOIN tenants t ON t.id = s.tenant_id WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') AND s.shipped_at >= timestamp '2025-01-01' AND s.shipped_at < timestamp '2026-01-01' GROUP BY t.name, s.carrier ORDER BY t.name, shipment_count DESC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, carrier, shipment_count, pct_of_tenant_shipments, avg_delivery_days, pct_delivered_within_3_days
submitSELECT t.name AS tenant_name, s.carrier, count(*) AS shipment_count, round(100.0 * count(*) / sum(count(*)) over (partition by t.name), 1) AS pct_of_tenant_shipments, round(avg(extract(epoch from (s.delivered_at - s.shipped_at)) / 86400.0) filter (where s.delivered_at is not null and s.shipped_at is not null), 1) AS avg_delivery_days, round(100.0 * count(*) filter (where s.delivered_at is not null and s.shipped_at is not null and s.delivered_at - s.shipped_at <= interval '3 days') / nullif(count(*) filter (where s.delivered_at is not null and s.shipped_at is not null), 0), 1) AS pct_delivered_within_3_days FROM shipments s JOIN tenants t ON t.id = s.tenant_id WHERE t.name IN ('Marrowby & Vane', 'Studio Vrij', 'Cragsley Outfitters') AND s.shipped_at >= timestamp '2025-01-01' AND s.shipped_at < timestamp '2026-01-01' GROUP BY t.name, s.carrier ORDER BY t.name, shipment_count DESC
Submitted to tonight's batch. Your work on this request is done.