Capstone: Text Analytics Dashboard
Project Overview
Build a text analytics dashboard that analyzes customer reviews and support tickets using Cortex AI functions. The system classifies topics, scores sentiment, generates summaries, and presents insights through an interactive Streamlit dashboard with charts, filters, and scheduled data refresh.
Dataset Description
| Column | Type | Description |
|---|---|---|
ticket_id | VARCHAR | Unique ticket identifier |
customer_id | VARCHAR | Customer who submitted the ticket |
channel | VARCHAR | Submission channel: email, chat, phone, web |
category | VARCHAR | Ticket category: billing, technical, account, shipping |
subject | VARCHAR | Ticket subject line |
body | VARCHAR | Full ticket text |
created_at | TIMESTAMP | When the ticket was submitted |
resolved_at | TIMESTAMP | When the ticket was resolved (NULL if open) |
Step 1: Sentiment Analysis
-- Score sentiment on all tickets
CREATE OR REPLACE TABLE analytics_db.public.ticket_sentiment AS
SELECT
ticket_id,
customer_id,
channel,
category,
subject,
body,
created_at,
resolved_at,
SNOWFLAKE.CORTEX.AI_SENTIMENT(body) AS sentiment_score
FROM analytics_db.public.support_tickets;
The AI_SENTIMENT function returns a float between -1 (very negative) and 1 (very positive).
| Score Range | Label |
|---|---|
| -1.0 to -0.3 | Negative |
| -0.3 to 0.3 | Neutral |
| 0.3 to 1.0 | Positive |
Step 2: Topic Classification
-- Classify tickets into detailed topics
CREATE OR REPLACE TABLE analytics_db.public.ticket_topics AS
SELECT
t.ticket_id,
t.sentiment_score,
t.category,
t.channel,
t.created_at,
SNOWFLAKE.CORTEX.AI_CLASSIFY(
t.body,
[
'billing_dispute',
'refund_request',
'technical_bug',
'feature_request',
'account_access',
'shipping_delay',
'product_quality',
'general_inquiry'
]
):label::VARCHAR AS topic,
SNOWFLAKE.CORTEX.AI_CLASSIFY(
t.body,
[
'billing_dispute',
'refund_request',
'technical_bug',
'feature_request',
'account_access',
'shipping_delay',
'product_quality',
'general_inquiry'
]
):score::FLOAT AS topic_confidence
FROM analytics_db.public.ticket_sentiment t;
Step 3: Generate Summaries
-- Create daily summaries of ticket themes
CREATE OR REPLACE TABLE analytics_db.public.daily_ticket_summaries AS
SELECT
DATE_TRUNC('day', created_at) AS summary_date,
topic,
COUNT(*) AS ticket_count,
AVG(sentiment_score) AS avg_sentiment,
SNOWFLAKE.CORTEX.AI_SUMMARIZE(
LISTAGG(LEFT(body, 500), ' | ') WITHIN GROUP (ORDER BY created_at DESC)
) AS daily_summary
FROM analytics_db.public.ticket_topics
GROUP BY 1, 2;
Step 4: Build Aggregate Insights
-- Create a view for dashboard consumption
CREATE OR REPLACE VIEW analytics_db.public.ticket_insights AS
SELECT
t.ticket_id,
t.customer_id,
t.channel,
t.category,
t.subject,
t.created_at,
t.resolved_at,
DATEDIFF('hour', t.created_at, t.resolved_at) AS resolution_hours,
s.sentiment_score,
CASE
WHEN s.sentiment_score < -0.3 THEN 'Negative'
WHEN s.sentiment_score > 0.3 THEN 'Positive'
ELSE 'Neutral'
END AS sentiment_label,
tp.topic,
tp.topic_confidence
FROM analytics_db.public.support_tickets t
JOIN analytics_db.public.ticket_sentiment s ON t.ticket_id = s.ticket_id
JOIN analytics_db.public.ticket_topics tp ON t.ticket_id = tp.ticket_id;
Step 5: Streamlit Dashboard
import streamlit as st
from snowflake.snowpark.context import get_active_session
session = get_active_session()
st.title("Customer Support Text Analytics")
# Sidebar filters
st.sidebar.header("Filters")
channels = session.sql(
"SELECT DISTINCT channel FROM analytics_db.public.ticket_insights ORDER BY 1"
).to_pandas()["CHANNEL"].tolist()
selected_channels = st.sidebar.multiselect("Channel", channels, default=channels)
date_range = st.sidebar.date_input("Date Range", value=[])
# Build filter clause
channel_filter = "','".join(selected_channels)
where_clause = f"WHERE channel IN ('{channel_filter}')"
# KPI row
kpis = session.sql(f"""
SELECT
COUNT(*) AS total_tickets,
AVG(sentiment_score) AS avg_sentiment,
AVG(resolution_hours) AS avg_resolution_hrs,
COUNT_IF(sentiment_label = 'Negative') AS negative_count
FROM analytics_db.public.ticket_insights
{where_clause}
""").to_pandas()
col1, col2, col3, col4 = st.columns(4)
col1.metric("Total Tickets", int(kpis["TOTAL_TICKETS"][0]))
col2.metric("Avg Sentiment", f"{kpis['AVG_SENTIMENT'][0]:.2f}")
col3.metric("Avg Resolution (hrs)", f"{kpis['AVG_RESOLUTION_HRS'][0]:.1f}")
col4.metric("Negative Tickets", int(kpis["NEGATIVE_COUNT"][0]))
# Sentiment distribution chart
st.subheader("Sentiment Distribution")
sentiment_dist = session.sql(f"""
SELECT sentiment_label, COUNT(*) AS count
FROM analytics_db.public.ticket_insights
{where_clause}
GROUP BY 1
""").to_pandas()
st.bar_chart(sentiment_dist.set_index("SENTIMENT_LABEL"))
# Topic breakdown
st.subheader("Topics by Volume")
topic_data = session.sql(f"""
SELECT topic, COUNT(*) AS ticket_count, AVG(sentiment_score) AS avg_sentiment
FROM analytics_db.public.ticket_insights
{where_clause}
GROUP BY 1
ORDER BY 2 DESC
""").to_pandas()
st.dataframe(topic_data, use_container_width=True)
# Trend over time
st.subheader("Sentiment Trend")
trend = session.sql(f"""
SELECT
DATE_TRUNC('week', created_at)::DATE AS week,
AVG(sentiment_score) AS avg_sentiment,
COUNT(*) AS volume
FROM analytics_db.public.ticket_insights
{where_clause}
GROUP BY 1
ORDER BY 1
""").to_pandas()
st.line_chart(trend.set_index("WEEK")["AVG_SENTIMENT"])
# Recent negative tickets
st.subheader("Recent Negative Tickets")
negative = session.sql(f"""
SELECT ticket_id, channel, topic, sentiment_score, subject, created_at
FROM analytics_db.public.ticket_insights
{where_clause} AND sentiment_label = 'Negative'
ORDER BY created_at DESC
LIMIT 10
""").to_pandas()
st.dataframe(negative, use_container_width=True)
Step 6: Schedule Refresh with Tasks
-- Refresh sentiment and classification daily
CREATE OR REPLACE TASK analytics_db.public.refresh_text_analytics
WAREHOUSE = compute_wh
SCHEDULE = 'USING CRON 0 3 * * * America/New_York'
AS
BEGIN
-- Re-score new tickets for sentiment
INSERT INTO analytics_db.public.ticket_sentiment
SELECT ticket_id, customer_id, channel, category, subject, body,
created_at, resolved_at,
SNOWFLAKE.CORTEX.AI_SENTIMENT(body)
FROM analytics_db.public.support_tickets
WHERE ticket_id NOT IN (SELECT ticket_id FROM analytics_db.public.ticket_sentiment);
-- Re-classify new tickets
INSERT INTO analytics_db.public.ticket_topics
SELECT ticket_id, sentiment_score, category, channel, created_at,
SNOWFLAKE.CORTEX.AI_CLASSIFY(body,
['billing_dispute','refund_request','technical_bug','feature_request',
'account_access','shipping_delay','product_quality','general_inquiry']
):label::VARCHAR,
SNOWFLAKE.CORTEX.AI_CLASSIFY(body,
['billing_dispute','refund_request','technical_bug','feature_request',
'account_access','shipping_delay','product_quality','general_inquiry']
):score::FLOAT
FROM analytics_db.public.ticket_sentiment
WHERE ticket_id NOT IN (SELECT ticket_id FROM analytics_db.public.ticket_topics);
END;
ALTER TASK analytics_db.public.refresh_text_analytics RESUME;
Summary
This capstone built a text analytics system using Cortex AI functions: AI_SENTIMENT for scoring, AI_CLASSIFY for topic detection, and AI_SUMMARIZE for daily summaries. Results are presented in a Streamlit dashboard with KPIs, charts, filters, and drill-down tables, with automated daily refresh via Snowflake Tasks.