Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

CRM & Marketing Automation › Automation Workflows

Standardising CRM Field Values With Workflows

How to standardise CRM data with workflows: country values, job titles mapped to roles, company names, rules that avoid loops, review queues and backfills.
Tutorialintermediate9 min readUpdated

Why values drift

The CRM fundamentals guide makes the case for picklists 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.

FieldHow it driftsWhat it breaks
CountryFull names, codes, local spellings, abbreviations and typos from different sourcesRouting by territory; consent rules applied by market; reports by region
Job titleEndless variation in wording, seniority and languageFit points in scoring; buyer-role segments; persona reporting
Company nameLegal suffixes, abbreviations, trading names, capitalisationAccount matching and Deduplication; account-based lists
Industry or sectorFree text where a picklist was intended; different lists per sourceFit scoring; ideal customer profile reporting
State, county or regionMixed with country; abbreviated differentlyTerritory routing below country level
Lead source from lead formsValues set by each platform's own vocabularyReports 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

  1. 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.

  2. 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 Segmentation 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.

  3. 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.

  4. 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.

  5. 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 centre. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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)

Illustrative example — not a real result

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.

Frequently asked questions

Why keep the raw value once a clean one exists?

Because the clean value is an interpretation and the raw value is what the person or the source actually said. Keeping both lets anyone see why a record was mapped the way it was, lets a wrong mapping be corrected and rerun without asking the person again, and lets the workflow trigger on changes to the raw field without reacting to its own writes. Accuracy is one of the GDPR's principles, and a workflow that replaces what someone typed with its own guess makes accuracy harder to show, not easier.

Should job titles be standardised into a picklist?

Not the title itself. Titles are too varied to fit a list, and people expect to see the title they gave. Keep the title as typed and map it to a short list of roles or buyer types that routing, scoring and segmentation actually use, such as decision-maker, user and influencer, or the functions the business sells to. The role field is the picklist; the title stays free text beside it.

How do you stop a standardising workflow from running in a loop?

Trigger it only on a change to the raw field and have it write only to the clean field, so its own action can never be the event that starts it again. Add a condition that skips records where a person has set the clean value by hand, and record which values the workflow wrote so they can be told apart from manual ones. Then test with seed records before switching it on, as the workflows hub describes.

Terms used on this page

Frameworks

Where this page is used

Also related

Sources

  1. Regulation (EU) 2016/679 (GDPR) — EUR-Lex

Related