Viewing and usage activity

1. Viewed Objects/Elements

1.1. Viewed elements per hour

USE CASE:

  • Retrieve a count of elements viewed per hour
SELECT DATE_FORMAT(view_time, "%Y-%m-%d %H:00:00") as "Date", COUNT(distinct element_id) as "Elements Viewed"
FROM dashboard_element_view_log_detail 
WHERE view_time > :measurement_time [:last-measurement_time for Metrics]
GROUP BY 1
1.2. Viewed elements per day

USE CASE:

  • Retrieve a count of elements viewed per day
SELECT DATE(view_time) "Date", COUNT(distinct element_id) "Elements Viewed"
FROM dashboard_element_view_log_detail dash
WHERE  view_time > :measurement_time
GROUP BY 1
1.3. Most viewed elements per month

USE CASE:

  • Retrieve most viewed elements per month (Top 10) 
  • Displays which elements were viewed the most within the last 30 days
  • Recommendation: build a Report
SELECT devld.element_id AS "Element ID", 
de.name AS "Element Name", 
count(distinct devld.user_id) AS "Number of Distinct Portal Users",
count(devld.user_id) AS "Total Number of Views"
FROM dashboard_element_view_log_detail AS devld    
JOIN dashboard_element de ON (devld.element_id = de.element_id)
WHERE DATE(devld.view_time)>(curdate()- interval 1 month)
GROUP BY 1
ORDER BY 4 desc Limit 10
1.4. Average number of viewed Tiles (per period)

USE CASE:

  • Retrieve the average number of Tiles per period
SELECT user_id, ifnull(count(DISTINCT element_id)/DATEDIFF(MAX(view_time),MIN(view_time)), 'N/A') as AVG_number_of_tiles_per_period
FROM dashboard_element_view_log_detail
GROUP BY 1;
1.5. List of last viewed elements

USE CASE:

  • Retrieve the list of last viewed elements
  • This Report will fetch data about the last viewed elements, including User information and viewing time
SELECT u.user_id AS User_id, u.username AS User_name, de.name AS Viewed_element_name, dc.category AS Element_category_name, 
IFNULL(devl.last_view_time, 'Not Available') AS Last_viewed_on  
FROM dashboard_element_view_log AS devl
JOIN user AS u ON(u.user_id=devl.user_id)
JOIN dashboard_element AS de ON(de.element_id=devl.element_id)
JOIN dashboard_category AS dc ON(dc.category_id=de.category_id)
ORDER BY last_view_time DESC
1.6. List of Viewed Elements per Category

USE CASE:

  • Retrieve the list of viewed Elements in a Category (including all Subcategories)
  • WHERE ancestor_category_id=137 (Category ID)
SELECT usr.username as Username, elmt.name,cat.category,dev.view_time AS Accessed_time
FROM dashboard_element_view_log_detail AS dev
JOIN user AS usr ON dev.user_id = usr.user_id
join dashboard_element as elmt on dev.element_id =elmt.element_id
JOIN (
SELECT category_id, category
FROM dashboard_category
WHERE category_id=137
UNION 
SELECT ca.category_id,dc.category
FROM category_ancestors AS ca
JOIN dashboard_category AS dc ON (dc.category_id=ca.category_id)
WHERE ancestor_category_id=137
) AS cat on elmt.category_id=cat.category_id
ORDER BY dev.view_time desc
1.7. Default View type for Metrics

USE CASE:

  • Retrieve the default View type for Metrics
  • This Report will show what type of Metric View was selected (Standart, Stoplight, Target, Projection, etc.)
SELECT de.element_id AS Element_id, de.name AS Element_name, devld.view_time AS Viewed_time, u.username AS User
, SUBSTRING(uco.overlay_state,10,POSITION('"' IN REPLACE(uco.overlay_state,'{"view":"',''))-1) AS View_name
 FROM dashboard_element AS de 
 JOIN dashboard_element_view_log_detail AS devld ON(devld.element_id=de.element_id)
 JOIN user_chart_overlay AS uco ON(de.element_id=uco.element_id)
 JOIN user AS u ON(u.user_id=devld.user_id)
 WHERE uco.overlay_state LIKE '{"view":"%'
 GROUP BY u.user_id
1.8. Dimension and time period changes for Metrics (at last View)

USE CASE:

  • Retrieve a Dimension and time period changes 
  • This Report shows what Dimension and time period were selected by User during the last Metric View
SELECT u.username AS User, u.user_id AS User_ID, de.name AS Metric_Name, de.element_id AS Metric_ID, sv.value_display_name AS Seen_Dimension_Values, uco.last_updated_time AS View_time, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(uco.overlay_state,'"interval_unit":"',-1),'}',1),'","interval_value":',' ') AS Last_Viewed_Time_interval
FROM user_chart_overlay AS uco
JOIN user AS u ON(u.user_id=uco.user_id)
JOIN dashboard_element AS de ON(uco.element_id=de.element_id)
JOIN segment_value AS sv ON(uco.segment_value_id=sv.segment_value_id)
ORDER BY u.username, uco.last_updated_time DESC, sv.value_display_name

2. Used Objects/Elements

2.1. Ten most used Datasets

USE CASE:

  • Retrieve most used Datasets (Top 10)
  • This Report will contain the most used Datasets and the count of elements sourced from them
SELECT de.dataset_id As Dataset_ID, d.name AS Dataset_Name, count(distinct de.element_id) AS Number_of_element
FROM dashboard_element AS de
JOIN dataset AS d ON(de.dataset_id=d.dataset_id)
GROUP BY de.dataset_id
ORDER BY 3 Desc LIMIT 10