Grouping and Aggregation

The one idea: When you combine groups of different sizes, never average their rates or averages together — a big group and a tiny group should not count equally. Add up the raw pieces (all the conversions, all the visitors) and divide once at the end.

⚠️ The trap: "Each segment already has its own conversion rate, so I'll average those rates to get the overall rate." That feels right and it's wrong. Averaging rates hands a 1,000-visitor segment the same vote as a 10,000-visitor one; pool the raw pieces instead — sum the conversions over the sum of the visitors — or you overstate the truth (here 7.5% versus the correct 5.45%).

Almost every business number is an aggregation: revenue by month, conversion by channel, average order value by segment. Grouping is how a million rows become twelve. It feels routine, so it's easy to forget what's actually happening: you are destroying information on purpose. The choices you make about what to keep silently answer questions you may not have meant to ask.

Two of the most common analytical errors in industry live here: the average-of-averages bug and the mismatched-grain aggregate. Both produce clean, plausible numbers. Both have flipped real business decisions.

Grouping changes the grain

A GROUP BY (or pandas groupby) takes a table at one grain and produces a table at another. Group orders by month and the output grain is one row per month. This sounds obvious, but its consequence isn't: every column in the output is now a property of the group, not of any order, and every downstream step inherits the new grain.

SELECT region, COUNT(*)…