This quickstart demonstrates how to build a comprehensive call center analytics solution leveraging Snowflake's Cortex AISQL for intelligent audio transcription (Cortex AI Transcribe), conversation analysis, and speaker identification. The solution integrates Streamlit dashboards to provide organizations with actionable insights from call center conversations through advanced AI-powered analytics, sentiment analysis, and natural language querying capabilities on audio file.

You'll learn to architect an end-to-end analytics pipeline that seamlessly processes audio files, extracts meaningful conversation insights, and delivers an intuitive conversational interface enabling business users to query call center data using natural language—all powered by Snowflake's integrated Cortex AISQL platform.

Key Components

Architecture Overview

Architecture

What You Will Learn

You will quickly learn how to use Cortex AI Transcribe for automated audio file processing, utilize advanced Cortex AISQL functions including AI_Complete and AI_Sentiment for intelligent data extraction from transcribed conversations, and integrate Cortex Analyst with semantic models to create sophisticated conversational chatbots. Additionally, you will develop interactive Streamlit dashboards that present critical KPIs and comprehensive analytics, empowering business stakeholders with data-driven insights.

What You Will Build

A comprehensive call center analytics solution featuring:

Prerequisites

1. Environment Setup

Create the necessary Snowflake objects for the solution:

-- Create database and schemas
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS DE_DEMO_ROLE;
CREATE DATABASE IF NOT EXISTS call_centre_analytics_db;
-- Create warehouse
CREATE WAREHOUSE IF NOT EXISTS cca_xs_wh 
    WAREHOUSE_SIZE = 'SMALL'
    AUTO_SUSPEND = 300
    AUTO_RESUME = TRUE;

USE DATABASE call_centre_analytics_db;
USE SCHEMA PUBLIC;

-- GRANT OWNERSHIP ON THE DB TO THE CUSTOM ROLE
GRANT OWNERSHIP ON DATABASE call_centre_analytics_db TO ROLE DE_DEMO_ROLE COPY CURRENT GRANTS;
GRANT OWNERSHIP ON ALL SCHEMAS IN DATABASE call_centre_analytics_db TO ROLE DE_DEMO_ROLE COPY CURRENT GRANTS;

GRANT USAGE ON WAREHOUSE cca_xs_wh TO ROLE DE_DEMO_ROLE;

GRANT EXECUTE TASK ON ACCOUNT TO ROLE DE_DEMO_ROLE;

-- Update the username 
GRANT ROLE DE_DEMO_ROLE TO USER <username>; 

USE ROLE DE_DEMO_ROLE;
USE DATABASE call_centre_analytics_db;
USE WAREHOUSE cca_xs_wh;
USE SCHEMA PUBLIC;

CREATE STAGE IF NOT EXISTS UDF
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
DIRECTORY = (ENABLE = TRUE)
COMMENT = ' used to create UDFs';
CREATE STAGE IF NOT EXISTS AUDIO_FILES
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
DIRECTORY = (ENABLE = TRUE)
COMMENT = ' stage for Cortex Analyst semantic model files';;;

CREATE STAGE IF NOT EXISTS SEMANTIC_MODEL_STAGE
ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')
DIRECTORY = (ENABLE = TRUE)
COMMENT = ' stores the semantic yaml file for cortex analyst';



-- Stored the audio file path and the transcription of the audio file. Duration is in seconds.
 CREATE or REPLACE TABLE ALL_CLAIMS_RAW (
	DATETIME DATE,
	AUDIOFILE VARCHAR(16777216),
    AUDIOFILE_RELATIVE_PATH VARCHAR(16777216),
	CONVERSATION VARCHAR(16777216),
	PRESIGNED_URL_PATH VARCHAR(16777216),
	DURATION FLOAT  NULL);

 CREATE or REPLACE TABLE AUDIO_CLAIMS_EXTRACTED_INFO 
    (
        DATETIME DATE,
        AUDIO_FILE_NAME VARCHAR(100),
        AUDIO_FULL_FILE_PATH VARCHAR(16777216),
        RAW_CONVERSATION VARCHAR(16777216),
        PROMPTED_CONVERSATION VARCHAR(16777216),
        DURATION FLOAT,
        CALL_DETAILS VARIANT,
        CALL_SUMMARY VARCHAR(16777216),
        CALL_SENTIMENT variant,
        REPONSE_GIVEN FLOAT
    );

