No Comments

Author: samdiaz Posted On: January 25th, 2013 In:Tips and Tricks

Recently I started using Piwik at my new job. According to their website, it’s “a free web analytics tool that provides you with detailed reports on your website’s visitors, your marketing campaigns and much more. Piwik is an open source alternative to Google Analytics, available in 45 languages, and has been downloaded more than 1 million times!” One thing I noticed it was missing was an easy way to clear out analytics after you’ve been testing before going live with a website. After a bit of researching, I found http://piwik.org/faq/how-to/#faq_73 which gave me an idea; why not write a simple query with a variable to do all of these things at once, but for a single website instead of dropping ALL of the data in the DB.

 

Here’s what I came up with:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*###################################################*/
/*                                                   */
/*   Set this variable equal to the 'idsite' field   */
/*          of the website you wish to clear         */
/*    The ID can be found in the PIWIK_SITE table    */
/*                                                   */
/**/               SET @siteID = 4;                /**/
/*                                                   */
/*   !!CAUTION!! This will delete ALL your analytic  */
/*   data including reports/goals for this website   */
/*                                                   */
/*###################################################*/

DELETE FROM piwik_archive_blob_2013_01 WHERE idsite = @siteID;
DELETE FROM piwik_archive_numeric_2013_01 WHERE idsite = @siteID;
DELETE FROM piwik_goal WHERE idsite = @siteID;
DELETE FROM piwik_log_conversion WHERE idsite = @siteID;
DELETE FROM piwik_log_conversion_item WHERE idsite = @siteID;
DELETE FROM piwik_log_link_visit_action WHERE idsite = @siteID;
DELETE FROM piwik_log_visit WHERE idsite = @siteID;
DELETE FROM piwik_report WHERE idsite = @siteID;

Simply save this as a query in your Piwik DB manager (I use Navicat) and run it whenever you need to clear out a website’s data. NOTE: Make sure you change the siteID variable to reflect the desired site in your DB.

 

If you wish, you can on my GitHub.


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>