1 min read

How many users per day by device? - GA4 BigQuery

Learn to query Google Analytics 4 data from BigQuery using SQL.

With Google Analytics 4 (GA4), you will now be able to store your data in Google BigQuery which opens up lots of interesting possibilities with your data. Ask questions of your data with SQL.

Query: How many users per day by device?

user_device as(
count (distinct user_pseudo_id) as user_count,
max(if(device.category in ('mobile'),1,0)) as is_mobile,
max(if(device.category in ('desktop'),1,0)) as is_desktop,
max(if(device.category in ('tablet'),1,0)) as is_tablet
from `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
where _table_suffix between '20210101' and '20210107'
group by event_date, user_pseudo_id
sum(user_count) as users,
sum(is_mobile) as mobile_users,
sum(is_desktop) as desktop_users,
sum(is_tablet) as tablet_users
from user_device
group by event_date
order by event_date asc



  • All queries reference the BigQuery sample GA4 data set. You can try this for free and Google provides a substantial free credit.
  • The dataset contains data from 1 Nov 2020 to 31 Jan 2021.
  • You will need to update the data source if you want to use this query with your own data.
  • You can update the date range to suit your needs. I have chosen 1 Jan 2021 to 7 Jan 2021 for this example.
  • Occasionally the results may not line up perfectly when querying this sample dataset.

Want to learn how to get more from your data? Get in touch with me on LinkedIn.