Queries · q179 · Head of B2B Commerce · week 1, night 3

B2B Wholesale: Win Rate and Cycle Time by Sales Rep (New Business)

“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

1. A business event lands on the Head of B2B Commerce's desk

   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?

   One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

2. Patrick O'Neill writes to Daniel Kim

   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:

   1. Where do deals die? Across the wholesale merchants, at what point in the journey from raw lead to signed deal do we lose the most opportunities? I want to know which stage is the graveyard, not just an overall conversion number.
   2. How long does it take to close, and is that getting better or worse? From a lead first showing up to a deal actually closing (won or lost), what's the typical cycle time, and has it been stretching out or shrinking over recent months?
   3. What separates a won deal from a lost one? Things like deal size, how many people/touches were involved before close, which rep or team owned it, or how old the opportunity got before it closed — anything that reliably shows up more on the winning side versus the losing side.
   4. Are some merchants or reps clearly struggling more than others? I'd like a sense of whether this is a widespread pattern across our B2B merchants or concentrated in a handful of accounts/teams, since that changes whether this is a product fix or a coaching fix.
   5. (If there's room) Does deal size or payment terms correlate with win rate or speed? Wholesalers care a lot about credit terms — I'm curious if longer terms or larger contract values are dragging out or killing deals.

   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

   Show the whole note
   A note from the Head of Merchant Success, Samuel Whitcombe

   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:

   1. For our B2B wholesaler merchants specifically, are deals actually stalling at a particular stage, or is it more that fewer leads come in to begin with? I want to know where the real friction sits before our QBRs.
   2. Among the accounts that do close, is there anything about them (size, how engaged they were beforehand, how long the courtship took) that separates the wins from the losses? I'm trying to understand whether our biggest merchants' sales teams are losing winnable deals or just working less promising ones.
   3. Is there a relationship between how long a deal has been open and its odds of closing at all — i.e. do stale pipelines quietly become risk, or do they usually still land eventually?

   Happy to compare notes before you finalize anything for Patrick.

   Best, Samuel

3. Daniel Kim plans the night

   The analyst · week 1, night 3, request 2 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.

   Read the whole plan
   B2B Wholesale: Win Rate and Cycle Time by Sales Rep (New Business)

   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?

   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

4. Felix writes the SQL

   The engineer · 1 dry run on the empty schema, then the submission

   Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

   SELECT
     u.full_name AS rep_name,
     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) / COUNT(*), 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(
       CASE WHEN o.currency_code = 'EUR' THEN o.amount
       ELSE 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)
       ) END
     ), 2) AS avg_amount_eur
   FROM opportunities o
   JOIN users u ON u.id = o.owner_user_id
   JOIN tenants t ON t.id = o.tenant_id
   JOIN pipelines pl ON pl.id = o.pipeline_id AND pl.tenant_id = o.tenant_id
   WHERE o.is_closed = true
     AND pl.name = 'New business'
     AND o.tenant_id IN (
       SELECT tn.id FROM tenants tn WHERE tn.industry IN ('industrial supplies', 'office equipment')
     )
   GROUP BY o.owner_user_id, u.full_name, t.name
   HAVING COUNT(*) >= 3
   ORDER BY win_rate_pct ASC, opportunities_closed DESC, rep_name ASC

   “Query compiles cleanly with the expected 8 columns at the requested rep grain.”

   Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

   “Submitted. Summary of what I did: - Scoped to B2B wholesale tenants via tenants.industry IN ('industrial supplies','office equipment'), pipeline = 'New business', and opportunities.is_closed = true. - Grouped by owner_user_id (joined to users for rep_name, tenants for merchant). - win_rate_pct = closed_won / opportunities_closed, avg_days_open from closed_at - created_at in days, avg_amount_eur converting each opportunity's amount via exchange_rates (EUR base) as of its closed_at date. - Applied HAVING COUNT(*) >= 3 to drop low-volume reps, ordered worst win rate first as requested. - Flagged in the note that I inferred the wholesale filter from the company's described verticals since the prior report's exact filter wasn't in front of me, and that the excluded-rep count isn't something I can verify without live data access.”

