Skip to main content

Track active users

You can use Lakehouse to track daily, weekly, and monthly active users for a customer domain and monitor adoption over time. This page provides example queries for active-user metrics in the USAGE_ANALYTICS schema, including DAU, WAU, MAU, stickiness ratio, and a user roster.

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'

Daily active users​

Returns the count of distinct users who performed any tracked action or page view on each calendar day for the specified domain.

-- dau_by_domain.sql
-- Daily Active Users per customer domain.
-- Uses PAGES.domain for filtering. Counts by user_id.
--
-- Parameters:
--   {{START_DATE}} - e.g., '2025-01-01'
--   {{DOMAIN}}     - e.g., 'acme.atlan.com' (or replace filter line with 1=1 for all)

WITH user_domains AS (
    SELECT user_id, MAX(domain) AS domain
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES
    WHERE domain IS NOT NULL
    GROUP BY user_id
),

activity_events AS (
    SELECT
        t.user_id,
        ud.domain,
        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.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.domain,
        DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP)) AS event_date
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
    WHERE p.TIMESTAMP >= {{START_DATE}}
)

SELECT
    domain,
    event_date,
    COUNT(DISTINCT user_id) AS dau
FROM activity_events
WHERE domain = {{DOMAIN}}
GROUP BY domain, event_date
ORDER BY domain, event_date DESC;
Sample output
DOMAINEVENT_DATEDAU
acme.atlan.com2025-03-1547
acme.atlan.com2025-03-1452
acme.atlan.com2025-03-1338

Weekly active users​

Returns the count of distinct users who performed any tracked action or page view within each calendar week for the specified domain.

-- wau_by_domain.sql
-- Weekly Active Users per customer domain.
-- Uses PAGES.domain for filtering. Counts by user_id.
--
-- Parameters:
--   {{START_DATE}} - e.g., '2025-01-01'
--   {{DOMAIN}}     - e.g., 'acme.atlan.com' (or replace filter line with 1=1 for all)

WITH user_domains AS (
    SELECT user_id, MAX(domain) AS domain
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES
    WHERE domain IS NOT NULL
    GROUP BY user_id
),

activity_events AS (
    SELECT
        t.user_id,
        ud.domain,
        DATE_TRUNC('WEEK', CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', t.TIMESTAMP)) AS event_week
    FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
    INNER JOIN user_domains ud ON ud.user_id = t.user_id
    WHERE 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.domain,
        DATE_TRUNC('WEEK', CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP)) AS event_week
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
    WHERE p.TIMESTAMP >= {{START_DATE}}
)

SELECT
    domain,
    event_week,
    COUNT(DISTINCT user_id) AS wau
FROM activity_events
WHERE domain = {{DOMAIN}}
GROUP BY domain, event_week
ORDER BY domain, event_week DESC;
Sample output
DOMAINEVENT_WEEKWAU
acme.atlan.com2025-03-1089
acme.atlan.com2025-03-0394
acme.atlan.com2025-02-2481

Monthly active users​

Returns the count of distinct users active each calendar month with month-over-month delta and percentage change.

-- mau_by_domain.sql
-- Monthly Active Users per customer domain with month-over-month delta.
-- Uses PAGES.domain for domain filtering. Counts by user_id (not email).
-- LEFT JOINs USERS for optional email enrichment.
--
-- Parameters:
--   {{START_DATE}} - e.g., '2025-01-01'
--   {{DOMAIN}}     - e.g., 'acme.atlan.com' (or replace filter line with 1=1 for all)

WITH user_domains AS (
    SELECT user_id, MAX(domain) AS domain
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES
    WHERE domain IS NOT NULL
    GROUP BY user_id
),

activity_events AS (
    SELECT
        t.user_id,
        ud.domain,
        DATE_TRUNC('MONTH', CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', t.TIMESTAMP)) AS event_month
    FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
    INNER JOIN user_domains ud ON ud.user_id = t.user_id
    WHERE 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.domain,
        DATE_TRUNC('MONTH', CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP)) AS event_month
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
    WHERE p.TIMESTAMP >= {{START_DATE}}
),

mau_counts AS (
    SELECT
        domain,
        event_month,
        COUNT(DISTINCT user_id) AS mau
    FROM activity_events
    WHERE domain = {{DOMAIN}}
    GROUP BY domain, event_month
)

SELECT
    domain,
    event_month,
    mau,
    LAG(mau) OVER (PARTITION BY domain ORDER BY event_month) AS prev_month_mau,
    mau - LAG(mau) OVER (PARTITION BY domain ORDER BY event_month) AS mau_delta,
    ROUND(100.0 * (mau - LAG(mau) OVER (PARTITION BY domain ORDER BY event_month))
        / NULLIF(LAG(mau) OVER (PARTITION BY domain ORDER BY event_month), 0), 1) AS mau_change_pct
FROM mau_counts
ORDER BY domain, event_month DESC;
Sample output
DOMAINEVENT_MONTHMAUPREV_MONTH_MAUMAU_DELTAMAU_CHANGE_PCT
acme.atlan.com2025-03-0112411865.1
acme.atlan.com2025-02-0111811087.3
acme.atlan.com2025-01-01110nullnullnull

Stickiness ratio​

Computes the DAU/MAU stickiness ratio per domain per month. A ratio greater than 0.3 indicates strong daily engagement. A ratio less than 0.1 indicates episodic usage.

