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

Liked this post?

Buy Me a Coffee

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

  • Analyze AB tests in GA4 via Big Query
  • How to make sure your GA4 events tables do not expire
  • Prepare your cloud project for “Bulk data export” Google Search Console
  • Making Sense of the GA4 Configuration Tag
  • Using Big Query to calculate DAU / MAU

Need help?

  • Contact me

Search

© Copyright Jules Stuifbergen · Powered by Genesis ·