Hello Matomo Community,
I’m working on analyzing time spent per visit using the Matomo database, but I’m encountering some strange results. Specifically:
- Many of the results show
0 seconds, even for visits where I expect some time to have elapsed. - Some results show unrealistically large values, as if Matomo is counting time beyond the actual visit duration.
Below is an example of a query I used (I’ve tried other variations as well, but none worked as expected):
WITH time_differences AS (
SELECT lv.idvisit,
a.name AS page_url,
MIN(lv.server_time) AS first_action_time,
MAX(lv.server_time) AS last_action_time,
TIMESTAMPDIFF(SECOND, MIN(lv.server_time), MAX(lv.server_time)) AS total_time_spent_seconds
FROM matomo_log_action AS a
JOIN matomo_log_link_visit_action AS lv ON a.idaction = lv.idaction_url
JOIN matomo_log_visit AS v ON lv.idvisit = v.idvisit
WHERE a.type = 1
AND v.visit_last_action_time BETWEEN ‘2024-06-01 00:00:00’ AND ‘2024-06-30 23:59:59’
GROUP BY lv.idvisit, a.name
)
SELECT idvisit, page_url, first_action_time, last_action_time, total_time_spent_seconds
FROM time_differences
WHERE idvisit IN (
SELECT idvisit
FROM time_differences
GROUP BY idvisit
HAVING COUNT(DISTINCT page_url) = 1
)
AND total_time_spent_seconds > 0
ORDER BY idvisit, first_action_time, page_url;
My understanding is that visit_last_action_time should provide accurate visit end times, but the results suggest otherwise. Is it possible that Matomo continues to track time after the last logged action? Or am I misunderstanding how these timestamps are stored and calculated?
Any insights or guidance would be greatly appreciated! If there’s a better approach to calculating time spent per visit, I’d love to hear it.
Thank you for your help!