CRM data modeling

The single most expensive modeling decision I have watched a team make was turning on Salesforce native duplicate rules set to “Block,” then pointing an enrichment integration at the same objects. The API inserts failed silently against the block, the integration logged success, and the data never landed. Nobody noticed for six weeks. That is the shape of CRM data modeling: the mistakes are invisible until a rollup, a routing rule, or a board slide reads the hole you left, and by then the hole has children.

Data modeling gets treated as plumbing that happens once and never again. It is closer to a foundation you keep pouring on top of. Contact data decays at roughly 30% a year (ZoomInfo), so a model that was clean at launch is a quarter of the way rotten by the next annual plan. Gartner pegs the average cost of poor data quality near $15M a year per organization, and almost none of that shows up as a line item you can point at. It shows up as misrouted leads, a forecast that reconciles to nothing, and a rep who stops trusting the CRM and keeps their real pipeline in a spreadsheet. The model is where you either pay that tax down or let it compound.

The Lead object is a live architectural fork

Salesforce and HubSpot are walking in opposite directions on the oldest question in the model, and both think they are right.

Salesforce keeps Lead separate from Account plus Contact. A lead is an unconverted person floating with no tie to a company until you convert it, which is the exact reason lead-to-account matching exists as a whole tooling category. High-growth teams increasingly run “leads-free,” skipping the Lead object and working contacts against accounts directly, because the float creates more reconciliation than it saves. HubSpot went the other way and added a standalone Leads object in 2024-25, on top of its contacts-associate-to-companies model. Same word, opposite bets. Pick based on how much control you need over the pre-qualification stage, not on which vendor already has your money.

Cardinality governs the rest, and it is worth drawing before you build. One Account has many Contacts. B2B needs many-to-many between Contacts and Opportunities, because a buying committee of six touches one deal and one champion touches three. Salesforce models that with Opportunity Contact Roles. Skip them and you cannot answer “who was in our won deals,” which means multi-threading analysis is off the table before you start. That matters more than it used to: single-threaded deals close far worse than multi-threaded ones, and if the model cannot see the committee, you cannot report on the thing that predicts the win.

Matching and dedup are prerequisites, not cleanup

You cannot model your way out of a matching problem. Without lead-to-account matching, I have watched 15 to 25% of inbound misroute, because the lead never resolves to the account that already exists. Email-domain matching gets you around 70% on its own. Fuzzy company-name matching adds 15 to 20 points on top, and carrying the LinkedIn company URL as a second key lifts the match another 15 to 25 points on the hard cases. The targets worth holding yourself to: match rate 70 to 85%, duplicate rate under 2%, routing accuracy above 95%.

Match method	Coverage	Notes
Email domain only	~70%	Breaks on gmail.com, free-mail, and subsidiaries
Domain + fuzzy company name	85-90%	Adds 15-20 points; handles typos and legal-entity variants
Domain + name + LinkedIn URL	90%+	Second deterministic key rescues the hardest 10-15%

Native CRM dedup cannot do fuzzy lead-to-account matching, which is why the tooling category exists. Standardize Account Domain before you run any backfill or matching pass. If half your accounts store the domain as acme.com and half as www.acme.com or a full URL with a path, your match key is broken and every downstream match inherits the mess. Normalize it once, at write time, and enforce it. Here is the pass I run before trusting any match:

-- Find domains that will silently fail to match: protocol, www, path, trailing junk
SELECT Id, Name, Website, Account_Domain__c
FROM Account
WHERE Account_Domain__c LIKE 'http%'
   OR Account_Domain__c LIKE 'www.%'
   OR Account_Domain__c LIKE '%/%'
   OR Account_Domain__c = null
ORDER BY Name

Duplicate rate is the number that quietly corrupts everything above it. Above 5%, rollups over-count and routing sends two reps at one account. Ungoverned mid-size orgs routinely run 10 to 25%. Treat 5% as the ceiling you actively manage below, not a target you drift toward.

View as table

Stage	Value
Ungoverned org	22%
Typical mid-size	12%
Ceiling to manage under	5%
Well-governed target	2%

The grain rule: where a field is allowed to live

A field belongs on the object whose grain it describes. Company revenue, employee count, and industry describe the company, so they live on Account. Deal amount and close date describe the deal, so Opportunity. Persona and seniority describe the person, so Contact. The test when you are stuck: if this value changes, how many rows should change? If the answer is one, you found the object.

The failure mode is denormalizing for convenience, like stamping account_industry onto every Contact so a report is one field shorter. Now one company’s industry lives in 40 places and they drift apart inside a quarter, because one gets re-enriched and the other 39 do not. Store it once on Account and read it through the relationship. If reporting genuinely needs it on the child, use a live formula or rollup, never a static copy.

Field	Grain	Object	Writer
Employee count	Company	Account	Enrichment integration user
Industry	Company	Account	Enrichment integration user
Amount, Close date	Deal	Opportunity	Rep (user-entered)
Persona, Seniority	Person	Contact	Enrichment or user
Buying-role	Person-on-deal	Contact Role	Rep (user-entered)
ARR (rollup)	Account	Account	Formula / rollup, no writer

