Tamsen Haught - Jan 5, 2015

How to Perform a Data Inventory for Associations

Sometimes when our clients begin a business intelligence initiative, they become more concerned about collecting new data to analyze, and focus less on the data that they already have. One reason this happens is that they don’t always know what data they do have. We often say that data is one of your association’s most valuable assets, and you want to treat it that way.  Just like with cash, you need to monitor and manage it.  Performing a data inventory is the first step to understanding your data and the state it is in. Because business intelligence is all about using data to make decisions and decisions are basically answers to question, it follows that a best practice is to determine what data you have available to answer your business questions.

groceries

Imagine you are a grocery store clerk and each night you need to perform an inventory of your store so that you can assess its current state. You start by walking around the store to see what aisles are either stocked or missing items. For your database, you will “walk around” to see what tables either have data or are empty. If that table does not have data, you will not be able to analyze anything around that topic.

Next in your grocery store, you start looking a bit closer at each aisle. When you are looking at cereal, how many boxes do you have in the aisle? For your database, you will look at your tables to see how many rows exist in those tables.   Maybe a table has just a few rows in it. For example, if you have 50K individual records in your database, but only 500 organization records, you may not be able to do meaningful analysis of organization data.

After looking at the overall cereal section, you then dive into the specifics. What is the breakdown of cereal on the aisle? Company, brands and type? Similarly, for your table inventory, you will want to look at the specific columns in the table to see:

  • Column Name
  • Data type
  • % of columns populated

For your 50K individual records, if only 10% of those records have demographic information such as gender, ethnicity and age, you will not be able to perform a meaningful analysis of the effect of demographics on an individual’s purchasing behavior. If this is an area that you know you want to analyze, you’ll know early in the process that this is an area to address by supplementing that data.

Now we want to look at the specifics of the column data, both quantitative and qualitative. Qualitative analysis can show you trends in string data. For our cereal, we look to see how many we have of each brand. 200 from General Mills, 175 of Kellogg. In our data we need to look at the top qualitative values in our columns. If you were looking at individuals, you would look at the top 10 companies represented by those individuals in your system.

We also want to look at the quantitative data. Quantitative analysis can show you discrepancies in your data. For cereal, we could look at price. What is the most expensive price for cereal? What is the cheapest cereal? Maybe you have cereal priced for -$2.75 or $100, those discrepancies are errors that become readily apparent using this type of analysis. For your data, you could look at total number of employees for your member firms. You should analyze:

  • Maximum value
  • Minimum value
  • Mean value
  • Standard deviation

Performing this type of data inventory provides a solid foundation for your business intelligence initiative. Doing this initial work will help you understand what data you have and by extension what questions you will be able to answer. And most importantly, you will be able to find out what you don’t know. And knowing is half the battle.

Written by Tamsen Haught