5 Reasons to Create Report in Google BigQuery in 2025

Alexander Diduh

,

Web analyst

March 8, 2024

Google Analytics 4 is the world’s most used web analytics system. It’s easy to implement and provides a variety of out-of-the-box reports. All in all, Google Analytics 4 is a great tool for analyzing basic KPIs. However, if you’ve ever tried performing deeper analysis of your website, you may have noticed that in some cases Google Analytics 4 lacks the capabilities you need.

The challenges in GA4 mainly revolve around understanding its new data model, navigating the interface, and adapting to the event-based tracking system. Additionally, users need to adapt to the changes in reporting, especially those who are transitioning from Universal Analytics.

In this article, you’ll learn what challenges you might face when building reports in Google Analytics 4, and how to solve these challenges using Google BigQuery cloud database.

Note: This post was originally published in July 2017 and was completely updated in January 2024 for accuracy and comprehensiveness.

What are Google BigQuery Reports

Google BigQuery is a fully managed, serverless data warehouse that is part of the Google Cloud Platform. It enables the processing of SQL queries to your data in just a matter of minutes. BigQuery's capabilities include the data import from a variety of external and internal systems, storage and analysis of extensive datasets.

For data visualization and reporting, your data in Google BigQuery can be integrated with various third-party tools such as Looker Studio or Google Sheets as a data source.

Yes, with these tools you can visualize data from a wide array of sources, but Google Cloud Services such as BigQuery will be natively integrated so you have extraordinary data freshness, easy reports sharing and colaboration, all of the data in one place, datasets, sql queries, and spreadsheets, making it more convenient to communicate insights with managers or clients.

Importance of Utilizing BigQuery Reports in Marketing

The importance of utilizing BigQuery reports in the context of marketing is multifaceted and particularly relevant in the data-driven landscape of modern marketing strategies. Key aspects include:

Handling of Massive Datasets: Marketing departments often deal with large volumes of data from various sources. BigQuery's capacity to efficiently process and analyze these datasets is invaluable for understanding market trends, customer behavior, and campaign performance.
Faster Decision-making: The exceptional speed offered by BigQuery in data querying and analysis enables marketing & data teams to quickly gather insights. This rapid processing is crucial for adapting marketing strategies in real time, optimizing campaigns, and staying ahead of market changes.
Scalability: With its serverless architecture, BigQuery scales, accommodating the fluctuating data needs of marketing campaigns. Whether dealing with seasonal spikes in data or growing data requirements, BigQuery ensures seamless data handling.
Cost-Effectiveness: BigQuery's pay-as-you-go model is particularly beneficial for non-technical departments, allowing them to control costs while still leveraging powerful data analysis capabilities. This makes advanced data analytics accessible even to small marketing teams with limited budgets.
Integration with Visualization Tools: The seamless integration of BigQuery with tools like Looker Studio or Google Sheets empowers marketers to create comprehensive reports and visualizations, aiding in the interpretation of complex data and facilitating data-driven decision-making.
Data Freshness: The ability of BigQuery to handle real-time data streaming is a game-changer for marketing. It enables immediate analysis of consumer interactions, allowing for prompt adjustments in marketing strategies and more effective targeting.
Data Security: We know. Privacy regulations. Adblockers. Cookie expiration time. For those who are handling sensitive customer data, BigQuery's is secure and compliant. It ensures that customer data is managed responsibly and in line with regulatory requirements.
Public Datasets : Access to BigQuery's extensive public datasets can provide additional context and insights for marketing teams, enhancing market research and competitive analysis.

BigQuery became a vital asset for marketing departments, offering advanced data management and analysis capabilities that are essential for developing effective, data-driven marketing strategies and maintaining a competitive edge in today’s rapidly evolving market.

3 Reasons to Create Reports Using Google BigQuery over GA4

Google BigQuery and Google Analytics 4 (GA4) are both powerful tools for data analysis, but they serve different purposes and offer unique advantages.

When it comes to creating in-depth and customizable reports, especially for complex data analysis, BigQuery often presents a more suitable option over GA4. Here are some reasons why creating reports using Google BigQuery can be advantageous compared to relying solely on GA4:

1. Limitations on the number of dimensions and metrics in reports

In Google Analytics 4 (GA4), you might encounter some limitations similar to those in previous versions of Google Analytics, particularly regarding the number of dimensions and metrics in reports. Some of the limitations in the dimensions are-

