Data Foundations and Analytics Technologies

Introduction

International businesses make decisions with data about customers, suppliers, orders, currencies, logistics, employees, markets, and regulations. Yet data is not automatically information. It becomes useful only when its meaning, origin, structure, quality, and limitations are understood. A poorly defined country field, an inconsistent currency, duplicate customers, or an unrecorded refund can produce apparently precise but misleading conclusions.

This chapter develops the foundations required for responsible business analytics. It explains the analytics lifecycle, data types and measurement scales, databases and sampling frames, data cleaning and validation, transformation, and introductory use of spreadsheets, SQL, and Python. It also emphasizes reproducibility: another analyst should be able to understand what was done, repeat it, and obtain the same result from the same source data.

Learning Objectives

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

1. Explain the stages of an analytics lifecycle.
2. Distinguish qualitative and quantitative data and classify measurement scales.
3. Describe relational databases, keys, joins, and sampling frames.
4. Identify common data-quality problems and design validation rules.
5. Apply appropriate transformations while preserving meaning and units.
6. Use spreadsheets, introductory SQL, and Python for basic analysis.
7. Interpret outputs critically and document an analysis reproducibly.
8. Recognize ethical, privacy, governance, and cross-border data issues.

1. The Analytics Lifecycle

Analytics is a structured process rather than a single calculation. A practical lifecycle contains the following connected stages:

1. Define the decision problem. Specify the business decision, population, time period, unit of analysis, and success criterion.
2. Acquire and understand data. Identify sources, owners, definitions, access permissions, and collection methods.
3. Prepare data. Profile, clean, integrate, transform, and validate the data.
4. Analyze or model. Use descriptive statistics, visualization, forecasting, classification, or other appropriate methods.
5. Communicate results. Explain findings, uncertainty, assumptions, and implications to the intended audience.
6. Implement and monitor. Put an action into practice and monitor whether the result remains valid.
7. Review and reproduce. Retain data lineage, code, versions, and decisions so that the analysis can be audited or repeated.

The lifecycle is iterative. A missing field discovered during analysis may require returning to data acquisition. A change in the business question may alter the population and sampling frame.

Figure: The analytics lifecycle is iterative; review and monitoring connect each stage.

A sound question is operationally precise. “Which market performs best?” is incomplete. A stronger question is: “Among completed online orders shipped during January–June 2025, which destination country produced the highest median order value in euros, excluding cancelled orders?” The second question identifies population, period, measure, currency, and exclusion rule.

2. Data Types and Measurement Scales

2.1 Qualitative and quantitative data

Qualitative data represent categories or attributes, such as destination country, payment method, or customer segment. Quantitative data represent numerical quantities, such as units sold, revenue, delivery time, or exchange rate.

Quantitative variables may be:

• Discrete: countable values, such as number of items.
• Continuous: measurements that can take values on a continuum, such as weight or delivery time.

A number does not automatically make a variable quantitative in the analytical sense. A customer code such as 10482 is an identifier, not a measured amount.

2.2 Measurement scales

The scale determines which comparisons and calculations are meaningful.

For example, 20°C is not twice as warm as 10°C because Celsius has an arbitrary zero. Revenue of €20 is twice revenue of €10 because revenue has a meaningful zero, subject to the same currency and period definitions.

2.3 Units and semantic meaning

A measure must include its unit and definition. “Sales = 500” is incomplete: 500 what—orders, units, euros, or thousands of euros? A useful data dictionary records field name, definition, type, unit, permitted values, source, and owner.

For a transaction with unit price p and quantity q, gross line value is:

Here, p is currency per unit, q is units, and V is currency. This formula applies only when price and quantity refer to the same product, transaction line, currency, and accounting basis. Discounts, taxes, shipping, and refunds must be handled explicitly.

3. Data Architecture: Tables, Keys, and Relationships

A relational database stores data in tables. A row is a record and a column is an attribute. A primary key uniquely identifies each row. A foreign key stores a value referring to a primary key in another table.

Consider these simplified tables:

Customers

Orders

The relationship is Customers.customer_id → Orders.customer_id. A join is needed to analyze orders by country; country is not a column in Orders.

