Analyze User Sessions Based on Click Activity

Analyzes user sessions based on users click activity using the session() function

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} 1{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> result
logscale
groupBy(cookie_id, function=session(maxpause=15m, count(as=clicks)))
| sort(clicks)

Introduction

The session() function can be used to group related events into sessions.

A session contains events that occur within a specified time interval. By default, this interval is 15 minutes. You can modify this interval by setting the maxpause parameter.

The session() function then calculates aggregate values across all events in each session.

In this example, the session() function is used to analyze user sessions based on users click activity. The session() function groups events by a given timespan.

Example incoming data might look like this:

timestampcookie_idaction_typepage_urluser_agent
2025-05-15 05:30:00user123pageview/homeMozilla/5.0 (Windows NT 10.0; Win64; x64)
2025-05-15 05:30:15user123click/productsMozilla/5.0 (Windows NT 10.0; Win64; x64)
2025-05-15 05:30:30user123click/product/item1Mozilla/5.0 (Windows NT 10.0; Win64; x64)
2025-05-15 05:31:00user123click/cartMozilla/5.0 (Windows NT 10.0; Win64; x64)
2025-05-15 05:31:30user123click/checkoutMozilla/5.0 (Windows NT 10.0; Win64; x64)
2025-05-15 05:35:00user456pageview/homeMozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)
2025-05-15 05:35:30user456click/aboutMozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)
2025-05-15 05:36:00user456click/contactMozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)
2025-05-15 05:38:00user789pageview/homeMozilla/5.0 (iPhone; CPU iPhone OS 14_0)
2025-05-15 05:38:30user789click/productsMozilla/5.0 (iPhone; CPU iPhone OS 14_0)

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} 1{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    groupBy(cookie_id, function=session(maxpause=15m, count(as=clicks)))

    Groups events by the field cookie_id (unique user identifier) and creates sessions with 15-minute inactivity timeout (the default value of the maxpause parameter), then makes a count of each event in a session returning the result in a new field named clicks.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} 1{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | sort(clicks)

    Sorts the results by number of clicks (default is descending order).

  4. Event Result set.

Summary and Results

The query is used to analyze user sessions based on the users click activity. The query is useful, for example, to identify most/least active user sessions, detect potential automated behavior or just to understand user engagement levels.

Sample output from the incoming example data:

cookie_idclicks
user1235
user4563
user7892

Note that each row represents an event (either pageview or click).