Cookbook
Report the quarter by stage and by owner
The report every sales manager builds by exporting to a spreadsheet. It is one question, the totals are computed rather than added up by hand, and the interesting row is the one with no name on it.
The situation
You need pipeline by stage and pipeline by person, and you need it often enough that the export-and-pivot version quietly costs you an hour a week. Worse, the spreadsheet is a copy: by Thursday it disagrees with the CRM and you are the only one who knows which is right.
What must be true when you are done
- Both totals come from one question each, over every deal rather than a page of them.
- Stages and people come back named, not as identifiers you have to look up.
- You know whether closed deals are in the number, because you chose.
- Anything unowned is stated as its own row rather than folded in or dropped.
By stage
You
Agent
Read that total before you quote it. It is 371,900 across ten deals, and 28,400 of it is already lost. The question asked for every deal and that is what it answered, honestly and unhelpfully. Nothing is wrong; the question was.
You
Agent
343,500. That is the number you can say out loud. The habit worth forming is stating the filter as part of the answer rather than the question: "open pipeline is 343,500 across eight deals" cannot be misread six weeks later, and "pipeline is 371,900" can.
By owner, including the row nobody owns
You
Agent
The unowned deals come back as a real row with a real total rather than being quietly excluded. That is the design decision that makes this report worth running: a report which drops what it cannot attribute is a report that gets less accurate exactly as your data gets worse, and tells you it is fine.
Sixty thousand on nobody's list is a finding. Fixing it is one pass, and it is worth doing before this report goes to anybody who will act on the per-person numbers, because until then those numbers are all missing the same unknown amount.
Each owner appears under whatever that owner is. The run above was driven by API credentials rather than by people, so the rows say so; where a person owns the account, the row carries their name.
Ask for the cut you actually argue about
Stage and owner are the two everybody starts with. The ones that change a conversation are usually a second dimension, and they cost the same single question.
You
You
The second one is available and almost nobody thinks to ask, because in most CRMs it means joining deals to accounts in a spreadsheet. If you ask for a cut that is not available you get told, along with the list of the ones that are, which is a faster way to find out what this report can do than any documentation.
Verify it
- Check the stage totals add up to the overall total. If they do not, something is in a stage you forgot about, and it is usually the closed ones.
- Compare the owner total to the stage total. They should match, because they are the same deals cut two ways. A gap means one of the two is filtered and you did not intend it.
- Look for the unowned row every time. Its absence is good news; assuming its absence is how the number goes wrong.
Run it again
Weekly, and before any conversation where somebody will write the number down. The forecast is the weighted version of the same question, and the gaps pass is what to run first if the unowned row keeps appearing.