Queries · q387 · Head of B2B Commerce · week 2, night 2

B2B Wholesale: Seasonality of Lost-Deal Cycle Time by Quarter

“Do lost deals linger longest at particular times of year (e.g., end-of-quarter or holiday periods), or is the ~35-day drag before a deal is given up on roughly constant regardless of when it started?”

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 2

   Note to Daniel Kim — reactions and next focus

   Daniel, this is a strong memo — a few things surprised me and changed what I want to chase next.

   What surprised me / needs explaining:

   • The "zero open pipeline" finding is bigger than a side note to me — if every opportunity we've ever seen across 3 years is already closed, we have no forward-looking view at all right now for any of these merchants. Before I take that to Samuel, I need to understand whether that's a real business fact (deals genuinely don't sit open long) or whether open deals simply aren't being entered into the system until they're decided.
   • The "lost deals get more attention, not less" finding cuts against my instinct. I want to understand the shape of that attention, not just the average — are reps front-loading effort and then the deal goes quiet for weeks before it's marked lost, or is the activity spread evenly, including right up to the end? That tells us whether the fix is "kill deals faster" or "the last mile of a sale is where we're weak."
   • Youssef Meijer is the one concrete, actionable lead we got this week — I want to go one level deeper before I talk to Polder Packaging.

   Questions for this week:

   1. For the deals that eventually lose, can you show roughly when the activity happens relative to the deal's life — clustered early, steady throughout, or a late flurry right before it's given up on? I want to know if these are "stalled and forgotten until someone finally closes it out" versus "actively fought to the end and still lost."
   2. Is the complete absence of open pipeline a constant feature of the last three years, or has it changed recently — i.e., are merchants currently sitting on live deals that just haven't shown up yet, or does this system genuinely never carry an open deal for more than a few weeks?
   3. Can you pull Youssef Meijer's individual deals — which accounts, what sizes, how old — so I can tell whether this looks like a few bad accounts/territory versus a pattern across all his work?
   4. Of the lost deals, is there any sign of when in the quarter or year they tend to die — e.g., do deals linger longest around certain times (end of quarter pushes, holiday periods), or is the ~35-day drag constant regardless of when the deal started?

   I know the instrumentation gaps (no stage history, no merchant-side payment terms) aren't fixable by you this week — flag both to product as you planned, that's the right move. Let's prioritize Q1 and Q3 above everything else; Q2 and Q4 can slip if slots run short.

   Thanks, Daniel — good detective work this week.

   Patrick

   Show the whole note

3. Daniel Kim plans the night

   The analyst · week 2, night 2, request 3 of 3

   Night 1 gave three clean answers. (1) Activity timing on lost deals: only 40% of lost opportunities have any logged activity in their first quarter of life, rising to 100% in the second quarter and staying high (96.7%, 76.7%) through the third and fourth — this reads as 'actively worked to the end' for the large majority, not 'stalled and forgotten', though ~23% do go quiet before closing. (2) Youssef Meijer's 7 deals span 7 distinct accounts (no territory/account concentration), but his 3 losses include a €26,000 deal — by dollar value he won only ~26% of his pipeline despite a 57% count-based win rate, suggesting he's fine on small/quick deals but struggles to close large ones. (3) The 'zero open pipeline' finding is NOT a 3-year constant — nearly every month-end from 2023 through Nov 2025 had a handful of genuinely open deals (some 40-70+ days old); only Dec 2025 (and two earlier one-off months) show zero. That raises a new question before I can tell Patrick whether this is a real business fact or a data gap. Tonight's three slots: (1) check whether opportunity creation volume dropped in Oct-Dec 2025 (would signal a data-freshness/ingestion gap) or stayed normal (meaning it's a real, if unusual, pipeline flush); (2) extend the Youssef finding to all reps via a dollar-weighted vs count-weighted win-rate comparison, to see if 'wins small deals, loses the big one' is a broader pattern worth flagging beyond one rep; (3) Patrick's lower-priority Q4 on seasonality of lost-deal duration, since we have a free slot and it's a quick, well-defined query.

   Read the whole plan
   B2B Wholesale: Seasonality of Lost-Deal Cycle Time by Quarter

   Question

   Do lost deals linger longest at particular times of year (e.g., end-of-quarter or holiday periods), or is the ~35-day drag before a deal is given up on roughly constant regardless of when it started?

   One row per

   calendar quarter, 2023Q1 through 2025Q4

   Columns

   • quarter
   • lost_opportunities_count
   • avg_days_open
   • median_days_open

