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

B2B Wholesale: Age Profile of Currently Open New-Business Pipeline

“How much of our current B2B wholesale new-business pipeline is already older than the typical window in which deals actually win, i.e. how much open pipeline is quietly turning stale and at risk?”

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 3 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: Age Profile of Currently Open New-Business Pipeline

   Question

   How much of our current B2B wholesale new-business pipeline is already older than the typical window in which deals actually win, i.e. how much open pipeline is quietly turning stale and at risk?

   One row per

   age bucket of currently-open opportunities

   Columns

   • age_bucket
   • opportunities_count
   • pct_of_open_pipeline
   • avg_amount_eur
   • total_amount_eur

4. Ines 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 fx AS (
     SELECT quote_code, rate
     FROM exchange_rates
     WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
   ),
   scoped AS (
     SELECT
       o.id,
       o.amount,
       o.currency_code,
       o.created_at,
       (DATE '2025-12-31' - CAST(o.created_at AS DATE)) AS age_days
     FROM opportunities o
     JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
     JOIN tenants t ON t.id = o.tenant_id
     WHERE o.is_closed = false
       AND lower(p.name) = 'new business'
       AND (
         lower(t.industry) LIKE '%wholesale%'
         OR lower(t.industry) LIKE '%industrial%'
         OR lower(t.industry) LIKE '%office equipment%'
       )
   ),
   converted AS (
     SELECT
       s.*,
       CASE WHEN s.currency_code = 'EUR' THEN s.amount
            ELSE s.amount / fx.rate END AS amount_eur
     FROM scoped s
     LEFT JOIN fx ON fx.quote_code = s.currency_code
   ),
   bucketed AS (
     SELECT
       c.*,
       CASE
         WHEN age_days < 10 THEN '0-9 days'
         WHEN age_days < 20 THEN '10-19 days'
         WHEN age_days < 30 THEN '20-29 days'
         WHEN age_days < 45 THEN '30-44 days'
         WHEN age_days < 90 THEN '45-89 days'
         ELSE '90+ days'
       END AS age_bucket,
       CASE
         WHEN age_days < 10 THEN 0
         WHEN age_days < 20 THEN 10
         WHEN age_days < 30 THEN 20
         WHEN age_days < 45 THEN 30
         WHEN age_days < 90 THEN 45
         ELSE 90
       END AS bucket_order
     FROM converted c
   )
   SELECT
     age_bucket,
     COUNT(*) AS opportunities_count,
     ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_open_pipeline,
     ROUND(AVG(amount_eur), 2) AS avg_amount_eur,
     ROUND(SUM(amount_eur), 2) AS total_amount_eur
   FROM bucketed
   GROUP BY age_bucket, bucket_order
   ORDER BY bucket_order

   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 the query does: - Scope: open (is_closed = false) opportunities on each tenant's New business pipeline, restricted to tenants whose industry suggests the B2B wholesale verticals (wholesale/industrial/office equipment). - Age: 2025-12-31 − created_at, bucketed into 0-9, 10-19, 20-29, 30-44, 45-89, 90+ days, straddling the ~9‑day win and ~35‑day loss reference points. - Currency: converted to EUR using the 2025-12-31 EUR exchange rate (EUR rows pass through unconverted). - Output: one row per bucket with count, share of open pipeline, average and total EUR value, ordered youngest to oldest. One flagged deviation: the request assumed a single pre-established "wholesale tenant," but none was specified here, so I matched tenants by industry text instead of a specific tenant_id — noted in the submit note for the analyst to confirm.”

5. The report comes back overnight

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

   age_bucket | opportunities_count | pct_of_open_pipeline | avg_amount_eur | total_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:

   • q384Did B2B Wholesale New-Business Pipeline Ever Carry an Open Balance at Month-End?

   It builds on:

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

