The Scenario
You are a VP of Sales. Last quarter you lost 80 deals. Your board wants to know why.
You have a gut feeling — price is the main objection. But you also suspect one pipeline stage is where deals die before they ever get to price, and you have a theory about which rep has the highest loss rate on price deals specifically.
All the data is in Kommo. Getting it into a shape where you can actually answer those questions is three hours of export, VLOOKUP, and pivot table work that you do not have time for on a Tuesday afternoon.
The bad version of this analysis:
- Export Kommo closed-lost deals to CSV — the loss reason is an ID, not a name
- Export the loss reason list separately — another CSV
- Open both in Google Sheets, VLOOKUP the reason names onto the deal rows
- Build a pivot table for deal count by reason
- Build a separate pivot for average deal size by reason
- Realize you still need to filter by pipeline stage and cross-tab by rep
- It is 6 PM and your board prep is two hours away.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet that can query Kommo for both the raw deals and the reference data, join them, and produce the pivot in one run.
Open the SheetXAI sidebar and type:
List all loss reasons from Kommo and write them to a 'Loss Reasons' sheet. Then list all leads moved to 'Closed Lost' in the last 90 days and write them to a 'Lost Deals' sheet with columns: lead_id, lead_name, loss_reason_id, value, pipeline_stage, responsible_user. Then join the two sheets on loss_reason_id and create a 'Loss Analysis' sheet with a pivot showing: loss reason name, count of deals, total value lost, average deal size, and top responsible user by deal count for each reason.
Three sheets, one prompt, ready for the board.
What You Get
A complete win/loss breakdown built from live Kommo data:
- Loss Reasons sheet — all canonical loss reasons from Kommo, with IDs and names
- Lost Deals sheet — every closed-lost lead from the past 90 days with full metadata
- Loss Analysis pivot — loss reason name, deal count, total value lost, average deal size, top rep per reason
The pivot is built from the actual join, not from memory or estimation. If 'Price' accounts for 38% of your lost deal count but only 22% of the lost value, the pivot shows it. If the rep with the highest price loss rate is also your top closer, that is visible too.
What If the Data Is Not Quite Ready
Win/loss analysis almost always requires extra cuts before it is boardroom-ready. SheetXAI handles the extra slices inline.
When loss reason IDs in the deal export are blank for older records
Some older deals were closed before loss reasons were required in Kommo. You do not want them skewing your counts.
When building the Lost Deals sheet, exclude any rows where loss_reason_id is blank. Write those rows to a separate 'Uncategorized Lost' sheet so they are not dropped silently.
When you want to break the analysis down by pipeline stage
Price may be the top loss reason overall, but what if it only shows up at the Proposal stage? That is a different problem than if it shows up at Negotiation.
Using the 'Lost Deals' and 'Loss Reasons' sheets, create a 'Stage Loss Analysis' sheet with a pivot showing: for each pipeline stage, break down deal count by loss reason name. Sort each stage block by deal count descending.
When you want to filter to a specific time range
Your board review covers Q1 only — January 1 through March 31 — not the rolling 90 days.
Re-pull the 'Lost Deals' sheet filtering to leads closed between January 1 and March 31 of this year only. Rebuild the 'Loss Analysis' pivot with that filtered set.
When you want the full breakdown in one shot: data pull, join, pivot, and rep ranking
The board meeting is in two hours and you need everything ready, not just the pivot.
Pull all loss reasons from Kommo and write them to 'Loss Reasons'. Pull all closed-lost leads from Q1 (January 1 to March 31) and write them to 'Lost Deals' with columns: lead_id, lead_name, loss_reason_id, value, pipeline_stage, responsible_user. Join them on loss_reason_id. Create a 'Loss Analysis' sheet with: loss reason name, deal count, total value, average value. Create a second tab 'Rep Loss Rates' showing each responsible_user, their total closed-lost count, their top loss reason, and their total value lost — sorted by total closed-lost count descending.
The pattern: instead of doing the join and pivot manually in three steps, you describe the full output you need and let SheetXAI build it.
Try It
Get the 7-day free trial of SheetXAI and run your next win/loss review directly from a Google Sheet connected to Kommo. The Kommo integration is included in every SheetXAI plan. For related workflows, see how to export pipeline deals for a quarterly review or the Kommo in Google Sheets overview.