Confluence SQL query for pageviews by user?


I'm developing a Confluence usage index for our staff and would like to run a SQL query that provides a count of pageviews by user the last three months.

I already have SQL queries for comments by user, page edits by user, and page creation by user. Is there any way to get the pageviews, too? Ideally, my table would look like this:

||User name||Pageview count||

|John Doe|165|

|Jane Smith|145|

|Texas Pete|25|

5 answers

1 accepted

This widget could not be displayed.

Confluence does not store content view statistics in the database, unfortunately.

On a related note, the Usage Tracking Plugin work around this by creating its own indexes.


At my company, we would like a list of page views by space, for all spaces. We want something that is similar to the "Most Popular Spaces (Views)" list in the Confluence Admin/Global Activity page -- but for all spaces. If the page view data is not stored in the database, where is it stored? The "Most Popular Spaces (Views)" list must be getting its data from somewhere.



This widget could not be displayed.

Let me suggest an alternative metric for "popularity", different from the SUM(PAGEVIEWS) per space.

The page view tracking feature of the Archiving Plugin logs the last view date on each page. These are written to the AO_xxx_PAGE_VIEW table in your database, keyed with the page ID. You can easily write an SQL query to merge the page table with their last view date, and aggregate those records by space in some way.

| ID  | PAGE_ID | VIEWER_NAME | VIEW_DATE           |
|  50 | 2470703 | admin       | 2013-09-18 16:52:04 |
| 134 | 2470468 | admin       | 2013-09-18 16:52:09 |
| 139 | 2470823 | admin       | 2013-09-18 16:52:04 |

For example, you could compute an AVG(LAST_PAGE_VIEW_DATE) per space as to measure "activity level" (or "popularity"). (Hint: use some very old date for those pages for which there is no page view date logged.)

This widget could not be displayed.

We also developed a custom plugin some time ago exactly for this purpose: Viewtracker plugin

This widget could not be displayed.

fyi... see Site Statistics for similar things. Also, you can contribute to that.

This widget could not be displayed.

@Remo Siegwart Could you provide a reference for read-only accessing the viewtracker stats, please?

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

530 views 7 6
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you