I need to be able to track users' time spent on the app and on each page (based on URL).
I have a visits
table that logs the users' page visits (by URL) and time spent.
The time spent is done using JavaScript that tracks activity on the page.
The DB table has the following structure:
id | user_id | page_url | visit_start_time | last_activity_time |
---|
I decided that an active visit session is considered 15 minutes. So for example if a user switches between 3 URL's and make activity on each of them with no breaks longer than 15 minutes, I update the last_activity_time
. Otherwise, I create a new record for the URL.
It works well. But the problem with this, is that I can't get the total time a user spent on the app.
Because if I just sum all the records for a a user between visit_start_time
and last_activity_time
, it will sum activities that were considered the same visit.
Example scenario:
User goes to
example.com/page1
and spends 3 minutes there.User goes to
example.com/page2
and spends 1 minutes there.User takes a break, then goes back to
example.com/page1
after 10 minutes and spends another 5 minutes there.
It means that if I simply take the total time between visit_start_time
and last_activity_time
it will be 19 minutes for example.com/page1
and 1 minute for example.com/page2
, which means total of 20 minutes. But that's not true because they overlap, so they should be considered the same activity, which should then sum up to 19 minutes.
I assume I need a completely different logic for that? Or, I could just add another column - visit_session_id
which will be some unique identifier. It will be set by the first visit that's made in the last 15 minutes and passed to other page URLs as long as this visit is active. Could that work?
Update: maybe add a separate table for the visits instead, with the visit id:
Page visits table:
id | user_id | page_url | page_visit_start_time | page_last_activity_time | visit_id |
---|
Total visit time table ("session"):
id | session_start_time | session_last_activity |
---|
Any tips would be helpful.
Eventually I need to track how much a user spent on each page, and the total time on the app. I can completely change the table/logic if necessary.