Data Cleaning in Survey and Market Research

Introduction to Data Cleaning

Imagine you're at a carnival with fun games to play to win prizes. However, each game is a bit disorganized—pieces are missing, instructions are unclear, and some are outright broken. The fun quickly fades as the chaos hampers your chances of winning.

This scenario is much like working with unclean survey data in market research. Just as organizing the games would bring back the fun, data cleaning, or data cleansing, can transform problematic data into a reliable and valuable asset.

In the world of survey research and market research, the raw data collected often resembles our carnival mess. It's got “bad” respondents (e.g., fraudulent or inattentive respondents), inconsistent data, outliers, missing entries, and duplicates.

Data cleaning is a systematic approach to refining this data so that you can get more accurate insights.

By the end of this article, you'll understand why clean data is crucial, the steps involved in the cleaning process, and how to tackle common challenges. So, let's dive into the carnival of data and turn it into a playground of insights.

What Is Data Cleaning?

Data cleaning, also known as data cleansing or data scrubbing, is the process of detecting and correcting (or removing) fraudulent, noisy (such as from inattentive respondents), outlier data points, or inaccurate records from a dataset. The primary goal is to ensure that the data used for analysis is accurate, consistent, and reliable. In the context of survey research and market research, data cleaning is so important as it directly impacts the quality of insights derived from the data.

In survey research, data cleaning might involve correcting typos in responses, handling missing data, dealing with outliers, and ensuring that the responses are within a logical range. 

Online (computerized) surveys can resolve a great deal of data cleaning concerns ahead of time compared to paper-based surveys. For instance, if a survey asks for a respondent's age and someone inputs "200," you could program survey software to only accept numeric input within reasonable ranges. Using a computerized survey also avoids the possibility that a data coder types wrong information when inputting responses from a paper-based survey.

The importance of data cleaning isn't confined to survey research. It spans across various industries such as healthcare, finance, and retail. 

In healthcare, accurate data is crucial for patient records and research studies. In finance, data cleaning helps in maintaining accurate transaction records and compliance reports. Retailers use clean data to understand customer behavior and optimize inventory management.

In this article, we’ll focus mainly on data cleaning issues in survey research.

Common issues addressed by data cleaning in survey research include:

• Inaccurate data entries: Typographical errors, or data entered by mistake (“fat finger” errors).
• Missing data: Gaps where questions were skipped, data wasn't collected or recorded.
• Bad/fraudulent respondents: Bad actors (whether human or bots), including human respondents who are inattentive or answering near-randomly just to finish the survey.
• Inconsistent responses: respondents who (for example) answer very differently when they are asked the same question in two different ways; or respondents in conjoint or MaxDiff questionnaires who get a low consistency statistic from HB utility estimation.
• Inconsistent data formats: Variations in data formats or categorizations.
• Outliers: Numeric values so far from the typical response that it would greatly affect measures such as means, variances, statistical tests of significance, and correlations (e.g., a question about household income where the respondent indicates 100 million dollars.)

By addressing these issues, data cleaning ensures that the dataset is reliable and ready for analysis, leading to more accurate and actionable insights.

Quality of the Sample

Different sources of data, such as customer records, convenience samples (including “river sampling”), or online panel sample, will lead to very different quality of data. The amount of data cleaning and incidence of bad respondents can vary dramatically. In our experience:

1. Customer records often lead to cleaner data than other sources, especially if customers are engaged in the product category and have positive feelings toward your brand.
2. Panel data often leads to 20% to 50% of respondents who need to be discarded as “bad” records (due to speeding, inconsistencies, randomness, or outright fraud).
3. The greater the incentive you pay to complete the survey, the greater the likelihood of fraud. We’re aware of studies paying incentives of $80 or more per completed record in which the majority of completed records were from bots or survey farms of professional respondents who pose as real respondents (even pose as doctors or IT professionals) for the purpose of earning incentives.

Importance and Benefits of Data Cleaning

