Which table stores pageview webparts HitCount?

sarathlal s asked on May 19, 2014 02:00

Can you please tell which table stores pageview webpart's Hitcount??

page views

Correct Answer

Jakub Kadlubiec answered on May 20, 2014 05:36

Ok, in that case I recommend writing custom query to get most visited articles. Something like this might do:

SELECT TOP 5 NodeID, NodeAliasPath, Hits.TotalHitsCount
FROM
  (
  SELECT SUM(HitsCount) AS TotalHitsCount, StatisticsObjectID
  FROM Analytics_Statistics
  INNER JOIN Analytics_YearHits ON StatisticsID = HitsStatisticsID
  WHERE StatisticsSiteID = 144 AND StatisticsCode = 'pageviews'
  GROUP BY StatisticsObjectID
  ) AS Hits
LEFT JOIN CMS_Tree ON NodeID = Hits.StatisticsObjectID
WHERE NodeParentID = 61396
ORDER BY TotalHitsCount DESC

Please replace 144 with the ID of your site and 61396 with the NodeID of document which contains articles.

1 votesVote for this answer Unmark Correct answer

Recent Answers

Jakub Kadlubiec answered on May 19, 2014 03:30

There are more tables depending on interval:

Analytics_HourHits
Analytics_DayHits
Analytics_WeekHits
Analytics_MonthHits
Analytics_YearHits

Statistics code name is "pageviews".

Best way to get this number via API is to use HitsInfoProvider.GetObjectHitCount(...) method.

0 votesVote for this answer Mark as a Correct answer

sarathlal s answered on May 19, 2014 03:40

How can i use it to take hit count of a particular document in my content tree? and i read we can use query repeater fro this.How and where i can write query fro this?

0 votesVote for this answer Mark as a Correct answer

Jakub Kadlubiec answered on May 19, 2014 04:37

Check the source code of the web part you mentioned in the original question: \CMSWebParts\Viewers\PageViews.ascx.cs. Method HitsInfoProvider.GetObjectHitCount(...) is called there to retrieve number of page views for the particular document. You can get data for specific document by passing NodeID as the second parameter of the method (parameter objectId).

I don't know why you would want to write query to get those numbers. Please be more specific and describe what do you want to accomplish.

0 votesVote for this answer Mark as a Correct answer

sarathlal s answered on May 19, 2014 05:49

Thanx Jakub Kadlubiec for your reply. i have a section for articles in my ContentTree name "MyArticles". It have 100 child documents of type Article. I need to list top 5 most read article from these articles in the home page.

0 votesVote for this answer Mark as a Correct answer

sarathlal s answered on May 20, 2014 06:51

Thank you very much Jakub Kadlubiec. I tried this code. And i added a repeater with custom query to list the articles. But it is not showing any results. I added query and created my transformation. Still it is not working

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on May 20, 2014 07:54

@Sarathlal, your StatisticsSiteID will be different as well as your NodeParentID as Jakub has mentioned. Have you tried turning on Debugging to see what your SQL statement is returning?

0 votesVote for this answer Mark as a Correct answer

sarathlal s answered on May 21, 2014 01:04

@Brenden Kehren.. ya i changed StatisticsSiteID and NodeParentID.. I tried directly in my database. it is working fine.

Is there any change need to make in transformation? my query is

SELECT TOP 5 NodeName FROM ( SELECT SUM(HitsCount) AS TotalHitsCount, StatisticsObjectID FROM Analytics_Statistics INNER JOIN Analytics_YearHits ON StatisticsID = HitsStatisticsID WHERE StatisticsSiteID = '4' AND StatisticsCode = 'pageviews' GROUP BY StatisticsObjectID ) AS Hits LEFT JOIN CMS_Tree ON NodeID = Hits.StatisticsObjectID WHERE NodeParentID ='164' ORDER BY TotalHitsCount DESC

and transformation is

Code <li><a href="<%# GetDocumentUrl() %>"><%#Eval("ArticleName", true) %></a></li> .

because it's article document type. Please guide me is any additional information needed in transformation??

Thanks Sarat

0 votesVote for this answer Mark as a Correct answer

sarathlal s answered on May 21, 2014 01:48

thank you, it works....Now i have 5 languages...and its names are different in different culture. How cam i change the article name to corresponding culture?

0 votesVote for this answer Mark as a Correct answer