How to Delete Web Analytics Data via API
If you track web analytics statistics over a long period of time, your database can become really large. To maintain database performance and size, you may eventually get to the point where you need to delete old web analytics data. In Kentico 9 this is not possible to do in the admin interface anymore but as always you can easily achieve this by using our Kentico API.
The best suitable method for deletion of web analytics data is this one:
CMS.WebAnalytics.StatisticsInfoProvider.RemoveAnalyticsData()
Reference for the method can be found here: http://devnet.kentico.com/docs/9_0/api/html/M_CMS_WebAnalytics_StatisticsInfoProvider_RemoveAnalyticsData.htm
The method has four parameters:
- fromDate
- toDate
- siteID
- where condition
All four parameters are pretty straightforward. You simply supply the method with a time interval, site context and optional WHERE condition.
For sake of simplicity, let’s imagine that you need to delete all web analytics data of the current site from a month ago until today. You could write it like this:
using CMS.WebAnalytics;
....
DateTime fromDate = DateTime.Now.AddMonths(-1); // from one month ago
DateTime toDate = DateTime.Now; // until today
StatisticsInfoProvider.RemoveAnalyticsData(fromDate, toDate, SiteContext.CurrentSiteID, String.Empty); // delete all data of a current site for a specified time interval
I have used an empty string as a fourth parameter. This basically means that all data will be deleted because there is no WHERE condition used.
If you wish to delete specific data only, you need to create a WHERE condition. The simplest way of doing this is by looking into the Analytics_Statistics table in your database and checking the values in the StatisticsCode column.
The column contains code names like pagenotfound, landing page, exitpage, avgtimeonpage, pageviews, visitfirst, visitreturn and many others.
If you for example would like to delete only the pagenotfound records, then your WHERE condition would be written like this:
string where = "StatisticsCode = N'pagenotfound'";
And your method would be called this way:
StatisticsInfoProvider.RemoveAnalyticsData(fromDate, toDate, SiteContext.CurrentSiteID, where);
Another example could be deletion of the visitors statistics. In this case you need to make sure that both "returning" and "first time" visitors code names are deleted as well:
string where = "(StatisticsCode = 'visitfirst' OR StatisticsCode = 'visitreturn')";
Deleting campaign statistics:
string where = "(StatisticsCode LIKE 'campconversion%' OR StatisticsCode = 'campaign')";
Deleting MVT test statistics:
string where = "StatisticsCode LIKE 'mvtconversion%'";
Deleting AB tests statistics:
string where = "StatisticsCode LIKE 'ab%'";
As you can see, web analytics data can be deleted very easily and it is up to you what data you want to keep.
In most cases, you would not need to specify a WHERE condition. You would just use an empty string instead. This is because usually you want to delete ALL web analytics data from a specific time interval.
-pj-