Welcome to Encatch Docs
Data syncBetaConnectorsWarehouse and spreadsheet

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 getsOne row per response in a table
EventsSurvey submitted, and Survey partially submitted when ticked
Anonymous respondentsNot sent: identified users only
Test connectionChecks the key only: no row is written
StatusAvailable

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

  1. In the Google Cloud console, choose the project of your dataset.
  2. Make sure billing is turned on for the project.
  3. Open APIs and services and make sure the BigQuery API is enabled.
  4. 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

  1. In the Google Cloud console, open IAM and admin → Service accounts and click Create service account.
  2. Give it a name you'll recognise, for example encatch-data-sync, and create it. It needs no project-wide role.
  3. 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

  1. In Encatch, open Data sync → Survey events and click Add connection.
  2. Choose Google BigQuery and enter a Name.
  3. Fill in the settings:
FieldWhat to enter
Service account keyOpen the JSON key file and paste its whole content.
Project IDThe project's ID, for example acme-analytics.
DatasetThe dataset's name, for example encatch.
TableThe 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

  1. 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";
  1. 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

  1. 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.
  2. 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:

ColumnHolds
Response ID and submitted timeWhich response, and when
Survey ID and nameWhich form
UserThe user's identifier
Statuscomplete or partial
AnswersThe answers, as JSON
TraitsThe 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 seeWhat it meansWhat to do
This is not a service account keyThe pasted text is not the whole JSON key fileOpen the downloaded file and paste all of it
Test passes, first response failsThe service account can read but not write the datasetRun the Grant statement (Step 4)
Refused by the tool, mentioning billingThe project has no billingTurn on billing for the project
Use the project IDThe project's name was entered instead of its IDCopy the Project ID from the Google Cloud console

Was this page helpful?