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 DB table has the following structure:
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
last_activity_time, it will sum activities that were considered the same visit.
User goes to
example.com/page1and spends 3 minutes there.
User goes to
example.com/page2and spends 1 minutes there.
User takes a break, then goes back to
example.com/page1after 10 minutes and spends another 5 minutes there.
It means that if I simply take the total time between
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:
Total visit time table ("session"):
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.