Sentiment Analysis For SEO Using Google Sheets

If you’ve had the dubious pleasure of working with me over the last ten or so years, you’ll have heard me talk about three things pretty regularly: how chili is the greatest food on the planet (if you disagree, you’re wrong), obscure black metal/ witch house/ synthwave/ industrial bands and sentiment analysis and how it relates to SEO.

I was recently lucky enough to be invited to speak at the Colchester Digital Meetup and I decided to do an introductory session on sentiment analysis, how it can be used for SEO, PR, link analysis and content creation and how you can easily perform this analysis for yourself in Google Sheets.

In today’s post, I’m going to share the slides from that talk and also go into a little more detail around how to use the sheet I’ve built, what you can do with it and how you can use this analysis to take your SEO game to the next level.

If you like what you see, please sign up for my FREE email list, where you’ll be sent notifications of my latest posts.

What Is Sentiment Analysis?

Sentiment analysis has been a part of my keyword research and general SEO approach since around 2012, where I was using it to ensure that the terms we were targeting for clients were going to put them in a positive light rather than pushing them into toxic territory.

Essentially, sentiment analysis is part of Natural Language Processing and is the attempt for a computer program to understand the feeling behind a piece of content rather than just the words themselves. As search engines and other platforms are moving into “Things not strings” territory, and updates such as BERT and EAT are becoming prevalent, it’s really important that we as SEO’s are working on how people are speaking about us rather than just that people are speaking about us.

Google have said that sentiment is not a “direct” ranking factor, but with Authority and Trust being components of EAT, I would be very surprised if it isn’t at least a component of how that trust and authority is determined. In fact, I would be shocked if it isn’t used in other areas as well, with it likely becoming a larger factor in the next few years.

The Cloud Natural Language API And Google Sheets

One of the main barriers to performing this kind of analysis that I’ve seen is that people think you need expensive tools, programming knowledge or a team of people manually reading everything. Fortunately, that’s not the case.

Sure, if you want to scale this analysis, you’re going to need to either buy or build something (it’s surprisingly easy to build something simple in python – maybe I’ll share that later on), but since we’re just introducing the methodology today, we’re going to do it all for free in Google Sheets and using Google’s Cloud Natural Language API. The best part? You don’t need any programming knowledge whatsoever to perform your sentiment analysis using this Google Sheet.

This Google Sheet has the following functions, which should be the perfect way to get you started:

• Analyse the sentiment of URLs
• Analyse the sentiment of TrustPilot reviews
• Perform sentiment analysis and keyword research with the SEMRush API

All the sentiment analysis work is performed by pulling Google’s Cloud Natural Language API into the sheet after scraping the text from the target URL.

This sheet is partially built off of the sentiment analysis script written by Kevin Deenanauth on Stack Overflow, but all the scraping and other API functions are mine. Thanks Kevin, you saved me a lot of yelling!

As I said earlier, you don’t need to do any programming, but there is a little setup to do.

Firstly, you need to make a copy of the Google Sheet into your own Drive account. Once you’ve done that, I’ll talk you through the rest.

Getting A Google Cloud Natural Language API Key

Firstly, you need to sign up for Google Cloud Services. They’ll give you $300 credit up front, which is nice of them. This service is insanely cheap since it’s built to scale and is completely free for up to 5,000 queries a month.

Once you’ve signed up, enter the console and click the big “New Project” button.

Now go to API’s and Services in the sidebar and then locate the Cloud Natural Language API.

Click “Enable”.

Next, you need to navigate to API’s and Services and click “Create Credentials”. Again, it’s a nice big button.

It’ll give you a dropdown. Select “API Key”. It’ll generate one for you. Keep it safe.

Linking The Cloud Natural Language API To Google Sheets

Finally, to link that Cloud Natural Language API key with Google Sheets, you’ll need to go back to my sheet, click on Tools and go to the Script Editor.

From here, select Project Properties, and go to Script Properties.

Paste your API key into the Value box, over “REPLACEME”. Now your API key is linked with the sheet and we’re ready to go.

How To Use The Google Sheet For Sentiment Analysis

I’m not going to go too in-depth on how this sheet works or what it does – you can read my slides below for that, and I like to think that the sheet is fairly self-explanatory.

Essentially, the first tab will allow you to analyse a series of URLs. If you paste your chosen URLs into the “URL” column, the sheet will scrape all the text from the page into one cell and then the Natural Language API will analyse that text and return a sentiment magnitude (the degree of salient words which have been analysed) a score and a grade. Anything above zero is considered positive, anything below zero is negative and zero is neutral.

This is really useful for analysing your backlinks, your competitors backlinks and unlinked mentions. This tab will basically perform sentiment analysis on any free text from a URL, so the possibilities for SEO, PR, reputation management and so on really are endless.

The one thing I would say is that you might want to run this in small batches as Google Sheets isn’t always the quickest.

Sentiment Analysis On TrustPilot Reviews

I have a love/ hate relationship with TrustPilot, but something which I have found a good use for sentiment analysis is on its reviews. The second tab of the sheet will scrape the average review score and the review text on any TrustPilot URL, which can be handy for running bulk analysis on your clients, your competitors or seeing if there’s a gap in a market that can be exploited.

Again, just paste the URL into the “URL” column on the TrustPilot tab of the sheet and it will do the rest.

There’s a lot of data on TrustPilot that can be harvested and analysed, so this is well worth looking into. Again, the possibilities are endless.

Keyword Research And Sentiment Analysis With SEMRush & Google Sheets

Going back to the original reason I started using sentiment analysis in the first place – keyword research, here’s a really easy way that you can bring SEMRush data into your Google Sheet and run the Natural Language API on the top ten results for your target keyword, returning an average score.

You’ll need an SEMRush API key to run this, but it’s well worth having one. The recent misstep on the “Guest Posting” drama aside, SEMRush is a great tool with a lot of useful data. If there’s enough interest, perhaps I’ll update this to use some other tools as well, such as AHREFs.

Navigate to the Keyword Research tab and paste your SEMRush API key into cell N3 (where it says “API KEY GOES HERE”).

Now just put your keywords into the Keyword column on the far left and let the magic happen.

Note: I’ve only built this to do five keywords at a time due to how long it can take to run, but hopefully it’ll give you a taste of how this can be used for your business or clients.

And We’re Done

So that’s how you can use sentiment analysis for SEO, PR, reputation management, link analysis or just about anything else using Google Sheets and the Cloud Natural Language API. I haven’t really covered what this can be used for in today’s post, but you can see more around it in my slides below and once the talk is uploaded to YouTube, I’ll update with the video.

I’m hugely passionate about this topic and language analysis in general, purely because I believe that as marketers, language is our strongest asset. Maybe it’s the copywriter in me, but all the technical optimisation in the world won’t help if people don’t believe in you, trust you or understand you.

That’s where language analysis gives you the edge. At least, that’s how I feel.

Thanks for reading, as always. Hopefully I’ll have something new to share soon.

Here are my slides from Colchester Digital. It’s a great event with some excellent speakers, so if you’re in the area or at a loose end one evening, be sure to check it out.

Until next time.