iFinity Blogs 

Free extra reports for DotNetNuke - Get Link Clicks statistics, Detailed User visits and more

by Bruce Chapman on Thursday, March 6, 2008 2:51 PM

 I've created a set of reports that expand the DNN Site Log Reports.  I wrote these reports to get more information out of the treasure-trove of log data stuck in the DNN tables.  In particular, I've always wanted a site-level view of the link clicks, particular with regard to file and page clicks.

The three reports are:

  • Link Clicks Report : Shows the link click statistics for any links in DNN which have been marked as 'Log each click'.
  • File Downloads Report: Shows the link click statistics for any downloads from a file link.
  • User Visit Detail:  Shows the detail of an individual user visit, including entry and exit times, pages viewed, referring Url and any Affiliate information.

 Link Clicks Report

 The Link Clicks report give a simple overview of the different links in a DNN portal, and how often they are clicked. The Clicks per day averages out the number of clicks since the link was created. Here is an example:

Link Destination Number Clicks Last Click Date Link Created Date Link Module Title Link Module Page Clicks per Day
/Blogs 212 Dec 02, 2007 Mar 29, 2007 Quick Links [Removed] 0.85
/Products 36 Mar 06, 2008 Feb 01, 2008 New on the site! /Home 1.06
/Products/PremiumSupport 28 Mar 06, 2008 Feb 01, 2008 New on the site! /Home 0.82

File Downloads Report

The file downloads report works off any file where you have used a standard DNN link module entry to download a file via a postback (instead of a straight Url to the file location). The report shows how many downloads there have been for each file, and how much data has been transferred for that file. The Clicks per day averages out the number of clicks since the link was created. Here is an example:

Downloaded File Number Downloads Last Download Date Link Created Date Total Download Size Clicks per Day
downloads/DotNetNuke.UrlRewrite_demo.zip 262 Mar 02, 2008 Aug 31, 2007 11.40 Mb 1.42
downloads/DNN_UpdateSiteLogProcsScript.zip 196 Mar 05, 2008 Aug 23, 2007 537.47 Kb 1.01

User Visit Detail

The User Visit Detail report gives a more detailed listing of actual user visits, showing where a user started and finished their visit, including the number of pages and if they came in on an Affiliate link. It can be quite useful for tracking individual problems and getting a good overview on how visitors actually use your site. Here is an example:

Date Start of Visit End of Visit Visit Length (minutes) User IP Address Pages Viewed Referrer Entry Page Exit Page User Name Vendor - Affiliate
06/03/2008 14:34:00:0 14:34:00:0 < 1 00.00.00.00 1 http://search.live.com/results.aspx ?q=support&mrt=en-us&FORM=LIVSOP Support Forums Support Forums    
06/03/2008 14:28:00:0 14:28:00:0 < 1 00.00.00.00 1 http://search.live.com/results.aspx ?q=bruce&mrt=en-us&FORM=LIVSOP Bruce's Blog Bruce's Blog    
06/03/2008 13:18:00:0 13:18:00:0 < 1 00.00.00.00 1 http://search.live.com/results.aspx ?q=dotnetnuke&mrt=en-us&FORM=LIVSOP Products Products    
06/03/2008 06:05:00:0 06:06:00:0 1 00.00.00.00 2 http://www.google.se/search ?q=dotnetnuke+friendly+url Products Free Downloads  

I removed the IP addresses in the interests of privacy

How to Install

Here is the very simple steps to install the reports;

  1. Download the DotNetNuke Reports Scripts
  2. Extract the zip file to a location on your computer, and open one of script files in a text editor
  3. On your DNN installation, go to the Host->Sql page
  4. Paste the script from the file into the 'SQL' box on the page
  5. Check the 'Run as Script' checkbox
  6. Click on 'Execute'
  7. Repeat for each of the Reports you want to install
  8. Go to your Admin->Site Log page
  9. You will see the new reports in the drop down list.  Select the report and run.
  10. Enjoy!

NOTE: The Link Click reports (File Downloads and Link Clicks) are not affected by the date range boxes shown on the Site Log Reports page.  Changing these dates will have no affect, as the links are only saved as a total, not logged over time.

Blogs Parent Separator Crafty Code
Author
Bruce Chapman

