Skip to main content

Connecting BigQuery to AccountAim

AccountAim's integration with Google BigQuery allows you to directly connect to your BigQuery data warehouse, enabling you to leverage your existing data infrastructure and bring together all of your GTM data in one place.

In this guide, you'll learn how to connect BigQuery to AccountAim.

Integration Overview​

AccountAim's integration with BigQuery connects directly to your BigQuery project via the BigQuery API. This integration allows you to:

  • Query data directly from your BigQuery datasets
  • Sync specific tables, views, or entire datasets to AccountAim
  • Keep your data synchronized on a schedule you control
  • Leverage AccountAim's analytics capabilities on top of your BigQuery data

Only a single AccountAim Admin needs to configure the connection in order to set up the integration.

Prerequisites​

Before connecting BigQuery to AccountAim, ensure you have:

  • Google Cloud Project: Access to a Google Cloud project with BigQuery enabled
  • Service Account or OAuth: Either a service account with JSON key file, or OAuth2 credentials for user authentication
  • Billing Enabled: BigQuery requires billing to be enabled on your Google Cloud project (even for free tier usage)
  • API Access: The BigQuery API must be enabled in your Google Cloud project

Required Permissions​

The service account or user account used for the connection should have the following IAM roles or permissions:

  • BigQuery Data Viewer (roles/bigquery.dataViewer): To read data from datasets and tables
  • BigQuery Job User (roles/bigquery.jobUser): To run queries and jobs
  • BigQuery Metadata Viewer (roles/bigquery.metadataViewer): To view dataset and table metadata (optional but recommended)

Alternatively, you can grant the following specific permissions:

  • bigquery.datasets.get
  • bigquery.tables.get
  • bigquery.tables.getData
  • bigquery.jobs.create
  • bigquery.jobs.get
tip

For security best practices, consider creating a dedicated service account specifically for AccountAim with only the minimum required permissions. Service accounts are recommended over user OAuth for production integrations.

Connection Parameters​

When setting up the BigQuery connection, you'll need to provide:

  • Project ID: Your Google Cloud project ID (e.g., my-project-12345)
  • Authentication Method: Choose between Service Account (JSON key) or OAuth2
  • Service Account JSON (if using service account): The JSON key file contents for your service account
  • OAuth2 Credentials (if using OAuth): Client ID and client secret for OAuth2 authentication
  • Default Dataset: The default dataset to connect to (optional)
  • Location: The BigQuery location/region (e.g., US, EU, asia-northeast1)

How to connect BigQuery​

To connect BigQuery to your AccountAim workspace, navigate to the Warehouse section and click "Add New" to add a new data source.

Step 1: Select BigQuery as your Source​

In the "Add New Record Source" modal, select "BigQuery" as your data source type.

Step 2: Choose Authentication Method​

Select your preferred authentication method:

  • Service Account (Recommended): Use a service account JSON key file
  • OAuth2: Use OAuth2 user authentication

Step 3: Enter Connection Details​

If using Service Account:​

  1. Project ID: Enter your Google Cloud project ID

    • You can find this in the Google Cloud Console or in your project settings

  2. Service Account JSON: Upload or paste your service account JSON key file

    • To create a service account:
      1. Go to Google Cloud Console → IAM & Admin → Service Accounts
      2. Click "Create Service Account"
      3. Give it a name (e.g., "accountaim-integration")
      4. Grant it the BigQuery Data Viewer and BigQuery Job User roles
      5. Create and download a JSON key file

  3. Default Dataset (optional): Enter the default dataset ID you want to connect to

  4. Location (optional): Enter the BigQuery location/region (e.g., US, EU)

If using OAuth2:​

  1. Project ID: Enter your Google Cloud project ID

  2. OAuth2 Setup: Follow the OAuth2 authentication flow

    • You'll be redirected to Google to sign in and grant permissions
    • AccountAim will request access to your BigQuery data

  3. Default Dataset (optional): Enter the default dataset ID you want to connect to

  4. Location (optional): Enter the BigQuery location/region

Step 4: Test Connection​

Click "Test Connection" to verify that AccountAim can successfully connect to your BigQuery project.

tip

If the connection test fails, verify:

  • Your project ID is correct
  • Your service account JSON is valid and not expired
  • The service account has the required permissions
  • BigQuery API is enabled in your Google Cloud project
  • Billing is enabled on your Google Cloud project

Step 5: Configure Sync Settings​

After a successful connection, configure your sync settings:

  • Sync Frequency: Choose how often AccountAim should sync data from BigQuery
    • Options: Manual, Hourly, Daily, Weekly
  • Objects to Sync: Select which tables, views, or datasets you want to sync
  • Sync Mode: Choose between full sync or incremental sync (if supported)
  • Query Timeout: Set a timeout for queries (optional, defaults to 5 minutes)

Step 6: Complete Setup​

Click "Save" to complete the BigQuery connection setup. AccountAim will perform an initial sync to import your selected data.

Sync Schedule​

Customers have the ability to set the sync frequency in AccountAim to control data freshness and query costs. By default, automatic syncing is enabled with a daily schedule.

Manual syncing can be enabled by navigating to the Settings page in the AccountAim app and adjusting the sync schedule for your BigQuery connection.

tip

Consider your BigQuery query costs when setting sync frequency. BigQuery charges based on data processed, so more frequent syncs of large tables will result in higher costs. Use table filters and date ranges to minimize data processing.

Supported Objects​

AccountAim can connect to and sync data from:

  • Tables: All standard BigQuery tables (native and external)
  • Views: All BigQuery views (standard and materialized views)
  • Datasets: Entire datasets can be selected for syncing
  • Partitioned Tables: Support for partitioned tables with automatic partition filtering
  • Clustered Tables: Support for clustered tables

Best Practices​

  1. Use Service Accounts: Service accounts are more secure and reliable than OAuth2 for automated integrations
  2. Minimize Permissions: Grant only the minimum required permissions to your service account
  3. Selective Syncing: Only sync the tables and datasets you actually need in AccountAim
  4. Monitor Costs: Keep an eye on your BigQuery query costs and adjust sync frequency as needed
  5. Use Filters: Configure table filters to only sync relevant data (e.g., date ranges)
  6. Partition Awareness: For partitioned tables, configure date filters to only sync recent partitions
  7. Location Matching: Ensure the BigQuery location matches your data location to avoid cross-region transfer costs

Troubleshooting​

Connection Issues​

If you're experiencing connection problems:

  • Verify your project ID is correct (not the project name)
  • Check that your service account JSON key is valid and not expired
  • Ensure the service account has the required IAM roles
  • Verify that BigQuery API is enabled in your Google Cloud project
  • Check that billing is enabled on your Google Cloud project
  • Ensure the dataset and table names are correct (case-sensitive)

Sync Issues​

If data isn't syncing properly:

  • Verify the service account has SELECT permissions on the objects you're trying to sync
  • Check that the tables/datasets exist and are accessible
  • Review sync logs in AccountAim for specific error messages
  • Ensure you have sufficient BigQuery quota for queries
  • Check for any data type compatibility issues

Cost Optimization​

To reduce BigQuery costs:

  • Use table filters to limit the amount of data processed
  • For partitioned tables, sync only recent partitions
  • Reduce sync frequency for large tables
  • Use column selection to only sync needed columns
  • Consider using materialized views for frequently accessed data

For additional support, contact AccountAim support.