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: https://actions.zapier.com/gpt/actions/ 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.

Step two: Building out our GPT

Now that we have Zapier up and running let’s head over to https://chat.openai.com/ 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.

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.

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 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:

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.

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: https://actions.zapier.com/gpt/api/v1/dynamic/openapi.json?tools=meta 

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.

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.

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’.

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

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.

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.

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.