The craft of writing code. The outcomes from being crafty with code. Crafty Code is tales from the coding bench.

21 comment(s) so far...

Anonymous 2/2/2009

Hi Bruce,<br>thanks for the nice scripts. Just the "Link Clicks" is exactly the "File Downloads" with a different Label. Can you have a look at this?<br>your's<br>felix

 
Anonymous 5/19/2009

Hi Bruce,<br><br>Can we also track number of user visits.<br><br>Gyanendra

 
Anonymous 6/16/2009

Is this DNN5 compatible?

 
Bruce Chapman 6/16/2009

@steve haven't checked but I think the table structures didn't change. Let me know if there's a problem

 
Anonymous 6/26/2009

Hi, I'm getting this error when I set the type report as: File Downloads and then click on display.<br>Can you tell me why this is happening?<br>Thanks, <br>Javier<br><br><br>Error: Site Log is currently unavailable.<br>DotNetNuke.Services.Exceptions.ModuleLoadException: Arithmetic overflow error converting expression to data type int. ---> System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type int. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.Common.DbEnumerator.MoveNext() at System.Web.UI.WebControls.DataGrid.CreateAutoGeneratedColumns(PagedDataSource dataSource) at System.Web.UI.WebControls.DataGrid.CreateColumnSet(PagedDataSource dataSource, Boolean useDataSource) at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) at System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) at System.Web.UI.WebControls.BaseDataList.DataBind() at DotNetNuke.Modules.Admin.SiteLog.SiteLog.BindData() at DotNetNuke.Modules.Admin.SiteLog.SiteLog.cmdDisplay_Click(Object sender, EventArgs e) --- End of inner exception stack trace ---

 
Anonymous 8/9/2009

Hi This looks like a good module, however i does not seem to want to instal on DNN 05.01.01<br><br>Are there any Plans for a DNN 5x version of this module<br><br>thanks,

 
Anonymous 6/17/2010

Bruce, <br><br>Any chance you could post the code for your report on the link click tracking. If you could update it would be much appreciated. As mentioned above it is a duplicate report of the download report.<br><br>These reports work fine for me in DNN 5.

 
Bruce Chapman 6/18/2010

@matt/@felix : yes finally got the hint and fixed up the link clicks report. I had actually lost the original script, which is why it took so long. But I found an old test database with the script still existing, so I re-extracted it and fixed it up. Just re-download the zip file, should show file date of 18th June 2010. The link clicks report has been replaced with the correct script.<br><br>I have confirmed these are DNN 5.4 compatible, I don't see any reason why they won't be compatible going forwards.<br><br>@javier - the arithmetic overflow is either a divide by zero error, or you have a very large figure in the calculations. It's hard to say unless you ran the query manually in a sql window to try and ascertain which column is providing the problem by commenting them out one by one.

 
Anonymous 2/9/2011

