CRM normalization and validation rules
Design normalization and validation rules that survive manual entry, bulk import, and integration writes, not just the form your reps use.
On this page
A phone field with five different formats breaks a routing rule that expects one.
A country field with USA, US, United States, and U.S.A. breaks a report
group-by. None of these are duplicate records. They're the same record with
inconsistent values, and inconsistent values quietly break exactly the automations
and reports that assume a field means one thing everywhere it appears.
Normalization makes a value consistent. Validation catches a value that shouldn't be saved at all. Both need a rule that specifies where it runs, because a rule that only fires on manual entry misses every other way data gets into the CRM.
A rule needs a canonical form and an enforcement behavior
For each field family that matters to a report, routing rule, or handoff, define:
| Element | What to specify |
|---|---|
| Canonical form | The one shape the value should take (for example, phone in E.164 with country code) |
| Trigger points | Every path a value can enter through: manual entry, bulk import, integration write, API update |
| Enforcement behavior | What happens when a value doesn't match the canonical form |
Enforcement behavior isn't one thing. Pick deliberately:
- Block: reject the save entirely. Use for values a downstream system can't tolerate at all.
- Block and flag: reject the save and log why, so the person entering it sees the problem immediately.
- Flag only: accept the save, but mark the record for the continuous check to catch. Use where blocking would stop legitimate work more often than it stops bad data.
- Auto-correct and flag: normalize the value automatically (strip formatting, map a known synonym) and log that it happened, so a human can audit the correction later.
The trigger-point gap is where normalization actually fails
A rule scoped to manual entry only looks like it's working, because every record a rep types in by hand comes out clean. The same field keeps breaking anyway, because a nightly integration sync or a one-time list import writes the same inconsistent values right past a rule that only fires on the manual-entry form.
Cover all three paths deliberately:
| Trigger point | Common failure if uncovered |
|---|---|
| Manual entry | Free-text fields without a picklist or format mask |
| Bulk import | A CSV import with no validation step before load |
| Integration write | A nightly sync writing a foreign system's native format straight through |
A validation rule that only fires in the UI and not on the API a bulk import or integration uses isn't a smaller version of the same protection. It's a different, much weaker rule that happens to share a name with the real one.
Values need definitions, not just format masks
A picklist value is not automatically self-explanatory. For every controlled value
in a field that matters, record what it means, what it explicitly doesn't mean, and
what replaces it if it's retired. Enterprise, Mid-Market, and SMB can mean
different segment thresholds on different records if two integrations wrote them
from two different source systems' definitions, even though the label looks
identical in the UI.
GTM Lab: the same fix, twice
GTM Lab, a fictional B2B software company, finds phone numbers, country names, and a job-title picklist each carrying five or more inconsistent formats across its Contact population, breaking a routing rule that expects a normalized country code and a reporting group-by that expects one canonical title bucket.
The first attempt scopes the fix to the manual-entry form only:
| Path | Before the rule | After the rule (manual-entry-only) |
|---|---|---|
| Rep enters a new Contact by hand | (555) 123-4567, 555.123.4567, +1-555-123-4567 all in use | Normalizes to +15551234567 at entry |
| Weekly marketing list import | Same three formats present | Still inconsistent; the import path never touched the rule |
| Nightly integration sync | Same three formats present | Still inconsistent; the integration writes past the UI entirely |
The routing rule keeps failing for exactly the records the team assumed were fixed, because two of the three trigger points were never covered. Rescoping the rule to run at manual entry, at import, and on the integration write (block-and-flag for manual entry, auto-correct-and-flag for the bulk paths where blocking an entire import over one bad phone number isn't worth the disruption) closes the gap. The same fix, applied to all three paths instead of one, is what actually holds.
This is a worked example; the specific canonical formats and enforcement choices are GTM Lab's operating decisions, not a universal standard.
Salesforce and HubSpot: what changes, what doesn't
Salesforce supports validation rules that run on record save, and picklists with additional information for controlled values. Validation rules fire on save regardless of entry path (UI, API, or most integrations going through the standard save path), which covers more of the trigger-point gap than a UI-only format mask would. Confirm your specific integration's write path actually goes through standard validation before assuming coverage; some bulk-load paths can bypass certain validation behavior depending on configuration.
HubSpot supports property validation including format requirements on certain property types, and workflow-based normalization for cases native validation doesn't cover. As with Salesforce, verify which entry paths (manual, import, API, workflow) a given validation actually applies to before treating "the field has a validation rule" as proof every path is covered.
Normalization and validation checklist
- Every field family that feeds a report, routing rule, or handoff has a stated canonical form.
- Coverage is verified across manual entry, bulk import, and integration write, not assumed from one path.
- Enforcement behavior (block, block-and-flag, flag-only, auto-correct-and-flag) is chosen deliberately per field, not defaulted.
- Controlled values have plain-language definitions, not just format masks.
- Auto-corrections are logged so a human can audit what changed.
- The rule set has an owner and shows up in the continuous-check layer of the data quality audit.
Where this connects
Format inconsistency is a different failure class from identity duplication; see duplicate detection, merge review, and survivorship for the latter. Both feed the baseline metrics defined in CRM data quality audit.
Related guides
Get the next guide
New guides and the occasional note on GTM tooling. Don't worry, I won't drop you into a three-month nurture.