May 03, 2007

View Statistics of your News Article Module Blog

As I see more and more people using the Ventrian News Articles module for their blogs I have to imagine that others are curious as I am as to which blog posts are the most popular, the highest rated, or maybe even the lowest rated.  Yes I understand that I could look through my blog and find this out, but with as many posts as I have I decided to do some research and have made a few helpful queries that will provide this type of information.  This entry will walk you through each of the scripts.

Most Viewed Articles

I will start with the most simple query of them all, the "Most Viewed" articles. The query below will search the News Articles table and return the 20 top viewed articles.  This is very helpful to determine if users are potentially interested in one specific type of article, or articles on a particular subject.  One thing to note is that this ONLY applies for articles which have at least 1 page of content.  (Where users must click "Read More" to view the whole article.)

--Gets a listing of articles with the most views
--ONLY reports on approved, active articles
SELECT TOP 20 Title,
                NumberOfViews
FROM DnnForge_NewsArticles_Article
WHERE IsApproved 1                --only approved articles
      
AND IsDraft 0               --Not a draft
      
AND StartDate <= GETDATE()    --Must be authored before today
      
AND (EndDate IS NULL
            OR 
EndDate >= GETDATE())--Must never expire, or still be current
ORDER BY NumberOfViews DESC

The above query is quite simple, the thing to note are the filters being applied to only get active approved articles.  This is done to prevent any articles that have not been approved or are no longer available from affecting the numbers.

Top Rated Articles

User ratings of an article are another big item that I like to monitor. The below query will list the 20 top rated articles by the users, you will be provided the title, a count of the ratings, and the average rating score for the article.  Note: Since this query uses an INNER join articles with no rating will NOT be shown.

--Gets a listing of the top rated articles
--Also providing a count of ratings
SELECT TOP 20 a.Title,
        COUNT(r.rating) 
AS "Num. Ratings",
        
AVG(r.rating) AS "Avg. Rating"
FROM DnnForge_NewsArticles_Article a
    
INNER JOIN DnnForge_NewsArticles_Rating r
        
ON (a.articleId r.articleId)
WHERE IsApproved 1                --only approved articles
      
AND IsDraft 0               --Not a draft
      
AND StartDate <= GETDATE()    --Must be authored before today
      
AND (EndDate IS NULL
            OR 
EndDate >= GETDATE())--Must never expire, or still be current
GROUP BY a.Title
ORDER BY AVG(r.rating) DESC --CHANGE TO ASC to see lowest ratings

Yes, this query does look quite complex, however it is not all that more complicated than the query for most viewed. We are using the exact same where clause to ensure that we are only reporting on active articles and we simply join to the Rating table to get the rating information.

Top Commented Articles

The last item I will investigate are the most "commented" articles. These would be the articles that contain the largest number of user comments. As with the other queries this will ONLY return records for articles with comments.

--Gets a listing of the most commented articles
SELECT TOP 20 a.Title,
        COUNT(c.commentId) 
AS "Comment Count"
FROM DnnForge_NewsArticles_Article a
    
INNER JOIN DnnForge_NewsArticles_Comment c
        
ON (a.articleid c.articleid)
WHERE a.IsApproved 1                --only approved articles
      
AND a.IsDraft 0               --Not a draft
      
AND a.StartDate <= GETDATE()    --Must be authored before today
      
AND (a.EndDate IS NULL
            OR 
a.EndDate >= GETDATE())--Must never expire, or still be current
GROUP BY a.Title
ORDER BY COUNT(c.commentid) DESC

This query just like the previous one is a fairly straight forward query, using the same where clause as the other two queries.

Conclusion

The above queries have helped me get a better idea of the activity and demands on my blog using the News Articles module.  Hopefully these queries will provide you useful information as you are working to build your site.  Be sure to modify the table names if you have used an object qualifier for your database as this does NOT take this into consideration.  Please let me know if you have any questions.

tags: DNN, Tutorials, SQL
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.