💡
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.

Language report

In the Audience | Geo | Language report you'll find data about acquisition, behaviour and conversion of your users, segmented by language ISO code (see reference data).

Let's query!

select 
  device.language,
  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
  language
order by
  users desc 

Free

$0

Join the conversation
  • Monthly newsletter packed with GA4 & BigQuery news and tips & tricks
  • Email notification when new content is added
  • Join the conversation (access to comment section)
  • Join 2100+ professionals who work at Google, Salesforce, Samsung, Booking.com, Bitvavo, Newsweek, Randstad, Dentsu, Nokia, Capgemini, Just Eat Takeaway and many many more

Premium

$49/month

Everything in Free, plus:
  • Immediately full access to premium tutorial/report archive*
  • Weekly new premium zero-to-hero tutorial/report in your inbox**
  • Ability to request a tutorial about your use case (not guaranteed)
  • 15% discount on Simmer course 'Query GA4 Data In Google BigQuery' (coupon available after subscribing)
  • 10% of your subscription fee will be donated to highly efficient charity
  • Invoice with specific company details (i.e. billing address and VAT id) available. Instructions on thank you page (after subscribing)
  • * Currently the premium archive contains 21 tutorials/reports, increasing to 30+ by July 2023 and 40+ by Jan 2024
  • ** Biweekly from July 2023, monthly from Jan 2024 onwards

Premium

$299/year

Everything in Free, plus:
  • Immediately full access to premium tutorial/report archive*
  • Weekly new premium zero-to-hero tutorial/report in your inbox**
  • Ability to request a tutorial about your use case (not guaranteed)
  • 15% discount on Simmer course 'Query GA4 Data In Google BigQuery' (coupon available after subscribing)
  • 10% of your subscription fee will be donated to highly efficient charity
  • Invoice with specific company details (i.e. billing address and VAT id) available. Instructions on thank you page (after subscribing)
  • * Currently the premium archive contains 21 tutorials/reports, increasing to 30+ by July 2023 and 40+ by Jan 2024
  • ** Biweekly from July 2023, monthly from Jan 2024 onwards