How to Filter GA4 Users by Device in BigQuery (Step-by-Step)

Posted on Posted in BigQuery Tagged with
How to Filter GA4 Users by Device in BigQuery (Step-by-Step)

If you’ve ever wondered how to analyze your Google Analytics 4 (GA4) data by device type in BigQuery, you’re in the right place. Understanding whether your users come from desktop, mobile, or tablet devices is crucial for tailoring marketing strategies and improving user experiences. This tutorial will guide you through a simple, step-by-step process to filter and break down your GA4 data by device category using BigQuery.

For a hands-on experience, you can also watch the video tutorial where I demonstrate the exact query and filtering steps in real time.

Step 1: Understand the GA4 Device Category Field in BigQuery

GA4 exports raw event data to BigQuery, which includes a field called device category. This field is part of the device information nested inside the event data and indicates the type of device that triggered the event. The values you’ll commonly see are:

  • desktop
  • mobile
  • tablet

This categorization allows you to segment your users based on the device they use, which is essential for analyzing engagement, conversions, and revenue by device type.

BigQuery GA4 device category field in query

Step 2: Prepare Your Query to Extract Device Type and User Data

When working with GA4 data in BigQuery, you’ll notice that the event_date field is stored as a string by default. To use it effectively in reports and visualizations, you need to cast it to the DATE data type. This transformation is straightforward and essential for proper date grouping.

The basic components you want to include in your query are:

  • Transform event_date from string to date
  • Select device.category to get the device type
  • Use user_pseudo_id as a proxy for unique users
  • Group the results by date and device category

Here’s a simple example of how the query looks:

SELECT
DATE(event_date) AS event_date,
device.category AS device_category,
COUNT(DISTINCT user_pseudo_id) AS users
FROM
`your_project.your_dataset.events_*`
GROUP BY
event_date,
device_category
ORDER BY
event_date

Sample BigQuery GA4 device category query

Step 3: Run the Query and Analyze Users by Device Type

After running your prepared query, you will get a table showing the number of users broken down by device type for each date. This insight helps you understand how your audience accesses your website or app across different devices.

For example, you might see trends such as mobile users increasing on weekends or desktop users dominating during workdays. These insights can guide your marketing and content strategies.

Query results showing users by device type and date

Step 4: Filter Results for Specific Device Types

Sometimes, you want to focus your analysis on a particular device category, such as mobile users only. To do this, you can easily add a filtering clause in your SQL query using the WHERE statement.

For instance, to see only mobile users, modify your query like this:

SELECT
DATE(event_date) AS event_date,
device.category AS device_category,
COUNT(DISTINCT user_pseudo_id) AS users
FROM
`your_project.your_dataset.events_*`
WHERE
device.category = ‘mobile’
GROUP BY
event_date,
device_category
ORDER BY
event_date

This filter returns user counts exclusively from mobile devices, which can be very useful for mobile marketing campaigns or app optimization.

BigQuery GA4 query filtered by mobile device category

Step 5: Combine Device Type with Other Metrics for Deeper Insights

Device type is just one dimension. You can combine it with other metrics like sessions, conversions, or revenue to get a more comprehensive view of user behavior.

For example, you might want to compare conversion rates between desktop and mobile users or understand which device type generates the most revenue. You can extend your query by joining with other event parameters or adding additional aggregations.

Here’s a conceptual example of how to include conversions by device type:

SELECT
DATE(event_date) AS event_date,
device.category AS device_category,
COUNT(DISTINCT user_pseudo_id) AS users,
SUM(CASE WHEN event_name = ‘purchase’ THEN 1 ELSE 0 END) AS conversions
FROM
`your_project.your_dataset.events_*`
GROUP BY
event_date,
device_category
ORDER BY
event_date

This query counts unique users and sums conversions for each device category by date, providing a clearer picture of device-specific performance.

Step 6: Use This Approach as a Building Block for Advanced Analytics

The method outlined here is a fundamental building block for more advanced BigQuery and GA4 analyses. You can combine device filtering with session analysis, funnel tracking, or revenue attribution.

Since device category is a standard field in GA4 exports, it integrates seamlessly with other dimensions and metrics, making it easy to customize reports in Looker Studio or other BI tools.

I have recorded several tutorials that dive into sessions, conversions, and revenue analysis using BigQuery and GA4 data. Feel free to explore those to enhance your data skills.

BigQuery GA4 advanced query concepts

Wrapping Up: Why Filtering GA4 Data by Device in BigQuery Matters

Filtering GA4 users by device type in BigQuery allows marketers and analysts to get granular insights into how different audiences engage with digital properties. This knowledge helps tailor marketing efforts, optimize UX for devices, and allocate budgets more effectively.

The process is straightforward once you understand the key GA4 fields and how to write simple SQL queries that transform raw data into actionable insights. Remember:

  • Device category is your main dimension for device filtering.
  • Cast event_date to a date type to enable proper time grouping.
  • Use user_pseudo_id to count unique users.
  • Add filters with a WHERE clause to narrow down device types.

Applying these steps will empower you to create tailored reports and dashboards that can drive smarter decisions in your marketing and product teams.

Explore More Tutorials to Boost Your Analytics Skills

If you found this tutorial helpful, here are some other articles you might enjoy to deepen your knowledge of Looker Studio, BigQuery, and marketing analytics: