Queries used by dashboards

This query returns the complete list of all user accounts with their provisioning status, account types, and authentication methods.

The query only includes users where orgMode is either FALSE or NULL, which means it excludes organization-level accounts, focusing on individual user accounts.

SELECT
    CONCAT(aru.displayName, ' - ', COALESCE(aru.email,aru.agentid)) AS "Username Extended"
  , aru.agentid                                                     AS "AgentId"
  , aru.displayname                                                 AS "User Name"
  , aru.email                                                       AS "Email"
  , CASE
      WHEN aru.allowedroles LIKE '%deactivated%' THEN 'deactivated'
      WHEN aru.is_activated = TRUE THEN 'activated'
      ELSE 'not activated'
    END                                                             AS "User Status"
  , CASE
      WHEN aru.highestaccesslevel = 'WRITE'
        OR aru.highestaccesslevel = 'ADMIN' THEN 'full user'
      ELSE 'read-only user'
    END                                                             AS "Account Type"
  , aru.scimmanaged AS "SCIM Managed"
  , CASE
      WHEN aru.serviceaccountmode = TRUE THEN TRUE
      ELSE FALSE
    END                                                             AS "Service Account"
  , CASE 
      WHEN aru.email LIKE '%@data.world'
        OR aru.allowedroles LIKE '%employee%' THEN TRUE
      ELSE FALSE
    END                                                             AS "Support User"
  , aru.created                                                     AS "Created"
FROM AGENTS_RETENTION aru
WHERE 
    (aru.orgMode != TRUE OR aru.orgMode IS NULL)

This query is source for the following dashboards:

Day-by-day tracking of individual user engagement including sessions, pageviews, and time spent on platform.

Only includes regular customers - excludes users with @data.world in their email AND users with 'employee' in their allowedroles.

SELECT
      daf.date                                                      AS "Date"
    , aru.agentid                                                   AS "AgentId"
    , aru.displayname                                               AS "User Name"
    , aru.email                                                     AS "Email"
    , daf.is_rad                                                    AS "Is RAD"
    , daf.ui_events                                                 AS "UI Events"
    , daf.api_events                                                AS "API Events"
    , daf.pageviews                                                 AS "Pageviews"
    , daf.total_active_days_within_last_month                       AS "Total Active Days within Last Month"
    , daf.last_active_day                                           AS "Last Active Day"
    , ROUND((daf.total_length_of_stay_in_milliseconds / 60000), 2)  AS "Total Length of Stay in Minutes"
    , daf.number_of_sessions                                        AS "Number of Sessions"
    , ROUND((daf.avg_length_of_session_in_milliseconds / 60000), 2) AS " Average Length of Stay in Minutes"
    , daf.avg_number_of_ui_actions_per_session                      AS "Average Number of UI Actions per Session" 
FROM CUSTOMER_DAILY_AGENT_FACTS daf
JOIN AGENTS_RETENTION aru
  ON daf.agentid = aru.agentid
WHERE 
    (aru.email NOT LIKE '%data.world%' AND aru.allowedroles NOT LIKE '%employee%')

This query is source for the following dashboards:

Historical rollup of each user's total engagement time and sessions from first to last active day.

Includes regular customers only (excludes @data.world employees); excludes partner accounts. Only includes users who have been active at least once (last_active_day is not null) and excludes partner accounts.

SELECT DISTINCT
    aru.agentid                                                   AS "AgentId"
  , CASE
      WHEN aru.allowedRoles LIKE '%deactivated%' THEN CONCAT(aru.displayname, ' (user no longer active)')
      ELSE aru.displayname
    END                                                           AS "User Name"
  , aru.email                                                     AS "Email"
  , p.last_active_day                                             AS "Last Active Day"
  , DATEDIFF('day', p.last_active_day, CURRENT_DATE())            AS "Days Since Active"
  , ROUND(SUM(p.total_length_of_stay_in_milliseconds)/3600000,2)  AS "Lifetime Engagement (hours)"
  , SUM(p.number_of_sessions)                                     AS "Lifetime Sessions" 
FROM CUSTOMER_DAILY_AGENT_FACTS p 
JOIN AGENTS_RETENTION aru
	ON p.agentid = aru.agentid
WHERE 
    (aru.email NOT LIKE '%data.world%' AND aru.allowedroles NOT LIKE '%employee%')
AND p.LAST_ACTIVE_DAY IS NOT NULL
AND p.customer NOT LIKE '%partner%'
GROUP BY 
   aru.agentid
  ,"User Name"
  ,aru.email
  ,p.last_active_day

This query is source for the following dashboards:

Granular day-by-day record of user login sessions (raw data for time-series analysis).

