Connecting GPT to BigQuery: Zapier AI actions

Unless you have lived under a rock for the last 12 months, you have probably heard of a little old company called OpenAI. In my humble opinion, they essentially kicked off another industrial revolution with their mind-blowing GPT 3.5 and even MORE impressive GPT-4. Since then, many of us in marketing analytics and data engineering have explored its utility for our day-to-day. Some exciting tools have spun up in that time, not least of which Sphinx mind, ‘your AI marketing assistant’ which allows you to connect to a number of data sources (like GA4 and BigQuery) and talk to them in natural language, helping to analyse and visualise your data. All of this tech is built on the GPT 3.5 and 4 models, and with the seismic release of GP4-turbo and GPTs, it is now possible, with minimal code/no code, to replicate some of this functionality in your own GPT.

So that is our aim today: set up a GPT that can retrieve some data from BigQuery and summarise what we have grabbed. We will be using GPT-4, GPTs and Zapier AI actions. Remember that this will require a GPT plus subscription and a free Zapier account.

Let’s get started!

Step one: Zapier set-up

Firstly, you will need to sign up for Zapier. Zapier is a no-code way of linking together various services from around the internet. It can handle all the permissions you may need, making it ideal for augmenting GPTs with powerful actions and automations.

Once you have a Zapier account, visit the following URL: here, you will see a request to authorise Open AI access to your Zapier account. You will end up doing this twice; one approval allows you to test things in the preview of the GPT, and the other approval allows for use when live. Click Allow. This will take you off to log into your Chat-GPT account.

Image of Zapier AI automation approval screen for connection with OpenAI

Step two: Building out our GPT

Now that we have Zapier up and running let’s head over to to start building out the GPT. To build a GPT, click ‘Explore’ on the left-hand side, then click ‘Create a GPT’ at the top of the screen.

Chat GPT "create a GPT" button shown within the 'My GPTs' menu

This will bring you to a new interface like the one below. On the left is all of your set-up and configuration, and on the right is the preview of your GPT.

Interface with a GPT Builder prompt and options to create or configure a GPT, and a preview area on the right.

As you can see, GPT asks us what we want to make. In this demo, I am going to create a connection to BigQuery, so I am going to add the following prompt.

The image is a screenshot of a chat conversation between a user and a GPT Builder interface. The GPT Builder offers to help create a new GPT (Generative Pre-trained Transformer) and suggests that the user could request a creative who helps generate visuals for new products or a software engineer who helps format code. The user responds with a desire to create a BigQuery assistant, which would construct SQL queries based on user requests and run them in BigQuery using Zapier AI actions. The GPT Builder acknowledges this as an innovative and useful GPT and suggests the name "BigQuery Helper" for it. The user agrees that the name sounds great, and the last message from the GPT Builder shows that it is generating a profile picture. There are buttons for "Create" and "Configure" at the top of the interface, indicating functionality to either start creating a new GPT or to configure existing parameters.

The whole building process is based on natural language prompts, with no need for code. Plenty of experimentation is needed to get the GPT to behave exactly as is needed, but this is a good start. It can be gratifying to play with different prompts and see improvements in the GPT’s behaviour. A wealth of resources are springing up on this exact subject.

Once you have given it its initial instructions, It will ask you about the logo, which I will skip over in this guide.

Next, it will want your help with refining things. We need to provide it with a very specific set of instructions. The below is a version of the instructions seen on Zapier demo page, but we have swapped out the confirmation links to BigQuery instead of the calendar demos. Also note you will need to add in the name of your own GPT in the fist line. Here is the instruction to feed into your model:

BigQuery helper will adhere to the following rules:

### Rules:
– Before running any Actions, tell the user that they need to reply after the Action completes to continue.
– You will ask them for the Bigquery project id, dataset and table to use in the query you create
– You will warn them about the risks of blindly querying tables in BigQuery
– You do not need the user to confirm the query on Zapier

### Instructions for Zapier Custom Action:
Step 1. Tell the user you are Checking they have the Zapier AI Actions needed to complete their request by calling /list_available_actions/ to make a list: AVAILABLE ACTIONS. Given the output, check if the REQUIRED_ACTION needed is in the AVAILABLE ACTIONS and continue to step 4 if it is. If not, continue to step 2.
Step 2. If a required Action(s) is not available, send the user the Required Action(s)’s configuration link. Tell them to let you know when they’ve enabled the Zapier AI Action.
Step 3. If a user confirms they’ve configured the Required Action, continue on to step 4 with their original ask.
Step 4. Using the available_action_id (returned as the `id` field within the `results` array in the JSON response from /list_available_actions). Fill in the strings needed for the run_action operation. Use the user’s request to fill in the instructions and any other fields as needed.