4. Grace 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)

   with tenant_scope as (
       select id as tenant_id, name
       from tenants
       where name in ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
   ),
   pipeline_scope as (
       select p.id as pipeline_id, p.tenant_id
       from pipelines p
       join tenant_scope t on p.tenant_id = t.tenant_id
       where p.name = 'New business'
   ),
   lost_opportunities as (
       select
           o.id as opportunity_id,
           o.created_at,
           o.closed_at,
           (extract(epoch from (o.closed_at - o.created_at)) / 86400.0) as days_open
       from opportunities o
       join pipeline_scope ps
           on o.pipeline_id = ps.pipeline_id
           and o.tenant_id = ps.tenant_id
       where o.is_closed = true
         and o.is_won = false
         and o.closed_at is not null
   ),
   dated as (
       select
           lo.opportunity_id,
           lo.days_open,
           cd.year_num,
           cd.quarter_num
       from lost_opportunities lo
       join calendar_days cd on cd.calendar_date = lo.closed_at::date
   )
   select
       (year_num || 'Q' || quarter_num) as quarter,
       count(*) as lost_opportunities_count,
       round(avg(days_open)::numeric, 1) as avg_days_open,
       round(percentile_cont(0.5) within group (order by days_open)::numeric, 1) as median_days_open
   from dated
   group by year_num, quarter_num
   order by year_num, quarter_num

   “Query compiles cleanly. Submitting.”

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

   “Submitted. The report groups the five named B2B tenants' lost opportunities (New business pipeline, is_closed=true, is_won=false) by the calendar quarter of closed_at, with count, average, and median days-open (closed_at − created_at) rounded to 1 decimal.”

5. The report comes back overnight

   As Daniel Kim saw it, from the small dataset the agents worked on

   quarter | lost_opportunities_count | avg_days_open | median_days_open
   --------+--------------------------+---------------+-----------------
   2023Q1  |                        2 |          20.5 |             20.5
   2023Q2  |                        2 |          31.8 |             31.8
   2023Q3  |                        1 |          18.0 |             18.0
   2023Q4  |                        3 |          39.9 |             37.0
   2024Q2  |                        2 |          31.7 |             31.7
   2024Q3  |                        3 |          49.2 |             37.0
   2024Q4  |                        8 |          31.3 |             34.5
   2025Q1  |                        3 |          37.8 |             35.2
   2025Q2  |                        1 |          38.0 |             38.0
   2025Q3  |                        1 |          22.0 |             22.0
   2025Q4  |                        4 |          46.7 |             49.4
   (11 rows)

6. What came of it

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

   It builds on:

   • q176New Business Only: Won vs Lost Opportunity Characteristics
   • q173B2B Wholesale Deal Cycle Time Trend by Month