7. It becomes benchmark query q180

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

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

   WITH fx AS (  SELECT quote_code, rate  FROM exchange_rates  WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'),scoped AS (  SELECT    o.id,    o.amount,    o.currency_code,    o.created_at,    o.id AS id,    o.amount AS amount,    o.currency_code AS currency_code,    o.created_at AS created_at,    (DATE '2025-12-31' - CAST(o.created_at AS DATE)) AS age_days  FROM opportunities o  JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id  JOIN tenants t ON t.id = o.tenant_id  WHERE o.is_closed = false    AND lower(p.name) = 'new business'    AND (      lower(t.industry) LIKE '%wholesale%'      OR lower(t.industry) LIKE '%industrial%'      OR lower(t.industry) LIKE '%office equipment%'    )),converted AS (  SELECT    s.*,    CASE WHEN s.currency_code = 'EUR' THEN s.amount         ELSE s.amount / fx.rate END AS amount_eur         ELSE CAST(s.amount / fx.rate AS NUMERIC(18,6)) END AS amount_eur  FROM scoped s  LEFT JOIN fx ON fx.quote_code = s.currency_code),bucketed AS (  SELECT    c.*,    CASE      WHEN age_days < 10 THEN '0-9 days'      WHEN age_days < 20 THEN '10-19 days'      WHEN age_days < 30 THEN '20-29 days'      WHEN age_days < 45 THEN '30-44 days'      WHEN age_days < 90 THEN '45-89 days'      ELSE '90+ days'    END AS age_bucket,    CASE      WHEN age_days < 10 THEN 0      WHEN age_days < 20 THEN 10      WHEN age_days < 30 THEN 20      WHEN age_days < 45 THEN 30      WHEN age_days < 90 THEN 45      ELSE 90    END AS bucket_order  FROM converted c)SELECT  age_bucket,  COUNT(*) AS opportunities_count,  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_open_pipeline,  ROUND(AVG(amount_eur), 2) AS avg_amount_eur,  ROUND(SUM(amount_eur), 2) AS total_amount_eur  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_open_pipeline,  AVG(amount_eur) AS avg_amount_eur,  SUM(amount_eur) AS total_amount_eurFROM bucketedGROUP BY age_bucket, bucket_orderORDER BY bucket_orderORDER BY bucket_order NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   WITH fx AS (
     SELECT quote_code, rate
     FROM exchange_rates
     WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
   ),
   scoped AS (
     SELECT
       o.id AS id,
       o.amount AS amount,
       o.currency_code AS currency_code,
       o.created_at AS created_at,
       (DATE '2025-12-31' - CAST(o.created_at AS DATE)) AS age_days
     FROM opportunities o
     JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
     JOIN tenants t ON t.id = o.tenant_id
     WHERE o.is_closed = false
       AND lower(p.name) = 'new business'
       AND (
         lower(t.industry) LIKE '%wholesale%'
         OR lower(t.industry) LIKE '%industrial%'
         OR lower(t.industry) LIKE '%office equipment%'
       )
   ),
   converted AS (
     SELECT
       s.*,
       CASE WHEN s.currency_code = 'EUR' THEN s.amount
            ELSE CAST(s.amount / fx.rate AS NUMERIC(18,6)) END AS amount_eur
     FROM scoped s
     LEFT JOIN fx ON fx.quote_code = s.currency_code
   ),
   bucketed AS (
     SELECT
       c.*,
       CASE
         WHEN age_days < 10 THEN '0-9 days'
         WHEN age_days < 20 THEN '10-19 days'
         WHEN age_days < 30 THEN '20-29 days'
         WHEN age_days < 45 THEN '30-44 days'
         WHEN age_days < 90 THEN '45-89 days'
         ELSE '90+ days'
       END AS age_bucket,
       CASE
         WHEN age_days < 10 THEN 0
         WHEN age_days < 20 THEN 10
         WHEN age_days < 30 THEN 20
         WHEN age_days < 45 THEN 30
         WHEN age_days < 90 THEN 45
         ELSE 90
       END AS bucket_order
     FROM converted c
   )
   SELECT
     age_bucket,
     COUNT(*) AS opportunities_count,
     100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_open_pipeline,
     AVG(amount_eur) AS avg_amount_eur,
     SUM(amount_eur) AS total_amount_eur
   FROM bucketed
   GROUP BY age_bucket, bucket_order
   ORDER BY bucket_order NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 28, Column 29: 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": "2f870009-ee3f-4287-9aac-2f6bc974dbef", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table3.amount Decimal(14, 4) Decimal64(size = 0), __table7.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

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

   Ines's whole session, 6 turns

   • The request, as Ines received it

     Report request from Daniel Kim (analyst for the Head of B2B Commerce):
     
     Title: B2B Wholesale: Age Profile of Currently Open New-Business Pipeline
     Question: How much of our current B2B wholesale new-business pipeline is already older than the typical window in which deals actually win, i.e. how much open pipeline is quietly turning stale and at risk?
     Grain: one row per age bucket of currently-open opportunities
     Columns:
     - age_bucket
     - opportunities_count
     - pct_of_open_pipeline
     - avg_amount_eur
     - total_amount_eur
     Details: Scope: same wholesale tenant filter, opportunities where pipeline_id = the tenant's 'New business' pipeline and is_closed = false (currently open), as of the last complete data date 2025-12-31 (use the fixed literal date '2025-12-31' as the reference point for age, not CURRENT_DATE/now(), per house SQL standards). age_days = 2025-12-31 minus created_at. Bucket into: '0-9 days', '10-19 days', '20-29 days', '30-44 days', '45-89 days', '90+ days' (chosen because last night's New-business-only comparison showed won deals close in ~9 days on average and lost deals take ~35 days on average before dying, so these buckets straddle both reference points). Convert amount to EUR using exchange_rates as of 2025-12-31. pct_of_open_pipeline = that bucket's opportunity count / total open opportunities in scope, as a percentage. total_amount_eur = sum of amount_eur in that bucket (euro value of pipeline sitting at that age). Order by age_bucket ascending (by lower day bound).
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Ines calls dry_run

     WITH fx AS (
       SELECT quote_code, rate
       FROM exchange_rates
       WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
     ),
     scoped AS (
       SELECT
         o.id,
         o.amount,
         o.currency_code,
         o.created_at,
         (DATE '2025-12-31' - CAST(o.created_at AS DATE)) AS age_days
       FROM opportunities o
       JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
       JOIN tenants t ON t.id = o.tenant_id
       WHERE o.is_closed = false
         AND lower(p.name) = 'new business'
         AND (
           lower(t.industry) LIKE '%wholesale%'
           OR lower(t.industry) LIKE '%industrial%'
           OR lower(t.industry) LIKE '%office equipment%'
         )
     ),
     converted AS (
       SELECT
         s.*,
         CASE WHEN s.currency_code = 'EUR' THEN s.amount
              ELSE s.amount / fx.rate END AS amount_eur
       FROM scoped s
       LEFT JOIN fx ON fx.quote_code = s.currency_code
     ),
     bucketed AS (
       SELECT
         c.*,
         CASE
           WHEN age_days < 10 THEN '0-9 days'
           WHEN age_days < 20 THEN '10-19 days'
           WHEN age_days < 30 THEN '20-29 days'
           WHEN age_days < 45 THEN '30-44 days'
           WHEN age_days < 90 THEN '45-89 days'
           ELSE '90+ days'
         END AS age_bucket,
         CASE
           WHEN age_days < 10 THEN 0
           WHEN age_days < 20 THEN 10
           WHEN age_days < 30 THEN 20
           WHEN age_days < 45 THEN 30
           WHEN age_days < 90 THEN 45
           ELSE 90
         END AS bucket_order
       FROM converted c
     )
     SELECT
       age_bucket,
       COUNT(*) AS opportunities_count,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_open_pipeline,
       ROUND(AVG(amount_eur), 2) AS avg_amount_eur,
       ROUND(SUM(amount_eur), 2) AS total_amount_eur
     FROM bucketed
     GROUP BY age_bucket, bucket_order
     ORDER BY bucket_order

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: age_bucket, opportunities_count, pct_of_open_pipeline, avg_amount_eur, total_amount_eur

   • Ines calls submit

     WITH fx AS (
       SELECT quote_code, rate
       FROM exchange_rates
       WHERE base_code = 'EUR' AND as_of = DATE '2025-12-31'
     ),
     scoped AS (
       SELECT
         o.id,
         o.amount,
         o.currency_code,
         o.created_at,
         (DATE '2025-12-31' - CAST(o.created_at AS DATE)) AS age_days
       FROM opportunities o
       JOIN pipelines p ON p.id = o.pipeline_id AND p.tenant_id = o.tenant_id
       JOIN tenants t ON t.id = o.tenant_id
       WHERE o.is_closed = false
         AND lower(p.name) = 'new business'
         AND (
           lower(t.industry) LIKE '%wholesale%'
           OR lower(t.industry) LIKE '%industrial%'
           OR lower(t.industry) LIKE '%office equipment%'
         )
     ),
     converted AS (
       SELECT
         s.*,
         CASE WHEN s.currency_code = 'EUR' THEN s.amount
              ELSE s.amount / fx.rate END AS amount_eur
       FROM scoped s
       LEFT JOIN fx ON fx.quote_code = s.currency_code
     ),
     bucketed AS (
       SELECT
         c.*,
         CASE
           WHEN age_days < 10 THEN '0-9 days'
           WHEN age_days < 20 THEN '10-19 days'
           WHEN age_days < 30 THEN '20-29 days'
           WHEN age_days < 45 THEN '30-44 days'
           WHEN age_days < 90 THEN '45-89 days'
           ELSE '90+ days'
         END AS age_bucket,
         CASE
           WHEN age_days < 10 THEN 0
           WHEN age_days < 20 THEN 10
           WHEN age_days < 30 THEN 20
           WHEN age_days < 45 THEN 30
           WHEN age_days < 90 THEN 45
           ELSE 90
         END AS bucket_order
       FROM converted c
     )
     SELECT
       age_bucket,
       COUNT(*) AS opportunities_count,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_open_pipeline,
       ROUND(AVG(amount_eur), 2) AS avg_amount_eur,
       ROUND(SUM(amount_eur), 2) AS total_amount_eur
     FROM bucketed
     GROUP BY age_bucket, bucket_order
     ORDER BY bucket_order

   • The warehouse answers

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

   • InesSubmitted. Summary of what the query does: - **Scope:** open (`is_closed = false`) opportunities on each tenant's `New business` pipeline, restricted to tenants whose `industry` suggests the B2B wholesale verticals (wholesale/industrial/office equipment). - **Age:** `2025-12-31 − created_at`, bucketed into 0-9, 10-19, 20-29, 30-44, 45-89, 90+ days, straddling the ~9‑day win and ~35‑day loss reference points. - **Currency:** converted to EUR using the `2025-12-31` EUR exchange rate (EUR rows pass through unconverted). - **Output:** one row per bucket with count, share of open pipeline, average and total EUR value, ordered youngest to oldest. One flagged deviation: the request assumed a single pre-established "wholesale tenant," but none was specified here, so I matched tenants by industry text instead of a specific `tenant_id` — noted in the submit note for the analyst to confirm.

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

   Ines's transcript (271 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)