5 Tips on Building Data Integrity Queries!
Happy New Year to our dear readers! I hope you had a wonderful holiday spending quality time with family and friends. The new calendar year is upon us, and hopefully, you are well rested and returned to the office (or your desk at home – you lucky bunny *shaking fist*). Now it is time to start thinking about ALL of the upcoming projects and new year resolutions to be completed in the next few months.
But before you start diving deep into your database again, you should take some time to clean up all of the new data that has come in over the holidays. If you have been following our newsletter here at Sentinel Consulting, we have been sharing some tips and resources to help you prepare your database for the end of the year (tax receipt season *wink wink*).
One of the first things we always recommend our clients do prior to starting any major data projects, such as a Direct Mail Appeal or processing year-end tax receipts & letters, is to run some cleanups in the database. The last thing you want to have is a last-minute delay during the data processing for an Appeal or Event invite that has to go out this week. As the old saying goes, measure twice and cut once.
Most databases provide several different options for data hygiene, such as tools to remove duplicate records and addon services to correct Address formatting. Another great tool to use for performing maintenance on your database is the query function within your CRM database. In today’s article, I want to share with you 5 tips for creating Data Integrity Queries to help you perform a quick cleanup of your database.
1. Review your most commonly run reports and exports
The best way to decide what type of Data Integrity queries you need is by looking at the types of data you usually have to procure for your team. The ideal place to start is the reports, exports and other queries you run on a day-to-day basis.
2. Know how your Data should look (collaborate with colleagues)
Another key requirement to creating effective queries is a good understanding of your database, not just functionality, but also how the different data fields need to link together. A thorough understanding of how the Campaign, Funds, and Appeals interact as well as how the required fields for a Constituent Record are used. This gives you insight into what you are looking for.
3. Build queries that are looking for typical errors in your data
Keep in mind that you are building queries to identify records that have incorrect information. For example, to ensure that your DM Appeal data is accurate – you may create a query that is specifically looking for records with parts of missing addresses such as missing postal codes or typos in Cities. Once records are identified, users can manually go into the records and fix the issues identified by the query. Or it may be possible to make global changes that will correct these records.
4. Focus on one common error at a time
The goal of Data Integrity queries is to create a collection of queries – focusing on identifying records with small issues throughout your database so they can be cleaned up (either manually or via global change/import). By keeping each query addressing one error, you can isolate the issues and ensure that cleanup is accurate.
5. Run your data integrity queries on a schedule
These queries should be run on a schedule, with consistent cleanup you can eliminate having to do massive data cleanups during crunch time and ensure that all data within the database is accurate. Consistent maintenance also reduces the overall time spent on cleaning the database as you can tackle issues in smaller bites. This allows for cleanup to be done during down-time and allows you to avoid overwork during stressful situations (deadlines).
Once you have these queries created in your database, save them in a separate folder called Maintenance Queries. They will become your templates for creating additional queries for reviewing and cleaning up new data fields for imports, exports, and future reports.
At Sentinel Consulting we have gotten the chance to work with multiple fundraising shops seeing how they can best implement data hygiene. If you are interested in specific examples of how these queries can look, please reach out to us!
If you would like us to tackle a topic or problem of interest to you, in future articles, please let us know here.
Sentinel Consulting is a consulting firm that focuses on helping non-profits strengthen their operational efficiency. We speak the same language as our clients because we were all fundraisers at some point in our careers.