Incudes regular customers only (excludes @data.world employees); only includes days where users were actually active (last_active_day is not null) and excludes partner accounts.

SELECT DISTINCT
    aru.agentid           AS "AgentId"
  , CASE
      WHEN aru.allowedRoles LIKE '%deactivated%' THEN CONCAT(aru.displayname, ' (user no longer active)')
      ELSE aru.displayname
    END                   AS "User Name"
  , aru.email             AS "Email"
  , p.date                AS "Engagement date"
  , p.number_of_sessions  AS "Number of Sessions"
FROM CUSTOMER_DAILY_AGENT_FACTS p 
JOIN AGENTS_RETENTION aru
	ON p.agentid = aru.agentid
WHERE 
    (aru.email NOT LIKE '%data.world%' AND aru.allowedroles NOT LIKE '%employee%')
AND p.last_active_day IS NOT NULL 
AND p.customer NOT LIKE '%partner%'

This query is source for the following dashboards:

Aggregated daily statistics at the customer/organization level (not individual users).

Only includes dates where the difference in days between the record date and current date is greater than or equal to 0, which means it includes today or any past dates and excludes any future-dated records.

SELECT
	*
FROM CUSTOMER_DAILY_FACTS
WHERE
    datediff('day', date, current_date()) >= 0

This query is source for the following dashboards:

Detailed tracking of user activity (views, edits, creates, suggestions) across all catalog items including system pages.

Only includes regular customers (excludes @data.world employees AND users with 'employee' roles). Only includes records where resourcename IS NOT NULL (has an actual resource name).

SELECT DISTINCT
    r.date                                                                      AS "Date"
  , aru.agentid                                                                 AS "AgentId"
  , CASE
      WHEN aru.allowedRoles LIKE '%deactivated%' THEN CONCAT(aru.displayname, ' (user no longer active)')
      ELSE aru.displayname
    END                                                                         AS "User Name"
  , CASE
      WHEN aru.allowedroles LIKE '%deactivated%' THEN 'deactivated'
      WHEN aru.is_activated = TRUE THEN 'activated'
      ELSE 'not activated'
    END                                                                         AS "User Status"
  , aru.email                                                                   AS "Email"
  , r.org                                                                       AS "Org"
  , r.resourceid                                                                AS "Resource ID"
  , r.resourcename                                                              AS "Resource Name"
  , REPLACE(REGEXP_SUBSTR(CAST(r.resourcetype AS STRING), '[^/]+$'), '"]', '')  AS "Resource Type"
  , r.views                                                                     AS "Views"
  , r.creates                                                                   AS "Creates"
  , r.edits                                                                     AS "Edits"
  , r.deletes                                                                   AS "Deletes"
  , r.requester                                                                 AS "Requester"
  , r.suggestions_approved                                                      AS "Suggestions Approved"
  , r.suggestions_denied                                                        AS "Suggestions Denied"
  , r.suggestions_submitted                                                     AS "Suggestions Submitted"
FROM DAILY_CATALOG_RESOURCES_PAGES_FACTS r
JOIN AGENTS_RETENTION aru
	ON r.agentid = aru.agentid
WHERE 
    (aru.email NOT LIKE '%data.world%' AND aru.allowedroles NOT LIKE '%employee%')
AND r.RESOURCENAME IS NOT NULL

This query is source for the following dashboards:

Tracks detailed user interactions with actual user-created content like datasets, projects, and queries on a daily basis, excluding catalog navigation and system page. Shows what users are doing with specific resources: how many times they viewed them, how many edits or creates they performed, and their collaboration activity through suggestions

Only includes regular customers (excludes @data.world employees AND users with 'employee' roles); only includes records where resourcename IS NOT NULL; excludes catalog/system resources where resourceid contains '/cat.' (resourceid NOT LIKE '%/cat.%').

SELECT DISTINCT
    r.date                                                                      AS "Date"
  , aru.agentid                                                                 AS "AgentId"
  , CASE
      WHEN aru.allowedRoles LIKE '%deactivated%' THEN CONCAT(aru.displayname, ' (user no longer active)')
      ELSE aru.displayname
    END                                                                         AS "User Name"
  , CASE
      WHEN aru.allowedroles LIKE '%deactivated%' THEN 'deactivated'
      WHEN aru.is_activated = TRUE THEN 'activated'
      ELSE 'not activated'
    END                                                                         AS "User Status"
  , aru.email                                                                   AS "Email"
  , r.org                                                                       AS "Org"
  , r.resourceid                                                                AS "Resource ID"
  , r.resourcename                                                              AS "Resource Name"
  , REPLACE(REGEXP_SUBSTR(CAST(r.resourcetype AS STRING), '[^/]+$'), '"]', '')  AS "Resource Type"
  , r.views                                                                     AS "Views"
  , r.creates                                                                   AS "Creates"
  , r.edits                                                                     AS "Edits"
  , r.deletes                                                                   AS "Deletes"
  , r.requester                                                                 AS "Requester"
  , r.suggestions_approved                                                      AS "Suggestions Approved"
  , r.suggestions_denied                                                        AS "Suggestions Denied"
  , r.suggestions_submitted                                                     AS "Suggestions Submitted"
