Revenue Data Modelling: Schemas That Survive Scale

Why the model outlives everything else

Tools get replaced every few years. Processes change every few quarters. The data model, once real data has accumulated against it, is close to permanent — because changing it means migrating history, and migrating history is where companies discover what they failed to record.

That asymmetry is the argument for spending disproportionate time on the model early. A mediocre tool on a good model is recoverable. A good tool on a bad model produces confident, well-presented numbers that are wrong, and no amount of dashboard work fixes it.

The core entities

Two entities are routinely missing. Subscription is absent in most CRM implementations, which is why so many companies cannot calculate retention without a spreadsheet. Signal is absent almost everywhere, and its absence is what makes it impossible to answer which signals actually preceded revenue.

Events, not overwrites

This is the single decision that cannot be retrofitted, so it deserves its own section.

When a subscription's value changes from $40,000 to $55,000, most systems update the field. The old value is gone. You now cannot say what that account was worth twelve months ago, cannot calculate net revenue retention for its cohort, and cannot reconstruct when the change happened or why.

The alternative is to write a change record — a row saying that on this date, this subscription moved from this value to that value, for this reason. The current value is derived from the latest event rather than stored destructively.

Most CRMs record stage history natively and record almost nothing else as events. The subscription and owner rows usually need deliberate design, and they are exactly the two that finance and leadership ask about.

Identity resolution

The same company arrives as acme.com, Acme Corporation, Acme Corp, and ACME Inc. The same person arrives with a work email, a personal email, and a LinkedIn URL. Deciding how these resolve is a modelling decision, not a data cleanup task, and it needs to be made once and documented.

1. 01
   Pick a canonical identifier per entity

   Root domain for accounts, and a normalised email or a provider person ID for contacts. Everything else is an alias that maps to it.

2. 02
   Store aliases explicitly

   Keep every known variant against the canonical record. This is what lets an inbound lead from a subsidiary domain resolve to the right parent account rather than creating a duplicate.

3. 03
   Document a precedence order

   When two sources disagree, which wins? Human-verified beats high-confidence enrichment beats inference. Write it down and enforce it in the pipeline rather than deciding case by case.

4. 04
   Handle corporate hierarchy deliberately

   Decide whether subsidiaries are separate accounts with a parent link or a single account. Enterprise motions usually need the hierarchy; SMB motions almost never do, and adding it unnecessarily creates reporting ambiguity.

Modelling signals properly

The common shortcut is to store signals as fields on the account: last_intent_date, hiring_signal, funding_flag. It works until you want to answer the only question that matters — which signals actually preceded revenue.

Signals should be their own entity: one row per observation, with type, source, timestamp, strength, and a reference to the account. Many rows per account, never overwritten. That structure makes it possible to join signals to closed-won outcomes and weight them by observed conversion rather than by intuition, which is the whole point of tracking buying signals at all.

The four mistakes that force a rebuild

• Contact-first modelling in B2B. Every campaign compounds the duplicate problem, and unwinding it after a year means reconciling activity history across thousands of merged records.
• No subscription entity. Retention reporting becomes a permanent spreadsheet exercise, and the numbers cannot be audited.
• Overwriting instead of recording events. Unrecoverable. The history is simply gone, and no tooling can reconstruct it.
• Modelling everything you can capture. A schema with 200 fields nobody uses is harder to work with than one with 30 that matter. Model what you decide on.

A practical test

Take four questions to your current model and see whether it can answer them without a manual reconstruction:

• What was this account worth twelve months ago?
• Which signals preceded our last twenty closed-won deals?
• How long did deals spend in stage three last quarter, by segment?
• Who owned this account when the opportunity was created?

Each unanswerable question maps to a specific modelling gap: no subscription events, no signal entity, no stage history, no owner history. Fixing them is cheap now and expensive later, which is the entire argument — and it is the same reasoning behind building scalable revenue systems.