Keyword Analysis Using Google Sheets

Keyword Analysis Using Google Sheets

A good decision is based on knowledge and not on numbers. That’s what Plato said many centuries ago and it still holds true today. We sometimes focus too much on our sales figures, NPS score, or average response time to determine trends and seize opportunities... but are we getting the entire picture?

We know that survey responses, product reviews, social media comments, and news articles can shed light on potential unexplored niches, tell us what customers want, where there’s room for improvement, and what the competition is currently doing. Without these insights, which can be found in data, we are at sixes and sevens, feeling our way to the right strategy. That’s why identifying keywords — important words or expressions — can shed some light and help build a data-driven strategy.

The problem is that text can be quite challenging to analyze if you try to do it manually. Your team has probably spent too many hours on this. Could you get actionable insights from text data without investing so much time? Keyword analysis models can do the trick so that you can go the whole nine yards with data analysis. But how?

Well, a keyword analysis model can detect the most relevant keywords from a piece of text with no manual work. Plus, if you have all your data on a Google Sheet, this model can analyze and provide the results in that same spreadsheet so you can answer questions such as:

  • What words or expressions are most associated with my business?
  • Which keywords are used to describe the competition?
  • How are our customers expressing themselves on social media when they talk about our industry?

In this post, you’ll find a step-by-step guide for getting the answers to these questions and other useful info for getting started with keyword analysis in Google Sheets. We’ll be covering:

Let’s begin!

What is Keyword Analysis?

Keyword analysis, also called keyword extraction or keyword detection, is a text analysis technique based on machine learning that can understand and extract the most important words and expressions — keywords — from text. These keywords are formed by one or more words and they make up the most relevant topics covered in a piece of content.

Let’s look at an example:

Elon Musk has shared a photo of the spacesuit designed by SpaceX. This is the second image shared of the new design and the first to feature the spacesuit’s full-body look.

If we run a keyword analysis, we may find that Elon Musk, Photo, Spacesuit, SpaceX, Second Image, New Design, and Body Look are the most relevant keywords.

How can Keyword Analysis be Useful?

Content is ever-growing and all-important since users generate about 70 million posts and 77 million new comments every month on Wordpress alone. Now, imagine adding social media, survey responses, product reviews, news articles, and the like to the equation. How can we possibly gain insight from all that information that is almost within reach?

Keyword extraction can be useful for knowing what huge amounts of text talk about, without even reading their content. For example, you can analyze 1 million hotel reviews in seconds and identify that Bangkok has a cockroach problem and that croissants are a massive disappointment in hotels in Paris.

Keyword analysis can put the knowledge in your hands because:

  • It provides scalability: Read an article, identify relevant keywords, add them to Google Sheets. Can you imagine repeating this process from 9 to 5? It’s not only a draining task but also time-consuming. With automated keyword extraction, time can be spent on other tasks while the analysis is done automatically in the background.

  • It ensures consistent criteria: We humans hardly ever agree on anything, let alone the most relevant words or expressions in a sea of content. Can you ensure that the criteria will be met? Keyword analysis is consistent because it doesn’t doubt, it doesn’t get tired, and it follows your rules consistently.

  • It can analyze in real-time: If you’re hoping to see how your latest product feature is faring, you can analyze mentions by your target audience in surveys or social media – and find out how they perceive your product within seconds.

How to do Keyword Extraction with Google Sheets?

It’s time to get those keywords from text, living in your Google Sheets! It may sound like a burdensome task, but with the right tool, it’s pretty easy and straightforward. By using a platform such as MonkeyLearn, you can use keyword extraction to analyze data in Google Sheets right away. You don’t even need to know about machine learning or know how to code, just follow these simple steps:

1. Gather the Data in Google Sheets

First of all, you’ll need to get the data you want to analyze with keyword extraction into a Google Sheet. Don’t worry; it doesn’t require any copypasting on your part. There are many data gathering tools you can make use of. Let’s go through some of them:

A. Web scraping tools

Web scraping tools, used to extract data from websites automatically, are plentiful and you can use them whether you’re a developer or not. It’s true, however, that not all of the scraping tools are convenient for non-coders. Some visual and user-friendly web scraping tools are Dexi.io, Portia, Import.io, and ParseHub, while for developers, Scrapy is the go-to framework for web scraping.