User-scoped Custom Dimensions: You can create up to 50 user-scoped custom dimensions per property in GA4. User-scoped custom dimensions are attributes that are associated with users and persist across multiple sessions.
Event-scoped Custom Dimensions: GA4 allows up to 50 event-scoped custom dimensions per property. These dimensions are related to specific events and provide additional context or detail about those events.
Custom Metrics: You can also define up to 50 custom metrics per property in GA4. Custom metrics allow you to measure additional quantitative values that are not captured by the default metrics in GA4.

These constraints can affect complex analytical tasks like cohort analysis for evaluating advertising performance and optimizing customer acquisition costs, or monitoring customer lifetime value (LTV) in different cohorts. Such analyses require segmenting users into cohorts and examining detailed data on hits, sessions, users, revenue, and advertising costs.

In GA4, data processing can be done within the GA4 interface or by exporting the data to third-party services using APIs. However, like its predecessors, GA4 imposes restrictions on the number of dimensions and metrics that can be used simultaneously in reports.

While GA4 is more advanced and flexible than the older Google Analytics versions, especially in terms of event-based data collection and user-centric analytics, it still has limitations in terms of the breadth of dimensions and metrics that can be combined in a single report.

For example, a report might be limited to combining 10 dimensions and 20 metrics. This means, that when you are creating a custom report, you can select up to 10 different dimensions (such as city, device category, page title, etc.) and 20 metrics (like users, new users, sessions, etc.) for your analysis.

For instance, if you want to analyze user engagement over the past month to create a targeted email campaign, you might need various user-related dimensions. But in GA4, as in earlier versions, each report or API request has a cap on the number of dimensions and metrics you can include, which can be restrictive for complex analysis needs.

This is where Google BigQuery comes into play. Unlike GA4, BigQuery does not have these constraints on dimensions and metrics. You can seamlessly integrate GA4 data into BigQuery and perform more complex analyses.

For example, if you need to segment your audience for retargeting campaigns, content creation, or website optimization, BigQuery reporting allows you to analyze extensive data points like pageviews, events, transactions, and sessions, along with a comprehensive set of user attributes such as device type, browser, city, and country, without the limitations you'd face in GA4.

With Google BigQuery, you can get all the information you need by simply listing the dimensions and metrics you want to see, in a single query.

In the examples below, the source data is a Public BigQuery dataset with GA4 data of an ecommerce store. You can use this code to query it in BigQuery interface or with BigQuery Reports Extension for Google Sheets (It’s free):

