Closed lost reason and loss pattern analysis

The honest version of this analysis leads with the data-quality finding, because the reason field is usually wrong rather than merely sparse. If one value accounts for more than roughly half of populated losses, you have measured rep behaviour and not buyer behaviour, and the correct output is what to instrument next rather than a pie chart.

Steps

1. Read the portal's actual loss taxonomy and stage order first. Call hubspot.list_properties for deals and read the options array of closed_lost_reason. The property is a HubSpot default, but its option values are portal-specific, and many portals also carry a parallel custom field: a free-text loss note, a competitor dropdown, a lost-to field. Enumerate everything that looks like a loss field and ask the user which is authoritative if there are several. Then call hubspot.list_pipelines for stage ids, labels, displayOrder and the closed and won flags, so losses can be attributed to the stage they died in and "late stage" is defined by order rather than by label text.

2. Size the blank-reason population before anything else. Run hubspot.search_deals with hs_is_closed EQ true, hs_is_closed_won EQ false, the closedate window, and NOT_HAS_PROPERTY on closed_lost_reason. This is the coverage number that leads the report. Compare it against the total lost count and compute populated share. Report the blank population as its own row and never redistribute it proportionally across the known reasons: unlabelled losses are systematically different, usually the oldest and the smallest.

3. Pull the full closed-lost cohort, sharded to fit a page. hubspot.search_deals with closedate BETWEEN the window bounds in epoch milliseconds, hs_is_closed EQ true and hs_is_closed_won EQ false, requesting dealname, amount, amount_in_home_currency, deal_currency_code, dealstage, pipeline, createdate, closedate, days_to_close, closed_lost_reason, hubspot_owner_id, hs_analytics_source, dealtype and notes_last_contacted. Use hs_is_closed_won EQ false rather than matching a lost stage label, because custom pipelines can carry several lost stages. This connector returns only the first page and never hands back paging.next.after, so narrow the closedate window month by month until each query comes back under the 200-row page limit, sum the shards, and report the retrieved row count with an explicit truncation caveat for any shard that hits the limit exactly. Pace at 5 requests per second or slower; the search API returns no rate-limit headers to throttle against.

4. Pull the won comparison set, or every loss statistic is uninterpretable. The same query with hs_is_closed_won EQ true, requesting closed_won_reason as well. Use it to compute time-to-loss against time-to-win: losses that take longer than wins indicate deals carried well past the point of usefulness, which is the same population the stalled-deal-audit playbook surfaces from the open side. Ebsta and Pavilion's 2025 report supports the mechanism, finding 36 percent of deals slipped and that late-stage slips beyond two months drop win rates by 113 percent.

5. Roll the reasons into three buckets and cross-tabulate them. Map each populated reason to competitive loss (the buyer bought something else), no decision (the buyer bought nothing and kept the status quo), or disqualified (never a real opportunity). Weight by amount and by stage reached rather than by count, since a loss at proposal or negotiation cost real selling time and carries real information while a first-stage loss is mostly a qualification signal. Then cross-tabulate reason against segment, source, owner and deal size: a reason uniform across every segment is probably a data-entry artefact, while a reason concentrating in one segment or one source is a real finding. If you group by hs_analytics_source and find a channel whose leads never close, the lead-source-to-revenue-attribution playbook is where the spend decision gets made.

6. Get first-party evidence, then build the two action lists. Where an Intercom connection is available, intercom.search_conversations filtered to the companies behind late-stage losses returns what the buyer actually wrote, which is the closest available substitute for a win-loss interview and is first-party text rather than a rep's dropdown pick. hubspot.search_companies gives the firmographics of lost accounts so you can test whether losses concentrate in a segment. apollo.search_organizations on the domains of no-decision losses detects changed circumstances, headcount growth, funding, new leadership, that justify re-engagement. Write both artefacts with google_sheets.add_sheet and google_sheets.append_rows: a prioritised interview list (late-stage, high-amount, final-two losses) and a re-engagement list.

7. Report. Open with the coverage line: closed-lost deal count, share with a reason populated, share concentrated in the single most common value. Then a table with one row per reason: deal count, amount lost, share of total lost amount, median stage reached, median days to loss, and the segment where it concentrates. Then the three-bucket roll-up with each bucket's share of lost amount, read against the floor and the ceiling: CSO Insights' 20.7 percent no-decision share of forecasted deals as the self-reported floor, and the 40 to 60 percent range from Dixon and McKenna's 2.5 million recorded conversations as the conversation-evidence ceiling. Close with one sentence that either names the dominant real loss driver or states plainly that the reason data will not support a conclusion, and says what to instrument instead.

Gotchas

• Enumeration filters are case-sensitive, and string values under IN must be lowercase. Filtering closed_lost_reason for "price" when the option value is "Price" returns zero rows and looks exactly like a finding. Always read the options array from hubspot.list_properties and filter on the exact option string. The same trap silently breaks any domain-exclusion join you build for the re-engagement list, producing a list that includes current customers.
• Lead with coverage, never with the modal reason. If a single value exceeds roughly half of populated losses, treat that as evidence of non-use rather than evidence about buyers. CRM loss reasons systematically under-report no-decision because reps recode stalls as competitive losses, which is why the CSO Insights figure is a floor and not a central estimate.
• "Lost" is not one stage. Custom pipelines routinely carry several lost or disqualified stages with different meanings, and some teams run a Closed Lost stage plus a separate Unqualified stage. Resolve all of them from hubspot.list_pipelines and split on hs_is_closed_won, not on a label match.
• Deals created by integrations inflate the disqualified bucket. Form fills and list imports that auto-create deals mostly end as losses with no reason, no owner and a tiny or empty amount. Bucket them as never-real rather than as losses, or the no-decision share is badly overstated.
• Reopened and recycled deals corrupt both the count and the timing. Where a team reopens a lost deal rather than creating a new one, the same opportunity is counted once, twice or not at all depending on the window. Where hs_is_closed is false but the deal has been lost before, exclude it from the loss cohort and report the count you excluded.
• Survivorship in the interview list. The prioritised list is built from deals the team logged properly, which skews toward deals reps felt good about and buyers reps could reach. State that bias in the output, because a win-loss programme built on reachable losses measures the wrong population.
• Currency, and no payment reconciliation here. Lost amount is the proposal value in the deal's own currency, so use amount_in_home_currency in multi-currency portals. Do not try to reconcile lost amounts against a payment provider: no money ever moved, so the reconciliation step from the attribution playbook does not apply.