Common analysis errors, from double counting to Simpson's paradox

Why this matters

Some analysis errors come up so often that recognising them is a skill in itself. They share an unpleasant feature: each produces a plausible number, with no error message, that is wrong. An analyst who knows the common patterns can spot them in their own work before a reviewer does, and in other people's work before a decision is made on it.

The exam tests these as troubleshooting scenarios: a total that is too high, an average that does not match, a trend that reverses. This lesson covers double counting, averages of averages, Simpson's paradox, errors in the order of operations, and errors in what the data never contained.

The lesson

Double counting after a join

Double counting happens when the same quantity is added more than once, and the most common cause is a join that duplicates rows.

Suppose each order has one row in the orders table and each customer has two addresses in an addresses table. Joining orders to addresses on customer returns every order twice, once per address. Summing order value over the result doubles revenue for every such customer. Nothing looks wrong: the rows are real, the values are real, only the total is inflated.

Double counting also arises from appending the same data twice, from counting a record in two categories that overlap (a customer who is both "new" and "returning" in a badly defined split), and from summing values that are already totals, such as including a subtotal row from a spreadsheet.

The defences are the ones from domain 2: check the row count after every join against what the relationship predicts, check that keys are unique where expected, and compare the total of a key measure before and after each step.

Averages of averages

Averaging several averages gives the wrong overall average whenever the groups are different sizes.

Suppose one region has 100 orders averaging £50 and another has 10 orders averaging £150. The average of the two averages is £100. The true average order value across all 110 orders is (100 × £50 + 10 × £150) ÷ 110, about £59. The average of averages gives the small region the same weight as the large one.

The same error appears when averaging percentages (the mean of each store's conversion rate is not the overall conversion rate), when averaging ratios, and when a dashboard shows a total row that averages the rows above it.

The fix is to go back to the underlying numbers: sum the numerators and sum the denominators, then divide. Where an average of group averages is genuinely what is wanted, for example to give each store equal weight, it should be labelled as such.

Simpson's paradox and aggregated data

Simpson's paradox occurs when a pattern that holds within every group reverses when the groups are combined. It is the most striking version of the aggregation problem, and it is real, not a trick.

Two support teams handle easy and hard tickets:

Team B has the higher resolution rate for easy tickets and for hard tickets, yet the lower rate overall. The reason is the mix: Team B handles mostly hard tickets, which have low resolution rates for everyone, while Team A handles mostly easy ones. The overall figure compares the teams' workloads as much as their performance.

The paradox arises whenever a lurking variable, here ticket difficulty, is related both to the grouping and to the outcome. The lesson is that aggregated comparisons can mislead, and that when groups differ in composition, results should be compared within comparable subgroups, or adjusted for the mix, before drawing conclusions.

Filtering after aggregating, and other order-of-operations errors

The order in which operations are applied changes results, and doing them in the wrong order is a frequent source of error.

• Filtering after aggregating: calculating average order value for all customers and then filtering to one segment gives the overall average, not the segment's. The filter must come before the calculation, which in SQL is the difference between WHERE and HAVING.
• Rounding before summing: rounding each row and then totalling produces a total that differs from the rounded true total.
• Deduplicating after joining instead of before, which can remove legitimate rows along with the duplicates.
• Calculating percentages of the wrong base: a percentage of customers who ordered, when the question was about all customers.
• Converting currency or units at the wrong step, such as applying one exchange rate to a total that mixed several currencies.

Writing the steps of an analysis out in order, and checking each step's output, is the most reliable way to catch these.

Survivorship and other errors in what the data never contained

Some of the most serious errors are not in the data at all, but in what it leaves out.

Survivorship bias is the classic case: analysing only the cases that survived some process. Studying the habits of successful companies ignores the failed companies that had the same habits. Measuring satisfaction among current customers excludes everyone who left because they were dissatisfied.

Related errors include analysing only customers with complete records, when those who left fields blank are different; drawing conclusions from a period that excludes an unusual event, or includes only one; and treating the absence of a record as the absence of an event, when it may only mean nobody recorded it.

The protection is to ask, for any data set, what is not here and why: who or what could have been excluded by how the data was collected, filtered or retained, and would their inclusion change the conclusion? The answer often cannot be fixed with the data available, and it can always be stated as a limitation.