I’ve been using for just short of four years now and have been pretty happy with it.
I generally consume the data it holds via the eweb portal, but I was wanting to extract some data to manipulate recently, in a way that the web portal didn’t allow, so I did a little poking around the database to work out the syntax needed to query the database directly in mySQL.
The below query returns all pages for the specified date range (the first two highlighted variables at the top), sorted in descending order of number of visits; the third parameter is the id of the site for which you want the data:
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SET @StartDate = '2023-01-01';
SET @EndDate = '2023-12-31';
SET @SiteID = 1;
SELECT
name
,COUNT(name) AS COUNT
FROM
matomo_log_visit
LEFT JOIN
matomo_log_link_visit_action
ON
matomo_log_visit.idvisit = matomo_log_link_visit_action.idvisit
LEFT JOIN
matomo_log_action
ON
matomo_log_action.idaction = matomo_log_link_visit_action.idaction_name
WHERE
matomo_log_visit.idsite = @SiteID
AND
type = 4
AND
matomo_log_link_visit_action.server_time >= @StartDate
AND
matomo_log_link_visit_action.server_time <= @EndDate
GROUP BY
NAME
ORDER BY
COUNT(NAME) DESC;
