runtimemilestone 1/4 Daily active users & engagement
Measure daily activity and user engagement.
Video Product Analytics · Move from watch events to daily engagement, rolling trends, and signup-cohort retention.
approach
DAU counts distinct people with activity on a calendar day, not event rows. Define engagement separately: here it is total seconds divided by active viewers. This result includes observed dates only.
worked-example.sql
SELECT date(watched_at) AS day, COUNT(DISTINCT viewer_id) AS active_viewers FROM watch_events GROUP BY day;
data for this milestone
watch_events5 columns · 26 rows
| idinteger · key | viewer_idinteger | video_idinteger | watched_attext | watch_secondsinteger |
|---|---|---|---|---|
| 1 | 1 | 1 | 2025-01-01 10:00:00 | 300 |
| 2 | 1 | 2 | 2025-01-01 11:00:00 | 450 |
| 3 | 2 | 1 | 2025-01-02 12:00:00 | 600 |
| 4 | 3 | 3 | 2025-01-02 13:00:00 | 120 |
| 5 | 1 | 4 | 2025-01-02 14:00:00 | 700 |
showing 5 of 26 rows