Sample SQL for returning GA4 Event_Starts for a single Audience_Name

Google Cloud Data Analytics
1

Hello,
I wonder if someone has experience in querying BQ from two joine tables, which in theory should be easy - here the basic event(s) tables with the pseudonymous_users.
Reason: I want to pull the daily count of session_start events along for one specific audience_name. That Audience detail is only supplied from the pseudonymous_users table. For some reason my join does not work.

1 Like
2

Hi @Jörg_Blank Join events_* and pseudonymous_users_* on user_pseudo_id, filter event_name = 'session_start' and the specific audience_name, then group by event_date; if it returns no rows, most likely the audience field is nested (requires UNNEST) or the date ranges in both tables do not match.

3

Hello Aleksei,
Thanks for jumping on it! While it did not fix the issue immediately, it was a good start.
Finally I got it running with

SELECT evt.event_date, COUNT(DISTINCT evt.user_pseudo_id) AS sessions

FROM `[project.detail].events_*` AS evt

JOIN `[project.detail]..pseudonymous_users_*` AS users

ON evt.user_pseudo_id = users.pseudo_user_id

WHERE

evt.event_name = ‘session_start’

AND EXISTS(

*SELECT 1*

*FROM UNNEST(users.audiences) AS audience*

*WHERE audience.name = 'Bot Filter Jan 2026'*

)

GROUP BY evt.event_date

ORDER BY evt.event_date

*LIMIT 1000;
*
Note, the users.pseudo_user_id is slightly different then evt.user_pseudo_id

1 Like