Avoiding A Data Mess

What steps can you take to prevent your data from turning into a mess?

Over my career, I've seen how data – both numeric and textual – is organized, collected, stored, retrieved, used, and presented. Numerous examples, both good and bad, have shown me what works and what doesn't. From these experiences both with clients and my own business, I have come up with a few general rules I follow on how to structure data, to efficiently and effectively turn it into actionable information.

Here are some examples of data from Todd Herman Associates (THA) that I'll use to illustrate the rules I'll recommend:

• Numeric – Hours we spend on client projects, Fees associated with those projects, and THA's monthly Financial Statements.
• Textual – Invoices sent to clients, Outputs of client and internal projects, and Job Aids documenting our key internal processes.

Preliminary Questions to Ask

Before you begin to structure your data, it's best to ask yourself a few questions:

• What data do you need to track?
• What do you intend to do with the data?
• How can you collect the needed data as easily as possible?
• How do you process the collected data?
• How do you turn data into presentable information?

In the following paragraphs, I describe THA’s key systems and data, allowing you to see how I answered these five questions in my own business.

As a service-based business, THA earns revenue by working with our clients to help improve their businesses. As we do our work we need to capture our time accurately, code it to the correct client project or THA internal time code, and briefly document what we were doing. We do this in an internally-developed Time Sheet application.

The Time Sheet, however, only collects dates, projects, hours, and comments. To convert this data into fees, we export Time Sheet information and import it into our Sage 50 accounting system – which I will forever call Peachtree Accounting, its original name. Reports out of Peachtree are used to draft the Invoices we send to clients, which we prepare in our internally-developed Client Folder application. Once the bills have been drafted, reviewed, and checked one last time, we email them as PDF files from our internally-developed Contact Management application to our clients.

"How To" documentation for key internal processes lives in our internally-developed Job Aid Library, which also serves as a "Master Calendar" showing who needs to do what each week of the month and how long each task should take. Key engagement outputs and deliverables for both client and internal projects reside in either our internally-developed Project Database or our Microsoft Team Foundation Server database.

Finally, revenues from client work and all expenses are entered into Peachtree, which generates our Financial Statements.

As you've probably noticed, THA relies on a number of internally-developed applications. That's because we need a combination of tight security, ease-of-use, powerful full-text search, excellent collaboration capabilities, efficient data transmission, and ability to work disconnected that was impossible to find in off-the-shelf products 25 years ago. Now, with 25 years of data in them, these applications have become invaluable knowledge bases for us.

Once these five preliminary questions have been answered, we are ready to move on to the data rules.

Suggested Rules to Follow

To better organize and structure your data, still using the THA example, here are eight rules I recommend:

1. Keep It Simple – Although a complex time-coding system can produce more information than a simpler system, it runs a significant risk of being too hard for all employees to follow the same conventions and then ending up a confusing jumble.

2. Error-Proof Data Entry – Especially in time keeping applications, restrict choices to valid options. Furthermore, if a choice in one field means only certain choices are valid in the next field, present ONLY the valid choices in the second field.

3. Make It Logical – If you're using an indexing system to organize information, make it logical. For example, in our Job Aid Library, we code everything like this: TTTT-SS, where TTTT is a Main Topic and SS is a Subtopic. Thus, ACCT-AP, ACCT-AR, ACCT-BL, and ACCT-PR are all in the Accounting (ACCT) Main Topic, and then Accounts Payable (AP), Accounts Receivable (AR), Billing (BL), and Payroll (PR) are its Subtopics.

4. Be Consistent – Once you've decided on your data structure and coding conventions, stick with them. Especially in accounting systems, it is a VERY big deal – because of many "behind the scenes" interdependencies – to move from a 4-digit ledger account number to 5-digit one. Better to save those ideas until you choose to upgrade or replace your accounting system.

5. Collect Only Enough Detail to Produce Meaningful Results – Collecting too much information has a cost. Input fields in older systems may have limited you to a certain number of characters for notes, while newer systems often provide virtually unlimited space. Yet, a limited number of characters forces conciseness and, in a new system, you may choose to limit input in certain areas for exactly that reason.

6. Write Process Documentation with Your Staff in Mind – Keep a picture of a generic staff member, including minimum education requirements, in your head when documenting a process. For example, certain accounting data entry is readily done by a new college graduate, while accounting entries requiring seasoned judgements are best left to an accountant.

7. Understand How Your Reporting Tools Work – Reporting tools are excellent at sorting and grouping data that's been coded a certain way, and then totaling quantities or financial data for each group and the overall report. Some reporting tools have additional capabilities specifically to produce financial statements. Be sure to understand the capabilities and limitations of the available reporting tools, then devise a coding system that aligns with your tool’s strengths.

8. Prepare Client Bills and Internal Reports with Your Users in Mind – While some firms may use a dump of their employees' cryptic time sheet notes as their bill to a client, we have always taken a different approach. Our bills are broken down by project, with bullet points describing the various tasks performed. We want our clients to be able to easily read and understand what we did so they can quickly approve our invoice and move on to the next task on their always overflowing To Do list. Similarly, when you prepare internal reports, think of the report users as “internal clients” and make the reports as easy-to-use as possible.

In our experience, both for ourselves and for clients, following these eight rules will better organize, collect, store, retrieve, use, and present data, allowing it to be turned into valuable, actionable information. We believe you'll find the rules especially helpful when a project requires implementing a new system in your business.

The Counter-Example

Unfortunately, there are times when someone was unaware of these eight data rules, leaving you with a big mess to cleanup. Over the years, I have helped my clients clean up many a data mess. So, should you find yourself in such a situation, please contact me – we've seen some doozies and we've always been able to tidy them up.

Sincerely,

Todd L. Herman