A one-to-many relationship occurs when one customer can have many orders. Database normalization reduces unnecessary duplication. For example, storing the customer’s country only in Customers avoids repeatedly storing and potentially disagreeing about the country in every order row.

4. Sampling Frames and Representativeness

The target population is the complete group about which a conclusion is intended. The sampling frame is the operational list from which observations are selected. They may differ. A study of all international customers may use a customer database that excludes cash-only customers or inactive accounts. The resulting coverage error limits generalization.

Important distinctions include:

• Census: attempts to observe every population unit.
• Sample: observes part of the population.
• Sampling unit: entity selected, such as customer or order.
• Nonresponse: selected units do not provide usable information.
• Undercoverage: population units are absent from the frame.

For a simple random sample without replacement, every eligible unit has the same selection probability. Stratified sampling divides the population into groups, such as region, and samples within each group. This can improve representation when regions differ materially. Convenience samples are easy to obtain but often have substantial selection bias.

Sampling error concerns random variation from observing a sample rather than a census. Bias is systematic deviation caused by the design, measurement, nonresponse, or coverage. Increasing sample size generally reduces random sampling error, but it does not automatically remove bias.

5. Data Quality, Cleaning, and Validation

Data quality is multidimensional:

• Accuracy: closeness to the true value.
• Completeness: required values are present.
• Consistency: related fields agree.
• Validity: values conform to definitions and permitted formats.
• Uniqueness: records are not duplicated improperly.
• Timeliness: data are available and current enough for the decision.

Cleaning should not silently overwrite evidence. Retain a raw, read-only source; create a processed layer; record each transformation; and flag uncertain corrections.

5.1 Validation rules

A rule must reflect the data-generating process. If refunds are represented as positive quantity with transaction_type = REFUND, then a valid rule is:

quantity > 0 AND transaction_type IN ('SALE', 'REFUND')

The signed impact can be calculated separately:

net_quantity = quantity × sign

where sign = +1 for SALE and −1 for REFUND. Thus, the example refund has quantity 1 but net_quantity −1. This representation avoids claiming that the physical quantity field is negative while still allowing negative net sales. An alternative design may store negative quantities for refunds, but the convention must be documented and applied consistently.

Other useful rules include:

• order_id is unique;
• customer_id exists in Customers;
• order_date is a valid date and is not in the future when future orders are impossible;
• unit_price ≥ 0 for ordinary sales and refunds;
• currency belongs to an approved code list;
• a refund references an original sale where policy requires it;
• country is an approved standardized code rather than unrestricted text.

A constraint violation is not automatically proof that a record is wrong. A future date may be valid for a planned order. Validation should produce an exception report for review.

5.2 Missing values and duplicates

Missingness may be accidental, structurally inapplicable, or informative. Do not replace every missing value with zero: zero means an observed amount of none, whereas missing means unknown or unavailable. Common strategies include deletion, explicit “unknown” categories, imputation, or modeling missingness. The choice depends on the variable and purpose and must be documented.

Duplicate detection should distinguish exact duplicates from legitimate repeated transactions. A repeated customer name is not sufficient evidence of duplication; customer_id, address, contact details, and transaction history may be required for entity resolution.

5.3 Transformation

Typical transformations include parsing dates, standardizing text, converting currencies, aggregating transaction lines, and creating derived variables. A currency conversion requires an explicit rate convention. If the rate is 0.92 EUR per USD, then:

The rate’s date, source, and whether it is a spot, average, or contractual rate must be recorded. Currency conversion is not the same as inflation adjustment; the latter also requires a price index and reference period.

For a positive variable x, a logarithmic transformation y = ln(x) is defined only when x > 0. It can reduce right skew but changes interpretation: a one-unit increase in ln(x) corresponds to multiplying x by e. Transformations should be justified rather than applied automatically.

6. Spreadsheet Analytics

Spreadsheets are valuable for small, transparent analyses, but they are vulnerable to overwritten formulas, inconsistent typing, hidden rows, and undocumented manual edits. Use separate sheets or layers for raw data, cleaned data, calculations, and outputs.

Suppose a worksheet has columns:

If quantity and unit price are positive for both sales and refunds, cell E2 can use:

