Skip to main content
Gainsight Inc.

Use Data from Google BigQuery in Data Designer

  1. Last updated
  2. Save as PDF
IMPORTANT: This feature will be available for customers from April 27, 2024 onwards.

This article helps admins create datasets by ingesting data from Google BigQuery using Data Designer.

Overview

This article provides a comprehensive guide for administrators on how to create and manage datasets in Data Designer by using Google BigQuery as a data source.

Business Use Case: If your organization uses Google BigQuery to manage business data, you can integrate this data with Gainsight. This integration allows you to combine BigQuery data with other customer information in Gainsight's Data Designer. You can then use these datasets for reporting, creating rules, and Journey Orchestrator (JO) campaigns.

Prerequisites

The following pre-requisites are required for you to create datasets in Data Designer:

  • Establish a connection: Create a Google BigQuery connection on the Connectors 2.0 page.
    For more information on creating a Google BigQuery connection, refer to the Google BigQuery Connector article.
  • Data Transfer Permissions: Ensure you have granted the create stage permission on the schema to allow Gainsight to import data sets larger than 5GB using the Google BigQuery connector.

Create Google BigQuery

To create Google BigQuery Dataset:

  1. Navigate to Administration > Data Designer. The Design List page appears.
  2. Click New Design. The New Design page appears where you can see four tabs: Details, Preparation, Explore, and Configure.

Data Designer workspace in Gainsight showing a list of designs with options to create a New Design.

Details

Enter the following information in the Details tab:

Note: The Details tab allows you to add basic information related to the dataset that is being created.

  1. In the Design Name, enter the name of the Data Design.
  2. In the Select Folder, select the folder in which the design needs to be saved.
  3. (Optional) In the Description, enter the details in the Description textbox as required.
  4. Click Prepare. The Preparation tab appears.

Dialog box for creating a New Design in Data Designer with fields for design name, folder selection, and description.

Preparation of Dataset

In the preparation stage, you can create the dataset by selecting the Data Source and its objects.

To prepare a Dataset:

  1. From the Data Source drop-down list, select the required Google BigQuery data source. You can see all the objects available under the selected Google BigQuery source.
    Note: You can see the Google BigQuery data source here if you have established a Google BigQuery connection on the Connectors 2.0 page as shown in the Prerequisites.
  2. Drag the required objects from the left pane to the canvas screen. The Select Fields window appears.

Data source selection interface in Data Designer, with BIGQUERY selected from a dropdown menu

Select Fields

In the Select Fields side pane, all the fields available for the source object are displayed.

To select Fields:

  1. Select the required fields you want to add to the dataset.
  2. Click Select. The Object slide-out pane appears. This page displays the three tabs:

Data Designer field selection for a new BigQuery dataset with a checkbox to select Region field.

Details and Summary Tab

The following information is available in the Details & Summary tab:

  • In the Output Dataset Name, enter the dataset name.
  • (Optional) In the Description textbox, enter the description of the dataset as required.
  • Fields & Filters: You can view a list of all Fields (including Group By, if any) and Filters in the Dataset.

Details and summary view in Data Designer for BigQuery data source with the 'Region' field added

Fields Tab

This displays the following information:

  • Fields: The name of the fields selected in the above step.
  • Group By: You can select the required fields to Group By, to slice and dice the data in the dataset. Once you select a Field to Group By, all the other fields in the dataset will become aggregated.
  • Aggregation: You can select the required aggregation type from the Aggregation dropdown list.
  • Display Name: By default, the field name appears in the Display Name textbox. You can modify it as required. Display names are shown in the Datasets and reports.

To add additional fields:

  1. Click Add Fields, if additional fields are to be added.
  2. Click the Settings/Gear icon to make changes to numeric data type fields. For more information refer to the Preparation details in Data Designer article.
  3. Click the Delete/Trash icon to delete the field from the dataset.
  4. Click Save.

Configuration screen in Data Designer showing the 'Region' field selected for the '20M' BigQuery data source.

Filters Tab

To apply Filters:

  1. Navigate to the Filters tab.
  2. Click Add Filter.
  3. From the Field dropdown list, select the field to which the filter is to be applied.
  4. Choose the Operator and then input the data in the Value textbox.
    Notes:
    • You can also Add more filters by clicking the + icon next to Value textbox.
    • You can Delete a filter by clicking the x icon.
    • You can add advance filters such as (A OR B) AND C, type in your desired expression in the Advanced Logic text box.
  5. Click Save.

Filter configuration in Data Designer showing an option to add an advanced filter for dataset '20M'

Edit Datasets

To edit a Dataset:

  1. Click the Pencil icon (or) click the three-vertical dots menu.

  2. Click Edit to modify a dataset. You will land on the following page as shown below.

Editable dataset '20M' in Data Designer with options to add or modify fields in a BigQuery data source.

Edit the Output Dataset Name, Description, Fields, and Filters as required.

You can also create more Datasets from Salesforce, MDA data, Amazon S3, and Databricks, and perform Merge, Transform, and other actions on the created Datasets. Then, you can proceed to the Explore tab to analyze the data and create reports.

Additional Resources