The Viewtracker plugin may be used together with conventional SQL Plugins like SQL for Confluence. 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.
Add the Viewtracker to the page and blog layout of a space or in the whole confluence. Then add the following code to get an overview over the page views. 

 

 

Examples

Viewtracker can be used together with "SQL for Confluence" instead of the "Reporting Plugin" to enable Space Usage Report

To enable the Space reporting function that is offered by the Viewtracker plugin either Reporting or SQL for Confluence have to be installed. In case you want to use the SQL code you have to adjust the dataSource according to your preferences in the SQL for Confluence add-on. The Query also might need some adjustments because of different database settings.

 

 

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}

Labels (0)

  • No labels

Comments  (0)

Attachments  (2)

Add Attachment
  File Modified
PNG File Screenshot 2016-09-15 17.37.32.png 15. Sep. 2016 by Robin Stohler
PNG File Screenshot 2016-09-15 18.06.19.png 15. Sep. 2016 by Robin Stohler