Master BigQuery Reporting: Essential SQL Templates for GA4

If you’re not new to our blog, you probably know that we recommend Google BigQuery as a cloud data warehouse for building an end-to-end marketing analytics system. BigQuery allows you to build reports of any complexity and structure based on complete data and tackle tasks that are difficult to handle even with the help of paid GA4 360.

In this post, we’re sharing report templates that you can build using OWOX BI BigQuery Reports Extension for Google Sheets.

First, you’ll find out what you can calculate with the standard GA4 BigQuery Export.

Next, you’ll discover what unique metrics can be added to the standard GA4 metrics with OWOX BI Streaming.

Finally, you’ll get a chance to download a PDF with all of the query templates. For that, simply enter your email address, and you’ll get the all of the queries you need to get started with GA4 data in BigQuery.

Note: This post was originally published in July 2018 and was completely updated in April 2024 for accuracy and comprehensiveness on marketing analytics.

Google BigQuery stands as a scalable and serverless data warehouse within Google Cloud, well-suited for the efficient processing of extensive data analytics tasks. It excels in handling massive datasets quickly and integrates seamlessly with various data visualization tools.

BigQuery supports standard SQL queries and is particularly useful for dynamic workloads, offering real-time analytics and machine learning capabilities.

For users of GA4, BigQuery is the best choice for storing, querying, and analyzing the extensive data provided by GA4, enabling sophisticated and customized reporting (And you don’t have to pay for integration, just for data processing)

Integrating Google Analytics 4 (GA4) with Google BigQuery and utilizing advanced SQL query in Google Analytics templates can significantly enhance your reporting and analytics capabilities.

Here's how this integration, combined with sophisticated SQL templates, can elevate your data analysis:

• Advanced User Behavior Analysis: Custom SQL templates can dissect complex user behaviors, track user journeys across multiple sessions, and provide insights into user retention and churn. This goes beyond the basic analysis available in Google Analytics 4.
• Customized Conversion Path Analysis: With SQL queries, you can create detailed reports on conversion paths, understanding the sequence of user interactions that lead to conversions, which is crucial for optimizing marketing strategies.
• Cohort Analysis and Segmentation: SQL templates allow for intricate cohort analyses, helping you understand how different groups of users behave over time. This can be pivotal in tailoring marketing efforts to specific segments.
• Enhanced E-commerce Analytics: For e-commerce sites, custom SQL queries can delve into product performance, shopping behavior, and sales trends, offering insights that are not readily available in standard Google Analytics 4 reports.
• Cross-Platform and Cross-Device Reporting: With BigQuery, you can merge Google Analytics 4 data with data from other platforms and devices, creating comprehensive reports that reflect a unified view of user interactions across the digital ecosystem.
• Event-Level Analysis: SQL templates enable detailed analysis of event data captured in Google Analytics 4, allowing for a granular understanding of user actions and preferences.
• Custom Funnel Analysis: Unlike the predefined funnels in Google Analytics 4, SQL queries can create customized funnels to analyze specific user paths and drop-off points, which is essential for optimizing user flow and increasing conversions.
• Time Series Analysis and Trend Forecasting: Utilize SQL queries for time series analysis to identify trends and forecast future behaviors or sales, aiding in strategic planning and decision-making.
• Attribution Modeling: Advanced SQL templates can be used to develop custom attribution models, providing a more nuanced understanding of how different marketing channels contribute to conversions.

• Anomaly Detection and Alerting: By analyzing historical data trends, SQL queries can help in detecting anomalies in traffic or user behavior, triggering alerts for further investigation.
• Performance Benchmarking: Compare performance across different time periods, geographies, or product categories, using SQL queries to benchmark and identify areas for improvement.
• Data Quality Monitoring: Consistently execute SQL queries to oversee and uphold the quality and consistency of the data gathered in Google Analytics 4. This practice is essential for ensuring dependable analysis results.

Harnessing the capabilities of Google Analytics 4 alongside Google BigQuery, and employing advanced SQL query templates, organizations can attain a personalized, profoundly insightful, and actionable level of reporting and analysis, facilitating strategic decision-making.

• Standard SQL Dialect: BigQuery uses a dialect of SQL that is ANSI-compliant, which means it adheres to the SQL standard used by most relational databases. This makes transitioning to BigQuery easier for those already familiar with SQL.
• Scalability: BigQuery automatically scales to handle massive datasets and high query loads, allowing users to run complex analytical queries.
• Data Analysis and Aggregation: BigQuery excels in running analytical queries, which often involve aggregating large amounts of data (using GROUP BY, HAVING, etc.).
• Integration with Google Ecosystem: BigQuery integrates seamlessly with other Google Cloud services, enhancing its capabilities for data processing and machine learning.

