Salesforce Data Cleanup Automation: Sort First, Fix Second

Every org has the cleanup that never finishes. Somebody exports 40,000 accounts, opens the file, fixes the first few hundred rows, hits a record they aren’t sure about, and parks the spreadsheet in a folder called “cleanup v3.” Six months later a board report comes out wrong and the folder gets a v4.

My position on Salesforce data cleanup automation: the tool was never the bottleneck. Decisions are. A big cleanup stalls the moment a person has to look at a row and decide what it is, and it stalls forty thousand times. So the thing to automate first is the sorting, not the fixing. Put every record into one of three piles, let rules handle the pile that rules can handle, and give humans a short list instead of a long spreadsheet.

That’s a build, and a small one. Here’s what it looks like.

Why the big cleanup always stalls

A messy object is really three different populations wearing one name.

Some records are wrong in a way a rule can fix. The state is spelled out instead of abbreviated. The phone number has four different formats. The industry field says “Mfg,” “Manufacturing” and “manufacturer.” There’s no judgment in any of that. There’s a mapping, and once the mapping exists the fix is mechanical.

Some records are wrong in a way only a person can settle. Two accounts that might be the same company. A contact with no email and a phone number that belongs to a different contact. An opportunity that’s been open for two years under an owner who swears it’s alive.

And some records are simply dead. Leads from a trade show list imported in 2021 that nobody ever called. Contacts at companies that no longer exist. Test records with names like “asdf.”

A spreadsheet treats all three the same way: one row at a time, top to bottom. The easy ones bore the person doing the work and the hard ones stop them. That’s the stall.

Give every record a verdict before changing anything

The first build doesn’t change a single value. It reads every record on the object and writes down what it found.

Concretely: a pass that runs your checks against each record and stamps two things on it, a cleanup status and a reason. The status is one of three values. Fix means a rule can correct it. Review means a person has to decide. Retire means it meets your definition of dead. The reason is the plain-language finding: “state not a two-letter code,” “possible duplicate of another account,” “no activity, no open deals, owner inactive.”

Now the mess has a shape. You can run one report grouped by status and reason and see, for the first time, what you’re dealing with. That report is usually the surprise. The spreadsheet made every row look like a decision. Grouped by reason, a lot of them turn out to be the same decision made once, and the part that needs a human is whatever is left.

The checks themselves are yours to define, and they’re ordinary. Blank where it shouldn’t be blank, which has its own traps in how SOQL treats null values. A value outside the allowed set. A format that doesn’t match. No activity since a date you choose. An owner who is no longer active. Write them as sentences first. If you can’t say a check in a sentence, it isn’t a rule yet.

Let rules fix what rules can fix

The Fix pile is where volume lives, and volume is the part Salesforce handles well when the work happens inside the org instead of in a file.

This is a batch job: it takes the records marked Fix, applies the mapping for each reason, and moves on. Scale is not the constraint. Salesforce’s documentation for batch Apex says a job can work through a query of up to 50 million records, and that the platform chunks them into batches of 200 by default, each one treated as its own transaction. Forty thousand accounts is a rounding error.

The mapping is the real deliverable. “California,” “Calif.” and “CA ” all become “CA.” Phone numbers get one format. The seven spellings of manufacturing collapse to one picklist value. Each mapping is a small table someone in ops can read and argue with, which is exactly what you want. An argument about a table is cheaper than an argument about 3,000 changed records.

I covered the mechanics of a single job like this in bulk updating Salesforce records without Data Loader. The difference here is that the job doesn’t decide what to touch. The verdict pass already did. The job only acts on records marked Fix, for the reason they were marked.

Hand people a queue, not a spreadsheet

The Review pile is where a normal cleanup dies, so it deserves the most design.

Build it as a list view or a simple queue inside Salesforce, one record per decision, with the reason on the row and the two or three possible actions as buttons: keep as is, merge into another record, retire. The person deciding never opens a CSV. They see the record in context, with its related contacts, deals and activity, which is the information they need to decide and the information a spreadsheet throws away.

