How to count sessions that have a specific event within a session using a metric?

Occasionally, you may need to count sessions that contain a specific event, such as a purchase or cart_update. While these events logically occur within a session, they aren't natively "joined" to session_start or session_end events in the database. In some cases, a cart_update might even appear on a profile without a corresponding session event.

This article demonstrates how to accurately count only those sessions where a target event (for example, cart_update) occurred between the session_start and session_end.

The logic

We want to identify sequences where:

session_start.timestamp < cart_update.timestamp < session_end.timestamp

Setup

Because running aggregates look backward from the event being analyzed, we'll use the session_end event as our anchor point and look back to see if a cart_update occurred after the most recent session_start.

1. Expression: Create an expression (named, for example, cart_update session_start) that subtracts the Last session_start timestamp from the Last cart_update timestamp.

   1. Running Aggregate (Last cart_update): Create a running aggregate that finds the timestamp of the last cart_update.

      

   2. Running Aggregate (Last session_start): Create a running aggregate that finds the timestamp of the last session_start.

      

2. Metric: Create a metric to count session_end events, where your Expression value is > 0.
   
   

Summary

This solution works by evaluating the timeline backwards from each session_end:

• If the result is positive (> 0): The cart_update has a higher (more recent) Unix timestamp than the session_start. It confirms the event happened after the session began but before the session_end being analyzed. The metric will count this instance and add it to the total.

• If the result is negative (< 0): The cart_update timestamp is older than the session_start, meaning the event happened during a previous session or outside of one. The metric will ignore these instances.