Skip to main content
Community Hub
TL;DR

Use Lakehouse to measure how deeply users engage with Atlan through pageview rates, session depth, duration, and user tier analysis.

Your AI can read this via Docs MCPcurl -fsSL "https://docs.atlan.com/install-docs-mcp" | bashConnect

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}}: 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.

-- 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_DATEACTIVE_USERSTOTAL_PAGEVIEWSAVG_PAGEVIEWS_PER_USER
2025-03-154789218.98
2025-03-1452114021.92
2025-03-133861416.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.

-- 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
DOMAINEVENT_MONTHTOTAL_SESSIONSAVG_ACTIONS_PER_SESSIONMEDIAN_ACTIONS_PER_SESSIONP90_ACTIONS
acme.atlan.com2025-03-01124024.314.061.0
acme.atlan.com2025-02-01118022.113.057.0
acme.atlan.com2025-01-01105019.811.051.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.

-- 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
DOMAINSESSION_MONTHTOTAL_SESSIONSUNIQUE_USERSAVG_SESSION_MINUTESMEDIAN_SESSION_MINUTESAVG_EVENTS_PER_SESSIONMAX_SESSION_MINUTES
acme.atlan.com2025-03-019848918.49.222.1247
acme.atlan.com2025-02-019218317.18.820.4312
acme.atlan.com2025-01-018477615.88.118.9198

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.

-- 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_DATETOTAL_SESSIONSUNIQUE_USERSAVG_DURATION_SECONDSMEDIAN_DURATION_SECONDSAVG_EVENTS_PER_SESSIONMAX_DURATION_SECONDS
2025-03-1584471124.3541.021.414280
2025-03-1497521287.6612.023.818720
2025-03-137138984.1488.018.211340

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.

-- 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_DATEBUCKETUSER_COUNTUSER_COUNT_ACTIVE_AUDIENCE
2025-03-151-4 events1812
2025-03-155-9 events1414
2025-03-1510-19 events99
2025-03-1520+ events66

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.

-- 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_MONTHTIERUSER_COUNTPCT_OF_USERS
2025-03-01Power1410.0
2025-03-01Heavy4834.3
2025-03-01Light4129.3
2025-03-01Dormant3726.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.

-- 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 'Data Exploration'
            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 'Data Exploration'
            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_IDEMAILUSERNAMEROLEJOB_ROLETOTAL_EVENTSACTIVE_DAYSFEATURE_BREADTHPOWER_SCORE
usr_001alice@acme.comaliceadminData Engineer342058794.2
usr_002bob@acme.combobmemberData Analyst185041572.8
usr_004dave@acme.comdavememberAnalyst124038668.1