Twelve queries that show you how much of your pipeline nobody is working.
Every CRM vendor will tell you your data is bad. None of them will show you. Run these read-only SOQL queries against your own org and find out in four minutes. No install, no form, nothing leaves your Salesforce.
Four minutes, read-only, no install
Open Salesforce, click the gear icon, then Developer Console. Go to the Query Editor tab at the bottom, paste a query, hit Execute. Nothing here writes to your org and nothing leaves it.
If Developer Console is locked down in your org, Workbench or the Salesforce Inspector browser extension will run the same queries. You need read access to Opportunity, Task, Event, Account, Contact and User.
Every query below was executed against a live org before publishing. If one errors on your org, it is almost always a custom field name or a permission gap, not a syntax problem.
Your open pipeline
Run this first. Every number below is a share of this one, and a count without a denominator tells you nothing.
Write both figures down. You need them to read the rest.
SELECT COUNT(Id) openOpps, SUM(Amount) openPipeline
FROM Opportunity
WHERE IsClosed = falsePipeline at risk
Open deals that are stale, have no next step, or are already past their close date. Deduplicated, so a deal with all three problems counts once.
Above 30% of open pipeline, your forecast is fiction. This is the number to take to your next pipeline review.
SELECT COUNT(Id) atRisk, SUM(Amount) atRiskPipeline
FROM Opportunity
WHERE IsClosed = false
AND (LastModifiedDate < LAST_N_DAYS:30
OR NextStep = null OR NextStep = ''
OR CloseDate < TODAY)Nobody has touched it in 30 days
Open deals where no field has changed in a month.
A deal nobody edits is a deal nobody is working. Compare this against how many your reps claim are active.
SELECT COUNT(Id) staleOpps, SUM(Amount) stalePipeline
FROM Opportunity
WHERE IsClosed = false AND LastModifiedDate < LAST_N_DAYS:30No next step
Open deals with the Next Step field empty.
Above 10% empty, your pipeline reviews turn into guesswork. A deal with no next step is a deal nobody has decided what to do with.
SELECT COUNT(Id) noNextStep, SUM(Amount) pipelineAtRisk
FROM Opportunity
WHERE IsClosed = false AND (NextStep = null OR NextStep = '')Close dates already in the past
Open deals that were supposed to close before today.
Every one of these is a date a rep chose not to update. This is the cheapest hygiene problem to fix and the most visible when you don't.
SELECT COUNT(Id) overdue, SUM(Amount) phantomPipeline
FROM Opportunity
WHERE IsClosed = false AND CloseDate < TODAYNo amount
Open deals with an empty Amount field.
Above 10%, your forecast has a hole in it that no dashboard will show you. These deals are invisible to every roll-up you run.
SELECT COUNT(Id) noAmount
FROM Opportunity
WHERE IsClosed = false AND Amount = nullCold pipeline, rep by rep
Open deals with no logged activity in 14 days, grouped by owner.
Read the spread, not the average. One rep carrying most of the cold pipeline is a coaching conversation. Every rep carrying some is a process problem.
SELECT Owner.Name, COUNT(Id) opps, SUM(Amount) pipeline
FROM Opportunity
WHERE IsClosed = false
AND (LastActivityDate = null OR LastActivityDate < LAST_N_DAYS:14)
GROUP BY Owner.Name
ORDER BY SUM(Amount) DESCWho actually logs anything
Tasks created in the last 30 days, by owner. Uses CreatedDate, because ActivityDate is the due date and is often empty.
A 5x gap between your top and bottom logger is normal and wrong. If a rep returns zero, they are not lazy, they are working outside Salesforce.
SELECT Owner.Name, COUNT(Id) activitiesLogged
FROM Task
WHERE CreatedDate = LAST_N_DAYS:30
GROUP BY Owner.Name
ORDER BY COUNT(Id) DESCMeetings, not just calls
Events created in the last 30 days.
Run this before you accuse anyone of logging nothing. Reps who look silent on Tasks are frequently logging Events instead.
SELECT COUNT(Id) events
FROM Event
WHERE CreatedDate = LAST_N_DAYS:30Where deals go to sit
Open deals and pipeline value by stage.
One stage holding more than 40% of open deals is where your process breaks. Usually it is the stage right after the first real objection.
SELECT StageName, COUNT(Id) opps, SUM(Amount) pipeline
FROM Opportunity
WHERE IsClosed = false
GROUP BY StageNameAccounts nobody has touched in a quarter
Accounts with no activity in 90 days, or none ever.
This is your dormant install base. In most orgs it is larger than the sales team believes and cheaper to reach than new logos.
SELECT COUNT(Id) coldAccounts
FROM Account
WHERE LastActivityDate = null OR LastActivityDate < LAST_N_DAYS:90Contacts you cannot contact
Contact records with no email address.
Above 15%, your marketing team is running campaigns against a database that cannot receive them.
SELECT COUNT(Id) unreachableContacts
FROM Contact
WHERE Email = nullLicences nobody logs into
Active Salesforce-licensed users, oldest login first. The UserLicense filter keeps integration and system accounts out of the list.
Anyone active who has not logged in for 30 days is a seat you are paying for and a person working somewhere else.
SELECT Name, LastLoginDate
FROM User
WHERE IsActive = true AND Profile.UserLicense.Name = 'Salesforce'
ORDER BY LastLoginDate ASC NULLS FIRST
LIMIT 25Three ways people misread their own numbers
Don't add queries 02, 03 and 04
They describe overlapping sets of the same deals. A deal that is stale, has no next step and is overdue appears in all three. Query 01 is the deduplicated union and the only one safe to quote as a total.
Zero is a result, not an error
If query 07 returns nothing, your team logged no tasks in 30 days. That is the finding. Check query 08 before drawing conclusions, because reps who look silent on Tasks are often logging Events instead.
The spread matters more than the average
A team averaging 40 logged activities a month sounds healthy until you see one rep at 130 and four at 12. Averages hide the exact problem you are trying to find.
Get all twelve queries plus the thresholds
One email, the full pack in a format you can paste straight into Developer Console, and the threshold table for reading the results. Send your numbers back and you get a written read on them, free.
If the numbers came back ugly
Bad hygiene is a behaviour problem, not a reporting one. Dashboards measure it. Nothing about a dashboard makes a rep fill in a next step on a Tuesday afternoon.
Novigem scores those behaviours inside Salesforce as they happen, so the number moves instead of just being watched. Run these queries again six weeks later and compare.