Analytics and total time spent on the app

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.