I am trying to determine the number of visits a user makes before a certain action takes place in a report. I have a visitorId, a sessionId and classification of event types, but I am not sure how to determine this.
VisitorId="x" SessionId="a" uri="/"
VisitorId="x" SessionId="b" uri="/login"
VisitorId="x" SessionId="c" uri="/login"
VisitorId="x" SessionId="c" action="updateProfile" <-- logged via a different mechanism
VisitorId="x" SessionId="d" uri="/about"
In the example, a visitor has had 4 sessions. They updated their profile during their 3rd session.
I was thinking of using a transaction by VisitorID, with an endswith being eventtype=UpdateProfile (the event type being defined as having an action of updateProfile), but cannot do a distinct mvcount on SessionId. How could I go about determining the number of distinct sessionIds by visitorId before a certain event takes place?
If SessionIds are unique per host, something like this might work. This assumes action is an explicit field, but if it isn't, you might search on _raw instead. Also it only identifies the first updateProfile occurrence; if you want to find them all I can try tweaking this.
... transaction VisitorId,SessionId | eval keyevent=if(isnotnull(mvfind(action,"updateProfile")),SessionId,null) | stats earliest(keyevent) as earliest, values(SessionId) as sessions by VisitorId | eval num=mvfind(sessions,earliest) | eval num=if(isnotnull(num), num+1,null) | table host, earliest, sessions, num
If SessionIds are unique per host, something like this might work. This assumes action is an explicit field, but if it isn't, you might search on _raw instead. Also it only identifies the first updateProfile occurrence; if you want to find them all I can try tweaking this.
... transaction VisitorId,SessionId | eval keyevent=if(isnotnull(mvfind(action,"updateProfile")),SessionId,null) | stats earliest(keyevent) as earliest, values(SessionId) as sessions by VisitorId | eval num=mvfind(sessions,earliest) | eval num=if(isnotnull(num), num+1,null) | table host, earliest, sessions, num
thanks. using stats with the "values" function is the key to getting the correct data.