Understand your BigQuery data and queries

Neelke Stadler Updated by Neelke Stadler

Once you've successfully connected your Turn.io account to BigQuery your data will be live streamed to data tables. The tables can be used for storage and/or to analyse and visualise insights from your data.

If you haven't connected your Turn.io account to BigQuery you should first read how to set up a connection. Once you've successfully established a connection you will benefit from knowing the following:

Your data will be streamed to a dataset named after your service's WhatsApp number.

Go to the console in Google Cloud Platform, click on the menu and scroll down to the Big Data section to find BigQuery.

Tip: You can pin 📌BigQuery in the console menu to make it easier to find.

Make sure you have selected the correct project and you should be able to see your data like this:

Eleven tables are currently streamed to BigQuery:

  1. Attachments: This table contains URLs to your media.
  2. Cards: This table contains all your pre-loaded content.
  3. Chats: This table contains information on users that are engaging with your service.
  4. Contacts. This table contains profile details for your contacts.
  5. Contact Details: This is a legacy table and can be ignored by most. The Contacts table now has this information.
  6. Faqs: This is a legacy table and can be ignored by most. The Cards table now has this information.
  7. Messages: This table contains information on individual messages.
  8. Message Attachments: This table links the Messages and Attachments tables.
  9. Message Tags: This table contains data of all your labelled messages.
  10. Number Tags: This table contains data on all the labels for your account.
  11. Statuses: This table contains all the message statuses (sent, read, delivered).

The tables contain your raw data. To help you make sense of it we provide definitions for the most important data fields in our Turn.io Data Dictionary.

We created a separate article with example queries to assist you.

The first time that you connect to BigQuery your historic data will be uploaded.

Depending on the size of your service to date this may take a while. The status of the upload will be available in the status table in the Turn UI under Settings > Integrations. Once the historic data is uploaded your data will continue to be live streamed. Any error messages will be communicated in the status table.

Due to the data being live streamed from Turn.io to BQ any updates to records will be written as new rows.

This has the implication that your SQL queries should use count(distinct(id) or count(distinct(uuid)to determine the unique number of users and /or messages. You can see our article on Refining Raw Turn Data in BigQuery for more details.

Download media assets using Turn.io's media API.

The URL in your BigQuery attachment table will point at the media API from where you can download media assets. Keep in mind that the same authentication methods apply for the media API as for the rest of the API. An authentication token can be generated in the Settings section. Using a valid token to authenticate using an `Authentication: Bearer <insert your token>` HTTP header one can download the media asset. The media API is documented at https://whatsapp.turn.io/docs/index.html#retrieving-media.

Was this article helpful?

Set up a data connection to BigQuery

Conversation billing

Contact