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.
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!