Monthly active users

1-- core.auth0.monthly_active_users
2-- Monthly active users and how many of them were new that month.
3--
4-- Grain: one row per calendar month (UTC).
5-- Depends on: staging.auth0.logs, staging.auth0.users
6--
7-- Active means the user authenticated at least once in the month: an interactive
8-- login, a silent re-authentication, or a token exchange made for them (refresh
9-- token, authorization code, password grant, device code, MFA). Machine-to-machine
10-- tokens have no user and are not counted. This follows the spirit of how Auth0
11-- describes active users for billing, but Auth0's own billing count is computed on
12-- its side and is the one that applies to your invoice; reconcile a closed month
13-- against Tenant Settings > Usage before using this for anything contractual.
14--
15-- A month is only complete when the tenant log covers all of it. The first synced
16-- month usually is not, because Auth0 keeps 1 to 30 days of logs depending on plan.
17-- `is_complete_month` is FALSE for that month and for the current one.
18
19WITH active AS (
20    SELECT
21        DATE_TRUNC(event_date, MONTH)   AS month_start,
22        user_id
23    FROM {{staging.auth0.logs}}
24    WHERE user_id IS NOT NULL
25      AND event_category IN ('login_success', 'silent_auth_success', 'user_token_exchange_success')
26    GROUP BY 1, 2
27),
28
29coverage AS (
30    SELECT MIN(event_date) AS first_log_date
31    FROM {{staging.auth0.logs}}
32)
33
34SELECT
35    a.month_start,
36    COUNT(*)                                                        AS monthly_active_users,
37    -- Users created in the same month they were active.
38    COUNTIF(DATE_TRUNC(u.created_date, MONTH) = a.month_start)      AS new_active_users,
39    COUNTIF(DATE_TRUNC(u.created_date, MONTH) < a.month_start)      AS returning_active_users,
40    -- Active in the log but missing from `users`: usually deleted in Auth0 since,