Counting unique shoppers sounds elementary until billions of events, repeated devices, rolling windows, and dashboard concurrency make exact distinct queries expensive. Approximate algorithms can make exploration faster, but “approximate” is not a harmless implementation detail. It changes what a number can support.
This guide explains how to use approximate distinct counts for ecommerce analysis without turning an estimate into an accounting fact. The worked figures are illustrative. Tool behavior and precision depend on the warehouse function and configuration you actually use.
Table of Contents
- Separate identity error from counting error
- Understand what the warehouse returns
- Choose exact and approximate lanes
- Validate against decision thresholds
- Avoid summing non-additive users
- Govern rolling windows and merges
- Publish uncertainty honestly
- The EcomToolkit view
Separate identity error from counting error
An approximate counter estimates the number of distinct input values. It cannot decide whether two identifiers belong to one person. Cookie resets, login sharing, cross-device activity, consent loss, and guest checkout create identity error before any counting function runs.
If the input key overstates people by 12%, improving the counter’s mathematical error from 1% to 0.5% does not solve the business problem. Name the entity being counted: customer account, checkout email hash, analytics user ID, device ID, or order customer key. “Unique customers” is too strong unless the identity rule supports it.
The customer identity resolution guide covers the upstream joining problem. Approximate counting begins after that identity definition has been chosen.
Understand what the warehouse returns
Google BigQuery documents APPROX_COUNT_DISTINCT as returning a statistical estimate rather than necessarily the exact value, with better performance on very large input. It also exposes HyperLogLog++ functions for sketches when users need explicit partial aggregation and merging. See the official BigQuery approximate aggregate documentation.
The distinction between a ready-made approximate function and a mergeable sketch matters. A dashboard query may only need a fast estimate. A distributed pipeline may need to create sketches by day or region and merge them later. Do not assume every approximate result can be summed or reconstructed.
| Method | Strength | Limitation | Suitable use |
|---|---|---|---|
| Exact distinct | Reproducible exact result for defined input | Can be expensive at scale | Finance, entitlements, small extracts |
| Approximate function | Simple and fast exploration | Precision may not be user-configurable | High-level trend dashboards |
| Mergeable sketch | Efficient distributed combination | Requires lifecycle and version governance | Large recurring aggregates |
Performance is not the only reason to approximate. Mergeable sketches can support fast queries across partitions without materializing every identity. But the saved cost only has value if the output remains fit for the decision.
Choose exact and approximate lanes
Create a policy that assigns metrics to lanes. Exact counts should remain the default for invoices, loyalty thresholds, contractual audience delivery, regulatory reporting, and any workflow that changes an individual customer’s treatment. A one-person discrepancy can matter there.
Approximate counts are often appropriate for exploratory reach, broad audience trends, top-of-funnel dashboards, and large segmentation discovery. A marketing team deciding whether reach is roughly growing can tolerate more estimation uncertainty than a finance team reconciling billed active accounts.
Keep an exact path available for drill-down. The approximate dashboard can identify a segment worth investigation; an exact query can validate that segment before budget or customer action changes. Fast discovery and controlled decision-making are complementary.
Document the switch point. It can be based on row volume, expected query cost, refresh SLA, or decision class. “The warehouse chose” is not a governance rule. Analysts should know whether a visible number is exact or estimated without reading SQL.
Validate against decision thresholds
Validation should compare the approximation with exact counts on representative partitions. Include small and large populations, high-overlap periods, sparse segments, and the grouping patterns used by the dashboard. One global test on a convenient day is not enough.
| Illustrative segment | Exact users | Approximate users | Relative difference | Decision margin |
|---|---|---|---|---|
| All visitors | 1,000,000 | 1,006,000 | +0.60% | 5% trend threshold |
| Returning buyers | 84,000 | 83,300 | −0.83% | 3% campaign threshold |
| VIP buyers | 1,020 | 1,009 | −1.08% | 1-customer entitlement |
| New market | 7,600 | 7,690 | +1.18% | 10% exploration threshold |
In this hypothetical table, approximation is harmless for a broad five-percent trend alert, but inappropriate for a VIP entitlement rule that acts on individual customers. The same relative error can be acceptable in one workflow and unacceptable in another.
Track signed difference, absolute relative difference, query duration, and cost. Validate after warehouse engine changes, sketch-precision changes, identity-key changes, and major data migrations. A reliable policy tests the whole metric pipeline, not the algorithm name in isolation.
Avoid summing non-additive users
Distinct users are not additive across overlapping groups. If 100 people visit on Monday and 100 on Tuesday, the weekly total is not necessarily 200. The same problem applies to channels, devices, countries, and product categories.
Never add approximate daily counts to create an approximate monthly count. Build the monthly estimate from the underlying identities or merge compatible sketches using the documented function. Summing estimates combines overlap error with estimation error.
This is especially important for platform comparisons. A customer may use web, app, marketplace, and store channels. Channel reach totals can each be valid while their sum overstates the customer population. Present channel counts as overlapping sets unless the model makes them mutually exclusive.
Ratios inherit the problem. Purchase frequency calculated as orders divided by approximate customers carries uncertainty in the denominator. If the movement is small, verify it using exact counts before explaining it as changed customer behavior.
Govern rolling windows and merges
Rolling 30-day unique buyers are operationally useful and computationally awkward. Each day both adds identities and expires old ones. A sketch designed only for union does not necessarily support subtraction. Recomputing the window from compatible daily sketches can work, but only if the sketches share the required precision and implementation.
Store metadata with every sketch: algorithm, precision, library or warehouse version, input-key definition, partition window, and creation time. Refuse incompatible merges. Silent coercion can make a fast metric impossible to audit.
Backfills require care. Rebuilding one daily partition may change every rolling window that includes it. Mark affected dashboards as provisional until downstream windows are recomputed. This links approximate counting to the same restatement discipline used for late order and refund data.
For cohort retention, the cohort membership denominator should be frozen under a declared identity policy. If historic sketches are regenerated after identity stitching changes, label the series as restated rather than pretending the past remained unchanged.
Publish uncertainty honestly
Label approximate values in the metric definition and interface. A subtle tooltip is useful, but the title or metadata should also reveal the method where precision matters. Do not format an estimate with unnecessary single-unit precision merely because the function returns an integer.
Set escalation rules. For example, when an alert is within twice the validated approximation difference of its threshold, run the exact query before notifying operators. This creates a review zone around consequential decisions.
Do not promise a generic error rate unless the platform documents it for the function and configuration you use. Your observed validation difference is evidence about tested datasets, not a universal guarantee. Keep the exact comparison history so users can see whether error is stable across scale and segment.
Finally, separate estimator uncertainty from pipeline completeness. An approximate count of incomplete events can be computationally precise and operationally wrong. Show source freshness, late-data status, and identity-definition version beside the estimate.
The EcomToolkit view
Approximate counting is valuable when it buys speed for a decision that can tolerate estimation. It becomes dangerous when dashboards hide the method, teams sum overlapping counts, or approximate reach silently drives exact customer treatment. Put the method, validation, and decision threshold in the same contract.
If your audience totals change between dashboards and nobody can explain whether identity, overlap, or approximation caused the gap, request an EcomToolkit audit to define an auditable counting policy.
