Stuifbergen.com

Analytics & Growth Hacking

  • About
  • Contact
  • Professional Services

Find me here

  • Mastodon (social)
  • Mastodon (measure)
  • No more Twitter
You are here: Home / Blog / Using Big Query to calculate DAU / MAU

Using Big Query to calculate DAU / MAU

10 November 2022 by jules 2 Comments

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!

Related posts:

ParisSession modeling in BigQuery SQL (using GA4 data and a trip to Paris) graphTrack, Store and Analyze granular Page Performance data: a practical guide Making sense of Event Parameters in GA4 Make your GA4 life easier: Some powertips!

Filed Under: Blog Tagged With: big query, engagement, ga4, sql

Comments

  1. Douglas says

    19 December 2022 at 18:33

    Hey, thanks for sharing this. I am curious if we really need to keep the “56” in the interval assuming that I want to have a date controller in my data studio dashboard

    Reply
    • jules says

      19 December 2022 at 18:41

      Hi Douglas,

      Well, if you want to go back further in time, the results will never change, so I suggest to save the metrics, and never recalculate again.

      If your dataset is small: fine, go for it. But the query itself is very intensive (the JOIN causes a lot of rows to be used when you have a lot of users).

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Recent Posts

  • How to use a pre-hashed email address in GA4 tags in GTM
  • GA4 intraday exports and cookieless pings
  • July 2024 change in collected_traffic_source in GA4 Big Query
  • srsltid parameter, GA4 and Big Query
  • Smart incremental GA4 tables in Dataform

Need help?

  • Contact me

Search

© Copyright Jules Stuifbergen · Powered by Genesis ·