Introduction to the Data Transfer for Google Analytics 4

Google recently announced a new Data Transfer in BigQuery to get ready-made tables with data from Google Analytics 4. It’s a free service, and you only pay for storage in BigQuery and any subsequent SQL queries you run on the data.

Benefits of working with Google Analytics 4 data in BigQuery

Working with digital analytics data in BigQuery offers several benefits over using the Google Analytics 4 (GA4) user interface:

• Custom Reporting: BigQuery allows you to create custom reports and queries, whereas GA4 offers limited predefined reports.
• Flexibility: With BigQuery, you can access raw, unprocessed data, giving you more control over your analysis. GA4 only provides processed data.
• Scalability: BigQuery can handle large volumes of data more efficiently, making it easier to work with big datasets. GA4 can be slower and less flexible when dealing with large amounts of data.
• Advanced Analysis: BigQuery supports advanced analysis using SQL, enabling deeper insights. GA4 has fewer analysis tools compared to BigQuery.
• Data Integration: BigQuery allows you to integrate data from various sources, providing a more complete view of your analytics. GA4 is limited to the data within its system.

Let’s look at how to set up the new Data Transfer from Google Analytics 4, what tables are created, and what analysis you can do with the data in BigQuery.

Set up the Data Transfer for GA4

To start the transfer, go to the Data Transfers section in BigQuery Studio from the left menu. Then, click on “Create New Transfer” and select Google Analytics 4 from the dropdown menu.

There are seven simple steps to complete before you begin your Google Analytics 4 Data Transfer.

1. Data source details
   Enter the PropertyID for the Google Analytics 4 property you want to export. You can find the PropertyID under Property Details in the Admin section, or in the property selector at the top of the Google Analytics 4 interface. You can leave the Refresh window blank; it will default to 4 days.

2. Destination settings
   Choose or create a new dataset where the tables will be created. You can name it something like “ga4_data_transfer.” If you plan to load data from several properties into the same BigQuery project, it might be helpful to include the PropertyID in the dataset name for easy identification.

3. Transfer config name
   Give this transfer a descriptive name, such as “ga4_data_transfer.” If you plan to load multiple properties into the same BigQuery project, it’s useful to include the PropertyID in the name for easier identification.

4. Schedule options
   Here you can set up the update schedule. You can leave it as is, but it’s best to set the Repeat Frequency to “Days.” This lets you decide at what time the data should be updated each day (preferably in the morning). Make sure you check the timezone.

5. Service account
   If you leave this blank, it will use your personal account in BigQuery. Make sure your account has access to the Google Analytics 4 property. It’s safer to set up a service account in Google Cloud Platform (GCP) with proper access for this transfer, as relying on your personal account is risky. If you lose access to either the property or the BigQuery project, the transfer will fail.

6. Notification options
   You can enable notifications when the transfer finishes. While email notifications might not be common, using Pub/Sub lets you trigger other actions, like running queries on the new data after it’s transferred.

7. Advanced options
   Leave the Encryption key as Google-managed encryption unless your organization uses a Custom Key Management Service (CKMS).

Start the Data Transfer

Once you’ve filled out all seven options, click Save, and an initial backfill will begin for a few dates.

In the top-right corner, you can edit and manage the transfer. The first thing you should do is trigger a backfill to import more dates. Click on “Schedule Backfill” and select the dates you want to import from your property. The transfer will start and process them one by one.

The Data Transfer for Google Analytics 4 and the jobs history.

The Google Analytics 4 Data Transfer creates two versions of each table. The first one, prefixed with p_, contains the raw data, while the second one, prefixed with ga4_, is a view that extracts the date from _PARTITIONTIME

An example of the table and view created for the Audience report for the Google Analytics 4 property with ID 252722903. 

For your queries, always use the ga4_ views.

The table names are the same as the reports in the GA4 interface they replicate. For example, the ga4_pageandscreens table corresponds to the Pages and Screens report in the Google Analytics 4 reporting interface.

The tables are ready to query!

The tables created by Google Analytics 4 Data Transfer

These are the tables and views that are loaded into BgiQuery. Further down in the article we will have an in-depth look at each and one of them.

Example querys for the tables created by the Google Analytics 4 Data Transfer

With the tables loaded into BigQuery, let´s look at two different query´s we can run using these tables.

Traffic Acquisition report

In our first query, we want to generate a simple Traffic Acquisition report and see which Channel Group drives sessions and conversions.

