User signups

1-- core.auth0.user_signups
2-- New users per day by how they signed up, and how many went on to verify their
3-- email and come back. The signup side of Auth0's Users page, as a time series.
4--
5-- Grain: one row per UTC signup day x connection x provider.
6-- Depends on: staging.auth0.users, staging.auth0.user_identities
7--
8-- Built from the users table, not the log, so it covers your whole user base back
9-- to the first user rather than only the log retention window. Each user is placed
10-- on the connection of their primary identity (index 0), the one they were created
11-- with; later account linking does not move them.
12
13SELECT
14    u.created_date                                      AS signup_date,
15    i.connection_name,
16    i.provider,
17    ANY_VALUE(i.is_social)                              AS is_social,
18
19    COUNT(*)                                            AS new_users,
20    COUNTIF(u.is_email_verified)                        AS email_verified_users,
21    -- logins_count includes the login that created the account, so > 1 means the
22    -- user came back at least once.
23    COUNTIF(u.logins_count > 1)                         AS returned_users,
24    COUNTIF(u.last_login_at IS NULL)                    AS never_logged_in_users,
25    COUNTIF(u.is_blocked)                               AS blocked_users,
26
27    ROUND(SAFE_DIVIDE(COUNTIF(u.is_email_verified), COUNT(*)), 4) AS email_verified_rate,
28    ROUND(SAFE_DIVIDE(COUNTIF(u.logins_count > 1), COUNT(*)), 4)  AS returned_rate
29FROM {{staging.auth0.users}} u
30LEFT JOIN {{staging.auth0.user_identities}} i
31  ON  i.user_id = u.user_id
32  AND i.identity_index = 0
33WHERE u.created_date IS NOT NULL
34GROUP BY u.created_date, i.connection_name, i.provider