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:

The following data is currently being streamed to BigQuery:
  1. Accounts: contains information of members of your organization.
  2. AI events: contains log data of all interactions with AI systems within the Turn.io platform.
  3. Attachments: contains URLs to your media.
  4. Cards: contains all your pre-loaded content.
  5. Chat events: contains events that happened in a chat (assignment and closing).
  6. Chat notes: contains internal notes written by your operators, including information about mentions.
  7. Chats: contains information on users that are engaging with your service.
  8. Contact Details: This is a legacy table and can be ignored by most. The Contacts table now has this information.
  9. Contacts. contains profile details for your contacts.
  10. Faqs: This is a legacy table and can be ignored by most. The Cards table now has this information.
  11. Flow Results: contains responses and interactions collected from contacts during automated conversational flows or journeys in the Turn.io platform.
  12. Flow Results Data Package: contains metadata and structural information about sets of flow results in the Turn.io platform.
  13. Message Attachments: links the Messages and Attachments tables.
  14. Message Tags: contains data of all your labelled messages.
  15. Messages: contains information on individual messages.
  16. Number Tags: contains data on all the labels for your account.
  17. Statuses: 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 and a Relational Diagram.

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

Refining Raw Turn Data in BigQuery

Contact