Unlimited Synergy: How GA4 and BigQuery are Redefining Data Transformation

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.

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.

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.

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.

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

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.

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.

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.

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. 

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. 

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:

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:

• Scalability: BigQuery is designed to handle large volumes of data efficiently. By integrating GA4 with BQ, you can easily scale your data analysis as your business grows without worrying about infrastructure limitations.
  
• Seamless data transfer: Integrating BigQuery and GA4 allows for automatic and real-time data transfer. This ensures you have access to the most up-to-date data for analysis and reporting.
  
• Building a simple but very effective client data platform: This integration allows you to view detailed information about each visitor.
  
• Easy report design: You can combine any GA metrics — Google Analytics user engagement, bounce rate, and so on.
  
• Grouping communication channels: You can do it according to individual rules and assess the impact of each channel on sales.
  
• Advanced analysis: BigQuery offers a wide range of analytical tools and machine-learning capabilities that can be applied to GA4 data. You can perform cohort analysis, predictive modeling, segmentation, and more, which can uncover valuable insights for marketing, user behavior, and customer engagement.
  

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.

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

You will get a table with nested fields.

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:

• Predicting which visitors to your website are most likely to churn.
  
• Predicting which products (or services) users are most likely to purchase.
• Predicting which pages users are most likely to visit..
• Predicting which ads users are most likely to click on.

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

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.