Hello, I’m trying to determine the cause of different values between what Matomo shows and what a SQL query shows regarding Unique Visitors and total Visits.
Specifically, regarding Event Categories (Behavior → Events page → Event Categories section)
When you export the dataset as XML you can view nb_events, nb_visits, and nb_uniq_visitors.
I’m able to query these values from the database using SQL for a particular day. The “Total Events” value is correct, but for certain categories (not all of them) the values I get for nb_uniq_visitors and nb_visits is wrong (much lower than what the XML values show).
I’ve searched the forums, read about how Matomo tracks unique visitors, and dug through some of the code on github.
My understanding is that unique visitors and visits are usually counted using count(distinct 'idvisitor')
and count(distinct 'idvisit')
See my query below for example
SELECT log_action
.name
AS Event Categories
, count(*) AS Total Events
, count(distinct log_link_visit_action
.idvisitor
) AS Unique Visitors
, count(distinct log_link_visit_action
.idvisit
) AS Visits
FROM log_action
RIGHT JOIN log_link_visit_action
ON log_link_visit_action
.idaction_event_category
= log_action
.idaction
WHERE (log_link_visit_action
.idsite
= {{id_site}}
AND log_link_visit_action
.server_time
>= {{start_date}}
AND log_link_visit_action
.server_time
< {{end_date}} AND log_action
.type
= 10)
GROUP BY log_action
.name
ORDER BY Total Events
DESC
The values I obtain are always correct in terms of “Total Events” but sometimes wrong (lower than what Matomo shows on the XML) for “Unique visitors” and “Total Visits”. I’ve noticed the values are more often wrong when it comes to Mobile event tracking. Is there some other way that Matomo could be counting these “unique visitors”? The only way it seems possible is if idvisitor can somehow represent more than 1 user since Matomo somehow finds more “nb_uniq_visitors” than there are distinct “idvisitor” values.
References:
Can anybody point me in the right direction regarding this query and the discrepancy that I’m finding between SQL and the Matomo XML export?
I’m using Matomo self-hosted on Linux (version 4.13.0) with MariaDB.