32.4 Estimated unique counts: HyperLogLog

Counting different values, such as distinct users in an hour, cannot be done with a running total. An exact count must remember every value it has seen, so it grows with the data. The estimated unique count uses the HyperLogLog algorithm instead: a fixed-size summary, at most 64 KB per interval, with a standard error of about 0.8 %, whether an interval saw a hundred values or a hundred million.

A unique count does not add up. The number of different users in a day is not the sum of the numbers per hour, because the same user in two hours is one user for the day. So a unique count, exact or estimated, has no total over a range, and its graph shows one value per interval. Ask at the size you mean: for distinct users per day, draw the graph with one point per day.

Which unique count to use:

• Count per value for a column with few distinct values that you want to see by name.
• Unique count when the number must be exact and stays in the thousands per interval.
• Unique count, estimated for anything that can grow: users, sessions, paths, search terms.