How to export GA4 data to BigQuery and view it in Looker Studio

In this post I’ll show you how to export your GA4 data to BigQuery and then view it in Looker Studio (formerly Data Studio).

1. Link your BigQuery account to Google Analytics 4

In GA4, go to the Admin section. Then scroll down to the list of Product Links and select BigQuery links.

selecting bigquery from the product links in GA4

You’ll then be presented with your links to BigQuery projects. If you’ve never linked any account before, it should say “No links yet. Click “Link” to create one.” Clink on the link button in the top right hand corner.

a screenshot from google analytics 4 showing no existing bq links

You’ll then be prompted to choose which BigQuery project you want to connect GA4 with.

choose a google bigquery project

You’ll then see the list of BigQuery projects you have access to and can select and confirm the one you want your analytics data to be exported to.

Google BQ projects list screenshot

You then select the Data Location suitable for your needs.

select data location in google bigquery

The next step is to configure the settings for the data export. There is a limit of 1 million events per day. If your export exceeds this repeatedly it will be paused. You also have the option to exclude events you think are not relevant or will cause you to exceed the daily limit.

You can choose either Daily or Streaming frequency. I’d recommend a daily export.

select the daily export of GA4 data to bigquery

Next you’d review your settings and click submit.

Now your data should start being sent to BigQuery where you can view it.

2. View your GA4 data in BigQuery

The next step is to confirm that GA4 data is arriving in BigQuery.

Open up the Google Cloud BigQuery console (https://console.cloud.google.com/bigquery) and under the Explorer tab on the left you should see your project, your analytics dataset, and your events table.

viewing your projects, datasets and tables in google cloud platform

To see your data, click on Query in the main tab. You can either perform a query in a new tab or side tab.

select query to use a SQL on your dataset

Then you can run a basic SQL query to view everything (*) from your table with a limit of 1000.

SELECT * FROM `your-project-123456.analytics_12356.events_20230204` LIMIT 1000

You’ll then get a look at the data in the table.

GA4 data in BigQuery

So we see our data is in the BigQuery table. Now we want to view it in Looker Studio (Data Studio).

3. Explore your BigQuery GA4 data in Looker Studio (Data Studio): Option 1

The first and simplest way is to simple add the Google Analytics Event export table labelled “events_YYYYMMDD” as a data source for Looker Studio.

Select BigQuery as your data source.

select the bigquery connector in looker studio

Then select the relevant Project, Data set and Table as your data source. Although it is GA4 export data it is labeled as Firebase Template Level event configuration.

You can then explore your exported GA4 data in Looker Studio.

4. Explore your BigQuery GA4 data in Looker Studio (Data Studio): Option 2

WARNING: If you have a large amount of data this might not be a good idea.

Please read this article from Anayltics Canvas about this method of adding BigQuery data to Looker Studio as it can apparently get very expensive.

Analytics Canvas: Avoid this costly mistake when connecting GA4 BigQuery to Data Studio

If you want to proceed then continue reading.

In your Looker Studio report click “Add Data” and select the BigQuery connector.

select the bigquery connector in looker studio

Select Custom Query and then the relevant Project from the options within the BigQuery connector.

select the appropriate bigquery project

Depending on your use case, you may want to have a custom SQL query that doesn’t simply select all (*) data from the table. I will show you how to get in all your data and be able to use the date range filter. Below is an example query.

SELECT * FROM `YOUR_PROJECT.analytics_12345678.events_*` 
WHERE _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE;

Make sure to enable date range parameters and include WHERE _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE; so you are able to use the date range filter correctly.

enable date range parameters in looker studio

You can then explore your GA4 data in Looker Studio without having to worry about the Google Analytics threshold, slow speed and other potential issues.

GA4 data in looker studio

Hopefully this guide helped you.

My Other Blog Posts

Comparing DALL-E and Midjourney AI Images in 2024
Four in One Analytics Looker Studio Dashboard Template
Visualizing a young Africa and an aging World
Visualizing Fertility Rates across the World
GDP per Capita within African Regions
Michael Howe-Ely