Queries · q178 · Head of B2B Commerce · week 1, night 3
“Is the acquisition struggle spread evenly across our B2B wholesale merchants, or concentrated in a few accounts?”
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 3, request 1 of 3
Night 2's key finding: the stage-history reconstruction came back 30/30 'no transition recorded' — change_history has zero coverage of stage_id changes for these lost opportunities. That's decisive on its own (not a sample-size fluke): the warehouse simply cannot tell us which CRM stage a deal died in, either from current state (always shows the terminal Closed Won/Lost stage) or from history (not logged). I'm treating that as answered-and-closed rather than spending another slot probing it further — it's itself the headline data-quality flag for the memo (reps/system aren't capturing stage progression, which is as much a coaching/hygiene story as a product one). The New-business-only refinement did give a very clean, usable signal instead: lost deals average ~35 days open and ~3.4 activities before dying, vs won deals closing in ~9 days with ~2.3 activities — a ~4x gap in cycle time is a much stronger 'where/why deals die' signal than a stage label would have been, and it directly answers Samuel's staleness question. Tonight's three reports build on that: merchant-level and rep-level win-rate/cycle-time breakdowns (Patrick Q4 / Samuel Q1 — is this widespread or concentrated), and an age-bucket view of the currently open pipeline benchmarked against the won (~9d) vs lost (~35d) cycle-time reference points, to quantify how much live pipeline is already past the point where deals historically still win (Samuel Q3). Holding deal-size/payment-terms correlation (Patrick's optional #5) for night 4 if the memo still has room after seeing tonight's results.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS merchant, COUNT(*) AS opportunities_closed, SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won, SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost, ROUND(100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0), 1) AS avg_days_open, ROUND(AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= o.closed_at::date ORDER BY er.as_of DESC LIMIT 1)), 2) AS avg_amount_eur 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 WHERE o.is_closed = true AND p.name = 'New business' AND (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') GROUP BY t.id, t.name ORDER BY win_rate_pct ASC, opportunities_closed DESC, t.name ASC
“Query compiles and returns the expected columns at the tenant grain.”
“Submitted. The report gives one row per wholesale tenant with closed New-business opportunities, ranked worst win-rate first so Daniel can see whether the struggle is concentrated in a few accounts.”
As Daniel Kim saw it, from the small dataset the agents worked on
merchant | opportunities_closed | closed_won | closed_lost | win_rate_pct | avg_days_open | avg_amount_eur ------------------------------+----------------------+------------+-------------+--------------+---------------+--------------- Polder Packaging | 16 | 12 | 4 | 75.0 | 16.4 | NULL Ballycorran Industrial Supply | 76 | 63 | 13 | 82.9 | 15.3 | NULL Midland Safety Wear | 40 | 34 | 6 | 85.0 | 10.4 | NULL Castell Fasteners | 36 | 31 | 5 | 86.1 | 12.8 | 6835.52 Hanse Industriebedarf | 17 | 15 | 2 | 88.2 | 9.9 | NULL (5 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 merchant, COUNT(*) AS opportunities_closed, SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won, SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost, ROUND(100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0), 1) AS avg_days_open, ROUND(AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= o.closed_at::date ORDER BY er.as_of DESC LIMIT 1)), 2) AS avg_amount_eur 100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0) AS win_rate_pct, AVG({{seconds_between(o.created_at, o.closed_at)}} / 86400.0) AS avg_days_open, AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= CAST(o.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST {{fetch_first(1)}})) AS avg_amount_eurFROM opportunities oJOIN tenants t ON t.id = o.tenant_idJOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_idWHERE o.is_closed = true AND p.name = 'New business' AND (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%')GROUP BY t.id, t.nameORDER BY win_rate_pct ASC, opportunities_closed DESC, t.name ASCORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST
SELECT t.name AS merchant, COUNT(*) AS opportunities_closed, SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won, SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost, 100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0) AS win_rate_pct, AVG(CAST(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open, AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= CAST(o.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST FETCH FIRST 1 ROWS ONLY)) AS avg_amount_eur 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 WHERE o.is_closed = true AND p.name = 'New business' AND (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') GROUP BY t.id, t.name ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST
SELECT t.name AS merchant, COUNT(*) AS opportunities_closed, SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won, SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost, 100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0) AS win_rate_pct, AVG(CAST(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_days_open, AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= CAST(o.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST LIMIT 1)) AS avg_amount_eur 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 WHERE o.is_closed = true AND p.name = 'New business' AND (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') GROUP BY t.id, t.name ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST
SELECT t.name AS merchant, COUNT(*) AS opportunities_closed, SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won, SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost, 100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0) AS win_rate_pct, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.created_at, o.closed_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0) AS avg_days_open, AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= CAST(o.closed_at AS date) ORDER BY er.as_of DESC NULLS FIRST FETCH FIRST 1 ROWS ONLY)) AS avg_amount_eur 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 WHERE o.is_closed = true AND p.name = 'New business' AND (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') GROUP BY t.id, t.name ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, t.name ASC NULLS LAST, t.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 Merchant (New Business) Question: Is the acquisition struggle spread evenly across our B2B wholesale merchants, or concentrated in a few accounts? Grain: one row per wholesale merchant (tenant) Columns: - merchant - opportunities_closed - closed_won - closed_lost - win_rate_pct - avg_days_open - avg_amount_eur Details: Scope: same wholesale tenant filter as prior nights (tenants.industry ILIKE '%industrial%' OR '%office equipment%' OR '%wholesal%'), opportunities where pipeline_id = the tenant's 'New business' pipeline (join pipelines on name = 'New business'), is_closed = true. avg_days_open = avg(closed_at - created_at) in days. Convert amount to EUR via exchange_rates (base_code='EUR', quote_code = opportunity currency_code, rate as_of closed_at date or nearest prior day). Order by win_rate_pct ascending (worst performers first), tie-break by opportunities_closed descending, then merchant name ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name AS merchant, COUNT(*) AS opportunities_closed, SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won, SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost, ROUND(100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0), 1) AS avg_days_open, ROUND(AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= o.closed_at::date ORDER BY er.as_of DESC LIMIT 1)), 2) AS avg_amount_eur 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 WHERE o.is_closed = true AND p.name = 'New business' AND (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') GROUP BY t.id, t.name ORDER BY win_rate_pct ASC, opportunities_closed DESC, t.name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant, opportunities_closed, closed_won, closed_lost, win_rate_pct, avg_days_open, avg_amount_eur
submitSELECT t.name AS merchant, COUNT(*) AS opportunities_closed, SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) AS closed_won, SUM(CASE WHEN NOT o.is_won THEN 1 ELSE 0 END) AS closed_lost, ROUND(100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0), 1) AS win_rate_pct, ROUND(AVG(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) / 86400.0), 1) AS avg_days_open, ROUND(AVG(o.amount / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of <= o.closed_at::date ORDER BY er.as_of DESC LIMIT 1)), 2) AS avg_amount_eur 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 WHERE o.is_closed = true AND p.name = 'New business' AND (t.industry ILIKE '%industrial%' OR t.industry ILIKE '%office equipment%' OR t.industry ILIKE '%wholesal%') GROUP BY t.id, t.name ORDER BY win_rate_pct ASC, opportunities_closed DESC, t.name ASC
Submitted to tonight's batch. Your work on this request is done.