Creating a simple page view report

Mike Bilz asked on February 21, 2018 20:49

Hello Kentico Team,

I am trying to build a simple report for the total number of page views for all blog posts within a certain blog.

My code so far returns all of the page views for all of the pages on my site, which is neat, but ultimately unhelpful.

SELECT SUM(HitsCount)
FROM Analytics_Statistics
INNER JOIN Analytics_YearHits ON Analytics_YearHits.HitsStatisticsID = 
Analytics_Statistics.StatisticsID
WHERE (StatisticsSiteID = @CMSContextCurrentSiteID)

How can I set the WHERE conditions to limit this report to only BlogPost items and only those with a specific parent page?

Thanks in advance.

-mike

Recent Answers

Brenden Kehren answered on February 21, 2018 23:37

What I'd do is take a look in the Reporting app and look at the page views reports SQL statements. This might help you get what you want with the best detail and results. This particular SQL statement is from the 'Page views - Daily report'. In there you may be able to filter the results even further by adding a path to it.

DECLARE @PaveViews TABLE
(
  PagePath NVARCHAR(500),
  ObjectID INT,
  Pageviews INT,
  Percents DECIMAL(10,2),
  Average INT  
)

DECLARE @Sum DECIMAL;

SET @FromDate ={%DatabaseSchema%}.Func_Analytics_DateTrim(@FromDate,'day');
SET @ToDate ={%DatabaseSchema%}.Func_Analytics_EndDateTrim(@ToDate,'day');

SELECT @Sum =   
  SUM(HitsCount)
  FROM Analytics_Statistics
  INNER JOIN Analytics_DayHits ON Analytics_DayHits.HitsStatisticsID = Analytics_Statistics.StatisticsID
  LEFT JOIN View_CMS_Tree_Joined ON View_CMS_Tree_Joined.NodeID = Analytics_Statistics.StatisticsObjectID  AND StatisticsObjectCulture = DocumentCulture
  WHERE (StatisticsSiteID = @CMSContextCurrentSiteID)
  AND (StatisticsCode=@CodeName)
  AND (HitsStartTime >= @FromDate)
 AND (HitsEndTime <= @ToDate)

INSERT INTO @PaveViews (PagePath,ObjectID,PageViews,Percents)
  SELECT TOP 100 StatsWithPath.NodeAliasPath, StatsWithPath.ObjectID, SUM(StatsWithPath.HitsCount) AS PageViews, (SUM(StatsWithPath.HitsCount)/@Sum)*100 AS Percents FROM
  (
    SELECT
     -- When NodeAliasPath is empty, use value from StatisticsObjectName instead. This could happed for example when document does not longer exist in one of the cultures.
     -- In this case row can't be joined with View_CMS_Tree_Joined.
     CASE
      WHEN NodeAliasPath = '' OR NodeAliasPath IS NULL THEN StatisticsObjectName
      ELSE NodeAliasPath
     END AS NodeAliasPath, StatisticsObjectID AS ObjectID, HitsCount 
     FROM Analytics_Statistics
     INNER JOIN Analytics_DayHits ON Analytics_DayHits.HitsStatisticsID = Analytics_Statistics.StatisticsID
     LEFT JOIN View_CMS_Tree_Joined ON Analytics_Statistics.StatisticsObjectID = View_CMS_Tree_Joined.NodeID AND StatisticsObjectCulture = DocumentCulture
     WHERE (StatisticsSiteID = @CMSContextCurrentSiteID) AND (HitsStartTime >= @FromDate) AND (HitsEndTime <= @ToDate) AND (StatisticsCode = @CodeName)
  ) AS StatsWithPath
  GROUP BY StatsWithPath.NodeAliasPath, StatsWithPath.ObjectID
  ORDER BY PageViews DESC

 UPDATE @PaveViews SET Average = (SELECT SUM(HitsValue)/SUM(HitsCount) FROM Analytics_DayHits JOIN
      Analytics_Statistics ON HitsStatisticsID = StatisticsID
      WHERE HitsStartTime >= @FromDate AND HitsEndTime <= @ToDate AND StatisticsObjectID = objectID
        AND StatisticsCode ='avgtimeonpage' AND StatisticsSiteID = @CMSContextCurrentSiteID
       )

 SELECT PagePath AS '{$reports_pageviews_Day.path_header$}',pageviews AS  '{$reports_pageviews_Day.hits_header$}',
      CAST (Percents AS NVARCHAR(10))+'%' AS '{$reports_pageviews.percent_header$}', ISNULL(CONVERT(varchar, DATEADD(s, average, 0), 108),'00:00:00') AS '{$reports_pageviews.average$}'

   FROM @PaveViews ORDER BY PageViews DESC

0 votesVote for this answer Mark as a Correct answer