Purpose

It can be useful to retrieve content analytics data directly from the database in case you want to inspect or manipulate the information externally.

In this KB, we provide multiple examples of information you may want to extract from the database; such as identifying how many times a page has been viewed recently, the most recent date a page was viewed or which actions have been taken on a page, among others. 

Examples

• To retrieve all Analytics events: 

  • PostgreSQL

  SELECT * FROM "AO_7B47A5_EVENT";

• Filtering by a specific type event, the NAME  column can be used, as the example below: 

  Click here for the list of unique event names

  • page_viewed
  • page_created
  • page_updated
  • page_removed
  • page_trashed
  • page_restored
  • blog_viewed
  • blog_created
  • blog_updated
  • blog_removed
  • blog_trashed
  • blog_restored
  • comment_removed
  • comment_created
  • comment_updated
  • attachment_viewed
  • attachment_created
  • attachment_updated
  • attachment_removed
  • PostgreSQL

  select * from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed';

• Example of filtering page views per user on a space, replace space key and event name accordingly:

  • PostgreSQL

  SELECT count(ev."ID") AS TOTAL_EVENTS_PER_SPACE_PER_USER, um.username, ev."NAME" AS EVENT_NAME, ev."SPACE_KEY"
  FROM "AO_7B47A5_EVENT" ev
  JOIN user_mapping um ON ev."USER_KEY" = um.user_key
  WHERE ev."NAME" = 'page_viewed' and ev."SPACE_KEY" = 'TEST'
  GROUP BY um.username, ev."NAME", ev."SPACE_KEY";

• Getting total page views per space:

  • PostgreSQL

  SELECT COUNT("ID") as TOTAL_VIEWS_PER_SPACE, "SPACE_KEY", "NAME"
  FROM "AO_7B47A5_EVENT" 
  WHERE "NAME" = 'page_viewed' AND "SPACE_KEY" = 'TEST'
  GROUP BY "NAME", "SPACE_KEY";

• Getting total views and last viewed date per page:

  • PostgreSQL

  SELECT count (*) as "TOTAL_VIEWS_PER_PAGE", c.title AS "PAGE_TITLE", s.spacename AS "SPACE_NAME", s.spacekey AS "SPACE_KEY",
  max(to_timestamp(aoe."EVENT_AT" / 1000)::timestamp) AS "LATEST_VIEWEDTIME", c.contentid AS "CONTENT_ID"
  FROM "AO_7B47A5_EVENT" aoe
  JOIN content c ON aoe."CONTENT_ID" = c.contentid
  JOIN spaces s ON c.spaceid = s.spaceid
  JOIN user_mapping um ON aoe."USER_KEY" = um.user_key 
  WHERE aoe."NAME" = 'page_viewed' AND c.prevver is null  AND c.CONTENT_STATUS = 'current'
  GROUP BY aoe."CONTENT_ID", c.spaceid, c.title, s.spacename, c.contentid, s.spacekey
  ORDER BY s.spacename;

• Fetching total page views per space in a given period of time (replace date range accordingly):

  • PostgreSQL

  SELECT count("ID") AS TOTAL_VIEWS_PER_SPACE_PER_PERIOD, "SPACE_KEY", "NAME"
  FROM "AO_7B47A5_EVENT" 
  WHERE "NAME" = 'page_viewed' AND "SPACE_KEY" = 'TEST' AND to_timestamp("EVENT_AT" / 1000)::date > '2022-07-01'
  GROUP BY "NAME", "SPACE_KEY";

• To fetch details about the latest view date, and total view count, for each content in a space:

  • PostgreSQL

  SELECT count(ev."ID") AS Total_page_views,max(to_timestamp(ev."EVENT_AT" / 1000)::timestamp) AS latest_viewedtime, um.username, ev."NAME" as EVENT_NAME, ev."SPACE_KEY", ev."CONTENT_ID" from "AO_7B47A5_EVENT" ev 
  JOIN user_mapping um ON ev."USER_KEY" = um.user_key 
  JOIN content ON content.contentid=ev."CONTENT_ID" 
  WHERE ev."NAME" = 'page_viewed' AND content.prevver IS NULL AND ev."SPACE_KEY" = '<insert_space_key>' 
  GROUP BY um.username, ev."NAME", ev."SPACE_KEY",ev."CONTENT_ID";

Data Retention job History

Confluence Analytics has two data retention strategies to contain its footprint on the database, more details here. To check the execution history of those jobs, we can use the following queries:

1. Data retention period (deletion based on date):

   SELECT * FROM scheduler_run_details WHERE job_id = 'com.addonengine.analytics.DataRetention';

2. Event retention (deletion based on event count):

   SELECT * FROM scheduler_run_details where job_id like 'AnalyticsForConfluence.EventLimiter';