Word Cloud
Introduction
Section titled “Introduction”This article looks at Astrato’s Word Cloud visualization.
A Word cloud data object consists of a textual dimension and numeric measures,
The word cloud gives users a complete view of all the values in the dimension and highlights the significance of each one of them by giving each record a different text size according to its measure result.
Word Cloud’s common use cases are:
-
Social media analytics
-
Marketing search engine analytics
-
Feedback and Survey Analytics
Building a Word Cloud
Section titled “Building a Word Cloud”Add a Word Cloud
Section titled “Add a Word Cloud”Word cloud can be found in the visualization tile, it positioned in the bottom of the visualization list
Define a Word Cloud
Section titled “Define a Word Cloud”-
Add one dimension and one measure in the Data tab of the properties panel.
Style a Word Cloud
Section titled “Style a Word Cloud”
-
Set Font family and style
-
Scaling- Select the scaling method out of these four options:
-
Sqrt - Square root (default)
-
Linear
-
Logarithmic
-
Squared
-
-
Max chars- set the maximum number of characters to show for each dimension record, 40 is the default value.
-
Word Layout Seed - click on the refresh icon or enter a number in the box to try different word placements.
-
Color - Select the method of coloring the chart out of these three options:
-
Single - only one color for all words
-
Categorical- color the words by the dimension order
-
Gradient - color the words by the measure result from lowest to highest
-
-
Conditional coloring - add specific conditions on dimension or measure values and how to color them
Prepare data for use in a wordcloud
Section titled “Prepare data for use in a wordcloud”This guide assumes you have a long text field, and are looking to break down the text into clean, individual words.
We recommend one of two approaches, depending on your use case.
-
You want a wordcloud overview, most likely for static reporting, where drill-down is not needed.
-
You want a super-powerful wordcloud and are happy to have some extra compute and storage used, to have a limitless drill-down wordcloud. [Recommended]
Pre-aggregated wordcloud. This is more efficient but should be disconnected from the data model, the result looks like this:
select * FROM ( WITH /* Split each message into individual words */ word_split AS ( SELECT VALUE::STRING AS word FROM ASTRATO_DEMO.PUBLIC.ASTRATO_DEMO, //🔴 Update the table name, currently it is `ASTRATO_DEMO.PUBLIC.ASTRATO_DEMO` LATERAL FLATTEN(INPUT => SPLIT(message, ' ')) //🔴 Update the field name, currently it is `message` ), /* Remove any non-alphabetic characters from words and filter out empty strings */ cleaned_words AS ( SELECT REGEXP_REPLACE(word, '[^a-zA-Z]', '') AS cleaned_word FROM word_split WHERE LENGTH(word) > 0 ) /* Aggregate and count word occurrences */ SELECT cleaned_word, COUNT(*) AS frequency FROM cleaned_words WHERE LENGTH(cleaned_word) > 0 GROUP BY cleaned_word ORDER BY COUNT(*) DESC limit 300 ) AS subquerywhere cleaned_word is not null;Comment-level/Row-level wordcloud data. This produces many rows, can be connected to the data model, ideally as a dynamic table if you are using Snowflake. Otherwise use a view to ensure that this updates along with the parent table. The result, with the CommentID looks like this:
WITH /* Split each message into individual words, including the ID */ word_split AS ( SELECT _ID as CommentID, //🔴 Update with the actual column name for the ID VALUE::STRING AS word FROM ASTRATO_DEMO.PUBLIC.ASTRATO_DEMO, //🔴 Update the table name if needed LATERAL FLATTEN(INPUT => SPLIT(message, ' ')) //🔴 Update `message` if the column name is different ), /* Remove any non-alphabetic characters from words, filter out empty strings, and include the ID */ cleaned_words AS ( SELECT CommentID, REGEXP_REPLACE(word, '[^a-zA-Z]', '') AS cleaned_word FROM word_split WHERE LENGTH(word) > 0 ) /* Aggregate and count word occurrences, grouped by ID and word */ SELECT CommentID, cleaned_word, COUNT(*) AS frequency FROM cleaned_words WHERE LENGTH(cleaned_word) > 0 GROUP BY CommentID, cleaned_word ORDER BY CommentID, COUNT(*) DESC;Stopwords
Section titled “Stopwords”See below for a CSV containing words that do not represent topics or themes, example: and, the, a, I, am.
If you prefer to add it directly into a single query rather than adding in a new table for stopwords, just add the statement below into your SQL statement.
and lower(cleaned_word) not in('dont','will','now','im','didnt','de','x', 'y', 'your', 'yours', 'yourself', 'yourselves', 'you', 'yond', 'yonder', 'yon', 'ye', 'yet', 'z', 'zillion', 'j', 'u', 'umpteen', 'usually', 'us', 'username', 'uponed', 'upons', 'uponing', 'upon', 'ups', 'upping', 'upped', 'up', 'unto', 'until', 'unless', 'unlike', 'unliker', 'unlikest', 'under', 'underneath', 'use', 'used', 'usedest', 'r', 'rath', 'rather', 'rathest', 'rathe', 're', 'relate', 'related', 'relatively', 'regarding', 'really', 'res', 'respecting', 'respectively', 'q', 'quite', 'que', 'qua', 'n', 'neither', 'neath', 'neaths', 'neath', 'nethe', 'nethermost', 'necessary', 'necessariest', 'necessarier', 'never', 'nevertheless', 'nigh', 'nighest', 'nigher', 'nine', 'noone', 'nobody', 'nobodies', 'nowhere', 'nowheres', 'no', 'noes', 'nor', 'nos', 'no-one', 'none', 'not', 'notwithstanding', 'nothings', 'nothing', 'nathless', 'natheless', 't', 'ten', 'tills', 'till', 'tilled', 'tilling', 'to', 'towards', 'toward', 'towardest', 'towarder', 'together', 'too', 'thy', 'thyself', 'thus', 'than', 'that', 'those', 'thou', 'though', 'thous', 'thouses', 'thoroughest', 'thorougher', 'thorough', 'thoroughly', 'thru', 'thruer', 'thruest', 'thro', 'through', 'throughout', 'throughest', 'througher', 'thine', 'this', 'thises', 'they', 'thee', 'the', 'then', 'thence', 'thenest', 'thener', 'them', 'themselves', 'these', 'therer', 'there', 'thereby', 'therest', 'thereafter', 'therein', 'thereupon', 'therefore', 'their', 'theirs', 'thing', 'things', 'three', 'two', 'o', 'oh', 'owt', 'owning', 'owned', 'own', 'owns', 'others', 'other', 'otherwise', 'otherwisest', 'otherwiser', 'of', 'often', 'oftener', 'oftenest', 'off', 'offs', 'offest', 'one', 'ought', 'oughts', 'our', 'ours', 'ourselves', 'ourself', 'out', 'outest', 'outed', 'outwith', 'outs', 'outside', 'over', 'overallest', 'overaller', 'overalls', 'overall', 'overs', 'or', 'orer', 'orest', 'on', 'oneself', 'onest', 'ons', 'onto', 'a', 'atween', 'at', 'athwart', 'atop', 'afore', 'afterward', 'afterwards', 'after', 'afterest', 'afterer', 'ain', 'an', 'any', 'anything', 'anybody', 'anyone', 'anyhow', 'anywhere', 'anent', 'anear', 'and', 'andor', 'another', 'around', 'ares', 'are', 'aest', 'aer', 'against', 'again', 'accordingly', 'abaft', 'abafter', 'abaftest', 'abovest', 'above', 'abover', 'abouter', 'aboutest', 'about', 'aid', 'amidst', 'amid', 'among', 'amongst', 'apartest', 'aparter', 'apart', 'appeared', 'appears', 'appear', 'appearing', 'appropriating', 'appropriate', 'appropriatest', 'appropriates', 'appropriater', 'appropriated', 'already', 'always', 'also', 'along', 'alongside', 'although', 'almost', 'all', 'allest', 'aller', 'allyou', 'alls', 'albeit', 'awfully', 'as', 'aside', 'asides', 'aslant', 'ases', 'astrider', 'astride', 'astridest', 'astraddlest', 'astraddler', 'astraddle', 'availablest', 'availabler', 'available', 'aughts', 'aught', 'vs', 'v', 'variousest', 'variouser', 'various', 'via', 'vis-a-vis', 'vis-a-viser', 'vis-a-visest', 'viz', 'very', 'veriest', 'verier', 'versus', 'k', 'g', 'go', 'gone', 'good', 'got', 'gotta', 'gotten', 'get', 'gets', 'getting', 'b', 'by', 'byandby', 'by-and-by', 'bist', 'both', 'but', 'buts', 'be', 'beyond', 'because', 'became', 'becomes', 'become', 'becoming', 'becomings', 'becominger', 'becomingest', 'behind', 'behinds', 'before', 'beforehand', 'beforehandest', 'beforehander', 'bettered', 'betters', 'better', 'bettering', 'betwixt', 'between', 'beneath', 'been', 'below', 'besides', 'beside', 'm', 'my', 'myself', 'mucher', 'muchest', 'much', 'must', 'musts', 'musths', 'musth', 'main', 'make', 'mayest', 'many', 'mauger', 'maugre', 'me', 'meanwhiles', 'meanwhile', 'mostly', 'most', 'moreover', 'more', 'might', 'mights', 'midst', 'midsts', 'h', 'huh', 'humph', 'he', 'hers', 'herself', 'her', 'hereby', 'herein', 'hereafters', 'hereafter', 'hereupon', 'hence', 'hadst', 'had', 'having', 'haves', 'have', 'has', 'hast', 'hardly', 'hae', 'hath', 'him', 'himself', 'hither', 'hitherest', 'hitherer', 'his', 'how-do-you-do', 'however', 'how', 'howbeit', 'howdoyoudo', 'hoos', 'hoo', 'w', 'woulded', 'woulding', 'would', 'woulds', 'was', 'wast', 'we', 'wert', 'were', 'with', 'withal', 'without', 'within', 'why', 'what', 'whatever', 'whateverer', 'whateverest', 'whatsoeverer', 'whatsoeverest', 'whatsoever', 'whence', 'whencesoever', 'whenever', 'whensoever', 'when', 'whenas', 'whether', 'wheen', 'whereto', 'whereupon', 'wherever', 'whereon', 'whereof', 'where', 'whereby', 'wherewithal', 'wherewith', 'whereinto', 'wherein', 'whereafter', 'whereas', 'wheresoever', 'wherefrom', 'which', 'whichever', 'whichsoever', 'whilst', 'while', 'whiles', 'whithersoever', 'whither', 'whoever', 'whosoever', 'whoso', 'whose', 'whomever', 's', 'syne', 'syn', 'shalling', 'shall', 'shalled', 'shalls', 'shoulding', 'should', 'shoulded', 'shoulds', 'she', 'sayyid', 'sayid', 'said', 'saider', 'saidest', 'same', 'samest', 'sames', 'samer', 'saved', 'sans', 'sanses', 'sanserifs', 'sanserif', 'so', 'soer', 'soest', 'sobeit', 'someone', 'somebody', 'somehow', 'some', 'somewhere', 'somewhat', 'something', 'sometimest', 'sometimes', 'sometimer', 'sometime', 'several', 'severaler', 'severalest', 'serious', 'seriousest', 'seriouser', 'senza', 'send', 'sent', 'seem', 'seems', 'seemed', 'seemingest', 'seeminger', 'seemings', 'seven', 'summat', 'sups', 'sup', 'supping', 'supped', 'such', 'since', 'sine', 'sines', 'sith', 'six', 'stop', 'stopped', 'p', 'plaintiff', 'plenty', 'plenties', 'please', 'pleased', 'pleases', 'per', 'perhaps', 'particulars', 'particularly', 'particular', 'particularest', 'particularer', 'pro', 'providing', 'provides', 'provided', 'provide', 'probably', 'l', 'layabout', 'layabouts', 'latter', 'latterest', 'latterer', 'latterly', 'latters', 'lots', 'lotting', 'lotted', 'lot', 'lest', 'less', 'ie', 'ifs', 'if', 'i', 'info', 'information', 'itself', 'its', 'it', 'is', 'idem', 'idemer', 'idemest', 'immediate', 'immediately', 'immediatest', 'immediater', 'in', 'inwards', 'inwardest', 'inwarder', 'inward', 'inasmuch', 'into', 'instead', 'insofar', 'indicates', 'indicated', 'indicate', 'indicating', 'indeed', 'inc', 'f', 'fact', 'facts', 'fs', 'figupon', 'figupons', 'figuponing', 'figuponed', 'few', 'fewer', 'fewest', 'frae', 'from', 'failing', 'failings', 'five', 'furthers', 'furtherer', 'furthered', 'furtherest', 'further', 'furthering', 'furthermore', 'fourscore', 'followthrough', 'for', 'forwhy', 'fornenst', 'formerly', 'former', 'formerer', 'formerest', 'formers', 'forbye', 'forby', 'fore', 'forever', 'forer', 'fores', 'four', 'd', 'ddays', 'dday', 'do', 'doing', 'doings', 'doe', 'does', 'doth', 'downwarder', 'downwardest', 'downward', 'downwards', 'downs', 'done', 'doner', 'dones', 'donest', 'dos', 'dost', 'did', 'differentest', 'differenter', 'different', 'describing', 'describe', 'describes', 'described', 'despiting', 'despites', 'despited', 'despite', 'during', 'c', 'cum', 'circa', 'chez', 'cer', 'certain', 'certainest', 'certainer', 'cest', 'canst', 'cannot', 'cant', 'cants', 'canting', 'cantest', 'canted', 'co', 'could', 'couldst', 'comeon', 'comeons', 'come-ons', 'come-on', 'concerning', 'concerninger', 'concerningest', 'consequently', 'considering', 'e', 'eg', 'eight', 'either', 'even', 'evens', 'evenser', 'evensest', 'evened', 'evenest', 'ever', 'everyone', 'everything', 'everybody', 'everywhere', 'every', 'ere', 'each', 'et', 'etc', 'elsewhere', 'else', 'ex', 'excepted', 'excepts', 'except', 'excepting', 'exes', 'enough')