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 ArticlesI 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 ArticlesUser 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 ArticlesThe 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. ConclusionThe 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.
|