Interaction logging export to BigQuery

You can export interaction logging to BigQuery. Once configured, all live interaction logging is written to your BigQuery table. This provides you with advanced analysis tools that can help you debug and improve your agent and to discover patterns in conversation data.

Limitations

The following limitations apply:

• A maximum of 500 turns can be exported for each conversation.

Cross project permissions

If your Dialogflow agent and BigQuery data are not in the same project, the service account associated with your Dialogflow Google Cloud project must also have the roles/bigquery.dataEditor IAM permission for the BigQuery dataset in your BigQuery Google Cloud project.

Service account format: service-<dialogflow-project-number>@gcp-sa-dialogflow.iam.gserviceaccount.com

The user configuring the export in Dialogflow must have permissions on the BigQuery project. If they don't, the BigQuery project won't appear as an option in the Dialogflow Console.

The minimum permission required on the BigQuery project in order for the user to see it in Dialogflow is resourcemanager.projects.get. Alternatively, you can assign one of the following Google Cloud predefined roles that include this permission but don't require the user to have access to the BigQuery dataset: roles/browser or roles/bigquery.metadataViewer.

Table description

Each row of the table contains one conversational turn with the following columns:

Column	Type	Description
project_id	STRING	The project ID.
agent_id	STRING	The agent ID.
conversation_name	STRING	The fully qualified resource name for the session.
turn_position	INTEGER	The conversational turn number.
request_time	TIMESTAMP	The time of the conversational turn.
language_code	STRING	The language tag.
request	JSON	The detect intent request.
response	JSON	The detect intent response.
partial_responses	JSON	Partial responses if applicable.
derived_data	JSON	Additional metadata for this conversational turn.
conversation_signals	JSON	NLU related analytics data. See ConversationSignals for the JSON schema.
bot_answer_feedback	JSON	Answer feedback if provided.

Configuration

To configure interaction logging export:

1. Ensure that interaction logging is enabled.
2. Follow the BigQuery dataset creation guide to create a dataset. Note the dataset name, as you will need this in the next step.

3. Follow the BigQuery table creation guide to create a table with a SQL schema definition. Use the following SQL statement for creation:

   CREATE TABLE <your_dataset_name>.dialogflow_bigquery_export_data(
     project_id STRING,
     agent_id STRING,
     conversation_name STRING,
     turn_position INTEGER,
     request_time TIMESTAMP,
     language_code STRING,
     request JSON,
     response JSON,
     partial_responses JSON,
     derived_data JSON,
     conversation_signals JSON,
     bot_answer_feedback JSON
   );
   

4. Configure your agent settings to enable BigQuery export, and to provide the dataset and table names created above.

   Note: If your agent is protected by a Customer Managed Encryption Key (CMEK), you will see a warning if the destination BigQuery table is not protected by CMEK.