How to Export Data from Google Analytics 4 to Google BigQuery
Google Analytics 4 makes analyzing data in Google BigQuery easier than ever. Now almost everyone can collect data in BigQuery for free. In this article, we explain how to properly export data to BigQuery from Google Analytics 4 and what else you should take into account to get the most value out of your collected information.
Table of contents
- Why you need to gather raw unsampled data
- Where to store collected data
- What is Google BigQuery?
- Why Google BigQuery?
- Export schema
- How to export raw data from Google Analytics 4 to Google BigQuery
- What’s next?
Find out the real value of campaigns
Automatically import cost data to Google Analytics from all your advertising services. Compare campaign costs, CPC, and ROAS in a single report.
Why you need to gather raw unsampled data
Raw (unprocessed) data allows you to precisely analyze your business processes. By collecting raw data, you can:
- Objectively assess your business processes
- Perform deep analysis of metrics
- Track the entire user journey
- Build any reports without limits
- Segment your audience and set up targeted advertising
Sampling means extrapolating the results of analysis for a given segment, when the amount of information is too big to process quickly (for example, if you use multiple custom dimensions in your reports). Sampling can significantly distort your reporting and cause you to misevaluate your results since you analyze not all data but only at portion. By doing this, you risk investing in inefficient ad campaigns or turning off revenue-generating advertising channels. As you can see, avoiding sampling is definitely a good idea. And thankfully, it’s achievable.
Try OWOX BI for free to collect unsampled data from various sources in Google Analytics or BigQuery.
Where to store collected data
Let’s get to the practical side of the question: Which analytics platform is convenient, affordable, and allows you to work with raw unsampled data? The solution we recommend — Google BigQuery — is probably the most popular among marketers around the world, and there are a bunch of solid reasons for that. We recommend you collect, store, and process raw data using BigQuery cloud storage, and below we’ll explain why.
What is Google BigQuery?
Google BigQuery is a multi-cloud data warehouse with a built-in query service and a high level of security and scalability. According to Gartner, “by 2022, 75% of all databases will be deployed or migrated to a cloud platform, with only 5% ever considered for repatriation to on-premises.” And thanks to the fact that BigQuery is part of the Google ecosystem and the Google Cloud Platform in particular, it natively integrates with other Google products and helps you develop your business at a competitive speed.
Why Google BigQuery?
There are multiple low-level aspects that make BigQuery almost irreplaceable for marketers. Let’s take a closer look at some of its most valuable benefits:
- Ability to upload large volumes of information. With BigQuery, you can perform real-time analysis of any type of data and quickly process it with SQL.
- High level of security. You get full control over your project and can take advantage of two-factor authentication.
- Affordable. Pay only for collected and processed data.
- Native integration with Google products. Easily connect with Google Analytics and other products.
- Scalable. Scale quickly and seamlessly to easily adjust to the rapidly changing world.
- BigQuery ML. Build machine learning prediction models on both structured and semi-structured data using SQL.
- BigQuery GIS. Thanks to the BigQuery Geographic Information System (GIS), you can analyze geospatial information and determine which users should get a mailer for a specific store location, for instance.
Read also: Why is Google BigQuery the perfect data lake for marketing?
Let’s examine the format and schema of the GA 4 property data that’s exported to BigQuery. One important thing to keep in mind when working with GA 4 is that its structure differs from the structure of Universal Analytics, which is familiar to marketers all over the world.
This is how Google Analytics 4 schema differs from Universal Analytics schema:
- Datasets. GA sample datasets are named analytics_, where property ID is your Analytics Property ID.
- Tables. A separate Google Analytics table is imported into the dataset for each day. The format of such tables is events_YYYYMMDD, unlike in Universal Analytics where it’s ga_sessions_YYYYMMDD.
- Rows. Each row corresponds to an uploaded event, in contrast to Universal Analytics where each row corresponds to a Google Analytics 360 session.
- Columns. The field names largely differ between GA 4 and Universal Analytics. You can compare them by following these links:
Of course, the new Google Analytics 4 structure has its advantages. But if you’re not ready for such changes and still want to collect your marketing data in BigQuery, you can use OWOX BI Pipeline.
Now let’s get to the main purpose of this article: providing step-by-step instructions on how to export your data from Google Analytics 4 to BigQuery.
How to export raw data from Google Analytics 4 to Google BigQuery
1. Create a Google-APIs-Console project
To create an APIs-Console project:
- Log in to the Google APIs Console.
- Create a new project or select an existing project.
2. Enable BigQuery
- Go to the APIs table.
- Go to the Navigation menu and click APIs & Services, then select Library.
3. Link BigQuery to a Google Analytics 4 property
- Log in to your Google Analytics account. The account should have Owner access to your BigQuery project and Edit access to the Google Analytics 4 property you’re working with.
- Go to the Admin tab and find the Analytics property you need to link to BigQuery.
- In the Property column, click BigQuery Linking.
- Click Link.
- Click Choose a BigQuery project to see projects you have access to. To create a new BigQuery project, click Learn more.
- Select your project and click Confirm.
- Select a location. (If your project already has a dataset for the Analytics property, you can’t configure this option.)
- Click Next.
- Select the data streams whose information you want to export.
If you need to include advertising identifiers, check Include advertising identifiers for mobile app streams.
- Set the Frequency: Daily or Streaming (continuous) export (you can also select both options).
- Finally, click Submit.
Congrats! You’ll see your Google Analytics 4 information in your BigQuery project within 24 hours.
Now you have all raw data on user behavior in BigQuery. However, to perform marketing analysis, find your growth zones and weak points, you need to add cost data from advertising services, data from CRM systems, call tracking services, and mobile apps (if you use any) to GBQ. Next, merge all this data into one dataset and make data business-ready, so that marketers can easily create reports based on BigQuery data.
One of the most optimal solutions for gathering and processing all your marketing data is OWOX BI Pipeline. It includes data from ad services, CRMs, call tracking services, and offline stores to BigQuery to complete your data puzzle.
Once you have all the necessary data in place, it’s time to make it work for you. Usually, that’s a task for an analyst, but with OWOX BI Smart Data, anyone can easily work with BigQuery data.
BigQuery frequently scares marketers and seems to be complex, but there’s no need to worry about that: there’s a solution that can help you easily discover all potential of your marketing data. OWOX BI can arrange it in a model adjusted to your business, so that you can easily build reports even if you don’t know any SQL at all. Just use a simple report builder or select a ready-made template and visualize the results in your favorite visualization tool.
You can easily export your GA 4 information to BigQuery. If the new structure works well for you, you can take advantage of this progressive service and add it to your marketing analytics toolbox. With OWOX BI Pipeline, you can collect data from your website, ad services, CRM, offline stores, and call tracking services in BigQuery to complete your data. And with OWOX BI Smart Data, you can make that data work for you by building reports, transforming multiple rows and tables into actionable insights, and improving your decision-making.
grow 22% faster
Grow faster by measuring what works best in your marketing
Analyze your marketing efficiency, find the growth areas, increase ROIGet demo
What is Google BigQuery?Google BigQuery is a multi-cloud data warehouse with a built-in query service and a high level of security and scalability.
How does Google Analytics 4 connect to BigQuery?
How do I pay for Google BigQuery?
You pay for using Google BigQuery separately from OWOX BI. Payments are made directly to Google with no commission or restrictions.
How do you pull data from Google Analytics 4 to BigQuery?To pull you data from Google Analytics 4 to BigQuery:
1. Create a Google-APIs-Console project.
2. Enable BigQuery.
3. Link BigQuery to a Google Analytics 4 property.
For a detailed instruction, see How to Export Data from Google Analytics 4 to Google BigQuery.