Using Big Query to calculate DAU / MAU

Mainly a note to self, since I always forget how to do it :)

If you want to know “stickyness” and loyalty of a website, one of the measures you can use is DAU / MAU.

Or: Daily Active Users divided by Monthly Active Users.

I’ve written befor about this. Or rather: presented a measurecamp session. Then I used the Google Analytics API to do the calculations.

It’s not difficult, but calculating it (or: one of the ways to calculate it) is via a self join.

The Code

Source should be a Google Analytics 4 dataset.

First, I select the user ids per day, and join the days back onto itself, with a window of 28 days.

Then, counting the distinct DAU and MAU numbers.

with users as (
  select distinct
    user_pseudo_id, parse_date('%Y%m%d', event_date) as date
  from `yourdata.analytics_12345.events_*`
  where _table_suffix > FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 56 day))
)

select
  daily.date,
  count(distinct daily.user_pseudo_id) as DAU,
  count(distinct monthly.user_pseudo_id) as MAU
from users daily
left join users monthly
on monthly.date between date_sub(daily.date, interval 28 day) and daily.date
group by date
order by date asc

I’ll leave the calculations to you, but the ingredients are there to make a graph like GA4 shows you:

These are the numbers from my blog. As you can see.. not a very sticky site. Please come back :)

Happy Query’ing, and let me know if you have any questions!