7. It becomes benchmark query q387

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

   • spellingcast (3×)
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
   • 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 (7×)
   • placeholderpercentile cont
   • 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 (2×)
   The benchmark text, against the submitted SQL

   with tenant_scope as (    select id as tenant_id, name    from tenants    where name in ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')),pipeline_scope as (    select p.id as pipeline_id, p.tenant_id    select p.id as pipeline_id, p.tenant_id AS tenant_id    from pipelines p    join tenant_scope t on p.tenant_id = t.tenant_id    where p.name = 'New business'),lost_opportunities as (    select        o.id as opportunity_id,        o.created_at,        o.closed_at,        (extract(epoch from (o.closed_at - o.created_at)) / 86400.0) as days_open        o.created_at AS created_at,        o.closed_at AS closed_at,        ({{seconds_between(o.created_at, o.closed_at)}} / 86400.0) as days_open    from opportunities o    join pipeline_scope ps        on o.pipeline_id = ps.pipeline_id        and o.tenant_id = ps.tenant_id    where o.is_closed = true      and o.is_won = false      and o.closed_at is not null),dated as (    select        lo.opportunity_id,        lo.days_open,        cd.year_num,        cd.quarter_num        lo.opportunity_id AS opportunity_id,        lo.days_open AS days_open,        cd.year_num AS year_num,        cd.quarter_num AS quarter_num    from lost_opportunities lo    join calendar_days cd on cd.calendar_date = lo.closed_at::date    join calendar_days cd on cd.calendar_date = CAST(lo.closed_at AS date))select    (year_num || 'Q' || quarter_num) as quarter,    count(*) as lost_opportunities_count,    round(avg(days_open)::numeric, 1) as avg_days_open,    round(percentile_cont(0.5) within group (order by days_open)::numeric, 1) as median_days_open    avg(days_open) as avg_days_open,    {{percentile_cont(0.5, days_open)}} as median_days_openfrom datedgroup by year_num, quarter_numorder by year_num, quarter_numorder by year_num NULLS LAST, quarter_num NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · clickhouse_brewFireboltClickHouse · chDB

   with tenant_scope as (
       select id as tenant_id, name
       from tenants
       where name in ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
   ),
   pipeline_scope as (
       select p.id as pipeline_id, p.tenant_id AS tenant_id
       from pipelines p
       join tenant_scope t on p.tenant_id = t.tenant_id
       where p.name = 'New business'
   ),
   lost_opportunities as (
       select
           o.id as opportunity_id,
           o.created_at AS created_at,
           o.closed_at AS closed_at,
           (CAST(EXTRACT(EPOCH FROM (o.closed_at - o.created_at)) AS NUMERIC(18,6)) / 86400.0) as days_open
       from opportunities o
       join pipeline_scope ps
           on o.pipeline_id = ps.pipeline_id
           and o.tenant_id = ps.tenant_id
       where o.is_closed = true
         and o.is_won = false
         and o.closed_at is not null
   ),
   dated as (
       select
           lo.opportunity_id AS opportunity_id,
           lo.days_open AS days_open,
           cd.year_num AS year_num,
           cd.quarter_num AS quarter_num
       from lost_opportunities lo
       join calendar_days cd on cd.calendar_date = CAST(lo.closed_at AS date)
   )
   select
       (year_num || 'Q' || quarter_num) as quarter,
       count(*) as lost_opportunities_count,
       avg(days_open) as avg_days_open,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_open) as median_days_open
   from dated
   group by year_num, quarter_num
   order by year_num NULLS LAST, quarter_num NULLS LAST

   Scale factor 1000, c8a.24xlarge, after the last load. q387 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

   Grace's whole session, 7 turns

   • The request, as Grace received it

     Report request from Daniel Kim (analyst for the Head of B2B Commerce):
     
     Title: B2B Wholesale: Seasonality of Lost-Deal Cycle Time by Quarter
     Question: Do lost deals linger longest at particular times of year (e.g., end-of-quarter or holiday periods), or is the ~35-day drag before a deal is given up on roughly constant regardless of when it started?
     Grain: one row per calendar quarter, 2023Q1 through 2025Q4
     Columns:
     - quarter
     - lost_opportunities_count
     - avg_days_open
     - median_days_open
     Details: Scope: tenants.name IN ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging'), pipeline_id = each tenant's 'New business' pipeline, is_closed = true and is_won = false (lost only). Group by the calendar quarter of closed_at (year_num and quarter_num from calendar_days, or equivalent). days_open = closed_at - created_at in days; report avg and median rounded to 1 decimal. Order by quarter ascending (year then quarter number).
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Grace calls dry_run

     with tenant_scope as (
         select id as tenant_id, name
         from tenants
         where name in ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
     ),
     pipeline_scope as (
         select p.id as pipeline_id, p.tenant_id
         from pipelines p
         join tenant_scope t on p.tenant_id = t.tenant_id
         where p.name = 'New business'
     ),
     lost_opportunities as (
         select
             o.id as opportunity_id,
             o.created_at,
             o.closed_at,
             (extract(epoch from (o.closed_at - o.created_at)) / 86400.0) as days_open
         from opportunities o
         join pipeline_scope ps
             on o.pipeline_id = ps.pipeline_id
             and o.tenant_id = ps.tenant_id
         where o.is_closed = true
           and o.is_won = false
           and o.closed_at is not null
     ),
     dated as (
         select
             lo.opportunity_id,
             lo.days_open,
             cd.year_num,
             cd.quarter_num
         from lost_opportunities lo
         join calendar_days cd on cd.calendar_date = lo.closed_at::date
     )
     select
         (year_num || 'Q' || quarter_num) as quarter,
         count(*) as lost_opportunities_count,
         round(avg(days_open)::numeric, 1) as avg_days_open,
         round(percentile_cont(0.5) within group (order by days_open)::numeric, 1) as median_days_open
     from dated
     group by year_num, quarter_num
     order by year_num, quarter_num

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: quarter, lost_opportunities_count, avg_days_open, median_days_open

   • GraceQuery compiles cleanly. Submitting.
   • Grace calls submit

     with tenant_scope as (
         select id as tenant_id, name
         from tenants
         where name in ('Ballycorran Industrial Supply','Castell Fasteners','Hanse Industriebedarf','Midland Safety Wear','Polder Packaging')
     ),
     pipeline_scope as (
         select p.id as pipeline_id, p.tenant_id
         from pipelines p
         join tenant_scope t on p.tenant_id = t.tenant_id
         where p.name = 'New business'
     ),
     lost_opportunities as (
         select
             o.id as opportunity_id,
             o.created_at,
             o.closed_at,
             (extract(epoch from (o.closed_at - o.created_at)) / 86400.0) as days_open
         from opportunities o
         join pipeline_scope ps
             on o.pipeline_id = ps.pipeline_id
             and o.tenant_id = ps.tenant_id
         where o.is_closed = true
           and o.is_won = false
           and o.closed_at is not null
     ),
     dated as (
         select
             lo.opportunity_id,
             lo.days_open,
             cd.year_num,
             cd.quarter_num
         from lost_opportunities lo
         join calendar_days cd on cd.calendar_date = lo.closed_at::date
     )
     select
         (year_num || 'Q' || quarter_num) as quarter,
         count(*) as lost_opportunities_count,
         round(avg(days_open)::numeric, 1) as avg_days_open,
         round(percentile_cont(0.5) within group (order by days_open)::numeric, 1) as median_days_open
     from dated
     group by year_num, quarter_num
     order by year_num, quarter_num

   • The warehouse answers

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

   • GraceSubmitted. The report groups the five named B2B tenants' lost opportunities (New business pipeline, is_closed=true, is_won=false) by the calendar quarter of closed_at, with count, average, and median days-open (closed_at − created_at) rounded to 1 decimal.

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

   Grace's transcript (261 KB) · Daniel Kim's transcript (789 KB) · Patrick O'Neill's transcript (263 KB) · the night's plan (6 KB) · the night's reports (7 KB) · the week's memo (3 KB)