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

Cet article Piwik : Et si on fait des statistiques ? est apparu en premier sur RootsLabs.

Original post of Progi1984.Votez pour ce billet sur Planet Libre.