Skip to main content

Export to Google Cloud BigQuery

LoyJoy automatically provides an integrated Analytics dashboard for any chatbot you create. You can find these dashboards in the manager under Analytics.

However, we offer to export all the underlying analytics events on a daily basis to Google Cloud BigQuery as an external data warehouse. To achieve this the following steps are necessary.

Create Tables in Google Cloud BigQuery​

Open Google Cloud and create a Google Cloud Project. In this Google Cloud Project open Google Cloud BigQuery. In the BigQuery view click the three dots beside the project name to create a dataset such as analytics.

Facts Table​

Create a table facts in your Google Cloud project with the following schema.

Create facts table

facts:
- amount (INTEGER)
- created_at (TIMESTAMP)
- entity_id (STRING)
- event_type (STRING)
- tenant_id (STRING)

Dimensions Table​

Create a table dimensions in your Google Cloud project with the following schema.

Create dimensions table

dimensions:
- entity_id (STRING)
- tenant_id (STRING)
- tenant_name (STRING)
- process_id (STRING)
- process_name (STRING)
- view_id (STRING)
- view_name (STRING)

Create a Service Account​

Create a new service account in Google Cloud Platform under service accounts with privileges BigQuery Job User, BigQuery Data Editor.

Click Keys > Add Key > Create new key > JSON to create JSON credentials for this service account. Your web browser will download a key file.

Provide Service Account Credentials to LoyJoy Support​

Please provide the following details to LoyJoy support:

  • credentials key file created by you
  • name of your Google Cloud project created by you
  • name of your BigQuery dataset created by you, probably analytics
  • name of the BigQuery facts table created by you, probably facts
  • name of the BigQuery dimensions table created by you, probably dimensions

We will enter the credentials in the Google Cloud BigQuery connector of your LoyJoy tenant. On a daily basis the Google Cloud BigQuery connector will copy all analytics events from LoyJoy to Google Cloud BigQuery.