Suppose you are a social media analyst, and you have to analyze the media coverage related to something really hot like FIFA’s latest corruption scandal, using nothing but a spreadsheet.
That’s because you don’t expect a lot of data, and you don’t have enough budget to use a Social Analytics platform.
As a social media analyst, your main task is to understand social media data, and translate it for stakeholders who would not otherwise be familiar with its benefits for the business. Usually, it means you have to present your findings using some kind of reports.
When you finally finish that massive report you’ve been working on for days and then decide to cc everyone so they can see how awesome you are
Let’s consider that you have to perform a small analysis focused on the FIFA brand its recent corruption scandal. You should start with two different tasks focused on the content:
- analyze the content taken from different sources, like news articles and tweets: it would be enough a small quantity of tweets written in Italian and English;
- summarize the content using tag clouds and other visualizations to make your boss happy but, more importantly, aware of what is happening around that brand.
Analyze the content using a simple Google Sheet
What if we could have a computer do all the heavy lifting for us – like, say, extracting the main topics expressed inside the article?
That way, you can stay focused on the content, to better understand the context. Moreover, you have these automatically extracted topics that you can use to produce useful hashtag suggestions to follow other related news. Last, but not least, you can select the most relevant topics to enrich your analysis with contextual information taken from Wikipedia.
Let’s see how we could do all this in three simple steps:
- copy and paste the URL you want to analize inside a cell of your spreadsheet;
- select the cell, and navigate through Add-ons / Text Mining / Analyze text, then select the option “Entity Extraction” inside the sidebar. Click on the “Analyze text” button to perform the extraction;
- inside a new sheet, called “Analysis“, you will find all over the entities extracted from the article. In this example, the sheet contains over 66 rows. Don’t forget that this new sheet include several columns:
- “Text“: to keep control of the the text analysed;
- “Highlight“: all the words identified in the text submitted;
- “Confidence“: the confidence value, a numeric estimation of the quality of the annotation, which ranges between 0.6 and 1. Entities with a confidence value below 0.6 are hidden (0.6 is the default threshold);
- “Entity Name“: the conceptual entity as it appears in our system;
- “Types“: the types associated with the entity, extracted from Wikipedia;
- “Categories“: the corresponding Wikipedia categories of every entity;
- “Wikipedia URL“: the link to the Wikipedia page, which you can use to pull additional data about the entity, and enrich your content.
- you can use the column “Entity Name” to build a tag cloud that is/should be more insightful and focused on the real content of the article. Copy and paste these values inside the text box you find on the bottom of the page at http://tagcrowd.com/, and click the “Visualize” button;
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364652022 FIFA World CupAndré MartyArrestAssociation footballAssociation footballAttorney generalBriberyBriberyChuck BlazerCNNCNNCNNCNNCollateral damageExecutive (government)ExtraditionFIFAFIFAFIFAFIFAFIFAFIFAFIFAFIFAFIFAFIFAFIFA (video game series)FIFA World CupFIFA World CupFIFA World CupFIFA World CupFlagship (broadcasting)FraudHammer-onIndictmentIRS Criminal Investigation DivisionMail and wire fraudMarketingMass mediaMoney launderingNew York CityPleaPolitical corruptionPolitical corruptionPrecedentProsecutorQatarQatarRacket (crime)Richard Weber (explorer)RussiaRussian Football UnionSepp BlatterSwitzerlandSwitzerlandSwitzerlandSwitzerlandSwitzerlandTax evasionUnited States Department of JusticeWhistleblowerWomen's association footballZürichZürich
- what you get is a useful keyword cloud, that contains more contextual information when compared to other word clouds. Compare it with the one built using the “SEO & Website Analysis” add-on, for example: (the smallest one at the bottom)
You can repeat this workflow analyzing different URLs as a source, to collect other points of view. You can iterate over the following, repeating the process from step 2:
This is the result:
Our keyword cloud is really useful to find concepts and terms related to the FIFA corruption scandal: now we can use the keywords as hashtag suggestions.
Mining sentiment from tweets
Let’s see how to mine the sentiment from a selection of tweets, using the same add-on for Google Sheets:
- collect some tweets with the hashtag #FIFA written in Italian and in English (since these are the two languages our Sentiment Analysis supports at the moment). It’s simple selecting the Italian or English language from the Advanced Search page on search.twitter.com;
- copy and paste these tweets inside your spreadsheet, if you don’t have time to follow step 1:
123456789101112131415161718192021222324252627282930313233Blatter will pay anything, bribe anyone, to get a prosecution. #Blatter #BlatterOut #corruption #bribery #LeeNelsonOf course! #Russia and #Qatar may lose World Cups if evidence of #bribery is found http://gu.com/p/49hyg/stw #fifa #blatter #worldcupAcross the Pond http://buff.ly/1f7l6Io #FIFA #Corruption #scandal #sports #extradition #SeppBlatter #briberyFirst #FIFA official extradited to the US in huge corruption probe #Football #Corruption #Worldfootball #USAFirst #FIFA official accused of #corruption extradited to U.S. from Switzerland http://upi.com/5172838Comparing FIFA to Mafia 'insulting to Mafia': US senator via @ABCNews #FIFA #mafia #corruption #RICO#Fifa corruption crisis: Key questions answered http://www.bbc.co.uk/news/world-europe-32897066 … #fifa #corruptionCasey Research. Doug Casey on the Real #FIFA Scandal. Why is it none of our business? http://www.ntmarkets.com/2015/08/casey-research-doug-casey-on-the-real-fifa-scandal-why-is-it-none-of-our-business/. @MenInBlazers' Roger Bennett talks brand sponsors' role in #FIFA indictment scandal https://shar.es/1tjBi2 #soccer @AMA_MarketingI wonder if the FIFA indictment worries Cristiano Ronaldo and Lionel Messi at all as they speak for the $HLF pyramid scheme.FIFA, dopo Blatter anche il segretario generale Jerome Valcke… http://dlvr.it/BdFfHf #FIFA #UpdateNews #dimissioni #scandalo #segretario#Scandalo #Fifa, #Blatter attacca: 'Da #Sarkozy e #Wulff pressioni per i Mondiali in Qatar' http://calcio.fanpage.it/scandalo-fifa-blatter-attacca-da-sarkozy-e-wulff-pressioni-per-i-mondiali-in-qatar/ via @fanpageLe alte sfere e il mondo periferico http://dlvr.it/BCt6NW #Nazionali #Blatter #FIFA #nazionali #scandalo#Scandalo #Fifa, nel mirino anche la #Nike http://in.diggita.it/BC2QsQAnche il Papa contro la Fifa: bloccati i soldi sporchi http://www.vogliadiroma.com/anche-il-papa-contro-la-fifa-bloccati-i-soldi-sporchi/ … #Fifa #Papa #scandalo #FedercalcioFifa, arrestato un funzionario argentino a Bolzano http://in.diggita.it/B8dHjZ #fifa #scandalo#scandalo #FIFA. Alejandro #Burzaco, imprenditore argentino, si è costituito alla Questura di #Bolzano. E' accusato di riciclaggio. #tgr
- select the cells which contain the tweets, and navigate through Add-ons / Text Mining / Analyze text, then select the option “Sentiment Analysis” inside the sidebar. Set the name of the output sheet as “Tweets Sentiment”, and click on the “Analyze text” button to perform the analysis;
Unsurprisingly, the overall sentiment of the conversation on Twitter related to this scandal is really negative.
Final note: for this example we only needed a small amount of data to analyze, but if you need to collect lots of tweets, starting from a hashtag, you can use TAGS, a free Google Sheet template which lets you setup and run automated collections of search results from Twitter.
- “Perform Text Analysis using Dandelion API” – the spreadsheet used in this tutorial;
- Text Mining & Text Analysis add-on for Google Spreadsheets – the official documentation on dandelion.eu;
- Text analysis for Google Sheets at your fingertips, using Text Mining add-on on our corporate blog;
- Google Doc Application for Text Mining – experiments on Text Mining and Google Documents done by Petros Karvelis;
- The Best Add-ons for Google Docs and Sheets, written by Digital Inspiration;
- 8 Small Wins in Every Social Media Analyst’s Day, from Unmetric’s blog;
- 5 Steps To Becoming a Better Social Media Analyst from Hootsuite;
- Supermetrics for Google Drive – add-on that turns Google Drive into a full-blown business reporting system for web analytics, social media and online marketing;
- SEO & Website Analysis – Chrome Web Store – SEO report is an extension for Google Chrome done by WooRank, that provides a very deep SEO report for any given website.