Tusk Software - Sorting Kentico documents by popularity

Introduction

Kentico comes with most of the features blogs typically need. One feature missing from the out-of-the-box version is a tool for creating a list of the most-viewed posts on the site. In this post, we will go over how to create a function that produces this list.

Prepping the Basics

The easiest way to achieve our objective is to use Kentico’s analytics tables. With the help of various tables (Analytics_HourHits, Analytics_DayHits, Analytics_WeekHits, etc.), we can create and display top posts lists and sort them by week, month, or all time. Before following these steps, make sure that Web Analytics is enabled and is tracking page views.

Instructions

Kentico Ultimate

As you might have anticipated, we will use a repeater and a custom query to display the results.

Create a custom query in the blog section of the site and name it “TopPosts”.
This query will return a list of blog posts along with the total page views of each post.

SELECT ##TOPN## ##COLUMNS##, SUM(HitsCount) FROM  View_CONTENT_BlogPost_Joined
left outer join Analytics_Statistics on StatisticsObjectID = NodeID
  and StatisticsObjectCulture = DocumentCulture
left outer join Analytics_DayHits on StatisticsID = HitsStatisticsID 
WHERE (StatisticsSiteID = 1) and (StatisticsCode='pageviews') and ##WHERE##
GROUP BY ##COLUMNS## 
ORDER BY ##ORDERBY##

Set “ORDER BY” to “SUM(HitsCount) desc”.
Create columns for “DocumentUrlPath,” “DocumentName,” “NodeAlias,” “NodeAliasPath,” “BlogPostTitle,” “BlogPostSummary,” and “BlogPostDate.” You can add additional fields if you require them.
Set up the rest of the query as needed.

If you want to display a monthly most viewed posts list, update your query to return data from “pageviews” for the current month only.

SELECT ##TOPN## ##COLUMNS##, SUM(HitsCount) FROM  View_CONTENT_BlogPost_Joined 
left outer join Analytics_Statistics on StatisticsObjectID = NodeID
  and StatisticsObjectCulture = DocumentCulture
left outer join Analytics_DayHits on StatisticsID = HitsStatisticsID 
  and (HitsStartTime >= DATEADD(month, DATEDIFF(month, 0, GetDate()), 0))
WHERE (StatisticsSiteID = 1) AND (StatisticsCode='pageviews')
and ##WHERE##
GROUP BY ##COLUMNS## 
ORDER BY ##ORDERBY##

As you might have noticed, this query is very resource-intensive and will affect the performance of the server if you have a large number of posts and/or visitors. However, we can easily optimize it with a few lines of code.

Create the integer-type column “BlogPostViews” in “CONTENT_BlogPost.” We will use it to sort out the list.

Add a custom query and name it “UpdatePageViews”. This query will calculate the total hits and update “BlogPostViews”.

UPDATE CONTENT_BlogPost set BlogPostViews = (SELECT SUM(HitsCount) FROM 
Analytics_Statistics, Analytics_DayHits, View_CONTENT_BlogPost_Joined p
WHERE (StatisticsCode='pageviews') and (StatisticsID = HitsStatisticsID)
  and (StatisticsObjectID = p.NodeID) and (StatisticsObjectCulture = p.DocumentCulture)
  and p.BlogPostID = CONTENT_BlogPost.BlogPostID)

Again, this query can be customized to filter results by week, month, and year.

Now, create a scheduled task, name it “PageViewsUpdater”, and place it in the App_Code folder or the Old_App_Code folder if you are using Kentico as a web application.
This task will execute the query we created above.

[assembly: RegisterCustomClass("PageviewsUpdater", typeof(PageviewsUpdater))]
public class PageviewsUpdater : ITask
{
    public string Execute(TaskInfo ti)
    {
        return updatePageviews();
    }

    private string updatePageviews()
    {
        try
        {
            var query = new DataQuery("CMS.BlogPost.UpdatePageviews");
            query.Execute();
            return "Page views Updated";
        }
        catch (Exception ex)
        {
            EventLogProvider.LogException("PageviewsUpdater", "E", ex);
        }
        return String.Empty;
    }
}

Register this task in Kentico and set it to run once a day. You can schedule it to run more frequently depending on your needs.
Next, use the repeater web part with a custom query and set “ORDER BY” to “BlogPostView Desc”.

Kentico Base

The approach we described above uses the Web Analytics module, which requires a Kentico Ultimate license. If you have a Base license, you can create top posts lists using a different approach.

First, we’ll need to create a new table. It can be a custom table or a custom module table. It will store the data that NodeGuid and ImpressionDateTime return.
Next, we’ll create a hidden script that adds a record to this table whenever page is loaded. If you don’t want search engine crawlers to execute it, you can fire an AJAX request from a web service client (or handler) with the NodeGUID of CurrentDocument, which will add a record to the table.

Update the custom query in the schedule task.

update CONTENT_BlogPost set BlogPostViews = (SELECT Count(ImpressionDate) FROM 
CustomPageViews c, View_CONTENT_BlogPost_Joined p
WHERE c.NodeGUID = p.NodeGUID)

Conclusion

We have learned how to order blog posts by total views. If you have a Kentico Ultimate License, you don’t have to worry about writing much code or modifying Kentico project files. The other two approaches require you to have some web development knowledge.

Please leave your thoughts and comments below. Let us know if you have a different approach to solve a similar problem!

Blog Article:

Sorting Kentico documents by popularity