Syncing people data between systems - why is it so hard?

Although some people write weeknotes I've never done that because I think what I've got to write about is too internal, or not interesting. However I'm writing about a work problem, not because I've got solutions, but to help me step back and think about it, and maybe get answers from you, dear reader.

Over the years I've been involved in getting data about people in sync between the HR system and the CRM (specifically a donor management system). You might ask, "why are staff on the CRM?". Staff can be customers, or in our case, donors. In particular, we would track potential staff ("enquirers") because the HR system didn't.

Here are some of the challenges I've had with the process:

People data is messy

Names in particular are messy, so when checking to see if two records are for the same person then it may be hard to work out. Systems tend to have unique ids, so storing the id of the person that the other system uses on this system gets round that. Until the member of staff makes a donation and spells their name slightly differently...

Addresses are messy too. In the UK there are unique DPSs, but validating incomplete addresses is more of an art best done by people, than a science at times.

The HR system isn't always definitive

I created a sync process that would compare the HR and CRM systems and highlight differences. When I created it I suspected that it would be unwise to assume that the HR system was the definitive source of truth, even though it was in theory, so the sync process would only highlight differences, not automatically update the CRM to be the same as the HR system. This turned out to be correct, and the process ran for several years, once a week, and most weeks there was something for someone to change on one of the systems manually.

Different definitions of people

You would have thought that all systems have the same definition of a person, but that's not the case with the sync method I'm using that's driven me to write this blog post. We use Salesforce as our CRM, and send out bulk emails with Campaign Monitor. There's a Salesforce App from Beaufort12 that synchronises data between the two systems. We have some couples that share an email address and so what Campaign Monitor treats as a unique subscriber corresponds to two different people on the CRM.

One consequent of this is that when one member of the couple dies that email address will automatically be unsubscribed and the remaining spouse may want to continue getting emails.

Testing integrated systems at scale can be hard

We don't have a test Campaign Monitor setup, so we're working on the live system all the time. Of course, it is possible to create test integrations in some circumstances, but if you're working with a complex system you may not be able to mimic all its complexity in a test setup.

Conclusion

None of this is has particularly helped me with the Campaign Monitor syncing problem I've currently got, but I hope it helps you with some general advice.