Open the lead source field in any CRM that has been running for a few years and you will find an archaeology dig. Forty spellings, one meaning, and a report that treats them as forty different channels.
- Website
- web
- Web Form
- www
- online
This is not a discipline problem. It is what free text does when it meets a busy Tuesday. The fix is a one-time cleanup followed by a structural change so it cannot happen again.
Step 1: export the distinct values
Pull every distinct value currently in the field, with a count of records per value. In HubSpot this is a simple property export; in Salesforce, a report grouped by the field. Sort by count descending. In most CRMs the top ten values cover the vast majority of records, which makes the next step less painful than it looks.
Step 2: build the normalization mapping table
For each messy value, decide which value in your new taxonomy it maps to. Most map obviously (“adwords” is Paid search). The judgement calls are the vague ones: “Website” and “online” usually have to map to Direct, because whatever the real source was, it was never recorded. Accept that loss once rather than pretending the history is better than it is.
Work one raw value per row, not one group per row. Grouping hides the judgement calls, and the judgement calls are the whole job.
| Raw value | Category |
|---|---|
| Website | Direct |
| web | Direct |
| Web Form | Direct |
| www | Direct |
| online | Direct |
| google ads | Paid search |
| adwords | Paid search |
| ppc | Paid search |
| sem | Paid search |
| gclid present on the record | Paid search |
| linkedin ad | Paid social |
| Li DM | Organic social |
| Paid social if it was an ad, Organic social if it was not | |
| social | Paid social if it was an ad, Organic social if it was not |
| Trade show | Trade show or conference |
| expo | Trade show or conference |
| Cold call | Cold call |
| SDR | Cold email or Cold call, whichever the sequence actually was |
| prospecting | Cold email or Cold call, whichever the sequence actually was |
| Referral | Referral partner if a company sent it, Customer referral if a customer did |
| word of mouth | Referral partner if a company sent it, Customer referral if a customer did |
| WOM | Referral partner if a company sent it, Customer referral if a customer did |
Rows with two options are not a gap in the table. They are values that genuinely cannot be resolved from the string alone, and a silent wrong mapping is worse than a visible decision.
To do this pass in bulk, paste your distinct values into the free lead source normalizer. It maps what it can match, flags the rest for you to decide, and exports the crosswalk as a CSV.
If you have not built the new taxonomy yet, start with the reference taxonomy in the lead source taxonomy guide, or generate one with the taxonomy builder.
Step 3: apply the mapping to historical records
Run the mapping as a bulk update, oldest records first, in batches.
Do not delete the old values and start fresh: the day you do, every trend report in the business breaks, and the first quarterly review after launch becomes an argument about why the numbers moved.
Step 4: convert the field to a picklist
Only after the history is mapped. Free text is how the field got dirty; a controlled picklist is how it stays clean. Retire “Other” if you possibly can, because within a quarter it will quietly become your largest category.
Step 5: automate the capture
The cleanup holds only if new records arrive clean. Anything with a digital trail should be classified without a human typing: UTM parameters map to channel, a gclid means Paid search, a form submission carries its source with it. Capturing the real source at the moment of submission and writing it into the CRM in your agreed values is exactly the job lead source tracking exists to do. Reserve manual entry for the sources that genuinely have no digital trail, like a referral mentioned on a phone call, and give those a picklist too.
- UTM parameters → channel
- gclid → Paid search
- Form submission → source attached
The maintenance habit
Ten minutes a quarter
Once a quarter, export the distinct values again and scan for drift. New channels appear; when they do, add them to the taxonomy deliberately rather than letting whoever saw the first lead invent a name. Ten minutes a quarter keeps the field trustworthy for good.