FROM DAILY_CATALOG_RESOURCES_PAGES_FACTS r
JOIN AGENTS_RETENTION aru
	ON r.agentid = aru.agentid
WHERE 
    (aru.email NOT LIKE '%data.world%' AND aru.allowedroles NOT LIKE '%employee%')
AND r.resourcename IS NOT NULL
AND r.resourceid NOT LIKE '%/cat.%'

This query is source for the following dashboards:

Records of all user searches including search terms, filters used, result counts, and search types

Only includes regular customers (excludes @data.world employees AND users with 'employee' roles). Only includes searches where search_value IS NOT NULL AND search_value is not an empty string (has actual search terms).

SELECT
    sf.date         AS "Date"
  , sf.agentid      AS "Agent Id"
  , aru.email       AS "Email"
  , CASE
      WHEN aru.date_deleted IS NOT NULL THEN CONCAT(aru.displayname, ' (user no longer active)')
      ELSE aru.displayname
    END             AS "User Name"
  , CASE
      WHEN aru.date_deleted IS NOT NULL THEN 'Inactive'
      ELSE 'Active'
    END             AS "User Status"
  , sf.search_type  AS "Search Type"
  , sf.search_scope AS "Search Scope"
  , sf.search_value AS "Search Term/Phrase"
  , CASE 
      WHEN sf.SELECTED_FACETS LIKE '%[]%' THEN 'none'
      WHEN sf.SELECTED_FACETS IS NULL THEN 'none'
      ELSE  LTRIM(RTRIM(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        REPLACE(
                          REPLACE(sf.selected_facets, '[{"facet":"', 'Facets: '), -- Remove the beginning
                        '"},{"facet":"', ' '),  -- Replace object separators with a space
                      '"value":"', ': '), -- Replace the key/value separator with a colon
                    '"}]', ''), -- Remove the ending characters
                  '"', '')  -- Remove any remaining quotes
            ))
    END             AS "Search Facets"
  , sf.SELECTED_FACETS 
  , CASE 
      WHEN sf.num_results IS NULL THEN '0'
      ELSE sf.num_results
    END             AS "Number of Searches"
  , CASE 
      WHEN sf.NUM_RESULTS IS NULL THEN '0'
      ELSE sf.NUM_RESULTS 
    END             AS "Number of Results"
  , sf.search_count AS "Search Count"
FROM DAILY_SEARCH_FACTS sf
JOIN AGENTS_RETENTION aru
  ON sf.agentid = aru.agentid
WHERE 
    (aru.email NOT LIKE '%data.world%' AND aru.allowedroles NOT LIKE '%employee%')
AND sf.search_value IS NOT null
AND sf.search_value NOT LIKE ''

This query is source for the following dashboards:

Audit trail of AI agent interactions including token spend, run duration, and agent types (Archie, Data Analyst, etc.).

Only includes events where spend = TRUE (tokens were actually consumed); excludes users with @data.world in their email OR includes records where email IS NULL.

SELECT 
    aktc.org  
  , aktc.dataset 
  , aktc.agent_name 
  , aktc.agentid 
  , CASE
      WHEN aru.date_deleted IS NOT NULL THEN CONCAT(aru.displayname, ' (user no longer active)')
      ELSE aru.displayname
    END AS "User Name"
  , CASE
      WHEN aru.date_deleted IS NOT NULL THEN 'Inactive'
      ELSE 'Active'
    END AS "User Status"
  , aktc.context 
  , aktc.bulk_operation
  , aktc.run_date
  , aktc.start_time
  , aktc.duration
  , aktc.relationships 
  , aktc.attributes 
  , aktc.concepts 
  , aktc.exception 
  , aktc.question
  , aktc.spend 
  , aktc.kt_bulk_data_analyst_agent
  , aktc.kt_standard_data_analyst_agent
  , aktc.kt_catalog_agent 
  , aktc.kt_bulk_archie_enrich 
  , aktc.kt_spend
FROM AICE_KT_CONSUMPTION_EVENTS_AUDIT aktc   
JOIN AGENTS_RETENTION aru
  ON aktc.agentid = aru.agentid
WHERE 
    aktc.spend = TRUE
AND aru.email NOT LIKE '%data.world%' OR aru.email IS NULL

This query is source for the following dashboards: