Wouldn't it be awesome to have an accurate estimate of how long it will take for tech support to resolve your issue? In this lab you will train a simple machine learning model for predicting helpdesk response time using BigQuery Machine Learning. You will then build a simple chatbot using Dialogflow and learn how to integrate your trained BigQuery ML model with your helpdesk chatbot. The final solution will provide an estimate of response time to users at the moment a request is generated!

The exercises are ordered to reflect a common cloud developer experience:

  1. Train a Model using BigQuery Machine Learning
  2. Deploy a simple Dialogflow application
  3. Use an inline code editor within Dialogflow for deploying a Node.js fulfillment script that integrates BigQuery
  4. Test your chatbot

What you'll learn


Upload Helpdesk Data to BigQuery

Go to the Google Cloud Platform Console and verify your project is selected at the top.

Select BigQuery from the navigation menu in Google Cloud Console.

Select your Project ID on the left sidebar, then create a new dataset called helpdesk. Leave Default selected as the location.

In the sidebar, select the new helpdesk dataset you just created, and select Create Table.

Use the following parameters to create a new table. Leave the defaults for all other fields.

Click Create Table. This will trigger a job that loads the source data into a new BigQuery table. It will take about 30 seconds for the job to complete, and you can view it by selecting Job History from the sidebar on the left.

In Query editor, execute the following query and examine the data.

SELECT * FROM `helpdesk.issues` LIMIT 1000

In the next query, we will use the data fields category & resolutiontime to build a machine learning model that predicts how long it will take to resolve an issue. The model type is a simple linear regression, and the trained model will be named predict_eta in our helpdesk dataset. The query takes about 1 minute to complete.

CREATE OR REPLACE MODEL `helpdesk.predict_eta` 
OPTIONS(model_type='linear_reg') AS
 resolutiontime as label

Run the following query to evaluate the machine learning model you just created. The metrics generated by this query tell us how well the model is likely to perform.

