Google BigQuery Connector
Gainsight NXT
| 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 to admins about how to create a connection from Gainsight to BigQuery, create a data job, merge datasets, transform, 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:
- 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/api/accounts/oauth2callback?accountType=BIGQUERY
- United States:
- 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
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.
- From the Connector dropdown list, select BigQuery.
- Enter the details in the following fields:
- 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, where you can 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 Service Account in Google BigQuery
A service account is a Google account that is associated with your Google cloud project. You can use a service account to authenticate the BigQuery connector at Gainsight rather than an end-user's credentials.
For this authorization, a service account user must be created, whose json key files will be utilized for authorization.
Prerequisites: Admins who have access to the BigQuery project can create the service account in Google BigQuery.
To create a service account in Google BigQuery:
- Log into your Google BigQuery Instance.
- Navigate to IAM & Admin > Service Accounts. The Service Account page appears.
- Click + CREATE SERVICE ACCOUNT. The Create service account page appears.
- In the Service account name field, enter the name of the account.
- Click CREATE AND CONTINUE.
- Select the required roles from the dropdown.
Note : Service Account users should have BigQuery Data Transfer Service Agent and BigQuery Data Viewer roles.
- Click DONE. The newly added service account is added to the Service accounts page.
Create a Private Key for the New Service Account
Each service account consists of a private key. The Service Account Credentials API uses this internal key pair to create JSON Web Tokens (JWTs).
To create a Private Key:
- Navigate to IAM & Admin > Service Accounts. The Service accounts page appears.
- Click the required accounts email id created in the above section.The Service account details page appears.
- Click the KEYS tab.
- Click the ADD KEY button and select Create new key. The Create private key window appears.
- Select the JSON option and click CREATE. The Private key saved to your computer confirmation window appears with the JSON file downloaded in your system.
Service Account Authorization
The BigQuery Connection can now be authorized by the service account. In the Gainsight application, the actions listed below are done.
Prerequisites: The service account user should have below roles:
- BigQuery Data Transfer Service Agent - that creates BigQuery jobs in consumer projects to fetch data when a query is executed.
- Big Query Data Viewer - that reads data and metadata from the table or view.
To authorize a Service Account:
- Navigate to Administration > Connectors 2.0.
- Click Create Connection. The Create Connection dialog appears.
- From the Connector dropdown list, select BigQuery.
- Enter the name of the connection.
- From the Authorization Type section, select Service Account.
Note: Re-authenticate the connection when you switch from Service Account to Oauth Authorization type.
- Enter the details in the following fields:
- 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.
- Upload Service Account Key File(json) in the given field.
Note: In this case, you must use the same JSON file that was downloaded as a private key for the new service account.
- Click Save.
This completes the Service account authorization process in the Gainsight application.
Context Menu Options
Once you authorize the connection, configure your connection with one of the following context menu options, as required:
- Edit Connection: Use this option to modify the Google BigQuery connection details.
- Re-Authorize Connection: If the existing connection is revoked or Google BigQuery org credentials are changed, use this option to re-authorize the Google BigQuery connection and provide the new set of credentials.
Note: If you change the password of the Google BigQuery org then the connection from Gainsight with Google BigQuery is revoked.
- Revoke Authorization: This option revokes the connection from Gainsight with Google BigQuery.
- Delete Connection: Delete the Google BigQuery connection, if the associated Job Chains and data jobs are deleted.
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.
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.
Job Chain
The Job Chain feature helps simplify the process of scheduling a group of related Jobs that you need to run in a particular sequence. This sequence is based on the scheduled time of the Job Chain.
For example, if Job A and B are in a sequence, ‘Job B‘ automatically starts as soon as ‘Job A’ completes, and there is no need to schedule a job separately.
Note: If a Job is added in a Job Chain, then the individual job schedule is ignored and the Job Chain’s schedule is considered and the jobs are run as per the sequence of the jobs in the Job Chain.
For more information on the Derived Mappings, refer to the Job Chain Page in the Additional Resources section at the end of this article.
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.