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 *
    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;​

SQL query to count and select all Outlinks from specific Page URLs:

    SELECT lan.name, COUNT(*) FROM (

    SELECT lva.idpageview AS idpageview, lva.idvisit AS idvisit
    FROM matomo_log_link_visit_action lva
    LEFT JOIN matomo_log_action ON matomo_log_action.idaction = lva.idaction_url

    WHERE idsite = 1
    AND server_time >= '2022-01-01 00:00:00'
    AND server_time < '2022-01-02 00:00:00'
    AND matomo_log_action.type = 1
    AND matomo_log_action.name LIKE
    '%example.org/homepage'

    ) AS pages LEFT JOIN matomo_log_link_visit_action lvn ON lvn.idpageview = pages.idpageview AND lvn.idvisit = pages.idvisit LEFT JOIN matomo_log_action lan ON lan.idaction = lvn.idaction_url WHERE idsite = 1
    AND server_time >= '2022-01-01 00:00:00'
    AND server_time < '2022-01-02 00:00:00'
    AND lan.type = 2
    GROUP BY lan.name ORDER BY
    COUNT(*) DESC;

There are a few adjustments that you need to make to the query in order for it to work correctly for you:
– You need to set the ID Site to the correct ID Site in two positions in the query
– You need to set the Date/Time between where the query will look for data in 2 locations
– You need to set the URL for which you want to check the outlinks for, in the example above example.org/homepage is used
You can replace this with the URL of your website without the URL prefix. So for example https://example.org/homepage should be set as '%example.org/homepage'

You can also get all of the outlinks for a specific section of your website by adding an additional % at the end of the URL. For example:
'%example.org/homepage%'

Will show you all outlinks for the following example pages:
https://example.org/homepage
https://example.org/homepage/section
https://example.org/homepage/abc
If you use a prefix other than matomo_ for your database, you need to update this in the query.

SQL query to count and select all Outlinks from specific Site Searches

    SELECT lan.name, COUNT(*) FROM (

    SELECT lva.idpageview AS idpageview, lva.idvisit AS idvisit
    FROM matomo_log_link_visit_action lva
    LEFT JOIN matomo_log_action ON matomo_log_action.idaction = lva.idaction_name

    WHERE idsite = 1
    AND server_time >= '2022-01-01 00:00:00'
    AND server_time < '2022-01-02 00:00:00'
    AND matomo_log_action.type = 8
    AND matomo_log_action.name LIKE 'search'

    ) AS pages LEFT JOIN matomo_log_link_visit_action lvn ON lvn.idpageview = pages.idpageview AND lvn.idvisit = pages.idvisit
    LEFT JOIN matomo_log_action lan ON lan.idaction = lvn.idaction_url
    WHERE idsite = 1
    AND server_time >= '2022-01-01 00:00:00'
    AND server_time < '2022-01-02 00:00:00'
    AND lan.type = 2
    GROUP BY lan.name ORDER BY
    COUNT(*) DESC;

You will need to make adjustments to this query to work for your database:
– You need to set the ID Site to the correct ID Site in two positions in the query
– You need to set the Date/Time between where the query will look for data in 2 locations
– You need to set the search string you wish to search for, 'search' is used in the example above and needs to be replaced with the search term you want to get data for

SQL Query to count the number of hits tracked in Matomo for all sites by ID Site

    SELECT COUNT(*) AS hits,idsite
    FROM matomo_log_link_visit_action
    GROUP BY idsite;

The above query shows the total count of hits for all the RAW data tracked in Matomo. If you have RAW data deletion enabled this will determine how far back the count goes.

You can restrict the query to a specific time frame to get the hits for a specific period:

    SELECT COUNT(*) AS hits,idsite
    FROM matomo_log_link_visit_action
    WHERE server_time > '2022-12-01'
    AND server_time < '2022-12-31'
    GROUP BY idsite;

Delete Alerts for a User

Has a user left your team? Even though the user’s alerts are no longer visible from the dashboard of Matomo, you can access them in the database. Use a SQL query to delete a user’s alerts:

    SELECT * FROM matomo_alert
    WHERE login LIKE 'username_here';

You can then use that list to make any necessary updates to the configured alerts or directly delete them, for example:

    UPDATE matomo_alert SET email_me = '0' 
    WHERE login LIKE 'username_here';

Or to disable emails from being sent to other users:

    UPDATE matomo_alert SET additional_emails = '[]' 
    WHERE login LIKE 'username_here';

Or to delete the report simply execute:

    DELETE FROM matomo_alert 
    WHERE login LIKE 'username_here';

SQL query to measure the number of unfinished invalidation jobs

If you update to a new Matomo version, and your database is large, the number of invalidations may be also large; the count of invalidations can be in the 100,000s or even millions. This overloads your database operations and you will notice very slow archiving.

To troubleshoot slow invalidations in your database server, check whether you have a flood of invalidations clogging your SQL queue:

    SELECT count(*), name, idsite 
    FROM matomo_archive_invalidations  
    GROUP BY name;


    SELECT count(*), name, idsite, date1, date2 
    FROM matomo_archive_invalidations 
    GROUP BY name;

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, or selecting the Visitor IDs 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.