Data Collection, Preparation and Quality

Introduction

Business analytics is only as reliable as the data on which it is based. A sophisticated dashboard, forecasting model or artificial-intelligence system can produce misleading results when its data is incomplete, inaccurate, duplicated, outdated or collected in a biased manner. Data collection and preparation are therefore not merely technical activities performed before analysis; they are central components of analytical reasoning and professional decision-making.

The data lifecycle provides a structured way to manage data from its initial acquisition to its use, maintenance and eventual disposal. In this chapter, the lifecycle is applied to five closely related activities: acquiring data, integrating data from different sources, cleaning and transforming data, validating its quality, and documenting decisions. The chapter also examines missing values, duplicate records, outliers, bias, governance and the practical use of spreadsheets, databases and analytical software.

A useful principle is:

Data preparation should preserve the meaning of the original data while making it suitable, traceable and fit for the intended decision.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain the main stages of an analytics data lifecycle.
2. Select appropriate primary and secondary data sources.
3. Integrate data from multiple systems while managing keys, formats and grain.
4. Identify and address missing values, duplicates, inconsistent values and outliers.
5. Apply suitable data transformation and validation techniques.
6. Evaluate data using the dimensions of accuracy, completeness, consistency, timeliness, validity and uniqueness.
7. Recognise sampling, measurement and process biases.
8. Apply professional, ethical and governance principles to data preparation.

1. The Analytics Data Lifecycle

The analytics data lifecycle is a sequence of activities through which data is planned, collected, stored, prepared, analysed, used and managed. In practice, the lifecycle is iterative rather than strictly linear: findings may reveal a data problem, requiring the analyst to return to collection or preparation.

The main stages are:

1. Define the business question. Identify the decision, users, scope, measures and required level of detail.
2. Plan data requirements. Specify variables, sources, time periods, population, frequency and quality thresholds.
3. Acquire data. Collect or obtain data from operational systems, surveys, sensors, APIs, files, public sources or commercial providers.
4. Store and protect data. Apply access controls, backups, retention rules and appropriate security.
5. Integrate data. Combine datasets using common identifiers, mappings and carefully defined relationships.
6. Profile and assess quality. Examine structure, distributions, missingness, duplicates, anomalies and metadata.
7. Clean and transform data. Correct errors, standardise formats, handle missing values, create derived variables and reshape data.
8. Validate and document. Test rules, reconcile totals, record assumptions and obtain business-owner review.
9. Analyse and communicate. Use the prepared data for descriptive, diagnostic, predictive or prescriptive analytics.
10. Monitor and maintain. Track changes in data sources, quality and business definitions over time.

The first stage is especially important. A dataset can be technically clean but unsuitable for a question. For example, monthly sales data cannot answer a question about individual customer journeys unless the required customer-level events have also been collected.

2. Data Sources and Collection

2.1 Primary and secondary data

Primary data is collected for a specific current purpose. Examples include customer surveys, interviews, experiments, observations and newly deployed sensors. Primary data can be closely aligned with the research question, but collection may be costly and affected by non-response or measurement error.

Secondary data was collected previously for another purpose. Examples include accounting records, government statistics, CRM data, web traffic logs and industry reports. It is often less expensive and available quickly, but its definitions, coverage and quality may not match the current need.

A data source should be evaluated before use. Important questions include:

• Who collected the data and for what purpose?
• What population and time period does it represent?
• What do the fields mean, and what units are used?
• How were observations sampled or recorded?
• What known limitations, revisions or exclusions exist?
• Are use, privacy and licensing conditions satisfied?

2.2 Collection methods

Common collection methods include:

• Transactional systems: point-of-sale, enterprise resource planning and payment systems.
• Customer and employee systems: CRM, human-resource and service-management platforms.
• Surveys: structured questions administered online, by telephone or in person.
• Web and application logs: records of visits, clicks, searches and events.
• Sensors and devices: machine, location, environmental or operational readings.
• APIs and external files: data supplied by partners, regulators or public institutions.
• Manual entry: spreadsheets, forms and administrative records.

Each method introduces possible errors. Manual entry may produce typographical mistakes; sensors may drift; surveys may suffer from non-response; and logs may record activity without revealing the user’s intention.

2.3 Sampling and representativeness

When it is impractical to observe an entire population, a sample is selected. A probability sample gives units a known, non-zero probability of selection and supports stronger generalisation. A convenience sample is easier to obtain but may systematically exclude important groups.

Sampling bias occurs when the observed sample differs from the target population in a way relevant to the analysis. For example, a customer survey distributed only through a mobile application may underrepresent customers who do not use the application. Increasing the sample size does not automatically remove this bias.

