Overview

BigQuery is a fully managed, serverless data warehouse used by organizations to store, query, and analyze large-scale datasets.

Common uses for the Alchemer BigQuery integration

• Personalize invites and surveys with information in BigQuery
• Use BigQuery information in survey logic
• Automate data retrieval and updates between Alchemer and BigQuery
• Reduce manual data entry and record maintenance
• Keep BigQuery records synchronized with responses collected in Alchemer
• Push survey and question metadata into BigQuery for analytics

What can the Alchemer BigQuery integration do?

• Get row data
• Update row
• Upsert row
• Push to table
• Structured response

You will need

• BigQuery API access and credentials
• An Alchemer plan that includes integrations and the Integration Manager permission enabled.  
  • Contact us if you are unsure if your plan includes integrations.

Setup Alchemer BigQuery integration in surveys

BigQuery | Get data

You will need:

• BigQuery API credentials
  • Service account client email
  • Private key linked to that service account
  • Service account permissions on project(s) you want to query
• Survey fields containing a unique identifier for the row

Configure the action

1. Open your survey in Survey builder.
2. Select Add New: Action.
3. In the Add Action modal, scroll to the Integrations section.
4. Select BigQuery.
5. Select BigQuery | Get data.
6. BigQuery | Authentication: Select an existing authentication or create a new authentication.
7. BigQuery | Select project: Select which project contains the dataset and table you will query on.
8. BigQuery | Select dataset: Select which dataset contains the table you will query on.
9. BigQuery | Select table: Select which table contains the data you will query on.
10. BigQuery | Find row: Select the Alchemer field containing the lookup value. You can also specify whether filters should be seperated by AND or OR operator.
11. BigQuery | Get data back: Select the BigQuery row fields you want returned to the survey. These can be used to personalize survey questions.
12. Save the action.

Status codes

• 200: A single row was successfully found
• 201: Query succeeded but no rows were found
• 202: Multiple rows were found. The first row is used for the values returned to Alchemer
• 400: Error returned by BigQuery API

BigQuery | Update Row

You will need:

• BigQuery API credentials
  • Service account client email
  • Private key linked to that service account
  • Service account permissions on project(s) you want to query
• Survey fields containing the identifier for the row
• Note: All rows matched on filters will be updated.

Configure the action

1. Open your survey in Survey builder.
2. Select Add New: Action.
3. In the Add Action modal, scroll to the Integrations section.
4. Select BigQuery.
5. Select BigQuery | Update row.
6. BigQuery | Authentication: Select an existing authentication or  create a new authentication.
7. BigQuery | Select project: Select which project contains the dataset and table you will query on.
8. BigQuery | Select dataset: Select which dataset contains the table you will query on.
9. BigQuery | Select table: Select which table contains the data you will query on.
10. BigQuery | Find row: Select the Alchemer field containing the lookup value. You can also specify whether filters should be seperated by AND or OR operator.
11. BigQuery | Update row in table: Choose the Alchemer fields used to update corresponding BigQuery row fields.
12. BigQuery | Get data back: Select metadata fields (e.g. message, status code). These can be used to help debug your action setup.
13. Save the action.

Status codes

• 200: Successfully updated row.
• 201: Query ran successfully. No rows updated.
• 202: Multiple rows were found. X row(s) updated.
• 400: BigQuery API returned an error.

BigQuery | Upsert Row

You will need:

• BigQuery API credentials
  • Service account client email
  • Private key linked to that service account
  • Service account permissions on project(s) you want to query
• Survey fields containing the identifier for the row
• Note: All rows matched on filters will be updated.

Configure the action

1. Open your survey in Survey builder.
2. Select Add New: Action.
3. In the Add Action modal, scroll to the Integrations section.
4. Select BigQuery.
5. Select BigQuery | Upsert row.
6. BigQuery | Authentication: Select an existing authentication or create a new authentication.
7. BigQuery | Select project: Select which project contains the dataset and table you will query on.
8. BigQuery | Select dataset: Select which dataset contains the table you will query on.
9. BigQuery | Select table: Select which table contains the data you will query on.
10. BigQuery | Find row: Select the Alchemer field containing the lookup value.
11. BigQuery | Upsert row: Choose the survey data you want to use to upsert the row in BigQuery. This will either update the row(s) if the data matches or create a new row.
12. BigQuery | Get data back: Select metadata fields (e.g. message, status code) to get back. These can be used to help debug your action setup.
13. Save the action.

