Usage Data Sample Queries

 

Periscope usage data can be used to analyze the SQL queries on a Periscope site, measure runtimes, and audit user activity. Example queries that are ready for use on the usage data dataset can be found below.

Note: These sample queries are written to be compatible with Periscope sites that are set up with a Periscope Redshift warehouse. Sample queries for Periscope sites not set up with the cache can be found here.

Search SQL

The "Search SQL" query can be used to search Periscope items - charts, views, SQL snippets, SQL alerts, and filters - that contain a specified string. In the example below, the query is searching each element for the string "us_states". This can be replaced with any string that may appear in a SQL query such as the name of a database table.

select
 'chart' as item
 , name
from
 periscope_usage_data.charts
where
 sql like '%us_states%'
union all
select
 'view' as item
 , name
from
 periscope_usage_data.sql_views
where
 sql like '%us_states%'
union all
select
 'snippet' as item
 , name
from
 periscope_usage_data.sql_snippets
where
 sql like '%us_states%'
union all
select
 'alert' as item
 , name
from
 periscope_usage_data.sql_alerts
where
 sql like '%us_states%'
union all
select
 'filter' as item
 , name
from
 periscope_usage_data.filters
where
 sql like '%us_states%'

Most Popular Dashboards

The "Most Popular Dashboards" query measures how often and for how long a dashboard has been viewed, as well as the number of refreshes and the total runtime of all charts on the dashboard.

The first CTE, refreshes, finds the number of refreshes per dashboard as well as the total runtime of all charts. The second CTE, views, counts the number of distinct users viewing the dashboard, as well as the total amount of time spent on the dashboard by users. The final query returns the dashboard name, dashboard id, distinct user count, total viewed time in second, and total dashboard runtime in seconds for each dashboard.

with
 refreshes as (
   select
     dashboard_id
    , count(1) as queries
     , sum(runtime_ms) * 1.0 / 1000 as total_runtime
   from
     periscope_usage_data.query_logs
     join periscope_usage_data.charts on
       query_logs.item_id = charts.id
   group by
     1
 )
 , views as (
   select
     dashboard_id
     , sum(seconds) as view_time
     , count(distinct user_id) as view_users
     , listagg(users.first_name || ' ' || users.last_name || ' ')
   from
     periscope_usage_data.time_on_site_logs
     join periscope_usage_data.users on
       time_on_site_logs.user_id = users.id
   where
     focused_tab = true
   group by
     1
 )
select
 dashboards.name
 , dashboards.id
 , views.view_users as users
 , views.view_time as view_time_s
 , refreshes.queries as queries_run
 , refreshes.total_runtime as total_query_time
from
 periscope_usage_data.dashboards
 left join refreshes on
   dashboards.id = refreshes.dashboard_id
 left join views on
   dashboards.id = views.dashboard_id
order by
 view_time_s desc

Top Users

For each user on a Periscope site, the "Top Users" query returns the name, email address, total minutes on site, number of charts created,

with
  view_usage as (
   select
    user_id
    , dashboard_id
    , sum(seconds) as seconds
    , (sum(sum(seconds)) over(partition by user_id)) * 1.0 / 60 as minutes_on_site
    , max(sum(seconds)) over(partition by user_id) as most_used_dash_time
   from
    periscope_usage_data.time_on_site_logs
   where
    focused_tab = true
    and [created_at=7days]
   group by
    1
    , 2
  )
  , query_usage as (
   select
    user_id
    , count(1) as queries_run
    , sum(runtime_ms) * 1.0 / 1000 as query_time_s
   from
    periscope_usage_data.query_logs
   where
    [created_at=7days]
   group by
    1
   )
   , chart_usage as (
   select
    created_by
    , count(1) as charts_created
   from
    periscope_usage_data.charts
   where
    [created_at=7days]
   group by
    1
   )
select
 users.first_name || ' ' || users.last_name
 , users.email_address
 , dashboards.name
 , view_usage.minutes_on_site
 , coalesce(chart_usage.charts_created, 0) as charts_created
 , coalesce(query_usage.queries_run, 0) as queries_run
 , coalesce(query_usage.query_time_s, 0) as query_time_s
 , dashboards.name || '( ' || seconds / 60.0 / minutes_on_site * 100 || '%)' as favorite_dashboard
from
 periscope_usage_data.users
 join view_usage on
   users.id = view_usage.user_id
 left join query_usage on
   users.id = query_usage.user_id
 join periscope_usage_data.dashboards on
   view_usage.dashboard_id = dashboards.id
 left join chart_usage on
   users.id = chart_usage.created_by
where
 view_usage.seconds = view_usage.most_used_dash_time
order by
 minutes_on_site desc

Recently Created Charts

The "Recently Created Charts" query returns the dashboard name, dashboard id, chart title, chart created timestamp, and the name of the last user to edit the chart for every chart created in the last seven days. The date range can be adjusted by modifying the where clause condition applied to the created_at field.

select
 dashboards.name as dashboard_name
 , dashboard_id
 , charts.name as chart_title
 , charts.created_at as created_at
 , users.first_name || ' ' || users.last_name as chart_creator
from
 periscope_usage_data.charts
 join periscope_usage_data.dashboards on
  charts.dashboard_id = dashboards.id
 join periscope_usage_data.users on
  charts.created_by = users.id
where
 charts.deleted_at is null
 and [charts.created_at=7days]
order by
 charts.created_at desc

Query Runtimes

The "Query Runtimes" query measures the average, min, max, and sum of all query runtimes for each chart, as well as the number of times that query has been executed in the last seven days. The date range can be adjusted by modifying the where clause condition applied to the created_at field.

select
 dashboards.name as dashboard_name
 , dashboards.id as dashboard_id
 , charts.name as chart_name
 , charts.id as chart_id
 , avg(query_logs.runtime_ms * 1.0) * 1.0 / 1000 as avg_runtime_s
 , min(query_logs.runtime_ms * 1.0) * 1.0 / 1000 as min_runtime_s
 , max(query_logs.runtime_ms * 1.0) * 1.0 / 1000 as max_runtime_s
 , count(1) as run_count
 , sum(query_logs.runtime_ms) * 1.0 / 1000 as total_runtime_s
from
 periscope_usage_data.dashboards
 join periscope_usage_data.charts on
  dashboards.id = charts.dashboard_id
 join periscope_usage_data.query_logs on
  query_logs.item_id = charts.id
where
 [query_logs.created_at=7days]
 and dashboards.deleted_at is null
 and charts.deleted_at is null
group by
 1
 , 2
 , 3
 , 4
order by
 total_runtime_s desc
 , avg_runtime_s desc

Users Who Can Edit

The "Users Who Can Edit" query returns all users on a Periscope site that have edit access. This can be adjusted to show all users without edit access by adjusting the WHERE clause condition applied to the groups.can_edit field to equal "false".

select
 users.first_name || ' ' || users.last_name
 , listagg(groups.name || ', ') as groups_that_can_edit
from
 periscope_usage_data.users
 join periscope_usage_data.user_group_memberships on
  users.id = user_group_memberships.user_id
 join periscope_usage_data.groups on
  user_group_memberships.group_id = groups.id
where
 groups.deleted_at is null
 and user_group_memberships.deleted_at is null
 and groups.can_edit = true
group by
 1

Our support team is ready to help