Best workflow for cleaning museum membership data before CRM import?

Support and Helpdesk
1

Hi everyone,

I'm working with a museum membership database that has been maintained over several years. The exported CSV contains inconsistent membership levels, duplicate member records, mixed date formats, and phone numbers stored in different formats.

Before importing everything into our CRM, I'd like to clean and standardize the dataset with OpenRefine.

I'm planning to:

  1. Merge duplicate member records
  2. Standardize membership types
  3. Normalize phone numbers and email addresses
  4. Fix inconsistent date formats
  5. Remove unnecessary whitespace and formatting issues

Has anyone used OpenRefine for museum or nonprofit membership databases?

We're also using digital membership card through MembershipAnywhere, so having clean member records before synchronization is important. I'm interested in learning which OpenRefine features or GREL expressions have saved you the most time.

2

I’d approach this as a reviewable cleanup process, rather than trying to fix everything in the exported columns at once.

For membership levels, start with a Text Facet to see the exact variants and their counts. Clustering is useful for finding likely spelling or spacing variants, but I would check each suggested merge before applying it—similar-looking labels can still represent different benefits or renewal states.

For duplicate members, I would make a review list rather than auto-merging rows. A normalized email address can be a useful signal, but shared household contact details and old records can produce false matches. Keeping a duplicate_candidate flag makes those decisions easier to audit before the CRM import.

For phones and dates, first decide what the CRM actually requires. Keep the source value, create a separate normalized column, and leave values that do not parse cleanly visible for manual review. That is safer than silently guessing a format.

Before exporting, I would use facets to check blank required fields, unresolved duplicate candidates, membership labels that still need a decision, and dates that failed to parse. Keeping that small exception list alongside the cleaned export makes the import much easier to reconcile later.

If you can share the required date and phone formats for the CRM—without any member data—the checks can be made more specific.