You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

How to replicate the 'Audience | Technology | Browser & OS' report

Join 3,200+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in
💡
This article is about GA3 - Universal Analytics

As a Google Analytics user you are probably quite attached to the default reports in the user interface of Universal Analytics. It can be hard to make sense of the data in the BigQuery export tables.

Let me enable you to replicate the reports you're familiar with. I'll try to keep it basic here.

Browser & OS report

In the Audience | Technology | Browser & OS report you'll find data about acquisition, behaviour and conversion of your users, segmented by browser specifications. The (primary) dimensions that are available are browser, browser version, browser size, operation system, operation system version, screen resolution, screen colors, flash version and java support.

Let's query!

select 
  device.browser,
  -- device.browserversion,
  -- device.browsersize,
  -- device.operatingsystem,
  -- device.operatingsystemversion,
  -- device.screenresolution,
  -- device.screencolors,
  -- device.flashversion,
  -- case when device.javaenabled is true then 'Yes' else 'No' end as java_support,
  count(distinct fullvisitorid) as users,
  count(distinct(case when totals.newvisits = 1 then fullvisitorid else null end)) as new_users,
  count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as sessions,
  count(distinct case when totals.bounces = 1 then concat(fullvisitorid, cast(visitstarttime as string)) else null end ) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as bounce_rate,
  sum(totals.pageviews) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as pages_per_session,
  ifnull(sum(totals.timeonsite) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))),0) as average_session_duration,
  ifnull(sum(totals.transactions),0) as transactions,
  ifnull(sum(totals.totaltransactionrevenue),0)/1000000 as revenue,
  ifnull(sum(totals.transactions) / count(distinct concat(fullvisitorid, cast(visitstarttime as string))),0) as ecommerce_conversion_rate
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
where
  totals.visits = 1
group by
  browser
  -- ,browserversion
  -- ,browsersize
  -- ,operatingsystem
  -- ,operatingsystemversion
  -- ,screenresolution
  -- ,screencolors
  -- ,flashversion
  -- ,java_support
order by
  users desc