5. The report comes back overnight

   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
   ---------+----------+----------------------+------------+-------------+--------------+---------------+---------------
   (0 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q181B2B Wholesale: Win Rate and Cycle Time by Sales Rep (New Business) — Corrected

   It builds on:

   • q176New Business Only: Won vs Lost Opportunity Characteristics

7. It becomes benchmark query q179

   Changed only where the benchmark needs the same result on every system

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
   • made explicita selected t.col gets AS col, its standard name made explicit
   • placeholderseconds between
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
   The benchmark text, against the submitted SQL

   SELECT  u.full_name AS rep_name,  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) / COUNT(*), 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(    CASE WHEN o.currency_code = 'EUR' THEN o.amount    ELSE 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)    ) END  ), 2) AS avg_amount_eur  100.0 * SUM(CASE WHEN o.is_won THEN 1 ELSE 0 END) / COUNT(*) AS win_rate_pct,  AVG({{seconds_between(o.created_at, o.closed_at)}} / 86400.0) AS avg_days_open,  AVG(CASE WHEN o.currency_code = 'EUR' THEN o.amountELSE o.amount / (SELECT er.rateFROM exchange_rates erWHERE er.base_code = 'EUR'  AND er.quote_code = o.currency_code  AND er.as_of = CAST(o.closed_at AS date)) END) AS avg_amount_eurFROM opportunities oJOIN users u ON u.id = o.owner_user_idJOIN tenants t ON t.id = o.tenant_idJOIN pipelines pl ON pl.id = o.pipeline_id AND pl.tenant_id = o.tenant_idWHERE o.is_closed = true  AND pl.name = 'New business'  AND o.tenant_id IN (    SELECT tn.id FROM tenants tn WHERE tn.industry IN ('industrial supplies', 'office equipment')    SELECT tn.id AS id FROM tenants tn WHERE tn.industry IN ('industrial supplies', 'office equipment')  )GROUP BY o.owner_user_id, u.full_name, t.nameHAVING 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

   As each system received it; highlighted, the parts spelled for that system

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · clickhouse_brewClickHouse · chDB

   SELECT
     u.full_name AS rep_name,
     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) / COUNT(*) 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(
   CASE WHEN o.currency_code = 'EUR' THEN o.amount
   ELSE 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)
   ) END
   ) AS avg_amount_eur
   FROM opportunities o
   JOIN users u ON u.id = o.owner_user_id
   JOIN tenants t ON t.id = o.tenant_id
   JOIN pipelines pl ON pl.id = o.pipeline_id AND pl.tenant_id = o.tenant_id
   WHERE o.is_closed = true
     AND pl.name = 'New business'
     AND o.tenant_id IN (
       SELECT tn.id AS id FROM tenants tn WHERE tn.industry IN ('industrial supplies', 'office equipment')
     )
   GROUP BY o.owner_user_id, u.full_name, t.name
   HAVING COUNT(*) >= 3
   ORDER BY win_rate_pct ASC NULLS LAST, opportunities_closed DESC NULLS FIRST, rep_name ASC NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 11, Column 15: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "160752b1-5dd9-4e4b-bb1d-21e7918d7923", "query_lab
   • DataFusionError during planning: Correlated scalar subquery must be aggregated to return at most one row
   • ClickHouseCode: 48. DB::Exception: Correlated subqueries in aggregate function argument are not supported. (NOT_IMPLEMENTED)

   Scale factor 1000, c8a.24xlarge, after the last load. q179 on the start page, with every configuration.

8. Everything, as recorded

   The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

   Felix's whole session, 7 turns

   • The request, as Felix received it

     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)
     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: Scope: same wholesale tenant filter, pipeline_id = the tenant's 'New business' pipeline, is_closed = true, grouped by owner_user_id (join users for full_name as rep_name, and tenants for merchant name). Exclude reps with fewer than 3 closed opportunities in scope to avoid single-deal noise (note the excluded rep count in the report notes). avg_days_open and avg_amount_eur defined as in the merchant-level report (EUR via exchange_rates as of 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.

   • Felix calls dry_run

     SELECT
       u.full_name AS rep_name,
       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) / COUNT(*), 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(
         CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE 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)
         ) END
       ), 2) AS avg_amount_eur
     FROM opportunities o
     JOIN users u ON u.id = o.owner_user_id
     JOIN tenants t ON t.id = o.tenant_id
     JOIN pipelines pl ON pl.id = o.pipeline_id AND pl.tenant_id = o.tenant_id
     WHERE o.is_closed = true
       AND pl.name = 'New business'
       AND o.tenant_id IN (
         SELECT tn.id FROM tenants tn WHERE tn.industry IN ('industrial supplies', 'office equipment')
       )
     GROUP BY o.owner_user_id, u.full_name, t.name
     HAVING COUNT(*) >= 3
     ORDER BY win_rate_pct ASC, opportunities_closed DESC, rep_name ASC

   • The warehouse answers

     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

   • FelixQuery compiles cleanly with the expected 8 columns at the requested rep grain.
   • Felix calls submit

     SELECT
       u.full_name AS rep_name,
       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) / COUNT(*), 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(
         CASE WHEN o.currency_code = 'EUR' THEN o.amount
         ELSE 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)
         ) END
       ), 2) AS avg_amount_eur
     FROM opportunities o
     JOIN users u ON u.id = o.owner_user_id
     JOIN tenants t ON t.id = o.tenant_id
     JOIN pipelines pl ON pl.id = o.pipeline_id AND pl.tenant_id = o.tenant_id
     WHERE o.is_closed = true
       AND pl.name = 'New business'
       AND o.tenant_id IN (
         SELECT tn.id FROM tenants tn WHERE tn.industry IN ('industrial supplies', 'office equipment')
       )
     GROUP BY o.owner_user_id, u.full_name, t.name
     HAVING COUNT(*) >= 3
     ORDER BY win_rate_pct ASC, opportunities_closed DESC, rep_name ASC

   • The warehouse answers

     Submitted to tonight's batch. Your work on this request is done.

   • FelixSubmitted. Summary of what I did: - Scoped to B2B wholesale tenants via `tenants.industry IN ('industrial supplies','office equipment')`, pipeline = `'New business'`, and `opportunities.is_closed = true`. - Grouped by `owner_user_id` (joined to `users` for `rep_name`, `tenants` for `merchant`). - `win_rate_pct` = closed_won / opportunities_closed, `avg_days_open` from `closed_at - created_at` in days, `avg_amount_eur` converting each opportunity's amount via `exchange_rates` (EUR base) as of its `closed_at` date. - Applied `HAVING COUNT(*) >= 3` to drop low-volume reps, ordered worst win rate first as requested. - Flagged in the note that I inferred the wholesale filter from the company's described verticals since the prior report's exact filter wasn't in front of me, and that the excluded-rep count isn't something I can verify without live data access.

   Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

   Felix's transcript (273 KB) · Daniel Kim's transcript (789 KB) · Patrick O'Neill's transcript (263 KB) · the night's plan (5 KB) · the night's reports (3 KB) · the week's memo (3 KB)