Start Exploring Keyword Ideas

Use Serpstat to find the best keywords for your website

3953 8
SEO 18 min read September 8, 2023

Unlimited Synergy: How GA4 and BigQuery are Redefining Data Transformation

Unlimited Synergy: How GA4 and BigQuery Redefining Data Transformation
Omi Sido
Omi Sido
Senior Technical SEO at
Canon Europe


Google Analytics 4 (GA4) is one of the essential tools for an SEO specialist. This free service tracks the effectiveness of website promotion and collects statistics on user interaction with web pages. However, it is not all-powerful. Reports in GA4 provide limited information, making them more suitable for evaluating the work done rather than making decisions based on the data.

To automate your marketing strategy, consider exporting data from Google Analytics to a more powerful analytics tool. Fortunately, Google offers a ready-made solution: GA4 and BigQuery integration. Learn more about the benefits of this powerful combination.

This article is based on the webinar. Together with Omi, we'll delve into all the benefits of Google Analytics 4 — from easy visualization of metrics to a holistic approach to data management. We'll also learn how BigQuery integration works, look at a few use cases for the tool, and explore the next generation of machine learning-based analytics.

Subscribe to Serpstat Google Calendar so you don't miss our helpful online events

Add to your Google Calendar

Understanding Google Analytics 4

Let's start with a bit of history. Google Analytics has come a long way in its development. Like many other big companies, Google didn't invent GA. In 2005, a simple web analytics tool called Urchin.js caught the search giant's attention. The corporation acquired the development company and renamed the service to Google Analytics in 2007.

The first version of the application focused on collecting information about page views, which were the key traffic metrics at the time. However, with the development of Web 2.0 technology and the rise of mobile devices, single-page websites became widespread on the Internet. Visits and views were no longer as informative to webmasters. As a result, Google was forced to improve its system, and around 2016, they introduced what are known as 'events,' but events were always considered secondary in Google Analytics.

In 2014, Google acquired another “analytical” company called Firebase and rebranded it as Google Firebase. Firebase was already doing amazing things with mobile tracking, and its system allowed for deeper insights into user engagement and better predictions of future behavior. The key elements of this service served as the basis for the next version of Universal Analytics, called Google Analytics 4.

Google Analytics 4 was released in October 2020. Initially, it was optional, but as of July 1, 2023, it became mandatory, and the previous version, Universal Analytics, stopped processing new data. The data in Universal Analytics remains accessible until July 1, 2024, after which the application will no longer be available.

GA4 history

Google Analytics 4 setup is very simple. You create an account, create a project, and connect your website to it. It takes 24 hours to fully load — during this time, the information may be inaccurate. To learn the platform's features, you can practice on Google Analytics test data. 

The key issue that made many users hesitate to migrate to GA4 is the inability to extract data from Google Analytics' previous versions, such as Universal Analytics, and transfer the information to the new platform. The reason is that these systems are incompatible with each other. The old system was based on page views, while the new system GA4 is centered around events. Because the two systems have different data models, the numbers will differ. For instance, metrics that appear to have similar names are, in fact, quite distinct. For example, Users in Universal Analytics and Active Users in GA4, though they may sound similar, refer to different measurements. With all that in mind, it is obvious that migrating from Universal Analytics to GA4 cannot be done without losing historical data.

GA4 history

But most of those who switched to the platform's new version voluntarily have never regretted their decision. And below, we will tell you why. 

GA4 Benefits

User journey

Reports in GA4 give you a more comprehensive picture of your customer journey. Google Analytics 4 is based on a holistic approach that treats your website and app as a unified entity rather than two separate conversion tools.

GA4 benefits User journey

Google Analytics 4 tracks cross-platform user activity more accurately. You get real data that allows you to make important management decisions. In addition, the new version of the service gives accurate metrics by device type — mobile and desktop. This benefits many SEO areas, including content marketing.

Focus on user engagement

GA4 offers fresh and improved indicators for assessing user engagement compared to Universal Analytics metrics like page views and bounce rates. Take the new engagement rate, for instance; it's arguably more informative than the traditional bounce rate because it provides a deeper insight into visitor behavior, showing not just how often they leave but also how frequently they stay on and interact with your website or mobile app.

GA4 benefits Focus on user engagement

In GA4 data, a session is considered engaged when one of three conditions is met:

1.A duration of more than 10 seconds.
2.Viewing two or more website pages.
3.The triggering of a conversion event. 

