Viewtracker - Analytics for Confluence
Space shortcuts
Skip to end of metadata
Go to start of metadata

Please note:

We do not offer support for custom configured Space Usage Reports. 

On this page:

Space Usage Report with SQL for Confluence


The Viewtracker plugin can be used together with conventional SQL Plugins like SQL for Confluence instead of only the Reporting Plugin. In order to use a plugin like SQL for Confluence, a datasource has to be declared. This is described in following article Configure a Datasource.

Please note:

SQL for Confluence is developed by Bob Swift. If you have questions revolving around it, please contact the Bob Swift Support.


Enable Custom Space Reports

To enable the Custom Space Reports, you need to activate them in the Administration of the Viewtracker (Activate Custom Space Usage Reports).


Examples

The following Queries might need some adjustments because of different database settings or SQL dialects.



Viewtracker Space Usage Report, SQL version
h2. Most viewed pages

{chart:type=bar|orientation=horizontal|dataOrientation=vertical|width=400|height=300|legend=true|colors=#6699CC,#003366|dataDisplay=after}
{sql:dataSource=example}
select title as "Page", totalViews as "Total Views", totalVisitors as "Total Visitors"
from(

select content_id2, sum(count) as totalViews, count(user_key) as totalVisitors
from (
select * from(
select content_id as content_id2, count(user_key) as count, user_key
from AO_05769A_VISIT_ENTITY
group by content_id, user_key) as viewed, (
select distinct `CONTENT_ID`, `SPACE_KEY`
from AO_92296B_AORECENTLY_VIEWED
)as key_
where `CONTENT_ID`=content_id2
) as withKey
where SPACE_KEY like "@space"
group by content_id2
order by totalViews desc) as all_

left join CONTENT on CONTENT.`CONTENTID`=content_id2
LIMIT 10;



{sql}
{chart}

\\

h2. Most active visitors
{chart:type=bar|orientation=horizontal|dataOrientation=vertical|width=400|height=300|legend=true|colors=#6699CC,#003366|dataDisplay=after}
{sql:dataSource=example}

select user_mapping.username as "Username", totalViews as "Total Views", totalPagesViewed as "Total Pages Viewed"
from(

select user_key as user_, sum(count) as totalViews, count(user_key) as totalPagesViewed
from(
select * from(
select user_key, count(`CONTENT_ID`) as count
from AO_05769A_VISIT_ENTITY
group by user_key, `CONTENT_ID`) as viewed, (
select distinct `USER_KEY` as key2, `SPACE_KEY`
from AO_92296B_AORECENTLY_VIEWED)as key_
where user_key=key2
) as withKey
where SPACE_KEY like "@space"
group by user_key
order by totalViews desc) as all_

left join user_mapping on user_mapping.`user_key`=user_
LIMIT 10;
{sql}
{chart}



Viewtracker Space Usage Report, Reporting version
h2. Most viewed pages

{chart:type=bar|orientation=horizontal|dataOrientation=vertical|width=400|height=300|legend=true|colors=#6699CC,#003366|dataDisplay=after}
{report-table:maxResults=10}
{content-reporter:space=@space}
{number-filter:viewtracker:totalviews|required=true}
{number-sort:viewtracker:totalviews|order=descending}
{content-reporter}

{report-column:title=Page}{report-info:title|link=true}{report-column}
{report-column:title=Total Views}{report-info:viewtracker:totalviews}{report-column}
{report-column:title=Total Visitors}{report-info:viewtracker:totalusers}{report-column}

{report-empty}_No views tracked._{report-empty}
{report-table}
{chart}

\\

h2. Most active visitors
{chart:type=bar|orientation=horizontal|dataOrientation=vertical|width=400|height=300|legend=true|colors=#6699CC,#003366|dataDisplay=after}
{report-table:maxResults=10}
{grouping-reporter:expanded:view > username|as=User}
{grouping-stats:expanded:view > viewcount|as=Viewcount}
{expanding-reporter:viewtracker:all|as=view}
{content-reporter:space=@space}
{text-sort:username}
{expanding-reporter}
{number-sort:grouped:Viewcount > stats:sum|order=descending}
{grouping-reporter}

{report-column:title=User}{report-info:grouped:User|link=true}{report-column}
{report-column:title=Total Views}{report-info:grouped:Viewcount > stats:sum|format=#}{report-column}
{report-column:title=Total Pages Viewed}{report-info:grouped:Viewcount > stats:item count|format=#}{report-column}

{report-empty}_No views tracked._{report-empty}
{report-table}
{chart}

View count per User and Page

Get view count for each page, user and the space name.

SPACE NAMETITLEUsernameCount
TQMProject DocumentsErika18
TestSpaceTest PageMartha9
TQMLetter TemplatesHeidy8
TQMProject DocumentsReto6
TQMProject DocumentsUrs5



View count per User and Page
{sql:dataSource=example}
select Spacename as "Space name", title, user_mapping.username as Username, count as Count
from
	(select space_key as space_, content_id as content_, user_key2 as user_, count
	from `AO_92296B_AORECENTLY_VIEWED`, 
		(select content_id as content_id2, user_key as user_key2, count(user_key) as count
		from AO_05769A_VISIT_ENTITY
		group by content_id,user_key2)as visit_entity 
	where AO_92296B_AORECENTLY_VIEWED.`CONTENT_ID`=visit_entity.content_id2 and AO_92296B_AORECENTLY_VIEWED.`USER_KEY`=visit_entity.user_key2
	group by user_, content_, space_
	order by space_, content_, count desc) as all_

left join user_mapping on user_mapping.`user_key`=user_
left join SPACES on SPACES.`SPACEKEY`=space_
left join CONTENT on CONTENT.`CONTENTID`=content_
/* where space_ like "SPACEKEY"  ## In case the report should only cover one Space*/
order by count desc
{sql}


View count per User

Get the views of a user in one space.

usernamecount
Erika31
Martha19
Heidy15
Reto13
Urs12



View count per User
{sql:dataSource=example}
select Spacename as "Space name", title, user_mapping.username as Username, count as Count
from
	(select space_key as space_, content_id as content_, user_key2 as user_, count
	from `AO_92296B_AORECENTLY_VIEWED`, 
		(select content_id as content_id2, user_key as user_key2, count(user_key) as count
		from AO_05769A_VISIT_ENTITY
		group by content_id,user_key2)as visit_entity 
	where AO_92296B_AORECENTLY_VIEWED.`CONTENT_ID`=visit_entity.content_id2 and AO_92296B_AORECENTLY_VIEWED.`USER_KEY`=visit_entity.user_key2
	group by user_, content_, space_
	order by space_, content_, count desc) as all_
left join user_mapping on user_mapping.`user_key`=user_
left join SPACES on SPACES.`SPACEKEY`=space_
left join CONTENT on CONTENT.`CONTENTID`=content_
where space_ like "TQM"
order by count desc
{sql}


View count per Page

Get a list of the most viewed pages.

SPACE NAMETITLEcount
TQMProject Documents63
TQMLetter Templates28
TestSpaceTest Page12
TQMProject11
TQMAdministration5
TQMContracts5



View count per Page
{sql:dataSource=example}
select Spacename, title, sum(count) as count
from
	(select space_key as space_, content_id as content_, user_key2 as user_, count
	from `AO_92296B_AORECENTLY_VIEWED`, 
		(select content_id as content_id2, user_key as user_key2, count(user_key) as count
		from AO_05769A_VISIT_ENTITY
		group by content_id,user_key2)as visit_entity 
	where AO_92296B_AORECENTLY_VIEWED.`CONTENT_ID`=visit_entity.content_id2 and AO_92296B_AORECENTLY_VIEWED.`USER_KEY`=visit_entity.user_key2
	group by user_, content_, space_
	order by space_, content_, count desc) as all_

left join user_mapping on user_mapping.`user_key`=user_
left join SPACES on SPACES.`SPACEKEY`=space_
left join CONTENT on CONTENT.`CONTENTID`=content_
/* where space_ like "SPACEKEY"  ## In case the report should only cover one Space*/
group by spacename, title
order by count desc
{sql}