How to Analyze Your Social Media Activities with Google Drive

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

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

8 Small Wins in Every Social Media Analyst’s Day

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:

  1. 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;
  2. 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 you need first: the “Text Mining” add-on installed, just follow the Getting Started guide.

Let’s start with this news article taken from CNN:

FIFA Corruption Scandal for CNN

Swiss: FIFA official is extradited to the U.S.

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:

  1. copy and paste the URL you want to analize inside a cell of your spreadsheet;
    URLs as input for the text analysis inside Google Sheet
  2. 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;
    New features available on Dandelion API add-on for Google Sheets
  3. 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.

    Entity extraction and Text Mining inside Google Docs

  4. 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, and click the “Visualize” button;

  5. 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)
    Tag Cloud built on entities extracted using Dandelion API
    Inline SEO analysis: the keywords cloud

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:

URLs as input for Text Mining inside your Google Sheets

This is the result:

Tag Cloud built with Dandelion API on three news articles

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:

  1. 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;
    Advanced Search on Twitter
  2. copy and paste these tweets inside your spreadsheet, if you don’t have time to follow step 1:
  3. 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;

    Sentiment Analysis on Tweets written in Italian and in English using Dandelion API

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.

Useful References

One comment on How to Analyze Your Social Media Activities with Google Drive

Leave a Reply