Database Schema - time_spent returning no results

General questions
1

Hello All in Matomo Forum,

I wanted use a query built like that in order to retrieve the “time_spent” fields (table “matomo_log_link_visit_action” ) information:

"
SELECT matomo_log_action.idaction AS idaction,
matomo_log_action.name AS URL,
matomo_log_action.url_prefix AS url_prefix,

LEFT(CONVERT(matomo_log_link_visit_action.server_time, CHAR), 10) AS “Day”,

matomo_log_link_visit_action.time_spent AS “Time on Page”,

matomo_log_visit.idvisit AS “Visit ID”,

matomo_log_visit.idsite AS “Live Site ID”

FROM matomo_log_visit
LEFT JOIN matomo_log_link_visit_action
ON matomo_log_link_visit_action.idvisit = matomo_log_visit.idvisit
LEFT JOIN matomo_log_action
ON matomo_log_link_visit_action.idaction_url = matomo_log_action.idaction
WHERE matomo_log_link_visit_action.idsite = <Parameters.Matomo Live> AND

matomo_log_visit.idsite = <Parameters.Matomo Live>"

However, the result of “time_spent” field is always “Null” - there seem to be no recorded information there. This is inconsistent with how information is presented in our Matomo instance where I can see “time spent on page” information.

If anyone could give a hint as for where this information is hidden in the database schema and why the field time_spent in the matomo_log_link_visit_action table returns no information I would be very grateful.

Matomo Query Result
Matomo Query Result760×750 54.2 KB

2

Hi @Morfix

https://developer.matomo.org/guides/database-schema

On the page above, is written:

The time_spent_ref_action column contains the time spent by the visitor on their previous pageview. The previous pageview’s Page URL as defined by idaction_url_ref and previous pageview’s Page Title as defined by idaction_name_ref. For example, to get the Time spent on a particular Page URL: first get the corresponding idaction_url value for this Page URL, then query eg. SELECT count(*) as page_hits, sum(time_spent_ref_action) as total_time_spent_in_seconds FROM log_link_visit_action WHERE idaction_url_ref = IDACTION_URL_ID_HERE. Note: to ensure you collect accurate time spent on each page, enable the Heartbeat timer.

1 Like
3

Thank you very much Philippe!
I still not quite understand how the filed “time_spent” is supposed to work “set by the CustomDimensions plugin”. However, the workaround with the “time_spent_ref_action” is definitely very helpful.

4

Hi @Morfix
Do you use heart beat timer? Do you use sendBeacon?

Option also available in Matomo Tag Manager…

Maybe the timespent is set only if these settings are set? (e.g. for last pages of visits: do you see any time spent different form 0?)