[Tutorial] Find the Sentiment of Any Twitter Search with Only A Spreadsheet
May 14, 2015 / Business, Tutorials
Understanding how your customers or users feel about your brand or products is important, especially for creating something that people love. We’ll be showing you how to find the sentiment of the results of any search on Twitter in 10 minutes with only a spreadsheet and a little creative thinking.
The power of Twitter comes from the ability to voice your opinions to the world. Understanding those voices–whether they’re happy, upset, or even angry–is paramount to the success of your brand or product. Sentiment analysis is the measure of how positive or negative a statement is. High quality social sentiment analysis tools are far and few between. Very few tools provide insight into the quality of performance, and the majority of them restrict search options which limit your understanding.
Note: this tutorial is meant for a non-technical audience. We’ve collaborated with Blockspring (Y Combinator ’14) to bring our APIs to non-developers; learn more about Blockspring below.
What You’ll Be Creating
Benefits of this Tool
1) Understand Your Brand Perception
Using the @twitterhandle search operator helps you get the sentiment of each person tweeting at your brand’s Twitter. Use this spreadsheet frequently so you can keep your eye out for potential unhappy customers/users. It’s also really useful for pinpointing awesome tweets that can be used in customer testimonials.
2) Keep Tabs on Competitors
This spreadsheet is useful for understanding the reception towards your competitors too. You could even swoop in and offer a better option to a competitor’s unhappy customer.
3) Nimble Campaign Insights
If you’re launching a new product, feature, or campaign this spreadsheet will be very helpful for keeping tabs on reception throughout the launch efforts.
Tools – What You’ll Need for this Tutorial
- Our Sentiment Analysis model has been trained on 1 million data points from a variety of sources. The model performs with 90% accuracy on Stanford’s IMDB movie review corpus.
- Blockspring (Y Combinator ’14) is a platform that allows you to run over 1,000 functions within Google Sheets without writing a single line of code. You can create interactive data visualizations, run algorithms, pull data sources, and more.
- Google Sheets Google Sheets is available for free to anyone with a Gmail account. If you don’t have Google Sheets, you’ll need to sign up for Gmail to get access to Google Sheets.
The Initial Setup
1) Create an account at Blockspring.com (30 Secs)
2) Installing Blockspring (30 Secs)
Download Blockspring and install it in Google Sheets. Click Continue when prompted for permission, then click Accept to grant Blockspring permission to edit your spreadsheet.
Make a new spreadsheet and sign into Blockspring within Google Sheets. In the menu bar, go to Add-ons, hover over Blockspring, and click Sign in to Blockspring. You’ll see the Blockspring Console on the right of your spreadsheet, like below.
4) Create an indico account (30 Secs)
Register for a free indico account (30 seconds flat) to get your API key or login to your Dashboard to copy your API key.
5) Using Your indico API Key in Blockspring (1 Min)
Make sure you’re signed in on the Blockspring website and go to our indico Sentiment Analysis Blockspring function, and paste your API key by clicking Set Value. Afterwards, feel free to run the test example to make sure everything is working.
6) Copy the Twitter Sentiment Analysis Spreadsheet into Your Drive (10 Secs)
Now here’s the fun part! Open the Twitter Sentiment Spreadsheet, go to File and click Make a copy… to duplicate the spreadsheet into your own Drive.
7) Refresh the Function Cells (potentially optional) (10 Secs)
Once you’ve made a copy, you might see something like below. Don’t worry! You just need to log into Blockspring, click Add-ons in the menu bar of Google Sheets, hover over Blockspring, and click Sign in to Blockspring.
Next you’ll need to refresh the function cell by first clicking cell A8, going to the Blockspring menu options and clicking Refresh selected cell. If you are already logged in, all you have to do is follow the cell refresh directions.
8) Making Your First Search (2 Min)
After you’ve refreshed the Twitter Search function, your spreadsheet will begin to gather the tweets for the search term in Cell A2.
Recently the TOMS shoe brand launched a campaign called One Day Without Shoes to raise awareness for impoverished children around the world. Their hashtag #WithoutShoes is a great example and relevant (at the time of publishing), so let’s see what Twitter thinks. We’ll type #WithoutShoes into Cell A2.
The results will be auto-categorized based on the level of positivity (Very Negative, Negative, Neutral, Positive, and Very Positive). Your spreadsheet should look like the screenshot below once the search is complete.
9) Visualizing Our Search Results – #WithoutShoes Example (1 Min)
Now to visualize the sentiment of our Twitter search, click on Cell K15 (sorry about the screenshot showing K19), then click Viewer within your Blockspring console. It’ll show you a preview of the interactive pie chart. Right-click pie.html and open it in a new tab or window to see the entire chart.
Looks like the TOMS campaign is being well received on Twitter! 39% of the returned Tweets were measured as Very Positive.
Important!: Twitter Search Guide
The Twitter search function provided by Blockspring uses the Twitter Search API to allow you to make these searches. The API has a few restrictions that you have to follow to receive the right results from your search.
1) 6 to 9 Day Index Limit
Twitter’s Search API provides only a partial index of tweets, limited to approximately 6 to 9 days from the date that you execute the search.
2) Results Limited to 100 Tweets
Twitter’s Search API only allows a maximum return of 100 tweets for each search.
3) 450 Requests Every 15 Minutes
The rate limit for Twitter’s Search API is 450 requests per 15 minute increment. One request is equivalent to making one search in the spreadsheet regardless if it returns 100 tweets or just 10 tweets.
4) Operators Hone Your Search
Operators allow you to search for different types of tweets whether you’re looking for mentions of your brand, hashtags, or tweets directed at your competitors. Here’s the full list with examples: