I’m no database expert, and I’m not going to claim to be one. Big data actually sort of makes my head hurt. But I have worked with many organizations, both large and small, over the years as they have tackled data de-duping projects. And, as a marketer, I have a great appreciation for keeping one’s data warehouse tidy.
If you’re using the slow months in the summer to focus projects that tend to get put off, consider a data de-duping project.
Broken down to its very basic workflow, data management is handled in the process of three stages:
- Extract – The extraction stage is where you pull the data out of a system with the end goal of transporting it into your database of record (CRM).
- Transform – The transformation stage is where you do your cleaning and updating to make sure it fits appropriately into your CRM
- Load – The load stage is the actual process of importing into your CRM.
Here are some of the common scenarios I’ve seen in my experience working with nonprofits:
- Data deduping with a CRM solution
A CRM solution (such as Salesforce, Oracle, Abila, Raiser’s Edge or the like) is your database of record and should always be treated with the utmost authority in terms of being the cleanest, best and most up-to-date information. In addition to your CRM system, you have constituent data coming in from all different sources, such as your email marketing platform, online donations platform, offline donations and volunteer databases. Sometimes you have an integration set up between these tools and your CRM system. Sometimes those integrations work well and sometimes they don’t. When they don’t, this causes a whole new set of data management needs. - Data deduping with Excel or Access
Are you using a tool such as Excel or Access to manage your database? You also have have constituent data coming in from all different sources such as your email marketing platform, online donations platform, offline donations and volunteer databases. You may have many, many different Excel spreadsheets that feel disorganized, disjointed and you often end up using Outlook as your email marketing platform, importing lists from a mail merged file.
Do either of those sound familiar?
3 Best-Practice Tips for Data Deduping
In terms of keeping your data clean, I’d like to focus on the Transform stage: That’s where the scrubbing happens. Here are three best practice tips to help you in both scenarios with the Transform stage.
- Sync your systems often so that dirty data doesn’t get the best of you! Your CRM, master Excel file or whatever you use as your database of record should be synced with your other systems on a regular basis (daily and/or at least weekly). Whether this happens through a manual process or an automated sync process, do it on the regular. If it’s a manual sync, set up a calendar appointment for 30 minutes every other day to make sure things are in check. If it’s an automated process, set up a calendar appointment for yourself for 15 minutes every morning just to make sure the sync ran properly.
- Research what your CRM tools and other data sources have built-in which could make the process of cleaning the data more simple. Many systems have built-in de-duping processes that make this easier. So if you feel like “there should be an easier way,” do a little research into the capabilities of your system because it’s very likely there is a better way. If you are using Excel, make the sort function your best friend. If you have an online donation platform taking in donor data, check to see if it has tools that allow you to recognize duplicate constituent records or duplicate transactions and clean them before doing the extraction stage. If you are using a tool like Salesforce for your CRM, research the help files for “data de-duplication” tricks of the trade, and be sure to explore the expansive selection of apps on the AppExchange to help with your de-duplication needs. There is also a great deal of help and information in the various online forums for Salesforce administrators and users – so take advantage of these channels to learn about new tools and best practices.
- Data matching. If you have data matching rules in place in your systems, take a second look at them. Are the match scenarios too strict, too lenient? You may have them set up so strictly that you are wasting time in trying to do your de-duping. You may have them set to be too lenient and therefore have more dupes getting through.
Make data de-duping a habit. No matter what your data de-duplication project looks like, try to always apply regular management of your data into your weekly schedule. If you take just 15 to 30 minutes daily or even a few days per week, you will be less likely to get into a scenario where you are scratching your head.
here.