Count of Unique Visitors and Visits is sometimes different when between Matomo UI and SQL query results

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.

This issue is resolved. The reason for the differences is that Matomo aggregates the unique visits of each day to make up a week. I believe this is done for performance reasons but people should know that unique visits can be counted multiple times for an individual over the span of a week/month/year leading to an inaccurate total of “unique” visits.

More explanation here: Unique visitor per week/month

1 Like