Google Analytics Universal reports were user-friendly and convenient. However GA4 is different, so you may face sampling, data cardinality, and other limitations.

Heads up! There’s a way out: using the standard data export to Google BigQuery to circumvent restrictions and build more comprehensive reports via SQL requests.

Say you’ve implemented new metrics or updated the existing ones on your website to measure KPIs important to your business. You can use the report on events number changes on your website to see if the data is sent correctly and to react timely to any analytics errors. You’ll need the following dimensions for this report type:

• device.deviceCategory.
• device.browser.
• eventCategory.
• eventAction.
• Content Grouping.

A regular Google Analytics 4 report wouldn’t provide you with all the aforementioned information, as it allows you to simultaneously choose 2 dimensions only, while a custom report suggests a little more — 5 dimensions.

SQL queries don’t have such restrictions, engaging you to pull all metrics and dimensions you’d like in a report.

The SQL query template we’re providing in the PDF file will help you find out how sessions, users, and events are distributed among browsers, devices, and event types. If needed, you can add any more dimensions to the query. For instance:

• device.operatingSystem.
• device.mobileDeviceInfo.
• device.language.
• geoNetwork.region.

You can also import the information from your CRM or ERP systems to Google BigQuery.

Thus, you’ll be able to analyze user behavior across any metrics required: product catalog, margin, product category, user description, order completion, etc.

For example, you send a query to get data on transactions, then combine them with the paid-off orders from CRM and calculate the percentage of online completed orders. This will help you understand if there are any issues on the way from placing an order to payment or delivery.

If you want to build up user segments and set up personalized newsletters, you’ll need info on online user behavior. The more details, the more options for segmentation.

Google Analytics 4 won’t provide you with a report on all user actions like pageview, event, social, and timing, as well as Enhanced Ecommerce events (click, detail, add, remove, checkout, purchase, refund), across event types.

You can’t view the average, maximum or minimum values across event types for a certain user.

However, all of that information will be available thanks to one of the SQL queries you’ll find in the PDF file attached to this article.

The aforementioned query will also help you calculate the following average, maximum, and minimum values for each user:

• Page views according to a certain search query.
• Page views per visit.
• The number of times products are added to the cart.
• The number of times products are removed from the cart.
• Number of products added to cart.
• The number of products removed from the cart.
• The total price of products added to the cart.
• The total price of products removed from the cart.

Using the very same SQL query, you can also calculate these values for each session:

• Page views according to a certain search query.
• Page views per session.
• The number of times adding to / removing products from the cart.
• The number of products added to / removed from the cart.
• The total price of products added to / removed from the cart.

With such information at hand, you can predict repeated purchases and micro-conversions.

To refine your sales funnel, you need to give proper credit to what users do on your website before making a purchase.

The «User Explorer» report in GA4 which can be customised from Explore → Template Gallery → User explorer and it will provide you with the info on all types of user actions (pageview, event, social, timing). However, it may be complicated and time-consuming, as you’ll get to see this kind of statistics per a single user only, applying advanced segments and product ID filters for that purpose.

However, building up a report based on the standard export from GA4 to BigQuery will provide you with data about all user actions on your website, tracked via the GA4 code.

For example, the SQL query we talked about earlier will help you group users that viewed some product pages. Thus, you’ll get to remind the users, who left the items in the cart without paying, of the products they were interested in, as well as recommend other products, and even develop customer profiling.

All the aforementioned things in the previous paragraph apply to this report as well. Using the SQL query from our PDF file, you’ll get to build a report that includes the names of users who bought a definite item. These names will allow you to offer users similar products or accessories to the products already bought.

Say you’ve posted an article in your blog to motivate your customers to subscribe to a newsletter. To track such a conversion, you’ll need both events: reading the article and the subscription. The thing is that you can’t combine these two in Google Analytics 4, as they are two different event types — regular and conversion.

Exporting data to Google BigQuery and a simple SQL query will help you circumvent the aforementioned restrictions. You can set up a funnel with any steps necessary to find any narrow gauges on the website and learn at what stages users drop off the most.

You’ll also be able to discover how often users view the product description and how such views affect conversion. In our example, the funnel looks this way:

1. Product page view → 2. Product description view → 3. Adding the product to the cart. However, you can set up any user action as a funnel step to track on the website. As a result, you’ll get a similar graph:

You can increase conversion rates by improving the inner search on the website. For example, analyze the percentage of zero search results, update the website content, add similar products, special offers, etc. Another option is to switch the category of the top queried products to a higher position, using the report on search queries.