=IF(B2="REFUND",-1,1)*C2*D2

For a data set with sale values 100 and 80 and a refund value 25, net value is:

=100 + 80 - 25

which equals 155. A pivot table can group net_value by country only after country has been brought into the working table through a validated lookup or database query.

Guided spreadsheet exercise

Enter the following rows:

1. Add a Net Value column using the formula above.
2. Sum the Net Value column.
3. Count the number of transactions.

Expected output: Net values are 60, 30, and −20; net revenue is 70; transaction count is 3. This exercise tests whether the refund convention has been implemented rather than merely described.

7. Introductory SQL

SQL is used to retrieve and summarize data in relational databases. The logical structure of a query is often expressed as:

SELECT grouping_columns, aggregate_function
FROM table
JOIN related_table ON matching_keys
WHERE row_conditions
GROUP BY grouping_columns;

To calculate net revenue by customer country, the correct query must join Customers because country is stored there:

SELECT
    c.country,
    SUM(
        CASE
            WHEN o.transaction_type = 'REFUND' THEN -1
            ELSE 1
        END * o.quantity * o.unit_price
    ) AS net_revenue
FROM Orders AS o
JOIN Customers AS c
    ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01'
  AND o.order_date <  '2025-02-01'
GROUP BY c.country
ORDER BY net_revenue DESC;

The half-open date interval includes January 1 through January 31 for systems storing dates or timestamps and avoids ambiguity at midnight. The expression treats quantity as a positive physical amount and applies the transaction sign. If a database stores refunds as negative quantities, this expression must be changed; applying both a negative quantity and a refund sign would incorrectly turn the refund positive.

For the sample data, Germany has sale revenue 4 × 25 = 100 and refund impact −1 × 25 = −25, giving net revenue €75. Spain has €80. Therefore, Spain ranks first at €80 and Germany second at €75.

Guided SQL exercise

Using the three sample orders and Customers table:

1. Run the query above.
2. Add COUNT(*) AS transaction_count to the SELECT list.
3. Change the date condition to include January and February.

Expected output for January: Spain, €80, 1 transaction; Germany, €75, 2 transactions. The order may depend on the database’s handling of ties, but these values should not change.

A LEFT JOIN is appropriate when analysts need to retain customers with no orders. An INNER JOIN, used above, retains only customers matched to at least one order. Confusing these joins can create coverage errors in reports.

8. Introductory Python

Python supports repeatable data preparation. The following pandas example uses an in-memory table and applies the documented refund convention:

import pandas as pd

orders = pd.DataFrame({
    "transaction_type": ["SALE", "SALE", "REFUND"],
    "quantity": [3, 2, 1],
    "unit_price": [20, 15, 20]
})

orders["sign"] = orders["transaction_type"].map({"SALE": 1, "REFUND": -1})
orders["net_value"] = orders["sign"] * orders["quantity"] * orders["unit_price"]

print(orders["net_value"].tolist())
print(orders["net_value"].sum())

Expected output:

[60, 30, -20]
70

The mapping produces a missing sign for any unrecognized transaction type. A robust script should test for that condition:

allowed = {"SALE", "REFUND"}
unknown = set(orders["transaction_type"]) - allowed
if unknown:
    raise ValueError(f"Unknown transaction types: {unknown}")

This is preferable to silently treating an unknown type as a sale. Python is not inherently more accurate than a spreadsheet; its advantages include automation, testing, version control, and explicit code.

9. Reproducibility and Governance

A reproducible analysis records:

• source files, database tables, and extraction dates;
• data dictionary and business definitions;
• code, formulas, software versions, and package versions;
• cleaning rules, exclusions, and transformation order;
• random seeds when sampling or modeling involves randomness;
• validation results and unresolved exceptions;
• approval, access, and retention decisions.

Reproducibility does not mean that an analysis is correct. It means that the process can be rerun and inspected. Correctness additionally requires valid definitions, suitable data, appropriate methods, and reasonable interpretation.

International businesses must consider privacy law, contractual restrictions, cross-border transfer rules, data minimization, access control, and retention schedules. Personally identifiable information should not be copied into exploratory files without a legitimate purpose. Aggregation, pseudonymization, and role-based access reduce risk but do not guarantee anonymity. Automated systems can reproduce discrimination if historical data reflect unequal treatment. Analysts should test outputs across relevant groups and document limitations.

