Determining how many visits before an action is taken

[brettcave]

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?

[cphair · Accepted solution]

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

[brettcave]

thanks. using stats with the "values" function is the key to getting the correct data.