Dimensions and indicators in the GA4 report need to be of the same level: session-level, user-level, event-level scope. This means that in a single report with dimensions, you can’t view, for instance, the product SKU or search query rank across session ID.

The template will help evaluate the performance of the sessions with search queries and to check hypotheses on website usability.

The SQL query will also provide you with this data:

• Number of sessions with a certain query.
• Average number of search results according to the search query.
• Total revenue across sessions with a definite search query.
• The rank of the search query across sessions.
• The rank of the search query across search results.

If you want to enrich your GA4 data with the additional information needed for assessing your business performance, OWOX BI will help and provide you with the necessary raw data as well as data transformations to do that.

The thing is that Google Analytics 4 allows importing only low-granular cost data across ad campaigns (not including keyword level)

Using OWOX BI, you can collect all of the data from all of the ad platforms right into Google BigQuery, blend them together with no-code templates with just a few clicks, and even attribute ad / marketing costs to the sessions (all with ready-to-use no or low-code customizable templates).

This will allow you to group your expenses and revenue at the user level, segment or cohort level, and even at the landing page level.

Below, we provide some of the reports examples that you can get with GA4 BigQuery Export & OWOX BI:

The report will give you information on costs for a certain period, as well as on metrics changes within a set time. You’ll be able to compare the information you obtained with the previous period and evaluate the performance of your ad efforts across sources.

Here’s a report example:

From the chart above, you can see that there was a sharp decrease in the facebook/cpm costs on June 10th. This could be a sign for a marketing specialist to check which campaigns, ad groups, and keywords for a definite source started driving fewer clicks.

Probably, the number of ad views decreased due to the competitors’ ads. To handle this issue, the marketing expert can adjust the ads, update creatives, and so on.

As mentioned before, Google Analytics 4 and the standard export to BigQuery won’t allow you to calculate your costs per session, user, and cohort. However, OWOX BI will surely let you do that.

With the help of the report below, you can learn about the average customer acquisition cost across cities and time periods. Check out how it looks:

Usually, ad campaigns have different settings for each of the regions. The aforementioned report will let you know which region to pay attention to.

For example, from the chart above, you can see that customer acquisition cost (CAC) for Dallas was the highest. This means that you should probably take a closer look at ad campaigns in this region and calculate the average revenue per user, CAC across ad campaigns, comparing it to the customer lifetime value (LTV).

In case ad campaigns don’t pay off, you can adjust bids or simply deactivate some of the ads.

Such a report can help you evaluate the performance of your ads and see the campaign contribution with the account of order completion rates and product prime cost from CRM.

To obtain this report, you’ll need to first import the data about orders from your CRM or ERP systems to Google BigQuery.

Below, you can see how the report might look like:

From the chart, you can see how campaigns perform, sorting from the best to the worst ones. Marketers will be able to give proper credit to each campaign, get to know why some of them succeed or fail, and choose the most efficient channels for future marketing efforts.

With this report at hand, you’ll get to find any issues with placing an order or payment options.

To build up the report, you’ll have to upload the CRM data about completed orders to Google BigQuery.

Next, you’ll be able to use our SQL template to finally get the following report:

This bubble chart shows that users pay by credit card (online card) three or two times four when choosing a customer pick-up or post office pick-up delivery option. However, customers can also pay by card for the courier delivery option. If this alternative is not used, you’ve probably got some issues with it on your website.

For this very report, you’ll also need data about completed orders from your CRM system. The report will allow you to check if there are any delivery problems in certain cities.

For example, you can see that delivery in London takes the most time.

If the average delivery time is more than stated on your website, you’ll possibly need to check if there’s a local offline store or a warehouse in that region.

It could be a good idea to find one, if not in there yet, as it will decrease the delivery time. Another good idea would be to check how satisfied your customers are in this region.

The standard data export to Google BigQuery and SQL queries, along with the OWOX BI tools, can come in really handy when overcoming the default Google Analytics limitations and creating reports for in-depth analysis.

For example, you can:

• Analyze user behavior across any number of parameters.
• Get statistics on key user actions, as well as average, maximum, and minimum values for event types per user.
• Group users according to certain pages they visited or see the user behavior of people who bought a certain product.
• Set up a funnel with all the steps necessary.
• Evaluate the performance of inner website search.

OWOX BI will enrich your collect you data into Google BigQuery, as well as help you see:

• What were the changes in the attributed costs across sources and mediums by days?
• How does the average customer acquisition cost depend on the city?
• How gross margin ROAS depends on the campaign source and medium.
• How the order number in the CRM system depends on the payment and delivery options.
• How does the average delivery time depend on the city?

For greater convenience, we’ve combined all of the aforementioned SQL queries in a PDF file.

Simply fill in a short form to get it right now.