As the year tapers off to a close, I figured I’d continue with a tradition and list the top ten posts of the year. I know it sounds simple in theory, but much like a simple exercise in Business Intelligence and project metrics, I found myself spending an inordinate amount of time trying to figure out just how to calculate what exactly should comprise the top ten list. As in many circumstances where a client asks a seemingly simple question, this question is actually quite tough to answer.
Hence, following the footsteps of many PMs before me, I engaged in the time honored ritual of taking something that should really be quite simple, and made it much more complicated than it actually deserves. For those bloggers out there reading this, take this as a gauntlet thrown down to challenge you to come up with an even more complicated blog rating metric in coming years.
First off the list (in alphabetical order). Following that, for those people who are actually interested, a few notes about the methodology.
- Adding Filtered Project Dashboards to PDPs (Part 1)
- Automatically Filtering Excel Reports in PerformancePoint Dashboards
- Capturing Custom Timescaled Data in Project Server (Part I)
- Depicting Detailed Timeline Views with Visio Reports
- First Look: Querying Project Server 2013 OData with LINQPad
- Flagging Project Owner Changes with OData and VBA
- Project 2013 On-Premises: The Missing Settings
- Project Server Business Intelligence Resources (Part 1)
- Project Server Business Intelligence Resources (Part 3)
- Task Paths in Project 2013
Now, a note about the methodology:
I could have been lazy and simply looked at the total number of hits each blog post received over the year – which is what I did last year. This would have resulted in a front loaded list of top posts, as clearly the posts I published in the beginning of the year would have had all year to accumulate hits, whereas the posts at the end of the year would have only had a few months to accumulate posts. Hence, option #1 would have been front loaded with earlier posts.
The second option would have been to take all of the posts and pro-rate them based on the total number of hits divided by the total number of working days since the post was published. This would have been a fair approach – if the overall blog readership had remained static over the year. But the blog readership did not remain static. In fact, I’m happy to report that the monthly traffic on this site is up 100% over the same time period in 2011. Hence, the average daily hit rate per post in the latter half of the year was much higher than the earlier part. This approach would have meant that the latter 47% of the posts were basically freeloading off of the audience generated by the posts in the first half off the year. Clearly, that situation would have been untenable.
So I settled on option #3: normalize the number of hits/post (filtered on 2012 posts only) per day; sum the results into a normalized number of hits, and then divide that normalized score by the number of working days since the post was published. It’s quite simple, really. Even simpler when you realize that WordPress offers an API to generate the required data.
I’ll spare you all of the boring details, but I was able to leverage some of my OData querying skills to generate the following URL:
This ends up generating our data set, which may be downloaded into Excel.
Once in Excel, I was able to do all of the required calculations using Vlookups and a couple of PivotTables. For those dedicated readers out there, feel free to provide additional suggestions on how we can take this relatively simple exercise and make it even more extraordinarily complicated in future years. (Did someone mention Eigenvectors?)
In other words….happy holidays and wishing you the best in 2012 and 2013!