RootsLabs

Et voilà, nous arrivons début 2014, il est temps de faire quelques statistiques. Voici donc un petit billet sur quelques requêtes MySQL sur les tables de Piwik. Cela va vous permettre d’extraire certaines données directement et rapidement.

Accèdons d’acord à Piwik pour faire nos statistiques

Premièrement, il vous faut juste un accès MySQL à votre base de données stockant MySQL.
Deuxièmement, certaines variables peuvent être modifiés :

• Date de début et fin : server_time
• Site (ID du site) : idsite

Requêtes

Nombre de visites uniques et hits en 2013

SELECT 
  COUNT( DISTINCT idvisit ) AS unique_visits, 
  COUNT( DISTINCT idlink_va ) AS hits
FROM piwik_log_link_visit_action
WHERE server_time >= '2013-01-01'
  AND server_time <= '2013-12-31'
  AND idsite = 1
  AND idaction_name IS NOT NULL

Pages les plus populaires de l’année 2013

SELECT 
  COUNT( DISTINCT idvisit ) AS unique_visits,
  COUNT( DISTINCT idlink_va ) AS hits, 
  (SELECT name FROM piwik_log_action WHERE idaction_url = idaction) AS url, 
  (SELECT name FROM piwik_log_action WHERE idaction_name = idaction) AS title
FROM piwik_log_link_visit_action
WHERE server_time >= '2013-01-01'
  AND server_time <= '2013-12-31'
  AND idsite = 1
  AND idaction_name IS NOT NULL
GROUP BY idaction_name
ORDER BY unique_visits DESC

Nombre de visites par pays

SELECT
  COUNT( DISTINCT piwik_log_visit.idvisit ) AS unique_visits,
  COUNT( DISTINCT idlink_va ) AS hits,
  piwik_log_visit.location_country AS country
FROM piwik_log_link_visit_action
  INNER JOIN piwik_log_visit ON piwik_log_link_visit_action.idvisit = piwik_log_visit.idvisit
WHERE server_time >= '2013-01-01'
  AND server_time <= '2013-12-31'
  AND piwik_log_link_visit_action.idsite =1
  AND piwik_log_link_visit_action.idaction_name IS NOT NULL
GROUP BY piwik_log_visit.location_country
ORDER BY unique_visits DESC

Mots clés Google ayant amené le plus de visites

SELECT
  COUNT( DISTINCT piwik_log_visit.idvisit ) AS unique_visits,
  COUNT( DISTINCT idlink_va ) AS hits,
  piwik_log_visit.referer_keyword AS keyword
FROM piwik_log_link_visit_action
  INNER JOIN piwik_log_visit ON piwik_log_link_visit_action.idvisit = piwik_log_visit.idvisit
WHERE server_time >= '2013-01-01'
  AND server_time <= '2013-12-31'
  AND piwik_log_link_visit_action.idsite =1
  AND piwik_log_link_visit_action.idaction_name IS NOT NULL
  AND piwik_log_visit.referer_keyword <> ''
  AND piwik_log_visit.referer_name = 'Google'
GROUP BY piwik_log_visit.referer_keyword
ORDER BY unique_visits DESC

Nombre de visites par Système d’exploitation

SELECT
  COUNT( DISTINCT piwik_log_visit.idvisit ) AS unique_visits,
  COUNT( DISTINCT idlink_va ) AS hits,
  piwik_log_visit.config_os AS OS
FROM piwik_log_link_visit_action
  INNER JOIN piwik_log_visit ON piwik_log_link_visit_action.idvisit = piwik_log_visit.idvisit
WHERE server_time >= '2013-01-01'
  AND server_time <= '2013-12-31'
  AND piwik_log_link_visit_action.idsite =1
  AND piwik_log_link_visit_action.idaction_name IS NOT NULL
GROUP BY piwik_log_visit.config_os
ORDER BY unique_visits DESC

Conclusion

Piwik, comme tout logiciel libre, est hackable dans le sens modifiable mais attention à ces requêtes car peut-être quand une ou deux versions la Team Piwik aura modifié les tables et les requêtes ne seront plus fonctionnelles. Donc ces requêtes sont fonctionnels avec la version 2.0.2 de Piwik.

Merci à l’équipe technique de Piwik de m’avoir relevé ce manque dans cet article.

@piwik Bien sûr… C'est juste pour montrer que @piwik comme tout logiciel libre est hackable (dans le sens modifiable).

— Progi1984 (@Progi1984) January 9, 2014

[EDIT 09/01/2014 08:10] ADDED : Conclusion et Information relative au Tweet de @piwik.