Piwik is an audience analysis tool, similar to Google Analytics, with the only difference that it is open source and that the user can install it himself on his hosting space. This tool therefore grows over time to record each visit to its website. When the traffic is heavy, the database which contains all the information of the visits can grow strongly. This article presents 2 methods to purge data: either by deleting certain targeted visits, or by purging old records.
Precautions: before doing anything, it is advisable to make a data backup or to make sure that there is a recent backup of the database. Bad handling happened so quickly …
Delete old data
In the Piwik interface it is possible to delete data related to old visits. However, it may be beneficial to delete the data yourself in SQL.
The following SQL query allows for example to delete the previous visits on January 1, 2013.
DELETE `piwik_log_visit`, `piwik_log_link_visit_action` FROM `piwik_log_visit` INNER JOIN `piwik_log_link_visit_action` ON `piwik_log_link_visit_action.idvisit` = `piwik_log_visit.idvisit` WHERE `visit_server_date` <= '2013-01-01'
It is possible to run this request directly from PhpMyAdmin.
Delete some visits
It may happen that they have visited their own website without wanting to add their visit to the rest of the data. Therefore, it is wise to be able to delete certain visits.
1st step: find the visits in “piwik_log_visit”
The first step is to find the visits in the “piwik_log_visit” table. For this it is possible to filter on the day and time of the visits in the “visit_last_action_time” column. The main thing is to retrieve the identifiers of these visits contained in the “idvisit” column.
2nd step: delete the visits and the actions linked to these visits
Once the list of identifiers has been retrieved, it is possible to perform SQL queries to delete the data.
DELETE FROM `piwik_log_visit` WHERE `idvisit` IN (id1, id2, id3);
To note : in this example you have to replace id1, id2 and id3 by the list of identifiers that you have recovered.
Delete actions from these visits:
DELETE FROM `piwik_log_link_visit_action` WHERE `idvisit` IN (id1, id2, id3);
3rd step: delete the cache tables:
To avoid keeping completely corrupted cache tables, you must delete the cache tables corresponding to the deleted visits. For this fictitious example we will delete the tables for October 2013.
DROP TABLE IF EXISTS `piwik_archive_numeric_2013_10`, `piwik_archive_blob_2013_10`;
4th step: consult the interface to regenerate the cache tables
The last step is to simply log into the user interface. Piwik will automatically notice that the cache does not exist and automatically re-create them.
Source of the tip: rootslabs.net.
With these tips it is possible to master data collection on Piwik. However, you should be careful not to delete data that might prove useful in the future.