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.
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
Files:
- {root}/Website/sitecore/shell/Applications/Analytics/Reports/Custom Reports/TopVisits.mrt (This is the .mrt file)
Sitecore Items:
- /sitecore/system/Settings/Analytics/Reports SQL Queries/Top Pages (This is the SQL Query)
- /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