The analyst should define:

• the target population, or all units about which conclusions are intended;
• the sampling frame, or the operational list from which the sample is drawn;
• the unit of analysis, such as a customer, order, product or day; and
• the selection process, including exclusions and response rates.

3. Data Structures, Grain and Integration

3.1 Structure and grain

Before combining tables, determine the grain: what one row represents. A sales table may contain one row per order line, while a customer table contains one row per customer. Joining these tables is usually appropriate through a customer identifier, but joining two tables without understanding their grain can multiply records and inflate totals.

Suppose an order has three product lines and the analyst joins it to a table containing two promotional records for the same order. A many-to-many join may produce six rows instead of three. Revenue calculated after this join could be double-counted.

3.2 Keys and relationships

A primary key uniquely identifies a record within a table. A foreign key stores an identifier referring to a record in another table. A reliable integration requires compatible keys, clear relationship cardinality and agreed definitions.

Common integration difficulties include:

• customer numbers stored as text in one system and numbers in another;
• different spellings of product or location names;
• changed identifiers after a system migration;
• different time zones or date conventions;
• one system recording gross sales and another recording net sales;
• historical records using definitions that have since changed.

A reference or mapping table can translate local codes into standard codes. Fuzzy matching may help identify similar names, but proposed matches should be reviewed because similar names do not necessarily represent the same entity.

3.3 Extract, transform and load

An extract, transform and load process obtains data from source systems, transforms it into an agreed structure and loads it into a target such as a data warehouse. Some organisations use extract, load and transform, in which raw data is first stored and transformed later. The choice depends on processing capacity, governance requirements and the need to preserve raw records.

A robust pipeline normally retains:

• the original source data where lawful and appropriate;
• a transformed or standardised layer;
• a curated analytical dataset;
• metadata describing fields, sources, transformations and dates;
• logs showing when processes ran and whether they succeeded.

4. Data Profiling and Quality Dimensions

Data profiling is the systematic examination of data structure, content and relationships before analysis. Profiling may include row and column counts, data types, distinct values, minimum and maximum values, frequency distributions, missingness, duplicate identifiers and referential integrity.

Data quality is multidimensional. A dataset can be strong on one dimension and weak on another.

Quality must be judged against purpose. A postcode may be unnecessary for a product-level inventory report but essential for geographical delivery analysis.

For a set of required fields, a simple completeness rate can be calculated as:

The numerator counts values present and usable according to the stated rule; the denominator is the total number of required field instances. For example, if 9,600 of 10,000 required values are present, completeness is 96%. This measure does not prove accuracy: a field can be filled with an incorrect value.

A missingness rate is:

These measures should be calculated by field, time period, source and relevant population group, because an overall average can conceal serious local problems.

5. Cleaning and Preparing Data

Cleaning should be rule-based, reproducible and documented. Analysts should avoid silently changing values because every correction can affect results.

5.1 Data types and formats

Correct data types prevent invalid operations. Dates should be stored as dates rather than arbitrary text; numerical measures should be separated from currency symbols; categorical fields should use controlled values; and identifiers that contain leading zeros should generally be stored as text.

Standardisation may include:

• converting dates to a consistent calendar and time zone;
• using a single unit, such as kilograms rather than a mixture of kilograms and pounds;
• trimming unnecessary spaces;
• standardising case where appropriate;
• mapping equivalent categories, such as “UK”, “U.K.” and “United Kingdom”;
• separating combined fields, such as “city, country”, when analysis requires separate variables.

Standardisation must not remove meaningful distinctions. For example, changing all text to lowercase may be harmless for email addresses in some contexts but inappropriate for case-sensitive identifiers.

5.2 Missing values

A missing value may mean different things: not collected, not applicable, refused, unknown, lost during transfer or not yet available. These meanings should not automatically be represented by the same code. A zero is also not equivalent to missing. Zero sales means a measured absence of sales; a missing sales value means the amount is unknown or unavailable.

Common approaches are:

1. Deletion. Remove records or fields when missingness is limited and removal will not materially bias the analysis.
2. Simple imputation. Replace missing numerical values with a mean or median, or categorical values with a mode. This is easy but can reduce variation and distort relationships.
3. Group-based imputation. Use a typical value within a relevant store, region, product or customer group.
4. Model-based imputation. Estimate values from other variables. This requires careful validation and should not be confused with observed facts.
5. Missingness indicators. Add a flag showing that a value was missing, especially when missingness itself may carry information.
6. Leave missing. Some algorithms and analyses can handle missing values, but the implications must still be understood.

