Bigquery

With this integration, you can directly send notifications on your 360 degree data sitting in your data warehouse. This enables your data teams or product managers to automate user syncing, setup recurring user cohort sync and create subscriber lists on SuprSend. You can then trigger notification to this list using our broadcast API.

Getting Started

To start syncing data from your Bigquery database, you need to add this integration on the connector page.

1. Go to SuprSend dashboard -> Settings -> Connectors page. Here, you'll see the list of available connectors. If you have already setup any connectors in the past, you'll see a list of existing connectors. Click on "+New Connector" button to add the connector.
2. Click on Bigquery and add below information:
   1. Name - A name to uniquely qualify the connection as you'll see it in the connector list on your sync task. You can add the name of database here for easy identification.
   2. Service account key json - Steps to generate this are mentioned in the next section.

Step 1: Generating OAuth Client Key and Secret

1. Go to the Credentials page in the Google Cloud Platform console and click on "+Create Credentials". Select OAuth client ID from the options list.

2. If you have not created OAuth consent screen before, you will need to create one.

3. Since SuprSend will be used for your internal employees only, Use Internal option from the list

4. Enter required details like App name, your email, developer contact information, authorized domain etc.

5. After creating consent screen, Once on the Create OAuth client ID screen choose Web application from the Application type dropdown. Give your application a name. We recommend something like SuprSend to make it easy to identify and locate in the future.

Step 2: Granting permissions

SuprSend requires you to grant certain user permissions on your BigQuery warehouse to successfully access data from it. Perform the following steps in the exact order to grant these permissions:

Step 1: Creating a role and granting permissions

1. Go to the Roles section of Google Cloud platform dashboard and click on CREATE ROLE.

2. Fill in the details as shown

3. Click on ADD PERMISSIONS and add the following permissions

bigquery.datasets.get
bigquery.jobs.create
bigquery.jobs.list
bigquery.tables.get
bigquery.tables.getData
bigquery.tables.list

4. Finally, click on CREATE.

Step 2: Creating a service account and attaching a role to it

1. Go to Service Accounts and select the project which has the dataset or the table that you want to use and Click on CREATE SERVICE ACCOUNT

3. Fill in the Service Account details as shown below, and click on CREATE AND CONTINUE:

4. Fill in the Role details as shown below, and click on CONTINUE:

5. Click on DONE to move to the list of service accounts.

Step 3: Creating and downloading the JSON key

1. Click on the three dots icon under Actions in the service account that you just created and select Manage keys, as shown:

2. Click on ADD KEY, followed by Create new key, as shown:

3. Select JSON and click on CREATE

4. A JSON file will be downloaded on your system. This file is required in your database integration form.

Best Practices

Before you setup your database sync, you should take some measures to ensure the security of your customers’ data and limit performance impacts to your backend database. The following “best practice” suggestions can help you limit the potential for data exposure and minimize performance impacts:

1. Sync your read-only replica instance: Do not sync data directly from your main instance. Instead use a read-only data replica to minimize the load and avoid data loss on your main database.
2. User connected here should have minimal privileges: You should have a database user with minimal privileges. This person only requires read permissions with access limited to the tables you want to sync from.
3. Sync only the data that you’ll need: Limiting your query can improve performance, and minimize the potential to expose sensitive data. Select only the columns you need to either update user profile in SuprSend and to create list sync.
4. Use {{last_sync_time}} to limit query results: Make sure you use the {{last_sync_time}} variable in your recurring sync queries. It stores the timestamp of last successful sync in your list. Adding it in your where statement against datetime index can really speed up the query and limit the number of results returned in consecutive syncs. {{last_sync_time}} is stored in timestamp format. Use relevant cast expression to format it based on your column type.
5. Limit your sync frequency: Setup a sync frequency based on how frequently you want to send notifications on that list. If the previous sync is still in progress when the next interval occurs, we’ll skip the operation and catch up your data on the next interval. Skipped syncs show Ignored status in the logs. Frequently skipped operations may indicate that you’re syncing too often. You should monitor your first few syncs to ensure that you haven’t impacted your system’s performance.