GaryLake
05/25/2023, 4:31 PMauth.users
to my own account table and joining this to my organisation_users and organisation tables etc – I've got all that and I can attribute what I think is an MAU to an organisation.
Below is a massively simplified version of the query which cuts out all the organisation/user joins and just joins auth.users
to auth.refresh_tokens
select
count(distinct auth.users.email)
from auth.users
inner join auth.refresh_tokens on auth.refresh_tokens.user_id::uuid = auth.users.id
where auth.refresh_tokens.created_at > (current_timestamp - interval '1 months')
You might be asking why I'm joining to refresh_tokens
and not just using auth.users.last_sign_in_at
?
Well I noticed that auth.users.last_sign_in_at
is literally timestamping the last actual sign in and not updating for a returning user like I personally expected (for example, I have a user who was definitely active this month but auth.users.last_sign_in_at
is showing a date in April.
The only thing I could find to test if a user was effectively an MAU this month was to see if they had a refresh token created in the last month... Is this reliable and a good way of doing this?
How does Supabase actually calculate an MAU?
As an aside, is anyone else's MAU reporting in the dashboard under settings > usage showing zero MAUs this month? (I'm meaning to open an ticket)garyaustin
05/25/2023, 4:57 PMhttps://cdn.discordapp.com/attachments/1111330747836543097/1111337215201660999/image.png▾
GaryLake
05/25/2023, 5:09 PMgaryaustin
05/25/2023, 5:10 PMGaryLake
05/25/2023, 5:25 PMstojan
05/25/2023, 5:58 PMrefresh_tokens
to derive metrics. We are very likely to change the table's schema which will break your queries. We don't provide many guarantees on the table's schema at this point, as the Auth product is being very actively worked on and some changes are inevitable.
There's a table called audit_log_entries
which is better suited for your use case, and probably less likely to be affected as it's not a table that derives state.GaryLake
05/25/2023, 6:53 PMven
05/25/2023, 7:08 PMGaryLake
05/26/2023, 8:28 AMsilentworks
05/26/2023, 8:42 AMGaryLake
05/26/2023, 8:42 AMaudit_log_entires.payload.actor_id
is this going to get expensive timewise? This works but it doesn't feel right...
select
count(distinct auth.users.email)
from auth.users
inner join auth.audit_log_entries on (audit_log_entries.payload ->> 'actor_id')::uuid = auth.users.id
where auth.audit_log_entries.created_at > (current_timestamp - interval '1 months')
silentworks
05/26/2023, 8:45 AMEXPLAIN ANALYZE
on the query to see how long it will take.GaryLake
05/26/2023, 8:48 AMsilentworks
05/26/2023, 8:51 AMGaryLake
05/26/2023, 8:54 AMsilentworks
05/26/2023, 9:00 AMGaryLake
05/26/2023, 9:04 AMselect
count(distinct auth.users.email)
from auth.users
inner join auth.audit_log_entries on (audit_log_entries.payload ->> 'actor_id')::uuid = auth.users.id
inner join ... -- do some more joins which let me link users to organisations
where auth.audit_log_entries.created_at > (current_timestamp - interval '1 months') -- active in current rolling month
and organisation.id = p_org_id -- function parameter;
Ideally the number I get back here will be the number of MAUs that I can attribute to a given organisation in my systemhttps://cdn.discordapp.com/attachments/1111330747836543097/1111580852154023946/image.png▾
silentworks
05/26/2023, 9:08 AMlast_sign_in_at
in the auth.users
table?last_sign_in_at
with the updated_at
column could do the trick as I think this column gets updated for returning users too.GaryLake
05/26/2023, 9:12 AMhttps://cdn.discordapp.com/attachments/1111330747836543097/1111582510887997500/image.png▾
silentworks
05/26/2023, 9:12 AMGaryLake
05/26/2023, 9:13 AMsilentworks
05/26/2023, 9:16 AMupdated_at
column.auth.users
updated_at
column for the user you said definitely logged in during that month but the last_sign_in_at
column hasn't changed?GaryLake
05/26/2023, 9:20 AMsilentworks
05/26/2023, 9:20 AMGaryLake
05/26/2023, 9:20 AMsilentworks
05/26/2023, 9:21 AMGaryLake
05/26/2023, 9:22 AMsilentworks
05/26/2023, 9:24 AMaudit_log_entries
just contain a lot more information which could be useful in certain circumstances.last_sign_in_at
or created_at
as you could have a user who has never signed in but their updated_at
was set at the same time as their created_at
.GaryLake
05/26/2023, 9:29 AMstojan
05/27/2023, 4:00 PMauth.sessions
on this, but can't guarantee yet