Bruce,<br><br>Love the scripts and all the resources you share. When executing the link clicks report get the following output. I also get the same error on the site log page as javier above. Any suggestions?<br><br>Secondly the user visit detail is only showing the super user account, none of the test users I've created (that are showing up i the detailed report)<br><br>System.Data.SqlClient.SqlException: Cannot drop the procedure 'dbo.GetSiteLog22', because it does not exist or you do not have permission. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String ConnectionString, String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String ConnectionString, String Script) /* drop the proc if it already exists */ IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'dbo.GetSiteLog21') AND type in (N'P')) DROP PROCEDURE dbo.[GetSiteLog22] System.Data.SqlClient.SqlException: Invalid column name 'MOduleTitle'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String ConnectionString, String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String ConnectionString, String Script) /* create the report procedure*/ CReaTE procedure dbo.[GetSiteLog22] @PortalId int, @PortalAlias nvarchar(50), @StartDate datetime, @EndDate datetime as /* Created : Bruce Chapman 6 March 2008 Modifications */ BEGIN /* work out the time difference between the portal site timezone and the installed server timezone*/ declare @timeDiff int /* the time difference is the difference between the portal site timezone, and the time zone of the sql server*/ /* the timezone of the sql server can be worked out by the difference between the current date and the utc date */ select @timeDiff = timezoneOffset - dateDiff(n,getutcDate(),getDate()) from Portals where portalID = @portalID /* the filtering start and end date should be assumed to be given in the local portal time so we go back to the sql server timezone by subtracting the difference between the portal timezone and the sql server timezone */ select @StartDate = DateAdd(mi, -@timeDiff, @StartDate) ,@EndDate = DateAdd(mi,- @timeDiff, @EndDate) select case when isnumeric(tr.Url) = 1 then replace(t.TabPath, '//','/') else tr.Url END as [Link Destination] , tr.Clicks as [Number Clicks] , Convert(varChar(12),LastClick ,107) As [Last Click Date] , Convert(varChar(12),CreatedDate ,107) As [Link Created Date] ,m.MOduleTitle as [Link Module Title] , coalesce(replace(rt.TabPath, '//','/'), '[Removed]') as [Link Module Page] ,case when coalesce(DateDiff(d, CreatedDate, LastClick ),0) > 0 then str(Clicks * 1.0 / (DateDiff(d, CreatedDate, LastClick ) * 1.0),10,2) else '-' end as [Clicks per Day] from UrlTracking tr left join Modules m on tr.ModuleId = m.ModuleId left join TabMOdules tm on m.ModuleId = tm.ModuleId left join Tabs rt on rt.TabId = tm.TabId left join Tabs t on convert(varchar(4),t.TabId) = tr.Url where urlType in ('T','U') and tr.PortalID = @portalId order by tr.clicks desc END

 

@Ben I have updated the files with the fix for the drop error you were getting.<br><br>To fix the arithmetic overflow you'll need to comment out the columns one by one to see where the problem is, and try and work out if it is because of a divide by zero, or because the value is too large to go into an INT. It's impossible to debug without having the data that causes the problem at hand so you'll need to do some simple debugging of the stored proc in a query manager.

 
Anonymous 3/7/2011

Hi Bruce,<br><br>Thank you for posting up these scripts. My client is not very happy that the "track the number of times this link is clicked" in the HTML module doesn't offer a report (even though the data is in the database).<br><br>So, I've run your SQL script to add "Link Clicks" to the "Site Log". It's perfect with one exception...<br><br>How can I get rid of [Removed] links? Better yet, is there a way I can regenerate all outbound links? I originally tried to create null pages with redirects to get the stats, but that didn't work. I ran your SQL, and noticed all those links that were outbound, are now [Removed]. ~ Giff

 
Bruce Chapman 3/8/2011

@GIfford - all you need to do is change the join on the tabs table to an inner join from an outer join. So, modify the sql so that:<br>left join {objectQualifier}Tabs rt<br> on rt.TabId = tm.TabId<br>becomes<br>inner join {objectQualifier}Tabs rt<br> on rt.TabId = tm.TabId<br>This will mean that removed tabs are no longer found, and you won't see the '[Removed]' value.<br><br>As for regenerating outbound links - not sure exactly what you mean by that (I assume you mean replace linkclick.aspx references) - but you can look into some html find/replace scripts. I think Mitchel Sellers published one once. But it will depend where the links are embedded. Unfortunately there's usually no easy way to do that type of thing.<br>

 
Anonymous 5/23/2011

Hi Bruce, these reports are great but wondered if the Downloads report can be updated to include the start and end date as the links we're using has the "track date and time" option selected so each click is recorded in the UrlLog DNN table. Is it easy enough to update to query to include the start and end date?<br>Thanks.

 
Bruce Chapman 5/24/2011

@Dan - a good idea. Not sure why I didn't do this in the first place.<br><br>I've uploaded a new copy of the files, which includes an update to the downloads report.

 
Andy 8/24/2011