SELECT
  _DATA_DATE AS date,
  session_default_channel_group as channel,
  sessions,
  conversions
FROM
  `<project>.<dataset>.ga4_userAcquisition_<propertyID>`
ORDER BY 
    _DATA_DATE DESC 

The result is a table that shows sessions and conversions per Channel Group and date.

Device category report

In our next query, we want to generate a simple Traffic Acquisition report and see which Channel Group drives sessions and conversions.

SELECT
  _DATA_DATE AS date,
  device_category,
  engaged_sessions
FROM
  `<project>.<dataset>.ga4_techDetails_<propertyID>`
ORDER BY 
  _DATA_DATE desc

The result is a table that shows engaged session per device category and date.

Schema of the tables created by Google Analytics 4 Data Transfer

Lets have a look at all the different tables that are create by the Google Analytics 4 Data Transfer.

Audiences table

This table contains aggregated data by audience group from Google Analytics 4. It shows how different user segments perform in terms of traffic, engagement, and revenue. You can use this data to compare audience behavior, evaluate marketing strategies, and find high-performing or underperforming user segments.

Conversions table

This table shows event-level performance in GA4. It connects user actions (events) to key outcomes like conversions and revenue. If the event was marked as a key event it will count as a conversion. 

Demographic details table

This table combines user demographics, location, and behavior data. It helps you analyze how different audience groups (by age, gender, interest, location) engage with your site or app. You can use this to tailor content, identify high-value segments, and improve targeting in marketing campaigns.

Ecommerce purchases table

This table contains product-level data from GA4 e-commerce events. It shows how individual items perform across categories and variants. You can use it to track product performance, understand customer preferences, and guide inventory or marketing decisions based on what sells or gets added to carts.

Events table

Very similar to the Conversion table. This table summarizes how each event statistics, both count, per user, eventual revenue and total users per event. 

In-app purchases table

This table focuses on individual item performance in purchase events. It helps you track which products generate the most revenue and how many units are sold. Useful for analyzing product sales, inventory planning, and evaluating item-level performance.

Landing page table

This table shows where your session came from, grouped by source, medium, campaign, and channel. It helps you understand the performance of marketing efforts, track which sources drive revenue and conversions, and compare acquisition strategies. It’s useful for traffic analysis, campaign tracking, and marketing ROI. But one thing, to note, even do it is named Landing Page report, it lacks any information about the landing page, making it more of a traffic acquisition report. 

Pages and screens table

This table gives insights into how users interact with different pages (URLs) or screens. It includes views, events, revenue, and engagement data. 

Tech details table

This table gives insights into how users on different platforms and devices interact with your site or app. It includes device details, operating systems, screen info, and engagement metrics. 

Traffic Acquisition table

Note: There is two tables acquisition, Traffic and User. But it appears that the “User” table is session-scoped and the “Traffic” table is user-scoped. 

This table shows how users were first acquired, including campaign, channel, and ad details. It links acquisition data to behavior and outcomes like conversions and revenue. 

User Acquisition table

This table provides a breakdown of traffic sources at the session level. It links acquisition details like campaign, source, and medium with user behavior and business outcomes such as events, conversions, and revenue.

The differences between this data transfer and the “old” GA4 event export to BigQuery.

The big difference compared to the old event-based export to BigQuery is that this new data transfer is aggregated and not as granular. This means we don’t have the same ability to combine and calculate data as we would like.

Therefore, there will likely be a benefit in using both exports for different needs and analyses within a business.

Summary 

The easy ways to load data between different systems are always helpful, whether you’re loading data into BigQuery or out. As of now, there are still some bugs, but hopefully, they will be fixed soon. After that, this will be a great addition to your toolbox for digital analytics.

One big advantage is that the tables follow the same definitions as the graphs and tables in the Google Analytics 4 interface, which makes things easier. Since they are pre-calculated and aggregated around the right dimensions with familiar terminology, it’s easy for even beginners to start their first queries and analyses in BigQuery.

If you want to explore hosting your own GA4 backfill pipeline solution, Ctrl Digital has developed its own open source transfer solution that lets you configure and run a similar export yourself. This Python script fetches data from Google Analytics 4 using the Google Analytics Data API and inserts it into Google BigQuery. You can also backfill data for any date range.

Check it out at https://github.com/ctrl-digital/ga4-bigquery-backfill.

Happy data backfilling!