Summary: This article explains why UXRs need to know how to clean operational data from CRMs, and how to make that data usable for study participant recruiting, sampling, screening, and outreach. I’ve also included 14 Cleaning Formula Templates that you can copy/paste, and adapt in your own spreadsheets.
As UX researchers, we almost always need data that lives in systems we do not own. 🎵Tale as old as time.🎵 Unfortunately, many CRMs are structured from the sales team’s point of view, which is not always useful when we are building study participant lists. Endless fields for accounts, territories, renewal dates, account owners, and deal stages may work well for sales because they are often managing relationships at the account level. But us researchers need a different structure. Depending on the study, a useful dataset might need one row per person, one row per survey response, one row per support issue, or one row per task, session, or behavior. The data can be accurate in the source system and still require cleaning before it can support the research work.
This is a big reason basic data cleaning skills are critical for being a pro UX researcher.
We need to know how to reshape operational data so it can support recruiting, sampling, screening, outreach, analysis, and responsible participant management.
When the structure of the data does not match the research need, you want the confidence to clean it yourself instead of waiting for someone else to find the time to do it for you. Lucky for us, it is not that hard. so, that is what this week’s article is all about.
What’s in Your Rows?
One of the first cleaning decisions is deciding what one row should represent. This sounds basic, but it affects almost every cleaning choice that follows. Before removing duplicates, splitting cells, standardizing categories, or fixing missing values, the research team needs to understand the unit of the dataset.
In business data, one row can mean many different things. It might represent one company, one account, one household, one support ticket, one purchase, one product event, one survey response, or one person. None of those structures is automatically wrong. The right structure depends on the research purpose.
For us UX research recruiting, one row usually needs to represent one individual person. That person may need to be invited, screened, scheduled, tracked, thanked, and excluded from future outreach for a period of time.
If the row represents a company instead of a person, those recruiting tasks become harder. The team may know that a company is a good fit, but it may not know which individual contact is appropriate for the study.
This issue comes up often in B2B2C orgs. A CRM export may have one row per customer company, with several contacts stored inside that same row. That can make sense for sales because the account is the main unit of work. It is less useful for UX research when the team needs to contact individual people for study participation.
For example, a CRM export might look like this:
That row contains useful info, but it is not yet structured for recruiting. Maria, James, and Priya are three different people. They may have different roles, different product experiences, and different levels of fit for a study. If they remain bundled inside one company row, the research team cannot easily track outreach, screening status, participation history, or eligibility at the person level.
A research-ready version would look more like this:
See the difference? This change is small in the spreadsheet, but meaningful for the research workflow. The company-level info still matters, so it stays attached to each person. The difference is that the cleaned file now supports the way the research team needs to work. Each row can be treated as a possible participant rather than as part of a bundled account record.
Keep It Traceable
Once the team knows what the cleaned data needs to become, the next step is to keep the cleaning work traceable. An important best practice that I see soooooo many UX researchers dismiss is keeping the original export untouched. The cleaning should happen in a separate workspace, and the final cleaned version should be easy to compare back to the source data.
In your spreadsheet, you might use tabs like this:
Raw Exportfor the untouched source data.Cleaning Workspacefor the active cleanup work.Cleaned Datafor the version that is ready for recruiting or analysis.Review Neededfor records that require human judgment.Removed Recordsfor records that were excluded.Duplicate Recordsfor records that may appear more than once.Cleaning Notesfor decisions, rules, and changes made during the cleanup process.
This structure matters because cleaning involves judgment. You’ll need to decide whether two rows are duplicates, whether a category should be standardized, whether a contact should be excluded, whether a name and email address appear to belong together, stuff like that. Those decisions should not disappear into one final spreadsheet.
In our spreadsheets, other researchers and stakeholders may need to understand what changed and why, and so will you when you revisit your own work!
Traceability is especially important when UX is using data from a system owned by another department. If a stakeholder asks where a contact came from, the team should be able to point back to the original CRM row. If an account owner questions why a person was included or excluded, the team should be able to explain the cleaning decision. If the same source data needs to be refreshed later, the team should be able to repeat the process without starting over.
A few simple fields can make this easier:
Original Row IDconnects each cleaned record back to the source export.Cleaning Notesexplains unusual decisions or changes.Review Neededseparates records that are ready to use from records that require human judgment.
These fields may feel administrative, but they make the cleaned data easier to trust.
The goal is to avoid creating a file that only one person understands. Cleaned research data should be usable by the person who made it, but it should also be understandable to another researcher who opens it later. That is part of making data cleaning a research practice rather than a private spreadsheet task.
Make Each Field Usable
Once the overall structure of the dataset is defined, the next step is making sure each field contains the type of info it is supposed to contain. This sounds obvious, but many business exports contain fields that have gradually accumulated formatting inconsistencies over years of use. Those inconsistencies may not bother the team that owns the system, but they often create unnecessary work for researchers trying to filter, sort, join, or segment the data.
Many of these problems are small on their own. Together, they make the spreadsheet harder to work with and increase the chance of mistakes during recruiting or analysis. Before doing anything more advanced, it helps to standardize the basic building blocks of the dataset.
Common cleanup tasks include:
Removing extra spaces before or after values.
Standardizing capitalization where appropriate.
Converting email addresses to lowercase.
Fixing inconsistent date formats.
Removing hidden line breaks or non-printable characters.
Splitting fields that contain several pieces of information into separate fields.
Making sure every column contains one type of information.
For example, an email column should contain email addresses. A segment column should contain one segment value. A date column should contain dates that Excel recognizes as dates. If a field contains several different kinds of info, it becomes much harder to filter the data, compare records, or identify patterns.
This is also a good point to standardize delimiters before doing more advanced cleaning. One export may separate multiple values with commas, another with semicolons, and another with line breaks. Choosing one consistent delimiter early makes the remaining cleaning work much simpler.
The goal here is to make each field predictable. When every column behaves consistently, the rest of the cleaning process becomes easier and less error-prone.
Expanding Rows
As discussed earlier, one of the more common problems in CRM exports is finding several related values stored inside the same row. Let’s use the same example as before to illustrate this point.
Suppose a CRM export contains this info:
The research team will eventually needs three rows instead of one, like this:
The important part of this transformation is preserving the relationships between the values. Maria’s email should stay with Maria. James’s email should stay with James. The company, segment, and any other account-level info should remain attached to all three contacts because that info still applies to each person. This is where I see many cleaning mistakes happen. Don’t worry, I mess this stuff up all the time too! It is easy to split one column and accidentally lose the relationship to another column. If the names are separated first and the emails are separated independently, there is no guarantee they will still align correctly. Once that relationship is lost, it often requires manual work to reconstruct it.
When I perform this type of cleaning, I usually think about whether the values belong together before deciding how to split them apart. If two fields describe the same person, they should be expanded together. The goal is to create rows that still represent real people with the correct info attached to them.
Missing & Unclear Data
Spoiler alert, no dataset is perfect and records will be incomplete. Others will contain info that is difficult to interpret with confidence. Both situations deserve attention because they affect recruiting decisions.
In almost all cases, it is better to leave uncertainty visible than to replace it with info that may be wrong.
Simple review fields can make these situations much easier to manage:
Missing EmailMissing RoleUnknown SegmentNeeds ReviewUnclear Name-Email Match
Consider a CRM row like this:
James and Priya can probably be matched with reasonable confidence. Maria is much less obvious because the remaining email appears to be a shared mailbox rather than an individual contact. That record should be flagged for review instead of being assigned automatically.
The same thinking applies to missing info. A contact without an email address may still belong in the cleaned dataset if another team can supply the missing info later. A contact without a job title may still be useful if the study includes a screener that verifies the participant’s role. The point is to make the uncertainty easy to identify so the research team can make informed decisions during recruiting.
Dedupe 101
Duplicates are one of the most common problems in data. They are also one of the easiest problems to underestimate. A spreadsheet may look like it contains hundreds of possible participants, but that number can shrink quickly once duplicate people, companies, accounts, or email addresses are removed.
For us researchers, duplicates create practical and ethical problems.
The same person might be invited twice. One company might be overrepresented in a study. A team might think it has a larger sample than it really does. Duplicate records can also make it harder to track who has already participated, who has already declined, and who should not be contacted again for a while.
Common duplicate patterns to look out for:
The same email address appearing in multiple rows.
The same person listed with slightly different names.
The same company listed under different spellings.
A parent company and subsidiary both appearing as separate accounts.
The same participant appearing in several old recruit lists.
The same contact appearing once in a CRM export and once in a marketing list.
Email address is often the strongest field for deduping individual contacts, but it should not be the only field the team checks. People can have more than one email address. Companies can change domains. Some records may use a generic inbox instead of an individual email. Exact matches are helpful, but they do not catch every duplicate.
This is why deduping should include both automatic checks and human review. Excel can highlight repeated emails, repeated company names, or repeated combinations of name and company. The team should then move questionable records to a review tab instead of deleting them immediately.
A practical deduping pass might include these steps:
Standardize email addresses by trimming spaces and converting them to lowercase.
Highlight duplicate email addresses.
Check duplicate combinations of name and company.
Review similar company names before merging them.
Move uncertain duplicates to a separate review tab.
Document which records were removed or kept.
Match the Real World
A cleaned contact list still needs to match the reality of the study. A person can have a valid name, a valid email address, and a company attached to the record, but still be wrong for the research. This is why cleaning has to connect back to the recruiting criteria.
If a study needs healthcare administrators from enterprise accounts, the spreadsheet needs consistent fields that support that filter. The segment field should not contain several versions of the same category. The role field should not mix job titles, departments, notes, and guesses. The customer tier field should be clear enough that the team can tell which accounts belong in the sample.
Useful fields for recruiting might include:
Cleaned segment
Cleaned role
Customer tier
Product line
Region
Account owner
Likely eligibility
Eligibility confidence
Account owner approval needed
Do not contact status
The cleaned fields should support the recruiting decision without hiding the original data. It is often better to create a cleaned version of a field than to overwrite the source value. For example, the original CRM value might say ENT, while the cleaned segment field says Enterprise. That makes the data easier to use while still preserving the source context.
For example, a simple mapping table might look like this:
The research team should also be careful with company-level signals. Even when a company is in the right segment or owns the right product, that does not mean every contact at that company is a “good” study participant. Some contacts may not use the workflow being studied, and others, like senior sponsors, may be important to the account without being close enough to the day-to-day experience. We all know, this is where a good screener comes into play.
Clean data can help the team find likely participants, but it does not prove that each person is eligible. A good screener can confirm role, product experience, workflow involvement, and recent usage before the person is scheduled.
The same logic applies to outreach appropriateness. Some contacts should not be used even if the record looks complete. Generic inboxes, distribution lists, former employees, personal email addresses, and contacts who require account owner approval should be flagged before outreach begins.
A useful recruiting-ready status field might include:
Ready for outreach
Needs screener
Needs account owner approval
Needs data review
Do not contact
Not eligible for this study
This keeps the cleaned file connected to the real recruiting workflow. The final spreadsheet should help the team decide who can be contacted, who needs more review, and who should be excluded from the current study.
14 Cleaning Formula Templates
⚠️ Disclaimer: These examples assume your CRM export has been converted into an Excel table named
CRM. Replace the table name and column names with the names used in your own spreadsheet.
1. Create an original row ID
Use this when you want every cleaned record to trace back to the original export row.
="CRM-"&TEXT(ROW()-ROW(CRM[#Headers]),"00000")Example result:
CRM-00001
CRM-00002
CRM-00003This is useful before splitting one CRM row into several person-level rows.
2. Clean a contact name
Use this to remove extra spaces and hidden characters from a name field.
=TRIM(CLEAN([@[Contact Name]]))Example:
" Maria Lopez " → "Maria Lopez"3. Clean an email address
Use this to remove extra spaces, remove hidden characters, and convert the email address to lowercase.
=LOWER(TRIM(CLEAN([@Email])))Example:
" Maria.Lopez@Acme.com " → "maria.lopez@acme.com"4. Flag missing recruiting fields
Use this when you want to quickly identify records that are not ready for outreach.
=TEXTJOIN("; ",TRUE,
IF([@[Clean Email]]="","Missing email",""),
IF([@[Clean Role]]="","Missing role",""),
IF([@[Clean Segment]]="","Missing segment",""))Example result:
Missing email; Missing roleIf the result is blank, none of those fields are missing.
5. Create a simple ready-for-review status
Use this when you want a cleaner status field instead of a long list of missing fields.
=IF(OR([@[Clean Email]]="",[@[Clean Role]]="",[@[Clean Segment]]=""),"Needs Review","Ready")Example result:
Readyor
Needs Review6. Flag generic inboxes
Use this to identify email addresses that may not belong to an individual person.
=IF(OR(
ISNUMBER(SEARCH("info@",[@[Clean Email]])),
ISNUMBER(SEARCH("support@",[@[Clean Email]])),
ISNUMBER(SEARCH("admin@",[@[Clean Email]])),
ISNUMBER(SEARCH("billing@",[@[Clean Email]])),
ISNUMBER(SEARCH("contact@",[@[Clean Email]]))
),"Generic Inbox","Likely Individual")Example result:
Generic Inbox⚠️ Disclaimer: This formula does not prove the contact is unusable, but it tells the research team to review it before outreach. It is only a quick review aid. A blank email may still need to be caught by a separate missing-field check, and a generic inbox list should be adjusted for your organization. You may want to add other patterns like
hello@,team@,customerservice@, or department-specific shared inboxes that show up often in your data.
7. Flag duplicate email addresses
Use this to identify repeated email addresses in a cleaned contact list.
=IF([@[Clean Email]]="","Missing Email",IF(COUNTIF(CRM[Clean Email],[@[Clean Email]])>1,"Duplicate Email","Unique"))Example result:
Duplicate EmailThis is one of the fastest ways to catch repeated contacts before recruiting begins.
8. Flag duplicate name-and-company combinations
Use this when email is missing or when you want a second deduping check.
=IF(OR([@[Clean Contact Name]]="",[@[Clean Company]]=""),"Needs Review",IF(COUNTIFS(CRM[Clean Contact Name],[@[Clean Contact Name]],CRM[Clean Company],[@[Clean Company]])>1,"Possible Duplicate","Unique"))Example result:
Possible DuplicateThis is useful because the same person can sometimes appear with different email addresses.
9. Standardize messy category labels with a lookup table
Create a small mapping table named SegmentMap.
Raw ValueClean ValueENTEnterpriseEnterprise CustomerEnterpriseHealth CareHealthcareMedicalHealthcare
Then use this formula in your cleaned segment column.
=XLOOKUP(TRIM([@Segment]),SegmentMap[Raw Value],SegmentMap[Clean Value],"Review Needed")Example result:
ENT → EnterpriseThis keeps the original CRM value visible while giving the research team a cleaner field for filtering and sampling.
10. Split multiple names into separate rows with TEXTSPLIT
Use this when a cell contains several names separated by semicolons.
=TEXTSPLIT(SUBSTITUTE(A2,"; ",";"),,";")Example input:
Maria Lopez; James Chen; Priya ShahExample output:
Maria Lopez
James Chen
Priya ShahThis is helpful for quick cleanup, but it only splits the names. If the row also has multiple emails that need to stay matched to the right names, Power Query is usually safer.
11. Split multiple emails into separate rows with TEXTSPLIT
Use this when a cell contains several emails separated by semicolons.
=TEXTSPLIT(SUBSTITUTE(B2,"; ",";"),,";")Example input:
maria.lopez@acme.com; jchen@acme.com; priya@acme.comExample output:
maria.lopez@acme.com
jchen@acme.com
priya@acme.comThis is useful for inspection, but be careful. Splitting names and emails separately can break the relationship between the person and the email address.
12. Power Query pattern for splitting names and emails together
Use this when a CRM row has multiple names in one cell and multiple emails in another cell, and you need one row per person.
For this example, let’s assume your Excel table is named CRM and starts with a row like this:
⚠️ Disclaimer: This template assumes the names and emails are separated by semicolons. If your data uses a different delimiter, change the
";"in the code to whatever your file uses.
Power Query M example:
let
Source = Excel.CurrentWorkbook(){[Name="CRM"]}[Content],
AddIndex = Table.AddIndexColumn(
Source,
"Original Row Number",
1,
1,
Int64.Type
),
AddOriginalRowID = Table.AddColumn(
AddIndex,
"Original Row ID",
each "CRM-" & Text.PadStart(Text.From([Original Row Number]), 5, "0"),
type text
),
RemoveOriginalRowNumber = Table.RemoveColumns(
AddOriginalRowID,
{"Original Row Number"}
),
SplitCleanList = (value as any, optional makeLower as nullable logical) as list =>
let
RawText = if value = null then "" else Text.From(value),
Parts = List.Transform(
Text.Split(RawText, ";"),
each Text.Trim(_)
),
NonBlank = List.Select(
Parts,
each _ <> ""
),
Cleaned = if makeLower = true
then List.Transform(NonBlank, each Text.Lower(_))
else NonBlank
in
Cleaned,
AddPersonTable = Table.AddColumn(
RemoveOriginalRowNumber,
"Person Rows",
each
let
Names = SplitCleanList([Contact Names], false),
Emails = SplitCleanList([Contact Emails], true),
NameCount = List.Count(Names),
EmailCount = List.Count(Emails),
MaxRows = List.Max({NameCount, EmailCount}),
SplitReview =
if MaxRows = 0 then
"Review Needed: missing names and emails"
else if NameCount <> EmailCount then
"Review Needed: name-email count mismatch"
else
"Split Complete",
PersonTable =
if MaxRows = 0 then
#table(
{"Contact Name", "Email", "Split Review"},
{{null, null, SplitReview}}
)
else
Table.FromColumns(
{
Names,
Emails,
List.Repeat({SplitReview}, MaxRows)
},
{"Contact Name", "Email", "Split Review"}
)
in
PersonTable
),
ExpandPersonRows = Table.ExpandTableColumn(
AddPersonTable,
"Person Rows",
{"Contact Name", "Email", "Split Review"},
{"Contact Name", "Email", "Split Review"}
),
KeepUsefulColumns = Table.SelectColumns(
ExpandPersonRows,
{
"Original Row ID",
"Company",
"Contact Name",
"Email",
"Segment",
"Tier",
"Account Owner",
"Split Review"
}
)
in
KeepUsefulColumnsThis keeps the first name with the first email, the second name with the second email, and so on. It also keeps the company-level fields attached to each new person row.
Example output:
If the number of names and emails does not match, the template keeps the available values and marks the row with Review Needed: name-email count mismatch. If both the names and emails are missing, it keeps one review row instead of silently dropping the original CRM record.
⚠️ Disclaimer: This template assumes that names and emails are listed in the same order when the counts match. It does not prove that the name and email belong together. Use the match-confidence formula after this step as a review aid.
13. Add a match-confidence flag after splitting names and emails
Use this after the rows have been expanded. It gives the team a quick signal about whether the contact name and email address appear to belong together.
=LET(
name,LOWER(TRIM([@[Contact Name]])),
email,LOWER(TRIM([@[Email]])),
handle,IFERROR(TEXTBEFORE(email,"@"),email),
first,IFERROR(TEXTBEFORE(name," "),name),
last,IFERROR(TEXTAFTER(name," ",-1),""),
IF(OR(name="",email=""),"Review Needed",
IF(OR(
AND(LEN(first)>=3,ISNUMBER(SEARCH(first,handle))),
AND(LEN(last)>=3,ISNUMBER(SEARCH(last,handle)))
),"Likely Match","Review Needed"))
)Example:
Maria Lopez + maria.lopez@acme.com → Likely Match
James Chen + jchen@acme.com → Likely Match
Maria Lopez + teamlead@acme.com → Review Needed⚠️ Disclaimer: This formula checks the part of the email address before the @ symbol and looks for the person’s first name or last name. It also avoids checking very short name fragments because those can create false matches. This should still be treated as a review aid, not final proof that the name and email belong together.
14. Create a recruiting-ready status
Use this when you want one field that tells the team what to do next.
=IF([@[Do Not Contact]]="Yes","Do Not Contact",
IF([@[Generic Inbox]]="Generic Inbox","Needs Review",
IF([@[Missing Fields]]<>"","Needs Data Review",
IF([@[Eligibility Confidence]]="Low","Needs Screener",
"Ready for Outreach"))))Example results:
Ready for Outreach
Needs Screener
Needs Data Review
Do Not Contact⚠️ Disclaimer: This kind of field helps turn a cleaned spreadsheet into a working recruiting list. The formula assumes you already have helper columns such as
Do Not Contact,Generic Inbox,Missing Fields, andEligibility Confidencein your table. You can rename those fields or change the status logic to match your own recruiting process. Treat the output as a workflow flag, not as a final decision about whether someone should be contacted.
Conclusion
Us researchers often talk about study design, moderation, synthesis, and storytelling, but some studies are shaped much earlier. The quality of the research can be affected by the quality of the data used to find, select, and contact participants. This matters in our orgs because researchers depend on systems owned by sales, support, customer success, product, or marketing. and alllllllll that data still needs to be cleaned before it can support recruiting, sampling, screening, or outreach.
Basic data cleaning helps the team understand what one row represents, which contacts are duplicated, which fields are usable, and which records need review. It also helps prevent outreach mistakes that can damage trust with participants and internal partners. This is why data cleaning should be treated as part of ResearchOps. In all orgs, UX research often starts with data someone else owns, and the useful work is turning that data into something research can responsibly use.
Amazing! Helps alot. Thanks alot.
You're my favourite author along the entire journey towards to become a researcher.