Generative AI can assist with query drafting, code explanation, and documentation, but generated code must be tested against known outputs. Confidential data should not be submitted to an unapproved service. Human review remains necessary because an apparently plausible query may use the wrong join, date boundary, currency, or population.

10. Illustrative Case: Global Retailer Returns Analysis

Background

A fictional retailer, Meridian Trade, sells consumer goods in Germany, Spain, and Singapore. Management observes that gross sales are increasing but cash receipts are not. It asks analysts to identify the country with the strongest January performance.

Problem

The first extract contains Orders but no country field. Refunds are stored with positive quantities and a transaction_type field. A preliminary spreadsheet incorrectly sums every line as positive revenue and includes duplicate order rows from a failed export.

Analysis

The team defines net revenue as signed quantity × unit price, with SALE positive and REFUND negative. It identifies customer_id as the foreign key and joins Orders to Customers to obtain country. It checks order_id uniqueness, tests permitted transaction types, validates dates, and compares row counts before and after the join.

The January results are:

The calculation is:

This formula is a special case for the stated representation, where all displayed amounts are non-negative magnitudes and refunds are subtracted. If refunds are already stored as negative amounts, the general operation is instead to sum signed amounts without subtracting again.

Approach and outcome

After duplicate removal based on the source’s documented order-line identifier, the team runs the corrected SQL query and reproduces its results in Python. Spain ranks first in January net revenue, although Germany has higher gross sales. Management therefore avoids concluding that Germany is the stronger market merely from gross sales.

Learning points

1. A field must be obtained from the table that contains it or through a valid join.
2. Business definitions must precede calculations.
3. Refund representation must be explicit and consistent.
4. Reconciliation between tools is a quality check, not an optional cosmetic exercise.
5. A result should distinguish gross sales, refunds, and net revenue.

Challenges and Limitations

Data preparation involves judgment. Standardizing “UK,” “United Kingdom,” and “GB” may be correct for reporting but inappropriate if historical legal entities must remain distinct. Removing outliers may eliminate genuine high-value international transactions. Imputation can create apparent precision. Joining tables can multiply rows if the join key is not unique, inflating totals. Aggregation can conceal differences between products, regions, or customer groups. Analysts must therefore inspect distributions, row counts, key uniqueness, and subgroup results before communicating conclusions.

Technology also introduces operational risks: changing source schemas, time-zone differences, API limits, encoding errors, and software-version changes. A professional workflow includes automated tests for row counts, null rates, key uniqueness, permitted categories, and reconciliation totals.

Chapter Summary

Analytics begins with a clearly defined decision problem and proceeds through acquisition, preparation, analysis, communication, implementation, monitoring, and review. Data must be classified by type and measurement scale because these determine meaningful operations. Relational databases organize entities through primary and foreign keys; joins are essential when required attributes reside in different tables. Sampling frames determine who can be selected and therefore affect representativeness.

High-quality analysis examines accuracy, completeness, consistency, validity, uniqueness, and timeliness. Cleaning should preserve raw data, distinguish missing from zero, document transformations, and use validation rules consistent with the data representation. Spreadsheet formulas, SQL queries, and Python scripts can produce equivalent results when definitions and conventions are explicit. Reproducibility requires documented sources, code, assumptions, versions, and validation. Privacy, governance, cross-border regulation, fairness, and human oversight are central to international business analytics.

Key Terms

Analytics lifecycle: Iterative process from problem definition through monitoring and review.

Accuracy: Closeness of a value to the true or accepted value.

Completeness: Degree to which required data are present.

Data dictionary: Documentation of fields, definitions, types, units, and permitted values.

Foreign key: Field referring to a key in another table.

Join: Operation combining rows from related tables.

Measurement scale: Level defining meaningful comparisons and operations.

Nominal: Categorical scale without order.

Ordinal: Ordered categorical scale without guaranteed equal intervals.

Interval: Scale with equal differences but no meaningful absolute zero.

Ratio: Scale with equal differences and a meaningful zero.

