If you manage an agency or are a freelancer, you’ll likely need a way to automate the process of keywords tracking and get periodic reports for the SEO campaigns you are maintaining!
There are plenty of SEO tools available to help with this. And with the SEO industry growing at a rate of 8.7% CAGR and reportedly valued at USD 8.27 billion in 2022, the market has introduced some of the best tools like Ahrefs and SemRush.
And likewise any other industry, there are tools designed for mid-sized markets, such as Ubersuggest and RankTracker, which provide more affordable options.
But what if you don’t want to spend too much too soon on these tools? Or perhaps you’re looking to track 1,000 keywords daily, but none of these tools fit your budget.
Now, don’t get me wrong — the tools I mentioned earlier are fantastic. However, if your sole purpose is to track rankings and automate the process, there’s a more economical way to achieve that.
In this read, we will make an automation in Google Sheets using Google APP script and Google SERP API.
Why Choose Our Google Sheets Keyword Tool over Others
Again, as I said earlier, this is best for businesses who just want to track keyword rankings.
I will take a scenario here wherein you can best use this Automation.
Let’s suppose you are a business that needs to track 1000 Keywords daily for 30 Days (A month).
In the table below, I have compared all the tools that I mentioned above and see which one would be economical to do just the sole task of rank tracking.
Clearly, you can track more keywords using an API and that too daily!!
What Tools Are We Using To Create Tracking Mechanism
We’ll use Google Sheets and the Google SERP API will help to pull data from search engine pages. By setting up automation, we’ll schedule daily keyword rank tracking, ensuring you always have the latest data.
I have used Google APP Script on Sheets to create the process wherein we fill in all the basic details and then the app script helps us to get data to sheets.
Google App Script is used to develop lightweight applications in Google Sheets.
I have used ChatGPT to develop this APP script and to add more functionalities to your script, you can again use GPT by copying it from your sheet (which we will give below) and paste in GPT.
How Does Our Rank Tracker Work
We’ll use the Google SERP API to pull keyword data, including the position of each keyword in search results. The API provides the data in JSON format, which we process to extract the keyword positions.
Additionally, we’ll automate the process by creating a scheduler that can run daily or weekly, depending on your needs.
For this tutorial I am using a Google SERP API from Scrapingdog.
Preparing Your Rank Tracking Mechanism⬇️
We have to first Sign Up for Free on Scrapingdog to get Your API KEY.
After signing up you will land on your dashboard and here you will find your API_KEY. ⬇️
This API_KEY is your access to Google’s Ranking Data, for testing purposes since we have 1000 Credits available, we can test this API.
👉Here is the link to your Google Sheets Rank Tracker👈
Once you click the link, you will get this notification.
Click on “Make a Copy”, and the template will be copied for your account!
In the “Basic User Credentials” you have to fill in all the details for your campaign.
- In the A2 cell, insert Your Scrapingdog’s API KEY.
- In the B2 cell, insert the domain for which you want to track the keywords.
- From the C2 cell start inserting your keywords, keep one keyword in one cell.
Let’s test our Rank Tracker, I have filled in all the details.
Click on Track Keywords and the script will run to track your keywords.
For the first time, the script will ask your permission to run.
Then Choose the account for which you want this script to run!!
Then Click on Advanced and click on Go To Rank Tracker.
The script will run and give a pop on the right side when done!!
All your keywords will now be freshly tracked on the second sheet with the name “Tracked Data”🔽
Automate Your Keyword Rank Tracker for Daily Tracking
Our current setup allows us to track keywords on demand. This means you can open up your Google Sheet & run the function which is tracking keywords. However, it isn’t automated yet.
Back to our sheet where we tracked keywords.⬇️
Up until here, those keywords on the sheet were tracked on Friday. Today while I am writing this post again, it is Tuesday!!
And since the process wasn’t automated, the Google Sheets showed the data one day (22/11/2024).
However, I have another Google Sheet setup made for Scrapingdog’s SEO keywords. You can see how this sheet looks up when automated!!
This Rank Setup is Automated and Set to Run at 8–9 AM Daily. You can also do the same in your setup.
Setting Up the Trigger for Daily Keyword Tracking
In your sheet, Go to Extension → Apps Script.
A new tab will open up your Google App Script. This is where the code is written to track keywords using the Google API.
Again you can use this script in ChatGPT to create more functionalities in your tracker. Also, if you want to understand this whole script you can do so by GPT.
But for now, let’s understand how to trigger our setup for daily tracking!!
This is where you can automate the function which is responsible to track keywords, to repeat itself at a specific time. It can be done daily, weekly, once in 15 days, or with any periodic cycle you would like to do.
After clicking, you will get a pop-up where you need to configure the right settings. Follow the steps to set it correctly.
- Choose the Function to Run: Select the function
trackKeywordsfrom the drop-down menu. This is the function that will be executed automatically according to the schedule you set. - Deployment to Run: Choose the latest deployment option, typically labeled as ‘Head’ in the Apps Script environment. This ensures that the most current version of the script is used each time the trigger runs.
- Event Source: Set this to ‘Time-driven’ to make the trigger operate based on time.
- Type of Time-based Trigger: Choose ‘Day timer’ to run the script once per day.
- Time of Day: Select a suitable time range, like ‘8am to 9am’, which dictates when the script should execute each day.
- Failure Notification Settings: This is optional but recommended. Set it to notify you daily of any failures. This will help you quickly address any issues that may arise with the automated tasks.
The above setup is quite understandable, to brief it we are triggering a function (trackKeywords) daily between 8 am To 9 am. Finally Save, the trigger!!
You can set as many triggers as you want!!
Finally, our trigger is working perfectly and you can check it below!!
How To Change the Location of Keywords to be Tracked
By default, our tracker tracks keywords from “US”. You can easily change this setting from Google App Script.
Let’s suppose you want to track ranking from France. Here’s a simple way to do it:
- Open the Script Editor: Access the Google Apps Script associated with your Google Sheet.
- Locate the API Request Code: Find the function where the API call is made. This is typically a function like
callScrapingdogApi. - Modify the
country Parameter: In the URL where parameters are defined, locate thecountryparameter. Change its value fromUSto the desired country code. For example, to track rankings from France, replacecountry=USwithcountry=FR. - Save and Test: After making the change, save the script and run a test to ensure that the rankings are now being pulled from the new location specified.
What other parameters we can change?
For rank tracking using the Scrapingdog Google SERP API, various parameters can be adjusted to refine search results, providing more targeted and useful data for SEO analysis. This can be done to implement more filters to your rank tracking sheet/tool.
Important parameters include:
- Page: Specifies the page number of Google search results, enabling tracking beyond the first page to understand deeper ranking dynamics. We are currently taking results from the first 100 results in our app script.
- Language: Adjusts the language of the search results, crucial for monitoring keyword performance in different linguistic markets. The default language is English (en).
- TBS: This parameter allows for filtering search results by time or type, such as retrieving only recent results, which is useful to see how new SEO strategies are affecting rankings.
- Safe: Controls the safe search filter, which helps in excluding adult content to maintain relevance and appropriateness of the results.
- Search_mob: Critical for tracking how keywords perform on mobile devices as compared to desktops, reflecting the increasing importance of mobile-first indexing and optimization.
- Domain: Specifies the local Google domain for geographically specific search results. For instance, using “google.co.in” for India or “google.co.uk” for the UK allows tracking of keyword rankings relevant to those specific markets. This is particularly useful for local SEO efforts and helps businesses to track regional keywords more effectively.
With these parameters, you can edit the Google App script and add more functionalities to your rank tracker. You can read more about these parameters in the documentation of Google API.
How Google Scrapingdog’s Pricing Work
Scrapingdog’s Google SERP API Pricing offers various pricing tiers to accommodate different usage levels and support needs, each defined primarily by the number of Google requests it supports. Understanding these plans is crucial for optimizing your cost and maximizing your SEO efforts.
Let’s take the example of the Lite Plan, which offers 40000 Google Requests per month. Suppose you want to track the ranking of 1,000 keywords daily for a full month. Each keyword tracked counts as one request. Here’s how the math works out:
- Daily Tracking: 1,000 keywords x 1 request per keyword = 1,000 requests per day.
- Monthly Tracking: 1,000 requests per day x 30 days = 30,000 requests per month.
This calculation shows you would use 30,000 of your 40,000 available requests for tracking 1,000 keywords each day across the month.
You will still have 10,000 requests remaining, which allows you to track up to 333 additional keywords for another 30 days, or you could use these extra credits to increase the frequency or volume of keyword checks temporarily without needing to upgrade your plan. So in total, you can track 1333 keywords daily from the $40 plan.
Conclusion
Every SEO tool relies on scraping search engines to gather data, and this is precisely what Scrapingdog’s Google SERP API facilitates.
For businesses looking to develop their SEO tools, Scrapingdog’s Google SERP API can offer a reliable source of data directly from Google search results.
Be it a rank tracker or any other SEO-related tool, our API can provide the detailed insights necessary to monitor and enhance your online visibility effectively.
Additionally, the provided Google Sheets template serves as a practical tool for testing the capabilities of our API.
Additional Resources
