Active users daily

1-- core.mixpanel_source.active_users_daily
2-- Daily, weekly and monthly active users and stickiness, per day: the DAU/WAU/MAU view
3-- from Mixpanel's Insights and its "Active users" template.
4--
5-- Grain: one row per day (UTC).
6-- Depends on: staging.mixpanel_source.events
7--
8-- A user is active on a day if they sent any event that day. WAU and MAU are rolling
9-- windows ending on the row's day: 7 and 28 days, Mixpanel's defaults. Stickiness is
10-- DAU / MAU.
11--
12-- Built by expanding each user's active days onto the windows they fall in, then
13-- counting distinct users per window, so a user active on several days of a window counts
14-- once. The first 27 days after your earliest synced event have incomplete windows;
15-- `is_full_window` is false on those rows.
16--
17-- `new_users` are users whose first event in the synced history falls on the row's day,
18-- and `returning_users` are the rest of that day's DAU. Before the synced history is
19-- complete, a long-standing user's first synced day counts as new, so read `new_users`
20-- from rows where `is_full_window` is true and treat early rows with care.
21--
22-- Every event counts as activity, including automatic ones like `$mp_web_page_view`.
23-- To count only meaningful actions, filter event_name in the first CTE.
24
25WITH active AS (
26    SELECT DISTINCT
27        DATE(event_at)                      AS date,
28        distinct_id
29    FROM {{staging.mixpanel_source.events}}
30    WHERE distinct_id IS NOT NULL
31),
32
33user_days AS (
34    SELECT
35        date,
36        distinct_id,
37        date = MIN(date) OVER (PARTITION BY distinct_id) AS is_first_day
38    FROM active
39),
40