Google BigQuery
Add each Encatch survey response as a row in a BigQuery table - how to set up the service account in Google Cloud and connect it
Overview
The Google BigQuery connector adds each survey response as one row in a BigQuery table, so responses can be joined with the rest of your warehouse.
Encatch writes with a service account that you create in your own Google Cloud project. You decide which dataset it can write to, and you can remove its access at any time.
| What it gets | One row per response in a table |
| Events | Survey submitted, and Survey partially submitted when ticked |
| Anonymous respondents | Not sent: identified users only |
| Test connection | Checks the key only: no row is written |
| Status | Available |
What You'll Need
Before you start, make sure you have:
- A Google Cloud project with billing turned on — BigQuery's free tier does not accept streamed rows
- A BigQuery dataset — the one that should hold the table
- Permission to create a service account and its key — and to grant it access to the dataset
Step 1: Prepare the project in Google Cloud
- In the Google Cloud console, choose the project of your dataset.
- Make sure billing is turned on for the project.
- Open APIs and services and make sure the BigQuery API is enabled.
- Note the Project ID (lowercase letters, numbers and
-, not the project's name) and the dataset name.
Step 2: Create a service account and its key
- In the Google Cloud console, open IAM and admin → Service accounts and click Create service account.
- Give it a name you'll recognise, for example
encatch-data-sync, and create it. It needs no project-wide role. - Open the service account, go to Keys → Add key → Create new key, choose JSON and create it. Your browser downloads the key file.
Google Cloud organizations created since May 2024 block service account keys by default. If Create new key is refused, ask your Cloud admin to allow key creation for the project (the iam.managed.disableServiceAccountKeyCreation policy).
Step 3: Add the connection in Encatch
- In Encatch, open Data sync → Survey events and click Add connection.
- Choose Google BigQuery and enter a Name.
- Fill in the settings:
| Field | What to enter |
|---|---|
| Service account key | Open the JSON key file and paste its whole content. |
| Project ID | The project's ID, for example acme-analytics. |
| Dataset | The dataset's name, for example encatch. |
| Table | The table's name. The default is encatch_responses. Encatch creates it if it does not exist. |
Once the key is pasted, the form shows the service account's email and a ready-made Grant statement.
Step 4: Give the service account access to the dataset
- Copy the Grant statement from the Encatch form. It looks like this:
GRANT `roles/bigquery.dataEditor` ON SCHEMA `your-project`.your_dataset
TO "serviceAccount:encatch-data-sync@your-project.iam.gserviceaccount.com";- In the BigQuery console, open a query tab, paste the statement and run it.
This gives the service account BigQuery Data Editor on that one dataset. You can also grant the same role from the dataset's Sharing → Permissions.
Step 5: Test, save and start sending
- Back in Encatch, press Test connection. It checks the service account key by reading the dataset and the table: no row is written. Write access shows with the first response.
- Pick the surveys, click Save, then press Start sending.
Other options (partial responses, rate limit, Admin API responses) are explained in Connection settings.
Check it in BigQuery
After the first response, open the table in the BigQuery console and preview it. Each response adds one row:
| Column | Holds |
|---|---|
| Response ID and submitted time | Which response, and when |
| Survey ID and name | Which form |
| User | The user's identifier |
| Status | complete or partial |
| Answers | The answers, as JSON |
| Traits | The user's traits, as JSON |
Encatch creates the table on the first response, partitioned by day. A table you made yourself needs the same columns.
Good to know
- One row per send. A response is sent as partial or as submitted, never both. On a form that lets people edit their response, each edit they submit adds a row under the same response ID. For one row per response, take the latest submitted time per response ID.
- Retries. A retry of the same send within about a minute is stored once.
- Billing. Google bills the project for streamed rows and storage.
- Rate limit. BigQuery limits the bytes streamed a second, not the requests, so a limit is rarely needed.
Troubleshooting
| What you see | What it means | What to do |
|---|---|---|
| This is not a service account key | The pasted text is not the whole JSON key file | Open the downloaded file and paste all of it |
| Test passes, first response fails | The service account can read but not write the dataset | Run the Grant statement (Step 4) |
| Refused by the tool, mentioning billing | The project has no billing | Turn on billing for the project |
| Use the project ID | The project's name was entered instead of its ID | Copy the Project ID from the Google Cloud console |
Was this page helpful?
Salesforce
Create or update Salesforce records from Encatch survey responses - how to set up the integration user and External Client App in Salesforce
Google Sheets
Add each Encatch survey response as a row in a Google Sheets spreadsheet - how to set up the service account and share the spreadsheet
