Sitecore Reporting : Top Page Views

Sitecore out of the box does provide a lot of useful reports which helps the marketers in analyzing their visitors, In my experience One of the most requested report and the one which is missing is the Top Page Views report, A report which can list out the most visited pages filtered by Date.

report1
Top Page Views Report Snapshot

It is fairly straight forward to create custom reports in Sitecore, but that topic is for another day

The SQL Query which returns the top page views

Sitecore OOTB does a pretty good job in providing a comprehensive list of pre built SQL queries. They can found here :  /sitecore/system/Settings/Analytics/Reports SQL Queries

Below is the SQL query I created for the Top Page Views Report

[sourcecode language=”csharp”]
SELECT TOP 50
Pages.ItemId,
(SELECT top 1 Page2.Url from Pages as Page2 where Page2.ItemId = Pages.ItemId) as Url,
COUNT(*) AS Total
FROM
Pages,
Visits,
Visitors
WHERE
Pages.DateTime BETWEEN @StartDate AND @EndDate
AND Pages.ItemId!=’00000000-0000-0000-0000-000000000000′
AND Visits.VisitId=Pages.VisitId
AND Visits.VisitorId=Visitors.VisitorId
GROUP BY
Pages.ItemId
ORDER BY
Total DESC
[/sourcecode]

I have created a Sitecore Package for the top page views report which will install the following items

DOWNLOAD SITECORE PACKAGE

Files:

  1. {root}/Website/sitecore/shell/Applications/Analytics/Reports/Custom Reports/TopVisits.mrt (This is the .mrt file)

Sitecore Items:

  1. /sitecore/system/Settings/Analytics/Reports SQL Queries/Top Pages (This is the SQL Query)
  2. /sitecore/system/Settings/Analytics/Reports/Reports/Custom Reports/Top Pages (This is the Report Definition)

How to View the Report

After installing the package, go to the Engagement Analytics section and then navigate to the “Custom Reports” in the left navigation bar and select “Top Page” report

you can further customize this report to add filters based on the Site, Language etc..

Should you have any questions , Please comment or email me at sjain@horizontalintegration.com or tweet @ sjain_HI