The right-hand column is the one teams skip and regret. Every field needs exactly one writer. When enrichment and reps both edit employee_count, you get a tug of war where the last write wins and neither side knows the value is contested. Name the writer, then set field-level security so only that writer can touch it. A validation rule makes the intent enforceable rather than hopeful:

// Employee count is enrichment-owned. Block manual edits by anyone
// except the integration user, so reps can't fight the enrichment tool.
AND(
  ISCHANGED( NumberOfEmployees ),
  $User.Id <> "0053000000INTEGRATIONUSERID",
  NOT( $Permission.Can_Override_Enrichment )
)

1. 1

   1. Identify the grain

   Company, person, deal, or activity. If this value changes, how many rows should change? One row means you found the object.

2. 2

   2. Place it on the matching object

   Put it where the grain lives. Do not duplicate onto children to save a join at report time.

3. 3

   3. Name exactly one writer

   User-entered, enrichment-populated, or formula-derived. One source, never three fields fighting over the same truth.

4. 4

   4. Lock field-level security to that writer

   Only the integration user or the correct profile can write it. FLS is the enforcement, not a suggestion.

5. 5

   5. Expose downstream via rollup, not copy

   If a child needs it for reporting, use a live formula. A static copy diverges from its parent inside a quarter.

6. 6

   6. Document the definition and unit

   Write down what the field means and its refresh cadence so "ARR" resolves to one thing across every team.

7. 7

   7. Constrain at entry

   Required, picklist, or range-checked so free text never enters. The cheapest place to stop bad data is the moment before it exists.

Constrain at entry, or clean forever

Free text is where reporting goes to die. A “Segment” field left open breeds “Enterprise / enterprise / ENT / Ent. / enterprize,” and every one of those is a separate bucket in a report that was supposed to show four segments and now shows nineteen. The fix is upstream and cheap: a picklist with a locked value set, marked restricted so the API cannot invent a new value either.

<!-- Segment as a restricted picklist: the API can't invent a 20th spelling -->
<fields>
  <fullName>Segment__c</fullName>
  <type>Picklist</type>
  <valueSet>
    <restricted>true</restricted>
    <valueSetDefinition>
      <value><fullName>Enterprise</fullName></value>
      <value><fullName>Mid-Market</fullName></value>
      <value><fullName>SMB</fullName></value>
    </valueSetDefinition>
  </valueSet>
</fields>

The same discipline applies to the required-field question, which is where modeling and rep adoption collide. Every field you mark required at creation is a tax on the rep and a bet that the data is knowable at that moment. Mark the wrong ones required and reps type “N/A” and “asdf” to get past the screen, which is worse than a blank because it looks like data. Require what is genuinely known at that stage and validate the rest at the stage where it becomes real. I wrote the longer version of that argument in the required-fields tax; the short version is that constraint is a scalpel, not a hammer.

Where enrichment and signals live

The modern pattern moves the source of truth out of the CRM and into the warehouse, then writes back only the fields that drive an action. The warehouse holds the full, messy, historical picture. Reverse ETL pushes the activation-relevant slice into Salesforce or HubSpot where reps and routing can see it. This keeps the CRM lean and keeps the ugly reconciliation in a place built for it. See warehouse and reverse ETL for the full pattern; the modeling consequence is what matters here.

Writeback is the highest-risk operation in the stack because there is no undo. A sync that fires the wrong field onto 40,000 accounts has already happened by the time you notice. So the model needs a clear contract for what the warehouse owns versus what the CRM owns, and the writeback should touch only warehouse-owned fields. The moment reps and a sync both write the same field, you are back to the tug of war, except now it runs at machine speed and overwrites human judgment every fifteen minutes.

Salesforce vs HubSpot, in practice

	Salesforce	HubSpot
Lead handling	Distinct Lead object; L2A matching required	Contacts associate to companies natively, plus a new Leads object (2024-25)
Native dedup	Rules can Block API inserts and lose data silently; set to Alert	Softer duplicate management; still needs a real matching tool at scale
Customization	Deep: custom objects, heavy validation, restricted picklists	Shallower, faster to stand up, fewer footguns
Best when	Complex multi-stage process, heavy governance	Speed and a unified contact view beat granular control

If you are moving between the two, the model is the migration, not the records. I walked through the object-mapping traps in the HubSpot-to-Salesforce migration; the one-line lesson is that you migrate the data contract first and the rows second, because rows landing against an undefined contract is how you recreate the mess you were trying to leave.

When a matching tool pays for itself

A dedicated matching and dedup tool (LeanData, Insycle, Cloudingo, DemandTools) earns its cost somewhere north of 200 leads a month. Below that, manual merges are cheaper than a subscription and you can eyeball the edge cases. Above it, the misroute rate pays for the tool on its own: at a 15 to 25% miss rate on 200-plus leads, you are losing dozens of inbound touches a month to a lead that never found its account. That is not a data-hygiene nicety. That is pipeline you sourced and then dropped on the floor before a rep ever saw it.

The model is never done, because the data underneath it decays at 30% a year and the business it describes changes shape every quarter. What you can finish is the contract: one writer per field, grain enforced by placement, entry constrained by picklists and validation, and matching that resolves a lead to the account that already exists. Ship those four and the CRM stops being the thing everyone routes around. Start with the field on the board deck, then the match key, then work outward until the report agrees with itself.