This template helps you to check & analyse your sites metadata so you can improve your page titles and meta descriptions.
SEO to an outsider can sound super interesting.
The truth is: a lot of SEO is following best practices
on loop consistently.
Sometimes this can get tedious.
And something that I’ve found super tedious is re-optimisation. I’m talking about finding and then writing and rewriting page titles and meta descriptions.
But like I said, good SEO is doing things consistently so the tedium is worth it.
Thankfully this Google Sheet template allows you to quickly find and optimize your site’s meta titles & descriptions.
What are meta tags in SEO?
Meta tags are snippets of text that describe what your page is about. They appear in your page’s source code and don’t appear on the page itself. Think of them as small descriptions to tell search engines about your page.
The two we’ll be focusing on in this article are page titles and meta descriptions:
Most visitors to your website are going to be human. With the exception of robots/crawlers and those absolute animals who disregard lane swimming rules (you know who you are).
So making sure your metadata is up to scratch is SEO 101.
Checking meta tags with Google Sheets
This sheet aka the best meta tag analyzer (probably) finds optimisation opportunities for you quickly, so you can spend more time doing SEO than look for things to SEO.
It evolved out of my laziness, annoyance and desire to speed up my work.
Why spend ages digging through data for work to do?
Find the opportunities quickly so you can spend more time doing the work and getting the results.
How it works
This sheet combines data from the Search Console API and a manual Screaming Frog export, to identify pages in need of a meta tag rewrite.
Titles and meta descriptions should include the top keyword that the page is ranking for – if they don’t, they’ll be flagged for you.
They’ll also be flagged if they’re over the suggest character counts – 60 and 160, for titles and descriptions, respectively.
How to use the sheet
To get this meta tag checker sheet setup…
What you’ll need:
- Google Search Console
- Search Analytics for Sheets
- Screaming Frog
- A Google account (obviously) so you can make a copy of this template:
Grab the Meta Tag Analyzer Sheet
Step 1 – Crawl your website with Screaming Frog
Fire up Screaming Frog and crawl your site. Once done, filter by HTML and export as CSV.
Now open your file and copy all the data. Go to the Screaming Frog tab, click in cell A1 + paste away.
Or import the CSV like by going to File >>> Import and then uploading your file.
When prompted, choose ‘Replace current sheet’:
Once your crawl data is in the Screaming Frog tab, make sure to delete the top row (Internal – HTML) otherwise the sheet will be blank.
Sidenote: Using IMPORTXML
You can actually get metadata from a website without firing up Screaming Frog. In fact, you can get it without ever leaving Google Sheets.
IMPORTXML is your friend. Use these formulas:
Import page title from URL:
Import meta description from URL:
A word of warning: I’d recommend you only use this method for a small site or if you only want to pull data from a small sample of pages.
When I’ve used lots of IMPORTXML formulas in the same sheet in the past, it can seriously slow it down and cause it to crash.
Step 2 – Get GSC into your sheet
If you haven’t already, you are going to need to install this sheets add-on to pull in GSC data:
Once you’ve got this installed, move to the next step to configure the add-on:
Step 3 – Setup the add-on
If it’s the first time running this add-on, you’ll need to authorize your sheet.
Make sure your Google account is authorized to use the Search Console property you want to use!
Search Analytics for Sheets settings:
- Go to Add-ons >>> Search Analytics for Sheets >>> Open Sidebar.
- Select a date range
- Choose site.
- Add Page & Query to the Group By box. MUST be in that order.
- In Results Sheet, select ‘GSC RAW’.
It should look like this:
Once you’ve set this up, hit request data.
Any existing data in the GSC RAW tab will be overwritten.
This will fill the tab with data.
Step 4 – Go look at your meta tags to rewrite
Here’s what the template shows you:
- URL – What URL is ranking
- Top Keyword – The top keyword your page is ranking for
- Impressions – How many impressions your page is getting
- Title Missing KW – A simple yes or no: does your title contain the top keyword
- Meta Missing KW – Another simple yes or no, this time: does your meta description contain your top keyword.
- Title – Your current page title
- Meta Description – Your current meta description.
Hmmm sounds great, but what kind of things can you actually do with this information?
The obvious one is:
Add in missing keywords to titles and metas.
- How to Craft the Perfect SEO Title Tag by Ahrefs
- How to Write an Effective Meta Description by HubSpot
- On-Page SEO Resources by OnPage Academy
Don’t forget to grab a copy of the sheet here to help you do optimise your page titles and meta descriptions. Also, checkout the directory of Google Sheets templates to help you automate other SEO tasks.