MySQL queries for Smart Folders: Usage and Engagement Stats

The queries below are used to retrieve data from the Dashboard database. Therefore Dashboard DB (SQL) must be selected as a Data Source when creating Datasets from Dataset Editors in Metric Insights.

1. Most viewed Elements with the date condition

USE CASE:

  • The provided queries will extract the Elements (Metrics, Change Reports or External Reports) that had the biggest number of views;
  • The conditions used below include the 1 month time frame changed daily (from today's day last month till today) and monthly (from 1st last month till 1st current month). For more Time Intervals available please refer to the MySQL Reference Manual.

1.1. Top 10 most viewed Elements for the latest month interval 

SELECT element_id, segment_value_id AS dim_value, count(*) AS views
FROM dashboard_element_view_log_detail
WHERE date(view_time) >= current_date - INTERVAL 1 MONTH
GROUP BY 1,2 
ORDER BY 3 DESC
LIMIT 10
Click to copy

1.2. Top 10 most viewed Elements in the past month 

SELECT element_id, segment_value_id AS dim_value, count(*) AS views
FROM dashboard_element_view_log_detail
WHERE date(view_time) >= DATE_FORMAT(current_date - INTERVAL 1 MONTH, '%Y/%m/01')
AND date(view_time) < DATE_FORMAT(current_date, '%Y/%m/01')
GROUP BY 1,2 
ORDER BY 3 DESC
LIMIT 10
Click to copy
2. Most viewed Elements by particular User Group

USE CASE:

  • The query will extract Top 10 Elements (Metrics, Change Reports or External Reports) that had the biggest number of views by Users from a particular User Group;
  • Useful to monitor the statistics by various departments.
SELECT element_id, segment_value_id AS dim_value, count(*) AS views 
FROM dashboard_element_view_log_detail AS devd 
JOIN user_group_member AS ugm USING(user_id) 
WHERE ugm.user_group_id IN (
    SELECT group_id 
    FROM user_group
    WHERE name = 'UserGroupName'
)
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 10
Click to copy

UserGroupName in the query above is to be replaced with the desired User Group Name that exists in MI app to filter by. If there are several User Groups you need to include in the filter, change the condition for WHERE name in ('UserGroupName1', 'UserGroupName2', 'UserGroupName3') and define User Group Names in quotes.

3. Elements with Alerts created today or not resolved yet

USE CASE:

  • To keep track of the Metrics with Alerts created today (regardless of the status) and the Metrics with Alerts not resolved yet (regardless of the status);
  • The script will extract all the Metrics that satisfy the conditions, either the user that runs the Dataset Update Data is subscribed to the Alerts or not.
SELECT de.element_id AS element_id, 
svi.segment_value_id AS dim_value
FROM alert_event AS ae 
JOIN dashboard_element AS de USING(element_id)
JOIN segment_value_info AS svi USING(segment_value_id)
JOIN alert_rule AS ar USING(alert_rule_id)
JOIN issue AS i USING(issue_id)
WHERE (ae.alert_log_time >= :measurement_time OR i.is_resolved_ind = 'N')
AND de.type = 'metric'
AND ar.assign_type = 'element'
AND ar.alert_rule_type = 'user'
AND ar.workflow_id > 0
GROUP BY de.element_id, svi.segment_value_id
Click to copy

Attention to the :measurement_time variable - the value is set in the Set "Data For" Date parameter on the Dataset edit page.  

If the Smart Folder has to display Metrics with Alerts created the same day when the Dataset Update Data runs, it's supposed to be set for Today. If set for Yesterday, the comparison sign to be changed for >  otherwise the query will extract the Metrics with Alerts created Yesterday and Today.

4. Elements with not resolved Alerts in defined statuses

USE CASE:

  • To keep track of the Metrics with Alerts in the specific statuses (to be modified directly in the query);
  • The script will extract all the Metrics that satisfy the conditions, either the user that runs the Dataset Update Data is subscribed to the Alerts or not.
SELECT de.element_id AS element_id, 
svi.segment_value_id AS dim_value 
FROM alert_event AS ae 
JOIN dashboard_element AS de USING(element_id)
JOIN segment_value_info AS svi USING(segment_value_id)
JOIN alert_rule AS ar USING(alert_rule_id)
JOIN issue AS i USING(issue_id)
LEFT JOIN workflow_status AS ws ON (ws.workflow_status_id = i.workflow_status_id)
WHERE ws.name in ('In Progress', 'In Review', 'Validation')
AND de.type = 'metric'
AND ar.assign_type = 'element'
AND ar.alert_rule_type = 'user'
AND ar.workflow_id > 0
GROUP BY de.element_id, svi.segment_value_id
Click to copy