If you want to get started with one of these tools, Dexi.io provides a straightforward tutorial to build your first scraping robot.

B. APIs

Would you like to gather mentions and comments on social media? Twitter, Facebook, and other social media networks provide APIs to push data to your Google Sheet, though this is a good option only for those who know how to code.

C. Zapier

Zapier is a useful tool for passing data from one app to another so if, for example, you want to gather survey data from SurveyMonkey and save it on a Google Sheet, those two apps can be linked with Zapier. But how?

Basically, after creating an account on Zapier — keep in mind that the multi-step free trial is available for 14 days — you can start creating your zap.

As a first step, choose your trigger app (in this case, SurveyMonkey) and the trigger action, that is, the receipt of a new open-ended response.

Now, what happens when you get a new response? It needs to be automatically sent to your Google Sheet. That’s why you now need to choose your action app — Google Sheets — and the spreadsheet you want to receive the responses in.

Once you’re done setting the zap, you’ll receive the data generated on SurveyMonkey automatically on Google Sheets! To get a more detailed step-by-step guide, check out this Zapier tutorial.

D. Google Sheets Add-ons

Add-ons on Google Sheets make things simple. Just a couple of clicks and you can give your spreadsheet superpowers!

Just click on ‘Add-ons’ on your Google Sheet toolbar:

Then select ‘Get Add-ons’:

And use the search tool to look for the tool that you need. Some useful tools include an add-on for saving emails on a Google Sheet, another one for getting your Salesforce data on your Google Sheet and one for getting tweets based on a keyword or hashtag you searched for.

E. Formulas

Google Sheets has many formulas available for different purposes, and one of them is IMPORTXML, which offers the possibility of building a web scraper and getting the data on your spreadsheet. Here’s a good tutorial for building a scraper with a Google Sheets function.

2. Run the Keyword Extraction in Google Sheets

Ok, now you have your data on the spreadsheet. What comes next? It’s time to see how you can run a keyword analysis in Google Sheets!

We’ll be using a pre-trained model for extracting keywords from text automatically. The great thing about pre-trained models is that once you have your data, you can start getting the most relevant keywords from the content on your spreadsheets right away.

Let’s take a look!

1. Sign up for free to MonkeyLearn

MonkeyLearn is an AI platform that allows you to analyze text with Machine Learning. It provides pre-trained machine learning models for specific text analysis tasks, including keyword extractors. Sign up to MonkeyLearn for free to have access to all the pre-trained models available, not just the keyword extractor.

2. Get the MonkeyLearn add-on for Google Sheets

There’s a MonkeyLearn add-on available on Google Sheets that makes it super easy to analyze text directly in your spreadsheets. So, go to your Google Sheets toolbar and click on Add-ons > Get Add-ons. A window with a search bar will open up. Just type ‘MonkeyLearn’:

Now, you’ll have to select your Google account and give MonkeyLearn access to your spreadsheet.

3. Set up your API Key

Click on ‘Add-ons’ in the spreadsheet toolbar:

A window will open up on the right side of your screen:

Click on ‘MonkeyLearn API Key’. A new window with your API Key will open up. Copy-paste it and click on ‘Set’.

4. Run the keyword analysis!

Select the rows you want to analyze, then, select the pre-trained keyword extractor from the dropdown menu on the right and click on ‘Run’. In just a few seconds, you’ll get the predictions in another column. That’s it!

How to Build Your Custom Keyword Extractor?

You may get the insights you’re looking for using the pre-trained keyword extractor, but what if you want a more fine-grained extraction?

Let’s say you want to run a keyword analysis for different departments… would they all consider the same words as important? Imagine you get this product review:

I loved the ease of use of this app. I got the hang of it right away, so kudos to the front-end developers! As far as the price goes, it’s quite cost-efficient, so I wouldn’t complain. I’d just recommend improving some visuals because I had some trouble finding some of the buttons.

What would the keywords be? Maybe a sales department would find ‘Price’ and ‘Cost-efficient’ as noteworthy words, but would the dev team agree? They’d probably highlight ‘Ease of Use’ and ‘front-end developers’. Even the design team would have a different viewpoint, focusing on ‘Visuals’ and ‘Buttons’.

