Export to BQ demo
Summary
TLDRIn this demo, Josh Radia, a Cloud engineer at Google, guides viewers through exporting conversation data from the Insights Console to BigQuery for advanced analysis. He demonstrates creating a dataset and table in BigQuery, applying filters to select specific conversations, and initiating the export process. The resulting schema in BigQuery includes conversation details, agent and client metrics, sentiment scores, and annotations, allowing for in-depth data exploration and visualization.
Takeaways
- 🚀 Josh Radia, a Cloud Engineer at Google, demonstrates exporting conversations from the Insights Console to BigQuery for custom analysis and visualization.
- 📋 Prerequisites include having a BigQuery table created with a specific dataset ID and region to match the Insights region.
- 🔍 The process involves filtering conversations by various fields such as agent ID, turn count, labels, duration, and status before exporting.
- 📊 After creating a dataset and table in BigQuery, an empty table is verified to ensure it has no schema before proceeding with the export.
- 🔗 The export function in the Insights Console allows for the selection of the dataset and table for exporting filtered conversations.
- 🔑 Viewing the export job can be done through the 'view running operations' feature once the export process is initiated.
- 📈 The exported data updates the schema of the BigQuery table, adding fields like conversation name, agent ID, and conversation-level information.
- 📝 The export includes detailed fields such as agent and client speaking percentages, sentiment scores, turn count, and medium of conversation.
- 📚 Nested fields like 'issues' and 'entities' provide in-depth information on topics, scores, speaker tags, and additional labels.
- 📉 Sentence-level sentiment scores and magnitude, along with speaker tags, are available for detailed analysis.
- 📝 Annotations and summaries from the latest summary section, including customer resolution and action, are part of the exported data.
- 🔧 The ability to export conversations using the API with options to append or overwrite data in BigQuery tables is also discussed.
Q & A
What is the purpose of the demo presented by Josh Radia?
-The purpose of the demo is to show how to export conversations from the Insights Console to BigQuery for custom in-depth analysis and visualization.
Who is presenting the demo and what is his role at Google?
-The demo is presented by Josh Radia, who is a Cloud Engineer at Google.
What is the prerequisite for starting the demo?
-The prerequisite is to have a table created on BigQuery before starting the demo.
How do you create a dataset in BigQuery according to the demo?
-You can create a dataset in BigQuery by clicking on the project and then clicking on 'Create Dataset', providing a demo dataset ID and selecting the same region as the CCI Insights region.
What is the initial state of the table created in BigQuery?
-The initial state of the table is empty and does not have any schema.
How can you filter conversations in the Insights Console for export?
-You can filter conversations by applying filters on various fields such as agent ID, conversation label, duration, summary, status, analysis status, language, and more.
What happens when you click on the 'Export' button in the Insights Console?
-Clicking the 'Export' button starts an export job that sends the filtered conversations to the specified BigQuery dataset and table.
What changes occur to the table schema after the export job is completed?
-After the export job, the table schema changes to include multiple fields such as conversation name, agent ID, conversation level information, and nested fields like issues and entities.
What kind of information can be found in the 'issues' nested field?
-The 'issues' nested field contains information about topics, including the name of the topic and the score of the topic.
How can you preview the exported data in BigQuery?
-You can preview the exported data by performing a 'SELECT *' query on the table in BigQuery.
Is it possible to export conversations to BigQuery using the API?
-Yes, conversations can be exported to BigQuery using the API, with options to append or overwrite information in the table.
What additional information can be found in the exported data?
-The exported data includes conversation level information, agent and client speaking percentages, client sentiment scores, turn count, medium of the conversation, annotations, and the latest summary with various feeds like customer resolution action and situation.
Outlines
🚀 Introduction to Exporting Data to BigQuery
Josh Radia, a Cloud engineer at Google, introduces a demo on exporting data from the Insights console to BigQuery for custom analysis and visualization. The process begins by creating a BigQuery dataset and table as prerequisites for exporting conversations. The demo will guide through filtering conversations, exporting them, and exploring the exported data's structure in BigQuery.
📚 Creating a BigQuery Dataset and Table
The script outlines the steps to create a BigQuery dataset and table. It involves selecting a project, naming the dataset, and choosing the same region as the Insights console. After the dataset creation, a table is created without modifying any additional settings. It's confirmed that the table is initially empty and schema-less, preparing it for the export process.
🔍 Filtering Conversations for Export
The demo continues with applying filters to select specific conversations for export, such as by agent ID and turn count. Multiple filters can be applied, including conversation label, duration, summary, status, analysis status, language, and more. Once the desired set of conversations is filtered, the export process can be initiated.
🔄 Starting the Export Job to BigQuery
After filtering, the script describes the export process. It involves selecting the dataset and table for the export and reviewing the applied filters and the number of conversations to be exported. The export job is started, and a long-running job is created, which can be monitored through the 'view running operations' feature.
📊 Exploring the Exported Data Schema and Content
Once the data is exported, the schema of the BigQuery table is updated to include various fields such as conversation name, agent ID, conversation-level information, sentiment scores, transcript, turn count, and medium. It also includes nested fields for issues, topics, entities, speaker tags, additional labels, and sentence-level sentiment scores. The annotations and latest summary are also part of the exported data, providing a comprehensive view of the conversation insights.
🛠 Using SQL for Data Analysis in BigQuery
The script concludes with the ability to preview the exported data and start writing SQL queries directly in BigQuery to perform custom analysis. It mentions that the summary section is correctly populated, indicating successful data export and readiness for in-depth analysis.
🔧 Exporting Conversations via API with Options
The script also covers the option to export conversations to BigQuery using the API, providing flexibility with flags to append or overwrite information in the table. This offers an automated way to integrate the export process into larger workflows or systems.
Mindmap
Keywords
💡BigQuery
💡Data Set
💡Table
💡CCI Insights
💡Export
💡Conversations
💡Filters
💡Schema
💡Nested Field
💡Sentiment Score
💡Annotations
💡API
Highlights
Introduction to a demo of exporting data to BigQuery for custom analysis and visualization.
Requirement of having a table created on BigQuery before starting the demo.
Step-by-step guide on creating a BigQuery dataset and table.
Verification of an empty table with no schema in BigQuery.
Filtering conversations in the Insights Console for export.
Applying multiple filters such as agent ID, conversation label, duration, and status.
Exporting conversations to the specified BigQuery dataset and table.
Viewing the long-running export job in BigQuery.
Observation of schema changes and field creation post-export.
Inclusion of conversation-level information such as agent and client speaking percentages.
Details on the nested 'issues' field containing topics and scores.
Presence of entity information including speaker tags and additional labels.
Introduction of sentence-level sentiment scores and magnitude.
Availability of annotations and latest summary in the exported data.
Previewing the data and writing SQL queries directly in BigQuery.
Exporting conversations using the API with options to append or overwrite data.
Summary of the process from creating an empty BigQuery table to reviewing the final data model.
Transcripts
next we are going to provide you with a
demo of export to Big query in this demo
we are going to cover how to create big
query data set and table for insights
data select and Export the conversations
to Big query and finally explore
important fields and exported
data hi my name is Josh Radia and I'm A
Cloud a engineer at Google today I'll be
demoing how to export conversations from
insights console to Big query this
enables custom in-depth analysis and
visualization on insights Data before we
get started with this demo as a
prerequisite it is important that we
have a table created on big query we can
click on the project and click on create
data set I've provided a demo data set
ID and selected the same region as the
CCI insights region once the data set is
created we can go to data set and then
click on create table apart from giving
the table name we don't really need to
modify anything else here once the table
is created we can click on go to table
we can verify that this is an empty
table that does not have any schema as
of this moment once this is done we can
head back to CCI insights console and
then start by filtering the
conversations that we want to export the
first filter I'm going to apply is on
agent ID we can apply multiple filters
for example on agent ID conversation
label duration summary status analysis
status language and many more fields
I'm going to apply another filter on
turn count once we are happy with the
set of conversations that we have we can
click on export here we can select the
data set and the table that we recently
created we can also expand this to look
at all the filters that are applied it
also shows the number of conversations
which will be exported we can click on
export button here to start the export
job this creates a long running job
which can be viewed by clicking on view
running operations once the data is
loaded we can see that the schema of
this table has changed it creates
multiple Fields like name of the
conversation which has the whole
conversation URI agent ID and lots of
conversation level information for
example agent speaking percentage client
speaking percentage uh client sentiment
score also it contains the transcript
turn count medium of the conversation it
also has a nested field called issues
this has all the topics like name of the
topic score of the topic and more
information it also has more information
about entities for example speaker tag
it also contains any additional labels
in our conversation and it has Ned
fields for words and sentences we can
also see sentence level sentiment score
and magnitude as well as Speaker tag and
more information we can also see
annotations and the latest summary can
be found under the latest summary
section this has different feeds like
customer resolution action situation Etc
that we observed in llm summarization
section we can we can then do select
star on the table to preview the data
and start writing our sqls in the big
query directly as you can see the
summary section is populated correctly
we can also export conversations from
insides to Big query using the API and
we can have different flags like whether
we want to append the information into a
table or do we want to overwrite it so
to summarize we looked at how to create
a empty big query table how to select
conversations that we want to export how
to start and Export big quer job and
then we reviewed the final data model
and data on big query
5.0 / 5 (0 votes)