Data Standardization for Salesforce: What Journalism Taught Me About CRM Data
Apply editorial standards to your database and watch data quality transform
Before I was a Salesforce architect, I was a journalist. And the thing that prepared me most for data quality work wasn't a certification. It was the AP Stylebook.
In journalism, the Associated Press Stylebook is the bible. It standardizes how you write dates, abbreviate states, format phone numbers, and handle thousands of other details. "March 9, 2026" not "3/9/26." "N.Y." in headlines, "New York" in text. Consistency isn't optional.
When I moved into Salesforce, I found databases that looked like they'd been edited by a hundred different writers with a hundred different style guides. "California" and "CA" and "Calif." in the same field. Phone numbers in every conceivable format. Dates that could mean anything.
The fix wasn't technical. It was editorial. Apply consistent standards to your data, enforce them on entry, and transform the outliers.
Here's the data standardization playbook I've developed over 14 years, built on principles every newspaper copy editor would recognize.
Why Standardization Matters
Unstandardized data creates problems:
Reporting Fails:
A report filtering on State = "California" misses records where State = "CA." Your California pipeline report is wrong.
Automation Breaks:
A Flow checking if Industry = "Healthcare" doesn't trigger for records where Industry = "Health Care" or "Medical."
AI Gets Confused:
AI trying to segment "VP-level contacts in the Northeast" can't work if job titles and regions aren't consistent.
Users Lose Trust:
When search doesn't find records that exist, users stop trusting the system.
Standardization isn't about being picky. It's about making data usable.
The Data Style Guide
Just as publications have style guides, your Salesforce org needs one. Here's how to build it:
Section 1: Geographic Data
State Fields:
Standard: Two-letter postal abbreviations (CA, NY, TX)
Don't Use | Use
California | CA
Calif. | CA
Cal | CA
california | CA
Implementation: Convert State fields from text to picklist with standard abbreviations.
Country Fields:
Standard: ISO 3166-1 alpha-2 codes or full names (pick one, enforce it)
Standard | Acceptable Variations to Convert
US | USA, United States, U.S.A., United States of America
UK | United Kingdom, Great Britain, England (if appropriate)
CA | Canada, CAN
Implementation: Picklist with standard values. Validation rule preventing non-standard entry.
Address Format:
Standard:
• Street addresses: Number, Street Name, Street Suffix (123 Main St)
• Street suffixes: St, Ave, Blvd, Dr, Rd, Ln (abbreviated, no periods)
• Unit numbers: Ste 100, Apt 2B, Unit 5
Implementation: Consider address validation tools (Smarty, Melissa) for real-time standardization.
Section 2: Phone Numbers
Standard Format:
(XXX) XXX-XXXX for US numbers
+1 (XXX) XXX-XXXX for international display
Store as digits only: XXXXXXXXXX
Input | Stored | Displayed
555-123-4567 | 5551234567 | (555) 123-4567
(555) 123-4567 | 5551234567 | (555) 123-4567
555.123.4567 | 5551234567 | (555) 123-4567
5551234567 | 5551234567 | (555) 123-4567
Implementation:
• Validation rule requiring 10 digits (for US)
• Formula field for formatted display
• Flow to strip non-numeric characters on save
Section 3: Names and Titles
Name Capitalization:
Standard: Title Case (John Smith, not JOHN SMITH or john smith)
Exception: Respect unusual capitalization when known (McDonald, DeWitt, van der Berg)
Implementation: Use Flow or Apex to apply title case, with exception list for known variations.
Name Suffixes:
Standard: Jr., Sr., III, IV (with periods for Jr./Sr., no comma before suffix)
Don't Use | Use
Jr | Jr.
junior | Jr.
JR. | Jr.
, Jr. | Jr.
Salutations:
Standard: Mr., Ms., Dr., Prof. (periods, followed by space)
Don't Use | Use
Mr | Mr.
Mister | Mr.
DR. | Dr.
doctor | Dr.
Implementation: Picklist for Salutation field with standard values.
Job Titles:
Standardization is harder here because titles vary legitimately. Focus on:
Title Level Field (picklist):
• C-Suite
• VP
• Director
• Manager
• Individual Contributor
This enables segmentation even when exact titles differ.
Common Title Cleanup:
Input | Standardized
Vice President | VP
V.P. | VP
Vice-President | VP
Vp | VP
Section 4: Company Names
Legal Suffixes:
Standard: Inc., LLC, Corp., Ltd. (with periods, preceded by comma)
Don't Use | Use
Incorporated | Inc.
Inc | Inc.
, INC. | , Inc.
Corporation | Corp.
Common Variations:
Create a mapping table for known companies:
Variation | Standard
IBM | IBM
I.B.M. | IBM
International Business Machines | IBM
MSFT | Microsoft
Microsoft Corporation | Microsoft Corp.
The vs. No The:
Standard: Omit "The" unless it's legally part of the company name.
Don't Use | Use
The Coca-Cola Company | Coca-Cola Company
The New York Times | The New York Times (legal name includes "The")
Section 5: Dates and Times
Date Fields:
Salesforce handles date storage consistently. Focus on:
• Text fields that should be dates (convert them)
• Date entry validation (no future birthdates, no close dates in the past for new opportunities)
Time References in Text Fields:
If capturing times in text fields:
Standard: 9:00 AM, 2:30 PM (colon, space before AM/PM)
Don't Use | Use
9am | 9:00 AM
9:00AM | 9:00 AM
9:00 a.m. | 9:00 AM
0900 | 9:00 AM
Section 6: Email Addresses
Standard:
• Lowercase (john.smith@company.com, not John.Smith@Company.com)
• Trimmed (no leading/trailing spaces)
• Validated format
Implementation:
• Validation rule for format: REGEX(Email, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")
• Flow to lowercase on save: LOWER(Email)
Section 7: Industry and Picklist Values
Industry:
Create a standard taxonomy. Example:
Broad Categories:
• Healthcare
• Financial Services
• Technology
• Manufacturing
• Retail
• Education
• Government
• Nonprofit
Avoid:
• Overlapping categories (Healthcare vs. Medical vs. Health Services)
• Overly specific categories that create fragmentation
• Spelling variations (Financial Services vs. Financial services)
Implementation: Picklist, not text. Map existing variations to standard values.
Implementing Your Style Guide
Phase 1: Document Standards
Write down your standards. A simple document covering:
• Each field requiring standardization
• The standard format
• Common variations to convert
• Implementation approach
Get stakeholder buy-in on standards before implementing.
Phase 2: Prevent New Violations
Implement technical controls for new records:
Validation Rules:
Block entry that doesn't meet standards.
Picklists:
Replace text fields where enumerated values work.
Flows:
Automatically standardize data on save (lowercase emails, format phone numbers).
Phase 3: Clean Historical Data
Use Data Loader or similar tools to mass update existing records:
1. Export records with non-standard values
2. Transform in Excel/Google Sheets using formulas
3. Import updates
For complex transformations, consider tools like:
• Informatica
• OpenRefine
• Custom Python scripts
Phase 4: Monitor Ongoing
Create reports showing:
• Records not matching standards
• Fields with unusual values
• New violations since last cleanup
Review weekly. Catch drift before it becomes a problem.
The Quick Wins
If you can only do three things:
1. Standardize State Fields
Convert to picklist with 2-letter abbreviations. Highest ROI for effort.
2. Enforce Email Formatting
Validation rule + Flow to lowercase. Prevents bounce issues.
3. Clean Phone Numbers
Validation rule for 10 digits. Store raw, display formatted.
These three changes improve data quality dramatically with minimal effort.
The Editorial Mindset
The best data stewards think like editors, not engineers.
Ask "Is this clear?" Would someone unfamiliar with this record understand it?
Ask "Is this consistent?" Does this match how we've formatted similar records?
Ask "Is this correct?" Does this data reflect reality?
These questions, applied at data entry and during review, prevent most quality issues.
Next Steps
1. Pick three fields to standardize first
2. Document standards for those fields
3. Implement validation rules preventing violations
4. Clean existing records using Data Loader
5. Build a report monitoring compliance
6. Expand to additional fields
If you're building a comprehensive data style guide or need help implementing standardization, Clear Concise Consulting offers data quality services. My journalism background informs my approach: treat your data with the same care you'd treat published content.
Jeremy Carmona is a 13x certified Salesforce Architect whose journalism background shapes his approach to data quality. He applies editorial standards to CRM data, treating every field as copy that needs consistent formatting.
