topic Re: first 5 pages visited post login in Dashboards & Visualizations

first 5 pages visited post login

dbcase — Mon, 04 Dec 2017 16:33:43 GMT

Hi,

I need to display a pie chart of the first 5 pages visited in an app POST login. Example: User 1 logs in at 1pm and views the home page and then goes to the settings page, then to the account page, etc etc.

Below is the query to get the login event

index=wholesale_app buildTarget=blah product=product1 analyticType=SessionStart

And this query get the pages visitied

index=wholesale_app buildTarget=blah product=product1 Activity Properties.index=5 |convert num(Properties.args{}) as activityvalue|stats values(activityvalue) as activityvalue by clientSessionId|eval activitytype=case(activityvalue == "0", "Home Page", activityvalue == "1", "Rules Page", activityvalue == "2", "History Page",activityvalue == "3", "Settings Page",activityvalue == "4", "Camera Page",activityvalue == "5", "Paring (Manage Devices) Page",activityvalue == "6", "Third-Party Card Page",activityvalue == "7", "Cloud Page",activityvalue == "8", "Custom Page")|stats count by activitytype

How can I only get the user activities POST login?

Re: first 5 pages visited post login

doweaver — Mon, 04 Dec 2017 17:44:32 GMT

index=wholesale_app buildTarget=blah product=product1 Activity Properties.index=5
| sort 0 _time
| streamstats count AS PageNumVisited BY clientSessionId
| search PageNumVisited<=5
| top activitytype

What's happening here:
- Sorting the events by _time, increasing (oldest events first)
- Using streamstats to tag the event with the order in which it was visited (the first page will get 1, the second page will get 2, etc.)
- Filtering out pages beyond the first five
- Using top to see the top pages visited after filtering out anything beyond the first 5

Re: first 5 pages visited post login

dbcase — Mon, 04 Dec 2017 17:50:32 GMT

ah yea that would probably be helpful 🙂 yes it is clientSessionID

Looking at your query that would get pages visited but not necessarily the first 5 pages post login, correct?

Re: first 5 pages visited post login

doweaver — Mon, 04 Dec 2017 17:56:29 GMT

Oh! I meant "streamstats", not "eventstats". Updating my original answer (and to incorporate the sessioni identifier)

What that will do is "count" the pages post login, so the first page they visit will be the first event for that clientSessionId, and will get PageNumVisited set to 1, the second set to 2, etc. By filtering out anything above 5, you should filter out the sixth pages visited and beyond.

Re: first 5 pages visited post login

dbcase — Mon, 04 Dec 2017 21:23:54 GMT

Here is what I have, the query "works" but I don't think the results are right....

Here is the query

 index=wholesale_app buildTarget=blah product=Product1 Activity Properties.index=5
     | sort 0 _time
     | streamstats count AS PageNumVisited BY clientSessionId |convert num(Properties.args{}) as activityvalue|eval activitytype=case(activityvalue == "0", "Home Page", activityvalue == "1", "Rules Page", activityvalue == "2", "History Page",activityvalue == "3", "Settings Page",activityvalue == "4", "Camera Page",activityvalue == "5", "Paring (Manage Devices) Page",activityvalue == "6", "Third-Party Card Page",activityvalue == "7", "Cloud Page",activityvalue == "8", "Custom Page")| search PageNumVisited<=5|top activitytype

And here are the results

activitytype    count   percent
Settings Page   49  29.166667
Camera Page 49  29.166667
Home Page   42  25.000000
Rules Page  16  9.523810
History Page    12  7.142857

The thing I would expect is the home page would be #1 as when you login the first place you go is the home page.

Re: first 5 pages visited post login

dbcase — Mon, 04 Dec 2017 21:25:40 GMT

I'm not clear how the query is performing the below

User 1 logs in then goes to the first 5 pages

User 1 has logged in already and we are collecting the pages he / she has visited during the session

Re: first 5 pages visited post login

doweaver — Mon, 04 Dec 2017 22:26:13 GMT

User 1 has logged in already and we are collecting the pages he / she has visited during the session

I didn't realize this was a possible distinction. Someone can save their login, or something, so they can have a new session without logging in?

Re: first 5 pages visited post login

dbcase — Tue, 05 Dec 2017 15:53:32 GMT

Sorry no, lemme try to explain better

Lets say we want to run this query for the last 24 hours (from 9 am yesterday to 9am today)

If someone logged in at 8:45am yesterday and was going from page to page for 1 hour, we wouldn't see the login (since it happened before our time window) but we would see the pages the user went to from 9am to 9:45 am. Those pages would not be one of the first five they went to and since we didn't see the login event they should be excluded.

Does that help?

I've been thinking about this and I think we need to proceed something like the below pseudo code

Run a query that gets the login event and capture the clientSessionID
Then use the list of clientSessionIDs to query for the first 5 pages visited

Does that make sense?

Re: first 5 pages visited post login

dbcase — Fri, 08 Dec 2017 17:26:10 GMT

finally figured it out

index=wholesale_app buildTarget=* product=* Activity Properties.index=5 [search index=wholesale_app buildTarget=* product=* analyticType=SessionStart|dedup clientSessionId|table clientSessionId]| convert num(Properties.args{}) as activityvalue|sort 0 _time
 | streamstats count AS PageNumVisited BY clientSessionId

 |eval activitytype=case(activityvalue == "1", "Rules Page", activityvalue == "2", "History Page",activityvalue == "3", "Settings Page",activityvalue == "4", "Camera Page",activityvalue == "5", "Paring (Manage Devices) Page",activityvalue == "6", "Third-Party Card Page",activityvalue == "7", "Cloud Page",activityvalue == "8", "Custom Page")
 | stats count by activitytype