Keywords are subjective and what is considered to be a keyword depends on each team’s point of view and objectives. So, how can we make sure the right analysis is done for each team? It’s in these cases that a custom keyword extractor may come in handy and it’s not difficult to build. Just check out this quick tutorial on building a custom extractor on MonkeyLearn.

Once you have your custom extractor, you can follow the same steps to analyze your data directly on a Google Sheet with the MonkeyLearn add-on.

Use Cases and Applications

You’ve just finished setting up your keyword analysis in Google Sheets. Great! But in what ways can you reap the benefits of keyword analysis?

Besides automating the keyword selection task, this tool lends a hand in different processes across various teams. Let’s go through some of them!

Get Insights into Customer Feedback

Customer feedback is piling up in your inbox. What do your customers say? Reading feedback can be a cumbersome task. Fortunately, it can be scratched off the to-do list with keyword analysis. But besides optimizing team members’ time, you can get various insights, for example, which words are most associated with your brand or product.

Say that you have a customer survey in place to keep track of customer satisfaction and that you run a keyword analysis on the latest batch of responses. You find out that one of the keywords you get from your analysis is ‘Difficult’. You’d probably start digging around to find out what customers find difficult about your product by looking at the other keywords extracted from those responses. This can help you detect actionable insights on how you can make your product easier to use for your customers.

What happens if you receive a high number of survey responses with the name of one of your features? Are they praising it or criticizing it? By complementing keyword extraction with sentiment analysis — a text analysis model that tags opinions as Positive, Neutral or Negative— you may find the answers you’re looking for.

Monitoring trends is a big part of marketing your product or service. You search for trending keywords with Google Trends and other tools, but is that enough? Some trends may pass you by if you don’t have the right tool to figure them out.

Are your customers engaging with a new app? Could this be an opportunity to jump the gun and provide a new service? What are customers looking for? What are they buying?

Answering these questions can be tiresome without a keyword extractor to search for the answers. By running a keyword analysis on feedback from your target audience across different social media platforms and review sites, you may spot some hidden gems, like specific niche keywords, and start taking the right steps to be at the forefront of your market.

Understand Your Brand Perception

How is your brand mentioned on social media or news outlets? What are the words or expressions people use when they talk about your product?

These questions can make a huge difference in decision-making processes in marketing campaigns, to decide where to allocate your budget.

To get an idea of brand perception, you could run a keyword analysis on Tweets that mention your brand to see how people are talking about you on Twitter.

After, you may want to know how many of those keywords are used in a positive way about your brand. No problem! Just run a sentiment analysis on all the mentions and see where it takes you. Now, you can go even further, and see which other keywords appear in negative mentions. For example, you could learn that positive mentions are often related to the keyword ‘Customer Service’ and negative ones to ‘Ease of Use’.

Keyword Research for SEO

Your team is pitching ideas to try to boost your website visibility on Google and get more traffic to your website. What’s the best strategy?

Keyword research is no easy feat, and running research with tools like Moz Keyword Explorer, SEMrush, Ahrefs, or Google keyword planner is fundamental for SEO.

The thing is, if you have a specific niche you want to dig into, these tools may not provide you with the keyword ideas you’re looking for. What can you do, then?

Well, you could gather content from your competitors on a Google Sheet and run a keyword analysis to see what keywords they are using on their website and their blog. With the keywords obtained through this automated analysis, you can create the right SEO strategy and use the right keywords to increase your traffic.

Wrap-up

Knowing what customers want has been getting harder and harder, not because they don’t speak up, quite the contrary; nowadays, every customer shares their views online. The problem is understanding all those opinions and detecting valuable insights. Manually reading each piece of content is time-consuming and inconsistent, however. It can even be counterproductive, so you’ll need to make way for keyword analysis if you want to gain useful insights.

With keyword extraction, you can learn what customers are saying and make informed decisions based on what your customers need. It’s fast and simple, and results from automatic keyword extraction are more reliable than if you were to manually pull out keywords.

If you want to learn more about keyword extraction in Google Sheets, request a demo. Our team will guide you on how to take the first steps with keyword analysis in your spreadsheets.

Federico Pascual

June 10th, 2019

Posts you might like...

MonkeyLearn Logo

Text Analysis with Machine Learning

Turn tweets, emails, documents, webpages and more into actionable data. Automate business processes and save hours of manual data processing.

Try MonkeyLearn
Clearbit LogoSegment LogoPubnub LogoProtagonist Logo