#The challenge
Marketing ops ran a daily dashboard of exception reports in the CRM — saved filters that surface records the marketing-automation-to-CRM plumbing had left in a known-bad state. Four mattered: leads created this year with a blank or off-picklist source, contacts with the same defect, records from one inbound funnel stamped with the wrong tier, and records that responded to a top-tier campaign without the tier ever landing. Every one was being fixed by hand, every day, and had been for years — each successive owner inherited the dashboard in reactive mode and never got runway to kill the cause. The cost was silent: tier-based conversion rates and attribution degraded underneath, and routing sent records to the wrong owner.
#The approach
Separate remediation from diagnosis, explicitly
Every record on an exception report is two things at once: a record to fix, and evidence of an upstream automation gap. We split those jobs. The fix recipes were written down so the daily pass became deterministic instead of investigative, and the recurring patterns in what showed up became the root-cause backlog. The end state was never a faster manual fix; it was an empty report.
Write down the field model, because conflating it is how records get mis-fixed
Source and tier were tracked in three parallel field families: an original/first-touch set, a most-recent/latest-touch set, and a current-value tier field with a ratchet rule — it only ever increases, so it holds the highest tier the record has ever legitimately reached. Those are three different fields with three different semantics, and treating them as one is the most common way a well-intentioned fix makes a record worse.
Diagnose blank-source records by which side has data
Open the record and check which family is populated. Most-recent populated and original blank means the first-touch stamp never fired — the common case, fixed by copying the correct first-touch value into the original-side fields. Neither populated means source was never stamped at all, which almost always means a human created the record. A pre-check on the automation-to-CRM sync comes first, because a sync backlog generates false positives that clear themselves within a minute or two.
Read the 'created by' field correctly — it lies in both directions
A human name in the created-by field does not reliably mean a person typed the record into the CRM. Records created through a data-provider integration carry the human's name rather than the integration user; records built inside the sales-engagement tool push through as that tool even though a human built them. The correct handling differs per case and the distinction is invisible unless someone tells you.
Diagnose the tier-mismatch report as a race condition, not as bad data
A lower-tier self-service form sits upstream of the demo request — you cannot request the demo through that path without completing the form first, so being top-tier on the original side is physically impossible there. What happens is timing: a fast user submits the form, and before the automation platform finishes stamping them at the lower tier, they click through and submit the demo. The higher-tier write lands on the original fields first. The most-recent side is correct; only the original side is wrong. Fix recipe: restore the original tier, strip the demo suffix from the original-side channel and asset fields, leave the entire most-recent side alone, and disqualify test-address submissions and reassign them to the integration user rather than correcting their source.
Find the steps missing from the client's own documentation
The internal runbook these fixes were supposed to follow had gone stale. Two live steps were missing from it entirely — the test-address pre-step and one of the original-side fields that needs its suffix stripped. Both were reconstructed from corrected records and pushed back upstream, and the gap became an input to a documentation-versus-configuration audit.
Automate each report at its root cause
Created-by user roles were aligned to their sourcing values so human-created records stamp correctly. An overnight flow now auto-corrects the tier race condition, because the fix was purely mechanical. A real-time flow corrects the tier chain when the last-response-date and most-recent-campaign-response fields misfire. The structural cause behind the blank-source reports was identified as a set of batch campaigns that stamp source on newly created records: a batch cadence guarantees a window in which records sit unstamped, which is precisely when they surface on the dashboard. The recommendation was to convert those batch campaigns to trigger campaigns and drain the report at the source.
Join to a tier field rather than parsing campaign names
For deriving original, most-recent and current tier, the durable pattern is to join campaign membership against the campaign object's own tier field, not to infer tier from campaign naming conventions. Name-parsing works until someone names a campaign slightly differently, which is always.
Make duplicate merge an explicit, triggered act
The duplicate cleanup was designed as a checkbox field on the lead and contact objects that triggers the routing tool's match-then-merge path, with the oldest record as master so the original source values survive the merge. Merging is deliberate and record-scoped rather than a bulk operation nobody can unwind.
Kill work the roadmap is about to make irrelevant
A substantial source-picklist cleanup was deliberately closed rather than finished, once it became clear the marketing team was moving from first-and-last-touch to multi-touch attribution — which would redefine what a 'correct' source value even means. Finishing it would have been rework with a known expiry date.
Write the handover before you leave
The daily cadence, all four fix recipes, the field-model traps, the timing false positives, the closed root causes and the explicitly unconfirmed ones were written into a single handover document for the incoming architect and the client's incoming ops hire — including the items we could not verify, flagged as unverified.
#Outcomes
All four exception reports moved from hand-fixed to automated
Overnight and real-time flows shipped for each. The operating rule handed over with them: a report that stays non-empty for more than roughly 24 to 48 hours is a regressed flow, not a record to fix by hand — which converts a daily chore into a monitoring signal.
A documented, testable field model
Original versus most-recent versus current tier written down with the ratchet rule and the created-by exceptions, so the next ops hire does not rediscover them by breaking records.
The upstream causes named, not just mopped up
The batch-versus-trigger stamping design and the front-end multi-click race were both identified as the real sources. The front-end fix sat with the client's external web vendor and was logged at handover as unconfirmed rather than claimed as closed.
One cleanup deliberately retired
The source-picklist cleanup was closed by joint decision as more work than it was worth given the coming attribution-model change.