Export conversations to BQ

Summary

The 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.