Data Aggregation

Data aggregation is the gathering of data from several places into one dataset that can be analyzed together — campaign results from three ad platforms, prices from competitor sites, product records from separate systems. The word is used in a second sense too, for collapsing many rows into a summary such as a total or an average, and the two meanings are worth keeping apart because they involve different risks.

In a spreadsheet, gathering usually means lookups: each source contributes columns to a table keyed on something shared. That makes the quality of the key the quality of the aggregation. A lookup configured to return the first match can ignore other rows with the same key. If those rows are separate transactions that should all contribute to a total, the lookup understates that total; if they are conflicting versions of one record, the problem is choosing the appropriate version. An unmatched key also needs an explicit rule: an error, blank, or replacement value carries a different meaning into later calculations.

Sources rarely agree by default, and the disagreements are decisions rather than defects. One system may identify a customer by number and another by email; one may split an address across fields another keeps whole; one may call a segment “Young Professional” where another says “Student Associate”. Each of those requires choosing a representation before any formula runs, and the choice changes the result rather than merely its appearance.

So an aggregated table should arrive with its record: which sources it draws on, how each was keyed, what an unmatched row means, how recently each source was refreshed, and which cleaning standard was applied to each. Without that, the merged sheet presents choices as facts — and the check that catches most of it is arithmetic, comparing row counts and matched counts before and after rather than trusting a table that looks complete.

References:

• How to correct a #N/A error in VLOOKUP
• Top ten ways to clean your data