A lead-source schema that survives the distributor handoff

Most CRM implementations have one field called something like Lead Source, and it is asked to answer two questions at once: where did this demand come from, and who sold it. In a direct business those are the same answer. In a channel business they are not, and the field silently records whichever one was written last.

That is usually the order. Which is why marketing-sourced pipeline in device and DME companies has a habit of shrinking as the channel grows.

Why does one field not work here?

Because the number of parties between a manufacturer and a patient is far larger than a single field can describe. In Medicare fee-for-service data, the busiest single durable medical equipment product code involves 39872 distinct suppliers and 207950 distinct referring providers. Orthotic devices reach 6225 suppliers against 62303 referrers.

Those are counts for one product code, not a whole category. A manufacturer whose product touches even a fraction of that structure has thousands of possible transaction parties and hundreds of thousands of possible clinical referrers, and a single Lead Source picklist has to represent all of it. It cannot, so it collapses to whichever value the last process to touch the record wrote.

The schema

Five fields. Each answers exactly one question, and no field is ever written twice by different processes.

Field	Written by	Written when	Mutable
demand_id	Web/form/event capture	First identified touch	Never
demand_channel	Web/form/event capture	First identified touch	Never
demand_actor	Web/form/event capture	First identified touch	Never
transaction_partner	Order process	At order creation	Once
attribution_state	Nightly job	Derived, recomputed	Derived

The values are deliberately few. A picklist that needs a training session is a picklist that will be filled in wrong.

demand_id            immutable id, minted at first identified touch
                     format: dmd_<yyyymm>_<8 random chars>

demand_channel       how the demand was created — pick exactly one
                     paid_search | paid_social | organic_search | direct
                     email_outbound | event | webinar | referral_clinical
                     referral_partner | pr_earned | unknown

demand_actor         who expressed the interest — pick exactly one
                     end_user            the patient or their carer
                     clinician_referrer  prescribes or recommends, does not buy
                     provider_org        clinic, hospital or IDN buying directly
                     distributor_rep     channel partner acting for a buyer
                     gpo_or_payer        contracting entity, not the user

transaction_partner  the entity that books the order
                     null until an order exists; partner id or "direct"
                     written exactly once, by the order process only

attribution_state    derived nightly, never hand-edited
                     direct              demand and transaction are the same party
                     partner_fulfilled   we created the demand, partner booked it
                     partner_originated  partner created and booked it
                     unresolved          demand record missing or unjoinable

How does it work?

The load-bearing idea is that demand_id is minted once and never touched again. Every later process joins to it rather than overwriting it. The order process gets exactly one field of its own, transaction_partner, and no permission to modify anything upstream.

demand_channel and demand_actor are separate on purpose, and separating them is the change that does most of the work. A clinician who recommends a device and a distributor rep who orders it are both plausible values for a single Lead Source field, and they mean completely different things. One is demand creation. The other is fulfilment. Recording them in one place makes them indistinguishable forever.

attribution_state is derived, never entered. It is the answer to the question the business actually asks — did marketing create this revenue — and deriving it means it is recomputed correctly when late-arriving data changes the picture. The partner_fulfilled value is the one that matters most, because it is the population that a single Lead Source field destroys: demand the manufacturer created, booked through someone else.

Reporting then becomes a question about attribution_state rather than an argument about definitions. Marketing-sourced pipeline is direct plus partner_fulfilled. Partner-sourced is partner_originated. The unresolved count is a data-quality metric, and watching it is how a team finds out the capture is degrading before the quarterly number does.

What does it replace, and how do you migrate?

An existing Lead Source field usually contains a mixture of all five things this schema separates. Migrating means reading the current picklist and deciding, value by value, which of the new fields each one was really describing. That exercise is short and unusually clarifying, because most teams discover their picklist has three or four values that mean the same thing and one that means four different things depending on who selected it.