Getting the following Error when trying to add the Link Clicks Report SQL in DNN 5.6.2 Community.<br /><br />System.Data.SqlClient.SqlException: Invalid column name 'MOduleTitle'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String ConnectionString, String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String ConnectionString, String Script) /* create the report procedure*/ Create procedure dbo.[GetSiteLog22] @PortalId int, @PortalAlias nvarchar(50), @StartDate datetime, @EndDate datetime as /* Created : Bruce Chapman 6 March 2008 Modifications */ BEGIN /* work out the time difference between the portal site timezone and the installed server timezone*/ declare @timeDiff int /* the time difference is the difference between the portal site timezone, and the time zone of the sql server*/ /* the timezone of the sql server can be worked out by the difference between the current date and the utc date */ select @timeDiff = timezoneOffset - dateDiff(n,getutcDate(),getDate()) from Portals where portalID = @portalID /* the filtering start and end date should be assumed to be given in the local portal time so we go back to the sql server timezone by subtracting the difference between the portal timezone and the sql server timezone */ select @StartDate = DateAdd(mi, -@timeDiff, @StartDate) ,@EndDate = DateAdd(mi,- @timeDiff, @EndDate) select case when isnumeric(tr.Url) = 1 then replace(t.TabPath, '//','/') else tr.Url END as [Link Destination] , tr.Clicks as [Number Clicks] , Convert(varChar(12),LastClick ,107) As [Last Click Date] , Convert(varChar(12),CreatedDate ,107) As [Link Created Date] ,m.MOduleTitle as [Link Module Title] , coalesce(replace(rt.TabPath, '//','/'), '[Removed]') as [Link Module Page] ,case when coalesce(DateDiff(d, CreatedDate, LastClick ),0) > 0 then str(Clicks * 1.0 / (DateDiff(d, CreatedDate, LastClick ) * 1.0),10,2) else '-' end as [Clicks per Day] from UrlTracking tr left join Modules m on tr.ModuleId = m.ModuleId left join TabMOdules tm on m.ModuleId = tm.ModuleId left join Tabs rt on rt.TabId = tm.TabId left join Tabs t on convert(varchar(4),t.TabId) = tr.Url where urlType in ('T','U') and tr.PortalID = @portalId order by tr.clicks desc END <br /><br />Am I doing something wrong, or does the code need to be updated for this version of DNN.<br /><br />Thank you.<br /><br />A

 
Anonymous 10/2/2011

Smae Problem with Linkklick<br /><br />MOduleTitle not found.<br /><br />?????

 
Bruce Chapman 10/2/2011

Yes, the module title needs to be removed from the Sql in the report. Please check the latest download and I'll see if I can get this updated.

 
Anonymous 12/20/2011

I'm getting the following error for the User Visit Detail...followed the steps as listed.<br /><br />Error: Site Log is currently unavailable.<br />DotNetNuke.Services.Exceptions.ModuleLoadException: Parameter count does not match Parameter Value count. ---> System.ArgumentException: Parameter count does not match Parameter Value count. at Microsoft.ApplicationBlocks.Data.SqlHelper.AssignParameterValues(SqlParameter[] commandParameters, Object[] parameterValues) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at DotNetNuke.Data.SqlDataProvider.GetSiteLog(Int32 PortalId, String PortalAlias, String ReportName, DateTime StartDate, DateTime EndDate) at DotNetNuke.Services.Log.SiteLog.SiteLogController.GetSiteLog(Int32 PortalId, String PortalAlias, Int32 ReportType, DateTime StartDate, DateTime EndDate) at DotNetNuke.Modules.Admin.SiteLog.SiteLog.BindData() at DotNetNuke.Modules.Admin.SiteLog.SiteLog.cmdDisplay_Click(Object sender, EventArgs e) --- End of inner exception stack trace ---

 
Anonymous 12/20/2011

Edit: Nevermind, I reran the script and it worked the 2nd time..go figure :)

 
Accuraty 10/16/2013

I was interested in getting the LinkClicks Report, but the download does not currently appear to work. Can you fix it or provide a link that will work? Thanks - Jeremy

 
Bruce Chapman 10/22/2013

Having trouble with the Repository module. Please try this link: http://www.ifinity.com.au/Downloaded?link=downloads/ifinity.dnn.Reports.zip&pg=652&md=1406

Bruce Chapman
Hi, I'm Bruce Chapman, and this is my blog. You'll find lots of information here - my thoughts about business and the internet, technical information, things I'm working on and the odd strange post or two.
Connect with Bruce Chapman on Google+

Share this page
Get more!
Subscribe to the Mailing List
Email Address:
First Name:
Last Name:
You will be sent a confirmation upon subscription

Follow me on Twitter
Stack Exchange
profile for Bruce Chapman at Stack Overflow, Q&A for professional and enthusiast programmers
Klout Profile

Page Tags