Why Counting Twice Sometimes Matters: Rethinking KPIs with a 'Union All' Mindset

Apr 15, 2026

9 min read

5 views

68%

Hook: Which number do you trust, the unique count or the roar of repeats?

Imagine two dashboards that report the same headline metric: "Acquisitions this month: 1,000." One counts unique users who completed signup. The other counts every completion event, including the 300 people who signed up twice because of a slow page, and the 50 who tested the form repeatedly. Both dashboards are technically true. Both came from the same raw tables. Yet they tell different stories, push different decisions, and reward different behaviors.

This choice about whether to collapse repeated rows into one or to keep every event is not a tiny engineering detail. It is a fundamental, invisible decision about what the organization values: uniqueness or intensity, distinct individuals or cumulative action, neat summaries or messy reality. The way you aggregate data shapes incentives, strategy, and attention. It changes what you optimize for.

In this article I will argue that choosing how to count is a strategic act. The simplest mental tool to make that choice explicit is what I call the "Union All Mindset": treat the moments you might otherwise deduplicate as meaningful signals. When you keep repeated rows, you preserve intensity, frequency, and paths. When you deduplicate, you enforce a different definition of success. Neither approach is uniformly right. What matters is matching the counting approach to the question you want to answer and the behavior you want to encourage.

The hidden tension in every KPI: specificity versus simplicity

Key Performance Indicators, when properly designed, are intended to focus attention on what matters. The popular SMART rubric encourages goals that are Specific, Measurable, Attainable, Relevant, and Timebound. But specificity includes a crucial, often-ignored subquestion: specific about what exactly you count. "Increase conversions by 10 percent" is precise in one sense, but it leaves open how conversions are defined: unique users versus conversion events, first-time conversions versus repeat conversions, or cleaned events after fraud filtering.

When teams pick a KPI they are doing more than tracking. They are implicitly defining the unit of value. That definition creates incentives. If your conversion KPI counts unique users only, growth teams will prioritize widening reach to new people. If your conversion KPI counts every conversion event, the team might optimize for frequency, nudging existing customers to convert more often.

This is the central tension: simplicity and clarity versus fidelity to complex behavior. Deduplicating events produces a cleaner signal that is easier to interpret across time, but it erases intensity. Keeping duplicates preserves behavioral texture, but introduces noise and demands more careful interpretation.

An analogy: think of a crowded bar at closing time. Two reports might be produced. One says the bar served 200 distinct customers tonight. The other says the bar processed 350 drink orders. Both are useful. One helps you plan seating and hygiene. The other helps you staff the bar and manage inventory. If you only look at the first number you may underprepare for peak bartending load. If you only look at the second you may misjudge how many patrons you attracted.

When you should prefer "Union All" thinking: five patterns where duplicates are signal

There are systematic cases where keeping duplicates is essential. Below are five concrete patterns, with short examples, to help you decide when to respect multiplicity rather than collapse it.

Frequency and intensity matter for value

If the business model captures value from repeated actions, count repeated events. A streaming service cares about total hours watched, not only distinct accounts. A media site selling ad impressions values pageviews, not just unique visitors. Counting only unique visitors would understate the resource consumption and revenue impact.

Example: An education app charges by course completion. Tracking total completions reveals engagement and monetization trends. Unique learners is also valuable, but total completions shows productivity and lifetime value.

Operational load is driven by events, not uniques

If infrastructure, fulfillment, or support scale with events, measure events. Shipping labels generated, support tickets filed, payment attempts processed: these are event counts. Collapsing them can hide bottlenecks.

Example: An e-commerce platform that deduplicates orders by user could miss duplicate payment attempts that inflate payment gateway costs and require reconciliation.

Behavioral sequencing and funnels depend on event-level data

Funnels track the path people take through a system. A user might pass through the funnel multiple times. Counting each pass can reveal churn, learning curves, and repeat conversion patterns.

Example: A SaaS trial user may activate, stop, then reactivate. Each activation is meaningful for understanding retention and feature discovery.

Fraud, quality, and error detection often reveal themselves as repeated rows

Repeated identical events can flag bots, retry storms, or system bugs. Keeping duplicates is vital for anomaly detection and root cause analysis.

Example: A sudden spike in identical purchase events from one IP suggests fraud or a malfunctioning webhook, not real growth. Deduplication could mask that signal.

Attribution models need raw multiplicity to fairly apportion credit

Marketing attribution, especially multi-touch models, depends on capturing all touchpoints. Collapsing touches into uniques can produce misleading credit assignment and poor budget decisions.

Example: If a user sees the same ad ten times and converts, counting only unique ad impressions misrepresents frequency effects that may drive conversion.

A simple framework to choose how to count: The Counting Matrix

To make this decision routine instead of accidental, use the Counting Matrix. It is a two-by-two grid that forces you to decide on two axes: whether value accrues to unique actors or each event, and whether operational risk depends on events or uniques. This yields four quadrants with clear guidance.

Axes:

Horizontal axis: Value accrues to: uniques or events.
Vertical axis: Operational risk driven by: uniques or events.

Quadrant A: Value to uniques, risk to uniques