The mapping is mechanical once the distinction is clear. Values naming a marketing activity — a campaign, a trade show, a webinar — were describing demand_channel. Values naming a kind of person or organisation — clinician, distributor, hospital — were describing demand_actor. Values naming a specific partner were describing transaction_partner. Values like “referral” were ambiguous between all three and have to be split by inspection or retired.

Keep the old field, stop writing to it, and leave it read-only. Deleting it destroys the only record of how historic deals were classified, and the year in which both models run side by side is the year anyone can check whether the new one behaves sensibly. Renaming it to something with legacy in the name is usually enough to stop people populating it out of habit.

One decision is worth making early rather than by default: whether demand_channel records first touch or last touch. This schema assumes first, because the question it exists to answer is who created the demand. A team that wants last-touch reporting should add a separate field rather than redefining this one, since a field that changes meaning halfway through a year is worse than either choice.

How do you know it is working?

Four checks, run monthly, each of which fails loudly rather than quietly.

The first is the unresolved rate. It will never be zero, and its level matters less than its direction. A rate that climbs month over month means capture is degrading somewhere upstream — a form that stopped passing the key, a new landing page that was never instrumented, a partner portal that was rebuilt.

The second is a reconciliation. Total pipeline across all four attribution_state values must equal total pipeline in the finance system. If those two numbers disagree, the join is dropping records, and no amount of reporting sophistication on top of a lossy join produces a trustworthy answer.

The third is a mutation audit: count records where demand_id changed after creation. The correct answer is zero. Any other answer means a process has write access it should not have, and finding that in a monthly check is much cheaper than finding it during a board review.

The fourth is a distribution check on demand_actor. If one value accounts for almost everything, the field is not being captured — it is being defaulted. That usually traces to a form that sets a hidden value rather than asking, which is a five-minute fix that nobody notices for two quarters.

Where does it break?

Four places, all worth knowing before adoption rather than after.

Demand with no digital touch. A clinician who recommends a device in conversation generates real demand and no demand_id. Those records land in unresolved and always will. The schema does not solve offline demand; it makes the size of it visible, which is more than most implementations manage.

Partners who do not report. transaction_partner depends on the order process knowing which partner booked the order. Where a distributor reports aggregated sell-through with no order-level detail, the field cannot be populated honestly and should be left null rather than guessed at.

Retroactive application. The schema only describes deals that closed after it existed. Backfilling demand_id onto historic records means inventing data, so the correct move is to run the old and new models side by side for a year and report both, rather than restating history.

Picklist drift. Every value added to demand_channel after launch reduces the comparability of the series. Adding values is a governance decision, not a ticket, and the smallest sustainable list is the one that survives.

There is also a failure mode that is organisational rather than technical, and it is the one that kills most implementations. The schema takes write access away from the order process, which in practice means telling sales operations that a system they own may no longer change a field they have always been able to change. That conversation goes better when the reason is framed as protecting a number sales also gets judged on, and badly when it is framed as a marketing requirement. The technical work here is a few days. The agreement is the project.

What is the smallest first step?

Add demand_id alone, and make it immutable. Nothing else in this schema delivers value without it, and it delivers some value on its own — even with the other four fields absent, an immutable demand key means the demand history stops being destroyed at the moment of the close.

The sequence that works is deliberately slow. Mint demand_id at every identified touch and write it nowhere else. Then add demand_channel and demand_actor at capture, since both are known at that moment and neither depends on the order process. Then give the order process transaction_partner and revoke its write access to everything upstream, which is usually the politically hardest step and the one that makes the rest hold. Finally add the nightly job that derives attribution_state.

A team that gets through the first two steps before a fiscal year starts will have a defensible marketing-sourced number twelve months later. A team that tries to do all four at once generally ships none of them, because the fourth requires agreement from sales operations that the third has not yet earned.

The reason to do any of it is narrow and worth stating plainly. In a channel business the question is not whether marketing created demand. It is whether the company can still prove it after the order is written. One immutable field is most of that proof.