CRM hygiene: a checklist for clean pipeline data

By Giorgio Bonan. Updated October 22, 2026, published October 22, 2026.

CRM hygiene is the routine that keeps CRM records accurate, complete and current: duplicates merged, dead deals closed, close dates in the future, and the fields your reports read actually filled in. For a sales team, the part that matters most is pipeline hygiene, because every forecast, coverage ratio and win rate is computed from those records. Dirty contact data wastes an email; a dirty pipeline misleads the whole company about next quarter.

This article covers what poor data costs, the ten checks we run on a pipeline (each with a SOQL query you can paste into Salesforce), who owns each fix, and a cadence that keeps the data clean without a quarterly cleanup marathon.

What poor CRM data costs

The most cited numbers come from surveys, so read them as estimates. In Validity's 2024 report on the state of CRM data management, 24% of CRM admins said less than half of their data is accurate and complete, and 31% said poor-quality data costs them at least 20% of their annual revenue. In the same survey, 67% were concerned about whether their data was ready for AI and machine learning applications.

The time cost is easier to see. Salesforce's own research, published in December 2022, found that reps spend just 28% of their week actually selling, with deal management and data entry taking much of the rest. Every field you ask a rep to update competes with selling time, which is why fields nobody reads go stale first, and why a hygiene routine should ask for as few fields as possible.

For forecasting, the cost has a precise shape. A forecast sums amounts by forecast category and does not check whether a deal is alive, as our guide to Salesforce forecasting shows on a worked month. One silent $60,000 deal left in Commit inflates the number by $60,000 until someone closes it.

Contact hygiene and pipeline hygiene are different jobs

Most guides on CRM data hygiene are written for marketing: bounced emails, incomplete contact records, unsubscribes, enrichment. That work matters, but it is a different job from pipeline hygiene, with a different owner and a different rhythm.

A pipeline can be full of perfect contact records and still forecast badly. The checks below focus on the pipeline, with two account checks that affect it directly.

Ten checks, each with a SOQL query

Each query below was run on a live Salesforce org on 22 October 2026 to confirm it is valid. Paste them into the Developer Console query editor, or use them as the logic of a report or list view. Thresholds (30 days, 45 days) are starting points: set them to the normal rhythm of your sales cycle and keep them fixed, so trends mean something.

1. Open deals with a close date in the past

The most common defect, and the easiest to fix. A past close date means the deal either closed and nobody updated it, or slipped and nobody said so.

SELECT Owner.Name, Name, StageName, Amount, CloseDate
FROM Opportunity
WHERE IsClosed = false AND CloseDate < TODAY
ORDER BY CloseDate

2. Open deals with no activity in 30 days

LastActivityDate is the date of the latest task or event logged on the opportunity. A deal in a late stage with no logged activity for a month is the main source of inflated forecasts.

SELECT Owner.Name, Name, StageName, ForecastCategoryName, Amount, LastActivityDate
FROM Opportunity
WHERE IsClosed = false
  AND (LastActivityDate = null OR LastActivityDate < LAST_N_DAYS:30)
ORDER BY Amount DESC

3. Deals stuck in the same stage

LastStageChangeDate records when the stage last changed; it is empty when the stage never changed after creation. See our guide to Salesforce opportunity stages for exit criteria that make this check meaningful.

SELECT Owner.Name, Name, StageName, LastStageChangeDate, CreatedDate
FROM Opportunity
WHERE IsClosed = false
  AND (LastStageChangeDate < LAST_N_DAYS:45
       OR (LastStageChangeDate = null AND CreatedDate < LAST_N_DAYS:45))

4. Deals whose close date was pushed twice or more

PushCount counts how many times the close date moved into a later month. One push is normal; repeated pushes usually mean the deal has no real timeline.

SELECT Owner.Name, Name, PushCount, CloseDate, Amount
FROM Opportunity
WHERE IsClosed = false AND PushCount >= 2
ORDER BY PushCount DESC

5. Open deals with no next step

SELECT Owner.Name, Name, StageName, Amount
FROM Opportunity
WHERE IsClosed = false AND NextStep = null

6. Open deals with no amount

A deal with no amount is invisible in every revenue total, which makes the pipeline look smaller than it is, the rare defect that errs on the side of caution.

SELECT Owner.Name, Name, StageName
FROM Opportunity
WHERE IsClosed = false AND (Amount = null OR Amount = 0)

7. Open deals with no contact role

Without a contact role, nobody can tell who the buyer is, and activity logged on contacts may not show on the deal.

SELECT Owner.Name, Name, StageName, Amount
FROM Opportunity
WHERE IsClosed = false
  AND Id NOT IN (SELECT OpportunityId FROM OpportunityContactRole)

8. Open deals owned by inactive users

When someone leaves, their pipeline stays open under their name, outside every manager's review.

SELECT Owner.Name, Name, StageName, Amount
FROM Opportunity
WHERE IsClosed = false AND Owner.IsActive = false

9. Accounts with the same name

A rough but fast duplicate check. Salesforce's matching and duplicate rules handle the fuzzy cases; this query finds the exact ones that slipped through.

SELECT Name, COUNT(Id) n
FROM Account
GROUP BY Name
HAVING COUNT(Id) > 1

10. Contacts sharing an email address

SELECT Email, COUNT(Id) n
FROM Contact
WHERE Email != null
GROUP BY Email
HAVING COUNT(Id) > 1

Who owns each fix

A hygiene routine fails in one of two ways: everyone is responsible, so nobody acts, or one admin is responsible for data they cannot judge. Split it by who has the knowledge.

The rule we apply: automate the detection, never the decision. A validation rule can force a next step before a deal enters Proposal. Nothing automatic can tell you whether a buyer who has been silent for five weeks is gone or on holiday. Closing that deal automatically would trade one wrong number for another.

A cadence that holds

The weekly slot carries most of the value, because pipeline data decays in days. Tie it to the forecast call: a deal that cannot survive the four questions (is the date in the future, did anything happen this month, did it move stage, how many times was it pushed) does not belong in Commit. Pipeline Inspection helps with what changed; the queries above catch what did not.

Two settings protect the quarterly work. Before changing forecast periods, the fiscal year or the rollup method, export forecast adjustments: Salesforce's Pipeline Forecasting Implementation Guide warns that those changes purge adjustments, manager judgments and quotas. And keep the stage to forecast category mapping under review, since every check above assumes the category means what it says.

Measure whether hygiene is working

Hygiene is only useful if it changes the numbers you report. Three measures tell you whether it does:

1. Share of open pipeline with no activity in 30 days. The sum of amounts from check 2, divided by the total open amount. It should fall, then stay low.
2. Forecast accuracy against a frozen snapshot. Use the forecast accuracy formula on the forecast taken at a fixed date each period. Cleaner Commit should mean smaller misses.
3. Coverage on clean pipeline. Run the pipeline coverage calculator on the pipeline left after silent deals are excluded. If coverage looks healthy only before cleaning, the target is at risk.

Track all three per rep, not only per team. A team average can look fine while one rep carries most of the silent deals, and that is the conversation the weekly review exists for.

TruthPipe runs the pipeline half of this checklist every night: it reads open opportunities and their activity in Salesforce, lists the deals that stopped moving, and gives each rep an integrity score with a 30-day trend, for $99 per month per org. It reads and never writes, so the decisions stay with the rep and the manager.