Quick answer
Data preparation is the process of collecting, cleaning, transforming and validating raw data so it is accurate, consistent and structured for analysis, reporting or machine learning.
What is data preparation?
Data preparation is the work of turning raw data into a dataset you can trust and analyse: collecting it from its sources, cleaning errors, transforming it into a consistent structure and validating the result. It comes before every chart, report and model, and it decides whether their numbers are right.
In the CRISP-DM process model, data preparation is a phase of its own between understanding the data and modelling. In interviews with enterprise analysts, Kandel and colleagues (2012) found that discovering, wrangling and profiling data took up much of analysts' time and was one of their main frustrations.
Why it matters
Most wrong numbers in a meeting are not caused by bad statistics. They come from preparation problems: test orders counted as revenue, the same customer counted twice, dates in different time zones, refunds treated as sales. A careful analysis on badly prepared data is still wrong.
The steps, with what to check
| Step | What you do | Checks |
|---|---|---|
| 1. Define the question | Write the metric and its definition | Which rows count? Which date? Which currency? |
| 2. Collect | Pull data from each source | Row counts match the source; date range complete |
| 3. Profile | Look at every column | Missing values, distinct values, min/max, unexpected types |
| 4. Clean | Fix or remove bad rows | Duplicates, test and internal data, invalid values, outliers (how to handle them) |
| 5. Transform | Reshape for the analysis | Units and currencies, time zones, derived fields, joins, aggregation |
| 6. Validate | Compare with a trusted total | Revenue matches finance within a known gap; counts match the dashboard |
| 7. Document | Record what you did | Filters, exclusions and definitions in a data dictionary |
The cleaning techniques themselves are covered in more depth in data cleaning techniques.
Tidy data
Hadley Wickham's "tidy data" rules describe the target shape for most analysis:
- Each variable is a column.
- Each observation is a row.
- Each type of observational unit is a table.
An export with one column per month ("Jan", "Feb", "Mar") is not tidy: the month is a variable spread across columns. Reshaping it to month, value rows makes filtering, charting and joining straightforward.
A worked example: average order value
Sample data: a monthly export of 10,000 orders with $642,000 in revenue, giving an average order value of $64.20.
| Preparation step | Rows | Revenue | AOV |
|---|---|---|---|
| Raw export | 10,000 | $642,000 | $64.20 |
| Remove 312 test orders ($10 each, from the staging coupon) | 9,688 | $638,880 | |
| Remove 145 duplicate orders (double submits, $65 each) | 9,543 | $629,455 | $65.96 |
| Flag 88 refunded orders (kept, marked) | 9,543 | $629,455 |
The test orders were small and the duplicates were average-sized, so cleaning raised AOV by $1.76 (2.7%). Had the duplicates been left in, revenue would have been overstated by $9,425 that finance never received. Refunds were flagged rather than removed so the analysis can report gross and net separately.
Data preparation for machine learning
Machine learning adds three steps: labelling the outcome you want to predict, splitting data into training and test sets before any transformation that learns from the data (otherwise information leaks from test to training), and encoding categories and scaling numbers so the algorithm can use them.
Common mistakes
- Deduplicating on the wrong key, such as email instead of order ID, which merges real separate orders.
- Mixed time zones. A UTC server log joined to a local-time order table shifts late-evening orders to the next day.
- Silent type changes. Spreadsheets turn product codes like
00123into123and long IDs into scientific notation. - Filtering without recording it. Six months later nobody knows why the numbers differ from the dashboard.
- Leakage in models. Using a field that is only known after the outcome (such as "cancellation reason" to predict churn).
Tools
Spreadsheets for small, one-off work; SQL for anything that lives in a database; Python (pandas) or R for repeatable analysis; dbt or similar for transformations that run on a schedule. Whether you transform before or after loading is the ETL vs ELT choice.
Preparing data in TapCub
The cheapest preparation is the problem that never enters the data. TapCub moves several checks to collection time:
- Validation rules in All-platform analytics › Data › Property dictionary: expected type, required, regex, length, min/max and allowed values, each set to warn, drop the property or block the event.
- Typed properties: each key is String, Number, Boolean, Datetime or List, so sums and filters do not break on mixed types.
- Virtual events and properties define or rename behaviour after the fact, with a backfill of up to 365 days.
- Exclusion rules keep internal and test traffic out; known bots are counted separately from visitors.
When you still need a notebook or warehouse, export raw data or query it with SQL.
Sources
- Wickham, H. (2014). Tidy Data. Journal of Statistical Software, 59(10).
- Kandel, S. et al. (2012). Enterprise Data Analysis and Visualization: An Interview Study. IEEE Transactions on Visualization and Computer Graphics, 18(12).
- Chapman, P. et al. (2000). CRISP-DM 1.0: Step-by-step data mining guide. SPSS.
Was this explanation clear?
We use your answers to fix and expand this page.
Couldn’t send. Please try again in a minute.
Thanks for the feedback. We’ll use it to improve this page.You’ve already sent feedback on this page. Thanks.
Product Experience Specialist
Writes about product experience, practical workflows, and growth, turning complex processes into clear steps for everyday decisions.