Queries · q045 · VP Customer Experience · week 1, night 3
“How satisfied are shoppers with support interactions overall, and is satisfaction trending up or down?”
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 1
Subject: Where I'd like to start — support & shopper experience
Hi Mateo,
Good to have you on this with me. I'm still building my map of the business, so I want to keep this week broad — I'd rather get a solid first read on a few big questions than a deep dive on one. Here's what's on my mind, roughly in priority order:
What I need these for: I'm meeting merchant success and the exec team soon and want a credible, numbers-backed view of shopper experience — not just war stories. I also want to start spotting the two or three things that, if we fixed them, would move the needle most broadly across merchants, rather than chasing individual complaints.
Given the slot constraints, let's prioritize #1 through #3 this week if we can't fit all five — those feel most urgent to me. Happy to talk through any of this before you lock in requests.
Thanks, Daniel
The analyst · week 1, night 3, request 3 of 3
Report 1 (night 2) shows the May-Jun 2025 anomaly is strongly concentrated in delivery-tagged tickets specifically (response/resolution hours for delivery tickets roughly 2-3x worse than 'other' tickets in those two months, whereas Nov/Dec spikes hit both groups evenly - that looks like genuine seasonal volume, not a delivery-specific problem). Report 2 pins the May-Jun spike almost entirely on one merchant: Studio Vrij had 86 tickets vs an expected ~16 (5.38x their normal rate), 83.7% delivery-tagged, and it was also the slowest-resolving merchant in the whole-year peer table from night 1. Every other merchant with a high spike ratio is tiny in absolute volume (noise), and the platform's other big merchants (Cragsley, Marrowby) were actually below their own baseline rate in May-Jun. So this looks like a single-merchant fulfillment incident, not a platform-wide issue - I want to confirm that with real delivery/shipment data before I write it into the memo as a finding. Report 3 (returns trend) is reassuring: resolve time is flat around ~8 days and rejection rate flat regardless of volume swings; the return-rate spikes track the same Nov/Dec seasonality as everything else. I want one merchant-level peer cut on returns (mirroring the support-speed peer report) to see if any merchant stands out the way Studio Vrij did on tickets. With priorities #1-3 now well covered, I'm using the third slot tonight to open priority #4 (CSAT) so there's something in hand for Friday's memo if priority #5 doesn't fit.
The engineer · 2 dry runs on the empty schema, then the submission
select to_char(date_trunc('month', submitted_at), 'YYYY-MM') as month, count(*) as csat_responses, round(avg(score)::numeric, 2) as avg_score, round(percentile_cont(0.5) within group (order by score)::numeric, 1) as median_score, round(100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*), 1) as pct_satisfied, round(100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*), 1) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc
select to_char(date_trunc('month', submitted_at), 'YYYY-MM') as month, strftime(date_trunc('month', submitted_at), '%Y-%m') as month, count(*) as csat_responses, round(avg(score)::numeric, 2) as avg_score, round(percentile_cont(0.5) within group (order by score)::numeric, 1) as median_score, round(100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*), 1) as pct_satisfied, round(100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*), 1) as pct_dissatisfiedfrom csat_responseswhere submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01'group by date_trunc('month', submitted_at)order by month asc
“Query compiles with the expected columns. Submitting.”
“Submitted. The report covers all 36 months (2023-01 through 2025-12), one row per month, with CSAT response count, mean, median, and the two satisfaction share metrics as requested.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
month | csat_responses | avg_score | median_score | pct_satisfied | pct_dissatisfied --------+----------------+-----------+--------------+---------------+----------------- 2023-01 | 22 | 3.55 | 4.0 | 59.1 | 18.2 2023-02 | 27 | 3.15 | 3.0 | 37.0 | 25.9 2023-03 | 18 | 3.17 | 3.5 | 50.0 | 27.8 2023-04 | 20 | 3.85 | 4.0 | 70.0 | 5.0 2023-05 | 33 | 3.97 | 4.0 | 63.6 | 6.1 2023-06 | 36 | 3.25 | 3.0 | 38.9 | 19.4 2023-07 | 25 | 3.24 | 3.0 | 40.0 | 24.0 2023-08 | 36 | 3.58 | 4.0 | 55.6 | 16.7 2023-09 | 23 | 3.78 | 4.0 | 56.5 | 17.4 2023-10 | 40 | 3.53 | 4.0 | 52.5 | 15.0 2023-11 | 54 | 3.57 | 4.0 | 55.6 | 14.8 2023-12 | 69 | 3.25 | 3.0 | 47.8 | 30.4 2024-01 | 36 | 3.31 | 4.0 | 55.6 | 36.1 2024-02 | 33 | 3.88 | 4.0 | 75.8 | 12.1 2024-03 | 27 | 3.00 | 3.0 | 44.4 | 29.6 2024-04 | 39 | 3.33 | 3.0 | 41.0 | 23.1 2024-05 | 39 | 3.44 | 3.0 | 48.7 | 25.6 2024-06 | 39 | 3.59 | 4.0 | 56.4 | 20.5 2024-07 | 34 | 3.59 | 4.0 | 52.9 | 23.5 2024-08 | 36 | 3.75 | 4.0 | 58.3 | 8.3 2024-09 | 37 | 3.57 | 4.0 | 54.1 | 18.9 2024-10 | 37 | 3.51 | 4.0 | 54.1 | 18.9 2024-11 | 50 | 3.22 | 3.0 | 38.0 | 28.0 2024-12 | 124 | 3.14 | 3.0 | 42.7 | 31.5 2025-01 | 43 | 2.72 | 2.0 | 34.9 | 51.2 2025-02 | 37 | 3.32 | 4.0 | 51.4 | 24.3 2025-03 | 28 | 3.61 | 4.0 | 53.6 | 21.4 2025-04 | 51 | 3.90 | 4.0 | 68.6 | 13.7 2025-05 | 51 | 3.39 | 4.0 | 52.9 | 27.5 2025-06 | 52 | 3.06 | 3.0 | 38.5 | 30.8 2025-07 | 46 | 3.35 | 3.5 | 50.0 | 21.7 2025-08 | 40 | 3.65 | 4.0 | 52.5 | 17.5 2025-09 | 54 | 3.43 | 4.0 | 51.9 | 22.2 2025-10 | 64 | 3.52 | 4.0 | 54.7 | 20.3 2025-11 | 72 | 3.29 | 3.0 | 44.4 | 27.8 2025-12 | 162 | 2.88 | 3.0 | 33.3 | 38.9 (36 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
select strftime(date_trunc('month', submitted_at), '%Y-%m') as month, {{year_month(date_trunc('month', submitted_at))}} as month, count(*) as csat_responses, round(avg(score)::numeric, 2) as avg_score, round(percentile_cont(0.5) within group (order by score)::numeric, 1) as median_score, round(100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*), 1) as pct_satisfied, round(100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*), 1) as pct_dissatisfied avg(cast(score as numeric(18,6))) as avg_score, {{percentile_cont(0.5, score)}} as median_score, 100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*) as pct_satisfied, 100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*) as pct_dissatisfiedfrom csat_responseswhere submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01'group by date_trunc('month', submitted_at)order by month ascorder by month asc NULLS LAST
select strftime(date_trunc('month', submitted_at), '%Y-%m') as month, count(*) as csat_responses, avg(cast(score as numeric(18,6))) as avg_score, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) as median_score, 100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*) as pct_satisfied, 100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc NULLS LAST
select to_char(date_trunc('month', submitted_at), 'YYYY-MM') as month, count(*) as csat_responses, avg(cast(score as numeric(18,6))) as avg_score, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) as median_score, 100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*) as pct_satisfied, 100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc NULLS LAST
select to_char(CAST(date_trunc('month', submitted_at) AS timestamp), 'YYYY-MM') as month, count(*) as csat_responses, avg(cast(score as numeric(18,6))) as avg_score, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) as median_score, 100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*) as pct_satisfied, 100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc NULLS LAST
select to_char(date_trunc('month', submitted_at), 'YYYY-MM') as month, count(*) as csat_responses, avg(cast(score as numeric(18,6))) as avg_score, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(score AS DOUBLE PRECISION)) as median_score, 100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*) as pct_satisfied, 100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc NULLS LAST
select formatDateTime(date_trunc('month', submitted_at), '%Y-%m') as month, count(*) as csat_responses, avg(cast(score as numeric(18,6))) as avg_score, quantileExactInclusive(0.5)(toFloat64(score)) as median_score, 100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*) as pct_satisfied, 100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc 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 Mateo Ruiz (analyst for the VP Customer Experience): Title: Shopper satisfaction (CSAT) trend, monthly Question: How satisfied are shoppers with support interactions overall, and is satisfaction trending up or down? Grain: one calendar month, platform-wide Columns: - month (YYYY-MM) - CSAT responses - avg score (1-5) - median score (1-5) - % satisfied (score >= 4) - % dissatisfied (score <= 2) Details: Source: csat_responses. Grain the month on submitted_at. Window: 2023-01-01 through 2025-12-31 (36 months). avg/median score computed directly over csat_responses.score for that month. '% satisfied' = count of responses with score>=4 / total responses that month, percentage rounded to 1 decimal; '% dissatisfied' = count with score<=2 / total responses that month, percentage rounded to 1 decimal. Round avg score to 2 decimals, median to 1 decimal. Order by month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runselect to_char(date_trunc('month', submitted_at), 'YYYY-MM') as month, count(*) as csat_responses, round(avg(score)::numeric, 2) as avg_score, round(percentile_cont(0.5) within group (order by score)::numeric, 1) as median_score, round(100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*), 1) as pct_satisfied, round(100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*), 1) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runselect strftime(date_trunc('month', submitted_at), '%Y-%m') as month, count(*) as csat_responses, round(avg(score)::numeric, 2) as avg_score, round(percentile_cont(0.5) within group (order by score)::numeric, 1) as median_score, round(100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*), 1) as pct_satisfied, round(100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*), 1) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, csat_responses, avg_score, median_score, pct_satisfied, pct_dissatisfied
submitselect strftime(date_trunc('month', submitted_at), '%Y-%m') as month, count(*) as csat_responses, round(avg(score)::numeric, 2) as avg_score, round(percentile_cont(0.5) within group (order by score)::numeric, 1) as median_score, round(100.0 * sum(case when score >= 4 then 1 else 0 end) / count(*), 1) as pct_satisfied, round(100.0 * sum(case when score <= 2 then 1 else 0 end) / count(*), 1) as pct_dissatisfied from csat_responses where submitted_at >= timestamp '2023-01-01' and submitted_at < timestamp '2026-01-01' group by date_trunc('month', submitted_at) order by month asc
Submitted to tonight's batch. Your work on this request is done.