How do I write SQL queries to select visitors, list of pageviews, searches, events in the Matomo database

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 llva.* 
    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%';

More resources and SQL tips

See also the following links:

• 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.