Status codes

• 200: Successfully inserted a new row. 
• 201: Successfully updated X row(s).
• 400: BigQuery API returned an error.

BigQuery | Push to table

You will need:

• BigQuery API credentials
  • Service account client email
  • Private key linked to that service account
  • Service account permissions on project(s) you want to push to
• Survey fields containing the identifier for the table

Configure the action

1. Open your survey in Survey builder.
2. Select Add New: Action.
3. In the Add Action modal, scroll to the Integrations section.
4. Select BigQuery.
5. Select BigQuery | Push to table.
6. BigQuery | Authentication: Select an existing authentication or create a new authentication.
7. BigQuery | Select project: Select which project contains the dataset and table you will query on.
8. BigQuery | Select dataset: Select which dataset contains the table you will query on.
9. BigQuery | Select table: Select which table contains the data you will query on.
10. BigQuery | Find row: Select the Alchemer field containing the lookup value.
11. BigQuery | Insert row into table: Select the Alchemer survey field with data to push to BigQuery.
12. BigQuery | Get data back: Select metadata fields (e.g. message, status code) to get back. These can be used to help debug your action setup.
13. Save the action.

Status codes

• 200: Successfully inserted a new row. 
• 400: BigQuery API returned an error.

BigQuery | Structured response

The Structured Response integration generates three tables in BigQuery: questions, sessions, and responses. The questions table is created during setup, and the sessions and responses tables populate automatically as people complete the survey. 

You will need:

• BigQuery API credentials
  • Service account client email
  • Private key linked to that service account
  • Service account permissions on project(s) you want to push the tables to
• Designated location for new tables

Configure the action

1. Open your survey in Survey builder.
2. Select Add New: Action.
3. In the Add Action modal, scroll to the Integrations section.
4. Select BigQuery.
5. Select BigQuery | Structured response.
6. BigQuery | Authentication: Select an existing authentication or create a new authentication.
7. BigQuery | Select project: Select which project contains the dataset you want to populate with the three new tables.
8. BigQuery | Select dataset: Select which dataset to add the three new tables to.
9. BigQuery | Checking tables: Checking if the three tables already exist from a previous setup. This is used in the next steps to construct the tables if necessary. Please wait for all boxes to load before proceeding.
10. BigQuery | Creating tables: This step builds the tables in your chosen dataset, unless they already exist. Please wait for all boxes to load before proceeding.
11. BigQuery | Select data to push: Selectthe questions you wish to push to the tables. 
12. BigQuery | Pushing question data: Pushes question metadata to BigQuery tables.
13. BigQuery | Get data back: Select metadata fields (e.g. message, status code) to get back. These can be used to help debug your action setup.
14. Save the action.

Status codes

• 200: Successfully pushed to BigQuery.
• 400: BigQuery API returned an error.

Testing and Validation

How to test

• Submit a survey response that triggers the integration action.
• Confirm the expected update, insert, or retrieval.
• Ensure all 3 tables are being made properly if using the Structured Response integration
• Use metadata for verification and debugging.

How to verify results

• Check the impacted record in BigQuery.
• Ensure retrieved or updated values match expectations.

Monitoring Integration Activity

 Where to find logs

• Go to Results → Individual Responses.
• Select the response you want to inspect.
• Open the Action Log tab.

What logs display

• Success or failure status
• Timestamp
• Input and output values
• Returned status codes from BigQuery

Troubleshooting

Authentication issues

• Please ensure authentication is setup correctly. BigQuery requires very specific permissions, which can be easily missed.
  • Use this how-to guide for help.

Lookup failures

• Invalid identifier values
• No matching records

API errors

• Endpoint restrictions, such as not adding BigQuery Data Editor or Bigquery Job User as permissions in your service account.

FAQs