Confluence SQL query for pageviews by user?

[ITops123]

Hi,

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|

[Azwandi Mohd Aris · Accepted solution]

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.

[Brad_Puett]

Azwandi,

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.

Thanks.

Brad

[Stan the man]

I don't really think view statistics aren't stored in the database as you can see persistent data when drilling into "Page View Statistics" maybe wasn't an option back on 2011 but now is

[Midori]

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.

mysql> SELECT * FROM AO_1991C6_PAGE_VIEW;
+-----+---------+-------------+---------------------+
| 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.)

[VijayakumarE]

I don't see any table name like "Page_View" in the db.

[Radek Janata]

We have Page Views add-on installed and it has its own DB/AO tables. Using this add-on, I can then query DB and prepare some basic statistics.

E.g., top 10 most viewed pages on the instance (MySQL syntax):

SELECT
    PT.PAGE_ID
  , PT.SPACE_KEY
  , PT.PAGE_NAME
  , C.CONTENTTYPE
  , U.display_name AS author
  , C.CREATIONDATE
  , COUNT(*) AS uniqusers
  , SUM(PT.TIMES_VIEWED) AS pageviews
FROM AO_572ACE_PAGE_TRACKING PT
LEFT JOIN CONTENT C ON C.CONTENTID = PT.PAGE_ID
LEFT JOIN user_mapping UM ON UM.user_key = C.CREATOR
LEFT JOIN (
  SELECT DISTINCT U.lower_user_name, U.display_name 
  FROM cwd_user U
  ) U ON U.lower_user_name = UM.lower_username
-- WHERE PT.SPACE_KEY = 'MYSPACE' -- optionally select just one space
GROUP BY PT.PAGE_ID
ORDER BY pageviews DESC
LIMIT 10

[NCFCU IT]

I know this is an old post @ITops123 but in the OP you mentioned queries for edits by user. I'm struggling to get this working myself, if you're able to share that query.

[Evgeny Victorov]

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

[Bob Swift]

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

[Remo Siegwart]

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