We understand, however, that not everyone has the programming knowledge required to use APIs, and this is why we created our Text Analysis Add-on for Google Sheets – to bring the power of NLP and Text Analysis to anyone who knows how to use a simple spreadsheet.
Today we want to show you how you can build an intelligent sentiment analysis tool with zero coding using our Google Sheets Add-on and a free service called IFTTT.
Here’s what you’ll need to get started;
What is IFTTT?
IFTTT stands for If This, Then That. It is a free service that enables you automate specific tasks by triggering actions on apps when certain criteria is met. For example, “if the weather forecast predicts rain tomorrow, notify me by SMS”.
Step 1 – Connect Google Drive to IFTTT
- Log in to your IFTTT account
- Search for, and select, Google Drive
- Click Connect and enter your Google login information
Step 2 – Create Applets in IFTTT
Applets are the processes you create to trigger actions based on certain criteria. It’s really straightforward. You define the criteria (the ‘If’) and then the trigger (the ‘That’). In our previous weather-SMS example, the ‘if’ is a rain status within a weather app, and the ‘that’ is a text message that gets sent to a specified cell phone number.
To create an applet, go to My Applets and click New Applet.
Here’s what you’ll see. Click the blue +this
You will then be shown a list of available apps. In this case, we want to source specific tweets, so select the Twitter app.
You will then be asked to choose a trigger. Select New tweet from search.
You can now define exactly what tweets you would like to source, based on their content. You can be quite specific with your search using Twitter’s search operators, which we’ve listed below;
Twitter search operators
To search for specific words, hashtags or languages
- Tweets containing all words in any position (“Twitter” and “search”)
- Tweets containing exact phrases (“Twitter search”)
- Tweets containing any of the words (“Twitter” or “search”)
- Tweets excluding specific words (“Twitter” but not “search”)
- Tweets with a specific hashtag (#twitter)
- Tweets in a specific language (written in English)
To search for specific people or accounts
- Tweets from a specific account (Tweeted by “@TwitterComms”)
- Tweets sent as replies to a specific account (in reply to “@TwitterComms”)
- Tweets that mention a specific account (Tweet includes “@TwitterComms”)
To exclude Retweets and/or links
- To exclude Retweets (“-rt”)
- To exclude links/URLs (“-http”) and (“-https”)
Our first trigger
We’re going to search for tweets that mention “bad santa 2 is” or “bad santa 2 was”. Why are we searching for these terms? Well, we find that original, opinionated tweets generally use either one of these phrases. It also helps to cut out tweets that contain no opinion (neutral sentiment) such as the one below;
Going to see Bad Santa 2 tonight. Yup.
— Thom Downie (@ThomDownie) November 23, 2016
Our goal with this tool is to analyze the viewer reaction to “Bad santa 2” which means Tweets such as this one aren’t entirely interesting to us in this case. However, if we wanted to asses the overall buzz on Twitter about Bad Santa 2 perhaps we might just look for any mention at all and concentrate on the volume of tweets.
And so, here’s our first trigger.
Click Create Trigger when you’re happy with your search. You will then see the following;
Notice how the Twitter icon has been added. Now let’s choose our action. Click the blue +that
Next, search for or select Google Drive. You will then be given 4 options – select Add row to spreadsheet. This action will add each matching tweet to an individual row in Google Sheets.
Next, give the spreadsheet a name. We simply went for ‘Bad Santa 2’. Click Create Action. You will then be able to review your applet. Click Finish when you are happy with it.
Done! Tweets that match your search criteria will start appearing in an auto-generated Google Sheet within minutes. Now you can go through this process again to create a second applet. We chose another movie, Allied. (“Allied was” or “Allied is”).
Here is an example of what you can expect to see accumulate in your Google Sheet;
Note: When you install our Google Sheets Add-on we’ll give 1,000 credits to use for free. You then have the option to purchase additional credits should you wish to. For this example, we will stay within the free range and analyze 500 tweets for each movie. You may choose to use more or less, depending on your preference.
Step 3 – Clean your data
Because of the nature of Twitter, you’re probably going to find a lot of crap and spammy tweets in your spreadsheet. To minimize the amount of these tweets that end up in your final data set, there are a few things we recommend you do;
Sort your tweets alphabetically
By sorting your tweets alphabetically, you can quickly scroll down through your spreadsheet and easily spot multiples of the same tweet. It’s a good idea to delete multiple instances of the same tweet as they will not only skew your overall results but multiple instances of the same tweet can often point to bot activity or spamming activity on Twitter. To sort your tweets alphabetically, select the entire column, select Data and Sort sheet by column B, A-Z.
Remove retweets (if you haven’t already done so)
Alphabetically sorting your tweets will also list all retweets together (beginning with RT). You may or may not want to include retweets, but this is entirely up to you. We decided to remove all retweets because there are so many bots out there auto-retweeting and we felt that using this duplicate content isn’t exactly opinion mining.
Search and filter certain words
Think about the movie(s) you are searching for and how their titles may be used in different contexts. For example, we searched for tweets mentioning ‘Allied’, and while we used Twitter’s search operators to exclude words like forces, battle and treaty, we noticed a number of tweets about a company named ‘Allied’. By searching for their company Twitter handle, we could highlight and delete the tweets in which they were mentioned.
NB: Remove movie title from tweets
Before you move on to Step 4 and analyze your tweets, it is important to remove the movie title from each tweet, as it may affect the quality of your results. For example, our tweet-level sentiment analysis feature will read ‘Bad Santa 2…” in a tweet and may assign negative sentiment because of the inclusion of the word bad.
To remove all mentions of your chosen movie title, simply use Edit – Find and Replace in Google Sheets.
Step 4 – Analyze your tweets
Now comes the fun part! It’s time to analyze your tweets using the AYLIEN Text Analysis Add-on. If you have not yet installed the Add-on, you can do see here.
Using our Add-on couldn’t be easier. Simply select the column containing all of your tweets, then click Add-ons – Text Analysis.
To find out whether our tweets have been written in a positive, neutral or negative way, we use Sentiment Analysis.
Note: While Sentiment Analysis is a complex and fascinating field in NLP and Machine Learning research, we won’t get into it in too much detail here. Put simply, it enables you to establish the sentiment polarity (whether a piece of text is positive, negative or neutral) of large volumes of text, with ease.
Next, click the drop-down menu and select Sentiment Analysis – Analyze.
Each tweet will then be analyzed for subjectivity (whether it is written subjectively or objectively) and sentiment polarity (whether it is written in a positive, negative or neutral manner). You will also see a confidence score for both subjectivity and sentiment. This tells you how confident we are that the assigned label (positive, negative, objective, etc) is correct.
By repeating this process for our Allied tweets, we can then compare our results and find out which movie has been best received by Twitter users.
Step 5 – Compare & visualize
In total we analyzed 1,000 tweets, 500 for each movie. Through a simple count of positive, negative and neutral tweets, we received the following results;
Bad Santa 2
Positive – 170
Negative – 132
Neutral – 198
Positive – 215
Negative – 91
Neutral – 194
Now to generate a percentage score for each movie. Let’s start by excluding all neutral tweets. We can then easily figure out what percentage of remaining tweets are positive. So, for Allied, of the remaining 306 tweets, 215 were positive,giving us a positive score of 70%.
By doing the same with Bad Santa 2, we get 56%.
To visualize your results, use your tweet volume data to generate some charts and graphs in Google Sheets;
Comparing our results with Rotten Tomatoes & IMDb
It’s always interesting to compare results of your analysis with those of others. To compare ours, we went to the two major movie review site – Rotten Tomatoes & IMDb, and we were pleasantly surprised with the similarity in our results!
The image below from Rotten Tomatoes shows both critic (left) and audience (right) score for Allied. Seeing as we analyzed tweets from a Twitter audience, we are therefore more interested in the latter. Our score of 70% comes so close to that of almost 15,000 reviewers on Rotten Tomatoes – just 1% off!
IMDb provide an audience-based review score of 7.2/10. Again, very close to our own result.
Our result for Bad Santa 2, while not as close as that of Allied, was still pretty close to Rotten Tomatoes with 56%.
With IMDb, however, we once again come within 1% with a score of 5.7/10.
We hope that this simple and fun use-case using our Google Sheets Add-on will give you an idea of just how useful, flexible and simple Text Analysis can be, without the need for any complicated code.
While we decided to focus on movie reviews in this example, there are countless other uses for you to try. Here’s a few ideas;
- Track mentions of brands or products
- Track event hashtags
- Track opinions towards election candidates
Ready to get started? Click here to install our Text Analysis Add-on for Google Sheets.