Why values drift
The CRM fundamentals guide makes the case for picklistsA CRM field type that accepts one value from a defined list of permitted values, so that records can be reported on instead of typed in many spellings. over free text, because free text cannot be reported on. Picklists solve the problem at the forms and screens the business controls. They do not solve it everywhere else: an ad platform lead form passes whatever the person typed, an event list arrives with its own conventions, an integration writes a value from a system with different options, and a salesperson creating a record by hand types what comes to mind. Each entry point is reasonable on its own; together they produce a field with many spellings of the same few things.
| Field | How it drifts | What it breaks |
|---|---|---|
| Country | Full names, codes, local spellings, abbreviations and typos from different sources | RoutingRules that assign each new lead to an owner, queue or next step based on territory, segment, score or source. by territory; consent rules applied by market; reports by region |
| Job title | Endless variation in wording, seniority and language | Fit points in scoringAssigning points to leads for fit and behaviour to decide which are ready for sales.; buyer-role segments; persona reporting |
| Company name | Legal suffixes, abbreviations, trading names, capitalisation | Account matching and DeduplicationRemoving double counts when one conversion is reported by more than one source.; account-based lists |
| Industry or sector | Free text where a picklist was intended; different lists per source | Fit scoring; ideal customer profile reporting |
| State, county or region | Mixed with country; abbreviated differently | Territory routing below country level |
| Lead source from lead forms | Values set by each platform's own vocabulary | Reports by source; the join to spend |
The damage is quiet. A lead from a country spelled an unexpected way falls through to a fallback owner or to nobody. A director with an unusual title never earns the fit points the scoring model meant for directors. Monthly reports show a long tail of values that each hold a few records, and the total for the main value looks smaller than it is. The data quality guide describes the same failure in event data, where campaign names fragment across spellings, and the cure is the same: fix it where it enters. When a routing or scoring workflow has quietly stopped matching because of a stray spelling, the diagnosis for a CRM workflow that is not running starts from the missed record and works back to the value.
Before you start
You need three things. Access to build workflows in the CRM or the marketing platform, with permission to create fields and to see the workflow history. An export of the current values of each field you plan to standardise, with a count of records per value, because the mapping tables are built from what is already there rather than from a list made up in advance. And an entry in the automation inventory for each workflow you will build, as the automation governance guide requires, with an owner and a change log. Read the workflows hub first if trigger, condition, action and exit are not yet familiar; this tutorial uses that anatomy throughout.
Agree one principle before building anything. The GDPR sets accuracy as one of its principles, as the GDPR glossary entry states, and people have a right to have their data corrected. Standardising serves accuracy only if it never replaces what a person said with a guess. So the clean value is always written to its own field beside the raw one, and a correction made by a person, whether the contact themselves or a colleague, is never overwritten by a workflow. Every step below follows from that.
The steps
Pick the fields that routing, scoring and reports depend on
List every field that a routing rule, a scoring rule, a segment or a regular report reads. Those are the fields where a stray value has a cost; the rest can wait. For each, note how many distinct values the export shows and how many records sit outside the main values. Place each field on the impact and effort matrix: impact is what breaks when the value is wrong, effort is how varied the values are. Country is usually high impact and moderate effort; job title is high impact and high effort; a rarely used sector field may not be worth a workflow at all. Start with one field and finish it before starting the next.
Write the standard list for each
For each chosen field, write the list of permitted values the business will report and route on. For country, a recognised list of names or codes, applied the same way everywhere. Where market and language both matter, keep them as two fields, as the guide to email across countries and languages explains, rather than letting country decide both. For job title, a short list of roles or buyer types that scoring and SegmentationDividing a market or customer list into groups that need different offers or messages. actually use. For sector and company size, the categories the tutorial on building an ideal customer profile defines, so that fit can be checked the same way for every account. Include an explicit "unknown" value, so a blank and a value that could not be placed are not confused.
Build mapping tables from the values already in the CRM
Take the export of current values and map each one to the standard list in a table with two columns: the raw value and the standard value. Start with the values that hold the most records, then work down the tail. For titles, map patterns as well as exact values where the rule is clear, and record each pattern in the table so it can be reviewed. Leave genuinely ambiguous values unmapped rather than guessing; they go to the review queue. Keep the table outside the workflow, in the same place as the field documentation, so it can be read and changed without editing the workflow itself.
Standardise at the form before the workflow
Every value fixed at entry is one the workflow never has to handle. Replace free-text fields with picklists on the forms you control, using the same standard list. Check the field mapping of every integration and lead form that writes into the field; the diagnosis of LinkedIn leads not syncing to the CRM shows how a picklist value the CRM does not recognise can stop leads arriving at all, so a mismatch can cost more than a messy report. Where a source cannot send a picklist value, its raw value goes to the raw field and the workflow takes over.
Trigger on create and on change of the raw field
Build the workflow so it runs when a record is created and when the raw field changes, and at no other time. That covers new records from every source and edits made later, such as a person updating their country in a preference centreA page where a subscriber chooses which messages, topics and channels they receive and updates their own details, instead of an all-or-nothing unsubscribe.. Do not trigger on changes to the clean field or on any update to the record, because the workflow's own write would then start it again. Name it by the convention in the governance guide and add its row to the inventory before switching it on.
Keep the raw value and write the clean one to its own field
The workflow reads the raw field, looks the value up in the mapping table and writes the standard value to a separate clean field. It never edits the raw field. Routing, scoring, segments and reports then read the clean field, while the raw value stays visible on the record for anyone checking why it was mapped that way. Record that the workflow set the value, with a date, either in a small companion field or in the record's history, so a workflow value can be told apart from one a person chose.
Send unmatched values to a review queue
When the raw value is not in the mapping table, the workflow sets the clean field to "unknown" and adds the record to a review queue: a list, a view or a task assigned to the field's owner. It does not guess. For routing, decide what an unknown value does in the meantime, usually a fallback owner who sees the queue, so a lead is never stuck because its country was spelled in a new way.
Guard against loops and overwrites
Check three guards before activation. The workflow triggers only on the raw field and writes only the clean field, so it cannot trigger itself. A condition skips records where a person has set the clean value by hand, so a correction is never undone. And no other workflow or integration writes the clean field; if one must, it becomes the field's owner and this workflow stops writing it. Then run seed records through every path (a mapped value, an unmapped value, a later change, a manual correction) and read the results, as the workflows hub describes for any workflow.
Backfill existing records in batches
New records are handled from activation onwards; existing ones need a backfill. Run it in batches rather than all at once, starting with a small batch you check by hand, then larger ones. Pause any workflow that triggers on the clean field while the backfill runs, or exclude backfilled records from it, so that a corrected country does not re-enrol thousands of people into a regional welcome sequence. Log each batch in the change log with its date and size, and keep the export taken beforehand so any batch can be undone.
Review the queue and extend the mappings
On a schedule, the field's owner reads the review queue, adds new raw values to the mapping table and sets the records' clean values. Re-run the lookup for records still marked unknown once their raw value has a mapping. Add the share of unknown values for each field to the monthly field completeness report the data operations and taxonomies guide describes, and look at which source created most of them, because a source that keeps producing new spellings is better fixed at the form than in the table.
A coffee wholesaler cleans countries and job titles (illustrative)
Yompetra Coffee Wholesale standardises country and buyer role
Yompetra Coffee Wholesale, an invented supplier of green and roasted coffee to cafés, roasters and hotel groups, sells to trade buyers in several countries and routes each lead to a regional account manager by country. An export shows 9,400 contacts with 63 different country values for what are in fact 11 countries, and 1,150 contacts whose value matched no routing rule and went to a shared inbox. Job titles are worse: 2,800 distinct titles. The team writes a standard country list and a buyer-role list of five values (owner, head buyer, roaster, operations and other), builds a mapping table from the export and maps 59 of the country values and the 400 most common titles, which cover 7,300 contacts. The web form switches to a country picklist. The workflow triggers on the raw fields only, writes clean country and buyer role to their own fields and sends anything unmatched to a review queue owned by the sales coordinator. The backfill runs in batches of 1,000 with the regional welcome sequence paused. In the first month the queue receives 85 new country spellings and titles, almost all from one trade-show list import, and the shared-inbox leads fall to a handful a week.
Common mistakes
- Overwriting the raw value. The record loses what the person said and a wrong mapping cannot be corrected. Write the clean value to its own field.
- Triggering on any change to the record. The workflow's own write sets it off again. Trigger on the raw field only.
- Overwriting manual corrections. A value a person fixed by hand must stay fixed; skip records with a manual clean value.
- Guessing unmatched values. A wrong guess routes a lead to the wrong person with confidence. Send it to the review queue.
- Building the standard list from imagination. Map the values already in the CRM, most common first.
- One country field doing two jobs. Market and language are separate fields.
- Backfilling everything at once into live workflows. Thousands of records enrol in sequences they were never meant for. Batch it and pause what triggers on the clean field.
- No owner for the queue. Unknown values accumulate and routing quietly falls back to the default.
