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.

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 Service Account, Project ID, Dataset, and Service Account JSON key configured in Google Cloud, you must create them before proceeding. These components are required to ensure CleverTap can securely access, store, and process your data.

To create each resource, perform the following steps:

1. Create a Service Account
2. Assign BigQuery Permissions
3. Create or Select 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 to 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.

   

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, refer to BigQuery IAM roles.

Create or Select BigQuery 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.

To set up CleverTap dashboard for intergration, perform the following steps:

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 addresses common questions about managing BigQuery connections and imports in CleverTap.

How can I delete a connection that has running imports?

Go to Import Connections and select the connection. Click on Delete and review any active imports. If there are any imports in Ready, Schedule, paused, or running states, stop all active imports associated with that connection. Ensuring all imports are stopped, click Delete again and confirm your action.

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 for Import.

Next Steps