Assess engagement depth
You can use Lakehouse to assess how users engage with Atlan after they sign in.
This page provides queries for engagement depth in the USAGE_ANALYTICS schema, including pageviews per user, actions per session, session duration, tiers, and power users.
Query parameters
These queries use the tables in the USAGE_ANALYTICS schema. For table definitions, see USAGE_ANALYTICS namespace.
Replace the placeholders in each query with values for your environment.
{{DATABASE}}: Snowflake database name{{SCHEMA}}: UseUSAGE_ANALYTICS{{DOMAIN}}: Customer domain, for example'acme.atlan.com'{{START_DATE}}: Start date, for example'2025-01-01'
These queries derive sessions using a 30-minute inactivity threshold.
Average pageviews per user
Returns the average number of page views per active user per day. Higher values indicate deeper exploration of catalog assets on a given day.
- Snowflake
-- avg_pageviews_per_user_daily.sql
-- Average page views per active user per day.
-- Adapted from Heap T6. Uses domain from PAGES directly.
--
-- Parameters:
-- {{START_DATE}} - e.g., '2025-01-01'
-- {{DOMAIN}} - e.g., 'acme.atlan.com'
WITH daily_pageviews AS (
SELECT
p.user_id,
DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP)) AS event_date,
COUNT(*) AS pageview_count
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
WHERE p.TIMESTAMP >= {{START_DATE}}
AND p.domain = {{DOMAIN}}
GROUP BY p.user_id, event_date
)
SELECT
event_date,
COUNT(DISTINCT user_id) AS active_users,
SUM(pageview_count) AS total_pageviews,
ROUND(SUM(pageview_count)::FLOAT / NULLIF(COUNT(DISTINCT user_id), 0), 2) AS avg_pageviews_per_user
FROM daily_pageviews
GROUP BY event_date
ORDER BY event_date DESC;
Sample output
| EVENT_DATE | ACTIVE_USERS | TOTAL_PAGEVIEWS | AVG_PAGEVIEWS_PER_USER |
|---|---|---|---|
| 2025-03-15 | 47 | 892 | 18.98 |
| 2025-03-14 | 52 | 1140 | 21.92 |
| 2025-03-13 | 38 | 614 | 16.16 |
Actions per session
Returns the average, median, and 90th-percentile number of actions (combined page views and tracked events) per derived session, grouped by month.
- Snowflake
-- actions_per_session.sql
-- Average events/pages per session per domain per month.
-- Indicates depth of each visit. Uses time-gap derived sessions (30-min inactivity threshold).
--
-- Parameters:
-- {{START_DATE}} - e.g., '2025-01-01'
-- {{DOMAIN}} - e.g., 'acme.atlan.com'
WITH user_domains AS (
SELECT user_id, MAX(domain) AS domain
FROM {{DATABASE}}.{{SCHEMA}}.PAGES
WHERE domain IS NOT NULL
GROUP BY user_id
),
-- Raw events with timestamps per user
raw_events AS (
SELECT
user_id,
TIMESTAMP,
CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', TIMESTAMP) AS event_ts,
LAG(TIMESTAMP) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS prev_ts
FROM (
SELECT p.user_id, p.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
INNER JOIN user_domains ud ON ud.user_id = p.user_id
WHERE p.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
UNION ALL
SELECT t.user_id, t.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
INNER JOIN user_domains ud ON ud.user_id = t.user_id
WHERE t.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
AND t.event_text NOT IN (
'atlan_analaytics_aggregateinfo_fetch',
'api_error_emit', 'api_evaluator_cancelled', 'api_evaluator_succeeded',
'Experiment Started', '$experiment_started',
'web_vital_metric_inp_track', 'web_vital_metric_ttfb_track',
'performance_metric_user_timing_discovery_search',
'performance_metric_user_timing_app_bootstrap',
'web_vital_metric_fcp_track', 'web_vital_metric_lcp_track'
)
AND t.event_text NOT LIKE 'workflow_%'
) AS combined
),
-- Mark session boundaries (new session when gap > 30 min or first event)
session_boundaries AS (
SELECT
user_id,
TIMESTAMP,
event_ts,
CASE
WHEN prev_ts IS NULL THEN 1
WHEN DATEDIFF('second', prev_ts, TIMESTAMP) > 1800 THEN 1
ELSE 0
END AS is_new_session
FROM raw_events
),
-- Assign session IDs using cumulative sum of boundaries
session_numbered AS (
SELECT
user_id,
TIMESTAMP,
event_ts,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS session_id
FROM session_boundaries
),
-- Aggregate to session level with month
session_stats AS (
SELECT
sn.user_id,
DATE_TRUNC('MONTH', MIN(sn.event_ts)) AS event_month,
sn.session_id,
COUNT(*) AS actions_in_session
FROM session_numbered sn
GROUP BY sn.user_id, sn.session_id
)
SELECT
{{DOMAIN}} AS domain,
event_month,
COUNT(*) AS total_sessions,
ROUND(AVG(actions_in_session), 1) AS avg_actions_per_session,
ROUND(MEDIAN(actions_in_session), 1) AS median_actions_per_session,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY actions_in_session) AS p90_actions
FROM session_stats
GROUP BY event_month
ORDER BY event_month DESC;
Sample output
| DOMAIN | EVENT_MONTH | TOTAL_SESSIONS | AVG_ACTIONS_PER_SESSION | MEDIAN_ACTIONS_PER_SESSION | P90_ACTIONS |
|---|---|---|---|---|---|
| acme.atlan.com | 2025-03-01 | 1240 | 24.3 | 14.0 | 61.0 |
| acme.atlan.com | 2025-02-01 | 1180 | 22.1 | 13.0 | 57.0 |
| acme.atlan.com | 2025-01-01 | 1050 | 19.8 | 11.0 | 51.0 |
Session duration (monthly)
Returns monthly session length statistics including average and median duration in minutes, average events per session, and maximum session length. Single-event sessions and sessions longer than 8 hours are excluded as outliers.
- Snowflake
-- session_duration.sql
-- Session length analysis per domain using time-gap derived sessions (30-min inactivity threshold).
-- Shows avg/median session duration, event count per session.
--
-- Parameters:
-- {{START_DATE}} - e.g., '2025-01-01'
-- {{DOMAIN}} - e.g., 'acme.atlan.com'
WITH user_domains AS (
SELECT user_id, MAX(domain) AS domain
FROM {{DATABASE}}.{{SCHEMA}}.PAGES
WHERE domain IS NOT NULL
GROUP BY user_id
),
-- Raw events with timestamps per user
raw_events AS (
SELECT
user_id,
TIMESTAMP,
CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', TIMESTAMP) AS event_ts,
LAG(TIMESTAMP) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS prev_ts
FROM (
SELECT p.user_id, p.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
INNER JOIN user_domains ud ON ud.user_id = p.user_id
WHERE p.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
UNION ALL
SELECT t.user_id, t.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
INNER JOIN user_domains ud ON ud.user_id = t.user_id
WHERE t.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
AND t.event_text NOT IN (
'atlan_analaytics_aggregateinfo_fetch',
'api_error_emit', 'api_evaluator_cancelled', 'api_evaluator_succeeded',
'Experiment Started', '$experiment_started',
'web_vital_metric_inp_track', 'web_vital_metric_ttfb_track',
'performance_metric_user_timing_discovery_search',
'performance_metric_user_timing_app_bootstrap',
'web_vital_metric_fcp_track', 'web_vital_metric_lcp_track'
)
AND t.event_text NOT LIKE 'workflow_%'
) AS combined
),
-- Mark session boundaries (new session when gap > 30 min or first event)
session_boundaries AS (
SELECT
user_id,
TIMESTAMP,
event_ts,
CASE
WHEN prev_ts IS NULL THEN 1
WHEN DATEDIFF('second', prev_ts, TIMESTAMP) > 1800 THEN 1
ELSE 0
END AS is_new_session
FROM raw_events
),
-- Assign session IDs using cumulative sum of boundaries
session_numbered AS (
SELECT
user_id,
TIMESTAMP,
event_ts,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS session_id
FROM session_boundaries
),
-- Aggregate to session level
sessions AS (
SELECT
sn.user_id,
ud.domain,
sn.session_id,
DATE_TRUNC('MONTH', MIN(sn.event_ts)) AS session_month,
MIN(sn.event_ts) AS session_start,
MAX(sn.event_ts) AS session_end,
COUNT(*) AS events_in_session,
DATEDIFF('minute', MIN(sn.TIMESTAMP), MAX(sn.TIMESTAMP)) AS duration_minutes
FROM session_numbered sn
INNER JOIN user_domains ud ON ud.user_id = sn.user_id
GROUP BY sn.user_id, ud.domain, sn.session_id
HAVING COUNT(*) > 1 -- Exclude single-event sessions
)
SELECT
domain,
session_month,
COUNT(*) AS total_sessions,
COUNT(DISTINCT user_id) AS unique_users,
ROUND(AVG(duration_minutes), 1) AS avg_session_minutes,
ROUND(MEDIAN(duration_minutes), 1) AS median_session_minutes,
ROUND(AVG(events_in_session), 1) AS avg_events_per_session,
MAX(duration_minutes) AS max_session_minutes
FROM sessions
WHERE duration_minutes > 0 AND duration_minutes < 480 -- Exclude >8hr outliers
GROUP BY domain, session_month
ORDER BY session_month DESC;
Sample output
| DOMAIN | SESSION_MONTH | TOTAL_SESSIONS | UNIQUE_USERS | AVG_SESSION_MINUTES | MEDIAN_SESSION_MINUTES | AVG_EVENTS_PER_SESSION | MAX_SESSION_MINUTES |
|---|---|---|---|---|---|---|---|
| acme.atlan.com | 2025-03-01 | 984 | 89 | 18.4 | 9.2 | 22.1 | 247 |
| acme.atlan.com | 2025-02-01 | 921 | 83 | 17.1 | 8.8 | 20.4 | 312 |
| acme.atlan.com | 2025-01-01 | 847 | 76 | 15.8 | 8.1 | 18.9 | 198 |
Session duration (daily)
Returns daily session duration statistics in seconds. Useful for tracking how session length changes day to day, identifying spikes or drops in engagement depth.
- Snowflake
-- session_duration_daily.sql
-- Daily session duration analysis (avg/median in seconds).
-- Adapted from Heap T7. Uses time-gap derived sessions (30-min inactivity threshold).
--
-- Parameters:
-- {{START_DATE}} - e.g., '2025-01-01'
-- {{DOMAIN}} - e.g., 'acme.atlan.com'
WITH user_domains AS (
SELECT user_id, MAX(domain) AS domain
FROM {{DATABASE}}.{{SCHEMA}}.PAGES
WHERE domain IS NOT NULL
GROUP BY user_id
),
-- Raw events with timestamps per user
raw_events AS (
SELECT
user_id,
TIMESTAMP,
CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', TIMESTAMP) AS event_ts,
LAG(TIMESTAMP) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS prev_ts
FROM (
SELECT t.user_id, t.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
INNER JOIN user_domains ud ON ud.user_id = t.user_id
WHERE ud.domain = {{DOMAIN}}
AND t.event_text NOT IN (
'atlan_analaytics_aggregateinfo_fetch',
'api_error_emit', 'api_evaluator_cancelled', 'api_evaluator_succeeded',
'Experiment Started', '$experiment_started',
'web_vital_metric_inp_track', 'web_vital_metric_ttfb_track',
'performance_metric_user_timing_discovery_search',
'performance_metric_user_timing_app_bootstrap',
'web_vital_metric_fcp_track', 'web_vital_metric_lcp_track'
)
AND t.event_text NOT LIKE 'workflow_%'
AND t.TIMESTAMP >= {{START_DATE}}
UNION ALL
SELECT p.user_id, p.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
INNER JOIN user_domains ud ON ud.user_id = p.user_id
WHERE ud.domain = {{DOMAIN}}
AND p.TIMESTAMP >= {{START_DATE}}
) AS combined
),
-- Mark session boundaries (new session when gap > 30 min or first event)
session_boundaries AS (
SELECT
user_id,
TIMESTAMP,
event_ts,
CASE
WHEN prev_ts IS NULL THEN 1
WHEN DATEDIFF('second', prev_ts, TIMESTAMP) > 1800 THEN 1
ELSE 0
END AS is_new_session
FROM raw_events
),
-- Assign session IDs using cumulative sum of boundaries
session_numbered AS (
SELECT
user_id,
TIMESTAMP,
event_ts,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS session_id
FROM session_boundaries
),
-- Aggregate to session level
derived_sessions AS (
SELECT
user_id,
session_id,
DATE(MIN(event_ts)) AS session_date,
COUNT(*) AS event_count,
DATEDIFF('second', MIN(TIMESTAMP), MAX(TIMESTAMP)) AS duration_seconds
FROM session_numbered
GROUP BY user_id, session_id
HAVING COUNT(*) > 1 -- Exclude single-event sessions
)
SELECT
ds.session_date,
COUNT(*) AS total_sessions,
COUNT(DISTINCT ds.user_id) AS unique_users,
ROUND(AVG(ds.duration_seconds), 1) AS avg_duration_seconds,
ROUND(MEDIAN(ds.duration_seconds), 1) AS median_duration_seconds,
ROUND(AVG(ds.event_count), 1) AS avg_events_per_session,
MAX(ds.duration_seconds) AS max_duration_seconds
FROM derived_sessions ds
WHERE ds.duration_seconds > 0
AND ds.duration_seconds < 28800 -- Exclude >8hr outliers
GROUP BY ds.session_date
ORDER BY ds.session_date DESC;
Sample output
| SESSION_DATE | TOTAL_SESSIONS | UNIQUE_USERS | AVG_DURATION_SECONDS | MEDIAN_DURATION_SECONDS | AVG_EVENTS_PER_SESSION | MAX_DURATION_SECONDS |
|---|---|---|---|---|---|---|
| 2025-03-15 | 84 | 47 | 1124.3 | 541.0 | 21.4 | 14280 |
| 2025-03-14 | 97 | 52 | 1287.6 | 612.0 | 23.8 | 18720 |
| 2025-03-13 | 71 | 38 | 984.1 | 488.0 | 18.2 | 11340 |
Daily engagement matrix
Distributes users across event-count buckets (0, 1–4, 5–9, 10–19, 20+ events) per day. Shows the full audience alongside the active audience (users with a derived session) to distinguish genuine engagement from passive visits.
- Snowflake
-- daily_engagement_matrix.sql
-- Daily event counts per user, bucketed into engagement tiers.
-- Adapted from Heap T1 (all users) and T2 (active audience only).
-- Returns day-level distribution of users across event-count buckets.
--
-- Parameters:
-- {{START_DATE}} - e.g., '2025-01-01'
-- {{DOMAIN}} - e.g., 'acme.atlan.com'
--
-- Output columns: event_date, bucket, user_count, user_count_active_audience
-- Buckets: '0 events', '1-4 events', '5-9 events', '10-19 events', '20+ events'
WITH user_domains AS (
SELECT user_id, MAX(domain) AS domain
FROM {{DATABASE}}.{{SCHEMA}}.PAGES
WHERE domain IS NOT NULL
GROUP BY user_id
),
-- All activity events (tracks + pages)
activity AS (
SELECT
t.user_id,
DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', t.TIMESTAMP)) AS event_date
FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
INNER JOIN user_domains ud ON ud.user_id = t.user_id
WHERE t.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
AND t.event_text NOT IN (
'atlan_analaytics_aggregateinfo_fetch',
'api_error_emit', 'api_evaluator_cancelled', 'api_evaluator_succeeded',
'Experiment Started', '$experiment_started',
'web_vital_metric_inp_track', 'web_vital_metric_ttfb_track',
'performance_metric_user_timing_discovery_search',
'performance_metric_user_timing_app_bootstrap',
'web_vital_metric_fcp_track', 'web_vital_metric_lcp_track'
)
AND t.event_text NOT LIKE 'workflow_%'
UNION ALL
SELECT
p.user_id,
DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP)) AS event_date
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
INNER JOIN user_domains ud ON ud.user_id = p.user_id
WHERE p.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
),
-- Derive sessions using time-gap logic for active audience detection
raw_events AS (
SELECT
user_id,
TIMESTAMP,
DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', TIMESTAMP)) AS event_date,
LAG(TIMESTAMP) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS prev_ts
FROM (
SELECT p.user_id, p.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
INNER JOIN user_domains ud ON ud.user_id = p.user_id
WHERE p.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
UNION ALL
SELECT t.user_id, t.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
INNER JOIN user_domains ud ON ud.user_id = t.user_id
WHERE t.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
AND t.event_text NOT IN (
'atlan_analaytics_aggregateinfo_fetch',
'api_error_emit', 'api_evaluator_cancelled', 'api_evaluator_succeeded',
'Experiment Started', '$experiment_started',
'web_vital_metric_inp_track', 'web_vital_metric_ttfb_track',
'performance_metric_user_timing_discovery_search',
'performance_metric_user_timing_app_bootstrap',
'web_vital_metric_fcp_track', 'web_vital_metric_lcp_track'
)
AND t.event_text NOT LIKE 'workflow_%'
) AS combined
),
session_boundaries AS (
SELECT
user_id,
TIMESTAMP,
event_date,
CASE
WHEN prev_ts IS NULL THEN 1
WHEN DATEDIFF('second', prev_ts, TIMESTAMP) > 1800 THEN 1
ELSE 0
END AS is_new_session
FROM raw_events
),
session_numbered AS (
SELECT
user_id,
event_date,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY TIMESTAMP) AS session_id
FROM session_boundaries
),
-- Users with a derived session on each day (active audience)
session_users AS (
SELECT DISTINCT user_id, event_date AS session_date
FROM session_numbered
),
-- Event counts per user per day
user_day_counts AS (
SELECT
a.user_id,
a.event_date,
COUNT(*) AS event_count,
CASE WHEN su.user_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_session
FROM activity a
LEFT JOIN session_users su ON su.user_id = a.user_id AND su.session_date = a.event_date
GROUP BY a.user_id, a.event_date, (su.user_id IS NOT NULL)
),
-- Assign buckets
bucketed AS (
SELECT
event_date,
user_id,
event_count,
has_session,
CASE
WHEN event_count = 0 THEN '0 events'
WHEN event_count BETWEEN 1 AND 4 THEN '1-4 events'
WHEN event_count BETWEEN 5 AND 9 THEN '5-9 events'
WHEN event_count BETWEEN 10 AND 19 THEN '10-19 events'
ELSE '20+ events'
END AS bucket
FROM user_day_counts
)
SELECT
event_date,
bucket,
COUNT(DISTINCT user_id) AS user_count,
COUNT(DISTINCT CASE WHEN has_session THEN user_id END) AS user_count_active_audience
FROM bucketed
GROUP BY event_date, bucket
ORDER BY event_date DESC, bucket;
Sample output
| EVENT_DATE | BUCKET | USER_COUNT | USER_COUNT_ACTIVE_AUDIENCE |
|---|---|---|---|
| 2025-03-15 | 1-4 events | 18 | 12 |
| 2025-03-15 | 5-9 events | 14 | 14 |
| 2025-03-15 | 10-19 events | 9 | 9 |
| 2025-03-15 | 20+ events | 6 | 6 |
Engagement tiers
Classifies all domain users into Power, Heavy, Light, or Dormant engagement tiers per month based on their activity volume relative to peers. Power users are the top 10% by event count. Heavy users are greater than the monthly median. Light users are less than the median.
- Snowflake
-- engagement_tiers.sql
-- Classify users into Power/Heavy/Light/Dormant tiers per month.
-- Power = top 10% by activity, Heavy = above median, Light = below median, Dormant = zero.
--
-- Parameters:
-- {{START_DATE}} - e.g., '2025-01-01'
-- {{DOMAIN}} - e.g., 'acme.atlan.com'
WITH user_domains AS (
SELECT user_id, MAX(domain) AS domain
FROM {{DATABASE}}.{{SCHEMA}}.PAGES
WHERE domain IS NOT NULL
GROUP BY user_id
),
-- All domain users (for generating dormant entries)
domain_users AS (
SELECT user_id
FROM user_domains
WHERE domain = {{DOMAIN}}
),
activity_events AS (
SELECT user_id, DATE_TRUNC('MONTH', CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', TIMESTAMP)) AS event_month
FROM (
SELECT t.user_id, t.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
INNER JOIN user_domains ud ON ud.user_id = t.user_id
WHERE ud.domain = {{DOMAIN}}
AND t.event_text NOT IN (
'atlan_analaytics_aggregateinfo_fetch',
'api_error_emit', 'api_evaluator_cancelled', 'api_evaluator_succeeded',
'Experiment Started', '$experiment_started',
'web_vital_metric_inp_track', 'web_vital_metric_ttfb_track',
'performance_metric_user_timing_discovery_search',
'performance_metric_user_timing_app_bootstrap',
'web_vital_metric_fcp_track', 'web_vital_metric_lcp_track'
)
AND t.event_text NOT LIKE 'workflow_%'
UNION ALL
SELECT p.user_id, p.TIMESTAMP
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
INNER JOIN user_domains ud ON ud.user_id = p.user_id
WHERE ud.domain = {{DOMAIN}}
)
WHERE TIMESTAMP >= {{START_DATE}}
),
-- All months in range
months AS (
SELECT DISTINCT event_month FROM activity_events
),
-- All domain users x all months
user_months AS (
SELECT du.user_id, m.event_month
FROM domain_users du
CROSS JOIN months m
),
-- Activity counts per user per month
user_activity AS (
SELECT
act.user_id,
act.event_month,
COUNT(*) AS event_count
FROM activity_events act
GROUP BY act.user_id, act.event_month
),
-- Merge and compute percentiles
user_tiered AS (
SELECT
um.event_month,
um.user_id,
COALESCE(ua.event_count, 0) AS event_count,
CASE
WHEN COALESCE(ua.event_count, 0) = 0 THEN 'Dormant'
WHEN PERCENT_RANK() OVER (PARTITION BY um.event_month ORDER BY COALESCE(ua.event_count, 0)) >= 0.9 THEN 'Power'
WHEN COALESCE(ua.event_count, 0) >= MEDIAN(CASE WHEN ua.event_count > 0 THEN ua.event_count END)
OVER (PARTITION BY um.event_month) THEN 'Heavy'
ELSE 'Light'
END AS tier
FROM user_months um
LEFT JOIN user_activity ua ON ua.user_id = um.user_id AND ua.event_month = um.event_month
)
SELECT
event_month,
tier,
COUNT(*) AS user_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY event_month), 1) AS pct_of_users
FROM user_tiered
GROUP BY event_month, tier
ORDER BY event_month DESC, CASE tier WHEN 'Power' THEN 1 WHEN 'Heavy' THEN 2 WHEN 'Light' THEN 3 ELSE 4 END;
Sample output
| EVENT_MONTH | TIER | USER_COUNT | PCT_OF_USERS |
|---|---|---|---|
| 2025-03-01 | Power | 14 | 10.0 |
| 2025-03-01 | Heavy | 48 | 34.3 |
| 2025-03-01 | Light | 41 | 29.3 |
| 2025-03-01 | Dormant | 37 | 26.4 |
Power users
Returns the top 25 users ranked by a composite power score that weights active days (40%), feature breadth (30%), and event volume on a log scale (30%). Useful for identifying champions and potential advocates.
- Snowflake
-- power_users.sql
-- Top users by composite activity score for a domain and time range.
-- Score = weighted sum of total events, active days, and feature breadth.
--
-- Parameters:
-- {{START_DATE}} - e.g., '2025-01-01'
-- {{DOMAIN}} - e.g., 'acme.atlan.com'
WITH user_domains AS (
SELECT user_id, MAX(domain) AS domain
FROM {{DATABASE}}.{{SCHEMA}}.PAGES
WHERE domain IS NOT NULL
GROUP BY user_id
),
all_activity AS (
SELECT p.user_id, p.TIMESTAMP, ud.domain,
CASE
WHEN p.name = 'discovery' THEN 'Discovery'
WHEN p.name IN ('saved_query', 'insights') THEN 'Insights'
WHEN p.name IN ('glossary', 'term', 'category') THEN 'Governance'
WHEN p.name IN ('asset_profile', 'overview') THEN 'Asset Profile'
WHEN p.name = 'reverse-metadata-sidebar' THEN 'Chrome Extension'
WHEN p.name = 'monitor' THEN 'Data Quality'
ELSE 'Other'
END AS feature_area
FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
INNER JOIN user_domains ud ON ud.user_id = p.user_id
WHERE p.TIMESTAMP >= {{START_DATE}}
AND p.name IS NOT NULL
AND ud.domain = {{DOMAIN}}
UNION ALL
SELECT t.user_id, t.TIMESTAMP, ud.domain,
CASE
WHEN t.event_text LIKE 'discovery_%' THEN 'Discovery'
WHEN t.event_text LIKE 'insights_%' THEN 'Insights'
WHEN t.event_text LIKE 'governance_%' THEN 'Governance'
WHEN t.event_text LIKE 'atlan_ai_%' THEN 'AI Copilot'
WHEN t.event_text LIKE 'lineage_%' THEN 'Lineage'
WHEN t.event_text LIKE 'chrome_%' THEN 'Chrome Extension'
ELSE 'Other'
END AS feature_area
FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
INNER JOIN user_domains ud ON ud.user_id = t.user_id
WHERE t.TIMESTAMP >= {{START_DATE}}
AND ud.domain = {{DOMAIN}}
AND t.event_text NOT IN (
'atlan_analaytics_aggregateinfo_fetch',
'api_error_emit', 'api_evaluator_cancelled', 'api_evaluator_succeeded',
'Experiment Started', '$experiment_started',
'web_vital_metric_inp_track', 'web_vital_metric_ttfb_track',
'performance_metric_user_timing_discovery_search',
'performance_metric_user_timing_app_bootstrap',
'web_vital_metric_fcp_track', 'web_vital_metric_lcp_track'
)
AND t.event_text NOT LIKE 'workflow_%'
),
user_scores AS (
SELECT
a.user_id,
COUNT(*) AS total_events,
COUNT(DISTINCT DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', a.TIMESTAMP))) AS active_days,
COUNT(DISTINCT CASE WHEN a.feature_area != 'Other' THEN a.feature_area END) AS feature_breadth
FROM all_activity a
GROUP BY a.user_id
)
SELECT
us.user_id,
u.email,
u.username,
MAX(u.role) AS role,
MAX(u.job_role) AS job_role,
us.total_events,
us.active_days,
us.feature_breadth,
-- Composite score: 40% active days, 30% feature breadth, 30% event volume (log-scaled)
ROUND(
40.0 * us.active_days / NULLIF(MAX(us.active_days) OVER (), 0)
+ 30.0 * us.feature_breadth / NULLIF(MAX(us.feature_breadth) OVER (), 0)
+ 30.0 * LN(1 + us.total_events) / NULLIF(MAX(LN(1 + us.total_events)) OVER (), 0)
, 1) AS power_score
FROM user_scores us
LEFT JOIN {{DATABASE}}.{{SCHEMA}}.USERS u ON u.id = us.user_id
GROUP BY us.user_id, u.email, u.username, us.total_events, us.active_days, us.feature_breadth
ORDER BY power_score DESC
LIMIT 25;
Sample output
| USER_ID | USERNAME | ROLE | JOB_ROLE | TOTAL_EVENTS | ACTIVE_DAYS | FEATURE_BREADTH | POWER_SCORE | |
|---|---|---|---|---|---|---|---|---|
| usr_001 | [email protected] | alice | admin | Data Engineer | 3420 | 58 | 7 | 94.2 |
| usr_002 | [email protected] | bob | member | Data Analyst | 1850 | 41 | 5 | 72.8 |
| usr_004 | [email protected] | dave | member | Analyst | 1240 | 38 | 6 | 68.1 |