How to Analyze your Competitors Content With This Simple Google Sheets Hack
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.
Content marketing is a major part of any digital marketing strategy. Most of us, are making a concerted effort to create high-quality content on a regular basis, that our target market value. Content marketing isn’t just about content creation, it’s also about staying on top of trends, analyzing what works and what doesn’t and watching your competition closely to understand what they write about, whats working for them and so on. Competitors blogs can be an excellent source of inspiration on what to do or even, what not to do.
This blog will take you through how we keep a close eye, on some of our major competitors.
We’ve hacked together a nice little workflow in a Google Spreadsheet that monitors and analyzes what type of content our competitors are putting out there. First off, we’re going to show you how we automatically extract blog URLs from a competitors site and populate them in a spreadsheet, then we’re going to show you what we do with them to extract some insight.
For this hack, you’re going to need the following:
- 10 minutes
- Google Spreadsheets
- AYLIEN Text Analysis Add-on (Free to download)
- Competitors to spy on
Create a Google Spreadsheet with two sheets. Sheet 1, we’re going to use to extract our URLs and Sheet 2, we’re going to use to analyze the URLs.
So what exactly do we mean by analyze? We’re going to do the following:
- Summarize their blog posts
- Extract Concepts present in them
- Generate Keywords from their content
- Identify themes for each post
Add your competitors blog URL to the spreadsheet. You have two options on how you actually pull your data into your spreadsheet.
Option 1. You can choose to scrape the page with the following XML Function. (best for competitors with a central hub of blog content)
=IMPORTXML(cell containing content hub URL, "//a/@href")
Option 2. You can use their RSS feed from their homepage.
=IMPORTFEED(SPLIT(Feeds(cell containing homepage URL), ", "))
For this blog, we’re going to use the XML method as one of our main competitors, has all of their blog content nicely listed on the one page. This method will pull down every link on the page you specify and list them in a spreadsheet as shown below.
Some of the links will be interesting to you and some won’t. Just decide what you want to keep and discard the rest. In our case, we kept anything that was on the blog.competitor subdomain as the rest of the links are just standard web pages.
Copy all of the desired links or blog URLs into your second spreadsheet and set up the sheet as shown below.
Lay out the sheet however you want. We just labeled a few columns for each particular analysis we planned on doing.
Now the fun starts… Open your Text Analysis add-on (download it here if you haven’t already). You’ll see it appear as a toolbar, on the right-hand side of your spreadsheet. If you need help using the add-on, check out our tutorial page.
The first thing we’re gonna do is summarize all of the blog posts. To do this, you need to highlight all the cells you want to analyze. Choose the summarization feature in your TA add-on, choose the number of sentences you want and hit go.
If you need help getting up and running with the add-on check out our tutorial page here.
The summaries are a nice way of transforming the blogs into consumable chunks. You get to decide how long you want the summaries to be.
In the image above you can see the first blog post summarized into 3 sentences.
Note: When you summarize all your links your sheet might look a little messy, clean it up by wrapping the text in the summary column.
Extracting Concepts is just as easy as summarizing the blog posts. Again, highlight the cells you want to analyze, choose concepts in the analysis type in your sidebar and hit analyze. You can decide whether you want the results generated in new columns or separated by a comma. It’s totally up to you.
If you want to do some more advanced analysis on your results, like creating graphs, for example, it might be easier to separate the results into new columns.
Extracting Keywords is a little different. To grab a list of keywords from the blog, we’re going to use an AYLIEN spreadsheet formula (you can see all formulas here in our cheat sheet). The formula we’re gonna use is really simple.
=keywords(cell containing URL)
Type the formula into a desired cell and hit enter to extract the keywords from each blog post.
You’ll be left with a sheet that looks like the one below. It lists the URL for quick access to the blog post. It has a brief summary of each post to make it easier to read. Concepts help to track what they’re writing about and the Keywords give you an understanding of what long and short tail keywords they could be ranking for.
Because the data is in a spreadsheet it’s really easy to slice and dice it however you want. You can also get a high-level view of themes and topics throughout a blog by running concept extraction on the keywords you extracted. This gives a clean quick-look reference of the general topics your competitor covers on their blog.
At AYLIEN, we’ve found this hack really useful for keeping a close eye on competitors. We use it to monitor competitor Keyword strategies, Carrying out our own Keyword brainstorming and competitor intelligence as a whole.