CodeLab — Connect Actions on Google with BigQuery

Recently I created a new codelab that teaches how to connect AoG with BigQuery and I thought it would be nice to publish it over here.

But why do I need to connect AoG to BigQuery?

If in real estate it’s all about “Location Location Location”, then I digital product it’s all about “Data Data Data”. And if you planning to collect and analyze a lot of data, you should probably use BigQuery for that job.

In this codelab, I'm showing how to collect the basic available data we get from the Action and how to create a Dashboard that will help in reading the data.

Enjoy the lab

1. Overview

Actions on Google is a developer platform that lets you create software to extend the functionality of the Google Assistant, Google’s virtual personal assistant, across more than 500 million devices, including smart speakers, phones, cars, TVs, headphones, and more.

Users engage Google Assistant in conversation to get things done, like buying groceries or booking a ride (for a complete list of what’s possible now, see the Actions directory.) As a developer, you can use Actions on Google to easily create and manage delightful and effective conversational experiences between users and your own 3rd-party service.

The platform supplies basic analytics tools, like Active users and Abort rate. As a developer, you might want to have more control over the reports and create your own analytics report that includes the action data. In this codelab you will learn how to store all the activity log of your action into BigQuery in order to analyze it.

This codelab module is part of a multi-module tutorial. Each module can be taken standalone or in a learning sequence with other modules. In each module, you’ll be provided with end-to-end instructions on how to build an Action from given software requirements. We’ll also teach the necessary concepts and best practices for implementing Actions that give users high-quality conversational experiences.

This codelab covers intermediate level concepts for developing with Actions on Google. We strongly recommend that you familiarize yourself with the topics covered in Build Actions for the Google Assistant (Level 1) and Build Actions for the Google Assistant (Level 2) before starting this codelab.

What you’ll build

In this codelab, you’ll build a sophisticated conversational Action that log data using BigQuery.

What you’ll learn

• How to create DB table on bigQuery.
• How to load data from the action to the BigQuery.

What you’ll need

The following tools must be in your environment:

• An IDE/text editor of your choice, such as WebStorm, Atom or Sublime.
• A terminal to run shell commands with NodeJS, npm, and git installed.
• A web browser, such as Chrome.
• An account on GitHub.com. (You can create one for free.)
• Familiarity with JavaScript (ES6) is strongly recommended, although not required, to understand the webhook code used in this codelab.

2. The Technology

BigQuery

BigQuery is Google’s data analytics service for very large datasets. It has a RESTful API and supports queries written in SQL. If you have data with latitude and longitude values they can be used to query your data by location. The advantage is you can visually explore very large datasets to look at the patterns without having to manage any server or database infrastructure. You can get answers to your questions in a few seconds no matter how large your tables grow using BigQuery’s massive scalability and managed infrastructure.

3. Set up for local development

In Build Actions for the Google Assistant (Level 2), you used the Dialogflow and fulfillment to quickly get you started on your first Actions project.

For this codelab, you’re going to start with the Action from the Level 2 codelab.

If you’ve already completed the Build Actions for the Google Assistant (Level 2) codelab, move to the next step.

If you’re starting from scratch, do the following:

Download your base files

To get the base files for this codelab, run the following command to clone the GitHub repository for the Level 2 codelab.

git clone https://github.com/actions-on-google/codelabs-nodejs

This repository contains the following important files:

• level2-complete/functions/index.js. The Javascript file that contains your webhook’s fulfillment code. This is the main file that you’ll be editing to add additional Actions and functionality.
• level2-complete/functions/package.json. This file outlines dependencies and other metadata for this Node.js project. You can ignore this file for this codelab; you should only need to edit this file if you want to use different versions of the Actions on Google client library or other Node.js modules.
• level2-complete/codelab-level-two.zip. This is the agent file for the Level 2 codelab. If you’ve already completed the Level 1 and Level 2 codelab, you can safely ignore this file.
• For the sake of clarity, rename the /level2-complete directory name to /level4. You can do so by using the mv command in your terminal. For example:

