How to Build a Social Listening and Sentiment Analysis Tool in Google Sheets
Note: the Google Sheets Add-on is now deprecated, but you can replicate most of its functionality with some code you can copy and paste from here.
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.
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.
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.
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)).
Following this you should have a spreadsheet that looks like this:
(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.
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.
Below is an example of what a basic pivot table should look like
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.
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.
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.
Geo locations: Here we have displayed mentions of locations which were extracted automatically from tweets.
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.