Forum Discussion
Helper IICount user sessions not page views
Hello,
I have a log evaluation ("SBB Statistik") of the visits to our website.
Now I want to evaluate not only the individual page views, but also the "sessions". I would define as a "session" if more than 20 minutes pass between two page views (in case of several visits per day). For page views that do not have such a pause in between, it should only count one session.
Fields:
- SessionID (would be the user)
- LogTime
Can anyone help me out here?
3 Replies
- MAwwad
Solution Sage
Create a calculated column in the table that assigns a session ID to each row based on the 20-minute threshold. Here's an example of such a formula:
SessionID = VAR CurrentLogTime = 'SBB Statistik'[LogTime] VAR LastLogTime = CALCULATE( MAX('SBB Statistik'[LogTime]), FILTER( 'SBB Statistik', 'SBB Statistik'[SessionID] = EARLIER('SBB Statistik'[SessionID]) && 'SBB Statistik'[LogTime] < CurrentLogTime - TIME(0, 20, 0) ) ) RETURN IF(ISBLANK(LastLogTime), 'SBB Statistik'[SessionID], 'SBB Statistik'[SessionID] & "-" & ROW('SBB Statistik') - COUNTROWS(FILTER('SBB Statistik', 'SBB Statistik'[SessionID] = EARLIER('SBB Statistik'[SessionID]) && 'SBB Statistik'[LogTime] <= LastLogTime)))This formula checks whether the current LogTime is more than 20 minutes from the last LogTime for the same SessionID. If it is, it creates a new session ID by appending a hyphen and a session sequence number to the original SessionID. Otherwise, it uses the original SessionID. Note that this formula assumes that the LogTime field is sorted in ascending order for each SessionID.
Create a measure that counts the distinct session IDs:
Distinct Sessions = DISTINCTCOUNT('SBB Statistik'[SessionID])This measure simply counts the number of unique values in the SessionID column.
