Google Sheets + Analytics = Simple, Actionable SEO
Like many of us, I’ve spent hundreds of hours diving into analytical rabbit holes – producing reports nobody reads or can understand, summarizing insights that nobody will take action on – it’s the silent killer of ambition in analytics.
So when I find a simple opportunity to turn data into action – actually changing my output and approach – it’s like striking the world’s tiniest, invisible goldmine.
That happened to me the other day when, inspired by Daniel’s post on the new Google Analytics <=> Search Console integration, I took a chance and cracked a Google Sheet to mash up two datasets:
- Google Analytics engagement data (measured by conversion rate), sliced by landing page.
- Google Search Console CTR data, sliced by landing page / keyword combination.
Since I’m lazy, I looked for an automated way to merge the datasets without too much manual work. I ended up using Blockspring to pull the data for my blog into Sheets, and I’m here to share what I learned along the way.
Note: if you want to skip straight to performing this analysis on your own site, you can grab a version of the Google Sheets template for either Blockspring or Supermetrics.
The results were so simple and actionable it blew my mind
It was like blending two bottles of Two Buck Chuck that you’ve had sitting in your kitchen for months, and finding out that, blended properly, they taste like a ’49 Lafite-Rothschild.
Following Daniel’s recipe, I wrote Sheets queries (similar to SQL) to pluck out two groups of pages for improvement.
1) Pages with low CTR but high engagement
Once people land on these pages, they’re loving them – but I’m shooting myself in the foot somehow with my search snippet.
The goal here is to rewrite the search snippet around the keywords I’m getting impressions for – which aren’t necessarily the keywords I wrote the post to target.
2) Pages with a high CTR but low engagement
This content is killing me.
People are clicking through from search results, expecting something magnificent, and I’m completely letting them down. The content of the page isn’t matching up to the intent of the searcher, and I can do a better job helping them answer their question.
One could call this approach ‘reactive SEO’ – because what’s actually happening IRL is more important than what you intended to happen when you produced a piece of content. It happened, so the only move now is to go with it. 🙂
Here’s what I learned, and some ideas for getting these pages off the mat.
Improving Search Snippet Efficiency
This post is ranking #36 for the search term ‘google analytics report template’ – a pretty high-traffic keyword for my niche. But its .5% CTR is abysmal, and is definitely preventing that ranking from edging higher.
The post is an aggregation of the most popular Google Analytics templates from folks like Avinash Kaushik, ported into Google Sheets with Blockspring. What’s ironic, is that the actual content of the post is interesting to the ‘report template’ searcher, but I shot myself in the foot with the snippet. Oh well, an easy fix:
Improving Content Efficiency
One benefit of doing an analysis like this is it’ll often expose opportunities for entirely new pages on your site. For example, I noticed the page below, a comparison between Blockspring and Supermetrics, is ranking #7 for the keyword ‘supermetrics templates,’ with an above-average CTR of 3.6%.
But the post itself has nothing to do with Supermetrics templates at all – it’s only being picked up for that term tangentially. An easy win would be to make a separate page containing just Supermetrics templates, and link to it from the intro of that post. At least then, when Google generates the dynamic snippet, it will read like a complete sentence. Over time, that new post can hopefully outrank this non-relevant post.
One more opportunity
This one slapped me right in the face, so I had to share it. It ranks #17 for ‘google sheets query,’ with a high CTR of 7% – but super-low conversions:
When I took one look at the post, it became pretty clear why. It’s loaded with embedded Youtube tutorials, but for some reason I opened the post with a wall of text. An easy win would be to open the post with a video, then explain what I’m talking about.
Running this for your own Site
Like I mentioned before, if you want to repeat this analysis for yourself, grab the template for Blockspring or Supermetrics. I’d love to hear what you can find out for your own site, feel free to holler to @losershq.
Postscript: How laziness inspires action
One secret I’ll share: I would’ve never been able to take this action were it not for my laziness. Let me explain.
If doing this analysis had required me to copy / paste / export data from GA and Search Console, I would’ve never done it. I’m too lazy, and let’s face it – none of those tasks are any fun.
Instead, by using Google Sheets Add-ons like Blockspring and Supermetrics, it became *fun* to explore data in this way – like spreadsheet magic, or painting with data.
Not only was the data wrangling fun, but I also had *time* to think about exploratory questions like Daniel’s, and write queries to explore them. When you’re lazy, you can start to work up the value chain of data.
So next time you’re thinking about an analytics challenge, ask yourself: how can I be lazier?