For most SEO specialists, a metric that indicates how frequently visitors remain on and interact with our website or mobile app is more crucial than a metric like bounce rate, which only reflects how often visitors leave. The new engagement metrics are considerably more useful for assessing the quality of UX/UI design. Additionally, they assist in accurately evaluating the effectiveness of optimizing blogs and other informational pages, where the primary conversion goal is reading the material.

Enhanced visualizations and reporting 

The Enhanced Visualizations and Reporting (Explorations), previously exclusive to GA360 paid subscription users, are now accessible to everyone. Think of this tool as Looker Studio (formerly Data Studio) within the GA environment. With Explorations, you can create personalized reports by selecting your own sets of metrics and objects of observation.

GA4 benefits Enhanced visualizations and reporting

One of the key benefits of the new feature is the possibility to build simple illustrative graphs. For example, you can display your mobile and desktop audience as a Venn diagram with intersecting circles. If you still don't use BigQuery with Google Analytics, ready-made reports will be your best information processing tool. 

DebugView

GA4 provides a debugging tool within the interface, offering a real-time report of the most popular events over the last 30 minutes. This debugging feature enables us to track relevant events in real time. The tool is precious for understanding GA4; simply click on a specific event to view all its parameters. This capability allows you to detect and resolve errors more swiftly. The easiest method to activate DebugView is by installing the Google Analytics Debugger Chrome extension.

GA4 benefits DebugView

You get access to all its parameters by clicking on any event in the list. This allows you to detect and eliminate errors much faster. Plus, you can run the debugger instantly using a plugin for Google Chrome. 

Holistic approach to data management

You can export data from Google Analytics 4 to Data Studio, Spreadsheets, BigQuery, and other cloud-based tools. This approach allows you to combine multiple information streams to create a single database with advanced analytics features.

GA4 benefits Holistic approach to data management

The interaction between GA4 and BigQuery does not require programming and does not involve any complex processes. The new platform also allows you to add a third component to the combination. For example, the current Serpstat version supports working with both tools — you can follow these updates in the changelog

Serpstat custom reports

By uploading Google Analytics raw data to third-party applications and combining them with other data streams, you get the tools you need to automate your marketing strategy. Manual management is replaced by scripts that are driven by predefined events. 

Unlock the power of data-driven decisions with Serpstat's exclusive 7-day trial!

Gain access to an arsenal of SEO metrics that matter, combined seamlessly with Google Analytics 4, all in one customizable dashboard.

Sign Up Now

BigQuery's Free Integration with Google Analytics 4

Connecting this platform was previously the privilege of GA360 paid subscription holders. Now, anyone can use BigQuery with Google Analytics 4. This is not an exaggeration — there is a free subscription in the application, which gives you 10 GB for database storage and up to 1TB of query data per month. This will be enough for small businesses and novice Internet marketers.

Connecting BigQuery with Google Analytics 4 is a simple process:

1.Create a project in Google BigQuery. You can do this by going to https://console.cloud.google.com/bigquery and clicking the Create project button.
2.Enable Google Analytics 4 BigQuery linking in GA4. To do this, follow these steps:
  • Open Google Analytics 4 and navigate to the Admin section.
  • Click Product Links in the left-hand menu.
  • Click BigQuery Link.
  • Select the project you want to connect to the database and click Confirm.
3.Choose the frequency of data updates. You can choose to export data daily or in real-time. Keep in mind that exporting all data from Google Analytics can take up to 2-3 days, so you should wait until your database is fully populated before choosing real-time updates.
4.Enable the BigQuery API and create a service account in the Google Cloud Platform Console. To do this, follow these steps:
  • Go to the APIs & Services section of the Google Cloud Platform Console.
  • Click Library and search for the BigQuery API.
  • Click Enable and then Manage.
  • Click Create service account.
5.Select the Service account client ID option and click Create.

Once you have completed these steps, your Google Analytics 4 data will be exported to BigQuery.

GA4 Connect to BigQuery

Why You Need Google BigQuery and Google Analytics 4 Integration

The main benefit will be the holistic approach we talked about above. You can export reports in Google Analytics, adding information from third-party sources, such as CRM. The app allows you to link these streams together, look for correlations, and get data to make management decisions.

The advantages of Google Analytics and Google BigQuery integration will also include:

How to Use BigQuery for Analytics?

After Google Analytics 4 setup, following all the instructions, and waiting 2-3 days, you can start to put your database into practice. To effectively analyze GA4 data, you will need to master SQL query language, at least at the elementary level. However, you can generate some reports using ready-made scripts.

To query a table, open BigQuery and find the icon in the form of a magnifying glass at the top center. Click on it to create a new SQL query.