WITH eval_table AS (
 resolutiontime as label
  ML.EVALUATE(MODEL `helpdesk.predict_eta`,
    TABLE eval_table)

Using the evaluation metrics, we can see that the model doesn't perform very well. When the r2_score and explained_variance metrics are close to 0, our algorithm is having difficulty distinguishing the signal in our data from the noise. We are going to use a few more fields during training and see if there is an improvement: seniority, experience & type. Run the query below.

CREATE OR REPLACE MODEL `helpdesk.predict_eta` 
OPTIONS(model_type='linear_reg') AS
 resolutiontime as label

Now, run the following query to evaluate the updated machine learning model you just created.

WITH eval_table AS (
 resolutiontime as label
  ML.EVALUATE(MODEL `helpdesk.predict_eta`,
    TABLE eval_table)

After adding the additional fields during training, we can see that our model has improved. When the metrics r2_score and explained_variance are close to 1, there is evidence to suggest that our model is capturing a strong linear relationship. We can also see that our *_error metrics our lower than before, which means our model will likely perform better.

Now we can execute a query to get a prediction of resolution time for a given scenario:

WITH pred_table AS (
  5 as seniority,
  '3-Advanced' as experience,
  'Billing' as category,
  'Request' as type
  ML.PREDICT(MODEL `helpdesk.predict_eta`,
    TABLE pred_table)

When seniority is 5, experience is 3-Advanced, category is Billing, and type is Request, our model is saying that the average response time is 3.74 days.

  1. Go to the Google Cloud Platform Console.
  2. Verify your project is selected at the top, and then go to the APIs overview by selecting APIs & Services -> Dashboard.

Select APIs and Services Dashboard

  1. In the list of APIs, select Dialogflow API. Click the Dialogflow agent tab.
  2. If you don't see DialogFlow in the list:

Click on Enable APIs and Services as shown below

Search for DialogFlow

Click on the Dialogflow API

Enable the API

  1. Click on Dialogflow agent in the sidebar and then select Open or create an agent at dialogflow.com. Make sure to use the correct account when logging in, and then accept the terms.

  1. Specify the name of your agent and other properties such as language and time zone.

When you're ready, click Create.

To save time, instead of configuring an agent from scratch, we can import the intents and entities for an existing helpdesk agent.

Import an IT Helpdesk Agent

Once the import completes, use the sidebar to navigate to Intents. Examine the intents that were imported. Submit Ticket is the main intent, which has the follow-up intents Submit Ticket - Name & Submit Ticket - Issue Category. Submit Ticket - Name is used to collect a user's name and Submit Ticket - Issue Category is used to collect the issue description from the user and automatically infer a support category.

Use the sidebar to navigate to Entities and look at the @category entity. This entity will be used to map a request description that the user provides to support category. The support category will be used for predicting response time.

Click on Fulfillment in the left panel and switch the Inline Editor toggle to "Enabled" and select "Deploy".

Copy the following code and paste it in the index.js tab, replacing the existing content. In the following code, you must update the variable BIGQUERY_CLIENT to reference your project id. Replace your-project-id with the project id of your project.

 * Copyright 2017 Google Inc. All Rights Reserved.
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *    http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * See the License for the specific language governing permissions and
 * limitations under the License.

'use strict';

const functions = require('firebase-functions');
const {WebhookClient} = require('dialogflow-fulfillment');
const {Card, Suggestion} = require('dialogflow-fulfillment');
const BIGQUERY = require('@google-cloud/bigquery');

  projectId: 'your-project-id' // ** CHANGE THIS **

process.env.DEBUG = 'dialogflow:debug'; 

exports.dialogflowFirebaseFulfillment = functions.https.onRequest((request, response) => {
  const agent = new WebhookClient({ request, response });
  console.log('Dialogflow Request headers: ' + JSON.stringify(request.headers));
  console.log('Dialogflow Request body: ' + JSON.stringify(request.body));

  function welcome(agent) {
    agent.add(`Welcome to my agent!`);

  function fallback(agent) {
    agent.add(`I didn't understand`);
    agent.add(`I'm sorry, can you try again?`);

    function ticketCollection(agent) {
        const OPTIONS = {
                    query: 'WITH pred_table AS (SELECT 5 as seniority, "3-Advanced" as experience,"' 
                    + request.body.queryResult.outputContexts[0].parameters.category 
                    + '" as category, "Request" as type) ' 
                    + 'SELECT cast(predicted_label as INT64) as predicted_label ' 
                    + 'FROM ML.PREDICT(MODEL helpdesk.predict_eta,  TABLE pred_table)',
                    timeoutMs: 10000,
                    useLegacySql: false,
                    queryParameters: {}
        return BIGQUERY_CLIENT
            .then(results => {
                const ROWS = results[0];
                console.log('SQL Completed ' + ROWS[0].predicted_label);
                    + ', your ticket has been created. Someone will you contact shortly. '
                    + ' The estimated response time is ' + ROWS[0].predicted_label + ' days.');
                agent.add(new Card({
                  title: 'New ' + request.body.queryResult.outputContexts[0].parameters.category 
                  + ' Request for ' + request.body.queryResult.outputContexts[0].parameters["given-name"]
                  + ' (Estimated Response Time: ' + ROWS[0].predicted_label + ' days)',
                  imageUrl: 'https://developers.google.com/actions/images/badges/XPM_BADGING_GoogleAssistant_VER.png',
                  text: 'Issue description: ' + request.body.queryResult.queryText,
                  buttonText: 'Go to Ticket Record',
                  buttonUrl: 'https://assistant.google.com/'
                agent.setContext({ name: 'submitticket-collectname-followup', lifespan: 2});
            .catch(err => {
              console.error('ERROR:', err);
    // Run the proper function handler based on the matched Dialogflow intent name
    let intentMap = new Map();
    intentMap.set('Default Welcome Intent', welcome);
    intentMap.set('Default Fallback Intent', fallback);
    intentMap.set('Submit Ticket - Issue Category', ticketCollection);

Notice that our fulfillment script contains the ML.PREDICT function in Query statement. This is what is going to return a prediction of response time back to the client. Dialogflow will automatically categorize the ticket description and send the category to BigQuery ML for predicting issue response time.

Copy the following code and paste it in the package.json tab, replacing the existing content:

  "name": "dialogflowFirebaseFulfillment",
  "description": "Dialogflow Fulfillment Library quick start sample",
  "version": "0.0.1",
  "private": true,
  "license": "Apache Version 2.0",
  "author": "Google Inc.",
  "engines": {
    "node": ">=6.0"
  "scripts": {
    "start": "firebase serve --only functions:dialogflowFirebaseFulfillment",
    "deploy": "firebase deploy --only functions:dialogflowFirebaseFulfillment"
  "dependencies": {
    "firebase-admin": "^4.2.1",
    "firebase-functions": "^0.5.7",
    "dialogflow-fulfillment": "0.3.0-beta.3",
    "@google-cloud/bigquery": "^1.3.0"

Then click the Deploy button. Wait until you see a message that the deployment was successful. This may take a few minutes.

Enable Webhook for Fulfillment

Next, go back to Intents in the left panel. Click the down arrow next to Submit Ticket to reveal its follow-up intents.

Click on the last follow-up intent Submit Ticket - Issue Category to open it for editing.

Then click on the Fulfillment header at the very bottom to toggle the section. Verify that Enable webhook call for this intent is turned on.

When you're finished, click Save.

Test Your Chatbot!

At this point, the Dialogflow should be set up. Test it in the Try it now panel on the right by entering the following conversation:

  1. Hi
  2. I would like to submit a ticket
  3. My name is John
  4. I can't login

The output from #4 should look like:

Notice that our fulfillment script is also leveraging the Dialogflow Fulfillment Library to provide richer content to the end user on certain platforms.

Understanding the BigQuery Integration

Remember that our BigQuery ML model required other fields to return a prediction: seniority & experience. If this were a real world app, we could programmatically get the user's seniority & experience from a company database using the name or company ID they provide. For this example we assume 5 as seniority and 3-Advanced as experience.

To extend this example further, you could also collect the seniority and experience information from the chatbot user by leveraging the Slot Filling functionality in Dialogflow. Click here to learn more about Slot Filling in Dialogflow.

Dialogflow provides many types of integrations for your chatbot. Let's take a look at a sample web user interface for the chatbot.

Click on Integrations in the Dialogflow left panel.

Enable the Web Demo integration by flipping the switch.

Click on the URL link to launch Web Demo:

Start using the chat interface by typing in the Ask something... section! If you are using a Chrome browser, if you click the microphone icon and you can speak your questions to the chatbot. Start chatting with the chatbot using the following conversation:

Delete the Dialogflow Agent

Delete the Helpdesk Dataset

You built a custom machine learning model, and you're now a chatbot developer!

Check out these other resources: