Skip to main content
Gainsight Inc.

Google BigQuery Connector

 

IMPORTANT NOTE: 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 Preparation of Connector Jobs 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 include:

  • 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.

Note: Only 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

  1. 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.

For more information on how to create and authorize a connection, refer to the Connections List Page in the Additional Resource section at the end of this article.

Create a Job

Once a connection is established, you can create Jobs. Data from the source system is synced to the target Gainsight objects through these jobs.You can create a dataset from one source object, similarly you can create as many datasets as you need to create a Job.

To Create a Job:

  1. Navigate to the Administration > Connectors 2.0 > Jobs page.
  2. Click Create Job. Create Job dialog appears.
  3. In the Name of the Job text box,enter a unique Job name
  4. Click Next. Job Preparation page appears.
  1. From the Data Source dropdown, select the required external system. Once you select the Data Source, you can see all the objects under the selected Data Source.
  2. Drag and drop the required object from the objects list to the Canvas screen. Dataset configuration page appears.

Fields

Following options are available in  the Fields tab:

  1. Reference for primary key:  From the dropdown list, select the reference type for primary key.
  2. Reference for last modified date: From the dropdown list, select the required Date field.
  3. Add Fields: Click Add Fields, to add multiple fields to the selection.
  4. Search: Use the Search textbox to search for any particular Field from the selected list of Fields.
  5. Fields: Displays the count and list of fields selected.
  6. Display Name: Default Field Name name appears in the Display Name text box, you can modify if required.
  7. Data Type: Displays the field data type.
  8. Delete: Click the x icon to delete a field from the dataset.
  9. Save: Click Save to save the changes made in the dataset.

Filters

Navigate to the Filters tab to add Filters to fields selected.

  1. Click Add Filter.
  2. Select the field you want to filter on.
  3. Choose the Operator and then input the data in the Value text box.
    Note:
    • Add more filters by clicking the + icon next to the Value text box.
    • Delete a filter by clicking the x icon.
    • Add advance filters such as (A OR B) AND C, type in your desired expression in the Advanced Logic text box.
  4. Click Save.

Summary

In the Summary tab, you can view a list of all Fields and Filters in the Dataset.

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 Preparation of Connector Jobs in the Additional Resource section at the end of this article. .

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 Add Destination, refer to the Preparation of Connector Jobs in the Additional Resource section at the end of this article.

Direct Mappings

In the Direct Mapping, you should map fields from the output dataset to the target object in the field mappings. Data sync happens from the source fields of the external system to the target fields of Gainsight, per field mappings. For more information on Direct Mapping, refer to the Preparation of Connector Jobs in the Additional Resource section at the end of this article.

Derived Mapping

This is optional and you must configure the derived mappings only if you want to populate values into the target fields of data type GSID. GSID values are populated from the same or another object through lookup. In this stage, you can create Lookup mapping in a data sync job. You can have a lookup to the same object or another standard object and match up to six columns. Once the required matching is performed, you can fetch Gainsight IDs (GSIDs) from the lookup object into GSID data type fields. For more information on the derived mappings, refer to the Preparation of Connector Jobs in the Additional Resource section at the end of this article.

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

Configure Job or Job Chain

Admins can configure the Schedule for the Job or Job Chain after its preparation.

If there are multiple Jobs in a Connection which are dependent on each other, Gainsight offers an option to create a Job Chain to sync data in sequence. For more information, refer to the Configure Job or Job Chain Schedule in the Additional Resource section at the end of this article.

Job Activities

You can view the Execution and Update Activities of all the data jobs in the Activity page. You can also download the error logs of the jobs from this page to help troubleshooting the configuration issues. For more information, refer to the Activity Page article in the Additional Resource section at the end of this article.

  • Was this article helpful?