A while back, I created a blog post that explained how to Sort Sitecore Items by Popularity using Google Analytics. In this post, I will explain how to achieve the same goal using xDB.
As a quick recap, clients may have a requirement to sort Sitecore items by Most Viewed. In many instances, Sitecore Analytics and xDB are already being used to track analytics information. We should be able to retrieve the data we need from xDB as well as the reporting database.
There may be alternate ways to retrieve the data from xDB and the reporting database. I am going to take the route that includes adding a Page Event and a custom Fact Table, demonstrating how they function.
Setting the Scene
The client has a requirement to list the most viewed items based on a given template type in the past month. In English: the client wants to display the most viewed Articles in the past month.
To achieve this requirement, we need to track the number of views each item has had. To get results by template, we need to track the template IDs and then filter by them. “In the past month” is a sliding window. The easiest way to handle this sliding window is to keep track of the total views per day.
We want to track the template ID associated with the Item viewed. The template ID is not included in the Interaction data in xDB. Looking up items by template ID will be much faster if the template ID is already included in the Analytics Data.
Note: You can view exactly what is being tracked per interaction by viewing the data in Mongo DB. To do this, you can use a tool such as Robomongo. Using the tool, open the Sitecore “analytics” Mongo Database. View the documents in the “Interactions” collection. Expanding each document will show you what is tracked per interaction. Within the document, expand the “Pages” node. You should see all the pages visited during this interaction. If you drill into a page, you can see what is tracked per page.
Using a page event is one way to add additional information to an item when it is viewed. We can use a page event to track the template ID of the item. To add a page event, we need to register it within Sitecore. In the Content tree (master database), navigate to System / Settings / Analytics / Page Events. From there, create a new Page Event and give it a name of “View”.
Once the page event is added in Sitecore, we need to register the event when an item is viewed. First, create a pipeline processor to that registers the page event.
Notice in the above example that the Data is the template ID.
We now need to add the pipeline processor to the <endAnalytics> pipeline.
Now that the Page Event is registered in Sitecore, and a custom pipeline has been configured, we should see the View Page Event appear in Mongo DB within the Page Interactions.
Note: you may not see your interactions until your session ends and the results are written to xDB. You can programmatically update the analytics database without ending the visitors session by following the example here.
In order to more easily retrieve the data we are logging via page events, we should aggregate this data into a Fact table in the Sitecore Reporting database. The data will be aggregated by day and item. From this we will be able to tell how many times an item was viewed per day. We will also keep track of the Template ID for quick lookups.
First, create a table in the reporting database that contains the ItemId, Date, TemplateId and Count. The reporting database is usually the one with “analytics” in its name.
Now that we have our table, we can create the aggregator that populates the table with data. Sitecore needs a Fact<> class defined that supports the new fact table. The fact class and related classes are defined as follows:
These classes can then be used within an AggregationProcessor that handles aggregating the data from xDB into the reporting database.
This processor needs to be registered via configuration.
Now as analytics data is collected, the Fact_Views table should be populated with aggregated data. You can test this out by navigating your site and watch the Fact_Views table populate.
Gathering the List of Top Items
Now that the analytics data exists in a form we can work with, it is time to query the data. We can create a helper method to retrieve the data.
These results should be cached to improve performance. I will leave it up to the reader to cache the results based on requirements.
As you can see, it is pretty straightforward to use the data from Sitecore Analytics to find the Items that are most viewed.