Last Updated: March 3, 2025

What is GA4 BigQuery Composer?

GA4 BigQuery Composer is a ChatGPT-4-turbo powered assistant for creating efficient, precise SQL queries for GA4 BigQuery within seconds.

What GA4 BigQuery Composer can do that other tools can not do?

#1 You can write SQL queries for Google BigQuery even when you don’t understand a single line of code.

The Composer will automatically convert your instructions in plain English into SQL code.

For example:

#2 Specially trained on GA4 BigQuery data sets.

Unlike the regular chatgpt, the Composer is specially trained on GA4 BigQuery data sets. So, its results are likely to be much more accurate.

#3 The Composer can generate even complex SQL queries within seconds.

The days of manually writing SQL code are over.

#4 You can troubleshoot SQL even when you don’t understand a single line of code. 

If you get an error message while using the generated SQL in the query editor, copy-paste the error message into the Composer tool.

GA4 BigQuery Composer will re-write the SQL for you free of charge.

For more details on troubleshooting, check out this article: 3 Methods to Fix ChatGPT’s SQL Errors for GA4 BigQuery.

#5 Get the desired SQL within seconds.

If you are unsatisfied with the result format (maybe you want to rename a particular column or add a new field or sort data by a particular column), it will re-write the SQL for you within seconds.

#6 It can learn from you.

The more you use the composer tool (without starting a new session), the more accurate and efficient it will get for you.

#7 The GA4 BigQuery Composer is trained to optimize SQL queries for speed and performance.

GA4 BigQuery Composer uses machine learning to identify the most efficient ways to query GA4 data.

This includes things like using the right indexes, avoiding unnecessary joins, and choosing the most appropriate data types.

GA4 BigQuery Composer generates queries that are tailored to the specific data that you are trying to query.

This means that the Composer will not generate any unnecessary code, and it will only include the fields that you need.

#8 It drastically reduces your chances of making errors.

Since the GA4 BigQuery Composer guides the entire query building process and understands the GA4 BigQuery Export schema, it drastically reduces the chances of making errors in query syntax or structure.

This is particularly beneficial for users who are not confident in their SQL skills.

#9 It saves tons of your time.

By automating the query-building process, the GA4 BigQuery Composer saves tons of time for its users.

Users can quickly generate complex SQL queries within seconds, which is especially helpful for repetitive or routine data analysis tasks. The days of saving queries are over. 

How does GA4 BigQuery Composer work?

It begins an interaction by asking for the user’s table ID.

When creating SQL queries, the Composer refers to specific documentation: GA4 BigQuery Export schema, GA4 BigQuery Export user-data schema, and BigQuery query syntax.

For GA4 and BigQuery data comparisons, it consults relevant GA4 documentation.

Before creating calculated fields, it ensures they don’t exist in the GA4 BigQuery schema to avoid duplication.

The Composer generates SQL code only for the fields specifically requested by the user and focuses on efficiency and performance.

Responses are concise and focus on the SQL code without additional explanatory documentation.

How to use GA4 BigQuery Composer?

Keep the following points in mind before using the Composer tool:

#1 Be as specific as possible when writing text prompts. You will get vague output if you write vague prompts (like “calculate the total number of users”).

#2 If you get an error message, copy-paste it into the Composer tool. It will rewrite the SQL for you.

#3 If you are unsatisfied with the result format (for example, you may want to rename a particular column), ask the composer to rewrite the SQL with the desired modification.

#4 The more you use the composer tool, the more accurate and efficient it will get for you. Just avoid starting a new session.

#5 It is very likely that when you compare GA4 BigQuery data with the data from UI or data API, they will not exactly match, but they should be reasonably close.