Data cleaning is not just a preliminary step; it is critical to the data analysis workflow. The importance and benefits of data cleaning in market research and beyond cannot be overstated. Here’s why:

Improved Data Accuracy and Reliability

The primary benefit of data cleaning is the improvement in data accuracy and reliability. Clean data ensures that the insights derived from it are based on near error-free information. For example, if your dataset has 40% bad respondents, or if the survey responses contain numerous errors or inconsistencies, the resulting analysis could lead to faulty conclusions (including the potentially false conclusion that there are no differences between items or respondents), impacting business strategies and decisions.

Data-Driven Decision-Making

Organizations rely heavily on data-driven decision-making. Clean data provides a solid foundation for these decisions, ensuring that they are based on precise and trustworthy information. For instance, a market research firm using clean survey data can more accurately gauge consumer preferences, leading to better product development and marketing strategies.

At Sawtooth Software, we often advise on the analysis of conjoint data. It is well known that failure to remove bad/random respondents through data cleaning can lead to exaggerated estimates of willingness to pay for product features.

In market segmentation analysis, failure to remove random/inattentive respondents can lead to finding a market segment via cluster or latent class analysis that seem to be average on all the basis variables.

Cost Reduction through Error Prevention

Errors in data can lead to costly consequences. Incorrect data can result in flawed reports, misguided strategies, and wasted resources. By investing in data cleaning, organizations can prevent these errors and save significant costs in the long run. 

Steps in the Data Cleansing Process

The data cleansing process is a systematic approach that ensures your dataset is accurate, complete, and ready for analysis. Many of these steps should be taken both at soft launch (e.g., interviewing 30 respondents and then pausing to assess the preliminary data) and with the final data set. Here’s a breakdown of the essential steps involved in data cleansing (with a focus on survey research):

1. Design Multiple Quality Checks into Your Survey

Upstream steps, prior to data collection, will help you greatly in terms of data cleaning as you design the survey, to include:

