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 emails and workflow steps with information from BigQuery
• 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
• Route workflow logic using real-time BigQuery data

What can the Alchemer BigQuery integration do?

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

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 workflow

BigQuery | Get 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
• Fields containing a unique identifier for the row

Configure the action

1. Open your workflow in Workflow builder.
2. On the right side, drag and drop the BigQuery connection where you want the action to trigger.
3. In the connection box, click the pencil icon in the top right corner.
4. Select BigQuery | Get data.
5. BigQuery | Authentication: Select an existing authentication or create a new authentication.
6. BigQuery | Select project: Select which project contains the dataset and table you will query on.
7. BigQuery | Select dataset: Select which dataset contains the table you will query on.
8. BigQuery | Select table: Select which table contains the data you will query on.
9. BigQuery | Find row: Select the Alchemer field containing the lookup value. You can also specify whether filters should be separated by AND or OR operator.
10. BigQuery | Get data back: Select fields you want to get back. You must put the same field in both the left and right column.
11. 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 (first returned is used)
• 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
• Fields containing a unique identifier for the row
• Note: All rows matched on filters will be updated.

Configure the action

1. Open your workflow in Workflow builder.
2. On the right side, drag and drop the BigQuery connection where you want the action to trigger.
3. In the connection box, click the pencil icon in the top right corner. 
4. Select BigQuery | Update row.
5. BigQuery | Authentication: Select an existing authentication or create a new authentication.
6. BigQuery | Select project: Select which project contains the dataset and table you will query on.
7. BigQuery | Select dataset: Select which dataset contains the table you will query on.
8. BigQuery | Select table: Select which table contains the data you will query on.
9.  BigQuery | Find row: Select the Alchemer field containing the lookup value. You can also specify whether filters should be separated by AND or OR operator.
10. BigQuery | Update row: Choose the Alchemer fields used to update corresponding BigQuery row fields.
    1. Note: All rows matched on filters will be updated.
11. Save the action.

Status codes

• 200: The row was successfully updated
• 400: The external integration 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
• Fields containing a unique identifier for the row
• Note: All rows matched on filters will be updated.

Configure the action

1. Open your workflow in Workflow builder.
2. On the right side, drag and drop the BigQuery connection where you want the action to trigger.
3. In the connection box, click the pencil icon in the top right corner. 
4. Select BigQuery | Upsert row.
5. BigQuery | Authentication: Select an existing authentication or create a new authentication.
6. BigQuery | Select project: Select which project contains the dataset and table you will query on.
7. BigQuery | Select dataset: Select which dataset contains the table you will query on.
8. BigQuery | Select table: Select which table contains the data you will query on.
9. BigQuery | Find row: Select the Alchemer field containing the lookup value.
10. BigQuery | Upsert row: Choose the data you want to use to upsert the row in BigQuery.
    1. Note: All rows matched on filters will be updated.
       
11. Save the action.

Status codes

• 200: Successfully inserted a new row. 
• 201: Successfully updated the row. 
• 400: The external integration 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 query
• Fields containing a unique identifier for the row

Configure the action

1. Open your workflow in Workflow builder.
2. On the right side, drag and drop the BigQuery connection where you want the action to trigger.
3. In the connection box, click the pencil icon in the top right corner. 
4. Select BigQuery | Push to table.
5. BigQuery | Authentication: Select an existing authentication or create a new authentication.
6. BigQuery | Select project: Select which project contains the dataset and table you will push to.
7. BigQuery | Select dataset: Select which dataset contains the table you will push to.
8. BigQuery | Select table: Select which table you will push data to.
9. BigQuery | Find row: Select the Alchemer field containing the lookup value.
10. BigQuery | Insert row into table: Select the Alchemer field with data to push to BigQuery.
11. Save the action.

Status codes

• 200: Successfully inserted a new row. 
• 400: The external integration returned an error.

Testing and Validation

How to test

• Trigger the workflow and monitor individual runs in the Monitor tab in your workflow
  • Click on individual workflow runs to see metadata outputs
• Confirm the expected update or retrieval occurs in BigQuery.
• 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 → Monitor 
• Select the integration step you want to inspect.

What logs display

• Input/Output

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