Keep your research panel up to date with your most current participant data with Rally's BigQuery integration, which automatically syncs data from BigQuery into Rally every 24 hours.

By configuring sync settings and property mappings, you can tailor the integration to bring in exactly the data your team needs to build targeted segments, recruit the right participants, and launch studies faster.

This integration can also be set up as a two-way sync to share Rally data back to BigQuery automatically.

How does the BigQuery integration work?

Rally connects to your Google Cloud project and syncs data from a specified BigQuery table into Rally on a set schedule.

After setting up a connection with BigQuery, you can set configuration settings and create property mappings between BigQuery properties and Rally properties. Each of these properties can be configured per property to either sync data from Rally → BigQuery, from BigQuery → Rally, or use a two-way sync (see below for more details).

Once your BigQuery account has been connected and the integration has been configured, Rally will automatically sync data between BigQuery and Rally every 24 hours. You can pause the automatic syncs, delete the connection, or start a manual sync at any time by going to Integrations within your workspace settings and clicking Manage.

Rally uses the Identifier Field (typically email address) to match BigQuery records to people in Rally. If a record from BigQuery is not found in Rally, Rally will either ignore it or create a new person, depending on your sync preferences.

Required user permissions

Before setting up the BigQuery integration, make sure you have the correct permissions in both Rally and Google Cloud.

Rally user permissions

Google Cloud permissions

Rally connects to BigQuery using a Google Cloud service account that you create and configure in your Google Cloud account. The service account will need the following IAM roles:

• BigQuery Data Viewer — to read data from your BigQuery datasets or tables

• BigQuery Job User — to run query jobs on your behalf

• BigQuery Data Editor (optional) — to write data from Rally to your BigQuery datasets or tables

See the Preparation section next for step-by-step instructions on creating the service account before connecting.

Preparation

Before connecting BigQuery to Rally, you'll need to create a service account in your Google Cloud account and grant it the necessary permissions. This service account acts as a secure intermediary that manages data transfers between Rally and your BigQuery warehouse.

What is a service account?

A Google Cloud Platform (GCP) service account is a specialized account that applications use to access Google APIs on your behalf. When you create this service account, it will serve as a trusted intermediary, safely managing data syncs between BigQuery and Rally. See Google's documentation for more information.

Create a service account

The steps below describe how to create and configure a service account in Google Cloud. Note that these instructions may not always reflect the most recent changes to the GCP console.

1. Enable the BigQuery API

1. In the Google Cloud Console, navigate to the Library page.

2. Search for "BigQuery" and select the BigQuery API.

3. Click Enable to activate the API.

2. Create a service account

1. Go to IAM & Admin > Service Accounts.

2. Click Create Service Account.

3. Enter a Service Account Name and an optional Service Account ID.

4. Click Create.

3. Grant permissions

1. On the service account details page, click Grant this service account access to project.

2. In the New members field, enter the Service Account ID you created.

3. In the Role field, select BigQuery Job User.

4. To allow Rally to read datasets from BigQuery for selection during setup, also select BigQuery Data Viewer.

   • If you prefer to grant access at the table level only, skip this step and follow the optional instructions in step 5 below instead.

5. Click Save.

4. Create a service account key

1. On the service account details page, go to the Keys tab.

2. Click Add Key > Create new key.

3. Select JSON as the key type.

4. Click Create. A JSON file containing the service account key will be downloaded to your computer.

5. Optional: Grant table-level permissions

If you chose not to grant BigQuery Data Viewer at the project level, you can instead grant access to a specific dataset and table by running the following queries in the BigQuery SQL Editor. Replace the placeholders with your actual values.

GRANT `roles/bigquery.dataViewer`
ON TABLE {{DATASET_NAME}}.{{TABLE_NAME}}
TO "serviceAccount:{{SERVICE_ACCOUNT_NAME}}";

GRANT `roles/bigquery.metadataViewer`
ON TABLE {{DATASET_NAME}}.{{TABLE_NAME}}
TO "serviceAccount:{{SERVICE_ACCOUNT_NAME}}";

Once you've created the service account and downloaded the JSON key file, you're ready to connect BigQuery to Rally.

6. Optional: Grant write permissions for two-way sync & Rally-to-BigQuery sync

Two-way sync and Rally-to-BigQuery sync require a dedicated staging dataset in BigQuery and additional write permissions on the target table. In a BigQuery SQL Editor, run the following queries to grant write permissions to the service account. Replace the placeholders with your actual values.

GRANT roles/bigquery.dataEditor
ON TABLE {{DATASET_NAME}}.{{TABLE_NAME}}
TO "serviceAccount:{{SERVICE_ACCOUNT_NAME}}";

