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.[caption id="attachment_651" align="alignnone" width="780"]

Top Page Views Report Snapshot[/caption]It is fairly straight forward to create custom reports in Sitecore, but that topic is for another dayThe SQL Query which returns the top page viewsSitecore 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 QueriesBelow is the SQL query I created for the Top Page Views Report[sourcecode language="csharp"]SELECT TOP 50Pages.ItemId,(SELECT top 1 Page2.Url from Pages as Page2 where Page2.ItemId = Pages.ItemId) as Url,COUNT(*) AS TotalFROMPages,Visits,VisitorsWHEREPages.DateTime BETWEEN @StartDate AND @EndDateAND Pages.ItemId!='00000000-0000-0000-0000-000000000000'AND Visits.VisitId=Pages.VisitIdAND Visits.VisitorId=Visitors.VisitorIdGROUP BYPages.ItemIdORDER BYTotal DESC[/sourcecode]I have created a Sitecore Package for the top page views report which will install the following itemsDOWNLOAD SITECORE PACKAGEFiles:

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 ReportAfter 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" reportyou 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