1

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:

  1. User goes to example.com/page1 and spends 3 minutes there.

  2. User goes to example.com/page2 and spends 1 minutes there.

  3. 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.

3 Answers 3

2

I can't get the total time a user spent on the app.

And you never will. You don't have any data that shows a user spending time on the app. You have data that shows a user making URL requests.

What you can track is activity. How time is spent between activities is completely unknown. You will never know if the user was reading frantically or had wandered off to the bathroom. Not unless you've hacked the web cam and are tracking eye movement.

What you need is a way to represent the data that is both useful and stores efficiently.

Consider a bar graph:

11:45 ***
12:00 *
12:15
12:30 ****

That shows the shape of an hour of activity. You don't know if a bathroom break was involved there but if it was it likely happened around 12:15. Or they could have been intensely studying the new privacy policy.

All you need to build that graph is 8 time stamps. If you want to be hyper efficient about storing them: one user, on one URL, has many time stamps.

Done this way the 15 minute window you talked about isn't something you have to bake into the data. You can tune the time when presenting the data. Not when gathering it.

The fundamental principle here is you record what happened. Not what you think about what happened. Gather data this way and it is far more useful because what you think can change over time.


Regarding Update:

Ever been told "store birthdays, not ages"? You will have to overwrite total_visit_time.session_last_activity every time a new page_visits record is added. Sure this can't be solved with a query or stored procedure? It's a bit redundant.

You seem determined to design this to be lossy. Be sure that isn't a premature optimization.


Regarding Comment:

Your "15 minutes" is what's known as a tuning variable. You need real world data to tune it to a good number.

When doing that it's better to gather complete data and work from that than to try a guess and fail because a failed guess gives you useless data which forces you to go gather data again for your next guess. With complete data you can tune over and over on the same data and see what you get.

So if you must do this in a lossy fashion, do it after tuning this variable to a good value.


How would you store it? - Stackerito

To get complete data I'd try this:

url

id page_url

visit

id user_id url_id timestamp

 

To get lossy data I'd try this:

visit

id user_id url_id session_id timestamp

session

id

Done this way you can prune visits down to two per page, per user, per session (first and last) with simple row deletes. But you don't have to until you need to.

12
  • Thank you, this makes sense.I think that for starters, I will completely change that for only timestamps as you suggested.But, assuming in the future I want to try and still use the "what I think happened" approach, do you think that, with a shorter visit time, not 15 minutes, and my new update about adding a separate table for "visit session" can be more accurate? I'd appreciate if you take a look at the update.Also, regarding your method-does it mean a new record for each timestamp?Or maybe some text column where I store the timestamps as JSON?Because using the former means plenty of records
    – Stackerito
    Jun 23, 2023 at 19:10
  • 1
    @Stackerito better now? Jun 23, 2023 at 20:21
  • 1
    "I need information on both" storing the same information twice is just asking for it to get out of sync and ruin your day. Stick to a single source of truth. Jun 23, 2023 at 21:16
  • 1
    By lossy I mean you're throwing away data that maybe you don't need for your final solution but I suspect you need to get the tuning variable dialed in. Collecting full data would also give you an idea if you really need go lossy to get the data size down. Don't optimize prematurely. Jun 23, 2023 at 21:20
  • 1
    @Stackerito better now? Jun 23, 2023 at 22:06
1

With a timeout of 15 minutes, you cannot really expect to measure breaks or interruptions with an accuracy of higher than 15 minutes per visit. If your user visits page2, or a foreign page, or just does some offline activitiy in between generally does not matter.

If you reduce the time interval to a lower value like five or one minute, you will get a higher accuracy (but more traffic). Alternatively, when a user visits a different page like page2 on your site, you can assume they ceased they activity on the formerly visited pages and "close" make the application for the formerly visited page(s) behave as if the 15 minutes were over.

5
  • But in this specific system it's a problem: It's a website where it's common to work with 2 monitors, and actually have 2 or more pages open in different tabs and windows and move between them to compare data/insert data. That's why I wanted to count even going to other windows and coming back to it within 15 minutes as the same. But yes I can lower it down. Assuming I lower it down - it means a new record in the database after 1 minute (for example) of inactivity per page? Won't that create many records and affect the DB?
    – Stackerito
    Jun 23, 2023 at 15:17
  • 1
    @Stackerito: sure it will create more records. I cannot tell you if this is a probem or not for your system, only you can find out what that will mean in terms of memory and performance by measuring it. If you observe the system wastes too much memory, you may be able to replace the measurements at the end of each day by some records where you just store the total time oer URL and user.
    – Doc Brown
    Jun 23, 2023 at 15:46
  • thank you, I then might try to reduce the intervals. What about my other idea, about adding another session UUID column which will be assigned for the first active session, and then be copied to the consequent records (Assuming I stay with 15 minutes intervals). Then, each record of that session will have that UUID. Then when I need to calculate total time, I will query the table for unique sessions, getting the oldest visit_start_time of that unique session, and the latest last_activity_time. Then I will have total time spent per session?
    – Stackerito
    Jun 23, 2023 at 16:19
  • In this case, I won't have overlapping or calculating twice of the same session as is happening now, and it will still be accurate because I will get the latest last_activity_time
    – Stackerito
    Jun 23, 2023 at 16:19
  • Also, is it still considered two sources of truth, if the two table track different things (The total time on the app, and the total time on each page, and they may overlap therefor the 2 tables)
    – Stackerito
    Jun 24, 2023 at 15:34
0

I think it is essential to restrict what a session is. Users reading content in non-mother languages or watching video ads is definitively time spent on the page and its content regardless of the time it takes them to do so. It would be reasonable to track that pseudo-inactivity as an activity.

From the tracking standpoint, if the metric is time spent per page, the session last until the page is no longer visible or activity ceases after a reasonable threshold. For example

  • A new watchdog timer (session) should be created when the page is loaded.
  • The timer (session) should be kept alive constantly upon window activity.
  • The timer (session) should go through stages, to not get closed immediately. A user who just landed on a page might need more time to ubicate the content than another who has been already on the page for 5min. The first might be worthy of a second opportunity. The second opportunity is represented by cutting by half the current timeout. Below a reasonable and human possible threshold (3s?) the timer is not renewed; hence session expires.
  • A stage last as much as the browser report activity. The thing is users, as they go through the stages, have less time to generate reportable activity.
  • The initial timeout should be proportional to historical data (user's avg) or the page content.
  • The session expires when the last timer's stage expires (is not renewed), as soon as the page is not visible, or the browser navigates (including page refresh).

When a session expires (session_expire_timestamp is recorded), a new one is created (if the page is visible), even if it's for the same page. The time spent is not the difference between session A and session B beginnings. Ever. Each session has its start and end time.

From the analytics perspective, sessions are generated artificially. We usually set date ranges (1h, 1d, 1m or 1y). Whatever happened within the range is the user's activity for that session/period.

Regarding, last_activity_time to detect absences falls under the pretence of knowing what a user is (was) doing behind the scenes. I don't others, but I honestly don't care if the user went to save the world or bake a cake, I only care if my page wasn't in the foreground, visible and/or active for x time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.