The correct choice depends on the mechanism and pattern of missingness. If dissatisfied customers are less likely to answer a satisfaction survey, replacing their missing responses with the average can make service quality appear better than it is.

5.3 Duplicates

Duplicates may be exact copies or near-duplicates. They can arise from repeated file loads, retries in an application, multiple data-entry events or legitimate repeated transactions.

A duplicate procedure should:

1. define the intended unique event or entity;
2. identify the relevant key or combination of fields;
3. count exact and suspected duplicates;
4. investigate timestamps, source systems and update indicators;
5. retain, merge or remove records according to documented rules.

Removing every repeated customer name, for example, would be incorrect because one customer may legitimately place many orders.

5.4 Outliers and anomalies

An outlier is an observation unusually distant from the rest of a distribution. It may be a data-entry error, a valid exceptional event or evidence that the data combines different populations.

For approximately symmetric numerical data, a standardised score is often written as:

where x is an observed value, μ is the population mean and σ is the population standard deviation. In a sample, analysts commonly use the sample mean and sample standard deviation instead. The score expresses distance in standard-deviation units. A large absolute score can flag an observation, but it is not proof of an error. The method is sensitive to extreme values and is less suitable for strongly skewed data.

For skewed data, the interquartile range method is often useful:

where Q₁ and Q₃ are the first and third quartiles. A common screening rule flags values below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a detection rule, not an automatic deletion rule.

Possible responses include correcting a confirmed entry error, retaining a genuine event, transforming a highly skewed variable, analysing segments separately or reporting results with and without the observation as a sensitivity check.

5.5 Transformation and derived variables

Transformation changes representation while preserving or clarifying analytical meaning. Examples include:

• aggregating daily transactions into weekly totals;
• converting revenue into a common currency;
• calculating profit as revenue minus relevant costs;
• creating a customer tenure variable from start date and reference date;
• encoding categories for a statistical model;
• reshaping data from wide to long format.

Every derived field should have a definition, source fields, calculation logic, unit and refresh date. A formula can be technically correct but conceptually wrong if its inputs do not share the same time period or population.

6. Validation and Quality Assurance

Validation tests whether data satisfies explicit expectations. It should occur at source, during integration and after transformation.

Typical validation rules include:

• Type checks: a date field must contain valid dates.
• Range checks: quantity cannot be negative unless returns are represented by negative quantities.
• Domain checks: order status must belong to an approved list.
• Uniqueness checks: invoice identifier must be unique where the business rule requires it.
• Referential checks: every order customer identifier must exist in the customer table.
• Cross-field checks: delivery date cannot precede order date.
• Reconciliation checks: the sum of detailed transactions should agree with a trusted control total, allowing for documented exclusions.
• Freshness checks: the latest successful load must meet the agreed reporting schedule.

A quality report should show the rule, threshold, result, exception count, responsible owner and corrective action. Automated tests reduce repetitive work, but business review remains necessary because software cannot determine every semantic issue.

A practical validation sequence is:

1. inspect metadata and data types;
2. profile missingness, uniqueness and distributions;
3. test field-level and relationship rules;
4. compare totals with source systems;
5. inspect exceptions and samples manually;
6. obtain domain-owner sign-off;
7. record unresolved limitations and their analytical consequences.

7. Bias, Ethics and Governance

Data quality is also a social and organisational issue. Bias can enter through collection, measurement, labels, sampling, historical decisions and missing data.

• Coverage bias occurs when some members of the target population are absent from the sampling frame.
• Non-response bias occurs when participation is related to the subject being measured.
• Measurement bias occurs when a variable is recorded systematically inaccurately.
• Historical or institutional bias occurs when past decisions reflect unequal treatment and the data reproduces it.
• Algorithmic or selection bias occurs when automated processes produce different error rates or opportunities across groups.

Analysts should examine performance and missingness across relevant groups, question whether a variable is a fair proxy, and avoid using sensitive attributes casually. Fairness assessment does not mean that every group must have identical outcomes in every context; it means that the purpose, legal requirements, decision consequences and error patterns must be examined responsibly.

Governance includes ownership, access control, data lineage, retention, versioning, incident management and accountability. Privacy principles generally require a legitimate purpose, data minimisation, appropriate security, transparency and retention only as long as necessary. Applicable laws vary by jurisdiction, so analysts should consult organisational policy and legal specialists, particularly when handling personal, financial, health or employee data.

A useful distinction is between de-identification and complete anonymity. Removing names may not prevent re-identification when several indirect attributes can be combined. Access should therefore be limited according to need, and sensitive extracts should be protected.