2. File to Upload to Stage

Audio File Listing

Upload the ‘Call-Centre-Analytics-Notebook.ipynb' notebook from Snowsight.

Navigate to Projects → Notebooks and then click ⌄ to Import ipynb file and select the file Call-Centre-Analytics-Notebook.ipynb. Make sure to choose the notebook location to match call_centre_analytics_db Database and the public Schema, and choose the warehouse we created earlier cca_xs_wh for the query warehouse and the notebook warehouse.

Run through the notebook which does following:

After you complete running through the notebook you can create the streamlit app by navigate to Projects → Streamlit and create a new Streamlit application by selecting the below options:

  1. App Title: CALL_CENTRE_ANALYTICS_APP
  2. Database: Choose CALL_CENTRE_ANALYTICS_DB
  3. Schema: Choose PUBLIC
  4. Warehouse: Choose cca_xs_wh warehouse

Streamlit App Dashboard

Streamlit App offer following Application Components

1. Dashboard Analytics

The solution provides multiple analytical views:

Overview Dashboard

Advanced Analytics

Trend Analysis

2. Cortex Analyst Integration

The heart of the application leverages Snowflake's Cortex Analyst for natural language querying on the audio files. We can have a conversation chatbot where it sends the history for asking question based on previous question.

Chat Interface Implementation

def query_cortex_analyst_rest_api(question: str, conversation_history: List[Dict] = None) -> Dict[Any, Any]:
    """Query Cortex Analyst with conversation context"""
    
    request_body = {
        "question": question,
        "semantic_model": semantic_model_content,
        "conversation": conversation_history or []
    }
    
    response = session.post(CORTEX_ANALYST_ENDPOINT, json=request_body)
    return response.json()

Question asked How mant calls were not resolved on the first attempt and below is the response: Analysts First Question

Now I am asking a followup question: give me the list of reps and the audio info along with date for the above data and below is the output Analysts Followup Question

You can see from the screenshot that it is giving us the details for the rep name and the audio file for all the conversation which is not a first call resolution. Following is the query generated by the Cortex Analyst where it filters for first_call_resolution = ‘No' showing how powerful cortex analyst is.

WITH __call_center_interactions AS (
  SELECT
    audio_file_name,
    representative AS representative_name,
    firstcallresolution AS first_call_resolution,
    datetime AS call_date
  FROM call_centre_analytics_db.public.streamlitapptable
)
SELECT
  representative_name,
  audio_file_name,
  call_date
FROM __call_center_interactions
WHERE
  first_call_resolution = 'No'
ORDER BY
  call_date DESC NULLS LAST
 -- Generated by Cortex Analyst
;

Features:

3. On-Demand Audio File Transcribing and Processing

The application provides comprehensive audio file handling capabilities. You can upload the file from your local machine and ask the app to process the audio file.

Key Features:

  1. Upload Audio Files: Drag and drop or browse for audio files
  2. Processing Status: Monitor upload progress and processing status
  3. File Management: View uploaded files and their processing status
  4. Playback: Listen to processed audio files directly in the interface

Audio Upload Interface Screenshot

You can use the file ztest_9.mp3 to test the uploading and processing adhoc audio file from the streamlit app.

This quickstart demonstrates how to build a comprehensive call center analytics solution which is powered by Cortex AISQL. By combining Cortex Analyst's natural language capabilities with Streamlit's interactive interface, you've created a powerful tool for extracting insights from call center audio data.

What You Learned

You've built a complete call center analytics solution featuring:

Related Resources