This is the first in what we plan on being a 3 part series on improving the tracking and reporting of Ad viewership and clickthroughs within DNN.
Overall, DotNetNuke'sBanner / Ad / Vendor implementation is a nice solution. It does a great job of serving up the appropriate ads based on a nice variety of options. But there's been very little change to the implementation over the last few years and we've found some areas of needed improvement. Efficion is hoping to contribute new features and improvements to these modules based on work we've done to bring it up to the next level.
One of the biggest weaknesses we found was it's reporting capabilities. It's easy enough for us to create reports for our clients (we'll be covering that in Part II of this series) but in this case, we didn't have the data we needed.
Our client wanted to be able to provided statistics on the views and clicks of their ads for a given time period (i.e. September 2010). The problem is, DNN only stores the total number of clicks and views for a given ad. There's no associated time period with those numbers.
So, we need to start storing clicks and views in a way that is time oriented. After some thought on this we decided that we should really accumulate the numbers on daily basis for each ad. Daily seems to be the standard in the analytics world and provides enough granularity to get the time frame you want while also not collecting data at too granular a level.
Daily totals will make it easy to create reports that show totals for a given period or to create trend charts for a particular ad or all ads over a given time period.
Note: I view this as just a temporary approach that allows me to get the data I need until I can make some broader changes to the related modules. We'll be exploring those changes in Part III of this series.
In order to track views and clicks over time, we need to make three fairly simple database changes: Adding a new table, and two modifications to existing DNN stored procedures for updating our new table.
First, create a new table where the daily click and view data will be stored.
Update the UpdateBannerViews Stored Procedure to also write to our new table.
Update the UpdateBannerClickThroughs Stored Procedure to also write to our new table.
That's it. No code changes are required to track this data.
This meets our needs for now but it does raise some real problems with the way DNN is handling tracking these stats. The main issue is the number of writes that are occurring against the database. For every ad being displayed on a page, there is a separate write to the database every time the page is viewed. For a site like DotNetNuke.com, which has thousands of visitors every hour and lots of ads on every page, this has to be a serious performance drain. So, we'll be looking at ways to better optimize this code in part III of this series.
But first, this data isn't much good if your client, and the advertisers can't see it. So, for part II, we'll be looking at creating reports to display this data in useful ways.