bernard
BuSo Pro
- Joined
- Dec 31, 2016
- Messages
- 2,599
- Likes
- 2,301
- Degree
- 6
We had a question here about free rank trackers in a different thread and I get it, because even the cheapest rank trackers have become quite expensive.
Here's a hack to turn Google Sheets and Google Search Console into a free Rank Tracker.
What you will need:
1. Google Sheets
2. Search Analytics for Sheets plugin
3. Google Search Console
We're going to use the fact that GSC has ranking data for keywords if you click into each keyword. We're going to export that data to Google Sheets and then we're going to use a simple lookup function to find the rankings for our keywords.
Ok, lets go.
1. Google Sheets
First of you'll need to create a new Spreadsheet in Google Sheets. Then you'll add two Sheets, one called "Keyword Rankings" and one called "GSC Data".
2. Set up Search Analytics for Sheets plugin
Next, you're going to install and launch the Search Analytics for Sheets plugin. You will need to allow it access to GSC. Then it will open in the sidebar. You will now export the data into our "GSC Data" sheet and you will do this every time you want rank tracking.
What you want to do is open the plugin, it opens into the sidebar, then choose your site under "Verified Site:". Then you'll use the Date range. GSC updates daily now, with 2-3 days delay, I tend to look at the last 7 days for more accurate rankings, but that is up to you. You can choose the last available day or the last 7 days or whatever you like.
The next is important, the "Group By", here you'll want to select "Query". In "Rows returned" choose the maximum number.
Now under "Results Sheet" choose the "GSC Data" sheet or the sheet you want. It's important to have the same sheet each time, so that you can reference it in your ranking/keyword sheet.
Then click and run. If you did it correctly, you'll get the data that looks like this:
3. Get your rankings and data with VLOOKUP
The final step is to set up your ranking sheet, which can also be your content planning sheet, more on that later.
This sheet can be as simple as having your keywords in column A and then rankings in column B.
Simply put your keywords in column A, one keyword pr. row.
Then in column B cells, put in the following formula:
And that should do it. We just added a check for if the keyword exists and then return a blank if it doesn't, just for aesthetics.
You just need to update the extension whenever you like to get fresh data.
4. Why bother with this method?
Seems like a lot of work for keyword rankings that aren't even up to date? Yes, that is true, but first of all, it's free and second, it's accurate, since it comes from Google.
More important though, this allows you to get your data into Google Sheets from where you can combine with other data, such as conversion data (from affiliate or signups or ad revenue).
It allows you to build a fully integrated, customized content overview and content plan, where the data is free and fresh.
With this data, you can run various checks using Google Script (similar to VBA in Excel). You might want to check how long it takes your content to rank on the first page. You can do that quite simple with a few lines of code that checks if your ranking is <10 and then fill out the column next to it with the number of days since publishing.
You can also add a custom filter that colors a cell based on its value. Very useful for quickly spotting something that needs an action.
What does this data show you then? Well it could tell you something about your decision making in choosing keywords, how you judge their difficulty, how much a high ranking correlates with earnings, compared to say long tail. All kinds of things you can do when the data is dynamic and in Google Sheets, because all these things can happen automatically.
In this example we also have CTR data, so that is also useful at a glance to find content where me might want to rewrite the Meta Description for better CTR.
While this particular use case is not that useful, I hope it inspires you to think about how you can combine data in Google Sheets to gain valuable insights.
Here's a hack to turn Google Sheets and Google Search Console into a free Rank Tracker.
What you will need:
1. Google Sheets
2. Search Analytics for Sheets plugin
3. Google Search Console
We're going to use the fact that GSC has ranking data for keywords if you click into each keyword. We're going to export that data to Google Sheets and then we're going to use a simple lookup function to find the rankings for our keywords.
Ok, lets go.
1. Google Sheets
First of you'll need to create a new Spreadsheet in Google Sheets. Then you'll add two Sheets, one called "Keyword Rankings" and one called "GSC Data".
2. Set up Search Analytics for Sheets plugin
Next, you're going to install and launch the Search Analytics for Sheets plugin. You will need to allow it access to GSC. Then it will open in the sidebar. You will now export the data into our "GSC Data" sheet and you will do this every time you want rank tracking.
What you want to do is open the plugin, it opens into the sidebar, then choose your site under "Verified Site:". Then you'll use the Date range. GSC updates daily now, with 2-3 days delay, I tend to look at the last 7 days for more accurate rankings, but that is up to you. You can choose the last available day or the last 7 days or whatever you like.
The next is important, the "Group By", here you'll want to select "Query". In "Rows returned" choose the maximum number.
Now under "Results Sheet" choose the "GSC Data" sheet or the sheet you want. It's important to have the same sheet each time, so that you can reference it in your ranking/keyword sheet.
Then click and run. If you did it correctly, you'll get the data that looks like this:
3. Get your rankings and data with VLOOKUP
The final step is to set up your ranking sheet, which can also be your content planning sheet, more on that later.
This sheet can be as simple as having your keywords in column A and then rankings in column B.
Simply put your keywords in column A, one keyword pr. row.
Then in column B cells, put in the following formula:
Code:
=IF(ISNA(VLOOKUP(LOWER(A2),'GSC Data'!A:E,5,FALSE)),"",VLOOKUP(LOWER(A2),'GSC Data'!A:E,5,FALSE))And that should do it. We just added a check for if the keyword exists and then return a blank if it doesn't, just for aesthetics.
You just need to update the extension whenever you like to get fresh data.
4. Why bother with this method?
Seems like a lot of work for keyword rankings that aren't even up to date? Yes, that is true, but first of all, it's free and second, it's accurate, since it comes from Google.
More important though, this allows you to get your data into Google Sheets from where you can combine with other data, such as conversion data (from affiliate or signups or ad revenue).
It allows you to build a fully integrated, customized content overview and content plan, where the data is free and fresh.
With this data, you can run various checks using Google Script (similar to VBA in Excel). You might want to check how long it takes your content to rank on the first page. You can do that quite simple with a few lines of code that checks if your ranking is <10 and then fill out the column next to it with the number of days since publishing.
You can also add a custom filter that colors a cell based on its value. Very useful for quickly spotting something that needs an action.
What does this data show you then? Well it could tell you something about your decision making in choosing keywords, how you judge their difficulty, how much a high ranking correlates with earnings, compared to say long tail. All kinds of things you can do when the data is dynamic and in Google Sheets, because all these things can happen automatically.
In this example we also have CTR data, so that is also useful at a glance to find content where me might want to rewrite the Meta Description for better CTR.
While this particular use case is not that useful, I hope it inspires you to think about how you can combine data in Google Sheets to gain valuable insights.