How to use BigQuery data in TheyDo
Once your BigQuery integration is connected, you can create metrics that pull data from your BigQuery tables using SQL queries.
BigQuery metrics in TheyDo are query-based — you write a SQL query that returns the data you want, and TheyDo fetches and displays it as a metric. Metrics update automatically every day at 3 AM CET, so you'll always see the latest data in your journeys without any manual updates.
Creating a metric from BigQuery
- Open a journey and select Add a metric, or go to the Metrics Library and select + Metric.
- Choose BigQuery as the data source.
- Select the metric type (CSAT, CES, NPS, RATIO, or Other).
- Write your SQL query in the query box. Your data team can help put together the right queries for your needs.
- Click Run the query — TheyDo will preview the data returned.
- Review the data preview, then click Save.
- Check the time frame of your data points and adjust the date range to get the view you need.
SQL query requirements
Your query must return the columns required for your metric type:
| Metric type | Required columns |
|---|---|
| CSAT | date, positives, negatives, respondents |
| CES | date, respondents, value |
| NPS | date, detractors, promoters, respondents |
| RATIO | date, numerator, denominator |
| OTHER | date, value |
If your BigQuery table uses different column names, use SQL aliasing:
SELECT det AS detractors, prom AS promoters, resp AS respondents, dt AS date FROM project_id.dataset_id.your_table_name
Using dimensions in your metric
Columns in your query beyond the required ones, like country, channel, or plan, automatically become dimensions. You can filter your metric by dimension directly in a journey, without writing a new query for each variation.
For example, one query with a channel column lets you create filtered views like Sign-ups (Mobile) and Sign-ups (Web) from the same metric.
Tips
- Work with a data analyst to build and validate queries before adding them to TheyDo.
- If you have multiple BigQuery instances, select the right one before writing your query.
- Metrics refresh daily at 3 AM CET — no manual sync needed after initial setup.
- After saving, adjust the date range on the metric graph to match the time frame of your data.