Set up a data connection to BigQuery

Neelke Stadler Updated by Neelke Stadler

We believe that you should own your data, which is why we've made it simple for you to do so. 

You can connect your Turn.io account to BigQuery – Google's serverless data warehouse that makes super-fast SQL queries possible. This is exciting news as it makes long-term storage easy and allows you to use any visualisation tool of choice. We recommend Google Data Studio as a free visualisation tool that works really well with BigQuery. 

Please note that the BigQuery integration will not work with a Sandbox BigQuery account as it does not support streaming of data. See their docs on how to upgrade to a full account.

How to set up a connection from Turn.io to BigQuery:

We need just one service key to be integrated in order to connect Turn to Big Query. There are six steps to get that service key from Google Cloud Platform console.

Create a GOOGLE CLOUD PLATFORM (GCP) account
Step 1: Go to console.cloud.google.com
Step 2: Log in using your Google account in which you want your data to be integrated
Step 3: Accept the terms and conditions
Create a NEW PROJECT inside your GCP account
Step 1: In the menu section, navigate to HOME
Step 2: Click on dashboard
Step 3: Create a new project
Step 4: Give your project a name and the organisation name
Step 5: Click on create project
Create a SERVICE ACCOUNT for your GCP project
Step 1: In the menu section, go to IAM & ADMIN
Step 2: In the sub-menu, click on Service Accounts
Step 3: Click on "CREATE SERVICE ACCOUNT"
Step 4: Enter a name and description for the service account
Step 7: Click Done to complete

Note: Do not grant the service account access to the project.
GENERATE SERVICE KEY for your service account
Step 1: Click on the three dots under actions in the service account
Step 2: Click on "Manage keys" in the sub-menu
Step 3: Select add key and create new key
Step 4: Select JSON and click create
Step 5: A file will be downloaded to you local machine, make sure you save it securely
Create a BIGQUERY DATASET
Step 1: In the menu section, go to BigQuery
Step 2: In the Explorer panel, select the project for which you want to create the dataset
Step 3: Expand the Actions option (three vertical dots) and click Create dataset
Step 4: On the Create dataset panel, for Dataset ID, enter the number of your WhatsApp line for which you want to stream data to BigQuery (without a '+' prefix). (Optional) For Data location, choose a geographic location for the dataset
Step 5: Click Create dataset to complete
Allow SERVICE ACCOUNT to Edit BIGQUERY DATASET
Step 1: In the menu section, go to BigQuery
Step 2: In the Explorer panel, expand your project and select the dataset you created previously
Step 3: In the details panel, click Share dataset
Step 4: In the Share dataset panel, in the Dataset permissions tab, enter the email address of the service account you created previously in the Add principals field
Step 5: For Select a role, select BigQuery Data Editor
Step 6: Click Add to add the principal
Step 7: Click Done to complete

Now that you have a service key, you can add the key to your Turn.io account. Adding a service key is fairly easy.

  • Upload your JSON credentials in Turn.io by going to Settings > Integrations > Connect to BigQuery > Upload JSON credentials.

Success 🙌! Two things will happen now:

  1. Your messages', chats', attachments' and faqs' (also known as content) historical data will populate eight tables in BigQuery. Please note that it might take some time to upload your historical data. 
  2. Your new data will continue to be live-streamed from Turn.io to BigQuery.

Connecting your Turn.io account to BigQuery means you can access all your data at any time.

Was this article helpful?

How to nudge and track behaviours in conversations

Understand your BigQuery data and queries

Contact