SELECT
  user_pseudo_id AS clientId,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS sessionId,
  event_date AS date,
  device.category AS deviceCategory,
  device.web_info.browser AS browser,
  device.web_info.browser_version AS browserVersion,
  geo.city AS city,
  geo.country AS country,
  traffic_source.name AS channel,
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  COUNT(*) AS hits,
  COUNTIF(event_name = 'page_view') AS pageviews,
  COUNTIF(event_name NOT IN ('page_view', 'session_start', 'first_visit')) AS events,
  COUNTIF(event_name = 'purchase' OR event_name = 'ecommerce_purchase') AS transactions,
  COUNT(DISTINCT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS visits
FROM
  `{gcp_project_id default="bigquery-public-data" type="input"}.{dataset_id default="ga4_obfuscated_sample_ecommerce" type="input"}.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'
GROUP BY
  clientId, sessionId, date, deviceCategory, browser, browserVersion, city, country, channel, source, medium

Note: You can run this query in BigQuery Reports Extension and replace GCP project details with you own GA4 data.

The query will return all the data you need:

The SQL query used above for Google BigQuery helps overcome the limitations of dimensions and metrics limit in Google Analytics 4 (GA4).

This query illustrates the extensive capabilities of BigQuery in handling complex data analysis that GA4's interface might restrict due to its limitations on dimensions and metrics.

2. Compatibility of dimensions and metrics in reports

In Google Analytics 4 (GA4), the structure and scope of dimensions and metrics have been adapted to fit its event-based data model, not the session-based data model as it was in Google Universal Analytics.

This affects how data is analyzed and the combination of dimensions and metrics in reports:

Event-Level Scope: Replacing the Hit-Level scope from Universal Analytics (UA), each user interaction with the website is tracked as an event in GA4. Examples include "page_view", "event_name", and various event parameters.
User-Level Scope: Similar to UA, this scope applies to data that is consistent across all sessions for a particular user, like demographic information ("gender", "age") and geographic data ("city").
Session-Level Scope: In GA4, the importance of sessions is reduced compared to UA. While session data is available, GA4 does not support the creation of custom dimensions with a session scope.
Item-Level Scope: Relevant for e-commerce tracking, this scope includes dimensions and metrics related to individual products, such as "item_name", "item_brand", and "item_category".

In GA4, combining dimensions and metrics in a report requires them to be compatible in terms of scope. For instance, it might not always be feasible to combine user-level dimensions with event-level metrics.

Especially in complex analytical scenarios like analyzing brand interactions across different user sessions, this limitation can cause significant challenges.

This is where BigQuery comes in and complements GA4 by addressing its limitations in handling large datasets and complex analysis.

It enables advanced SQL querying for combining various data scopes, which is typically not possible always possible in GA4 reports.

BigQuery's flexibility in data analysis allows for creating custom reports and integrating different data sources, offering a more comprehensive view of user behavior across platforms.

In our example, the Item_brand of a sold product is a dimension that is incompatible with the Users and Event metrics.

With Google BigQuery, it only takes a couple of seconds to run a simple query and see how users interacted with the brand:

SELECT
 item.item_brand,
 COUNT(DISTINCT CONCAT(CAST(user_pseudo_id AS STRING), CAST(event_timestamp AS STRING))) AS sessions,
 COUNT(DISTINCT user_pseudo_id) AS users
FROM
 `{gcp_project_id default="bigquery-public-data" type="input"}.{dataset_id default="ga4_obfuscated_sample_ecommerce" type="input"}.events_*`,
 UNNEST(items) AS item
WHERE
 item.item_brand IS NOT NULL
GROUP BY
 item_brand

Note: You can run this query in BigQuery Reports Extension and replace GCP project details with you own GA4 data.

The resulting report shows that 31,327 users have viewed ‘Google’ products in 161,719 sessions:

Use this information to achieve your goals.

For instance, you can measure the performance of your retargeting campaigns for ‘Google’ products. Or send a special offer email to users who were interested in this group of products.

BigQuery's ability to handle these complex queries quickly and efficiently makes it a powerful tool for analyzing specific data queries and patterns in customer behavior, especially for e-commerce and marketing insights.

And if you don’t want to login to BigQuery, just run the query from Google Sheets and get this data quickly visualized, so you can play around with it (or you need somebody else to get a dashboard in Google Sheets) - start building reports with OWOX BI BigQuery Reports Extension:

The use of BigQuery for such deep dives into user interaction with specific brands demonstrates the advantage of integrating Google Analytics data with BigQuery, especially for businesses looking to glean actionable insights from their online traffic and user interactions.

3. Data Sampling

In Google Analytics 4 (GA4), data sampling is engineered to manage larger datasets faster. Google says that GA4 data processing capabilities are less reliant on sampling compared to UA...

However, It's important to be aware of these limitations. Sampling involves analyzing a subset of data to extrapolate overall trends. While this is efficient, it can sometimes lead to less accurate results, especially in complex analyses over long periods or with numerous dimensions and metrics.

To bypass the potential inaccuracies of sampling, integrating GA4 data with Google BigQuery is the best solution. BigQuery allows for the analysis of unsampled, raw data. This is especially valuable if you really have a lot of data.

With BigQuery, processing large volumes of data, whether it's daily traffic or years' worth of data, is efficient and quick. So if you look for a deep, granular analysis of your web analytics data - BigQuery is your choice.

Also, utilizing OWOX BI Streaming or Google BigQuery Export for GA4 enables you to get the most out of the user behavior analysis.

What are the Key Elements of an Actionable BigQuery Report?

Here are the key elements of a Bigquery Report.

An actionable BigQuery report is characterized by its ability to provide clear, concise, and relevant data that can be used to drive decision-making.

First key element is the use of well-defined metrics. These are quantifiable measures used to track and assess the status of a specific business process. They should be carefully selected and relevant to the business objectives.

Another important element is the use of filters. Filters allow users to narrow down the data set to focus on specific subsets of data. This can help in identifying trends, patterns, or anomalies that might be lost in the larger data set. It's important to use filters that are relevant and meaningful to the data being analyzed.

Data visualization is also a critical element. Visuals such as charts, graphs, and tables can help users to understand the data more easily. They can highlight trends, patterns, and outliers in a way that raw data cannot. The choice of visualization should depend on the nature of the data and the message that needs to be conveyed.

An actionable BigQuery report should also have a clear layout. The information should be presented in a logical order, with the most important data highlighted. The layout should make it easy for users to navigate the report and find the information they need.

Finally, an actionable report should have a clear call to action. This is a statement that tells the user what action they should take based on the data. The call to action should be directly related to the data and the business objectives.

Examples of BigQuery Reports

Google Analytics 4 (GA4) offers robust analytics capabilities, but there are instances where its functionality may not suffice, particularly for creating complex, non-aggregated reports. This is where Google BigQuery comes into play, offering the flexibility and depth needed for advanced analysis. Let’s consider a practical example to illustrate this point.

Suppose you want to analyze user paths on your website – the sequence of pages visited – and identify the most common paths. GA4 provides insights into entry or exit pages for a specific URL, but it doesn't offer a comprehensive view of the entire user journey through your website. In contrast, Google BigQuery allows for a more detailed analysis. You can select a base URL and view both the previous and the next URLs for that page, offering a holistic view of user navigation.

The URL sequence report can be easily created by querying the data in Google BigQuery:

WITH page_views AS (
 SELECT
   user_pseudo_id,
   event_timestamp,
   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS URL
 FROM
   `{gcp_project_id default="bigquery-public-data" type="input"}.{dataset_id default="ga4_obfuscated_sample_ecommerce" type="input"}.events_*`
 WHERE
   event_name = 'page_view'
),
ranked_page_views AS (
 SELECT
   user_pseudo_id,
   URL,
   LAG(URL) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS PreviousURL,
   LEAD(URL) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS NextURL
 FROM
   page_views
)
SELECT
 PreviousURL,
 URL AS CurrentURL,
 NextURL,
 COUNT(*) AS TransitionCount
FROM
 ranked_page_views
WHERE
 PreviousURL IS NOT NULL OR NextURL IS NOT NULL
GROUP BY
 PreviousURL, CurrentURL, NextURL
ORDER BY
 TransitionCount DESC
LIMIT 100

Note: You can run this query in BigQuery Reports Extension and replace GCP project details with you own GA4 data.

As a result, instead of disparate data that you would have to combine manually, you will receive a ready-to-use report:

The results of the query can be easily visualized using Google Sheets or Looker Studio

The example above shows that 5,613 transactions happened with the path of /basket -> /yourinfo -> mypayment pages.

The resulting analysis can reveal, for instance, that users frequently navigate from one specific product page to another. This information is invaluable for optimizing website UX and design, such as adjusting the placement of product recommendations, to enhance the customer experience.

Google BigQuery makes it possible to create reports of virtually any complexity. For example, cohort analysis reports with all the indicators your business needs to see. A case in point would be the success story of Contentmart , who used cohort analysis with Google BigQuery to improve the LTV and broaden their customer base.

Steps for Creating a Report in Google BigQuery

Creating a Google BigQuery report can be streamlined into three easy steps, especially when you use a tool like OWOX BI to manage and visualize your data. Here’s how you can do it:

1. Connect Your Data Sources to BigQuery:

First, identify the data sources you want to use for your report, such as Google Ads, TikTok Ads, facebook, Instagram, etc.
Instead of manually copying data, use OWOX BI to connect your platforms once. This ensures that your data is automatically filled and updated each time you need to create a report.
OWOX BI allows you to consolidate data from multiple marketing platforms in one place, eliminating the need to piece together data from various sources.

2. Visualize the Data in a Report:

While BigQuery is excellent for analyzing large datasets using SQL, presenting this data in an easily digestible format requires visualization.
Use our simple extension to Google Sheets to query data from your BigQuery and visualize it in the convenient interface of a spreadsheet:

To create a new report in Google Sheets just install our extension:

Begin by selecting "Extensions" in the toolbar, then choose "OWOX BI BigQuery Reports"
Click "Add a new report".
Configure your report in the sidebar of your Google Sheets.

Note that each new report is created in a new sheet.

First, select an existing Google Cloud Platform project.
Then, choose a query that will provide data for your report. This can be done by either adding a new query or editing an existing one.
OWOX BI offers a convenient query editor that supports features like syntax highlighting, auto-suggestions, versioning, query verification, and data processed volume estimation.
If your selected SQL query contains dynamic parameters, you can modify them as needed.
Finally, click the 'Add & Run' button. This action will load your Google BigQuery data into the spreadsheet, ready for analysis and reporting.

Customize your report by dragging and dropping dimensions and metrics from your dataset.
Utilize the filter function to refine your dataset and adjust details like widget placement, design elements, and branding.

By following these steps, you can efficiently create comprehensive and visually appealing reports using Google BigQuery and OWOX BI, turning complex data sets into actionable insights.

Key Takeaways

Google BigQuery is a powerful tool, an indispensable helper for marketers and analysts who need more than Google Analytics 4 functionality. It helps overcome such challenges as the limit on the number of dimensions and metrics in reports, data sampling and data aggregation.

Moreover, you can use it to create more complex and informative reports. There’s no reason not to give it a try, even more so because there’s a free Trial with $300 processing costs free of charge :)