-- mau_dau_ratio.sql
-- DAU/MAU stickiness ratio per domain per month.
-- >0.3 = strong daily engagement, <0.1 = episodic usage.
-- Uses PAGES.domain for filtering. Counts by user_id.
--
-- Parameters:
--   {{START_DATE}} - e.g., '2025-01-01'
--   {{DOMAIN}}     - e.g., 'acme.atlan.com' (or replace filter line with 1=1 for all)

WITH user_domains AS (
    SELECT user_id, MAX(domain) AS domain
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES
    WHERE domain IS NOT NULL
    GROUP BY user_id
),

activity_events AS (
    SELECT
        t.user_id,
        ud.domain,
        DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', t.TIMESTAMP)) AS event_date,
        DATE_TRUNC('MONTH', CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', t.TIMESTAMP)) AS event_month
    FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
    INNER JOIN user_domains ud ON ud.user_id = t.user_id
    WHERE 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.domain,
        DATE(CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP)) AS event_date,
        DATE_TRUNC('MONTH', CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP)) AS event_month
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
    WHERE p.TIMESTAMP >= {{START_DATE}}
),

daily_users AS (
    SELECT
        domain,
        event_month,
        event_date,
        COUNT(DISTINCT user_id) AS dau
    FROM activity_events
    WHERE domain = {{DOMAIN}}
    GROUP BY domain, event_month, event_date
),

monthly_users AS (
    SELECT
        domain,
        event_month,
        COUNT(DISTINCT user_id) AS mau
    FROM activity_events
    WHERE domain = {{DOMAIN}}
    GROUP BY domain, event_month
)

SELECT
    m.domain,
    m.event_month,
    m.mau,
    ROUND(AVG(d.dau), 1) AS avg_dau,
    ROUND(AVG(d.dau) / NULLIF(m.mau, 0), 3) AS stickiness_ratio,
    CASE
        WHEN AVG(d.dau) / NULLIF(m.mau, 0) >= 0.3 THEN 'Strong'
        WHEN AVG(d.dau) / NULLIF(m.mau, 0) >= 0.1 THEN 'Moderate'
        ELSE 'Episodic'
    END AS engagement_level
FROM monthly_users m
JOIN daily_users d ON d.domain = m.domain AND d.event_month = m.event_month
GROUP BY m.domain, m.event_month, m.mau
ORDER BY m.domain, m.event_month DESC;
Sample output
DOMAINEVENT_MONTHMAUAVG_DAUSTICKINESS_RATIOENGAGEMENT_LEVEL
acme.atlan.com2025-03-0112441.20.332Strong
acme.atlan.com2025-02-0111818.50.157Moderate
acme.atlan.com2025-01-011108.90.081Episodic

User roster​

Returns the full list of active users for a domain with activity metadata: total events, active days, first and last activity timestamps, days since last activity, and a status label (Active, Inactive, or Churned).

-- user_roster_by_domain.sql
-- Full user list for a domain with activity status, last activity, and event counts.
-- Starts from active user_ids in PAGES, LEFT JOINs USERS for metadata.
-- Shows all active users even if they have no USERS record.
--
-- 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
),

activity_events AS (
    SELECT
        t.user_id,
        CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', t.TIMESTAMP) AS event_ts
    FROM {{DATABASE}}.{{SCHEMA}}.TRACKS t
    INNER JOIN user_domains ud ON ud.user_id = t.user_id AND ud.domain = {{DOMAIN}}
    WHERE 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,
        CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', p.TIMESTAMP) AS event_ts
    FROM {{DATABASE}}.{{SCHEMA}}.PAGES p
    WHERE p.domain = {{DOMAIN}}
      AND p.TIMESTAMP >= {{START_DATE}}
),

user_activity AS (
    SELECT
        user_id,
        COUNT(*) AS total_events,
        COUNT(DISTINCT DATE(event_ts)) AS active_days,
        MIN(event_ts) AS first_activity,
        MAX(event_ts) AS last_activity
    FROM activity_events
    GROUP BY user_id
),

user_meta AS (
    SELECT id, email, username, role,
           MAX(license_type) AS license_type,
           MAX(job_role) AS job_role,
           MIN(created_at) AS user_created_at
    FROM {{DATABASE}}.{{SCHEMA}}.USERS
    WHERE email IS NOT NULL
    GROUP BY id, email, username, role
)

SELECT
    a.user_id,
    um.email,
    um.username,
    um.role,
    um.license_type,
    um.job_role,
    um.user_created_at,
    a.total_events,
    a.active_days,
    a.first_activity,
    a.last_activity,
    DATEDIFF('day', a.last_activity, CURRENT_TIMESTAMP()) AS days_since_last_activity,
    CASE
        WHEN a.last_activity >= DATEADD('day', -30, CURRENT_TIMESTAMP()) THEN 'Active'
        WHEN a.last_activity >= DATEADD('day', -90, CURRENT_TIMESTAMP()) THEN 'Inactive'
        WHEN a.last_activity IS NULL THEN 'Never Active'
        ELSE 'Churned'
    END AS status
FROM user_activity a
LEFT JOIN user_meta um ON um.id = a.user_id
ORDER BY a.total_events DESC;
Sample output
USER_IDEMAILUSERNAMEROLELICENSE_TYPEJOB_ROLEUSER_CREATED_ATTOTAL_EVENTSACTIVE_DAYSFIRST_ACTIVITYLAST_ACTIVITYDAYS_SINCE_LAST_ACTIVITYSTATUS
usr_001[email protected]aliceadminguestData Engineer2024-01-103420582025-01-022025-03-141Active
usr_002[email protected]bobmembermemberData Analyst2024-02-151850412025-01-052025-03-105Active
usr_003[email protected]carolmemberguestBusiness Analyst2024-03-01320122025-01-082024-12-2085Inactive