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.
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:
- Log into your Google BigQuery Instance.
- Navigate to APIs & Services > Credentials. Credentials page is displayed.
- Click +CREATE CREDENTIALS and select OAuth client ID. Create OAuth client ID page is displayed.
- From the Application type dropdown, select Web application.
- Enter Name of OAuth 2.0 client.
- Click +ADD URI in the Authorized redirect URIs section, and enter the following URLs based on your Gainsight org location:
- Europe: https://eu-app.gainsight.com/v1.0/ap...tType=BIGQUERY
- United States: https://app.gainsight.com/v1.0/api/a...tType=BIGQUERY
- Click CREATE. New credentials are created successfully.
- Click the credentials name in the Credentials page of BigQuery to view the Client ID and Client secret as shown in the following image:
Copy the Client ID and Client Secret to use them while creating the Google BigQuery connection from Gainsight.
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:
- Navigate to Administration > Connectors 2.0.
- Click Create Connection. The Create Connection dialog appears.
- Select BigQuery from the Connector dropdown list.
- 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.
- 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.
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.
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.
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.
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.
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.
(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.