– Action: Google BigQuery: Run a Query
Confirmation Link: query&setup_params=set%20have%20AI%20guess%20for%20projectid%20and%20sql%20query

Once we have the above in the model, we need to ensure the ability to pull Zapier actions is in the GPT. To do this, click ‘Configure’ on the top of the page, scroll down and then click ‘Create new action’ under the Actions heading.

A screenshot of the GPT interface detailing assistance in creating and running BigQuery SQL queries, with instructions and capabilities.

Here, you will see a Schema box. We need to import the specific schema for Zaper AI actions. Luckily, we can do this using a URL, which makes things much simpler. Click Import from URL, then use the following: 

Once imported, a JSON script appears in the schema box. We do not need to make any changes to this. You will also see the schema has added 2 actions:
1. list_available _actions – This will allow the GPT to see what the user already has set up in Zapier actions

2. run_action  – This allows the GPT to run action within your Zapier AI actions account

 We have already provided the GPT with instructions on using these, so there is nothing more to do on this page. 

Please note that you must provide a privacy policy URL if you want to share this GPT publicly or with your team. In our case, as we plan on only having this demo available to us, there is no need.

Screenshot of an API schema for Zapier AI Actions for GPT with fields for API version, title, and descriptions of available actions.

In the top right of the screen, we will save the GPT and select ‘Only me’. This will close the editing window and bring you into your new BigQuery GPT interface. Let’s try it out!

Step three: Testing

As you can see below, I have asked the GPT to help me with a SQL query, and it has asked me to sign into Zapier. I can click sign in and then ask again once that is done.

Chat interface showing a user asking for help to create a BigQuery SQL query and a tool offering a sign-in link for

Next, it is asking me to set up the automation it needs. Here is where we see a common issue. It has not used the confirmation URL we gave earlier to auto-create the connection. Instead, it is asking us to create it manually. For this demo, that is precisely what we will do, but you can try and refine your version to ensure it asks the right questions. There is also a chance that your version worked as expected; this is the joy/pain of working with these models; the same inputs can have slightly different outputs. If the GPT makes you start over, select ‘BigQuery Run a Query’ from the list. We want the model to use the Project ID and SQL query from our conversations, so select ‘Have AI guess’ for both of those fields. Leave ‘require preview’ unselected and then click ‘enable action’.

A chat where a user requests SQL query help and BigQuery Helper prompts to configure a Zapier AI Action.
Configuration panel for setting up a GPT action to run a SQL query in Google BigQuery with fields for account and project ID.

Now head back to GPT and let it know you have enabled the action.

A chat interface where a user confirms completing setup and BigQuery Helper requests project ID, dataset, table, and query goal details for SQL query assistance.

Brilliant, it asks us what project we want to query, etc., just as instructed in our rule set! Let’s try and see if it can query what tables are in a dataset.

Chat interface showing a SQL query to list all tables in a specified BigQuery dataset, with the user instructing to proceed.

Would you look at that? It knows the syntax to grab table information and has even drawn up the query for us to check before processing! That looks to me; let’s run it.

A chat bot provides a summary of table names from a BigQuery dataset execution, offering further assistance.

It ran the query and returned all the dataset tables to me. If I wished, I could ask it to run queries on any of those tables, but let’s leave it there for this demo.

Wrapping up

This was a speedy and straightforward demo connecting BigQuery to chatGPT using Zapier AI actions. You can play with the prompts and instructions to refine it, getting it to behave exactly as needed for your use case. For example, right now, this only pulls through samples of larger results sets; you may want to work on getting all of the data from your query results into your GPT to kick off an analysis stage. This is also only using one action! Imagine having email and PDF generation also available; you could be running queries, collecting data, analysing and then sending results to people via email, all within the one GPT. All of this is possible with the correct set of actions and prompts.Remember to be cautious with running queries in BIgQuery. The same best practice rules apply as if you were using the UI. Not thinking about this could lead to expensive queries!

If you have any questions about using AI in marketing analytics, BigQuery or anything related, please reach out.

Written by

Matthew is the Engineering Lead at Measurelab and loves solving complex problems with code, cloud technology and data. Outside of analytics, he enjoys playing computer games, woodworking and spending time with his young family.

Subscribe to our newsletter: