Skip to main content
Gainsight Inc.

Google BigQuery Connector

IMPORTANT: Gainsight is upgrading Connectors 2.0 with Horizon Experience. This article applies to tenants which have been upgraded to the Horizon Experience for Connectors 2.0. If you are using Connectors 2.0 with the previous version, you can find the documentation here.

​This article explains how to create a connection from Gainsight to BigQuery, create a data job, merge datasets, and configure job or Job chains in Gainsight.

Note: This document provides general guidance on creating a Connection and setting up Jobs because the use case of each job is different and needs unique configuration. For detailed information on creating any Job, refer to the Jobs List Page in the Additional Resource section at the end of this article.

Overview

Google BigQuery is a serverless, highly scalable cloud data warehouse designed for business agility. Users can store datasets in Google BigQuery and integrate these datasets into Gainsight org.

Integration of Gainsight with Google BigQuery allows you to ingest customer data into Gainsight standard objects (Company/Person, etc.) and custom objects.

Benefits of this integration

  • Derive business solutions offered by Gainsight on Google BigQuery data like Renewal Management, Customer Health Scoring, Usage data analytics using Adoption Explorer, etc.
  • Coordinating across Customer Success Teams by creating Call to Actions (CTA) on Google BigQuery data in Gainsight.

Create Credentials in Google BigQuery

Credentials from Google BigQuery are used to ensure that Gainsight has access to BigQuery data. Credentials contain Client ID and Client Secret which are needed to create a BigQuery connection in Gainsight from the Administration > Connectors 2.0 > Connections page.

Prerequisites: Admins or Users having access to the BigQuery project can create the Credentials in Google BigQuery.

To create credentials in Google BigQuery:

  1. Log into your Google BigQuery Instance.
  2. Navigate to APIs & Services > Credentials. Credentials page is displayed.

Credentials1.png

  1. Click +CREATE CREDENTIALS and select OAuth client ID. Create OAuth client ID page is displayed.

Credentials2.png

  1. From the Application type dropdown, select Web application.

Credentials3.png

  1. Enter Name of OAuth 2.0 client.
  2. Click +ADD URI in the Authorized redirect URIs section, and enter the following URLs based on your Gainsight org location:
  1. Click CREATE. New credentials are created successfully.

Credentials4.png

  1. Click the credentials name in the Credentials page of BigQuery to view the Client ID and Client secret as shown in the following image:

Credentials5.png

Copy the Client ID and Client Secret to use them while creating the Google BigQuery connection from Gainsight.

Create Connection

Create Connection is the first step towards the configuration of a Gainsight connector to sync data from the respective source system to Gainsight.

To create a Google BigQuery connection:

  1. Navigate to Administration > Connectors 2.0.
  2. Click Create Connection. The Create Connection dialog appears.
  3. Select BigQuery from the Connector dropdown list.
  4. Enter the following details:
  • Name of the connection: Enter name of the connection.
  • Project Id: Enter the ID of a Project created in Google BigQuery.
  • Dataset Id: Enter the ID of a Dataset created within the Project in Google BigQuery.
  • OAuth API Key: Enter the Client ID collected from Google BigQuery in the APIs & Services > Credentials page.
  • OAuth Secret Key: Enter the Client Secret collected from Google BigQuery in the APIs & Services > Credentials page.
  1. Click Authorize to validate the connection.

After you click Authorize, the BigQuery OAuth page appears, complete the authorization by entering the BigQuery credentials.

Note: Gainsight will have access to fields for which access is granted to the user used in OAuth, but will pull only the fields mapped in the jobs.

Create a Job

Admins can create jobs from the Jobs page to sync data from required source objects (tables and views) with Gainsight. After selecting the Data Source, you can view all the BigQuery tables and views in the left pane Data Source source field. Drag and drop a source object to the Preparation screen to start creating a job.

Note: To sync newly created views from Bigquery to Gainsight, admin must re-authorize the BigQuery connection from the Connections page.

For more information, refer to the Jobs List Page in the Additional Resources section at the end of this article.

BQ_JOB.png

If the data jobs in a connection are dependent on each other, create a Job chain and configure the schedule to the Job Chain. For more information, refer to the Job Chain page in the Additional Resources section at the end of this article.

Merge Datasets

You can merge two or more datasets together and create an output dataset. For example, you can merge datasets related to Bitcoin Cryptocurrency and Company datasets to know the list of transactions for each company and create an output dataset such as Company ARR. For more information on Merge, refer to the Jobs List Page in the Additional Resource section at the end of this article.

Transform Data

In the Preparation step of a connector job, admins can Transform data and add Case Fields to get more meaningful insights from the customer data.

Business use case: The Transform function provides the capability to create or modify new case fields. The new case fields can be used to modify the external field as per the consumption requirement in Gainsight’s data model. Case fields can be defined to populate different values for different case conditions. For example, External picklist values such as New, Open, and Closed can be modified to Active and Inactive to match Gainsight’s picklist values.

Add Destination

Once the final output dataset is prepared, you can add a destination to the output dataset to sync data from the source to the target Gainsight object. For more information on how to add destinations, refer to the Jobs List Page article in the Additional Resources at the end of this article.

Direct Mappings

To sync data from an external system to Gainsight using Direct Mappings, you must map fields from the external system's source objects to Gainsight's target objects. The data sync happens based on the configured field mappings. For more information on Direct Mapping, refer to the Jobs List Page article in the Additional Resources at the end of this article.

Derived Mapping

(Optional) You can use Derived Mappings to populate values in an object's field (of the GSID data type) with values from the same or another standard Gainsight object. Lookup is used to accomplish this, and you can match up to six columns in the lookup.

Note: To use Derived Mappings, your Target Object must have at least one field of data type GSID.

For more information on the derived mappings, refer to the Jobs List Page article in the Additional Resources at the end of this article.

Additional Resources

For more information about Connectors 2.0, refer to the following articles:

  • Was this article helpful?