Mary, Mary, How Does Your Data Grow? - Part 2

This article is part two in a series that focuses on how the University of Washington created a robust data audit program. This program was created to ensure data is tended to carefully, like a garden. In part one of the series, authors Cynthia Lee and Krista Berg share the “why” of their journey ― the many considerations taken in order to bring this program to life. Here in part two, Lee and Berg discuss the details of building a program ― the “how” ― once there is buy-in from leadership.

Be sure to read part one before diving into this tip-filled article.

Selecting Your Crops

Once you have leadership-buy in to build a robust data auditing process, the next step is figuring out what exactly to audit. This depends a lot on your role at your organization, as well as the amount of time you can spend on data auditing. It is easy to get caught up in all the exciting things you can audit, but prioritization is key, at least at first. Flowers are beautiful, but vegetables can be eaten.

We suggest thinking about things like:

  • What are your most common data entry errors?
  • Is there a notification you like to provide to your fundraisers, but pulling it manually is tedious?
  • Do you have processes that could benefit from automation?
  • What stands out most to your leadership?

All of these are things you can build audits for whether you are savvy in SQL or just starting out in Excel. Our colleagues in gift services, donor relations and biographic maintenance have all built their audits using different tools, but they all serve the purpose of having clean data. While we have focused on prospect management here, audits are vital for all areas of a development shop.

You also have to start thinking about how you access your data. If you have a backyard garden, it is easy to walk outside and start weeding. However, if your garden is in a community plot, you need to be more thoughtful about when and what you harvest. As we mentioned above, our audits are built in Access using our CRM’s base tables. If you are among the lucky few who have similar access to your database, Access is an easy tool to use for digging into your prospect records and portfolios. Your database may also have mass change capability and/or a query tool, both of which could be used for auditing. If you do not have direct access or mass change capabilities, you can still query your data either by using the filters in your database or by exporting information to Excel. VLOOKUP, IF and CONCATENATE are a few of our favorite functions, and pivot tables, filters and macros are great tools to have in your garden shed.

For example, take a data set where we are looking at booked proposals. If we want to determine whether the result amounts are listed correctly, one tool we can employ is Excel’s IF function. The IF function looks for a condition you designate and returns one of two values, depending on if the condition is met.

In this example, we want to see if the result amounts for any of our booked proposals are listed as $0, less than 10 percent of the ask amount or greater than 10 times the ask amount, as any of these are almost certainly mistakes. The two possible outcomes in this situation are “ERROR” if the result amount is blank or significantly different than the ask amount, and “ok” if the result amount is in an acceptable range. Because there are several criteria for an acceptable booked proposal amount, this example includes nested IF functions to identify all potential issues.

Our audit looks like this:

If we want to crosscheck other fields, we can simply add additional formulas in new columns. An audit to verify that we have not forgotten to add a result date or mistakenly listed the result date prior to the ask date might look like this:

=IF([Result Date]<[Ask Date],"ERROR",IF([Result Date]="","ERROR","ok"))

This gives us the following results:

These formulas are simple, but your audits can be as intricate as you want them to be. Once you get the hang of it, you can write something much more complex.

Deciding When to Weed and Picking a Head Gardener

“It is only the farmer who faithfully plants seeds in the spring who reaps a harvest in the autumn.” – B.C. Forbes

Another important consideration when building your audits is timing ― when do you run them? How frequently do you need to run them? Depending on the data in question, our audits run daily, monthly and quarterly. We have learned how often each type of data error happens, or when they are most noticeable, and have scheduled the audits accordingly.

For example, at the end of each quarter, when performance reports for our gift officers are about to run, we audit all personal visit contact reports to make sure they contain enough content to qualify as in-person meetings. We audit daily for deceased prospects so we can notify our fundraisers and update the prospect record as needed. Monthly, we audit for RMs who have not contacted their prospects in 12 months so we can sunset their assignments.

Sometimes the frequency with which your audits run will be driven by your schedule. If you do not have the capacity to run and work through audits every day, consider scheduling a quarterly Data Day of your own so the time is committed on your calendar.

Finally, you will need decide who will manage your audits — who will write, document and run them. At UW, we utilize our student employees to help with this. They handle our routine audits, such as assigning our regional fundraisers to prospects based on geographic area, leaving the more complex ones for us. We then split the audits based on who last touched that data or which unit the audit refers to. Balance is important.

Reaping Your Harvest

“Gardening is learning, learning, learning. That’s the fun of them. You’re always learning.” – Helen Mirren

Audits do not have to be a huge time or technology investment. We truly believe anyone can do this — you can do this. There is no wrong way to audit; if the end data is cleaner than it was when you began, it is a win. We are the stewards of the data. Do not rely on your fundraisers to pull the weeds. Reliable data is at the core of our reputation, and our trustworthiness can be easily undermined by data inaccuracies. Building trust gives fundraisers confidence in your abilities and allows you to make informed choices for them. Audits matter — do not be afraid to dig in.


Krista Berg is a prospect management strategist at the University of Washington. She has been working in development for over 10 years, starting as a student caller at the UW. She is also the current president of her regional chapter of Apra, Apra-NW.

Cynthia Lee is a prospect management strategist at the University of Washington. She has been working in development for over 10 years and in prospect management since 2011.

Looking for more information on maintaining relationships? Check out Plug In to Relationship Management, Apra’s virtual event coming this November, for a deep dive that can be taken right from your computer.

If you can't wait till November to learn more about relationship management, download the Apra University Relationship Management Bundle 1. For a discounted price, you can download five relationship management-focused sessions, including "More Than a Data Scrub: Crafting an Ongoing Portfolio Consultation Service" and "A Decade of Supporting Open Cultivation."

Recent Stories
Ask the Ethicist: Is a Photo Worth 1,000 Headaches?

How Prospect Research Can Turn ‘No’ Into ‘Yes’

7 Steps for Elevating Prospect Research and Data-Driven Decisions to Achieve Fundraising Goals