Split the queue by who actually knows. Possible duplicate accounts go to the account owner. Stale opportunities go to the rep’s manager. Orphaned contacts go to ops. A review list of sixty records that belong to you is a task. A review list of four thousand that belong to nobody is a folder called “cleanup v4.”

Duplicates are often a large slice of this pile, and they have their own logic. If that’s your main problem, start with duplicate management rules that fit your actual policy, and look at where lead conversion creates duplicates at the handoff.

Retire records without deleting them

The instinct with dead records is to delete them. Resist it for one cycle.

Deleting is the one cleanup action that’s hard to take back, and “dead” is a definition you wrote last week. So the Retire pile gets marked, not removed. Flip a retired flag, pull the records out of list views, reports and routing, and reassign anything owned by a departed user to a holding owner. To the people working in the org, the records are gone. To you, they’re one filter away.

Then wait. If a quarter passes and nobody has gone looking for anything in the retired set, you’ve earned the right to delete or archive it, and you can do it in one move with a clear conscience. If someone does come looking in week two, you’ve learned your definition of dead was wrong for a specific slice, and it cost you nothing.

Make every change something you can undo

Here’s the piece that lets a cautious team say yes. Before the fix job changes a field, it writes a row to a change ledger: which record, which field, the old value, the new value, which rule did it, and when.

That ledger is a custom object, nothing exotic. It does two jobs. It’s the receipt, so when a sales manager asks why an account’s industry changed, the answer is a lookup instead of a shrug. And it’s the way back: a rule that turned out to be wrong can be reversed for exactly the records it touched, by reading old values out of the ledger and writing them back.

Salesforce’s field history can do part of this, but only for fields you chose to track before the change happened. A cleanup tends to touch the fields nobody thought to track. A ledger you write yourself covers whatever the job touches, by construction.

Close the door the mess came in through

A cleanup with no follow-through is a subscription. The same records go bad in the same ways, and you’re back in the spreadsheet next year.

The verdict pass already told you where the mess comes from, because the reasons are counted. If “state not a two-letter code” is the top reason, free-text state is your entry point, and a picklist closes it. If “possible duplicate” clusters on records created by one integration, that integration needs a matching step. If “owner inactive” keeps growing, the fix belongs in user offboarding, not in a cleanup.

Then schedule the verdict pass itself. Run weekly, it stops being a cleanup and becomes a gauge: a small number of new Fix records handled automatically, a handful of Review items routed to their owners, and a trend line that tells you whether the org is getting cleaner or dirtier. It’s the same idea as automating pipeline hygiene checks, applied to the records underneath the pipeline.

Why this stays a spreadsheet, and what changes that

Count the builds. Two fields and a pass that fills them in. A batch job with a few mapping tables. A list view with three buttons. A ledger object. None of it is a project, and that’s the reason it doesn’t exist: it’s too much for a spare admin hour and too little for anyone to quote. So the work falls back to a CSV and a loading tool, which need nobody’s approval and never finish.

That gap is what Sentinel is for. It makes your AI the developer of your CRM, so “read every account, mark each one fix, review or retire with a reason, and show me the counts” is something you describe and then watch get built in your own org. It’s a natural early build alongside the others on the list of things to build first with Claude in Salesforce.

Salesforce changes go to a sandbox with tests before production, every action is logged, and a snapshot is taken before each deploy. None of that stops a wrong mapping from being written. It means you can see what changed and get back to where you were, which is what the safety layer is designed to do. Pricing is flat per Sentinel and is covered on a short demo call.

Start with the count

Don’t start by fixing anything. Start with the verdict pass on your worst object and look at the report it produces. The number in the Review pile is the real size of your cleanup. Until you’ve seen it, you’re estimating from the size of a spreadsheet.

Book a Demo Call and bring the object you’ve tried to clean up twice. We’ll talk through what the three piles would look like for it.