Text Analytics Toolbox: Getting started

Analyze your first text corpus, compute sentiment polarity, discover latent topics with LDA, extract TF-IDF keywords, and perform vector similarity search in Excel.

← Text Analytics Toolbox overview · Modeling guide · Methods guide · Examples · Reference

What you will learn

By following this guide, you will:

  • Run a reproducible first analysis classifying customer product reviews into sentiment categories with negation handling.
  • Discover latent themes across IT support tickets using Latent Dirichlet Allocation (LDA) topic modeling.
  • Extract salient unigram and bigram discriminative keywords across technical abstracts using sublinear TF-IDF.
  • Execute natural language vector searches across an internal knowledge base using cosine similarity scoring and relevance tiers.
  • Customize preprocessing directives on Stopwords & Rules (stopword additions, minimum token length thresholds, domain lexicons, and search queries).
  • Interpret the 7-check automated Diagnostics Suite to verify corpus integrity and vocabulary coverage.

Prerequisites and setup

Before running the toolbox:

  1. Open text-analytics-toolbox.xlsx in Excel with the Boardflare add-in enabled, or load the workbook in the Boardflare development workspace.
  2. Confirm the Python for Excel notebook environment is active.
  3. Review the six worksheets comprising the workbook:
    • Start: Master control panel for task selection, topic counts (\(K\)), term count limits, case-folding options, and the model catalog.
    • Corpus: Unstructured text database organized by Model, DocID, Category, and Text.
    • Stopwords & Rules: Preprocessing configurations, token length thresholds, sentiment dictionaries, and query strings.
    • Results: Published KPI summary block (A5:B17) and document-level scoring table (D5:I45).
    • Vocabulary & Topics: Extracted top TF-IDF keywords, latent topic word mixtures, and query vector weights (A5:F40).
    • Diagnostics: 7-check automated corpus verification suite reporting test severities (PASS / WARN) and diagnostic details (A5:C25).

Guided first run: Sentiment analysis on customer reviews

The default workbook configuration runs Product Reviews — Sentiment (customer_reviews_sentiment), evaluating customer feedback on consumer electronics.

Step 1: Verify Start controls

  1. Navigate to the Start sheet.
  2. Confirm cell B6 (Active task) is set to customer_reviews_sentiment.
  3. Confirm cell B7 (Num topics (K)) is set to 3.
  4. Confirm cell B8 (Top terms count) is set to 8.
  5. Confirm cell B9 (Min doc freq) is set to 1.
  6. Confirm cell B10 (Case fold) is set to Lowercase.

Step 2: Inspect the raw review text

Switch to the Corpus sheet. Notice the 12 customer feedback entries starting at row 6:

Model DocID Category Text
customer_reviews_sentiment 1 Reviews Excellent product, superior build quality and fast delivery. Very satisfied!
customer_reviews_sentiment 2 Reviews Terrible customer service, delayed shipment and defective item. Do not buy.
customer_reviews_sentiment 3 Reviews Decent performance for the price, but packaging was damaged on arrival.
customer_reviews_sentiment 4 Reviews Outstanding battery life, responsive screen, and intuitive interface.
customer_reviews_sentiment 5 Reviews Complete waste of money. Stopped working after two days of light use.
customer_reviews_sentiment 6 Reviews Average experience. Does what it says on the box without exceeding expectations.
… … … …
customer_reviews_sentiment 12 Reviews Poor build quality, loose hinges and noisy fan. Very disappointed.

Step 3: Run the notebook and examine Results

Click Run in the Python for Excel notebook (or allow automatic reactive recalculation). Within 0.05 seconds, scikit-learn and the sentiment engine process the texts and publish outputs back into the spreadsheet.

Navigate to the Results sheet to inspect the findings:

Corpus summary (Results!A5:B17)

  • Active task: Product Reviews — Sentiment
  • Algorithm: Lexicon
  • Corpus status: OPTIMAL
  • Document count: 12
  • Total tokens: 113
  • Unique vocabulary: 80 distinct terms
  • Mean doc length: 9.42 tokens/document
  • Dominant polarity: Positive
  • Positive documents: 5
  • Negative documents: 5
  • Matrix sparsity (%): 90.62%

Document scoring (Results!D5:I17)

DocID Category TokenCount SentimentScore Polarity DominantTopic
1 Reviews 10 0.4000 Positive Topic 1
2 Reviews 10 -0.4000 Negative Topic 2
3 Reviews 10 0.0000 Neutral Topic 3
4 Reviews 9 0.3333 Positive Topic 1
5 Reviews 10 -0.1000 Negative Topic 3
6 Reviews 9 0.0000 Neutral Topic 2
7 Reviews 9 0.2222 Positive Topic 1
8 Reviews 9 -0.2222 Negative Topic 2
9 Reviews 9 0.3333 Positive Topic 1
10 Reviews 9 -0.2222 Negative Topic 2
11 Reviews 9 0.3333 Positive Topic 1
12 Reviews 9 -0.3333 Negative Topic 2