8. Technology and Contemporary Practice

Spreadsheets remain useful for small, controlled datasets and exploratory checks, but they create risks involving version confusion, manual formulas and weak audit trails. Relational databases support structured storage and constraints. Data warehouses support consistent reporting, while data lakes can retain varied raw data but require strong metadata and governance.

Modern data-quality tools can automate profiling, schema checks, lineage and pipeline monitoring. Machine-learning systems can assist entity matching, anomaly detection and imputation. However, automation may amplify errors when training data is biased or when a probabilistic match is accepted without review. Human oversight, threshold setting and exception sampling remain essential.

Contemporary organisations increasingly use data contracts: explicit agreements between data producers and users about schemas, definitions, quality expectations and change procedures. Data observability extends monitoring beyond pipeline failure to freshness, volume, distribution changes and unusual null rates. These practices support reliable analytics in environments where data changes continuously.

9. Applied Case: Retail Returns Analytics

Background

The illustrative case concerns Meridian Retail, a fictional chain that wants to understand why product returns increased. Managers combine point-of-sale transactions, an online order system, warehouse records and a customer survey.

Problem

The first combined report shows that return rates rose from 8% to 14%. However, the analyst notices that online orders appear twice in the integrated table, product codes differ between systems, and survey responses are concentrated among customers who contacted support.

Analysis

The analyst first defines the grain: one row should represent one fulfilled order line. The online system contains an order-line identifier, but a repeated file load created duplicate records. A staging-layer uniqueness test identifies 12,000 repeated rows. Product codes are mapped through an approved reference table. The analyst also discovers that warehouse return dates are recorded in local time while online orders use coordinated universal time, causing some returns to fall in the wrong reporting day.

The survey is not treated as representative of all customers because dissatisfied customers are more likely to contact support. It is used to generate hypotheses rather than to estimate the population-wide satisfaction rate.

Approach and solution

The team removes only duplicates confirmed by the order-line identifier and load history. It standardises product codes and time zones, preserves a raw copy, and creates a curated table. It validates that every return refers to an existing fulfilled order line, that return dates do not precede shipment dates, and that product-level totals reconcile with the warehouse control report. Missing return reasons are reported separately rather than coded as “no reason.”

Outcome

After preparation, the return rate is 10.5%, not 14%. The remaining increase is concentrated in one product category and began after a packaging change. The business investigates packaging rather than attributing the entire increase to customer behaviour.

Learning points

The case demonstrates that integration errors can create a false trend, that grain must be defined before joining data, that missing values require interpretation, and that biased survey responses should not be presented as population estimates. It also shows how data-quality work can materially change managerial action.

10. Best-Practice Checklist

Before releasing an analytical dataset, ask:

• Is the business question and unit of analysis explicit?
• Are source, owner, period and definitions documented?
• Has the grain of every table been checked?
• Are keys, joins and relationship cardinalities understood?
• Have missing values, duplicates and outliers been investigated?
• Are units, dates, categories and currencies standardised appropriately?
• Have automated validation and reconciliation tests passed?
• Are limitations, assumptions and unresolved exceptions recorded?
• Has potential bias been assessed across relevant groups?
• Are privacy, access, retention and licensing requirements satisfied?
• Can another analyst reproduce the preparation process?

Chapter Summary

Reliable business analytics begins with a clearly defined question and a data requirement that matches the intended decision. The analytics data lifecycle connects acquisition, storage, integration, profiling, cleaning, transformation, validation, analysis and monitoring. Understanding data grain, keys and relationships is essential when combining sources because incorrect joins can create duplicate or inflated results.

Data quality includes accuracy, completeness, consistency, timeliness, validity, uniqueness and integrity. Missing values must be interpreted rather than automatically replaced; duplicates must be distinguished from legitimate repeated events; and outliers should be investigated rather than mechanically deleted. Transformations and derived variables should be documented with their definitions, units and assumptions.

Validation combines automated rules, reconciliation, exception review and business-owner judgement. Bias may enter through sampling, non-response, measurement, historical records or automated processes. Ethical and professional practice therefore requires privacy protection, appropriate access, transparency, lineage and critical evaluation of representativeness. High-quality preparation is not a clerical precondition to analytics: it is a substantive part of producing valid evidence and responsible decisions.

Key Terms

Data lifecycle: The stages through which data is planned, collected, stored, prepared, used, maintained and retired.

Primary data: Data collected directly for a specified current purpose.

Secondary data: Data originally collected for another purpose and reused.

Target population: The complete group about which an analysis intends to draw conclusions.

