Like many who use Rock RMS, we're always looking for ways to improve the staff experience—and being the data-driven folks we are, we
couldn't help but ask: What are the most commonly viewed pages in our internal staff portal?
We had our suspicions (👋 Person Profile page), but we wanted to validate them with real data. So, we put together a
simple SQL script to dig into our page view analytics and sort the winners from the rest.
A Peek Behind the Curtain
This turned into a fun internal conversation, and we figured - why keep the nerdy joy to ourselves? Below is the same
SQL we used. Feel free to run it against your own instance and see what shows up!
We even added a column for average page load time, so you can keep an eye on any performance red flags while you’re at it.
🛠️ At the top of the script, you’ll find parameters to adjust how far back to look and which site to analyze.
And the Winner Is...
DECLARE @SiteId INT = 1 -- Rock Site Id
DECLARE @DaysBack INT = 30
SELECT
x.[EntityId] AS [PageId]
, FORMAT(x.[PageViews], 'N0') AS [PageViews]
, CAST( x.[AvgTimeToServe] AS decimal(6,2)) AS [AvgTimeToServe]
, p.[PageTitle] AS [PageName]
, par.[PageTitle] AS [ParentPageName]
, gpar.[PageTitle] AS [GrandParentPageName]
, (SELECT TOP 1 [Route] FROM [PageRoute] WHERE [PageId] = x.[EntityId]) AS [Route]
FROM (
SELECT
ic.[EntityId]
, COUNT(*) AS [PageViews]
, AVG(i.[InteractionTimeToServe]) AS [AvgTimeToServe]
FROM [Interaction] i
INNER JOIN [InteractionComponent] ic ON ic.[Id] = i.[InteractionComponentId]
INNER JOIN [InteractionChannel] ich ON ich.[Id] = ic.[InteractionChannelId]
INNER JOIN [DefinedValue] m ON m.[Id] = ich.[ChannelTypeMediumValueId]
WHERE
ich.[ChannelEntityId] = @SiteId
AND m.[Guid] = 'e503e77d-cf35-e09f-41a2-b213184f48e8'
AND i.[InteractionDateTime] >= DATEADD(DAY, -@DaysBack, GETDATE())
GROUP BY ic.[EntityId]
) x
INNER JOIN [Page] p on p.[Id] = x.[EntityId]
LEFT OUTER JOIN [Page] par ON par.[Id] = p.[ParentPageId]
LEFT OUTER JOIN [Page] gpar ON gpar.[Id] = par.[ParentPageId]
ORDER BY x.[PageViews] DESC
No surprise here—the Person Profile page takes top billing. And when you consider all the subpages that funnel through the profile page, its lead is even more impressive.
Give it a try and let us know what your top pages are. Maybe you’ll discover a hidden gem that your team leans on more than you expected—or a page that's dragging its feet on performance that we could help you with. And if you'd like to see deeper analytics for your internal (and external) Rock sites, download our Site Analytics plugin.