Last week one of my friend asked me whether I have worked on something that kinda allows us to show Sitecore Analytics data like Top 5 Visited pages of a website in a 404 error page…Something like page you searched for is not found…Interested in checking out these top visited pages….(Probably some content with better English)
Although I haven’t worked on such requirement but the idea of displaying some analytics data in a webpage of the website thereby providing a better user experience caught my attention.
So I explored it further and thought I would share about the steps involved.. in this article.
Sitecore has a reporting DB that has details about these data. The reporting database contains aggregated data from the xDB collection database. In Experience Analytics module, were you see the Number of Visits, Top Page Entry/Exit etc are presented based on the data in this DB.
This reporting db has a table Fact_PageViews. Fact table is a data warehousing term used to describe tables that contain measurements and metrics. In a fact table foreign keys allow joins to be made with dimension tables.
After exploring this DB/table for few mins wrote a query that solves our requirement.
So, now all we have to do is create some logic that runs this query when needed and provides the Item IDs. Based on this Item ID, we will be able to get the page details like page name and URL which can be presented in the rendering.
After some browsing I came across Sitecore Report Query.
Sitecore has this Report Query template that allows you to specify the datasource (the DB) and the query that needs to be executed.
So, create an item based on this template and include the necessary values. (Datasource – reporting and our query)
From code prespective,
Create a class that is based on ItemBasedReportingQuery. Make use of the item ID of the report query item we just created and pass them in the constructor (base)
Implement the Execute method and include our logic there. After the query is executed, the results are available in datatable format, from which we can convert them to our custom format.(PageandViewCount).
Create a helper class/method that executes this query.
Finally, based on the item id we can get the item. Based on the item we can get the page name/page url. Create a corresponding rendering and add it to the required page.
There is also another way to execute sitecore query, using the reporting API straight away without using the report query item. But here you need to hard code the query or fetch it from some constants.
Sitecore Blog 