Active-user counts sound trivial until you try to compute them from raw events and realize a rolling window is not a GROUP BY. The GA4 export has everything you need for DAU, WAU, and MAU once you handle distinct users over time correctly. Here is how.

What the three metrics mean

All three count distinct active users, differing only in the window. DAU is distinct users in a single day, WAU in a rolling or calendar seven-day window, and MAU in a rolling or calendar 28 to 30-day window. The ratio between them, stickiness, is often more telling than any one number:

stickiness = DAU ÷ MAU

DAU is the easy one

A single day's active users is a straight distinct count grouped by date:

SELECT
  PARSE_DATE('%Y%m%d', event_date)      AS day,
  COUNT(DISTINCT user_pseudo_id)        AS dau
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY day
ORDER BY day

Rolling windows need a date spine

The mistake people make is trying to get a rolling 28-day count with a window function, but COUNT(DISTINCT) is not allowed as an analytic function in BigQuery. The clean approach is to build a distinct list of active user-days, then join each calendar date to the prior 27 days:

WITH user_days AS (
  SELECT DISTINCT
    PARSE_DATE('%Y%m%d', event_date) AS d,
    user_pseudo_id
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260331'
)
SELECT
  cal AS day,
  COUNT(DISTINCT ud.user_pseudo_id) AS mau_28d
FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2026-01-28', DATE '2026-03-31')) AS cal
JOIN user_days ud
  ON ud.d BETWEEN DATE_SUB(cal, INTERVAL 27 DAY) AND cal
GROUP BY day
ORDER BY day

The GENERATE_DATE_ARRAY gives you a row per calendar day, and the join pulls in every user active in the trailing 28-day window, so the distinct count is a true rolling MAU. Swap 27 for 6 to get a rolling WAU the same way.

Choose rolling or calendar deliberately

Rolling windows and calendar windows answer different questions, so decide up front:

  • Rolling windows smooth out weekday and weekend swings and are better for trend lines.
  • Calendar windows (this month, this ISO week) are easier to explain to stakeholders and match finance reporting.
  • Whichever you pick, keep it consistent across DAU, WAU, and MAU, or the stickiness ratio becomes meaningless.

Watch the cost and the identity

Two caveats keep these numbers honest. First, rolling queries scan a wide date range, so filter with _TABLE_SUFFIX to exactly the span you need. Second, user_pseudo_id is device-level, so a person on two devices counts twice; if you have a user_id, count distinct on that for a person-level view instead.

Read stickiness, not just the raw counts

The DAU-to-MAU ratio is the number worth watching, because it says how habitual your product is: a ratio of 0.5 means the average monthly user shows up half the days, while 0.1 means they visit only occasionally. Rising active-user counts with a falling ratio is a warning – you are acquiring users who do not stick. Tracking the ratio over time catches that long before the headline counts do.

Mind what counts as active

Active is not free of definition. By default any event makes a user active for the day, which can inflate counts with bounced or bot traffic. For a stricter measure, require an engaged session or a meaningful event, not just any hit, and apply the same rule across DAU, WAU, and MAU so the windows stay comparable. For very large properties the exact distinct counts get expensive across long windows, and APPROX_COUNT_DISTINCT trades a small accuracy margin for a much cheaper query when you are trending rather than reporting exact figures.

Active-user counts are trivial only until a rolling window meets a GROUP BY, and then the trick is a date spine, not a window function. Compute DAU as a daily distinct count, build rolling WAU and MAU by joining a date array to distinct user-days, and pick rolling or calendar on purpose. Do that and the numbers everyone quotes casually finally rest on logic you can stand behind.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.