Queries · q173 · Head of B2B Commerce · week 1, night 1
“From an opportunity being opened to it closing (won or lost), how long does that take for our B2B wholesale merchants, and is that cycle time getting longer or shorter over the last three years?”
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 Samuel Whitcombe, Head of Merchant Success
B2B merchants say their sales teams struggle. How do their pipelines convert from lead to won deal, how long does it take, and what predicts winning or losing?
The manager, at the start of week 1
Note to Daniel Kim — B2B pipeline health
Daniel,
Samuel and I need to give the B2B wholesale merchants (and our own leadership) a clear-eyed view of why their sales teams are struggling to close business. This feeds a joint recommendation Samuel and I are making next week on where to invest — better pipeline coaching/tooling for merchants vs. changes to how we structure stages, quotes and terms in the product. So I want the picture to be accurate more than exhaustive — pick your battles across the nights you have.
Here's what I actually want to know, in plain terms:
Priority order is 1, 2, 3 first — those directly shape the recommendation. 4 and 5 are valuable but can slip if you run out of slots this week.
Keep each report to one clean question — I'd rather have four sharp answers than one crowded one. Flag anything that looks like a data quality issue rather than a real trend.
Thanks — looking forward to the Friday memo.
Patrick
Subject: A few questions from the Merchant Success side
Hi Daniel,
Good to see this getting attention this week. A few things I'd love your analysis to help answer, from the merchant-health angle:
Happy to compare notes before you finalize anything for Patrick.
Best, Samuel
The analyst · week 1, night 1, request 2 of 3
Night 1: going straight at Patrick's top three priorities (graveyard stage, cycle-time trend, won-vs-lost drivers), which also cover most of Samuel's three questions (stage friction, what separates wins/losses, implicitly sets up stale-pipeline-risk follow-up for night 2). All three reports scope to B2B wholesale tenants via an industry-text heuristic (industrial supplies / office equipment / wholesale) since there's no explicit 'B2B wholesaler' flag on tenants — I've asked the report to surface the merchant list so I can sanity-check the filter caught the right handful of tenants before going further. Kept each report to one grain: (tenant x stage) for the funnel, (month) for cycle time, and (won/lost) for the comparison, per the one-question-one-report rule. Held back Samuel's 'stale pipeline = risk' question and Patrick's #4 (merchant/rep dispersion) and #5 (terms/size correlation) for later nights once I see where the real friction shows up — no point spending slots on dispersion or terms analysis if the stage report shows everyone dying at the same place for the same reason.
The engineer · 2 dry runs on the empty schema, then the submission
SELECT to_char(o.closed_at, 'YYYY-MM') AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, round(100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0), 1) AS win_rate_pct, round(avg(DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS avg_cycle_days, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS median_cycle_days FROM opportunities o JOIN accounts a ON a.id = o.account_id JOIN tenants t ON t.id = a.tenant_id WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01' GROUP BY to_char(o.closed_at, 'YYYY-MM') ORDER BY month ASC
SELECT to_char(o.closed_at, 'YYYY-MM') AS month, cd.month_label AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, round(100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0), 1) AS win_rate_pct, round(avg(DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS avg_cycle_days, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS median_cycle_daysFROM opportunities oJOIN accounts a ON a.id = o.account_idJOIN tenants t ON t.id = a.tenant_idJOIN calendar_days cd ON cd.calendar_date = CAST(o.closed_at AS date)WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'GROUP BY to_char(o.closed_at, 'YYYY-MM')GROUP BY cd.month_labelORDER BY month ASC
“Query compiles correctly. Submitting now.”
“The report has been submitted. Summary of what it delivers:
- Grain: one row per month (YYYY-MM, based on closed_at)
- Scope: opportunities at B2B wholesale tenants (industry matching industrial/office equipment/wholesale), closed (is_closed = true, closed_at not null), 2023-01-01 through 2025-12-31
- Columns: opportunities closed, closed won, closed lost, win rate %, average cycle days, median cycle days
Noted limitation in the submit note: cycle time is measured as closed_at - created_at on the opportunity record itself, not from the originating lead, since opportunities have no FK back to leads — this was flagged as a known proxy per the request's own caveat.”
As Daniel Kim saw it, from the small dataset the agents worked on
month | opportunities_closed | closed_won | closed_lost | win_rate_pct | avg_cycle_days | median_cycle_days --------+----------------------+------------+-------------+--------------+----------------+------------------ 2023-01 | 5 | 5 | 0 | 100.0 | 12.0 | 15.0 2023-02 | 5 | 5 | 0 | 100.0 | 16.6 | 12.0 2023-03 | 8 | 5 | 3 | 62.5 | 22.8 | 21.5 2023-04 | 5 | 5 | 0 | 100.0 | 17.2 | 21.0 2023-05 | 6 | 5 | 1 | 83.3 | 24.2 | 22.5 2023-06 | 9 | 6 | 3 | 66.7 | 21.1 | 24.0 2023-07 | 5 | 3 | 2 | 60.0 | 33.6 | 34.0 2023-08 | 7 | 6 | 1 | 85.7 | 17.4 | 16.0 2023-09 | 10 | 8 | 2 | 80.0 | 30.5 | 35.0 2023-10 | 9 | 8 | 1 | 88.9 | 26.1 | 25.0 2023-11 | 12 | 8 | 4 | 66.7 | 24.5 | 23.0 2023-12 | 7 | 4 | 3 | 57.1 | 22.7 | 26.0 2024-01 | 11 | 9 | 2 | 81.8 | 15.6 | 16.0 2024-02 | 10 | 10 | 0 | 100.0 | 22.9 | 24.5 2024-03 | 8 | 8 | 0 | 100.0 | 26.5 | 30.0 2024-04 | 8 | 8 | 0 | 100.0 | 19.4 | 20.5 2024-05 | 12 | 10 | 2 | 83.3 | 21.6 | 20.0 2024-06 | 6 | 4 | 2 | 66.7 | 27.7 | 26.5 2024-07 | 10 | 7 | 3 | 70.0 | 28.8 | 26.5 2024-08 | 10 | 10 | 0 | 100.0 | 21.3 | 19.0 2024-09 | 7 | 7 | 0 | 100.0 | 25.9 | 23.0 2024-10 | 19 | 15 | 4 | 78.9 | 24.3 | 24.0 2024-11 | 12 | 8 | 4 | 66.7 | 24.1 | 21.5 2024-12 | 11 | 6 | 5 | 54.5 | 24.2 | 26.0 2025-01 | 11 | 8 | 3 | 72.7 | 25.5 | 26.0 2025-02 | 11 | 7 | 4 | 63.6 | 32.2 | 35.0 2025-03 | 12 | 9 | 3 | 75.0 | 20.5 | 21.0 2025-04 | 13 | 11 | 2 | 84.6 | 21.5 | 19.0 2025-05 | 10 | 10 | 0 | 100.0 | 26.7 | 26.5 2025-06 | 9 | 8 | 1 | 88.9 | 22.9 | 26.0 2025-07 | 11 | 11 | 0 | 100.0 | 21.8 | 22.0 2025-08 | 11 | 10 | 1 | 90.9 | 21.5 | 24.0 2025-09 | 14 | 13 | 1 | 92.9 | 22.7 | 22.0 2025-10 | 16 | 14 | 2 | 87.5 | 27.2 | 31.0 2025-11 | 18 | 13 | 5 | 72.2 | 26.4 | 27.0 2025-12 | 9 | 6 | 3 | 66.7 | 25.2 | 19.0 (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 cd.month_label AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, round(100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0), 1) AS win_rate_pct, round(avg(DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS avg_cycle_days, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS median_cycle_days 100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0) AS win_rate_pct, avg(EXTRACT(DAY FROM o.closed_at - o.created_at)) AS avg_cycle_days, {{percentile_cont(0.5, EXTRACT(DAY FROM o.closed_at - o.created_at))}} AS median_cycle_daysFROM opportunities oJOIN accounts a ON a.id = o.account_idJOIN tenants t ON t.id = a.tenant_idJOIN calendar_days cd ON cd.calendar_date = CAST(o.closed_at AS date)WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01'GROUP BY cd.month_labelORDER BY month ASCORDER BY month ASC NULLS LAST
SELECT cd.month_label AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, 100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0) AS win_rate_pct, avg(EXTRACT(DAY FROM o.closed_at - o.created_at)) AS avg_cycle_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(DAY FROM o.closed_at - o.created_at)) AS median_cycle_days FROM opportunities o JOIN accounts a ON a.id = o.account_id JOIN tenants t ON t.id = a.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.closed_at AS date) WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01' GROUP BY cd.month_label ORDER BY month ASC NULLS LAST
SELECT cd.month_label AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, 100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0) AS win_rate_pct, avg(EXTRACT(DAY FROM o.closed_at - o.created_at)) AS avg_cycle_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(DAY FROM o.closed_at - o.created_at) AS DOUBLE PRECISION)) AS median_cycle_days FROM opportunities o JOIN accounts a ON a.id = o.account_id JOIN tenants t ON t.id = a.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.closed_at AS date) WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01' GROUP BY cd.month_label ORDER BY month ASC NULLS LAST
SELECT cd.month_label AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, 100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0) AS win_rate_pct, avg(EXTRACT(DAY FROM o.closed_at - o.created_at)) AS avg_cycle_days, quantileExactInclusive(0.5)(toFloat64(EXTRACT(DAY FROM o.closed_at - o.created_at))) AS median_cycle_days FROM opportunities o JOIN accounts a ON a.id = o.account_id JOIN tenants t ON t.id = a.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.closed_at AS date) WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01' GROUP BY cd.month_label 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 Daniel Kim (analyst for the Head of B2B Commerce):
Title: B2B Wholesale Deal Cycle Time Trend by Month
Question: From an opportunity being opened to it closing (won or lost), how long does that take for our B2B wholesale merchants, and is that cycle time getting longer or shorter over the last three years?
Grain: one row per calendar month (based on the opportunity's closed_at month)
Columns:
- month (YYYY-MM)
- opportunities closed (count)
- closed won (count)
- closed lost (count)
- win rate (closed won / opportunities closed, %)
- average cycle days (closed_at - created_at)
- median cycle days (closed_at - created_at)
Details: Scope to the same B2B wholesale tenants as the stage report: tenants where industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'. Use opportunities where is_closed = true, closed_at is not null, for the full history 2023-01-01 through 2025-12-31. Cycle time = DATE_PART('day', closed_at - created_at) per opportunity; this is a proxy for the full lead-to-close journey since opportunities have no direct FK back to the originating lead — note this as a known limitation in the report. Group by month of closed_at (use calendar_days.month_label or an equivalent truncation). Round average and median cycle days 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(o.closed_at, 'YYYY-MM') AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, round(100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0), 1) AS win_rate_pct, round(avg(DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS avg_cycle_days, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS median_cycle_days FROM opportunities o JOIN accounts a ON a.id = o.account_id JOIN tenants t ON t.id = a.tenant_id WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01' GROUP BY to_char(o.closed_at, 'YYYY-MM') ORDER BY month ASC
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runSELECT cd.month_label AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, round(100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0), 1) AS win_rate_pct, round(avg(DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS avg_cycle_days, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS median_cycle_days FROM opportunities o JOIN accounts a ON a.id = o.account_id JOIN tenants t ON t.id = a.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.closed_at AS date) WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01' GROUP BY cd.month_label ORDER BY month ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, opportunities_closed, closed_won, closed_lost, win_rate_pct, avg_cycle_days, median_cycle_days
submitSELECT cd.month_label AS month, count(*) AS opportunities_closed, count(*) FILTER (WHERE o.is_won) AS closed_won, count(*) FILTER (WHERE NOT o.is_won) AS closed_lost, round(100.0 * count(*) FILTER (WHERE o.is_won) / NULLIF(count(*), 0), 1) AS win_rate_pct, round(avg(DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS avg_cycle_days, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY DATE_PART('day', o.closed_at - o.created_at))::numeric, 1) AS median_cycle_days FROM opportunities o JOIN accounts a ON a.id = o.account_id JOIN tenants t ON t.id = a.tenant_id JOIN calendar_days cd ON cd.calendar_date = CAST(o.closed_at AS date) WHERE (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') AND o.is_closed = true AND o.closed_at IS NOT NULL AND o.closed_at >= DATE '2023-01-01' AND o.closed_at < DATE '2026-01-01' GROUP BY cd.month_label ORDER BY month ASC
Submitted to tonight's batch. Your work on this request is done.