GRANT roles/bigquery.dataEditor
ON SCHEMA {{STAGING_DATASET_NAME}}
TO "serviceAccount:{{SERVICE_ACCOUNT_NAME}}";

Setting up the BigQuery integration

Step 1. Connect BigQuery to Rally

1. In the top left corner of Rally, select your workspace name and select Settings.

2. From the sidebar, select Integrations.

3. To the right of BigQuery, select Connect.

4. Enter your Google Cloud Project ID and the service account credentials (JSON key file) for the service account you've configured.

5. Select Connect to establish the connection.

Step 2. Link a BigQuery table

After connecting, you'll need to specify which BigQuery table Rally should sync from.

1. Enter the following details about your BigQuery table:

   • Project — your Google Cloud project ID

   • Dataset — the BigQuery dataset name

   • Table — the specific table to sync from

2. Select Save to link the table.

Step 3. Configure the sync

Carefully configure sync preferences to determine how Rally handles records during sync. You can update your sync configuration settings at any time to better fit your needs.

Create new records in BigQuery during sync

• Always selected; if a Rally record isn't found in BigQuery using the person's identifier, a new BigQuery record will be created.

• Only takes effect if one or more property mappings have sync direction Rally to BigQuery or Two-way.

Delete records in BigQuery during sync

• If selected, any BigQuery records that don't exist / have a match in Rally (based on the person's identifier) will be deleted from BigQuery.

• If enabled, Create new people in Rally during sync preference is disabled as we would delete external records in BigQuery if it does not exist in Rally.

Create new people in Rally during sync

• When enabled, BigQuery records that don't already exist in Rally will be created as new participants. Rally uses the Identifier Field (email) to check for existing records.

• If enabled, select which Population new participants should be added to.

What should Rally do if a record exists in Rally but is missing in BigQuery?

• Do nothing — Rally takes no action on the missing record.

• Opt out missing people in Rally — People missing from BigQuery will be marked as Opted Out in Rally and cannot be contacted.

Trigger sync whenever a CSV is uploaded to Rally

• When enabled, Rally will automatically trigger a sync any time a .csv file is uploaded, so a researcher uploading a list of participants can be sure they get the latest, most complete participant records from BigQuery without waiting for the next scheduled sync.

• Contact Rally to get this feature, which can be enabled later.

4. Map fields between BigQuery and Rally

Finally, map BigQuery fields to properties in Rally and select the sync direction.

The Identifier Field is how Rally matches BigQuery records to people in Rally. Only Unique properties (like email address) can be used as an identifier field.

Property Mapping

Rally currently supports mapping the following field types from BigQuery:

Notes about property mapping

• Field types must be compatible for a mapping to work. For example, you cannot map a BigQuery INTEGER field to a Rally date property. If a mapping isn't working as expected, check that the property type in Rally matches the corresponding field type in BigQuery. You can recreate a Rally property with the correct type and re-import existing data if needed.

• We recommend creating the necessary Properties in Rally before running your first sync so that field mappings are ready to go.

• Properties can also be mapped and added over time; any new mappings will populate values for existing participants on the next sync.

Property level sync direction

Rally supports three different sync directions: BigQuery to Rally, Rally to BigQuery, and Two-Way. These sync directions are configured per field/property.

Enabling Rally to sync specific data back to BigQuery allows for research activities conducted in Rally to update your BigQuery database, keeping your organization aligned and ensuring data stays fresh. For example, we suggest syncing properties such as “Last Contact Date”, “Last Interview Date”, “Opted Out” status, or “Incentives Sent".

Managing the BigQuery integration

1. In the top left corner of Rally, select your workspace name and select Settings.

2. From the sidebar, select Integrations.

3. To the right of BigQuery, select Manage.

4. Update your sync configuration settings at any time to better fit your needs.

5. Add, update, or remove property mappings, and configure their sync directions and conflict resolution setting as needed. Select + Add property mapping to add a new field mapping, and select the trash icon if you need to delete a property mapping.

Sync history

View a table of all prior syncs to see how many people had data updated during each sync.

• The # updated refers only to rows that have changed

• A sync occurs when data in BigQuery differs from what’s stored in Rally, and may result in records being created, updated, or deleted.

Check the status column to see if daily syncs are successful or not. If syncs are failed or canceled, contact Rally to learn more about why and how to resolve it.

Start or pause syncs

You can click Run manual sync to kick off a new sync immediately, rather than waiting for the next 24hr interval.

Click Pause to pause data from syncing between Rally and BigQuery until you select Resume.

Delete connection