Documentation Index

Fetch the complete documentation index at: https://help.madkudu.com/llms.txt

Use this file to discover all available pages before exploring further.

BigQuery

  • Updated on Jun 25, 2026
  • Published on Oct 9, 2024
  • 4 minute(s) read
Prev Next

✅  Source  ❌ Destination

With HG Insights + BigQuery, send your engagement, intent or CRM data to HG Insights.

What does this integration do?

Turning on the BigQuery integration allows you to send BigQuery data to HG Insights: product usage, web visits, contact properties to surface to your Sales reps in the HG Insights iFrame in Salesforce or leverage in a scoring model.

Authentication options

HG Insights supports two ways to authenticate your BigQuery connection. Choose the one that fits your GCP setup.

Option A: Service Account (recommended)

Uses a GCP service account key - no user session involved, no periodic re-authentication required. This is the most reliable option for a persistent background integration.

GCP setup (your team):

  1. Create a service account in a GCP project

  2. Grant roles/bigquery.jobUser on the billing project

  3. Grant roles/bigquery.metadataViewer at the data project level + roles/bigquery.dataViewer on the target dataset (or roles/bigquery.dataViewer at project level to cover both)

  4. Generate a JSON key file

HG Insights setup:

  1. Log in to RGIP and go to Integrations > BigQuery

  2. Select Service Account at Step 0 of the setup

  3. Paste the JSON key file contents at Step 1 and complete the setup

Option B: OAuth + Trusted App Exemption

Uses a Google OAuth flow tied to a user account. By default, Google Workspace enforces a session control policy on Cloud-scoped OAuth apps (including BigQuery) with a maximum session length of 24 hours - meaning the connection would break at least once a day and require manual re-authentication.

This option only works if your Google Workspace IT admin exempts HG Insights as a trusted app, which disables the session timeout for this specific app. Without that exemption, OAuth is not suitable for a persistent integration.

Your IT admin setup (required before connecting):

  1. In the Google Workspace Admin Console, go to Security > Access and data control > Google Cloud session control

  2. Mark the HG Insights / MadKudu app as Trusted in the App access control page

  3. Enable the Exempt trusted apps checkbox in the Cloud session control settings

HG Insights setup (once IT has completed the above):

  1. Log in to RGIP and go to Integrations > BigQuery

  2. Select OAuth at Step 0 and follow the Google authentication flow

Data setup requirements

Data in BigQuery resides in tables. When sharing data with HG Insights, the best practice is to create separate tables for each object type. You don't need to send all three - only what your use case requires:

  • Contact attributes only: a contact table is sufficient

  • Account attributes only: an account table is sufficient

  • Events or intent data: both an event table and a contact table are required, so HG Insights can reconcile events with a contact's email across integrations (format for anonymous intent: anonymous@domain.com)

Each table must include created_at and updated_at timestamp columns. HG Insights pulls data incrementally - new rows via created_at, updated rows via updated_at.

Scope: The integration uses https://www.googleapis.com/auth/bigquery. Other scopes such as cloud-platform are not compatible. To restrict HG Insights' access to specific datasets and tables, configure those permissions on the service account (or OAuth user) in BigQuery directly.

What Objects can you send to HG Insights?

HG Insights works with the following entities and required properties (if the naming doesn't match exactly these column you'll be able to map them in HG Insights but they need to be in there): 

Contacts

Column

Type

example

Comment

id

VARCHAR(256)

93d8AB4f69fa80

Required  (Can be equal to the email field)

email

VARCHAR(256)

john@slack.com

Required

created_at

TIMESTAMP

2023-12-02 20:23:12.010000

Required. UTC

updated_at

TIMESTAMP

2023-12-02 20:23:12.010000

Required. UTC

contact_{property}

depends on the property can be numeric or varchar or boolean

Optional.

Events

Column

Type

example

Comment

id

VARCHAR(256)

03492f12-061b-4f54-b866-764b4f69fa80

Required

event_name

VARCHAR(256)

logged_in

Required

event_timestamp

TIMESTAMP

2023-12-02 20:22:51.780000

Required

contact_id

VARCHAR(256)

93d8AB4f69fa80 / john@slack.com

Required (needs to match “id” in Contacts table)

created_at

TIMESTAMP

2023-12-02 20:23:12.010000

Required

updated_at

TIMESTAMP

2023-12-02 20:23:12.010000

Required

event_{property}

depends on the property can be numeric or varchar or boolean

Optional

Accounts

Column

Type

example

Comment

id

VARCHAR(256)

RECAB4f6

Required

domain

VARCHAR(256)

slack.com

Required

created_at

TIMESTAMP

2023-12-02 20:23:12.010000

Required. UTC

updated_at

TIMESTAMP

2023-12-02 20:23:12.010000

Required. UTC

account_{property}

depends on the property can be numeric or varchar or boolean

Optional.

After connecting

Pre-requisite

You have access to your BigQuery account and to HG Insights app

For HG Insights to start pulling data from your BigQuery account, please follow the following steps

  1. Log in to RGIP

  2. Click on Integrations > BigQuery

  3. Click on "Grant access to Google BigQuery" and follow the setup flow

  4. Once connected you'll see a form asking for

    • Project ID: located in your project list in your Google Console  



    • Dataset: the name of your dataset

    • Location: the location of your dataset

    • Enter the table names corresponding to Events, Contacts or Accounts (coming soon) (you don't need to send HG Insights all 3, only the ones needed for your use case)

    • For each object, map your BigQuery table column names corresponding to the required properties. In the example below, the column in BigQuery corresponding to the event time is named "timestamp" and needs to be mapped into the event_timestamp (HG Insights naming)


    • Click Save

    • Click on Test connection to check HG Insights can connect to your dataset

HG Insights will start pulling data moving forward! For HG Insights to pull historical data on these tables, please submit a support ticket - it's a quick configuration to turn on on our end.

Example of request:

"Hi, we'd like to pull all data from our BigQuery instance connected to HG Insights

  • Table Events: from YYYY-MM-DD  (we recommend 9 months of data)

  • Table Contacts: from YYYY-MM-DD

  • Table Account: from YYYY-MM-DD (coming soon)

Thanks"

At any time, you can see the status of the integration in Settings > Processes, under Connectors - Pull you should see the BigQuery integration and any error.

After a few hours, you can see the volume of Data already pulled by going to Data in the main left navigation bar and BigQuery

 

FAQ

How do BigQuery contacts get linked to Salesforce Accounts?

HG Insights uses the email domain of your provided contacts to link to the website of the respective Salesforce Account.

Troubleshooting

Seeing invalid_rapt errors in Settings > Processes?
This means you're connected via OAuth and your Google Workspace session control policy is expiring the connection. You have two options:

  • Switch to Service Account auth (Option A above) - permanent fix, no re-authentication ever required

  • Have your IT admin exempt the HG Insights app as trusted in Google Workspace (Option B above) - removes the session timeout for this specific app


Related articles