Queries · q181 · Head of B2B Commerce · week 1, night 4
“Among reps who own B2B wholesale new-business opportunities, are a few reps clearly underperforming on win rate or cycle time, or is performance broadly similar?”
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 4, request 1 of 3
Night 3 take: two of three reports came back broken/empty, and I need my last night to fix the essential one and still land priority 5. The merchant-level report (Report 1) worked but had a clear bug: avg_amount_eur was NULL for 4 of 5 merchants (all the EUR ones) — almost certainly a failed EUR-to-EUR identity lookup in exchange_rates rather than a real data gap. The rep-level report (Report 2) came back with 0 rows because the engineer used an exact-match tenant filter (industry IN (...)) instead of the pattern that's worked all week, so it silently matched nothing — this is still an open, important gap (Patrick's Q4) and needs a clean re-run. The open-pipeline-age report (Report 3) also returned 0 rows, but I'm treating that as a real, confirmed finding rather than a bug: night 1's stage report already showed zero open opportunities at every New-business stage for every wholesale tenant, so there is genuinely no live open new-business pipeline in the warehouse right now — worth flagging to Patrick/Samuel as 'we can't assess forward-looking staleness risk because there's no open pipeline to look at; the only staleness signal we have is backward-looking (lost deals average ~35 days open vs ~9 for won).' Spending tonight's three slots on: (1) the corrected rep-level report with an explicit, unambiguous tenant-name filter, (2) a corrected merchant-level report with an explicit EUR-conversion fix, and (3) a deal-size-band breakdown to chase the odd avg-vs-median flip in the won/lost amount comparison, which gives a real answer to Patrick's optional #5 on deal size vs win rate. Payment terms (also part of #5) aren't captured anywhere in the B2B CRM/deals schema for the wholesaler's own customers, so I'm leaving that out of the memo as 'not measurable from current data' rather than forcing a weak proxy.
The engineer · 1 dry run on the empty schema, then the submission
WITH scoped AS ( SELECT o.id, o.owner_user_id, o.tenant_id, o.currency_code, o.amount, o.is_won, o.closed_at, o.created_at, t.name AS merchant, u.full_name AS rep_name FROM opportunities o JOIN tenants t ON t.id = o.tenant_id JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id AND p.name = 'New business' JOIN users u ON u.id = o.owner_user_id AND u.tenant_id = o.tenant_id WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') AND o.is_closed = true ), rated AS ( SELECT s.*, CASE WHEN s.currency_code = 'EUR' THEN s.amount ELSE s.amount / ( SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of <= s.closed_at::date ORDER BY er.as_of DESC LIMIT 1 ) END AS amount_eur FROM scoped s ) SELECT rep_name, merchant, COUNT(*) AS opportunities_closed, COUNT(*) FILTER (WHERE is_won) AS closed_won, COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost, ROUND(100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400.0)::numeric, 1) AS avg_days_open, ROUND(AVG(amount_eur)::numeric, 2) AS avg_amount_eur FROM rated GROUP BY owner_user_id, rep_name, merchant HAVING COUNT(*) >= 3 ORDER BY win_rate_pct ASC, opportunities_closed DESC, rep_name ASC
“Query compiles and returns the requested columns. Submitting.”
“Submitted. Summary of what the query does:
- Scopes opportunities to the 5 named wholesale tenants, each tenant's own pipelines.name = 'New business', and is_closed = true.
- Converts amount to EUR directly when currency_code = 'EUR', otherwise divides by the nearest exchange_rates rate (EUR→currency) on or before closed_at.
- Groups by rep (owner_user_id/full_name) and merchant, excluding reps with fewer than 3 closed opportunities via HAVING.
- Outputs win rate, average cycle time in days, and average EUR amount, ordered worst win-rate first.
One caveat noted to the analyst: since the report's grain is one row per qualifying rep, the count of reps excluded by the <3 threshold isn't representable as a row in this same table — I flagged that in the submit note so it can be pulled separately if needed for the memo.”
As Daniel Kim saw it, from the small dataset the agents worked on
rep_name | merchant | opportunities_closed | closed_won | closed_lost | win_rate_pct | avg_days_open | avg_amount_eur --------------------+-------------------------------+----------------------+------------+-------------+--------------+---------------+--------------- Youssef Meijer | Polder Packaging | 7 | 4 | 3 | 57.1 | 20.7 | 7828.57 Logan Roberts | Ballycorran Industrial Supply | 40 | 32 | 8 | 80.0 | 17.8 | 3867.50 Lotte van den Broek | Midland Safety Wear | 18 | 15 | 3 | 83.3 | 9.6 | 5800.00 Greta Schmitt | Hanse Industriebedarf | 12 | 10 | 2 | 83.3 | 8.9 | 4683.33 Joshua Jackson | Castell Fasteners | 21 | 18 | 3 | 85.7 | 11.6 | 7313.84 Niamh Harrison | Ballycorran Industrial Supply | 36 | 31 | 5 | 86.1 | 12.5 | 4280.56 Jesse Novotný | Midland Safety Wear | 22 | 19 | 3 | 86.4 | 11.1 | 4968.18 Megan Taylor | Castell Fasteners | 15 | 13 | 2 | 86.7 | 14.6 | 6096.30 Thijs Dijkstra | Polder Packaging | 9 | 8 | 1 | 88.9 | 13.1 | 5288.89 Sarah Hartmann | Hanse Industriebedarf | 5 | 5 | 0 | 100.0 | 12.1 | 7500.00 (10 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 scoped AS ( SELECT o.id, o.owner_user_id, o.tenant_id, o.currency_code, o.amount, o.is_won, o.closed_at, o.created_at, o.id AS id, o.owner_user_id AS owner_user_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.amount AS amount, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at, t.name AS merchant, u.full_name AS rep_name FROM opportunities o JOIN tenants t ON t.id = o.tenant_id JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id AND p.name = 'New business' JOIN users u ON u.id = o.owner_user_id AND u.tenant_id = o.tenant_id WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') AND o.is_closed = true),rated AS ( SELECT s.*, CASE WHEN s.currency_code = 'EUR' THEN s.amount ELSE s.amount / ( SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of <= s.closed_at::date ORDER BY er.as_of DESC LIMIT 1 AND er.as_of <= CAST(s.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST {{fetch_first(1)}} ) END AS amount_eur FROM scoped s)SELECT rep_name, merchant, COUNT(*) AS opportunities_closed, COUNT(*) FILTER (WHERE is_won) AS closed_won, COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost, ROUND(100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400.0)::numeric, 1) AS avg_days_open, ROUND(AVG(amount_eur)::numeric, 2) AS avg_amount_eur 100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*) AS win_rate_pct, AVG({{seconds_between(created_at, closed_at)}} / 86400.0) AS avg_days_open, AVG(amount_eur) AS avg_amount_eurFROM ratedGROUP BY owner_user_id, rep_name, merchantHAVING COUNT(*) >= 3ORDER BY win_rate_pct ASC, opportunities_closed DESC, rep_name ASCORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, rep_name ASC NULLS LAST, owner_user_id ASC NULLS LAST
WITH scoped AS ( SELECT o.id AS id, o.owner_user_id AS owner_user_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.amount AS amount, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at, t.name AS merchant, u.full_name AS rep_name FROM opportunities o JOIN tenants t ON t.id = o.tenant_id JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id AND p.name = 'New business' JOIN users u ON u.id = o.owner_user_id AND u.tenant_id = o.tenant_id WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') AND o.is_closed = true ), rated AS ( SELECT s.*, CASE WHEN s.currency_code = 'EUR' THEN s.amount ELSE s.amount / ( SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of <= CAST(s.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST FETCH FIRST 1 ROWS ONLY ) END AS amount_eur FROM scoped s ) SELECT rep_name, merchant, COUNT(*) AS opportunities_closed, COUNT(*) FILTER (WHERE is_won) AS closed_won, COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost, 100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*) AS win_rate_pct, AVG(CAST(EXTRACT(EPOCH FROM (closed_at - created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open, AVG(amount_eur) AS avg_amount_eur FROM rated GROUP BY owner_user_id, rep_name, merchant HAVING COUNT(*) >= 3 ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, rep_name ASC NULLS LAST, owner_user_id ASC NULLS LAST
WITH scoped AS ( SELECT o.id AS id, o.owner_user_id AS owner_user_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.amount AS amount, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at, t.name AS merchant, u.full_name AS rep_name FROM opportunities o JOIN tenants t ON t.id = o.tenant_id JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id AND p.name = 'New business' JOIN users u ON u.id = o.owner_user_id AND u.tenant_id = o.tenant_id WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') AND o.is_closed = true ), rated AS ( SELECT s.*, CASE WHEN s.currency_code = 'EUR' THEN s.amount ELSE s.amount / ( SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of <= CAST(s.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST LIMIT 1 ) END AS amount_eur FROM scoped s ) SELECT rep_name, merchant, COUNT(*) AS opportunities_closed, COUNT(*) FILTER (WHERE is_won) AS closed_won, COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost, 100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*) AS win_rate_pct, AVG(CAST(EXTRACT(EPOCH FROM (closed_at - created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open, AVG(amount_eur) AS avg_amount_eur FROM rated GROUP BY owner_user_id, rep_name, merchant HAVING COUNT(*) >= 3 ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, rep_name ASC NULLS LAST, owner_user_id ASC NULLS LAST
WITH scoped AS ( SELECT o.id AS id, o.owner_user_id AS owner_user_id, o.tenant_id AS tenant_id, o.currency_code AS currency_code, o.amount AS amount, o.is_won AS is_won, o.closed_at AS closed_at, o.created_at AS created_at, t.name AS merchant, u.full_name AS rep_name FROM opportunities o JOIN tenants t ON t.id = o.tenant_id JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id AND p.name = 'New business' JOIN users u ON u.id = o.owner_user_id AND u.tenant_id = o.tenant_id WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') AND o.is_closed = true ), rated AS ( SELECT s.*, CASE WHEN s.currency_code = 'EUR' THEN s.amount ELSE s.amount / ( SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of <= CAST(s.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST FETCH FIRST 1 ROWS ONLY ) END AS amount_eur FROM scoped s ) SELECT rep_name, merchant, COUNT(*) AS opportunities_closed, COUNT(*) FILTER (WHERE is_won) AS closed_won, COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost, 100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*) AS win_rate_pct, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, closed_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0) AS avg_days_open, AVG(amount_eur) AS avg_amount_eur FROM rated GROUP BY owner_user_id, rep_name, merchant HAVING COUNT(*) >= 3 ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, rep_name ASC NULLS LAST, owner_user_id 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: Win Rate and Cycle Time by Sales Rep (New Business) — Corrected
Question: Among reps who own B2B wholesale new-business opportunities, are a few reps clearly underperforming on win rate or cycle time, or is performance broadly similar?
Grain: one row per sales rep (opportunities.owner_user_id)
Columns:
- rep_name
- merchant
- opportunities_closed
- closed_won
- closed_lost
- win_rate_pct
- avg_days_open
- avg_amount_eur
Details: Last night's attempt at this exact report returned 0 rows; the engineer's note said it filtered tenants with an exact match (industry IN ('industrial supplies','office equipment')) rather than the ILIKE pattern used successfully in earlier reports, which likely matched zero tenants. To remove all ambiguity, please filter explicitly by tenant name instead: tenants.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') — these are the 5 wholesale merchants confirmed present in every prior report this week. Filter opportunities to pipeline_id = that tenant's 'New business' pipeline (join pipelines on name = 'New business'), is_closed = true, grouped by owner_user_id (join users for full_name as rep_name, tenants for merchant name). Exclude reps with fewer than 3 closed opportunities in scope (note the excluded rep count in the report notes). avg_days_open = avg(closed_at - created_at) in days. For avg_amount_eur: if the opportunity's currency_code = 'EUR', amount_eur = amount directly (do not rely on a self-referential EUR/EUR row in exchange_rates, which may not exist and produced NULLs in a prior report); otherwise amount_eur = amount / rate where rate is from exchange_rates with base_code = 'EUR', quote_code = opportunity.currency_code, as_of = the nearest date on or before closed_at. Order by win_rate_pct ascending (worst first), tie-break by opportunities_closed descending, then rep_name ascending.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runWITH scoped AS ( SELECT o.id, o.owner_user_id, o.tenant_id, o.currency_code, o.amount, o.is_won, o.closed_at, o.created_at, t.name AS merchant, u.full_name AS rep_name FROM opportunities o JOIN tenants t ON t.id = o.tenant_id JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id AND p.name = 'New business' JOIN users u ON u.id = o.owner_user_id AND u.tenant_id = o.tenant_id WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') AND o.is_closed = true ), rated AS ( SELECT s.*, CASE WHEN s.currency_code = 'EUR' THEN s.amount ELSE s.amount / ( SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of <= s.closed_at::date ORDER BY er.as_of DESC LIMIT 1 ) END AS amount_eur FROM scoped s ) SELECT rep_name, merchant, COUNT(*) AS opportunities_closed, COUNT(*) FILTER (WHERE is_won) AS closed_won, COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost, ROUND(100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400.0)::numeric, 1) AS avg_days_open, ROUND(AVG(amount_eur)::numeric, 2) AS avg_amount_eur FROM rated GROUP BY owner_user_id, rep_name, merchant HAVING COUNT(*) >= 3 ORDER BY win_rate_pct ASC, opportunities_closed DESC, rep_name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: rep_name, merchant, opportunities_closed, closed_won, closed_lost, win_rate_pct, avg_days_open, avg_amount_eur
submitWITH scoped AS ( SELECT o.id, o.owner_user_id, o.tenant_id, o.currency_code, o.amount, o.is_won, o.closed_at, o.created_at, t.name AS merchant, u.full_name AS rep_name FROM opportunities o JOIN tenants t ON t.id = o.tenant_id JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id AND p.name = 'New business' JOIN users u ON u.id = o.owner_user_id AND u.tenant_id = o.tenant_id WHERE t.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging') AND o.is_closed = true ), rated AS ( SELECT s.*, CASE WHEN s.currency_code = 'EUR' THEN s.amount ELSE s.amount / ( SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = s.currency_code AND er.as_of <= s.closed_at::date ORDER BY er.as_of DESC LIMIT 1 ) END AS amount_eur FROM scoped s ) SELECT rep_name, merchant, COUNT(*) AS opportunities_closed, COUNT(*) FILTER (WHERE is_won) AS closed_won, COUNT(*) FILTER (WHERE NOT is_won) AS closed_lost, ROUND(100.0 * COUNT(*) FILTER (WHERE is_won) / COUNT(*), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 86400.0)::numeric, 1) AS avg_days_open, ROUND(AVG(amount_eur)::numeric, 2) AS avg_amount_eur FROM rated GROUP BY owner_user_id, rep_name, merchant HAVING COUNT(*) >= 3 ORDER BY win_rate_pct ASC, opportunities_closed DESC, rep_name ASC
Submitted to tonight's batch. Your work on this request is done.