Note: Sometimes you see might additional formatting at the end of the generated SQL code, which looks like this: “` ​“【oaicite:0】“​

Ask the GA4 BigQuery Composer to rewrite the SQL without additional formatting.

How to use GA4 BigQuery Composer?

Follow the steps below to use GA4 BigQuery Composer:

Step-1: Make sure that your GA4 property is linked to your BigQuery project.

Step-2: Make sure that your BigQuery project has collected at least a couple of days of GA4 data.

Step-3: Subscribe to one of the paid versions of ChatGPT. You can also use the free version of ChatGTP to access a custom GPT. But I highly recommend using a paid version (‘Plus’, ‘Pro’ or ‘Team’).

Paid users generally experience faster response times and have priority access during peak hours. They can access the more powerful GPT-4 model.

Step-4: Login to your ChatGPT account.

Step-5: Click on this link to directly navigate to GA4 BigQuery Composer.

You should now see a screen like the one below:

Step-6: Let’s query all the columns of the data table meant for Dec 21, 2024.

You can type the following text prompt in chatGPT:

Write SQL which meets the following requirements:

1) Show data for Dec 21, 2024

2) Shows all the columns of the data table.

3) Give the example of expected query result.

My table ID is: dbrt-ga4.analytics_207472454.events_20241221

Step-7: Click on the arrow button to submit your prompt:

You can find your table ID from the events_ data table:

Step-8: Click on the ‘Copy Code‘ button to copy the generated SQL code:

Step-10: Navigate to your events_ data table in your GA4 BigQuery project, compose a new query, paste the copied query there and then click on the ‘Run’ button:

You should now see the query results in the window below the query editor:

That’s how you can use the GA4 BigQuery Composer.

Calling GA4 BigQuery Composer directly into any ChatGPT session.

You can call GA4 BigQuery Composer directly into any ChatGPT session by using @ instead of first navigating to the GPT or the GPT Store.

When you mention the GPT, you see the message ‘Talking to GA4 BigQuery Composer’.

What is the difference between GA4 BigQuery Composer and ChatGPT?

The GA4 BigQuery Composer and ChatGPT are fundamentally different tools designed for distinct purposes and operate on different principles.

Here’s an overview of the key differences:

#1 Purpose and Functionality.

The GA4 BigQuery Composer tool is specifically designed to work with GA4 data within Google’s BigQuery environment. 

It assists users in composing and running SQL queries, making it easier to analyze and extract insights from their GA4 data. 

The Composer understands the structure and nuances of the GA4 data schema in BigQuery and aids in formulating accurate and efficient queries.

ChatGPT does not specialize in any specific dataset or database structure like GA4 BigQuery Composer.

#2 Data Access and Handling.

GA4 BigQuery Composer directly interacts with the data in Google’s BigQuery, particularly the GA4 datasets. 

It can read and understand the structure of these datasets, enabling it to assist in creating queries specific to the user’s data.

ChatGPT does not have direct access to external databases or the ability to interact with specific datasets. 

#3 Use Cases.

GA4 BigQuery Composer is primarily used by data analysts and marketers who need to extract and analyze data from GA4 within BigQuery. 

It simplifies querying large datasets and helps generate insights from analytics data.

ChatGPT has a broad range of applications. It’s more versatile in its usage but not specialized in data analytics.

In summary, while GA4 BigQuery Composer is a specialized tool for querying and analyzing GA4 data within BigQuery, ChatGPT is a general-purpose conversational AI model with broad capabilities but without specialization in specific datasets or analytics platforms.

How to use GA4 BigQuery Composer like a Pro

To use GA4 BigQuery Composer like a pro, first, get familiar with the BigQuery User Interface and GA4BigQuery export schema.

This knowledge will help you greatly in writing better prompts.

12 lessons learned from automating SQL generation

I have been automating SQL generation for GA4 data in BigQuery for over a year now. Following are the 12 lessons I learned about prompt engineering during this time:

1. AI does not inherently “know” what you want.
2. AI is only as good as your input.
3. Effective prompt engineering goes beyond basic instructions.
4. A well-designed prompt acts as a programming language.
5. Not every user can use AI tools to develop code at a professional level.
6. Prompt engineering is a “serious skill”.
7. It’s no longer about creating the correct SQL/code.
8. SQL Logic requires human expertise.
9. AI can make Logical Assumptions while generating SQL.
10. You need excellent “creative writing” skills to thrive in the world of prompt engineering.
11. Prompt Engineering is an iterative process.
12. You need Prompt Library and Prompt Management Tool.

To learn more about my lessons, check out this resource: AI Prompt Engineering For SQL Generation – 12 Lessons I Learned.

FAQ: Should I use Google Gemini in BigQuery?

Google Gemini struggles to produce accurate SQL queries for GA4 BigQuery, often requiring manual fixes.

It seems to lack a proper understanding of both the Google Cloud environment and the GA4 BigQuery export schema, a surprising shortcoming.

For more details, check out this article: Gemini in BigQuery? – The Top AI Tools for Code Generation.

Another article you will find interesting: I Invented DSL based prompt engineering for SQL Generation.

GA4 BigQuery Composer Use Cases

1. Retrieving all the columns of GA4 BigQuery data table.
2. Retrieving particular columns of GA4 BigQuery data table.
3. Retrieving particular rows of GA4 BigQuery data table.
4. Sort columns of GA4 BigQuery data table in descending order.
5. Sort columns of GA4 BigQuery data table in ascending order.
6. How to access a nested field in GA4 BigQuery data table.
7. How to query data from multiple GA4 BigQuery data tables.
8. How to format the values of a field in GA4 BigQuery data table.
9. Calculating New vs Returning Users in GA4 BigQuery Data Table.
10. How to track GA4 BigQuery Schema Change.