Garbage In, Garbage Out - Collecting Data The Right Way

As founder of Financial Systems Advisors I work with small and medium businesses owners to help entrepreneurs make better decisions by performing data and financial analysis. It all starts with data collection. Unfortunately, I can tell you from experience that far too often I come across founders who have gone through the trouble of collecting all sorts of data, which is often manual and time consuming, and once we begin working with it, it’s so messy and unreadable by any data analysis method that we with have to either spend a lot of time and money to clean it or simply we start from scratch. Fortunately, it’s actually pretty easy to collect data the right way from the start, which is why I am writing this post. I hope to help founders collect data in a way that if they ever choose to do any kind of analysis then it can be workable and yield meaningful results. So here goes.

1.) One header for each column of data

All columns should have one header that is reasonably descriptive of the data in that column. If a column requires more explanation than one row of headers, it is ok to have multiple rows with headers as long as they aren’t merged with other columns. Most analytics software asks how many rows of headers to skip.

2.) Avoid merging cells, coloring cells manually, or using special characters

Merging cells, whether between columns, rows, or in the headers will make the merged cells unrelatable to the unmerged columns and rows and it will be unreadable. Coloring cells might help you understand it better, but the databases can't read it or use that information. If you however use conditional formatting, which colors the cells based on calculations rather than manually putting the colors on, the database still can’t read that but can recreate those calculations and give you the same information. Special characters, like emojis, can often be unreadable for databases at worst and unworkable at best. It’s best to avoid them.

3.) Each record needs to be unique

By record, I mean row of data. This doesn’t mean every cell within the row needs to be unique but the combination of cells that makes up the row should be unique.

4.) Each cell should have a single value

Please don’t list data within a cell. If you are finding the need to do so, instead you can make a new row for each of the items in the list and fill all the other cells in the new rows with the same data as the original row. This might make the data set larger than it seems necessary, but any data analyst should be able to transform this data into a more efficient form easier than the former way.

5.) Make sure like items are spelled the same

This is a big one. The database will read things written exactly the same as the same and anything else as different. If you do a bar chart, the same thing spelled in 2 different ways will appear as 2 different bars, for example. This means spelling must be exact and case sensitivity matters. There are things a skilled analyst can do to rectify certain problems somewhat easily, however It can be very time consuming to go through the database and identify then fix all the same but slightly misspelled things. If these errors exist, then the resulting analysis won’t be correct.

Here are some practical tips for this matter specifically. It might be easier in some situations, especially if the data is from customers or external sources to use forms for data entry, in which case give multiple choice options instead of allowing free text wherever possible. On the spreadsheet itself each column should be structured with the most specific data validation (google this) possible, meaning the cells will reject or at least warn you if what has been entered doesn’t fit the way you want it to. For lists of text, this will create dropdowns to ensure you are always picking the same spelling of whatever you are entering. Advanced tip: check out dynamic dropdowns so that you can still enter new values to the column while ensuring you are always using the same spelling if you repeat that value.

6.) Every record should be uniquely identifiable by a column, called a primary key

In order to find a specific record, each record should have one column or, if you really need to, a combination of two columns, that are entirely unique. This is used as an identifier of this record. This is why you have a unique username on websites or ID #s that no one else has. It’s so the database can identify you specifically. If there is nothing in your columns that is entirely unique, that’s ok. The analyst will usually create a new column and make an incremental ID for this purpose.

7.) Relate tables to each other with that primary key

This is a slightly more advanced tip, however if you are using multiple tables to represent your data and you want them to work together in your analysis, then one column in one of the tables should have values that are related to a primary key in the other column. The primary key, as we said previously, is a unique identifier of each record, and therefore if a second table has a column with values containing the primary key of the first table (AKA a foreign key) that will relate the row in the second table to the specific record in the first, regardless of the order of values found in the second table. Dates or timestamps can be used as a primary key.

Well entrepreneurs, there you have it. Start collecting your data the right way so that meaningful data and financial analysis can be performed when you want to get a clearer picture of your business to make more profitable decisions. Otherwise, it will be a lot of time spent on garbage in, to get garbage out.

Feel free to comment here or reach out at andrew@fsadvisors.co if you have any questions.