Calculate True Cohort Win Rates from a HubSpot Deals CSV Export

Why standard CRM win rate charts mislead RevOps teams

Most CRM dashboards divide Deals Closed Won in Q3 by Deals Closed Won + Closed Lost in Q3. That metric (close-date win rate) gets distorted whenever reps carry out a pipeline cleanup and mass-close 50 stale deals from last year as Closed Lost on the final day of the quarter.

Grouping win rate by Create Date cohort (DATETRUNC('month', [Create Date])) tracks what percentage of deals created in a given month eventually closed won, unaffected by when old deals were purged.

How to export HubSpot Deals for win rate analysis

1. In HubSpot, open CRM > Deals and select All deals.
2. Ensure the table includes Record ID, Deal Name, Pipeline, Deal Stage, Deal Owner, Deal Type (New Business vs. Existing Business), Amount, Create Date, Close Date, Is Closed Won, and Is closed lost (as of October 2026).
3. Click Export > CSV and download the file.

Three win rate formulas and SQL equivalents

1. Closed-Deal Win Rate vs. Total Cohort Win Rate

For cohorts older than your median sales cycle, compare Closed Win Rate (Won / (Won + Lost)) against Total Cohort Conversion (Won / All Created, which includes deals still sitting open).

xmetric / Tableau formula ([Closed Count Win Rate]):

SUM(IF [Deal Stage] = 'Closed Won' THEN 1 ELSE 0 END)
/
NULLIF(SUM(IF [Deal Stage] IN ('Closed Won', 'Closed Lost') THEN 1 ELSE 0 END), 0)

xmetric / Tableau formula ([Total Cohort Win Rate]):

SUM(IF [Deal Stage] = 'Closed Won' THEN 1 ELSE 0 END) / COUNT([Record ID])

2. Dollar-Weighted Win Rate vs. Count Win Rate

For example (sample numbers), if a rep wins 8 out of 10 small deals (5K each) and loses 5 out of 5 enterprise deals (100K each), their count win rate is 53% (8 / 15), but their dollar win rate is only 7.3% (40K / 550K). Tracking the gap between count and dollar win rate exposes discounting and large-deal qualification issues.

xmetric / Tableau formula ([Dollar Win Rate]):

SUM(IF [Deal Stage] = 'Closed Won' THEN [Amount] ELSE 0 END)
/
NULLIF(SUM(IF [Deal Stage] IN ('Closed Won', 'Closed Lost') THEN [Amount] ELSE 0 END), 0)

SQL equivalent:

SELECT
  DATE_TRUNC('month', "Create Date") AS create_cohort,
  "Deal Owner" AS deal_owner,
  COUNT(*) AS total_created,
  COUNT(*) FILTER (WHERE "Deal Stage" = 'Closed Won') AS won_count,
  COUNT(*) FILTER (WHERE "Deal Stage" = 'Closed Lost') AS lost_count,
  COUNT(*) FILTER (WHERE "Deal Stage" NOT IN ('Closed Won', 'Closed Lost')) AS still_open_count,
  ROUND(
    COUNT(*) FILTER (WHERE "Deal Stage" = 'Closed Won') * 100.0
    / NULLIF(COUNT(*) FILTER (WHERE "Deal Stage" IN ('Closed Won', 'Closed Lost')), 0),
    1
  ) AS closed_count_win_rate_pct,
  ROUND(
    SUM("Amount") FILTER (WHERE "Deal Stage" = 'Closed Won') * 100.0
    / NULLIF(SUM("Amount") FILTER (WHERE "Deal Stage" IN ('Closed Won', 'Closed Lost')), 0),
    1
  ) AS closed_dollar_win_rate_pct
FROM hubspot_deals
WHERE "Deal Type" = 'New Business'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

3. Rep Win Rate Relative to Team Average in the Same Deal Size Tier (FIXED LOD)

An enterprise AE will naturally have a different win rate than an SMB AE. A FIXED Level of Detail expression compares each rep against the peer average within the exact same Amount tier.

xmetric / Tableau formula:

[Closed Count Win Rate]
- MIN({ FIXED [Deal Size Tier] : [Closed Count Win Rate] })

Common traps in HubSpot win rate reporting

• Mixing New Business and Renewals in one pipeline: If renewals and upsells live in the same HubSpot pipeline as new logos, always filter Deal Type = New Business on the Filters shelf in the xmetric app (or in your query) before reading win rates.
• Unqualified Stage-0 deals: If marketing or SDRs automatically create a HubSpot deal for every booked meeting before an AE qualifies it, filter your denominator to deals that reached at least Stage 2 (Date entered Qualified is not null) to measure qualified win rate (SQL-to-Close).