When you want to easily extract RAW data from your website visitors and app users, all the information you will need is accessible from the Matomo database and you sometimes may want to query it using SQL. In this FAQ you will find a list of SQL queries you can easily re-use.
Matomo Database Schema documentation
When using SQL to query your analytics data, you may want to learn more about the database schema and the meaning of various columns: read the Matomo Database Schema reference doc to learn more.
The SQL queries below will read a lot of data at once and use significant memory on the database, especially if your Matomo collects a lot of data.
SQL query to select all visits and actions (during a certain time period)
Run this standard SQL query to extract from Matomo all of the raw data and (all rows and all columns) for all visits and all their interactions on all your websites. In this example we extract all value from the first 14 days of May by using the column visit_last_action_time:
SELECT *
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_url
LEFT JOIN matomo_log_conversion ON matomo_log_visit.idvisit = matomo_log_conversion.idvisit
LEFT JOIN matomo_log_conversion_item ON matomo_log_visit.idvisit = matomo_log_conversion_item.idvisit
WHERE visit_last_action_time >= '2022-05-01'
AND visit_last_action_time < '2022-05-15';
SQL query to select all visits and actions for a specific website ID
All the data in Matomo tables is separated for each website by using the column idsite (website ID):
SELECT *
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_url
LEFT JOIN matomo_log_conversion ON matomo_log_visit.idvisit = matomo_log_conversion.idvisit
LEFT JOIN matomo_log_conversion_item ON matomo_log_visit.idvisit = matomo_log_conversion_item.idvisit
WHERE idsite = X;
SQL query to select the list of all site searches
Select all visit actions where the action type is 8, indicating site searches:
SELECT matomo_log_visit.idvisit,
server_time,
matomo_log_action.name as search,
matomo_log_link_visit_action.custom_var_v5 as searchresults
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 type = 8
ORDER BY idvisit, server_time;
SQL query to select all actions where an Event action contains a specific string
Run the following SQL query to select all Events, where the “Event action” matches a certain string (in this example my-event-action-to-delete):
SELECT *
FROM matomo_log_link_visit_action llva
JOIN matomo_log_action as la
WHERE llva.idaction_event_action = la.idaction
AND la.name LIKE '%search%';
SQL query to select all Event names, where the Event action contains ‘search’
SELECT llva.*, la_names.*
FROM matomo_log_link_visit_action llva
JOIN matomo_log_action as la
JOIN matomo_log_action as la_names
WHERE llva.idaction_event_action = la.idaction
AND llva.idaction_name = la_names.idaction
AND la.name LIKE '%search%'
AND idsite = X;
/*You can additionally add two more AND statements to only query a specific timeframe:*/
/*This will only work in MySQL versions lower than MySQL 8*/
AND server_time >= '2021-03-01'
AND server_time < '2021-03-29'
SQL query to select all page title pageviews (Each row is a unique pageview for a Page Title)
SELECT *
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 = X
AND type = 4
AND matomo_log_link_visit_action.server_time >= '2021-06-01'
AND matomo_log_link_visit_action.server_time < '2021-06-30';
SQL query to count the total number of pageviews for each Page Title:
SELECT name as page_title, COUNT(*) as hits
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 = X
AND type = 4
AND matomo_log_link_visit_action.server_time >= '2021-06-01'
AND matomo_log_link_visit_action.server_time < '2021-06-30'
GROUP BY page_title
ORDER BY hits DESC;
SQL query to select all page URL pageviews (Each row is a unique pageview for a Page URL)
SELECT *
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_url
WHERE matomo_log_visit.idsite = X
AND type = 1
AND matomo_log_link_visit_action.server_time >= '2021-06-01'
AND matomo_log_link_visit_action.server_time < '2021-06-30';
SQL query to count the total number of pageviews for each Page URL:
SELECT name as page_url, COUNT(*) as hits
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_url
WHERE matomo_log_visit.idsite = X
AND type = 1
AND matomo_log_link_visit_action.server_time >= '2021-06-01'
AND matomo_log_link_visit_action.server_time < '2021-06-30'
GROUP BY page_url
ORDER BY hits DESC;
More resources and SQL tips
See also the following links:
- SQL queries to get the total number of Impressions and Plays for Media (video & audio) from the Matomo DB
- Selecting IP addresses in SQL.
- Select users within a range of IP addresses.
- Deleting visits with SQL queries.
- Matomo Database Schema reference guide.
Exporting RAW visitors and actions data via the HTTPS API (Alternative)
You can also alternatively export all this RAW data using our HTTPs API, so you don’t have to use SQL queries. The API used to export all your Raw data is called Live.getLastVisitsDetails and lets you export all the user and clickstream data for a given website and a given date: Learn more.