$ cd codelabs-nodejs
$ mv ./level2-complete ./level4

Set up your project and agent

Next, you’ll need to set up the Actions project and the Dialogflow agent for your codelab.

1. Open the Actions Console.
2. Click on Add/import project.
3. Type in a Project name, like “actions-codelab-4”. This name is for your own internal reference; later on, you can set an external name for your project.
4. Click Create Project.
5. Rather than pick a category, click Skip on the upper-right corner.
6. Click Build your Action then click Add Action(s)
7. Click Add your first Action.
8. On the Create Action dialog, select Custom Intent, and then click Build to launch the Dialogflow console.
9. In the Dialogflow console’s Create Agent page, click Create.
10. Click the gear icon on the left navigation.
11. Click Export and Import.
12. Click Restore From Zip.

1. Upload the codelab-level-two.zip file from the /level4 directory you created earlier.
2. Type “RESTORE” and click the Restore button.
3. Click Done.

Deploy your fulfillment

Now that your Actions project and Dialogflow agent are ready, do the following to deploy your local index.js file using the Firebase Functions CLI:

1. In a terminal, navigate to the /level4/functions directory of your base files clone.
2. Using the Actions project ID you set, run the following command:

firebase use <PROJECT_ID>

Tip: Your Action’s project ID can be found in the Actions Console, under Overview > (Gear icon) > Project settings.

Run the following command in the terminal to install dependencies.

npm install

Run the following command in the terminal to deploy your webhook to Firebase.

firebase deploy

TIP: If you see an error message that says “An unexpected error has occurred.”, try running the firebase deploy command again.

After a few minutes, you should see “Deploy complete!” indicating that you’ve successfully deployed your webhook to Firebase.

Retrieve the deployment URL

You need to provide Dialogflow with the URL to the cloud function. To retrieve this URL, follow these steps:

1. Open the Firebase Console.
2. Select your Actions project from the list of options.
3. Navigate to Develop > Functions on the left navigation bar. If you’re prompted to “Choose data sharing settings”, you can ignore this option by clicking Do this later.
4. Under the Dashboard tab, you should see an entry for “dialogflowFirebaseFulfillment” with a URL under Trigger. Copy this URL.

Set the URL in Dialogflow

Now you need to update your Dialogflow agent to use your webhook for fulfillment. To do so, follow these steps:

1. Open the Dialogflow Console (you can close the Firebase console if you’d like).
2. Navigate to Fulfillment on the left navigation.
3. Enable Webhook.
4. Paste the URL you copied from the Firebase dashboard if it doesn’t already appear.
5. Click Save.

Verify your project is correctly set up

At this point, users can start a conversation by explicitly invoking your Action. Once users are mid-conversation, they can trigger the ‘favorite color’ custom intent by providing a color. Dialogflow will parse the user’s input to extract the information your fulfillment needs — namely, the color — and send this to your fulfillment. Your fulfillment then auto-generates a lucky number to send back to the user.

Tip: You can find the most recent information about using the Actions Console simulator in this guide. Please refer there if you run into any issues following the steps listed below.

To test out your Action in the Actions console simulator:

1. In the Dialogflow Console left navigation, click on Integrations > Google Assistant.
2. Make sure Auto-preview changes is enabled and click Test to update your Actions project.
3. The Actions Console simulator loads your Actions project. To test your Action, type “Talk to my test app” into the Input field and hit enter.
4. You should see a response: “Hi there, to get to know you better, I’ll just need to get your name from Google. Is that ok?”
5. Type “no”.
6. You should see a response: “OK, no worries. What’s your favorite color?”
7. Type “blue”.
8. You should see a response: “Your lucky number is 4.”

If you don’t see this response, there was likely an issue with your Firebase setup. In this case, try repeating the steps under the ‘Deploy your fulfillment’ section.

4. Setup BigQuery

