How to show Google BigQuery data in Dixa

One of the challenges customer support agents face is quickly accessing relevant information from various internal systems. This tutorial demonstrates how to display data from Google BigQuery in a custom card within Dixa conversations using FactBranch.

FactBranch is a powerful tool that connects to different data sources and displays information at your agent's fingertips. We will walk you through setting up your FactBranch account, connecting it to Google BigQuery, and creating a custom card in Dixa to display the data.

Prerequisites

Before getting started, you need the following:

1. A Google BigQuery account with a dataset and table.
2. A Dixa account with the ability to create custom cards.
3. A FactBranch account. You can start your 14-day free trial here.

Connect FactBranch to Google BigQuery

Once you are signed in to FactBranch, click on Data Sources from the left menu. Select Add data source at the top-right corner and choose Google BigQuery. Follow the prompts to authenticate and grant FactBranch read-access to your BigQuery account.

Once connected, you will see Google BigQuery listed under Data Sources in your FactBranch account.

Create a New Flow in FactBranch

After connecting Google BigQuery as a data source, it's time to create a data pipeline to query data from BigQuery. A flow in FactBranch represents a data pipeline that, in this case, is triggered when a user views a conversation in Dixa and the custom card gets loaded.

To create a new flow, click on Flows from the left menu and then on Create Flow.

Choose Show data in a Dixa custom card.

You'll see a basic flow that starts with a trigger and ends with a visual node to generate a UI displayed in the Dixa custom card conversation. To query the data from Google BigQuery, you'll need to add a REST API node. Click on Create node and select REST API.

In the REST API node head to the Authentication tab and select the Google BigQuery account we've connected previously.

Return to the Request body tab and enter the following details:

• HTTP Method: POST
• URL: https://bigquery.googleapis.com/bigquery/v2/projects/YOUR_PROJECT/queries

Replace YOUR_PROJECT with your actual project ID in Google BigQuery.

Next, you'll need to enter the request payload, which contains the SQL query to fetch data from BigQuery. Use the following as a starting point.

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

Replace YOUR_PROJECT, YOUR_DATASET and YOUR_TABLE with your actual project, dataset and table names in Google BigQuery.

Using placeholders

The payload above contains two levels of placeholders: In queryParameters we define a placeholder called email that we can use in the SQL query as @email. In parameterValue.value we use FactBranch's placeholder to supply the actual value, that we want to look up, to the request.

Create UI to Display Data in the Custom Card

In the Show data in Dixa flow, click Edit in the Render UI node to create a visual output that will be displayed in the custom card in Dixa.

An example template displaying the data in a label-value style and a table style simultaneously, that you can use as a starting point, is shown below:

<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 %}



<h3>As a table</h3>

<table class="ui_table">
  <tr>
    <th>Name</th>
    <th>Email</th>
    <th>Revenue</th>
  </tr>
  {% for row in data.rows %}
    <tr>
      <td>{{ row.f[0].v }}</td>
      <td>{{ row.f[1].v }}</td>
      <td>{{ row.f[2].v }}</td>
    </tr>
  {% endfor %}
</table>

Also check out our detailed documentation on the Render UI node

If everything is set up correctly, you should see the query result from Google BigQuery rendered in the custom card preview.

Add Custom Card in Dixa

Return to the Show data in Dixa flow and click on Config in the Trigger block to start setting up the custom card in Dixa. On the trigger config page you'll find all the information you need to create the custom card in your Dixa account.

Once you've followed the instructions, when you view a conversation in Dixa, you will see the FactBranch custom card displaying live data from your Google BigQuery database.

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