The Report Nobody Trusts: Why Your Leadership Team Stopped Looking at Dashboards
How to diagnose whether you have a reporting problem or a data quality problem
If your leadership team doesn't trust your Salesforce reports, the problem isn't the reports. The problem is the data behind them.
A client called me last year to "rebuild their reporting." The request: 14 dashboards, 40+ reports, all of which "don't look right." The VP of Sales had stopped checking them. The CFO maintained a parallel spreadsheet. The marketing director pulled campaign data from Mailchimp instead of Salesforce because "the numbers never match."
The team assumed the reports were built wrong. They wanted me to rebuild them from scratch. I told them I'd look at the data first.
It took 90 minutes. 23% of Accounts had no owner. 31% of Opportunities had no close date. 14% of Contacts had no email address. 8% of Opportunities were still in "Open" stage with close dates from 2023. The pipeline report showed $2.4M in open Opportunities. The actual pipeline, after removing the stale records, was $1.6M. Leadership had been making hiring and budget decisions based on a pipeline that was inflated by $800K of ghost Opportunities that nobody had updated in over a year.
The reports were technically correct. They were reporting on fiction. Accurate fiction is still fiction.
I didn't rebuild a single report. I cleaned the data. The existing reports started working. The total project cost: $8,500 for data cleanup instead of $22,000 for a reporting rebuild that wouldn't have fixed anything.
Why Data Quality Deteriorates
Data quality doesn't fail all at once. It erodes one skipped field at a time.
No Required Fields at the Right Stages
Salesforce lets you mark fields as required on page layouts. Most orgs make Name and Company required at lead creation and stop there. Nobody requires Close Date at Opportunity creation. Nobody requires Email at Contact creation. Nobody requires Owner at Account creation. The result: records get created with the minimum viable information and never get updated.
Stage Progression Without Validation
An Opportunity moves from "Qualification" to "Negotiation" without anyone verifying that Amount, Close Date, and Primary Contact are populated. Validation rules can prevent this. Most orgs don't implement them because stakeholders complain that validation rules "slow them down." The tradeoff: faster data entry now, unreliable reports forever.
The "I'll Update It Later" Problem
A sales rep creates an Opportunity during a meeting. They enter the Account, the name, and the stage. They plan to add the amount and close date after the meeting. They never do. Six months later, that Opportunity is still in the pipeline with no amount and no close date, inflating the pipeline count without contributing real forecast data.
Ownership Drift
A rep leaves the company. Their Accounts and Opportunities aren't reassigned. The records sit with a deactivated user as the owner. No one receives notifications for those records. No one updates them. They become orphans: visible in reports but invisible in practice.
Integration Data Pollution
A marketing automation tool syncs leads into Salesforce. The sync creates Contact records with a company name but no email, no phone, and no lead source. These records inflate the Contact count without contributing usable data. Over time, they outnumber the manually entered records.
How to Diagnose Data Quality
Before touching a single report, run these queries. They take 15 minutes and tell you whether your reporting problem is actually a data problem.
The 10-Metric Data Quality Baseline
Open Developer Console (click the gear icon in Setup, select "Developer Console") or use Workbench (workbench.developerforce.com).
Metric 1: Accounts with no owner
SELECT COUNT(Id) FROM Account WHERE Owner.IsActive = false
Benchmark: below 5%. If this number is above 10%, you have orphaned Accounts that nobody is managing.
Metric 2: Opportunities with no close date
SELECT COUNT(Id) FROM Opportunity WHERE CloseDate = null
Benchmark: 0%. Every Opportunity should have a close date. Anything above 0% means your pipeline report is unreliable.
Metric 3: Opportunities with past close dates still in open stages
SELECT COUNT(Id) FROM Opportunity
WHERE CloseDate < TODAY
AND IsClosed = false
Benchmark: below 5%. These are the "ghost Opportunities" inflating your pipeline. The $800K discrepancy I found came entirely from this query.
Metric 4: Contacts with no email
SELECT COUNT(Id) FROM Contact WHERE Email = null
Benchmark: below 10%. Contacts without email addresses can't receive marketing campaigns, can't be matched for deduplication, and contribute nothing to engagement reporting.
Metric 5: Leads with no lead source
SELECT COUNT(Id) FROM Lead WHERE LeadSource = null
Benchmark: below 15%. If you can't attribute leads to a source, your marketing ROI reporting is guesswork.
Metric 6: Records not modified in 12+ months
SELECT COUNT(Id) FROM Account
WHERE LastModifiedDate < LAST_N_MONTHS:12
Benchmark: below 30%. Stale records indicate data that nobody is maintaining. Run the same query for Contacts and Opportunities.
Metric 7: Duplicate Accounts (estimated)
Salesforce doesn't have a native duplicate count query. Use the Duplicate Record Sets report:
Navigate to: Reports → New Report → Duplicate Record Sets
If you haven't enabled Duplicate Management, you won't have this report type. In that case, run a manual check:
SELECT Name, COUNT(Id)
FROM Account
GROUP BY Name
HAVING COUNT(Id) > 1
ORDER BY COUNT(Id) DESC
This catches exact name matches. Fuzzy duplicates (different spellings, abbreviations) require a tool like Duplicate Management rules or a third-party deduplication app.
Metric 8: Picklist values with "Other" selected
SELECT COUNT(Id) FROM Lead
WHERE LeadSource = 'Other'
Benchmark: below 10%. High "Other" usage means your picklist values don't reflect real business categories. Users default to "Other" when the right option doesn't exist.
Metric 9: Required field population rate
For each field you consider critical (Amount on Opportunity, Email on Contact, Phone on Lead), calculate the population rate:
SELECT COUNT(Id) FROM Opportunity WHERE Amount != null
Divide by total Opportunity count. Benchmark: above 95% for truly required fields.
Metric 10: Data entry format consistency
Spot-check 50 records for format variations. Are phone numbers stored as (212) 555-1234, 212-555-1234, 2125551234, and +12125551234? Are company names stored as "IBM," "I.B.M.," "International Business Machines," and "ibm"? Format inconsistency breaks reports, deduplication, and integration matching.
Scoring Your Results
Add up how many metrics fall below their benchmarks:
| Score | Assessment |
|---|---|
| 8-10 metrics at benchmark | Clean data. Your reporting problem is elsewhere. |
| 5-7 metrics at benchmark | Moderate issues. Fix the worst 3 metrics first. |
| 3-4 metrics at benchmark | Significant problems. Your reports are unreliable. |
| 0-2 metrics at benchmark | Critical. Stop trusting any dashboard until this is fixed. |
How to Fix the Data
Data cleanup follows a priority order: fix what impacts the most reports first.
Priority 1: Close the ghost Opportunities
Every Opportunity with a past close date and an open stage needs to be either updated (new close date, accurate stage) or closed-lost. Run the Metric 3 query. Export the results. Send the list to each Opportunity owner with a deadline: update or close within 5 business days. After the deadline, close any remaining stale Opportunities as "Closed-Lost" with a note: "Closed by admin during data cleanup - no update received from owner."
This single step often fixes 20-40% of the pipeline discrepancy.
Priority 2: Reassign orphaned records
Every Account and Opportunity owned by a deactivated user needs a new owner. Navigate to: Setup → Users → filter by Active = false. For each deactivated user, reassign their records to the appropriate active user or to a queue for redistribution.
Use Mass Transfer Records: Setup → Data → Mass Transfer Records → Transfer Accounts (or Opportunities). This lets you bulk-reassign by owner.
Priority 3: Populate missing required fields
Export records missing critical fields (Amount, Close Date, Email). Send lists to record owners for update. For records where the owner can't provide the data, consider whether the record should be archived rather than left incomplete.
Priority 4: Standardize formats
For phone numbers: use a data cleanup tool (Data.com Clean, DemandTools, or a custom Flow) to standardize formats. For company names: run deduplication with fuzzy matching to identify and merge variants.
How to Prevent Data Quality from Deteriorating Again
Validation rules on stage progression
Create validation rules that require critical fields at specific Opportunity stages:
Navigate to: Setup → Object Manager → Opportunity → Validation Rules → New
Example rule: "Amount is required at Negotiation stage"
- Rule Name: Amount_Required_At_Negotiation
- Error Condition Formula:
AND(ISPICKVAL(StageName, "Negotiation"), ISBLANK(Amount)) - Error Message: "Amount is required when moving to Negotiation stage."
Apply similar rules for Close Date, Primary Contact Role, and any other field your reports depend on.
Monthly data quality report
Build a dashboard with 4 report components: Accounts with no active owner (count), Opportunities past close date in open stages (count), Contacts with no email (count), Leads with no source (count). Review this dashboard on the first Monday of every month. If any number trends upward, investigate immediately.
Navigate to: Reports → New Report → build each of the 4 reports above → save to a "Data Quality" folder → create a Dashboard with these 4 components.
Ownership reassignment trigger
Create a Flow that fires when a User record is deactivated. The Flow notifies the Salesforce admin to reassign that user's records within 48 hours. This prevents ownership drift before it starts.
Field completion requirements in page layouts
Mark critical fields as required on the page layout. This isn't the same as a validation rule (which fires on save). Page layout requirements show the field as required in the UI, which prompts users to fill them in during creation.
Navigate to: Setup → Object Manager → [Object] → Page Layouts → drag the field to the layout → click the wrench icon → check "Required"
Download the Data Quality Assessment Template
The Data Quality Assessment Template is a spreadsheet with all 10 queries pre-written, benchmark targets, a scoring calculator, and an action plan tab for prioritizing fixes. Run it against your org today. The results will tell you whether you need a reporting rebuild or a data cleanup. Spoiler: it's the data.
Download it below. It's free.
Part 4 of 10 in the series: What Your Salesforce Org Says About Your Company.