Data Access
Overview
This page describes how users can access the database to obtain the page view information for automation or bulk data retrieval.
Confluence Database Tables
The information is stored in the Confluence database, using Active Object tables, these tables are called:
- AO_B0BF90_PVT_PAGE_VIEWS
- AO_B0BF90_PVT_USER_VIEWS
- AO_B0BF90_PVT_ATTACHMENT_DL
Data Retrieval
To retrieve the specific data, you will need to have access to the underlying Confluence database, this can only be done via the Confluence admins.
Once access is granted, you will need either an SQL command line utility or a database viewer to execute the SQL statements.
Use the following statement prototype, the context needed for each statistic is described below:
select * from public."AO_B0BF90_PVT_PAGE_VIEWS";
Page Views
The PAGE_VIEWS table contains specific information about each page. There will be at most one row per Page for the site.
It contains the following columns
| Name | Key | Type | Description |
|---|---|---|---|
| ID | Yes | Integer | A unique row ID |
| CONTENT_ID | BigInt | The Page or BlogPost content ID, there is one row for each Page/BlogPost tracked | |
| FIRST_VIEWED | String | The date (formatted string) this page was first viewed and tracked by Page View Tracker.This column is obsolete, unless FIRST_VIEWED_TS is null. | |
| FIRST_VIEWED_TS | bigint | The date (epoch) this page was first viewed and tracked by Page View Tracker.This column should be used if not null. | |
| LAST_VIEWED | String | The date and time (formatted string) the page was last viewed.This column is obsolete, unless LAST_VIEWED_TS is null. | |
| LAST_VIEWED_TS | bigint | The date and time (epoch) the page was last viewed.This column should be used if not null. | |
| SPACE_KEY | String | The Space the Page/BlogPost belongs | |
| TOTAL_VIEWS | bigint | The total number of views for the Page/BlogPost since the tracker was installed. |
User Views
The USER_VIEWS table contains all the statistics for user page views. This table contains 1 row for each user’s views on a page for each day viewed. This data is very expensive to keep, therefore it is limited to the Data Retention Period, which is 6 months by default. All data stored for views older than the Retention Period will be deleted.
It contains the following columns
| Name | Key | Type | Description |
|---|---|---|---|
| ID | Yes | integer | A unique row ID |
| CONTENT_ID | bigint | The Page or BlogPost content ID | |
| DATE_KEY | String | The day that the entry/row was created | |
| USER_KEY | String | The user who viewed the content on the day | |
| SPACE_KEY | String | The Space the Page/BlogPost belongs | |
| LAST_VIEWED | String | The date and time (formatted string) the Page/BlogPost was last viewed by the user.This column is obsolete, unless LAST_VIEWED_TS is null. | |
| LAST_VIEWED_TS | bigint | The date and time (epoch) the Page/BlogPost was last viewed by the user.This column should be used if not null. | |
| USER_COUNT | bigint | The number of times the user viewed the Page/BlogPost on the day |
Attachment Downloads
The ATTACHMENT_DL table contains separate rows for each individual download. This data is very expensive to keep, therefore it is limited to the Data Retention Period, which is 6 months by default. All data stored for views older than the Retention Period will be deleted.
It contains the following columns
| Name | Key | Type | Description |
|---|---|---|---|
| ID | Yes | integer | A unique row ID |
| ATTACHMENT_ID | bigint | The attachment ID that was downloaded | |
| VERSION | integer | The version of the attachment that was downloaded | |
| DOWNLOAD_TS | bigint | The date and time (epoch) the Attachment was downloaded | |
| USER_KEY | String | The user who downloaded the Attachment |
Example Queries
To find the total views on a page
select TOTAL_VIEWS from public."AO_B0BF90_PVT_PAGE_VIEWS" where CONTENT_ID = "123456";
To see the count of page views per day for a page
select sum(USER_COUNT) as TOTAL from public."AO_B0BF90_PVT_USER_VIEWS" where CONTENT_ID = "123456" AND DATE_KEY = "20201225";
To see all the users who have viewed a page
select USER_KEY from public."AO_B0BF90_PVT_USER_VIEWS" where where CONTENT_ID = "123456";
The USER_KEY is the internal id used by Confluence to uniquely identify a user. In order to find the User Name associated with a UserKey, you must join with the internal User table in the Confluence database.
To see page view history for a specific user on a specific page
select DATE_KEY, USER_COUNT from public."AO_B0BF90_PVT_USER_VIEWS" where where CONTENT_ID = "123456" AND USER_KEY = "ABCD123456";
To see the total number of space views per day for a space
select sum(USER_COUNT) as Total from public."AO_B0BF90_PVT_USER_VIEWS" where SPACE_KEY = "ABC" AND DATE_KEY = "20201225";
KC Integrations 