How to Uncover Hidden Keyword-Level Data Using Google Sheets
Posted by SarahLively
Keyword-level data isn’t gone, it’s just harder to get to. By using Google Sheets to marry the data from Search Console and Google Analytics into a sheet, you’ll have your top keywords and landing page engagement metrics together (for free!). It’s not perfect keyword-level data, but in 7 steps you can see the keywords that drove clicks to a page and the organic engagement metrics for that page, all together in one place. The Google Analytics Add-on for Google Sheets will pull organic landing page engagement metrics, and the Search Analytics for Sheets Add-on will pull the top queries by landing page from Search Console. Then, use VLOOKUP and an Array Formula to combine the data into a new tab that has your specified landing pages, the keywords that drove clicks there, and the specified engagement metrics.
What do you mean you don’t know which keyword drove that conversion?
Since the disappearance of keyword-level data in Google Analytics, SEOs have been struggling to tie keyword strategies to legitimate, measurable metrics. We put much of our time, resources, and research efforts into picking the perfect keyword theme, full of topically relevant terms that leverage new semantic strategies. We make sure to craft the perfect metadata, positioning our top keywords in the right place in the title tag and integrating them seamlessly into the meta description, but then what? We monitor rankings and look to landing page metrics, but all of our data is disjointed and we’re left to extrapolate insights based on a limited understanding of how our themes are truly performing.
There is good news, though! Keyword-level data is still there — it’s just much harder to get to given the structure of existing platforms. If you’re like me, you have your landing page metrics in Google Analytics, your keyword click data in Search Console, and your keyword themes in a manual program (probably Excel). Given the way Google Analytics exports data, the way Search Console separates keywords and landing pages, and the nuances you’ve applied to your own keyword theme documents, it’s difficult to marry all of the data in a way that gives you actionable insights and real-time data monitoring capabilities.
Difficult… but not impossible. Enter: Google Sheets. In 7 easy steps you can pull all of this data into one sheet so you can see your keyword theme, the keywords you’re getting clicks for, the page ranking, and any organic metric for that page (think engagement metrics, conversion metrics, revenue metrics, etc.), all in one place! You can monitor keyword opportunities within striking distance, whether the keywords you want to rank for are actually ranking, and what terms and themes are driving the majority of your revenue or conversions. At the end of the day all of this works to give you actionable metrics you can monitor and change through keyword strategies. It’s much easier than you may think, and the steps below will get you started.
Follow this guide to build out a basic Google Sheet that ties …read more
Source:: Moz Blog