Sampling frame: The operational list or source from which a sample is selected.

Unit of analysis: The entity represented by one analytical observation.

Grain: The precise meaning of one row in a dataset.

Primary key: A field or field combination that uniquely identifies a record.

Foreign key: A field that refers to an identifier in another table.

Data profiling: Examination of data structure, content, distributions and relationships.

Completeness: The extent to which required data is present.

Accuracy: The extent to which data represents the real-world attribute correctly.

Consistency: Agreement of values and definitions across records or systems.

Timeliness: The extent to which data is current and available when needed.

Validity: Conformity to permitted formats, ranges and business rules.

Duplicate: A repeated record or event that violates the intended uniqueness rule.

Outlier: An observation unusually distant from the distribution of other observations.

Imputation: Estimation or replacement of a missing value.

Data lineage: A record of data origins, transformations and destinations.

Data bias: Systematic distortion arising from collection, measurement, sampling, historical or processing practices.

Review Questions

Short Answer Questions

1. What is meant by the grain of a dataset, and why must it be identified before joining tables?
2. Distinguish primary data from secondary data.
3. Name four dimensions of data quality and define each briefly.
4. Why is a missing value not necessarily equivalent to zero?
5. What is the purpose of a referential-integrity check?

Long Answer Questions

1. Explain the main stages of the analytics data lifecycle and show why the lifecycle is iterative.
2. Discuss the risks of integrating data from multiple systems with different identifiers, dates and definitions.
3. Compare deletion, simple imputation, model-based imputation and retaining missing values as approaches to missing data.
4. Explain how duplicates and outliers should be investigated and treated in a professional analytics project.
5. Discuss the relationship between data quality, privacy, governance and responsible business decision-making.

Analytical / Application Questions

1. A company joins a customer table to an order-line table and reports that revenue has doubled. Explain the likely cause, identify diagnostic checks and propose a corrected approach.
2. A customer satisfaction survey is completed mainly by customers who contacted support. How could this create bias, and how should the survey be used in decision-making?
3. A dataset contains 4,800 present values out of 5,000 required values. Calculate the completeness rate, then explain why this figure alone cannot establish data accuracy or fitness for purpose.

Multiple Choice Questions with Answers

Question 1

Which concept describes what one row in a dataset represents?

A. Data lineage

B. Grain

C. Referential integrity

D. Imputation

Correct Answer: B. Grain

Question 2

A customer identifier appears twice because the same file was loaded twice. Which quality dimension is most directly affected?

A. Uniqueness

B. Timeliness

C. Accuracy

D. Validity

Correct Answer: A. Uniqueness

Question 3

Which action is most appropriate before joining two tables?

A. Delete all repeated customer names

B. Convert every field to text

C. Determine each table’s grain and relationship cardinality

D. Replace all missing values with zero

Correct Answer: C. Determine each table’s grain and relationship cardinality

Question 4

A field contains 9,600 non-missing required values out of 10,000. What is its completeness rate?

A. 0.96%

B. 9.6%

C. 96%

D. 104.2%

Correct Answer: C. 96%

Question 5

Why should a missing sales value generally not be coded as zero?

A. Zero is always invalid in sales data

B. A zero records no sales, whereas missing means the amount is unknown or unavailable

C. Missing values can only occur in categorical fields

D. Zero automatically creates duplicate records

Correct Answer: B. A zero records no sales, whereas missing means the amount is unknown or unavailable

Question 6

Which test checks that every order refers to an existing customer?

A. Range check

B. Freshness check

C. Referential-integrity check

D. Distribution check

Correct Answer: C. Referential-integrity check

Question 7

What is a major limitation of automatically deleting every outlier?

A. Outliers are always missing values

B. A valid exceptional event may be removed

C. Deletion increases data completeness

D. Outliers cannot occur in numerical data

Correct Answer: B. A valid exceptional event may be removed

Question 8

A survey excludes customers without access to a mobile application. What type of problem may result?

A. Coverage bias

B. Duplicate loading

C. Unit conversion error

D. Referential integrity

Correct Answer: A. Coverage bias

Question 9

Which statement best describes data lineage?

A. A list of all missing values

B. A measure of sample size

C. A record of data origins, transformations and destinations

D. A method for deleting duplicates

Correct Answer: C. A record of data origins, transformations and destinations

Question 10

A dataset has 4,800 present values out of 5,000 required values. Which result is correct?

A. Completeness is 4.8%

B. Completeness is 48%

C. Completeness is 95%

D. Completeness is 104.2%

Correct Answer: C. Completeness is 95%