Dirty Data Dooms Measurement: Here Are 5 Tidy-Up Techniques

This is one of three related articles on how to avoid and deal with bad data:

• Dirty Data Dooms Measurement: Here Are 5 Tidy-Up Techniques
• 5 Real Life Horror Stories of Bad Data, and
• The 10 Biggest Mistakes When Hiring a Media Monitoring Vendor

by Katie Paine — Dirty data is the doom of your measurement project. It’s the difference between vital insight and months of wasted effort. And here’s the media monitoring industry’s dirty little secret: You can’t trust their data. Nope. They’ll feed you gibberish and cash your check with a smile. Not that they are malicious, they just can’t take the time or effort to clean your precious data like you can.

The good news is that there are specific efforts you can make when you collect, code, and analyze your data to ensure that it is as clean as possible. You’ve got to clean up Bad Collection, Bad Content, and Bad Numbers. Follow the 5 steps below to tidy up your data and your measurement will be off to a clean start.

1. Bad Collection: Filter Out the Junk, Duplicates, and Irrelevant Items.

Pretty much all dirty data starts with a collection process that pulls in too much of what you don’t want. That means that either the search strings are too broad or too narrow, there aren’t adequate spam filters, or there is an inadequate process for removing duplicate and irrelevant items:

• Search Strings: The fix is to budget (both time-wise and dollar-wise) for adequate time to test your search strings, dig into the results, modify them, test them again, and tweak as necessary. This can be tricky and time-consuming: For complicated programs it is not unusual to have upwards of 1000 terms within a single search string.
• Spam: Use your “find” function in Excel to find spam (like Viagra and Vioxx). In one database for a restaurant we searched for “Viagra” and eliminated about 25% if the mentions! Look for calendar listings, weddings, police blotter notifications, and anything with similar but inappropriate terms. For example, we did a search for Johnson & Johnson and turned up a remarkably revealing discussion of sexual innuendo and puns that had very little to do with the products the famous medical supply company provides.
  Tip: If Twitter is part of your data, look for and eliminate Twitter handles of people with similar names to your brand.
• Duplicates: The fastest way to find duplicates is to use the “Sort” function in Excel. Sort on date, outlet name, and title and you’ll be surprised at how many duplicates you can quickly identify.
  Tip: Go to Data in Excel and click on “remove duplicates.” You can select the appropriate columns. We typically do it on the item URLs.
• Irrelevant: These include:
  • Press releases that are picked up because the company’s boiler plate says “…has worked with clients such as…” Fix: Do a search for “PRNEWSWIRE,” “PRWEB”  or just “WIRE,” and you’ll be able to quickly identify the press release
  • Underwriting credits: Many of the broadcast clipping services now provide radio, including transcripts from National Public Radio (NPR) as well as individual shows like Marketplace and Living on Earth. Unfortunately they haven’t figured out that those nice message rich statements at the end of the broadcast are in fact paid underwriting credits. They should not count as “earned” media.
  • Foreign interference: Mentions from the UK, Germany, Australia, Canada, France and India in supposedly “US Only” clips. Fix: Look for URL domains like .dk, .fr, .ca  etc and remove.
  • Paid Bloggers: Any blog that has on its home page, “I was paid or compensated to write this” is not earned media. Sort all blogs by frequency and tone. If you get one blogger that is consistently positive and posts regularly, check him/her out. I’m not saying they are all paid, but you’ll want to double check.

2. Bad Content, Part I: Make Sure You Are Getting The Content You Do Want.

The other side of the bad data collection coin is failing to pull in articles that you do want. After more than two decades in the business, I can tell you that you will never get 100% of the items you want. There’s just too much stuff out there to guarantee that you’ll get everything.

But, frankly, everything doesn’t matter. What matters are those stories that are going to reach your target audience and get them to do something – buy your product, change their mind, donate, volunteer, or in some other way benefit your organization. For most people, that means on-message, on-topic stories that appear in key media. So check your data to make sure you are bringing those in. If you can’t find them, go back to your search string and revise it. Then check and revise again and again, until you get the stories you’re looking for.

Tip: It often happens that you know that an article appeared in April, but it is listed in your June results. Fix that by checking to make sure whether your vendor is reporting “day of publication” or “day of collection.”

3. Bad Content, Part II: Clean Up Your Coding.

Let’s assume you’ve refined your content collection, and you now have reasonable confidence that the data you want is actually somewhere in your database or Excel file. Now you need to insure that it is actually coded correctly.

Check Sentiment: If you are using an automated computer-generated sentiment system, the first thing you need to do is to look at a representative sample of the data (about 10%) and see if you agree with how it’s been coded. Ideally you’d have two independent coders review it and then conduct a Krippendorf’s alpha test to make sure it was, in fact, accurate. If you don’t have the time or the resources to do that, at least review the coding. If you don’t agree with 80% of how it’s coded, you will want to go back to the system or the vendor and modify the words the sentiment engine is using to determine positive negative and neutral.

Don’t Get Stuck in Neutral: Chances are that an automated system will find an overwhelming amount of neutral items, since neutral seems to be the default sentiment. Most good systems will learn from any changes you make to it. So if the system allows you to, change the sentiment of the items you don’t agree with and with luck the next time you run the results you’ll have a higher percent of accuracy.

Tune Up Your Tagging: The other source of content errors is bad tagging. Most collection systems use a tagging process to bucket items into subject categories. In theory it’s a great system because a computer can easily find a specific word or “tag.” Too often, however, what the computer thinks is the right tag may not be what you think is the right category. Again, getting the tagging correct is an iterative process that may take several tries to get right.

4. Bad Numbers: Check Your Math.

Ultimately, most dashboards come down to numbers, which means you really need to check the math. Dirty data frequently includes made up “impression” counts and/or multipliers. (Read my story about how one agency used bogus numbers to achieve 10 times more impressions than the population of the Earth(!).)

Tip #1: If you think you have the wrong circulation/OTS numbers, then:

• For online data: First check the URL. Is your system reporting for ALL of Yahoo, or the specific blog in which your news appeared. This happens with Facebook data all the time. The good news is that Twitter actually tells you the number of followers an individual author has. So why do some organizations report the “circulation:” for Twitter of 42 million? Because it’s easier than actually tying the Tweeter to the Tweet.
• For traditional media data: Do a common sense check. Put the data into an Excel spreadsheet and sort by circulation (OTS) largest to smallest. Go to the URL of the top 10 items with the biggest circulation. Are they for real? 

Tip #2: For online news circulation, if you use www.compete.com you will only get the root URL, not the subdomains. So check carefully that the URL corresponds to where the item actually ran. 

Overblown impressions are relatively easy to spot. Bad algorithms or inaccurate correlations are a lot harder to find. Frequently, you can spot them just by noticing when you start scratching your head or yell out “WTF!” If you’ve cleaned up your data to this point in the process, but your analysis numbers don’t make sense, then chances are good that there’s a bad calculation in there somewhere. Do a gut check, drill down into the data, and see why the numbers are giving you agitation.

5. Bonus: Standards Are Mr. Clean For Your Data.

Some of these bad data problems can be reduced or eliminated by the new measurement standards and the tools that these standards include (like the transparency table and sample code book). You’d be foolish to not insist that your vendor comply with the standards. The standards will improve the quality of your data, and save you time, money, and headaches.

The more of us that use the standards, the more the data and analysis will improve across our entire industry. So please sign the standards pledge to show your support. Let’s flush that dirty data down the drain, and get on with the measurement. ∞