Build a GA4 Tech (Device, OS, and Browser) Performance Report in BigQuery

GA4’s tech report tells you how visitors split across devices, operating systems, and browsers, but the moment you want it defined your way, on the grain you choose, feeding your own dashboard, the interface runs out of room. The raw export does not. This code-along builds a custom tech performance report straight from the export, with every metric calculated transparently. As with any export query, it will not match a GA4 screen exactly, because the documentation, the interface, and the raw data each define things slightly differently, and that is the value. You own the definitions, and the result drops cleanly into a spreadsheet, a saved table, or Looker Studio.

What the report contains

The dimensions describe the visitor’s technology in increasing detail: the date, the device category, the operating system and its version, the browser and its version, and then the mobile-specific fields, the brand name, model name, marketing name, and OS hardware model. The metrics describe behaviour: users, new users, engaged sessions, engagement rate, engaged sessions per user, average engagement time in seconds, event count, conversions, and total revenue. One convention worth stating up front, as in the other reports in this series: the calculated user metrics use total users as the denominator.

The nice thing about a tech report is that nearly everything we need lives in one place. GA4 stores all the device attributes in a single structured field on every event, so the build is short: flatten the events, then aggregate.

Step one: the base layer

We read the export once, pulling the session identifier and engagement fields from the parameter array and the device attributes straight from the device record.

WITH base AS (
  SELECT
    user_pseudo_id,
    CONCAT(user_pseudo_id, '-', CAST(
      (SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS STRING)) AS session_key,
    PARSE_DATE('%Y%m%d', event_date) AS event_dt,
    event_name,
    (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'session_engaged')      AS session_engaged,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'engagement_time_msec') AS engagement_msec,
    (SELECT ep.value.int_value    FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_number')    AS session_number,
    device.category                    AS device_category,
    device.operating_system            AS operating_system,
    device.operating_system_version    AS operating_system_version,
    device.web_info.browser            AS browser,
    device.web_info.browser_version    AS browser_version,
    device.mobile_brand_name           AS mobile_brand_name,
    device.mobile_model_name           AS mobile_model_name,
    device.mobile_marketing_name       AS mobile_marketing_name,
    device.mobile_os_hardware_model    AS mobile_os_hardware_model,
    ecommerce.purchase_revenue         AS purchase_revenue
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
)

The session key combines the user and session id so we can count sessions later. The engagement flag, engagement time, and session number come out of the parameter array, where the session number lets us identify first-time visitors. Everything tech-related comes from the device record, including the browser fields nested under web_info, with no unnesting required because these are plain struct fields. Purchase revenue comes from the ecommerce record, and the table suffix filter sets the window and contains the scan.

Step two: the report

Because the device attributes sit on every event, we can compute the whole report in a single aggregation grouped by the dimensions.

SELECT
  event_dt AS date,
  device_category,
  operating_system,
  operating_system_version,
  browser,
  browser_version,
  mobile_brand_name,
  mobile_model_name,
  mobile_marketing_name,
  mobile_os_hardware_model,
  COUNT(DISTINCT user_pseudo_id)                                       AS users,
  COUNT(DISTINCT IF(session_number = 1, user_pseudo_id, NULL))         AS new_users,
  COUNT(DISTINCT IF(session_engaged = '1', session_key, NULL))        AS engaged_sessions,
  ROUND(COUNT(DISTINCT IF(session_engaged = '1', session_key, NULL))
        / COUNT(DISTINCT session_key), 4)                             AS engagement_rate,
  ROUND(COUNT(DISTINCT IF(session_engaged = '1', session_key, NULL))
        / COUNT(DISTINCT user_pseudo_id), 2)                          AS engaged_sessions_per_user,
  ROUND(SUM(engagement_msec) / 1000 / COUNT(DISTINCT user_pseudo_id), 1) AS avg_engagement_seconds,
  COUNT(*)                                                            AS event_count,
  COUNTIF(event_name IN ('purchase', 'generate_lead'))               AS conversions,
  SUM(IF(event_name = 'purchase', IFNULL(purchase_revenue, 0), 0))    AS total_revenue
FROM base
GROUP BY
  date, device_category, operating_system, operating_system_version,
  browser, browser_version, mobile_brand_name, mobile_model_name,
  mobile_marketing_name, mobile_os_hardware_model
ORDER BY users DESC

Users are the distinct visitors, and new users are the distinct visitors whose session number is one, which marks a first session. Engaged sessions count the distinct sessions flagged as engaged, and engagement rate divides that by total sessions, which we count internally as distinct session keys even though it is not an output column. Engaged sessions per user and average engagement time both use total users as the denominator, following the stated convention. Event count is simply the number of events, conversions count your key events, and total revenue sums the revenue from purchase events.

Considerations

The browser and browser-version fields live under the web-info part of the device record and will be empty for app data streams, just as the mobile-specific fields are often not set for web traffic, so expect placeholder values depending on your platform mix. Grouping by all ten dimensions at once is extremely granular and will produce many sparse rows, so in practice trim the dimension list to the level you actually need, often just device category, operating system, and browser. The revenue here is purchase revenue, so if you also earn subscription or ad revenue and want a true total, add those streams in. Average engagement time is per user because of the convention, so divide by sessions instead if you want the per-session figure. The export timestamps are UTC, which produces small day-boundary differences from a locally timed interface. And as always, the totals will not match the GA4 screen exactly, which is the sign that you now control the definitions.

The payoff is a tech report you can defend and extend. Every metric is defined in SQL you can read, the device detail goes as deep as you want, and the same query feeds a one-off export, a scheduled table, or a live dashboard. From here you can pivot it to compare conversion rates across device categories, isolate a single browser to investigate a bug, or join it with your acquisition report to see which channels bring which devices.

See you soon.