Work with User Sessions
The concept of a “user session” is central to many product’s analytics. User sessions may encompass the number of actions someone takes, how long someone is spending in the product, or what actions lead to others. You can think of it as period of uninterrupted activity from a single user.
Different applications define user sessions differently. Some applications define the end of a user session as the application being closed. Chat apps may have a user opening and closing the application several times, all be in one user session. Lack of actions may define user sessions, even if the application is technically open the whole time. Some tools automatically define a user session for you, but there are cases that you’ll want to use raw event data to define your user sessions.
It's beneficial to define a user session manually, and have the logic of that easily exposed.
Define a User Session
In this example, you define users based on a unique identifier, in this case their email. Using Sigma parameters, you set up a variable to determine the time between actions that will count as a new sessions.
Create a Workbook Parameter
Create a new parameter. For instructions see Workbook Parameters.
Name it [Minimum Time Between Sessions (m)] and set the value at 10. You can easily update this number at any time.
Find the Start of a User Session
To do this analysis, your data needs two things: a unique user identifier, and time stamps for actions.
This example looks at the gaps between actions for each user. When the gap is more than what is set as the minimum time between sessions, the next set of actions will count as a new session.
First, set up the data for analysis.
- Organize your data based on user. Create a new grouping defined by your unique user identifier, such as a user ID number or email.
- Organize the timestamp data. Sort [Timestamp] ascending, which will put the first user events at the top of the list, and the most recent events at the bottom.
After you set up the data, you can start analyzing it.
Calculate the Time Between Each of the Events
Create a new column to calculate the time between events, and name it [Time Between Events (m)]. Using the DateDiff and Lag functions, you can calculate the time between the current event and the event right before it.
DateDiff(“minute”, Lag([Timestamp]), [Timestamp])
- minute: Indicates that the difference between the two dates to be reported is measured in minutes.
- Lag([Timestamp]): Puts the previous row’s timestamp as the first date in the DateDiff function.
- [Timestamp]: Puts the current row’s timestamp as the ending date in the DateDiff function. This provides the time in minutes between the current row’s timestamp and the previous row’s timestamp.
The value for the first row of every group will be null because the Lag function is constrained to the windows defined in the User grouping.
Identify the Start of Each Session
Create a new column named [Start Session]. Use the If function to determine which values to populated the new column with.
Set up the logic that defines what counts as a new session. Use the parameter [Minimum Time Between Sessions (m)] in the logic to determine if a timestamp is the beginning of a session. If the time between events is more than [Minimum Time Between Sessions (m)], count that timestamp as the first of a new session.
How the time between sessions is calculated means that the very first value for “time between sessions” will be null for each group. If the value is null, print the value for [Timestamp].
If([Time Between Events (m)] > [Minimum Time Between Sessions (m)], [Timestamp], IsNull([Time Between Events (m)]) = True, [Timestamp])
The function will create a column where there is a value contained in every row that has a Timestamp that is the beginning of a user sessions, and a null value in all the other rows.
Map Each Event to Its Session
After you have a column with the timestamp values that start each session, map the values to every event in the session.
Use the FillDown function to print the the start of session time to every event in the user session. FillDown replaces a column’s null values with the closest prior non-null value. Create a new column [Start of Session].
FillDown([Start Session])
Group by [Start of Session] to organize the data into user sessions.
To assign each session a session number, use the RowNumber function. Create a new column [User Session Number] on the level that is grouped by [Start of Session].
RowNumber([Start of Session])
Calculate Average Session Length
When you have the start time of each session, you can calculate several different metrics.
To calculate the length of a session, find the difference between the first and the last timestamp in each session. Create a new column [Length of Sessions (s)] under the grouping defined by [Start of Session]. Then calculate the length in seconds.
DateDiff(“second”, [Start of Session], Max([Timestamp]))
When you know the length of each session, you can aggregate the [Length of Sessions (s)] column to find the average length for each user. Create a new column under the grouping defined by [User Email].
Avg([Length of Session (s)])
After you create a workbook table like this, you can build all types of analysis on top of it. For example, you can create aggregates to count types of actions in a session, and create cohorts of users based on the number of user sessions.