How to Create a Win-Loss Analysis with AI: Step by Step
A win-loss analysis compares the deals you closed against the deals you lost, then explains the difference. Build it from a closed-deals export. Count wins and losses by segment, divide to get win rate, then group the loss reasons into a short list. The counting takes minutes. The grouping is where every win-loss analysis falls apart.
This guide walks the spreadsheet route first, shows exactly where it stalls, then rebuilds the same analysis from the export in three steps.
What a win-loss analysis actually tells you
Three questions, in order of how often they change a decision.
How often do we win? Win rate is wins divided by closed deals in a period. On its own it is a scoreboard, not an insight.
Where do we win? The same rate split by segment, product, or deal size usually moves more than the overall number. A company at 35% overall might be at 41% in mid-market and 22% in enterprise, and those two numbers point at two different problems.
Why do we lose? This is the part everybody wants and almost nobody gets cleanly. The answer lives in a free-text field that five reps filled in five different ways.
A win-loss analysis that answers only the first question is a report. One that answers all three is a decision document, and it is worth the extra hour.
What you need before you start
- A closed-deals export covering at least two quarters, one row per opportunity.
- Four columns at minimum: close date, outcome, segment or product, and amount.
- A loss-reason column, even a messy one. This is the column that decides whether the analysis is useful.
- One definition of a period. Close date or created date, chosen once and written down.
Microsoft's PivotTable guidance sets the format requirement plainly: "Your data should be organized in columns with a single header row." CRM exports frequently arrive with two header rows or a merged title bar, so fix that before anything else.
One more thing worth agreeing before the first build: who owns the reason field. In most teams the rep closes the deal and picks the label. Reps are optimising for closing the next one rather than for clean taxonomy. That is not a character flaw. It just means the label set has to be short enough that the right choice is obvious in three seconds. One person should own that list each quarter.
How to do a win-loss analysis in a spreadsheet
Option 1: count wins and losses with COUNTIFS
COUNTIFS "applies criteria to cells across multiple ranges and counts the number of times all criteria are met." That is the whole win-rate calculation. Count rows where outcome is Won and segment is Mid-Market, count all closed rows for that segment, then divide.
Two documented details save time later. Microsoft notes that "each additional range must have the same number of rows and columns as the criteria_range1 argument." Ragged selections therefore break the formula quietly. And up to 127 range and criteria pairs are allowed, which is far more than any analysis needs.
One trap sits in the same page. If a criteria argument points at an empty cell, COUNTIFS "treats the empty cell as a 0 value." A blank segment therefore counts as something rather than nothing, which is how a segment nobody sells to ends up with deals in it.
Option 2: summarise with a PivotTable
Drop outcome into Rows and amount into Values, and you have won and lost pipeline by segment. To show win rate as a share rather than a count, open Value Field Settings and use the Show Values As tab, where % of Grand Total lives.
You can also show a value as both a calculation and a percentage. Microsoft's instruction is to "drag the item into the Values section twice," then set Summarize Values By and Show Values As for each one. That gives you deal count and share of total side by side without a second pivot.
Option 3: add the loss reasons
Now drag the loss-reason column into the PivotTable and watch what happens. Microsoft documents the behaviour precisely: "By default, PivotTable fields placed in the Values area are displayed as a SUM. If Excel interprets your data as text, the data is displayed as a COUNT."
That is correct and expected. A reason column is text, so it counts. The trouble is what it counts.
Where the spreadsheet route slows down
Your reason column probably contains Price, price, Too expensive, Budget, No budget this year, and Lost on cost. Those are one reason written six ways. A count of distinct strings tells you that six things happened, when really one thing happened six times.
So somebody sits down with the list and decides which labels mean the same thing. That judgement is the actual analysis, and it is manual, and it has to be redone every quarter as reps invent new phrasings.
Three smaller frictions compound it.
The real reason is often in the notes. The picklist holds a shrug and the free-text field holds the story, and nobody aggregates free text by hand.
Small segments read as trends. Four deals in a segment is an anecdote, but a percentage next to it looks like a finding.
The pivot goes stale. The PivotTable documentation matters here too. When new rows arrive, "any PivotTables that were built on that data source need to be refreshed." One forgotten refresh, and last quarter's analysis gets presented as this quarter's.
How to create a win-loss analysis with AI
Step 1: Upload the closed-deals export
Open Powerdrill Bloom and upload the export. Excel, CSV, PDF, and docs are all listed as supported uploads on the free plan, so a raw CRM download works without cleanup first.
Do not delete the messy reason column, and do not delete the notes field either. They are the two most valuable columns in the file, and they are the two people usually strip out before they start.
Step 2: Ask for the counts and the reason groups in one prompt
Describe the analysis in natural language. Ask for win rate by segment across the last two quarters, plus average deal size for won and lost deals. Then ask for the loss reasons grouped into a short list, with the raw labels that rolled into each group.
Ask for the mapping, not just the totals. Seeing that Lost on cost and No budget this year were folded together lets you accept or reject that call. That is a judgement call rather than a calculation, and it is the part you want to review rather than delegate.
Add a floor while you are there. Ask for segments below a minimum deal count to be shown with their count, or suppressed entirely. That stops the four-deal segment from being read as a trend.
Each figure comes back with the row behind it. You can open a suspicious count and read the deals that produced it before it reaches a slide.
Step 3: Turn it into the document people will read
Ask for the layout directly. Headline win rate, trend against the prior period, win rate by segment, grouped loss reasons, and one line on the biggest movement.
Save the prompt. Next quarter you upload the new export and run the same words, which is what turns a one-off analysis into a repeatable one. If the output has to become a recurring published document, the AI report generator page covers that route. The make graphs from Excel page covers the chart side.
What belongs in a win-loss analysis
| Block | What it answers | Common mistake |
|---|---|---|
| Headline win rate | Are we converting better than last period? | Mixing open deals into the denominator |
| Win rate by segment | Where is the strength concentrated? | Reporting segments with three deals as if they were stable |
| Average deal size, won vs lost | Are we losing the big ones or the small ones? | Using median in one row and mean in another |
| Grouped loss reasons | What keeps beating us? | Counting raw labels instead of grouped ones |
| Sales cycle length | Are losses slow or fast? | Measuring from created date in one row, close date in another |
| Competitive losses | Who are we actually losing to? | Leaving the competitor field blank on half the rows |
| One movement callout | What changed since last quarter? | Listing every change instead of the one that matters |
Keep the finished document to a single page. A win-loss analysis that runs to nine slides gets read once.
One column is missing from most versions of this table, and it is the cheapest one to add: the date of the export. Two people comparing last quarter's file with this quarter's will eventually disagree about which deals were open at the time. A single line stating when the data was pulled ends that conversation before it starts.
A worked example
Say a quarter closes with 180 deals: 63 won, 117 lost. Overall win rate is 35%. Split by segment and enterprise sits at 22% on 27 deals while mid-market sits at 41% on 96 deals.
The grouped reasons matter more than either number. Say 44 of the 117 losses roll up to price and 31 roll up to a missing integration. Those are two different conversations with two different teams. The raw list, ungrouped, would have shown eleven reasons and prompted neither.
Now add the deal-size row. Say the 44 price losses average $12k and the 31 integration losses average $58k. The integration problem is then worth roughly twice the revenue, despite having fewer deals behind it. That reversal is the single most common thing a count-only analysis hides.
Who reads it, and how often
Three audiences use this document differently, and the layout should respect that.
Sales leadership reads the headline rate and the movement callout, and stops there unless something moved. Product and marketing read the grouped reasons, because that is where the roadmap and the messaging arguments start. Finance reads deal size, because a rising win rate on shrinking deals is not the win it looks like.
Build for the first audience and annotate for the other two. One page, three readable layers.
Getting a usable reason list
Everything above depends on a loss-reason field somebody can group. Three habits fix most of the mess at source.
Cap the picklist at seven options. A longer list guarantees inconsistent picking, because nobody scrolls a dropdown at the end of a lost deal.
Separate the reason from the story. One required picklist for the category, one optional free-text box for the detail. Merge them and you get neither.
Make one option honest. Include an explicit Unknown or No decision choice. Without it, every ambiguous loss gets filed under price, and price becomes the answer to everything.
Review the mapping, not the field. You will never get clean data at entry. What you can get is a stable mapping from messy labels to seven groups, reviewed once a quarter by one person.
Common mistakes
Counting open deals in the denominator. Win rate is wins divided by closed deals. Including still-open opportunities flatters the number every time.
Letting the reason list grow forever. If this quarter has eleven reasons and last quarter had six, you are watching vocabulary drift rather than market change. Fix the grouping, then compare.
Reading small segments as trends. Show the count next to every rate so readers can calibrate for themselves.
Presenting a stale pivot. New rows in the source do not update a PivotTable on their own, and a refreshed-looking layout with old numbers is worse than no analysis.
Hiding the mapping. Whoever reads this will eventually ask what counted as "price." Publish the grouping alongside the chart and that conversation takes ten seconds.
Running it only after a bad quarter. A win-loss analysis you build when the number drops has no baseline to compare against. Running it quarterly, in good quarters too, is what makes the bad one legible.
Reporting rate without volume. A segment that moved from 20% to 40% on five deals has not doubled anything. Print the count beside every percentage and the reader calibrates themselves.
Conclusion
The arithmetic in a win-loss analysis is genuinely simple: COUNTIFS for the counts, a PivotTable for the summary, % of Grand Total for the share. The work that takes the afternoon is deciding what your loss reasons actually say.
Hand that part to a tool that can group the labels, show you the mapping, and point back to the deals behind each number. Try Powerdrill Bloom on last quarter's closed-deals export and see how much of your reason list collapses.
For the pipeline view upstream of this, see our guide on turning a CRM export into a pipeline report. The roundup of AI tools for sales pipeline analysis covers the tooling side.
Frequently asked questions
What is win-loss analysis?
It is a structured comparison of the deals you won against the deals you lost in the same period. It covers win rate, where that rate differs by segment, and the grouped reasons behind the losses.
How do you calculate win rate?
Divide the number of won deals by the total number of closed deals in the same period. COUNTIFS handles both counts, since it counts rows where every criterion is met across matching ranges.
Why does my loss-reason field show a count instead of a total?
Because it is text. Microsoft documents that PivotTable fields in the Values area display as a SUM by default. They display as a COUNT when Excel interprets the data as text.
How many loss reasons should a win-loss analysis have?
Few enough to compare across quarters, which in practice means about five to seven groups. Keep the raw labels underneath so anyone can check what rolled into each group.
How often should you run a win-loss analysis?
Quarterly suits most teams, because a month rarely holds enough closed deals per segment to be stable. Whatever the cadence, regenerate it from a fresh export rather than editing last period's file.