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

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

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

 

Advertisements

2 Responses to “Sitecore Reporting : Top Page Views”

  1. Gareth Goodman Says:

    Hi 🙂

    I’m trying to figure out how to adapt this to Sitecore 8’s analytics db structure, which is completely different. Have you had a go at doing this ?

  2. Ravi Says:

    Can you show us the whole process or provide us the video URL that how we can do it?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: