SQL Query on Matomo Database to Calculate the Number of Visits to Each Page

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;