Login to Google Cloud Platform console (console.cloud.google.com) and select the Action project. At the top of your screen, there is a Project drop down menu:

Enable Billing

Billing must be enabled on this project. Once billing is enabled, you can enable the BigQuery API.

Note: You should not be charged for the queries run in this codelab, but if you exceed the BigQuery free tier (1TB of data queries per month), it’s possible that you will incur charges.

How you enable billing depends on whether you’re creating a new project or you’re re-enabling billing for an existing project.

Google offers a 12 month free trial for up to $300 worth of Google Cloud Platform usage which you may be able to use for this Codelab, find out more details at https://cloud.google.com/free/.

New projects

When you create a new project, you’re prompted to choose which of your billing accounts you want to link to the project. If you have only one billing account, that account is automatically linked to your project.

If you don’t have a billing account, you must create one and enable billing for your project before you can use many Google Cloud Platform features. To create a new billing account and enable billing for your project, follow the instructions in Create a new billing account.

Existing projects

If you have a project that you temporarily disabled billing for, you can re-enable billing:

1. Go to the Cloud Platform Console.
2. From the projects list, select the project to re-enable billing for.
3. Open the console left side menu and select Billing. You’re prompted to select a billing account.
4. Click Set account.

Create a new Billing account

To create a new billing account:

1. Go to the Cloud Platform Console and sign in or, if you don’t already have an account, sign up.
2. Open the console left side menu and select Billing
3. Click the New billing account button. (Note that if this is not your first billing account, first you need to open the billing account list by clicking the name of your existing billing account near the top of the page, and then clicking Manage billing accounts.)
4. Enter the name of the billing account and enter your billing information. The options you see depend on the country of your billing address. Note that for United States accounts, you cannot change tax status after the account is created.
5. Click Submit and enable billing.

By default, the person who creates the billing account is a billing administrator for the account.

For information about verifying bank accounts and adding backup methods of payment, see Add, remove, or update a payment method.

Enable the BigQuery API

Open the Google Cloud Console and click on the API Manager item in the left menu. You may need to open the Product and Services menu by clicking the three-line icon at the top left.

If you’re on the Dashboard page it may also be shown as a card on the main page.

From the API Manager screen, click Enable API at the top of the main screen.

A list of APIs will be shown.

Find and click Google BigQuery API.

On the BigQuery page, if the API is not already enabled, click Enable.

5. Create Dataset and Table in BigQuery

Create a new table in BigQuery