Count uniques for KPI targets, but keep event-level logs for diagnostics. This quadrant includes cases where onboarding unique customers is the primary objective and operational burden is low per event.

Decision: KPI is unique count. Still track events for context.

Quadrant B: Value to uniques, risk to events

You care about unique conversions, but events create operational headaches. Here you must report uniqueness to stakeholders while alerting on event spikes for operations.

Decision: Use deduplicated KPI with event-level monitoring and threshold alerts.

Quadrant C: Value to events, risk to uniques

Events are the monetized unit, but systemic issues could harm unique users if not managed. Examples include platforms monetized per request but sensitive to repeated stressors.

Decision: KPI should be event count. However, instrument deduplicated unique-level KPIs to detect user-facing regressions.

Quadrant D: Value to events, risk to events

This is a full "Union All" territory. You monetize or measure by events, and events create operational burden. Keep events as the primary KPI and build tooling to manage noise.

Decision: Make event totals the KPI and invest in filtering, anomaly detection, and clear definitions to prevent gaming.

Using this matrix forces a conversation about what the business truly values and what the team must monitor to stay healthy.

The Duplicate Signal Test: a short checklist to avoid common traps

Before you define a KPI, run it through this quick checklist. If you answer yes to any item, prefer keeping repeated events in your instrumented data, and make a conscious choice about whether that multiplicity should influence the KPI.

Would collapsing duplicates erase meaningful frequency or intensity information? Example: total sessions, hours, impressions.
Do operational costs or risks scale with events rather than users? Example: API calls, delivery attempts, support tickets.
Could duplicates reveal bugs, retries, or fraud that deserve separate attention? Example: repeated failed payments.
Is the product experience shaped by repetition or cumulative exposure? Example: ad frequency effects, habit formation.
Would reporting only uniques change incentives in harmful ways? Example: encouraging spurious account creation to meet new user targets.

If none of these apply, deduplicating may be fine for the KPI, as long as raw events remain available for analysis. If any apply, treat duplicates as signal and design the KPI and the surrounding monitoring strategy accordingly.

Practical playbook: implementing counting decisions in dashboards and SQL

How do you operationalize this thinking without drowning teammates in data complexity? Here are concrete steps and patterns.

State the counting rule in the KPI name

Include the counting decision in the label. For example: "Total Purchases, event-level" or "New Paying Customers, unique by customer_id." This prevents silent reinterpretation.

Publish both views where they answer different questions

Whenever feasible, expose side-by-side panels: uniques, events, and a ratio of events per unique. The ratio is often the most revealing number because it captures intensity per actor.

Instrument dashboards with alerts on event anomalies

If your KPI is deduplicated, still alert on sudden increases in raw events. A spike in events with no matching spike in uniques is often the earliest sign of churn, bots, or regressions.

Use sampling and rollups to control cost while preserving fidelity

High-volume events can be expensive to store at full fidelity. Use strategic sampling, and compute rollups that preserve distributions such as counts per user bucket. Do not throw away raw event IDs unless you have a retention policy and a forensic plan.

Make the SQL explicit about union semantics

In many analytics stacks the choice between collapsing and keeping duplicates manifests in set operations and aggregation. A simple example illustrates the difference. Suppose you want to combine two event feeds of purchases. If you use a union that removes duplicates you might write something that deduplicates by order ID. If you use a union-all operation, you preserve every row and thus every attempt. Being explicit in your queries keeps the counting decision visible to future analysts.

Audit KPIs periodically for incentives and gaming

Metrics can be gamed intentionally and unintentionally. Schedule a KPI audit every quarter: who benefits from this metric, what behaviors it incentivizes, and whether the counting rule still aligns with strategy.

Key Takeaways

Decide deliberately whether to count uniques or events, because that decision defines value and shapes incentives.
Use the Counting Matrix to map whether value and operational risk live with uniques or events, and let that guide your KPI choice.
When you deduplicate for a KPI, still keep raw event streams and set alerts on event anomalies to catch bugs, abuse, and operational stress.
Label KPIs with their counting rule, publish both uniques and events side-by-side where useful, and track the ratio between them.
Run a Duplicate Signal Test before finalizing KPIs: if frequency, operations, fraud, or behavioral sequencing matter, keep duplicates as signal.

Conclusion: measurement is not neutral; counting is policy

Behind every tidy headline metric there is a policy decision about what counts. Choosing to collapse repeated rows is a vote for simplicity, uniqueness, and perhaps a cleaner scorecard. Choosing to keep every event is a vote for fidelity, intensity, and operational truth. Neither is morally superior. The real failure is when teams make that choice implicitly, leaving incentives misaligned and analysts surprised.

Measurement is not a passive mirror of reality. It is an active lens that highlights some facts and hides others. Choosing whether to keep duplicates is a decision about which facts you will see.

Next time you set a KPI, ask not only whether it is SMART, but whether it is explicit about multiplicity. Say it aloud: are you counting every act, or only distinct actors? Make that choice visible, instrument both views when possible, and design alerts to catch the cases where the two perspectives diverge. That single step will convert measurement from a source of confusion into a driver of clearer, more honest decision making.