Search Console Keyword Rank Tracker Template [Google Sheet]

Are you tired of endlessly checking your Google Search Console to see where your website is ranking for your target keywords? Or do you have a tool to track your keyword rankings but it’s costing you a little too much?

I recently started exploring the Search Console API to see if I can build a completely free keyword rank tracker template just using Google Sheets and the API. I’m quite excited to show you guys how the report turned out, help you understand the different things you can do with it and also give you instructions on how to set it up.

What can you do with the keyword rank tracker template?

My expectations for the free rank tracker included several specific needs. These needs comprised the capability to extract data from all available search console properties, automatic daily tracking of selected keywords, the option to categorise keywords into distinct groups, and the ability to generate daily trend charts.

So let’s see how all of this turned out:

1. Ability to extract data from all available search console properties:

Once the keyword rank tracker template is set up properly, you can get a list of all the Google search console properties that you have access to. From this, you can select which property you would like to set up the rank tracking for.

You can extract the impressions, clicks, position and CTR for the last 7 days, last 28 days, last 3 months, last 6 months and last 12 months. You can even set the number of keywords you want to extract (up to 25000 keywords).

2. Select which keywords to track:

You can appear in search results for various words or phrases. However, not all of them may be relevant. After obtaining keywords from the search console, you might find some unrelated keywords in the list. To address this, I ensured that we can choose only the relevant keywords from the list and add them to the rank tracker before starting it.

3. Automatic daily tracking of keywords and categorisation into keyword groups

Set up a trigger to run daily and the Google sheet will keep getting updated with the rankings for your selected keywords. You can even assign keywords to different groups and filter the view to analyse an entire group of keywords at once.

4. Get ranking trend charts for up to 5 keywords:

You can select up to 5 keywords and see the daily ranking trend in a line chart.

How to set up the keyword rank tracker template?

Let’s get started with the setup.

💡 Note:
Make sure that the email address you use for this setup is the same as the one you use to access properties in Google Search Console.

1. Make a copy of the google sheets template:

Make a copy of the Google Search Console Keyword Rank Tracker Template.

2. Create a google cloud project

Head to https://console.cloud.google.com/ and create a new Google Cloud project.

Click on Select a Project > New Project > Enter a project name and click on Create.

3. Enable google search console API

Once you’ve created the project, you’ll have to enable the Google Search Console API. To do this, open the project that you just created, search for Google Search Console API and click on Enable.

4. Build an OAuth consent screen

To build your own oAuth consent screen, click on the OAuth consent screen option in the left-hand navigation > Select User Type as External > Click on Create.

Once you’ve done that, assign your App Name (e.g. Keyword Rank Tracker) and under the support email and Developer contact information, put your email address > Click on Save and Continue.

Under scopes > Click on Add or Remove Scopes > Search for Google Search Console API > Select the View Search Console data for your verified sites > Click on Update > Click on Save and Continue.

Under test users > Add your email address as a test user > Click on Save and Continue.

Under Summary check if everything is correct and click on Back to Dashboard.

5. Insert cloud console project number into google sheets

To get your project number, click on the hamburger icon on the top left > Go to Cloud Overview and then Dashboard > Copy the Project number from under Project info.

Go to the Keyword Rank Tracker Template > Click on Extensions > App Script > Go to Project Settings > Under Google Cloud Platform (GCP) Project click on Change project and insert the project number that you just copied.

6. Get search console sites

On the Google Sheet’s setup tab, click on the Get Search Console Sites button. A popup will open asking you to Authorise the script, click on Continue > Select your email address and click on Allow.

Once you’ve done that, click on the Get Search Console Sites button again. Once, the script has finished running, you can click on the dropdown icon to get a list of the properties to which you have access.

From this list, you can select the one that you want to track keyword stats for.

7. Extract keywords data

Once you’ve selected the site, date range and no. of keywords to extract, click on the Extract Keywords button.

All your keywords data will be populated in the Analytics Tab.

8. Select the keywords that you want to add to the keyword rank tracker

You can select the keywords that you want to add to the tracker by selecting the checkboxes and clicking on the Add to Tracker button.

9. Assign keywords to different Groups

To set up your keyword groups, click on the first dropdown and then the pencil icon to edit the groups. Once you’re done, click on Done > Click on Apply to All.

Setting up groups allows you to use the Keyword Group filter above and analyse entire keyword groups at once.

10. Setup a trigger for the rank tracker to run daily

Now you have to set up the trigger so this rank tracker runs daily in the background and you don’t have to worry about it.

💡 Note:
Due to a delay of 2 to 3 days in the Search Console data, the rank tracker will show rankings from three days prior when it runs automatically. For instance, if today’s date is April 20th, the rank tracker will display the rankings from April 17th.

To set up the trigger, go to Extensions > App Script > Click on Triggers > Add Trigger

• For “Choose which function to run”, select getKeywordPosition
• For “Select event source”, select Time-driven.
• For “Select type of time based trigger”, select Day timer
• For “Select time of day”, select 11 pm to 12 am.

Click on Save. You can now close the App Script. The sheet will automatically get populated with the rankings for your selected keywords somewhere between 11pm – 12am and will run once every day since the time you created the trigger.

After a few days of the rank tracker running automatically, the sheet will look something like this.

11. Analyse daily ranking trends of selected keywords

You may choose a maximum of 5 keywords to track and analyse the daily ranking trends for. To do this, go to the Daily Trends Chart tab > enter the start and end dates > enter your 5 keywords > click on the Get Data button.

Pros and cons of the keyword rank tracker template

There are a few pros and cons of this tool that I’d like to share with you guys.

Wrapping up

I hope you all like this free tool and find it helpful. Do let me know in the comments if you face any issues or have any other comments or suggestions.

I also recently built a Keyword Research Tool using Google Ads API that you can use. Definitely check it out.