Go to BigQuery on Google Cloud Platform console (https://console.cloud.google.com/bigquery) and select the Action project. At the top of your screen, there is a Project drop down menu:

Select your project name under the Resources and click on CREATE DATASET.

On the popup area, set “reports” as Dataset ID and click Create dataset

On the left panel, the dataset “reports” will be added under the project name. Select it and click CREATE TABLE

Set the table name in Destination table to “actionLog”

And change the Schema to “Edit as text”

Then copy this JSON in:

[
{
"description": "Time",
"name": "time",
"type": "TIMESTAMP",
"mode": "REQUIRED"
},
{
"description": "User ID",
"name": "userId",
"type": "STRING",
"mode": "NULLABLE"
},
{
"description": "Conversation ID",
"name": "conversationId",
"type": "STRING",
"mode": "NULLABLE"
},
{
"description": "Intent",
"name": "intent",
"type": "STRING",
"mode": "NULLABLE"
},
{
"description": "Raw text",
"name": "text",
"type": "STRING",
"mode": "NULLABLE"
},
{
"description": "Response Text",
"name": "responseText",
"type": "STRING",
"mode": "NULLABLE"
},
{
"description": "Locale",
"name": "locale",
"type": "STRING",
"mode": "NULLABLE"
}
]

Click on Create table

Now it’s time to set the action to load data to BigQuery

6. Add a BigQuery Client API to your Action

This codelab uses the BigQuery Node.js Client API

Installing the client library:

npm install — save @google-cloud/bigquery

Setup BigQuery connection in index.js

In your copy of index.js add the following lines:

// Imports the Google Cloud client library
const BigQuery = require(‘@google-cloud/bigquery’);
// Your Google Cloud Platform project ID
const projectId = ‘YOUR_PROJECT_ID’;
const datasetId = ‘reports’;
const tableId = ‘actionLog’;
// Creates a BigQuery client
const bigquery = new BigQuery({
projectId: projectId,
});

Add report function

Create a new function that will report the action activity to the BigQuery.

In this codelab, you will collect all the basic data that the action provide, but in your own action you can add more info that you collect (username, game level, etc.)

function report(conv, line) {
const logInput = {
time: bigquery.timestamp(new Date()),
userId: conv.user.id,
text: conv.input.raw,
intent: conv.intent,
locale: conv.user.locale,
responseText: typeof line === ‘string’ ? line : JSON.stringify(line),
conversationId: conv.id,
};
let dataset = bigquery.dataset(datasetId);
let errors = dataset.table(tableId).insert([logInput]);
if (errors && errors.length && errors[0].insertErrors) {
console.error(‘Bigquery Insert failed’, errors[0].insertErrors);
}
}

Replace ask and close command

In order to send updates to BigQuery on any event in the action, create new functions for ask and close that reports everything.

Start with adding new functions:

function ask(conv, line) {
 report(conv, line);
 conv.ask(line);
}
function close(conv, line) {
 report(conv, line);
 conv.close(line);
}

And now replace all the conv.ask and conv.close with calls to the new functions.

conv.ask(`OK, no worries. What’s your favorite color?`);

to

ask(conv, `OK, no worries. What’s your favorite color?`);

Deploy and test

Now you can deploy the code and use the simulator to create some log lines in the BigQuery.

To check the table in BigQuery, go to the BigQuery console at: https://console.cloud.google.com/bigquery

Type this SQL query in the Query editor:

SELECT *
FROM `YOUR_PROJECT_ID.reports.actionLog`
ORDER BY time desc

Check that all your activities are log in the table.

7. Bonus — Create Dashboard for your Action with Data Studio

Now that you have all the action data collected in the BigQuery, you can start getting analytics and process info out of it.

Data Studio

Google Data Studio lets you build live, interactive dashboards with beautiful data visualizations, for free. You can fetch your data from a variety of sources and create unlimited reports in Data Studio, with full editing and sharing capabilities.

In this example, you will create a query that shows the count of new & returned users over time.

Go to the BigQuery console at https://console.cloud.google.com/bigquery

Run this SQL query:

SELECT
day2 AS date,
users,
IFNULL(unique_users, 0) new_users,
(users - IFNULL(unique_users, 0)) old_users
FROM (
SELECT
COUNT( DISTINCT userId) users,
DATE(time) day2
FROM
`YOUR_PROJECT_ID.reports.actionLog`
GROUP BY
day2) AS totalAll
LEFT JOIN (
SELECT
COUNT(DISTINCT userId) unique_users,
day3
FROM (
SELECT
userId,
MIN(DATE(time)) day3
FROM
`YOUR_PROJECT_ID.reports.actionLog`
GROUP BY
userId )
GROUP BY
day3
ORDER BY
day3) uniqueAll
ON
uniqueAll.day3 = totalAll.day2
ORDER BY
day2

In the Query results, you will get a table looks like this one:

Click on EXPLORE IN DATA STUDIO

New tab with Data Studio will open.

Change the view style from Table to Combo chart

Set the Metric to be: users, new_user, old_users

Set sort to be Date and Ascending

Go to style tab and set Stacked bar on.

On Series 1 set Show data labels

On Left Y-Axis set Custom Tick Interval to 1

* This is a chart of the query you ran on the bigQuery console. It will not be updated over time. In order to get real time Dashboard you will have to create new Report on https://datastudio.google.com/u/0/navigation/reporting.

To learn more about DataStudio: https://support.google.com/datastudio/answer/6283323?hl=en&ref_topic=6267740