Export to BQ demo

Summary

In 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

00:00

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

Keywords

💡BigQuery

BigQuery is a fully-managed, serverless data warehouse that enables scalable analysis over petabytes of data. In the video, BigQuery is used to export and analyze conversation data from the insights console, allowing for in-depth analysis and visualization of insights data. The script mentions creating a BigQuery dataset and table for this purpose.

💡Data Set

In the context of BigQuery, a data set is a collection of tables, views, and metadata. The script describes the process of creating a data set in BigQuery as a prerequisite for exporting conversation data, which is a fundamental step in organizing and managing the exported data.

💡Table

A table in BigQuery is a structured collection of data, similar to a table in a relational database. The script discusses creating an empty table within a data set to store the exported conversation data, highlighting the importance of tables in structuring and querying data.

💡CCI Insights

CCI Insights likely refers to a specific insights platform or console, possibly related to Google's suite of cloud services. The script mentions using the CCI insights console to filter and export conversations, indicating its role as the source of the data being analyzed.

💡Export

The term 'export' in the script refers to the process of transferring data from one system to another. The video demonstrates how to export conversation data from the CCI insights console to BigQuery, which is a key action in the workflow described.

💡Conversations

Conversations, in this context, are the data points being analyzed. The script discusses filtering and exporting conversations, which are likely interactions or dialogues captured for analysis, emphasizing their central role in the data analysis process.

💡Filters

Filters are used to refine data based on specific criteria. The script mentions applying filters such as agent ID and turn count to select the conversations for export, illustrating how filters help in narrowing down the data set for analysis.

💡Schema

In BigQuery, a schema defines the structure of a table, including the columns and their data types. The script notes that the table initially has no schema and later describes how the schema changes to include various fields after the export, showing the dynamic nature of data structuring.

💡Nested Field

A nested field in BigQuery is a field that contains a repeated group of columns. The script refers to a nested field called 'issues' that contains topics and scores, demonstrating how complex data structures can be represented in BigQuery.

💡Sentiment Score

Sentiment score is a metric that reflects the emotional tone behind a conversation, often used in customer service analysis. The script mentions client sentiment score and sentence level sentiment score, indicating the use of sentiment analysis in understanding the data.

💡Annotations

Annotations in the context of the script likely refer to notes or tags added to the conversation data for further analysis. The script mentions annotations as part of the exported data, showing how they contribute to the richness of the data set.

💡API

API stands for Application Programming Interface, which allows for programmatic interaction with software systems. The script briefly mentions the possibility of exporting conversations to BigQuery using an API, suggesting an alternative method to the manual process shown in the demo.

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