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.

Before you begin

Before you run these queries, make sure:

• Your Lakehouse setup is complete and Lakehouse is enabled for your organization. If setup isn't complete, see Get started with Lakehouse.

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}}: Use USAGE_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	EMAIL	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