How Google Sheets Can Help You to Uncover Hidden Keyword Level Data

Google Sheets data Analytics

It is not that keyword level data has completely gone, rather you find it harder to obtain. You can use Google sheet to bring data from Google Analytics and Search Console. You would get the privilege of having top keywords and landing page engagement metrics altogether for free. None can call it perfect key-word level data but you can see the keywords getting more clicks and organic engagement across the Internet. Find how to achieve this in 6 steps:

Get Google Analytics and Search Analytics for sheets add-ons

Google Analytics add-on will allow you to pull any metric from Google analytics into your spreadsheet. On the other hand, Search Analytics for Sheets will pull data from Search Console. Combining data from both resources represents report in a meaningful way.

Get Google analytics reports

Upon installation of Google analytics add-on, you will get “Google analytics” in your menu. With this option, sidebar menu would pop-in, where you need to select the account, view and property option to pull the data. You would find options to rename and create report from here.

Configure Google analytics reports

Familiarize yourself with the report configuration tab, using which you can configure the data that you want to pull. You could set the name and it can later be changed too. Be conscious to find the type of data whilst pulling them from the Core Reporting API. You can see start-end date and metrics you want to pull and dictate columns to see in your report. View option will be there in your profile ID. Finally, sort metrics and segment organic traffic that you want to see.

Run Google analytics report

You will get Run reports under Google Analytics add-on and can run on a specific day to align with ongoing report you have. It will also notify you if any of the fields has been implemented incorrectly.

Run search analytics for sheets report

Running Search Analytics for sheet report is quite simple. Launching the sidebar for Search Analytics for sheets, you will authorise the app and can set the parameters of your report. As long as everything runs correctly, you can see the top search results, clicks, impression, average position and CTR.

Remove domain name from search analytics landing page

Soon after getting Google analytics data and search analytics data of landing pages, combine them all. Entire data should remain in same format, but Google analytics fetches top landing pages excluding domain name and Search Console pulls entire domain. Hence, strip domain name from Search Console data and replace your domain name with no replacement, i.e., eliminating the domain name from the URL.

Download Keyword Level Data template that has proper formula to pull landing page session over one year. It will contain report on total goal conversions and bounce rate. Once the template is in use, copy the keyword data tab to your Google worksheet. It will give you the privilege to combine the data.


No description.Please update your profile.