Overview

Configuring Google BigQuery with CleverTap enables seamless data import, ensuring synchronization and access to relevant information for analysis, personalized engagement, and data-driven growth.

📘

Note

BigQuery is a private beta release. Contact your Customer Success Manager for access. This document covers importing data from BigQuery into CleverTap. For exporting data from CleverTap to BigQuery, refer to BigQuery Export.

Quick Start Guide for Existing Users

Prerequisites for Integration

Before configuring BigQuery in CleverTap, ensure that the required Google Cloud resources and access permissions are available.

• CleverTap Access to configure BigQuery
• A Google Cloud Project with BigQuery enabled
• A BigQuery Project ID and dataset
• A Service Account JSON key with required permissions

Set Up BigQuery for Integration

You can set up BigQuery using one of the following options:

• For new users who need to provision BigQuery resources from scratch: Create a Service Account, assign BigQuery permission, create or select a Dataset, and create a Service Account JSON key.

• For users who already have configured resources in BigQuery: Use Existing BigQuery Credentials

Create New BigQuery Setup

If you do not already have a dataset, service account, or JSON key configured in Google Cloud, you must create them before connecting to CleverTap.

To create each resource, perform the following steps:

1. Create a Service Account
2. Assign BigQuery Permissions
3. Create a Dataset
4. Create a Service Account JSON Key

Create Service Account

To allow CleverTap to access your BigQuery environment, first create a dedicated service account:

1. In the Google Cloud Console, click on navigate to IAM & Admin > Service Accounts.

2. Click Create Service Account.

   

3. Enter a Service account name (for example, clevertap-bq-sa).

4. Click Create and continue, then Done.

   

📘

Best Practices

• Use a dedicated service account for each CleverTap workspace to maintain clear access boundaries.
• Avoid long-lived service account keys. Rotate or revoke keys regularly to immediately remove CleverTap access when required.

Refer to Google Cloud’s official guidance on managing service account keys.

Assign BigQuery Permissions

Assign the required roles to the service account:

1. In the Google Cloud Console, click on navigate to IAM & Admin > Service Accounts.

2. Locate the service account and click Manage Permissions.

   

3. Click on Manage Access.

   

4. Assign the following required roles to the service account.

   Role	Purpose
   BigQuery Job User	Allows CleverTap to run BigQuery jobs
   BigQuery Data Editor	Allows read/write access to datasets

5. Click Save. To learn more, see BigQuery IAM roles.

Create Dataset

Create a dataset for CleverTap to store and query data:

1. Go to BigQuery in the Google Cloud Console.

2. Select your project and click Create dataset.

3. Enter:

   • Dataset ID (for example, clevertap_dataset)
   • Location (must remain unchanged)

4. Click Create dataset.

Or use SQL:

CREATE SCHEMA IF NOT EXISTS `[ProjectID].[DatasetID]`;

Create Service Account JSON Key

Generate a JSON key that CleverTap will use for authentication:

1. In the Google Cloud Console, click on navigate to IAM & Admin > Service Accounts.

2. Open the Keys tab.

3. Click Add key > Create new key.

   

4. Select JSON and click Create.

   

5. Download and securely store the JSON file.

Use Existing BigQuery Credentials

If you already have BigQuery set up, perform the following steps to find each detail on the BigQuery dashboard.

• Find Project ID
• Find Existing Dataset ID
• Find Service Account
• Download Service Account JSON key

Find Your Project ID

To configure the integration, you need your BigQuery project identifier. Follow these steps to find it:

1. Open Project Picker next to the Google Cloud logo.

2. Copy the Project ID of the target project.

   

Find Existing Dataset ID

To configure the integration, identify the dataset that CleverTap will use to read or write data in BigQuery.

1. Go to BigQuery.
2. Expand your project to view datasets.
3. Select the dataset you intend to use with CleverTap.

Or use SQL:

SELECT schema_name FROM 'projectID'.INFORMATION_SCHEMA.SCHEMA;

Find a Service Account

Locate an existing service account with the required permissions, or create one if needed.

1. Navigate to IAM & Admin > Service Accounts.

2. Select an existing service account with sufficient permissions, or create a new one with the following permissions.

   Role	Purpose
   BigQuery Job User	Allows CleverTap to run BigQuery jobs
   BigQuery Data Editor	Allows read/write access to datasets

Download or Create a Service Account JSON Key

To authenticate the integration, download an existing service account JSON key or create a new one from the Google Cloud Console.

1. Select the service account.
2. Go to the Keys tab.
3. Create a new JSON key if needed.

Set Up CleverTap Dashboard for Integration

After preparing your BigQuery project, dataset, and service account, connect BigQuery to CleverTap using the collected values.

Integrate BigQuery with CleverTap

1. Go to Settings > Partners > BigQuery and select Add Database.
2. Enter the following details:

3. Click Test Connection or Save:

   • Test Connection: Verifies project access, dataset validity, and service account permissions.
   • Save: Stores the connection for use with imports.

4. After saving the connection, navigate to Create Import in the Import Connections dashboard.

FAQs

This section answers common questions related to managing BigQuery connections and imports within CleverTap.

How can I delete a connection that has running imports?

Go to Import Connections, select the connection, click Delete, and review the running imports. If there are any imports in ready, scheduled, paused, or running state, it stops all these active imports associated with the connection. Once done, click Delete and confirm.

How can I filter import connections?

Use the filters on Import Connections to refine the list:

• Connected On: Filter by creation date.
• Connected By: Filter by the user who created the connection.

How can I whitelist IPs for CleverTap integration?

To ensure seamless communication between CleverTap and your systems, whitelist the required IP ranges. To access the list of IPs to whitelist for import integrations, refer to CleverTap IP Ranges.