Text Analytics Toolbox: Worked examples
Four comprehensive case studies covering customer sentiment analysis, support ticket topic discovery, scientific keyword extraction, and vector similarity search.
← Text Analytics Toolbox overview · Getting started · Modeling guide · Methods guide · Reference
Overview
This guide walks through four comprehensive, end-to-end case studies demonstrating how the Text Analytics Toolbox extracts actionable business intelligence from unstructured text in Excel.
Each example includes: - Problem context and objective. - Sample input text snippets from Corpus. - Configuration controls and preprocessing rules. - Exact, verified numerical outputs and metric interpretations.
Example 1: Product Reviews Sentiment Analysis
Scenario context
A consumer hardware brand sells wireless noise-canceling headphones. After a major product release, the customer experience team exports 12 representative customer reviews into Excel to quantify customer satisfaction, detect recurring defects, and verify sentiment polarity.
Input data snippets (Corpus!A5:D17)
Doc 1: "Excellent product, superior build quality and fast delivery. Very satisfied!"
Doc 2: "Terrible customer service, delayed shipment and defective item. Do not buy."
Doc 3: "Decent performance for the price, but packaging was damaged on arrival."
Doc 4: "Outstanding battery life, responsive screen, and intuitive interface."
Doc 5: "Complete waste of money. Stopped working after two days of light use."
Doc 6: "Average experience. Does what it says on the box without exceeding expectations."
Doc 8: "Horrible experience, misleading specifications and zero return support."
Doc 11: "Fast delivery, wonderful packaging, and very helpful user manual."
Doc 12: "Poor build quality, loose hinges and noisy fan. Very disappointed."Configuration and rules
- Start controls:
Active task = customer_reviews_sentimentCase fold = LowercaseMin doc freq = 1
- Stopwords & Rules:
- English function stopwords applied (
the, and, of, to, in, a, is...). MinLength = 3tokens.- Domain positive words added:
durable, ergonomic, sleek, flawless, effortless, intuitive, premium, seamless. - Domain negative words added:
fragile, clumsy, sluggish, broken, unusable, overpriced, defective, disappointing.
- English function stopwords applied (
Numerical results & verification
Fit summary (Results!A5:B17)
| Metric | Value | Interpretation |
|---|---|---|
| Active task | Product Reviews — Sentiment |
Evaluated via Lexicon model |
| Corpus status | OPTIMAL |
Successfully ingested and tokenized |
| Document count | 12 |
12 customer reviews |
| Total tokens | 113 |
Average 9.42 clean tokens per review |
| Unique vocabulary | 80 |
80 distinct terms post-filtering |
| Dominant polarity | Positive |
Plurality of reviews reflect satisfaction |
| Positive documents | 5 |
5 reviews with \(s > +0.05\) |
| Negative documents | 5 |
5 reviews with \(s < -0.05\) |
| Matrix sparsity (%) | 90.62% |
Typical sparse review term distribution |
Document scoring highlights (Results!D5:I17)
| DocID | Text Excerpt | Tokens | Score | Polarity | Key Mechanism |
|---|---|---|---|---|---|
| 1 | “superior build quality and fast delivery…” | 10 | +0.4000 |
Positive | 4 positive terms (superior, fast, satisfied, excellent) |
| 2 | “defective item. Do not buy.” | 10 | -0.4000 |
Negative | Lookback negation flips buy; matches terrible, delayed, defective |
| 3 | “Decent performance… packaging damaged” | 10 | 0.0000 |
Neutral | 1 positive (decent), 1 negative (damaged); net score within deadband |
| 5 | “Complete waste of money…” | 10 | -0.1000 |
Negative | Matches negative term waste |
| 6 | “without exceeding expectations” | 9 | 0.0000 |
Neutral | without negates positive word exceeding |
| 12 | “Poor build quality, loose hinges and noisy fan” | 9 | -0.3333 |
Negative | Matches poor, loose, noisy, disappointed |
Vocabulary insights (Vocabulary & Topics!A5:F15)
- Top TF-IDF keyword:
packaging(Mean TF-IDF0.0593, Weight5.93%). This reveals that packaging condition is a frequent driver of neutral and dissatisfied ratings, providing a concrete operational target for supply chain improvement.
Example 2: Technical Support Tickets Topic Discovery
Scenario context
A SaaS platform’s technical support desk receives hundreds of unstructured customer support tickets daily. Supervisors need an unsupervised method to cluster tickets into functional queues (Billing, Infrastructure, Authentication) without manual tagging.
Input data snippets (Corpus!A18:D25)
Doc 1 (Billing): "Invoice charged twice on my credit card for annual subscription renewal."
Doc 2 (Technical): "Application crashes with memory allocation error when exporting large reports."
Doc 3 (Account): "Cannot reset password because two-factor authentication SMS is not received."
Doc 4 (Billing): "Refund request for unused enterprise license seats after team restructuring."
Doc 5 (Technical): "Database connection timeout occurred during scheduled automated nightly sync."
Doc 6 (Account): "User invitation link expired before team member could complete registration."
Doc 7 (Billing): "Credit card payment declined despite sufficient funds and active banking authorization."
Doc 8 (Technical): "API endpoint returns 500 internal server error on bulk JSON data upload."Configuration and rules
- Start controls:
Active task = support_tickets_topicsNum topics (K) = 3Top terms count = 8
- Stopwords & Rules:
- Domain stopwords filtered out:
ticket, issue, error, please, thanks, help.
- Domain stopwords filtered out:
Numerical results & verification
Fit summary (Results!A5:B17)
| Metric | Value | Interpretation |
|---|---|---|
| Active task | Support Tickets — Topic Model |
Variational Bayes LDA |
| Document count | 8 |
8 tickets |
| Total tokens | 80 |
10.0 tokens per ticket on average |
| Unique vocabulary | 62 |
62 distinct technical terms |
| Num topics (K) | 3 |
Decomposed into 3 semantic clusters |
| Dominant topic | Topic 3 |
Highest aggregate topic probability |
| Avg topic confidence | 92.5% |
High categorical clarity |
| Matrix sparsity (%) | 86.90% |
Compact vocabulary overlap |
Cluster discovery and document assignments (Results!D5:I13)
| DocID | Ground Truth Category | Dominant Topic | Topic Weight | Extracted Salient Keywords |
|---|---|---|---|---|
| 1 | Billing | Topic 1 | 0.9244 |
invoice, annual |
| 2 | Technical | Topic 2 | 0.9244 |
crashes, allocation |
| 3 | Account | Topic 3 | 0.9255 |
authentication, factor |
| 4 | Billing | Topic 1 | 0.9253 |
enterprise, refund |
| 5 | Technical | Topic 2 | 0.9244 |
automated, connection |
| 6 | Account | Topic 3 | 0.9246 |
expired, invitation |
| 7 | Billing | Topic 1 | 0.9312 |
active, authorization |
| 8 | Technical | Topic 2 | 0.9304 |
endpoint, api |
Topic interpretation (Vocabulary & Topics!A14:F16)
- Topic 1 (Billing & Licensing, 37.1% prevalence): Top terms
invoice,credit,card,subscription,refund. - Topic 2 (System Performance & Crashes, 36.9% prevalence): Top terms
crashes,memory,database,timeout,endpoint. - Topic 3 (User Access & 2FA, 26.0% prevalence): Top terms
authentication,password,invitation,expired.
The unsupervised LDA model achieved 100% concordance with the ground-truth business categories without seeing training labels.
Example 3: Scientific Research Abstracts TF-IDF Extraction
Scenario context
A research library curates academic abstracts across cutting-edge science and technology domains. Researchers need to extract the most discriminative unigrams and bigram phrases to build an automated catalog index.
Input data snippets (Corpus!A26:D33)
Doc 1 (Quantum Computing): "Quantum superposition and entangled qubits enable exponential speedup in quantum computing algorithms, while overcoming decoherence requires fault-tolerant error correction circuits."
Doc 2 (Neuroscience): "Neural plasticity and synaptic transmission govern cognitive pathways in the brain, with functional fMRI cortical mapping revealing adaptive network reorganization."
Doc 3 (Renewable Energy): "Perovskite solar cells dramatically improve photovoltaic efficiency in renewable energy generation, coupled with grid energy storage to mitigate lithium-ion battery degradation."
Doc 4 (Astrophysics): "Gravitational wave interferometry detects binary neutron star mergers across cosmic distances, analyzing cosmological redshift signatures and unexplained dark matter interactions."
Doc 5 (Cryptography): "Post-quantum cryptography relies on lattice-based encryption and zero-knowledge proofs to secure key exchange protocols against quantum algorithm decryption attacks."
Doc 6 (Genomics): "CRISPR-Cas9 gene editing and high-throughput RNA sequencing facilitate precise genomic variant calling while profiling complex epigenetic DNA methylation patterns."
Doc 7 (Robotics): "Reinforcement learning guides autonomous navigation and robotic trajectory planning, leveraging multi-sensor LiDAR SLAM for real-time robotic localization in dynamic environments."
Doc 8 (Climate Science): "Oceanic circulation and greenhouse gas radiative forcing drive global climate feedback loops, validated through high-resolution atmospheric aerosol modeling simulations."Configuration and rules
- Start controls:
Active task = keyword_tfidf_extractionMin doc freq = 1Top terms count = 8
- Stopwords & Rules:
NgramMax = 2(Extracts both individual words and two-word bigram sequences).
Numerical results & verification
Fit summary (Results!A5:B17)
| Metric | Value | Interpretation |
|---|---|---|
| Active task | Document Corpus — TF-IDF |
Sublinear TF-IDF extraction |
| Document count | 8 |
8 scientific abstracts |
| Total tokens | 161 |
20.12 tokens per abstract |
| Unique vocabulary | 276 |
Feature union of unigrams and bigrams |
| Max n-gram order | 2 |
Bigram extraction active |
| Top corpus keyword | quantum |
Most prominent cross-document term |
| Mean top TF-IDF | 0.2142 |
Strong discriminative feature separation |
| Matrix sparsity (%) | 87.41% |
Sparse, highly specialized vocabulary |
Extracted document key terms (Results!D5:I13)
| DocID | Domain | Top Keyword | Top TF-IDF | Key N-grams (Unigrams & Bigrams) |
|---|---|---|---|---|
| 1 | Quantum Computing | quantum |
0.2398 |
quantum, fault, circuits |
| 2 | Neuroscience | functional |
0.2285 |
functional, fmri, mapping |
| 3 | Renewable Energy | battery |
0.2078 |
battery, cells, coupled |
| 4 | Astrophysics | binary |
0.2114 |
binary, cosmic, cosmological |
| 5 | Cryptography | quantum |
0.2143 |
quantum, algorithm, attacks |
| 6 | Genomics | calling |
0.2073 |
calling, cas9, complex |
| 7 | Robotics | robotic |
0.2748 |
robotic, autonomous, dynamic |
| 8 | Climate Science | aerosol |
0.2294 |
aerosol, atmospheric, circulation |
Sublinear TF-IDF cleanly suppresses generic terms while isolating high-impact technical terminology for each scientific discipline.
Example 4: IT Knowledge Base Cosine Similarity Search
Scenario context
An enterprise IT helpdesk maintains a repository of infrastructure runbooks. When an engineer experiences an authentication outage, they enter an ad-hoc query into Excel to retrieve the most relevant documentation.
Knowledge base articles (Corpus!A34:D41)
Doc 1 (Authentication): "Configuring multi-factor authentication (MFA), SAML single sign-on (SSO), and OAuth2 identity providers for secure enterprise login."
Doc 2 (Billing): "Managing enterprise subscription licenses, seat allocations, prorated invoices, and automated credit card payment methods."
Doc 3 (Networking): "Virtual private cloud (VPC) network peering, transit gateway configuration, and low-latency hybrid cloud VPN tunnels."
Doc 4 (Storage): "Configuring S3 object storage lifecycle policies, server-side encryption with KMS keys, and cross-region replication."
Doc 5 (Security): "Role-based access control (RBAC), least-privilege IAM policies, audit logging, and automated threat detection alerts."
Doc 6 (API): "REST API rate limiting, webhook payload delivery, bearer token authentication, and idempotency key headers."
Doc 7 (Hardware): "Dedicated bare-metal server provisioning, GPU cluster orchestration, and high-performance NVMe storage arrays."
Doc 8 (Deployment): "Continuous integration and deployment (CI/CD) pipelines, Docker container registry images, and blue-green Kubernetes rollouts."Search query setup
On Stopwords & Rules!C11, the active query is defined: multi-factor authentication SSO login security
Numerical results & verification
Fit summary (Results!A5:B17)
| Metric | Value | Interpretation |
|---|---|---|
| Active task | Content Library — Search |
Vector cosine similarity |
| Document count | 8 |
8 infrastructure runbooks |
| Total tokens | 120 |
15.0 tokens per document |
| Unique vocabulary | 99 |
99 unique technical tokens |
| Search query | multi-factor authenticati... |
5 matching terms in vocabulary |
| High matches (≥0.4) | 1 |
Doc 1 qualifies as high relevance |
| Top match doc | Doc 1.0 (0.577) |
Doc 1 with cosine score 0.5774 |
| Matrix sparsity (%) | 86.62% |
High selectivity |
Similarity match rankings (Results!D5:I13)
| DocID | Category | Cosine Similarity | Match Tier | Matched Query Vocabulary Terms |
|---|---|---|---|---|
| 1 | Authentication | 0.5774 |
High (≥0.4) | authentication, factor, login, multi, sso |
| 5 | Security | 0.1601 |
Low (<0.2) | security |
| 6 | API | 0.1065 |
Low (<0.2) | authentication |
| 2 | Billing | 0.0000 |
Low (<0.2) | None |
| 3 | Networking | 0.0000 |
Low (<0.2) | None |
| 4 | Storage | 0.0000 |
Low (<0.2) | None |
| 7 | Hardware | 0.0000 |
Low (<0.2) | None |
| 8 | Deployment | 0.0000 |
Low (<0.2) | None |
Doc 1 matches 5 out of 5 non-stopword query tokens, producing an unambiguous high-relevance score of 0.5774. Peripheral documents matching only one query token (Docs 5 and 6) are cleanly filtered into the low-relevance tier.