Primary key: Field or field combination uniquely identifying a row.

Sampling frame: Operational list from which a sample is selected.

Undercoverage: Population units missing from the sampling frame.

Validation: Testing data against stated rules and constraints.

Transformation: Conversion or derivation that changes data representation or creates variables.

Reproducibility: Ability to repeat an analysis using recorded data and procedures.

Data lineage: Record of a data item’s origins and transformations.

Net revenue: Revenue after applying the documented treatment of refunds and deductions.

Review Questions

Short Answer Questions

1. What is the difference between a target population and a sampling frame?
2. Why is a customer code generally nominal rather than ratio-scale data?
3. What is the purpose of a foreign key?
4. Why should missing values not automatically be replaced with zero?
5. Name four characteristics of data quality.

Long Answer Questions

1. Explain the analytics lifecycle and show why it is iterative rather than strictly linear.
2. Compare nominal, ordinal, interval, and ratio measurement scales with international business examples.
3. Explain how primary keys, foreign keys, and joins support reliable business reporting.
4. Discuss the difference between sampling error, coverage error, and nonresponse bias.
5. Describe a reproducible workflow using a spreadsheet, SQL, or Python.

Analytical / Application Questions

1. An Orders table stores positive quantities for both sales and refunds and includes transaction_type. Design a validation rule and calculation for net quantity. Explain what would change if refunds were stored as negative quantities.
2. A report shows 1,000 orders before joining Customers and 1,240 rows afterward. Give two possible causes and propose diagnostic tests before accepting the report.
3. A manager wants to compare revenue from Germany, Spain, and Singapore using USD, EUR, and SGD transaction data. Specify the data definitions, currency-conversion documentation, validation checks, and limitations you would require before comparison.

Multiple Choice Questions with Answers

Question 1

Which operation is required to obtain country when country is stored in Customers and Orders contains only customer_id?

A. A join between Orders and Customers

B. A sort of Orders by order_id

C. A conversion of quantity to text

D. A deletion of unmatched rows before querying

Correct Answer: A. A join between Orders and Customers

Question 2

A customer satisfaction rating of low, medium, or high is measured on which scale?

A. Nominal

B. Ordinal

C. Interval

D. Ratio

Correct Answer: B. Ordinal

Question 3

If refunds have positive quantity and transaction_type = REFUND, which net value is correct for quantity 1 at unit price €25?

A. €25

B. €0

C. −€25

D. €50

Correct Answer: C. −€25

Question 4

For the sample January data, Germany has a €100 sale and a €25 refund. What is its net revenue?

A. €25

B. €75

C. €100

D. €125

Correct Answer: B. €75

Question 5

Which statement best describes a sampling frame?

A. The complete theoretical population of interest

B. The operational list from which units are selected

C. The final analytical result

D. A database encryption method

Correct Answer: B. The operational list from which units are selected

Question 6

Why is a customer_id usually treated as an identifier rather than a ratio-scale measurement?

A. It cannot be stored numerically

B. Differences and ratios between codes have no business meaning

C. It always contains missing values

D. It is necessarily an ordinal ranking

Correct Answer: B. Differences and ratios between codes have no business meaning

Question 7

Which SQL clause limits rows before grouping?

A. ORDER BY

B. GROUP BY

C. WHERE

D. SELECT

Correct Answer: C. WHERE

Question 8

What output should the guided Python example produce for net values?

A. [60, 30, -20] and total 70

B. [60, 30, 20] and total 110

C. [3, 2, 1] and total 6

D. [20, 15, 20] and total 55

Correct Answer: A. [60, 30, -20] and total 70

Question 9

Which practice most directly supports reproducibility?

A. Replacing the raw file after cleaning

B. Recording source data, code, versions, and transformation rules

C. Hiding exclusions from the final report

D. Relying on memory for spreadsheet changes

Correct Answer: B. Recording source data, code, versions, and transformation rules

Question 10

A LEFT JOIN is especially appropriate when the analyst wants to:

A. Retain customers even when they have no matching orders

B. Remove all customers without orders

C. Convert refunds into sales

D. Guarantee that every join key is unique

Correct Answer: A. Retain customers even when they have no matching orders