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

Useful WebsitesI’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;

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

Your email address will not be published. Required fields are marked *