Comment écrire des requêtes SQL pour sélectionner des visiteurs, des listes de pages vues, des recherches, des événements dans la base de données Matomo FAQ

Lorsque vous souhaitez extraire facilement des données RAW des visiteurs de votre site web et des utilisateurs de votre application, toutes les informations dont vous aurez besoin sont accessibles à partir de la base de données Matomo et vous pouvez parfois vouloir l’interroger en utilisant SQL. Dans cette FAQ, vous trouverez une liste de requêtes SQL que vous pouvez facilement réutiliser.

Documentation sur le schéma de la base de données Matomo

Lorsque vous utilisez SQL pour interroger vos données analytiques, vous pouvez vouloir en savoir plus sur le schéma de la base de données et la signification des différentes colonnes : lisez la documentation de référence sur le schéma de la base de données Matomo pour en savoir plus.

Les requêtes SQL ci-dessous liront beaucoup de données à la fois et utiliseront beaucoup de mémoire sur la base de données, surtout si votre Matomo collecte beaucoup de données.

Requête SQL pour sélectionner toutes les visites et actions (pendant une certaine période de temps)

Exécutez cette requête SQL standard pour extraire de Matomo toutes les données brutes (toutes les lignes et toutes les colonnes) pour toutes les visites et toutes leurs interactions sur tous vos sites Web. Dans cet exemple, nous extrayons toutes les valeurs des 14 premiers jours de mai en utilisant la colonne 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 (invalid_tags_codes_1_(https://matomo.org/faq/general/faq_19212/)): 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 invalid_tags_codes_2_(https://matomo.org/docs/site-search/): 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 invalid_tags_codes_3_(https://matomo.org/docs/event-tracking/), 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 invalid_tags_codes_4_(https://matomo.org/docs/site-search/) 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 invalid_tags_codes_5_(https://matomo.org/faq/troubleshooting/faq_42/) 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;

Supprimer les alertes d’un utilisateur

Un utilisateur a quitté votre équipe ? Même si les alertes de l’utilisateur ne sont plus visibles depuis le tableau de bord de Matomo, vous pouvez y accéder dans la base de données. Utilisez une requête SQL pour supprimer les alertes d’un utilisateur :

SELECT * FROM matomo_alert
WHERE login LIKE ‘username_here’;

Vous pouvez ensuite utiliser cette liste pour apporter les modifications nécessaires aux alertes configurées ou les supprimer directement, par exemple :

UPDATE matomo_alert SET email_me = ‘0’
WHERE login LIKE ‘username_here’;

Ou pour désactiver l’envoi d’e-mails à d’autres utilisateurs :

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

Ou pour supprimer le rapport, il suffit d’exécuter :

DELETE FROM matomo_alert
WHERE login LIKE ‘username_here’;

Requête SQL pour mesurer le nombre de travaux d’invalidation non terminés

Si vous mettez à jour une nouvelle version de Matomo, et que votre base de données est volumineuse, le nombre d’invalidations peut également être important ; le nombre d’invalidations peut être de l’ordre de 100 000 ou même de millions. Cela surcharge les opérations de votre base de données et vous remarquerez un archivage très lent.

Pour résoudre le problème des invalidations lentes dans votre serveur de base de données, vérifiez si vous avez un flot d’invalidations qui encombre votre file d’attente SQL :

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

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

Autres ressources et astuces SQL

Voir aussi les liens suivants :

Exporter les données RAW des visiteurs et des actions via l’API HTTPS (Alternative)

Vous pouvez également exporter toutes ces données RAW en utilisant notre API HTTPs, de sorte que vous n’avez pas besoin d’utiliser des requêtes SQL. L’API utilisée pour exporter toutes vos données brutes s’appelle Live.getLastVisitsDetails et vous permet d’exporter toutes les données relatives aux utilisateurs et aux flux de clics pour un site Web donné et une date donnée : En savoir plus :