How to use the BigQuery integration

The BigQuery integration allows you to digest and visualize data from your BigQuery warehouse directly in TheyDo. Once your Metrics are set up to pull from BigQuery, you won’t have to worry about manually adding data again. 

Set up your integration

Once you have access to the BigQuery integration, we recommend partnering with an analyst or account owner for your BigQuery account. That person can choose between two options for implementing the BigQuery integration.

Start by navigating to Settings > Integrations. You'll need to do this from a TheyDo admin account.

1A. Use TheyDo’s service account

Choose this method if you prefer to use TheyDo’s service account to process your data. This is the simplest method as it doesn’t require management of service account credentials. To allow access, assign the BigQuery User and BigQuery Data Viewer roles in your Google Cloud project to the following TheyDo service account: bigquery-integrations@theydo.iam.gserviceaccount.com

1B. Use your own BigQuery service account

Choose this option if you wish to use your own service account to process your data. This method requires you to provide a Service Account JSON Key for your Google account.

2. Configure your BigQuery instance

Instance name: Enter a name for your BigQuery instance. This name will be used to identify this particular connection in TheyDo (this is useful if you plan to set up multiple BigQuery instances).

3. Test your first BigQuery full table

Before you finalize the connection setup, it’s important to test the integration to ensure that everything is configured correctly. This step requires you to provide the full reference of a BigQuery table. By specifying a particular dataset and table, our system can verify the connection and data accessibility—ensuring that your integration will function as expected.

Enter the full table reference in the format project_id.dataset_id.table_name . This should be the exact path to a table of a dataset you wish to give TheyDo access to. The testing step is crucial for confirming that TheyDo has the necessary permissions and connectivity to retrieve data from your BigQuery account.

You can find the table reference under Details → Table ID of your BigQuery table.

4. If you’d like to add multiple instances, follow the instructions in the below video guide

TheyDo's BigQuery integration currently supports automation of CSAT, CES, NPS, RATIO, and Other metric types. Once your BigQuery integration is set up, it's important to prepare your data, properly formatting according to the below requirements for each metric type.

Prepare your data in BigQuery

• CSAT

  Required columns: date, positives, negatives, respondents

• CES

  Required columns: date, respondents , value

• NPS

  Required columns: date, detractors, promoters, respondents

• RATIO

  Required columns: date, numerator, denominator

• OTHER

  Required columns: date, value
  

Please keep in mind that for the 'Other' metric, TheyDo pulls a pre-calculated average value. You'll need to have prepared that value in your BigQuery data.

Supported data types

For the integration to function correctly and for your data to be processed without errors, it is essential to adhere to specific data type requirements for each column in your BigQuery tables. Here’s a breakdown of the supported data types:

• Date columns

  Required data type: DATE

• Metric Value columns

  Required data type: INTEGER | FLOAT | NUMERIC
  
  

  All other columns that represent metric values (such as positives, negatives, respondents, value, detractors, promoters, numerator, denominator) must use one of the supported BigQuery numeric data types.

Aliasing columns for compatibility

The original column names in your BigQuery tables do not need to match the column names expected by TheyDo. You can use SQL aliasing to map your table columns to the required fields. For example, for an NPS metric, if your table has a column for detractors called det you can alias it in your query as follows:

SELECT det AS detractors, ... FROM project_id.dataset_id.your_table_name

This flexibility allows you to maintain your original database schema while ensuring compatibility with the TheyDo integration.

Set up a BigQuery metric

Congratulations! Your BigQuery integration is properly set up. It's time to create your BigQuery-fueled metrics in TheyDo.

1. From your Metrics Library or within the Metrics Lane in any journey, click +Metric

2. Select the source BigQuery

3. Select your Metric type

4. Insert your SQL in the query box. We recommend asking your data analyst for a list of queries depending on your needs

5. Run the query

6. You'll be able to preview the data fetched as a result of your query

7. Click to save your Metric and your data will be synced within seconds

8. We recommend checking the time frame of your data points and adapting the date range to modify your metric graph

Once your BigQuery metrics are set up, they'll fetch data every day at 3AM CET—making sure you're always seeing the latest data in your journeys and journey steps.

The BigQuery integration is free for all users throughout it's beta stage (during summer 2024) and is subject to become a paid feature after that time period.

We'd love your feedback! Please join our Journey Management Slack community to share your comments, questions, thoughts and suggestions—so we can improve the BigQuery integration for you.