• “Gotcha” (trap) questions, repeated questions (such as age asked as a numeric entry early in the questionnaire but as a drop-down selection of dates of birth, or asking the same question twice where the response scale is flipped the second time it’s asked.
• Strategic open-ends (“explain in detail what this survey was about”).
• Honeypot questions (questions that only bots can see and answer on the page, such as using white-on-white text or a tiny font unreadable by humans).
• Captcha-like questions, where the respondent is asked to interpret an image that would be difficult for AI-powered bots to process.
• MaxDiff questions where each item is shown at least 3 times to each respondent (the consistency score from HB analysis can very reliably identify random and near-random response patterns)

2. Identifying Speeders and Bad Respondents

The first step in the data cleansing process is to inspect and profile (e.g., multiple-strike score) your dataset to identify bad respondents, based on multiple criteria such as speeding, straightlining, inconsistency in “gotcha” questions, poor fit statistic in MaxDiff, and open-end responses that look like gibberish or that seems carefully composed by AI. 

You can sort respondents by time to complete the survey. It’s common to throw away respondents who have answered the survey less than 25% to 40% as long as the median time to complete (different researchers favor different cutoffs). 

It’s also recommended not to just rely on a single criterion to identify bad respondents (for example, a “2 strikes and you are out” criterion). 

3. Identifying Inconsistencies and Outliers

Data profiling involves analyzing the dataset's structure, content, distributions, and relationships to detect anomalies. This step helps in understanding the data's overall health and pinpointing areas that need attention. For example, in a survey dataset, profiling might reveal missing responses, outliers, or duplicates that need to be addressed.

4. Cleaning Erroneous Data Entries

Once errors are identified, the next step is to clean the erroneous data entries. This can involve correcting typos, standardizing data formats, removing invalid entries, or dealing with outliers in numeric data such as via Winsorization, which could involve replacing all values greater than the 90th percentile with the 90th percentile value. These steps often requires domain knowledge and contextual understanding to make accurate corrections.

5. Handling Missing Data

Dealing with missing data is a crucial aspect of data cleaning. There are several strategies to handle missing data, including:

• Imputation: Replacing missing values with estimated ones based on statistical methods or patterns in the data.
• Deletion: Removing records with missing values if they represent a small portion of the dataset and do not significantly impact the analysis.
• Substitution: Using default or placeholder values where appropriate.

Choosing the right approach depends on the nature of the data and the specific analysis requirements.

6. De-duplication of Data Entries

Duplicate records can skew analysis results and lead to incorrect conclusions. De-duplication involves identifying and removing duplicate entries to ensure each data point is unique. For example, if a respondent accidentally submits a survey multiple times, de-duplication will ensure that only one response is retained.

7. Verification of Data Cleanliness

After cleaning, it’s important to verify the cleanliness of the data. This involves running validation checks to ensure that all errors have been addressed and the data is now accurate and consistent. Verification might include cross-referencing cleaned data with original sources or running automated checks using data validation tools.

8. Reporting on Data Cleaning Steps

The final step is to report on the data quality steps and logical rules made during the cleaning process. This involves documenting the types of errors found, the steps taken to clean the data, and any remaining issues. Regular reporting helps in tracking improvements over time and identifying recurring issues that need to be addressed.

By following these steps, you can ensure that their data is clean, accurate, and ready for reliable analysis, leading to better decision-making and more valuable insights.

Data Cleaning Tools

Data cleaning can be a time-consuming and complex task, but the right tools can significantly streamline the process. Here are some popular data cleaning tools and techniques that can help ensure your data is accurate and ready for analysis:

Manual vs. Automated Data Cleaning

• Manual Data Cleaning: This involves using basic tools like spreadsheets (e.g., Microsoft Excel or Google Sheets) to manually inspect, correct, and clean data. While this approach is flexible and allows for a detailed examination, it can be time-intensive and prone to human error.
• Automated Data Cleaning: Automated tools use algorithms and predefined rules to clean data quickly and accurately. Such tools could involve code written in Python, SQL, or R. Automation can handle repetitive tasks such as de-duplication, standardization, and validation more efficiently.

Data Cleaning in Excel

Excel provides several features and functions that make it suitable for data cleaning:

• Conditional Formatting: Highlighting cells that meet certain conditions, such as duplicate entries or outliers.
• Data Validation: Ensuring data meets specific criteria before it’s entered, such as restricting values to a certain range.
• Text Functions: Functions like TRIM, CLEAN, and SUBSTITUTE help clean up text data by removing extra spaces, non-printable characters, and unwanted substrings.
• Power Query: An Excel add-in that provides advanced data import, transformation, and cleaning capabilities. Power Query can automate repetitive cleaning tasks and handle large datasets more efficiently than standard Excel functions.

Conclusion

Data cleaning, also known as data cleansing or data scrubbing, is essential for ensuring the accuracy and reliability of your data. Clean data forms the foundation for accurate analysis, reliable insights, and informed decision-making. The data cleaning process addresses common issues such as inaccuracies, inconsistencies, and missing data, making your datasets ready for thorough analysis.

Recap of the Importance of Data Cleaning

• Improved Data Accuracy and Reliability: Ensures your analysis is based on accurate and trustworthy information.
• Data-Driven Decision-Making: Supports precise decision-making and strategic planning.
• Cost Reduction Through Error Prevention: Prevents costly errors and corrections by addressing issues early.

Implementing data cleaning processes is crucial for any organization relying on data for decisions. The future of data cleaning is being shaped by trends like AI and machine learning, real-time data cleaning, cloud-based solutions, and enhanced data governance. Staying updated with these trends can help maintain high data quality standards.

In conclusion, investing in data cleaning is vital for maintaining the integrity and utility of your data. Embrace comprehensive data cleaning practices, leverage the right tools, and stay ahead of trends to ensure your data is always clean, accurate, and ready for action. Clean data is the key to unlocking valuable insights and driving organizational success.