I’ve been collecting, cleaning, and analyzing data for over 30 years. During that time I’ve seen certain data problems and snafus occur over and over again. To help you deal with them, we’ve put together a list of the six most common, along with advice on how to avoid or fix them.
Most of the tips below help you insure that your data is valid, in other words it represents what it is supposed to represent. Sadly, if you don’t dive into your data enough to determine if it is valid, the consequences can be dire. I had a client base an entire board-level presentation on metrics derived from flawed data. Somehow no one bothered to check the data for false positives; as the result of a bad search string, their data was irrelevant to the subject at hand.
For another client, a well-known fast food chain, we had to go back in and scrub some 4000 references to an underground transportation system that had no relevance to their business. Even worse, they’d assigned each of those 4000 references an inflated impression count, so their “reach” was larger than the population of the entire earth! No wonder that vendor was terminated.
Here are six common data problems and how to avoid, fix, or eliminate them:
1. Eliminate duplicates.
If you’re looking at media monitoring data in Excel (and if you’re not, then you should put it into Excel) go to the Data tab and click on “Remove Duplicates.” Select a column with a unique number, like “Cutting ID,” or “Item ID,” or if that doesn’t exist then use the URL (each article has a unique URL) and click OK. The system will automatically eliminate duplicates.
Alternatively, if you sort your data by date, publication, and URL or Title, you will be able to spot duplicates easily.
2. Put your data collection on a diet.
The easiest way to cut down on the number of errors in your data base is to make sure you are collecting only the data that matters. Look at every data point and define how it contributes to running your business better. (And you really ought to read Avinash Kaushik’s post on this first.)
- If you are analyzing media data, check the outlet list or platforms first. Are the sources you are collecting ones that influence your customers, prospects, prospective employees, investors, and other shareholders? If not, delete data from those sources, they don’t matter to your business.
- Then check the subject matter. Are the products, services or issues that have been collected relevant to your business? Are they old, obsolete, or too obscure to track? If the answer is yes, eliminate them.
- One of the most common problems is to capture all the data that Google Analytics or whatever platform you’re using spits out. Stop it! Review each data point: Users, Sessions, Bounce Rate, Session Duration, and the rest. Have a conversation with your team about which are actually useful to make better decisions and improve your organization’s performance. More importantly, decide whether a few well-crafted conversions would make more sense. (To learn how to set up conversions watch our YouTube video.) Also, you’ll want to refer to our surprisingly short list in “The Only Metrics You’ll Ever Need: Katie Paine’s Guide to Taming Your Data Dragon.”
3. Don’t compare apples to goats.
Make sure that your various data streams are in fact parallel and not duplicative. For example, in Google Analytics data you can certainly compare Mobile vs. Desktop, but you can’t compare Mobile and Referral Traffic, since Mobile is already included in Referral Traffic.
Check your time periods. Too often we’ve seen numbers that look totally wacky until we realize that one represents a month and the other represents a day.
Make sure to check your axes to make sure that you aren’t exaggerating a data change because you are using an inappropriate range. This tactic is a favorite of devious chart-makers who wish to either hide or emphasize a change.
4. Develop a standard dictionary for all terminology.
I’ll never forget sitting with a client who had six reports on her desk from three different departments and three different agencies. She threw up her hands and said, “I can’t figure out what any of these mean!”
Every one of those six reports used the term “engagement,” but each reported widely different numbers. Metrics like users, sessions, unique visitors, and page views seemed to be used interchangeably. No wonder she was confused.
What she needed—and we developed—was a dictionary that defined each term, nixed any that weren’t meaningful, and narrowed dozens of different metrics down to six. (We kept the discarded terms in the dictionary just in case they cropped up in some future report.)
If you are using multiple vendors and data sources, then the best way to get everyone on the same page is to start with terminology used in standards, like the MRC standards for social, or the IPR standards for PR. You can find all of them at Paine Publishing’s Standards Central.
List the terms each vendor uses and what you would consider an ideal alternative. Some vendors have very rigid systems that can’t be changed, in which case they’ll need to manually change the terminology in your custom reports. Bring everyone together on a conference call and hash out any major issues. Then post the dictionary somewhere that everyone can access.
5. Audit your data.
Auditing is the process of checking each item to make sure it belongs in your data set and is accurate. This is particularly important if you are using multiple sources of data, and if you are running correlations.
The most frequent error when you are combining data sets is a misalignment of dates. You need to make sure that all your date formats are consistent. If you are comparing data quarter to quarter and using two different data sets, make sure that the start and end dates of the quarters are accurate and don’t overlap.
If you are correlating media coverage with web analytics data, remember that analytics are reported daily, whether or not there is any activity. But in your media reports you may not have data for ever day of the month, so you need to go in and manually line up the dates.
6. Test for intercoder reliability.
A good reliability test selects a random sample of articles and has multiple readers analyzing the same articles to make sure the instructions are consistent and clear. If three coders can’t agree on a coding element, you need to clarify with better examples or a longer conversation.
It doesn’t matter if you are using human coders or machine coders, it is still critical to check the results monthly. Or else the errors multiply and the amount of cleanup quickly gets overwhelming. For a comprehensive introduction to evaluating intercoder reliability, see pages 10-12 of Jim Macnamara’s paper here. ∞
