Export conversations to BQ
Summary
TLDRThe script explains how to export conversation data from CCAI Insights to BigQuery for detailed analysis and visualization. It outlines the steps to create an empty table in BigQuery, apply filters, configure the export job, and monitor its progress. The export supports various write dispositions and can be filtered by specific criteria.
Takeaways
- 📊 Conversations can be exported to BigQuery for advanced analysis and visualization of Insights data.
- 🔍 Filtering conversations is an optional step before exporting to BigQuery, allowing for targeted data analysis.
- 📝 An empty table must be created in BigQuery prior to exporting data from Insights.
- 💾 The schema for the BigQuery table is dynamically set during the export job and does not need to be pre-defined.
- 🔗 After table creation, the export process is initiated from the Insights console by reviewing filters and conversation counts.
- 📋 Users should input the correct BigQuery dataset and table information to ensure accurate data transfer.
- 🚀 The export process is a long-running job, and its progress can be monitored through the Insights console's notification icon.
- ⚠️ The Insights interface's Export button has limitations on the number of records that can be exported and does not support data appending.
- 🔄 BigQuery supports two write disposition options for the export: WRITE_TRUNCATE and WRITE_APPEND.
- 🔒 The export feature also supports writing data to tables protected by Customer-Managed Encryption Keys (CMEK).
- 🌐 Export to BigQuery is compatible with all filter combinations that can be applied to conversation queries, allowing for complex data sampling.
Q & A
What is the purpose of exporting conversations into BigQuery?
-The purpose is to enable custom in-depth analysis and visualization on Insights data, allowing for more detailed insights into the conversation data.
Is it necessary to filter conversations before exporting to BigQuery?
-No, filtering conversations is an optional step. You can export all the conversations if required.
What is the first step before triggering an export job to BigQuery?
-The first step is to create an empty table in BigQuery where the Insights data will be loaded.
How do you create a new table in BigQuery?
-Go to the BigQuery console, select a dataset or create one if it doesn’t exist, and then click on 'Create a new table'. Input the required fields without setting a schema, as it will be done dynamically during the export job.
What is the role of the 'Export' button in the CCAI Insights console?
-The 'Export' button in the CCAI Insights console is used to initiate the export process to BigQuery, allowing users to review applied filters and conversation count before proceeding.
How can the progress of an export job be checked?
-The progress of an export job can be checked by clicking on the notification icon at the top right corner of the Insights console.
Why should the Export button in the Insights interface not be used?
-The Export button in the Insights interface should not be used due to current limits on the number of records that can be exported and the inability to append data to the target table.
What are the write disposition options supported by the CCAI Insights export to BigQuery via the API?
-The supported write disposition options are 'WRITE_TRUNCATE', which overwrites existing table data with the schema from the query result, and 'WRITE_APPEND', which appends data to an existing table.
Can data be exported to customer-managed encryption key (CMEK) protected tables?
-Yes, the export supports writing data to customer-managed encryption key (CMEK) protected tables.
Is the export to BigQuery compatible with all combinations of filters applied to conversation queries?
-Yes, the export to BigQuery is compatible with all combinations of filters that can be applied to conversation queries.
Can you provide an example of a sample export query?
-An example query could be to export all conversations with 10 or more turns handled by agent_id '007' between January 1st 2021 and January 2nd 2021 Pacific Standard Time.
Outlines
📊 Exporting Conversations to BigQuery for Analysis
This paragraph explains the process of exporting conversation data into BigQuery for advanced analysis and visualization. It highlights the ability to apply filters to select specific subsets of conversations before exporting. The user is instructed to create an empty table in BigQuery to load the Insights data. The export job is initiated from the CCAI Insights console, and the progress can be monitored through notifications. The paragraph also mentions the limitations of using the Export button in the Insights interface and outlines the supported write disposition options in BigQuery, such as WRITE_TRUNCATE and WRITE_APPEND. Additionally, it covers the compatibility of export with various filters and the ability to write to customer-managed encryption key (CMEK) protected tables.
Mindmap
Keywords
💡BigQuery
💡Custom Analysis
💡Filters
💡Export Job
💡Dataset
💡Schema
💡Conversation Hub
💡WRITE_TRUNCATE
💡WRITE_APPEND
💡Customer-Managed Encryption Key (CMEK)
💡Long Running Job
Highlights
Conversations can be exported directly into BigQuery for further analysis, enabling custom in-depth analysis and visualization on Insights data.
Exporting to BigQuery requires an empty table to be created first, where Insights data will be loaded.
Filtering the conversation is optional; all conversations can be exported if required.
BigQuery console is used to select or create a dataset and then create a new table for Insights data.
The schema for the BigQuery table does not need to be set initially; it will be done dynamically during the export job.
After creating the BigQuery table, the Export button in the CCAI Insights console is used to initiate the export process.
The export job allows reviewing applied filters and the conversation count before execution.
The correct BigQuery dataset and table must be specified during the export configuration.
A long-running job is triggered by the Export button, with progress viewable through the Insights console's notification icon.
There are limits on the number of records that can be exported using the Export button in the Insights interface.
Appending data to the target table is not possible with the Insights interface's Export button.
CCAI Insights export supports WRITE_TRUNCATE and WRITE_APPEND write disposition options via the API.
WRITE_TRUNCATE overwrites existing table data with the schema from the query result, which is the default option.
WRITE_APPEND allows BigQuery to add data to an existing table.
The export supports both filtering and writing data to customer-managed encryption key (CMEK) protected tables.
Export to BigQuery is compatible with all combinations of filters that can be applied to conversation queries.
An example provided shows how to craft a query to export all conversations with 10 or more turns handled by a specific agent within a date range.
Transcripts
Conversations can be exported directly into BigQuery for further analysis.
This enables custom in depth analysis and visualization on Insights data.
Once a subset of a conversation is selected
after applying filters, we can configure the export job to BigQuery.
Do note that filtering the conversation is an optional step.
We can also export all the conversations if required.
Before we trigger the export job, it is important that we have an empty table created in BigQuery
first in which Insights data will be loaded.
Go to BigQuery console and select a dataset (or create one if it doesn’t exist).
Then click on create a new table.
Input the required fields as shown in the slide and note that the schema is not required
to be set as it will be done dynamically during the export job.
After creating the BQ table, we can come back to the conversation hub in the CCAI Insights
console and click on the Export button.
This opens up a panel where we can review the applied filters and the conversation count.
Enter the correct BigQuery dataset and table as shown in the snapshot.
Once you’re satisfied with the list of conversations, click on the Export button at the bottom.
This triggers a long running job (depending on the volume of conversations to be exported).
Its progress can be checked by clicking on the notification icon at the top right corner
of the Insights console.
Do not utilize the Export button in the Insights interface as there are currently limits on
the number of records that can be exported.
It’s also not possible to append data in the target table.
CCAI Insights export supports the following write disposition options from BigQuery via
the API:
WRITE_TRUNCATE: If the table already exists, BigQuery overwrites the table data and uses
the schema from the query result.
This is the default option.
WRITE_APPEND: If the table already exists, BigQuery appends the data to the table.
Additionally, the export supports both filtering and writing data to customer-managed encryption
key (CMEK) protected tables.
Export to BigQuery is compatible with all combinations of filters that can be applied
to conversation queries.
For example, the you can craft a sample that exports all conversations with 10 or more
turns handled by agent_id "007" between January 1st 2021 and January 2nd 2021 Pacific Standard
Time:
5.0 / 5 (0 votes)