Queries · q172 · Head of B2B Commerce · week 1, night 1
“Across our B2B wholesale merchants, at which pipeline stage do the most opportunities get stuck or lost — i.e. which stage is the graveyard?”
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 1 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 · 1 dry run on the empty schema, then the submission
WITH wholesale_tenants AS ( SELECT id AS tenant_id, name AS merchant FROM tenants WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%' ), stages AS ( SELECT os.tenant_id, os.id AS stage_id, os.name AS stage_name, os.sort_position, os.win_probability, p.name AS pipeline_name FROM opportunity_stages os JOIN pipelines p ON p.id = os.pipeline_id AND p.tenant_id = os.tenant_id JOIN wholesale_tenants wt ON wt.tenant_id = os.tenant_id ), opp_agg AS ( SELECT o.tenant_id, o.stage_id, COUNT(*) FILTER (WHERE o.is_closed = false) AS open_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = true) AS won_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = false) AS lost_count, AVG(o.amount) AS avg_amount, MIN(o.currency_code) AS currency_code FROM opportunities o JOIN wholesale_tenants wt ON wt.tenant_id = o.tenant_id GROUP BY o.tenant_id, o.stage_id ) SELECT wt.merchant, s.pipeline_name, s.stage_name, s.sort_position AS stage_order, ROUND(s.win_probability, 4) AS default_win_probability, COALESCE(oa.open_count, 0) AS opportunities_open, COALESCE(oa.won_count, 0) AS opportunities_closed_won, COALESCE(oa.lost_count, 0) AS opportunities_closed_lost, ROUND(oa.avg_amount, 2) AS avg_opportunity_amount, oa.currency_code FROM stages s JOIN wholesale_tenants wt ON wt.tenant_id = s.tenant_id LEFT JOIN opp_agg oa ON oa.tenant_id = s.tenant_id AND oa.stage_id = s.stage_id ORDER BY wt.merchant, s.sort_position, s.stage_name
“Query compiles cleanly. Submitting now.”
“Submitted. Summary of what the query does:
- Scope: tenants whose industry matches industrial/office equipment/wholesale (ILIKE), per the brief.
- Grain: one row per (merchant, pipeline stage), ordered by merchant name then stage order.
- Measures: open count (is_closed=false), closed-won count, closed-lost count (both keyed to stage_id as the final stage), average opportunity amount and its currency code, plus the stage's default win probability and order — all as requested, with no time window and no FX conversion.
- Used a LEFT JOIN from stages to the opportunity aggregates so stages with zero opportunities still appear (with zeros), which supports the "graveyard stage" and "zero opportunities" checks the analyst wants.
Flagged in the submit note: average/currency are per (tenant, stage) without conversion — safe under the stated assumption that a tenant's opportunities are single-currency, but I couldn't verify that from a dry run since it reads no data. I also asked the analyst to check for any wholesale tenant returning zero stages/opportunities, since dry runs can't surface that either.”
As Daniel Kim saw it, from the small dataset the agents worked on
merchant | pipeline_name | stage_name | stage_order | default_win_probability | opportunities_open | opportunities_closed_won | opportunities_closed_lost | avg_opportunity_amount | currency_code ------------------------------+---------------+---------------+-------------+-------------------------+--------------------+--------------------------+---------------------------+------------------------+-------------- Ballycorran Industrial Supply | New business | Prospecting | 0 | 0.1000 | 0 | 0 | 0 | NULL | NULL Ballycorran Industrial Supply | Renewals | Renewal due | 0 | 0.6000 | 0 | 0 | 0 | NULL | NULL Ballycorran Industrial Supply | Renewals | Proposal sent | 1 | 0.7500 | 0 | 0 | 0 | NULL | NULL Ballycorran Industrial Supply | New business | Qualification | 1 | 0.2500 | 0 | 0 | 0 | NULL | NULL Ballycorran Industrial Supply | New business | Proposal | 2 | 0.5000 | 0 | 0 | 0 | NULL | NULL Ballycorran Industrial Supply | Renewals | Renewed | 2 | 1.0000 | 0 | 74 | 0 | 4374.32 | EUR Ballycorran Industrial Supply | Renewals | Churned | 3 | 0.0000 | 0 | 0 | 17 | 4494.12 | EUR Ballycorran Industrial Supply | New business | Negotiation | 3 | 0.7500 | 0 | 0 | 0 | NULL | NULL Ballycorran Industrial Supply | New business | Closed Won | 4 | 1.0000 | 0 | 63 | 0 | 4088.89 | EUR Ballycorran Industrial Supply | New business | Closed Lost | 5 | 0.0000 | 0 | 0 | 13 | 3938.46 | EUR Castell Fasteners | New business | Prospecting | 0 | 0.1000 | 0 | 0 | 0 | NULL | NULL Castell Fasteners | Renewals | Renewal due | 0 | 0.6000 | 0 | 0 | 0 | NULL | NULL Castell Fasteners | Renewals | Proposal sent | 1 | 0.7500 | 0 | 0 | 0 | NULL | NULL Castell Fasteners | New business | Qualification | 1 | 0.2500 | 0 | 0 | 0 | NULL | NULL Castell Fasteners | New business | Proposal | 2 | 0.5000 | 0 | 0 | 0 | NULL | NULL Castell Fasteners | Renewals | Renewed | 2 | 1.0000 | 0 | 38 | 0 | 11002.63 | USD Castell Fasteners | Renewals | Churned | 3 | 0.0000 | 0 | 0 | 4 | 6175.0 | USD Castell Fasteners | New business | Negotiation | 3 | 0.7500 | 0 | 0 | 0 | NULL | NULL Castell Fasteners | New business | Closed Won | 4 | 1.0000 | 0 | 31 | 0 | 7877.42 | USD Castell Fasteners | New business | Closed Lost | 5 | 0.0000 | 0 | 0 | 5 | 6300.0 | USD Hanse Industriebedarf | New business | Prospecting | 0 | 0.1000 | 0 | 0 | 0 | NULL | NULL Hanse Industriebedarf | Renewals | Renewal due | 0 | 0.6000 | 0 | 0 | 0 | NULL | NULL Hanse Industriebedarf | Renewals | Proposal sent | 1 | 0.7500 | 0 | 0 | 0 | NULL | NULL Hanse Industriebedarf | New business | Qualification | 1 | 0.2500 | 0 | 0 | 0 | NULL | NULL Hanse Industriebedarf | New business | Proposal | 2 | 0.5000 | 0 | 0 | 0 | NULL | NULL Hanse Industriebedarf | Renewals | Renewed | 2 | 1.0000 | 0 | 19 | 0 | 7410.53 | EUR Hanse Industriebedarf | Renewals | Churned | 3 | 0.0000 | 0 | 0 | 1 | 5600.0 | EUR Hanse Industriebedarf | New business | Negotiation | 3 | 0.7500 | 0 | 0 | 0 | NULL | NULL Hanse Industriebedarf | New business | Closed Won | 4 | 1.0000 | 0 | 15 | 0 | 5553.33 | EUR Hanse Industriebedarf | New business | Closed Lost | 5 | 0.0000 | 0 | 0 | 2 | 5200.0 | EUR Midland Safety Wear | New business | Prospecting | 0 | 0.1000 | 0 | 0 | 0 | NULL | NULL Midland Safety Wear | Renewals | Renewal due | 0 | 0.6000 | 1 | 0 | 0 | 2600.0 | EUR Midland Safety Wear | Renewals | Proposal sent | 1 | 0.7500 | 3 | 0 | 0 | 10366.67 | EUR Midland Safety Wear | New business | Qualification | 1 | 0.2500 | 0 | 0 | 0 | NULL | NULL Midland Safety Wear | New business | Proposal | 2 | 0.5000 | 0 | 0 | 0 | NULL | NULL Midland Safety Wear | Renewals | Renewed | 2 | 1.0000 | 0 | 44 | 0 | 5984.09 | EUR Midland Safety Wear | Renewals | Churned | 3 | 0.0000 | 0 | 0 | 11 | 4990.91 | EUR Midland Safety Wear | New business | Negotiation | 3 | 0.7500 | 0 | 0 | 0 | NULL | NULL Midland Safety Wear | New business | Closed Won | 4 | 1.0000 | 0 | 34 | 0 | 5526.47 | EUR Midland Safety Wear | New business | Closed Lost | 5 | 0.0000 | 0 | 0 | 6 | 4300.0 | EUR Polder Packaging | New business | Prospecting | 0 | 0.1000 | 0 | 0 | 0 | NULL | NULL Polder Packaging | Renewals | Renewal due | 0 | 0.6000 | 0 | 0 | 0 | NULL | NULL Polder Packaging | Renewals | Proposal sent | 1 | 0.7500 | 0 | 0 | 0 | NULL | NULL Polder Packaging | New business | Qualification | 1 | 0.2500 | 0 | 0 | 0 | NULL | NULL Polder Packaging | New business | Proposal | 2 | 0.5000 | 0 | 0 | 0 | NULL | NULL Polder Packaging | Renewals | Renewed | 2 | 1.0000 | 0 | 9 | 0 | 5388.89 | EUR Polder Packaging | Renewals | Churned | 3 | 0.0000 | 0 | 0 | 4 | 3275.0 | EUR Polder Packaging | New business | Negotiation | 3 | 0.7500 | 0 | 0 | 0 | NULL | NULL Polder Packaging | New business | Closed Won | 4 | 1.0000 | 0 | 12 | 0 | 4908.33 | EUR Polder Packaging | New business | Closed Lost | 5 | 0.0000 | 0 | 0 | 4 | 10875.0 | EUR (50 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
WITH wholesale_tenants AS ( SELECT id AS tenant_id, name AS merchant FROM tenants WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%'),stages AS ( SELECT os.tenant_id, os.tenant_id AS tenant_id, os.id AS stage_id, os.name AS stage_name, os.sort_position, os.win_probability, os.sort_position AS sort_position, os.win_probability AS win_probability, p.name AS pipeline_name FROM opportunity_stages os JOIN pipelines p ON p.id = os.pipeline_id AND p.tenant_id = os.tenant_id JOIN wholesale_tenants wt ON wt.tenant_id = os.tenant_id),opp_agg AS ( SELECT o.tenant_id, o.stage_id, o.tenant_id AS tenant_id, o.stage_id AS stage_id, COUNT(*) FILTER (WHERE o.is_closed = false) AS open_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = true) AS won_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = false) AS lost_count, AVG(o.amount) AS avg_amount, MIN(o.currency_code) AS currency_code FROM opportunities o JOIN wholesale_tenants wt ON wt.tenant_id = o.tenant_id GROUP BY o.tenant_id, o.stage_id)SELECT wt.merchant, s.pipeline_name, s.stage_name, wt.merchant AS merchant, s.pipeline_name AS pipeline_name, s.stage_name AS stage_name, s.sort_position AS stage_order, ROUND(s.win_probability, 4) AS default_win_probability, s.win_probability AS default_win_probability, COALESCE(oa.open_count, 0) AS opportunities_open, COALESCE(oa.won_count, 0) AS opportunities_closed_won, COALESCE(oa.lost_count, 0) AS opportunities_closed_lost, ROUND(oa.avg_amount, 2) AS avg_opportunity_amount, oa.currency_code oa.avg_amount AS avg_opportunity_amount, oa.currency_code AS currency_codeFROM stages sJOIN wholesale_tenants wt ON wt.tenant_id = s.tenant_idLEFT JOIN opp_agg oa ON oa.tenant_id = s.tenant_id AND oa.stage_id = s.stage_idORDER BY wt.merchant, s.sort_position, s.stage_nameORDER BY wt.merchant NULLS LAST, s.sort_position NULLS LAST, s.stage_name NULLS LAST, s.stage_id NULLS LAST
WITH wholesale_tenants AS ( SELECT id AS tenant_id, name AS merchant FROM tenants WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%' ), stages AS ( SELECT os.tenant_id AS tenant_id, os.id AS stage_id, os.name AS stage_name, os.sort_position AS sort_position, os.win_probability AS win_probability, p.name AS pipeline_name FROM opportunity_stages os JOIN pipelines p ON p.id = os.pipeline_id AND p.tenant_id = os.tenant_id JOIN wholesale_tenants wt ON wt.tenant_id = os.tenant_id ), opp_agg AS ( SELECT o.tenant_id AS tenant_id, o.stage_id AS stage_id, COUNT(*) FILTER (WHERE o.is_closed = false) AS open_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = true) AS won_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = false) AS lost_count, AVG(o.amount) AS avg_amount, MIN(o.currency_code) AS currency_code FROM opportunities o JOIN wholesale_tenants wt ON wt.tenant_id = o.tenant_id GROUP BY o.tenant_id, o.stage_id ) SELECT wt.merchant AS merchant, s.pipeline_name AS pipeline_name, s.stage_name AS stage_name, s.sort_position AS stage_order, s.win_probability AS default_win_probability, COALESCE(oa.open_count, 0) AS opportunities_open, COALESCE(oa.won_count, 0) AS opportunities_closed_won, COALESCE(oa.lost_count, 0) AS opportunities_closed_lost, oa.avg_amount AS avg_opportunity_amount, oa.currency_code AS currency_code FROM stages s JOIN wholesale_tenants wt ON wt.tenant_id = s.tenant_id LEFT JOIN opp_agg oa ON oa.tenant_id = s.tenant_id AND oa.stage_id = s.stage_id ORDER BY wt.merchant NULLS LAST, s.sort_position NULLS LAST, s.stage_name NULLS LAST, s.stage_id 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 Pipeline: Opportunities by Merchant and Stage Question: Across our B2B wholesale merchants, at which pipeline stage do the most opportunities get stuck or lost — i.e. which stage is the graveyard? Grain: one row per wholesale merchant (tenant) and pipeline stage Columns: - merchant (tenants.name) - pipeline name - stage name - stage order (sort_position) - default win probability (win_probability) - opportunities currently open at this stage (is_closed = false) - opportunities closed won with this as final stage_id - opportunities closed lost with this as final stage_id - average opportunity amount at this stage (in the opportunity's own currency) - currency_code Details: Scope to B2B wholesale merchants only: tenants where industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%' (per the company briefing's 'industrial supplies, office equipment' wholesalers). Use opportunities joined to opportunity_stages (stage_id) and pipelines, and tenants for the merchant name. For each (tenant, stage) pair count: opportunities where is_closed = false (currently sitting there), opportunities where is_closed = true and is_won = true and stage_id = this stage, and opportunities where is_closed = true and is_won = false (lost) and stage_id = this stage. Include avg(amount) per currency_code — do not convert currencies here since the grain is per-tenant so no cross-currency summing occurs. This is an all-time snapshot (all opportunities ever created through 2025-12-31), not time-windowed. Order by merchant name, then stage order (sort_position) ascending. Flag in the report notes if any wholesale tenant has zero opportunities or zero stages, since that would indicate the industry filter missed a merchant or the merchant doesn't use the CRM module. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH wholesale_tenants AS ( SELECT id AS tenant_id, name AS merchant FROM tenants WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%' ), stages AS ( SELECT os.tenant_id, os.id AS stage_id, os.name AS stage_name, os.sort_position, os.win_probability, p.name AS pipeline_name FROM opportunity_stages os JOIN pipelines p ON p.id = os.pipeline_id AND p.tenant_id = os.tenant_id JOIN wholesale_tenants wt ON wt.tenant_id = os.tenant_id ), opp_agg AS ( SELECT o.tenant_id, o.stage_id, COUNT(*) FILTER (WHERE o.is_closed = false) AS open_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = true) AS won_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = false) AS lost_count, AVG(o.amount) AS avg_amount, MIN(o.currency_code) AS currency_code FROM opportunities o JOIN wholesale_tenants wt ON wt.tenant_id = o.tenant_id GROUP BY o.tenant_id, o.stage_id ) SELECT wt.merchant, s.pipeline_name, s.stage_name, s.sort_position AS stage_order, ROUND(s.win_probability, 4) AS default_win_probability, COALESCE(oa.open_count, 0) AS opportunities_open, COALESCE(oa.won_count, 0) AS opportunities_closed_won, COALESCE(oa.lost_count, 0) AS opportunities_closed_lost, ROUND(oa.avg_amount, 2) AS avg_opportunity_amount, oa.currency_code FROM stages s JOIN wholesale_tenants wt ON wt.tenant_id = s.tenant_id LEFT JOIN opp_agg oa ON oa.tenant_id = s.tenant_id AND oa.stage_id = s.stage_id ORDER BY wt.merchant, s.sort_position, s.stage_name
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant, pipeline_name, stage_name, stage_order, default_win_probability, opportunities_open, opportunities_closed_won, opportunities_closed_lost, avg_opportunity_amount, currency_code
submitWITH wholesale_tenants AS ( SELECT id AS tenant_id, name AS merchant FROM tenants WHERE industry ILIKE '%industrial%' OR industry ILIKE '%office equipment%' OR industry ILIKE '%wholesal%' ), stages AS ( SELECT os.tenant_id, os.id AS stage_id, os.name AS stage_name, os.sort_position, os.win_probability, p.name AS pipeline_name FROM opportunity_stages os JOIN pipelines p ON p.id = os.pipeline_id AND p.tenant_id = os.tenant_id JOIN wholesale_tenants wt ON wt.tenant_id = os.tenant_id ), opp_agg AS ( SELECT o.tenant_id, o.stage_id, COUNT(*) FILTER (WHERE o.is_closed = false) AS open_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = true) AS won_count, COUNT(*) FILTER (WHERE o.is_closed = true AND o.is_won = false) AS lost_count, AVG(o.amount) AS avg_amount, MIN(o.currency_code) AS currency_code FROM opportunities o JOIN wholesale_tenants wt ON wt.tenant_id = o.tenant_id GROUP BY o.tenant_id, o.stage_id ) SELECT wt.merchant, s.pipeline_name, s.stage_name, s.sort_position AS stage_order, ROUND(s.win_probability, 4) AS default_win_probability, COALESCE(oa.open_count, 0) AS opportunities_open, COALESCE(oa.won_count, 0) AS opportunities_closed_won, COALESCE(oa.lost_count, 0) AS opportunities_closed_lost, ROUND(oa.avg_amount, 2) AS avg_opportunity_amount, oa.currency_code FROM stages s JOIN wholesale_tenants wt ON wt.tenant_id = s.tenant_id LEFT JOIN opp_agg oa ON oa.tenant_id = s.tenant_id AND oa.stage_id = s.stage_id ORDER BY wt.merchant, s.sort_position, s.stage_name
Submitted to tonight's batch. Your work on this request is done.