In this tutorial, we'll walk you through how to display Google BigQuery data in Zendesk with FactBranch. We'll explain how to query your BigQuery dataset with FactBranch and how to generate a nice data display for FactBranch's Zendesk app. Follow along to learn the basics of FactBranch's BigQuery and Zendesk integration.

Connect FactBranch to your Google BigQuery account

First, you need a FactBranch account. If you haven't done so, start your free trial today and continue following this tutorial.

Then head to your list of data sources in FactBranch and add a new data source. Select Google BigQuery. Click on Connect and allow FactBranch to access your Google BigQuery datasets.

Create a Zendesk-App flow in FactBranch

Next we'll create a data pipeline. FactBranch flows can contain multiple nodes, that access data, transform data or generate an output. In our case the flow will query your BigQuery dataset and generate a display output for the FactBranch app in Zendesk.

Go to your list of flows in FactBranch and add a new flow. Select Show data in the FactBranch Zendesk app.

To learn more about the flow system, check out the introduction to the flow system and how to edit a flow in our docs.

Run a Google BigQuery query from FactBranch

We'll use BigQuery's API to run a SELECT query on a simple dataset. This is very easy, but you need to include a few settings that we've collected here so you don't have to wade through BigQuery's documentation.

Add a REST API node by clicking on the Create node button and selecting REST API.

The REST API node is created outside the flow. Drag and drop it inbetween the Zendesk App Trigger and the Zendesk App Display nodes.

Click on Edit in the REST API node to open its configuration.

Now tell the API node to use the BigQuery account we've connected earlier by clicking on Authentication > Select a data source > Google BigQuery.

Return to the Config section and select POST as the request method and copy/paste the following URL:

https://bigquery.googleapis.com/bigquery/v2/projects/<PROJECT_ID>/queries

In this URL replace <PROJECT_ID> with the ID of the project in your Google account that contains your BigQuery dataset.

Then copy/paste the following payload as a scaffold for the query you'd like to run:

{
  "query": "SELECT * FROM <TABLE_NAME> WHERE email=@email",
  "defaultDataset": {
    "datasetId": "<DATASET_ID>",
    "projectId": "<PROJECT_ID>"
  },
  "useLegacySql": false,
  "queryParameters": [
    {
      "name": "email",
      "parameterType": {
        "type": "STRING"
      },
      "parameterValue": {
        "value": $data.email
      }
    }
  ]
}

Your API node should now look something like this:

There are several important things going on here.

First replace <TABLE_NAME>, <DATASET_ID> and <PROJECT_ID> with your own values.

The property called "query" contains the query you'd like to run. In this example we're defining and using a named placeholder called @email.

Using placeholders in the BigQuery query

You can't use FactBranch's own placeholder syntax in the query directly. Instead we'll use BigQuery's parameterized queries. This way BigQuery takes care of preventing SQL injections for us.

As you can see we're using BigQuery's convoluted syntax inside the queryParameters property to define a parameter of type string that we're calling email. We're giving it the value $data.email - this is now FactBranch's placeholder syntax and FactBranch will replace this with the value of $data.email.

When you hit Run, you should see the results on the right.

Generate the output for the Zendesk app

In the Zendesk App Display node you'll see the test query results from the BigQuery node. The button Generate template will try to generate a template for you, but with BigQuery's deeply nested syntax it will likely fail to surface the fields you're interested in. You're much better off using the following HTML template as a starting point for your own HTML code.

<h3>Customer data</h3>
{% for row in data.rows %}
  <div class="ui_item">
    <div class="ui_item__label">Name</div>
    <div class="ui_item__value">{{ row.f[0].v }}</div>
  </div>

  <div class="ui_item">
    <div class="ui_item__label">Email</div>
    <div class="ui_item__value">{{ row.f[1].v }}</div>
  </div>

  <div class="ui_item">
    <div class="ui_item__label">Revenue</div>
    <div class="ui_item__value">{{ row.f[2].v }}</div>
  </div>
{% endfor %}

Feel free to adapt this template to your own needs. The templating language is close to Liquid and Jinja2 and you can learn more about advanced HTML templating in our docs.

Install the FactBranch app in Zendesk

There are three different FactBranch apps for Zendesk - so make sure to pick the correct one from the Zendesk Marketplace:

• Zendesk Chat
• Zendesk Sell
• Zendesk Support

Click the Install button and follow the installation process. You might have to log in to your Zendesk account here.

When you're asked for the FactBranch API-key, copy/paste the API-key from the trigger config page in FactBranch. You can find it by going to your flow and clicking on Config in the Zendesk App Trigger box.

Optionally, you can select the roles and groups for which to install the app in your Zendesk account. Hit Install to finish setup.

Now, when you open a ticket in Zendesk Support, or any details pages in Zendesk Sell, the FactBranch app will load and display relevant data from your Google BigQuery dataset.

Next Steps

You've learned the basic setup how to show data from a BigQuery dataset in Zendesk using FactBranch.

Here are some ideas what to try next:

• Add some logic to your HTML template
• Add more nodes to the flow

If you are stuck or have general questions and ideas, reach out to support@factbranch.com.