Changing membership systems is a bit like moving house. "Time for a good old clear out!" goes the cry. "Let's get rid of all those obsolete and duplicated records". This is not a bad plan, but in my experience, the execution is always a problem. 

The first instinct is to fix the data in the current system, then export it, so as to import nice shiny data into the new system. There are two problems with this. The first is that this cleaning process needs resource, and probably from someone who is already overloaded - after all, that's why you're going to a new system. The result is delay to the overall project, and probably a half-hearted clean-up as well. The second problem is that the existing system probably doesn't have good tools for filtering and de-duplicating - again, that's why you are changing. 

So the next instinct is to export the data to spreadsheets, and do the tidying up there. Although spreadsheets can have reasonable filtering and good de-duplication, if you need to merge data it gets tricky. And if you have linked data across multiple spreadsheets, this is a recipe for disaster. Oh, and if anything gets accidentally deleted it may not even be noticed, especially as there will be time pressure to go-live on the new system. Having said that, spreadsheets are good at search-and-replace, so this is a good place to fix terminology, reduce pick-lists, and add new tags. Practice on a test export, and document so you can repeat on the go-live data.

Counter-intuitive as it may seem, it often makes sense to move your data to the new system as-is, and then use the new tools you have to filter, de-duplicate and merge. If the new system has member self-service, get them to update their own profiles. If you have separate archive and delete capabilities, set up a workflow between staff to maximise member-knowledge and experience. Write a procedure to nail down the de-duplicate process. 

A very useful feature for those adopting this kind of post-processing is selective export and re-import. If just one or two fields need to be merged or updated, they can be exported to a spreadsheet along with the key index (usually membership number), amended using search and replace or spreadsheet functions, and then re-imported to overwrite just those fields in those records. Scary, but very effective.

The only problem with post migration clean-up, is that much like moving house, it's very tempting just to put all the clutter in the new garage or loft with the firm intention of sorting it out "at your leisure", and we all know how that turns out.