How to prevent duplicate counting in event-based reports

When you track status changes as events, your reports can count the same entity more than once. Each update creates a new event, even though all events belong to the same appointment, order, ticket, or other entity.

To report accurately, count each entity once based on its latest recorded status.

Reason for inflated event counts

Event-based reports typically count every event occurrence, not unique entities.

For example, one order can generate several events over time:

• Order placed
• Order paid
• Order shipped
• Order delivered

All of these events share the same order_id, but the report can still count each one separately. As a result, the total number of events can exceed the total number of actual orders, appointments, or tickets.

To fix this, identify the latest event for each entity ID and include only that event in the report.

How to address it 

Step 1: Make sure your events have an ID, status, and timestamp

Before building aggregates and filters, confirm that your events contain at least:

• Entity ID - appointment_id, order_id, subscription_id, ticket_id and more.
• Status field - For example: status = created/confirmed/completed/cancelled, and so on.
• Event timestamp - event_time, created_at, or use a special system event property timestamp

Your data model should look conceptually like this:

In this example, the goal is for the system to understand that:

• For ORDER_123 the final event is the one at 2025-03-02 09:00:00
• The final status is delivered
• Reports should count ORDER_123 exactly once, with status = delivered

Step 2: Create an Aggregate to Store the Latest Timestamp per ID

Next, create an aggregate that finds the distinct timestamp values for each entity ID.

To create this aggregate, follow the approach below based on your data structure:

• Select an aggregate to calculate the distinct values of the event timestamp. 
• Set the maximum number of distinct values (for example, max 1000) to ensure the aggregate can handle a large number of entity IDs.
• Enable the Group events before applying the filter option.
• Group the events by the entity ID (such as appointment_id, order_id, subscription_id, or ticket_id).
• Configure the aggregate to take the value from the last event in each group.

This setup returns the latest event timestamp for each entity ID and stores it in the customer profile as an aggregate attribute. Use this as a reference to identify the entity's final event.

Step 3: Create an event expression with the aggregate.

After creating the aggregate, insert it into an expression and save it for use in the final report.

Step 4: Filter reports to include only final events per entity ID

In your reporting setup:

• Use a count event metric with the desired event, such as appointment.
• Add a filter in the metric where (that expression) any item equals timestamp.
• Drill down by timestamp property in Rows and add status in Columns to view final counts

Result

With this setup:

• Each entity ID contributes one row to the report
• The status shown in the report is the entity's latest status
• Past intermediate statuses no longer inflate the numbers

Common use cases

This approach is useful for tracking an entity's latest status as it changes over time. Common use cases include:

• Appointment status changes 
• Order lifecycle updates
• Subscription status changes
• Ticket lifecycle updates