Your first SQL Statement

Insert a "*" between the SELECT and FROM commands, then click Run. 

Your first SQL Statement

You will get a table with nested fields.

Your first SQL Statement

Source: omisido.com

2 Queries to Get Started with GA4 in BigQuery

To group pages by the number of visits, you will need this script:

SELECT
  (SELECT value.string_value FROM UNNEST(event_params)
 WHERE key = "page_title") AS Page_Title,
  COUNT(*) AS Page_Views
FROM`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`WHERE
  event_name = "page_view"AND EXISTS (SELECT 1 FROM UNNEST(event_params)
 WHERE key = "page_title")
GROUP BY
  Page_Title
ORDER BY
  Page_Views DESCLIMIT10;

If you want to compare the total number of users to the number of new visitors, enter the following query:

WITH UserInfo AS (
  SELECT
    user_pseudo_id,
    MAX(
      IF(event_name IN ('first_visit', 'first_open'), 1, 0)
    ) AS is_new_user
  FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20201101' AND '20220131'
  GROUP BY
    1
)
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM
  UserInfo;

Note: Don’t forget to replace the example table name with your table name.

If you export all data from Google Analytics, you'll have a variety of scenarios for applying the information. By combining BigQuery variables in your queries, you can find out which landing pages have a higher level of user engagement, which promotion strategies are most effective for sales, and which sections of the site have the highest bounce rate.

And that's not all. For those who consider themselves a little geeky and inclined towards adventure, BigQuery offers the exciting capability of training and deploying machine learning models directly within the BigQuery environment.

GA4 + BigQuery: From SEO to ML

BigQuery allows you to train predictive models right in this data processing environment without third-party tools. BigQuery ML can be used to predict the behavior of your website visitors in a variety of scenarios, such as:

GA4 BigQuery machine learning

By connecting GA4 to BigQuery, you can use the following models for data analysis:

1.Linear regression will continue a series of data by predicting the value of a variable at a certain point. It will tell you how many products you can sell on a certain date and what the total revenue of the company will be by the Nth date.
2.Logistic regression allows you to categorize an observation by placing it in the most appropriate category. This model can predict which product a user with unique behavioral characteristics will buy. 
3.K-means will divide the finished data set into categories. With Google Analytics user engagement statistics and information from your CRM, you can categorize potential customers into groups based on the likelihood of making a purchase. 

BigQuery also supports processing data export from Google Analytics using TensorFlow models. They are the closest to modern AI — they are used to find user behavior patterns. For example, you can predict how a visitor will behave from the first seconds to generate a personalized offer for them. The only downside is that TensorFlow models require pre-training. You can connect them to BigQuery but not customize them inside the database.

Conclusion

Google Analytics 4 offers several advantages over its previous versions, including a strong emphasis on user engagement, customer journey tracking, and improved reporting. However, one of the most significant benefits of this update is the ability to export and analyze Google Analytics data in BigQuery, at no cost.

BigQuery enables the integration of multiple data streams and provides an extensive suite of tools for in-depth analysis. Whether you choose to learn the SQL or use pre-made scripts, you can create customized reports tailored to your specific business needs. BigQuery allows you to fully harness the potential of the Google Analytics 4 API by combining various metrics and observation objects.

Another distinctive feature of BigQuery is the integration of machine learning technology. BigQuery ML empowers you to develop and train predictive models that can predict user behaviour. This capability enables you to optimize your marketing strategy and automate its practical implementation.

The opinion of the guest post authors may not coincide with the opinion of the Serpstat editorial staff and specialists.

Rate the article on a five-point scale

The article has already been rated by 7 people on average 5 out of 5
Found an error? Select it and press Ctrl + Enter to tell us

Discover More SEO Tools

Backlink Cheсker

Backlinks checking for any site. Increase the power of your backlink profile

API for SEO

Search big data and get results using SEO API

Competitor Website Analytics

Complete analysis of competitors' websites for SEO and PPC

Keyword Rank Checker

Google Keyword Rankings Checker - gain valuable insights into your website's search engine rankings

Share this article with your friends

Are you sure?

Introducing Serpstat

Find out about the main features of the service in a convenient way for you!

Please send a request, and our specialist will offer you education options: a personal demonstration, a trial period, or materials for self-study and increasing expertise — everything for a comfortable start to work with Serpstat.

Name

Email

Phone

We are glad of your comment
I agree to Serpstat`s Privacy Policy.

Thank you, we have saved your new mailing settings.

Report a bug

Cancel
Open support chat
mail pocket flipboard Messenger telegramm