Product

How to Build a Social Listening and Sentiment Analysis Tool in Google Sheets

Social Listening

It simply isn’t an option these days for businesses to ignore the voice of their customers on social channels. There is a huge amount of business insight hidden in text on social channels, but it can be difficult to block out the noise and gain business insight from social data. Buying signals, support queries, complaints etc can all be gleaned from social chatter and activity by properly analyzing the voice of customers and users online. For more on this check out our blog on “Why sentiment analysis is important from a business perspective.”

Analyzing social data and listening to the voice of your customers can be hard and often involves costly software solutions and/or certain technical expertise to gather data, analyze it and visualize the results.

That’s pretty much why we built our Text Analysis add-on. We built it with the everyday analyst or marketer in mind. We wanted to provide a quick and easy way for our users to analyze text, without the hassle, cost and complications of traditional Text Analysis tools.

Our Text Analysis add-on is built on a package of machine learning and natural language processing API’s that allow you to perform sophisticated text analysis without any programming or technical expertise.

In this how-to guide we are going to demonstrate just how easy it is to collect tweets, analyze them and report on your findings from within Google Sheets.

If you haven’t used the add-on before you can download it here and if it’s your first time then check out our getting started tutorial to get up and running.

To build a social listening tool, you will need the following:

  • An inquisitive mind
  • Google Spreadsheets
  • AYLIEN Text Analysis add-on
  • Some way of gathering your tweets (Copy and paste, RSS feeds, Twitter Curator)

For the purpose of this blog, we are going to gather a sample of 100 tweets that mention Ryanair, analyze them, look for insights and graph our results. We will aim to automatically determine what language the tweets are written in, extract any mentions of locations and determine what the sentiment is towards Ryanair from this sample set of tweets.

Step 1 – Data Collection

Collect and gather your tweets in a blank spreadsheet. You can copy and paste the tweets from another source, use Twitter curator to collect your tweets with the click of button or if you have the technical expertise write a script to automatically mine Twitter. (Keep an eye out for our webinar and blog on how to build a basic twitter mining tool)

Step 2 – Analysis

Once you have your tweets laid out as desired in a Spreadsheet, start your Text Analysis Add-on. For a guide on how to get up and running with the add-on visit our tutorial page.

First things first, determine what language your tweet is written in by using the language detection function. (=language(X)). Keep in mind you can drag the formula down through the rest of the column to analyze all the tweets automatically, which saves a lot of time and effort.

 

Screen shot 2014-10-10 at 5.18.27 PM.png

Screen shot 2014-10-10 at 3.19.48 PM.png

 

Then, extract any mentions of locations by using the locations extraction function (=locations(X)) and do the same as above to drag the formula throughout the rest of the column.

 

Screen shot 2014-10-10 at 5.19.04 PM.png

 

Lastly, use the sentiment analysis feature to find out if the tweets are negative, positive or neutral. This can be done using the Sentiment Polarity Feature (=sentimentpolarity(x)).

 

Screen shot 2014-10-10 at 5.21.33 PM.png

 

Following this you should have a spreadsheet that looks like this:

 

Screen shot 2014-10-10 at 3.32.00 PM.png

 

(Keep in mind the colour coding on the sentiment column is down to the formatting of the column and isn’t generated automatically.)

So far we have collected and analyzed our tweets, now all that is left to do is build some pretty graphs to visualize the data.

Step 3 – Reporting

The advantage of having this data in a Spreadsheet means it is extremely flexible. It can be shared, copied, combined with other data and reported on very easily.

We are going to create some basic reports based off the data we have gathered throughout the process. This will be done entirely within Google Spreadsheets by utilising the pivot table report. Pivot tables are a very handy way of preparing your data to be visualized in graphs, you can read more about them here.

To get started with your report, select the range of data you want to report on. Choose data in the main toolbar and click on Pivot Table Report.

 

Screen shot 2014-10-10 at 3.34.53 PM.png

 

As an example we are going to create a simple bar chart showing the different languages of the tweets in our dataset.

Once you have clicked on Pivot Table Report in the drop down menu, a separate sheet called “pivot table 1” will open. In the sidebar of the sheet the is a reporting widget. Here is where you choose how your report is laid out.

In this particular report, we want to get a breakdown of the different languages used in the sample set of tweets and figure out what language is used the most.

Sort your “rows” by language and under “values” we also choose language. The report widget will be defaulted to summarize by SUM which will leave our table full of zero’s. This needs to be changed to “COUNTA” in order to display the count data.

 

Screen shot 2014-10-10 at 3.36.10 PM.png

 

Below is an example of what a basic pivot table should look like

Screen shot 2014-10-10 at 3.35.57 PM.png

 

In order to graph the results, choose the data you want to include by highlighting the appropriate cells in the table. Click on “insert” in your toolbar and choose “chart”.

You should be left with a simple bar chart like the “Tweets by Language” one below. You can get a bit more creative with how you customize your charts by adding colours and formatting.

You can choose from a wide range of bar charts, geo charts, pie charts etc… all of which are displayed in our completed graphs below.

Findings

Tweets by language: Using the language detection feature we could easily recognise that the majority of tweets out of our 100 sampled were in English.

image

Sentiment of tweets: Here we have provided a chart that displays the percentage of tweets that were positive, negative and neutral. On close inspection we noticed the majority of the neutral tweets were general enquiries or news reports.

image

Geo locations: Here we have displayed mentions of locations which were extracted automatically from tweets.

 

image

 

Sentiment of tweets by location: From studying this graph it is pretty clear to see that, of the small sample of tweets we analyzed, there was a lot of negativity in tweets that also mentioned Corfu. On further investigation it became clear that there was in fact a delayed flight which left passengers stranded in Corfu at the time the sample tweets were collected. image

You can download the add-on here and for more information on our Text Analysis add-on videos, cheat sheets and tutorials visit or tutorial page.





Text Analysis API - Sign up




Author


Avatar

Mike Waldron

Head of Marketing & Sales @ AYLIEN A legal convert with a masters degree from Smurfit Business School, Mike runs our Sales and Marketing at AYLIEN. Mike gathered his Sales and Marketing experience with technology companies in Sydney and Dublin before getting the startup itch and joining the team at AYLIEN. Twitter: @MikeWallly