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:

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.