[!TIP] Notice how Doc 2 (“Do not buy”) and Doc 6 (“without exceeding expectations”) handle negation. The engine recognizes negator tokens (not, never, no, without, n't) within a punctuation clause and flips the polarity of subsequent affective words.

Step 4: Examine Vocabulary & Topics

Switch to Vocabulary & Topics (A5:F40). The engine lists: - Top extracted corpus keywords ranked by mean TF-IDF weight: Term 1 is packaging (Score 0.0593, Weight 5.93%), followed by other frequent discriminatory tokens. - Domain affective terms matched in the corpus: positive terms (durable, ergonomic, flawless, intuitive) and negative terms (broken, clumsy, defective, disappointing).

Guided second run: Unsupervised topic modeling on support tickets

Now let’s switch tasks to discover latent operational themes across technical support tickets.

Step 1: Change the active task

  1. Navigate to the Start sheet.
  2. Click cell B6 and select support_tickets_topics from the validation dropdown.
  3. Observe cell B7 (Num topics (K)): keep it at 3.

Step 2: Review ticket contents on Corpus

Navigate to Corpus. Filter or scroll to the 8 support tickets registered under support_tickets_topics:

  • Doc 1: “Invoice charged twice on my credit card for annual subscription renewal.” (Billing)
  • Doc 2: “Application crashes with memory allocation error when exporting large reports.” (Technical)
  • Doc 3: “Cannot reset password because two-factor authentication SMS is not received.” (Account)
  • Doc 4: “Refund request for unused enterprise license seats after team restructuring.” (Billing)
  • Doc 5: “Database connection timeout occurred during scheduled automated nightly sync.” (Technical)
  • Doc 6: “User invitation link expired before team member could complete registration.” (Account)
  • Doc 7: “Credit card payment declined despite sufficient funds and active banking authorization.” (Billing)
  • Doc 8: “API endpoint returns 500 internal server error on bulk JSON data upload.” (Technical)

Step 3: Inspect topic model outputs on Results

Switch to Results. The summary block immediately reflects the LDA decomposition:

  • Active task: Support Tickets — Topic Model
  • Algorithm: LDA
  • Corpus status: OPTIMAL
  • Document count: 8
  • Total tokens: 80
  • Unique vocabulary: 62 distinct terms
  • Mean doc length: 10.00
  • Num topics (K): 3
  • Dominant topic: Topic 3
  • Avg topic confidence: 92.5%
  • Matrix sparsity (%): 86.90%

Examine the Document scoring table:

DocID Category TokenCount TopicWeight DominantTopic TopKeywords
1 Billing 9 0.9244 Topic 1 invoice, annual
2 Technical 10 0.9244 Topic 2 crashes, allocation
3 Account 10 0.9255 Topic 3 authentication, factor
4 Billing 10 0.9253 Topic 1 enterprise, refund
5 Technical 9 0.9244 Topic 2 automated, connection
6 Account 10 0.9246 Topic 3 expired, invitation
7 Billing 11 0.9312 Topic 1 active, authorization
8 Technical 11 0.9304 Topic 2 endpoint, api

Notice how the unsupervised LDA model cleanly separates the tickets into three distinct business domains: - Topic 1: Billing & invoices (invoice, annual, refund, authorization) - Topic 2: System technical failures (crashes, allocation, connection, endpoint) - Topic 3: User account & authentication (authentication, factor, invitation, expired)

Each document has an exceptionally clear topic probability (> 92% confidence).

Guided third run: TF-IDF discriminative keyword extraction

Next, analyze scientific abstracts to identify domain-specific terminology using n-gram TF-IDF weighting.

Step 1: Select the TF-IDF task

  1. On Start, set cell B6 to keyword_tfidf_extraction.
  2. Notice the configuration on Stopwords & Rules: row 10 specifies NgramMax = 2, instructing the vectorizer to extract both single words (unigrams) and two-word phrases (bigrams).

Step 2: Review Results

Switch to Results:

  • Active task: Document Corpus — TF-IDF
  • Algorithm: Tfidf
  • Corpus status: OPTIMAL
  • Document count: 8
  • Total tokens: 161
  • Unique vocabulary: 276 features (unigrams + bigrams)
  • Mean doc length: 20.12
  • Max n-gram order: 2
  • Top corpus keyword: quantum
  • Mean top TF-IDF: 0.2142
  • Matrix sparsity (%): 87.41%

In the Document scoring table, each scientific domain receives its top discriminative terms: - Doc 1 (Quantum Computing): Top score 0.2398, Top keyword quantum, Key n-grams "quantum, fault, circuits". - Doc 2 (Neuroscience): Top score 0.2285, Top keyword functional, Key n-grams "functional, fmri, mapping". - Doc 3 (Renewable Energy): Top score 0.2078, Top keyword battery, Key n-grams "battery, cells, coupled". - Doc 4 (Astrophysics): Top score 0.2114, Top keyword binary, Key n-grams "binary, cosmic, cosmological".

Guided fourth run: Cosine similarity search against query

Finally, run a vector similarity search across IT infrastructure articles using an ad-hoc query string.

Step 1: Select the search task

  1. On Start, set cell B6 to document_similarity_search.
  2. Switch to Stopwords & Rules. In cell C11, inspect the active query: "multi-factor authentication SSO login security"

Step 2: Review Results

Switch to Results:

  • Active task: Content Library — Search
  • Algorithm: Cosine
  • Corpus status: OPTIMAL
  • Document count: 8
  • Total tokens: 120
  • Unique vocabulary: 99
  • Search query: multi-factor authenticati...
  • High matches (≥0.4): 1
  • Top match doc: Doc 1.0 (0.577)

In the Document scoring table: - Doc 1 (Authentication): Score 0.5774 | Tier High (≥0.4) | Matched: authentication, factor, login, multi, sso - Doc 5 (Security): Score 0.1601 | Tier Low (<0.2) | Matched: security - Doc 6 (API): Score 0.1065 | Tier Low (<0.2) | Matched: authentication - All other documents: Score 0.0000 | Tier Low (<0.2) | Matched: None

Step 3: Test a custom query

  1. Go to Stopwords & Rules!C11.
  2. Replace the text with: bare-metal server GPU cluster NVMe
  3. Return to Results.
  4. Doc 7 (Hardware) immediately jumps to the top position with SimilarityScore = 0.6780, Tier High (≥0.4), matching bare, cluster, gpu, metal, nvme, server.

Customizing worksheet rules on Stopwords & Rules

The Stopwords & Rules worksheet controls all NLP feature engineering without editing Python code:

1. Adding custom stopwords

In cell C6, standard English stop words (the, and, of, to...) are assigned to Model = "ALL". To add domain-specific stopwords for customer reviews (e.g., item, box, days): - Insert a new row with Model = customer_reviews_sentiment, RuleType = Stopword, and Value = item, box, days. - The engine automatically combines these with scikit-learn’s 318 built-in English stopwords.

2. Modifying minimum token length

In cell C7, MinLength is set to 3. If your corpus contains noisy short abbreviations: - Change cell C7 to 5. - Tokens under 5 characters are filtered out during tokenization. For customer reviews, the active vocabulary contracts from 80 to 64 terms, while total tokens decrease from 113 to 75.

3. Expanding affective dictionaries

Rows 8 and 9 declare PositiveWords and NegativeWords for customer_reviews_sentiment. - To recognize new product terminology, append words separated by commas (e.g., bulletproof, snappy, ergonomic to PositiveWords).

Interpreting the 7-check Diagnostics Suite

Navigate to the Diagnostics sheet (A5:C12). The toolbox validates every run across 7 quality dimensions:

Check Baseline Severity Meaning and Thresholds
1. Corpus ingestion PASS Confirms the corpus contains at least 4 valid documents (n_docs >= 4).
2. Vocabulary filtering PASS Confirms the post-filtering vocabulary contains at least 10 distinct terms. Returns WARN if aggressive stopword or min_df rules prune the vocabulary below 10.
3. Matrix sparsity PASS Verifies term-document matrix sparsity is under 99.5%. Sparsity > 99.5% warns that documents share very few overlapping terms.
4. Lexical diversity PASS Checks the Type-Token Ratio (\(\text{TTR} = V / N\)). A healthy corpus falls between \(0.10\) and \(0.95\). TTR < 0.10 indicates extreme repetition; TTR > 0.95 indicates a collection of singletons.
5. Sentiment & query coverage PASS Task-dependent coverage: confirms affective matches (> 0 hits), discrete count presence for LDA, non-zero TF-IDF peaks, or query vocabulary matches.
6. Topic model balance PASS Task-dependent balance: checks for both positive and negative review representations, verifies topic prevalence range (\(\ge 5.0\%\)), or checks for at least one high/moderate query match.
7. Rule configuration PASS Confirms all rules on Stopwords & Rules parsed correctly without syntax or type errors.

Next steps

  • Explore the Modeling guide to learn how to structure your own text corpora and add custom model tasks.
  • Read the Methods guide for mathematical deep dives into sublinear TF-IDF, variational LDA inference, and cosine distance.
  • Review Worked examples for four complete enterprise case studies.
  • Check the Reference for cell coordinates, parameter defaults, and troubleshooting advice.