Frequently Asked Questions

Visualize position changes of your tracked keywords in Sheets and Data Studio via the Nightwatch API

What is Nightwatch?

Nightwatch is an advanced SEO tool with many functionalities. It has a supreme user interface, enabling keyword position tracking (rank tracking), backlink motioning, and site audits. 

You can use the tool as an alternative to Semrush or Ahrefs but it’s fair to say that in comparison to the industry giants, there are a few elements still missing in Nightwatch, which is reflected in its lower price-point, too. 

The tool also enables API integration with Google Analytics and Google Search Console, as well as keyword discovery modules and competitors’ research. 

One thing I recently discovered the tool does not support fully is a Data Studio integration. Surprisingly, I didn’t find an in-depth tutorial to cover the integration of the existing Nightwatch API to Data Studio via Google Sheets Formula with AppScript. Hence, this tutorial is being made. 

Wait. Doesn’t Nightwatch have a Data Studio connector? 

Good news — yes, Nightwatch has a Data Studio connector.

The bad news is it only pulls three metrics and one (date) dimension: 

• data for average position
• click potential
• visibility index

Here is what you can quickly visualize using the Nightwatch Data Studio connector.

This really will not be sufficient for reporting purposes for any of the sites you track in Nightwatch. 

What now? 

Nightwatch has an API, available with Optimize and Agency plans. 

Here is how the pricing structure looks like — it’s a bit friendlier than Semrush or Ahrefs.

How to connect the Nightwatch API with Google Sheets using Google Scripts

Fast-track Nightwatch Set-up

To fast-track, the process, copy the sheets template below and simply enter your URL number and API key, then jump to the section How to visualize the position tracking data from Nightwatch API in Data Studio or copy the Data Studio dashboard I built for Nightwatch Rank Tracking.

Replicate the process via Google Script in Google Sheets

So, let’s explore how to connect the API with Google Sheets via a Google Script.

1. Create a new Spreadsheet and open the script editor

2. Rename your file ‘importJSON’ and your project (any way you like).

Ensure that you keep the .gs extension on the file (that stands for google script). 

Delete the function() code in the file and leave it blank for now. 

3. Locate Stefan Judis’ API to sheets Google Script and paste the code.

Stefan Judis adapted this script for the integration of different APIs to Google Sheets and shared it with his audience on YouTube. Definitely check it out and show him some love. 

Here is a link to the google script for API integrations over at Stefan’s Github. 

Open the first file and copy its contents. 

Paste the raw contents that you just copied into the script editor and save the script.

4. Go back to the spreadsheet and use the function.

In the top left-hand cell (A1), paste the following line by replacing:

=ImportJSON(“https://api.nightwatch.io/api/v1/urls/REPLACEWITHTHEURLID/keywords?access_token=REPLACEWITHYOURACCESSTOKEN")

• REPLACEWITHTHEURLID with the id of the site you want to pull positions for

You can find the site URL by opening the dashboard for the set site and copying it from the URL, it’s a six-digit number after the /urls/ part of the address.

• REPLACEWITHYOURACCESSTOKEN with your access token

To access your access token, go to Settings > API Access (/settings/api-access)

And get your token from there.

Then, voila. 

If all worked well, your spreadsheet should be filled in with the data from Nightwatch. 

That is great but what did we do with the Nightwatch API?

We performed a request for Listing Keywords via the script and the API. 

List Keywords is just one of the many functions that the Nightwatch API has, but it’s the one the most comprehensively mirrors the position tracking reports in the Nightwatch Dashboard for each site. 

You can check the documentation of this function and others on Nightwatch’s website. 

What if you want to integrate other data via the Nightwatch API?

You can do this by changing the URL in the import function (from the previous section), by following the relevant structure, indicated in the docs (you can view this on the panel on the right-hand side). 

For instance, you can: 

• explore backlinks of a particular URL
• get SERP data for your tracked keywords, and even 
• retrieve data for your competitors.

A nifty trick of testing whether your call URL works is to paste it in your browser before pasting it in the formula and see what type of data it returns and how it’s wrapped. 

What if you don’t want to pull all the data fields from the call?

Stefan’s script is amazing as it allows you to also indicate the fields that you want to pull. 

After the import URL, you can put a comma and, in brackets, specify the dimensions that you want to pull. 

How to visualize the position tracking data from Nightwatch API in Data Studio

Here is a simple way how the data, pulled from the API can be visualized in Data Studio, alongside the overview data provided by the Nightwatch API, illustrated at the start of this article.

Which dimensions will you need to visualize?

• Query — the keyword you are tracking
• Position — the current position
• Google Hl — language
• Google Gl — country
• Last Day Change
• Last Week Change
• Last Month Change
• Best Position — the best position the site has ranked for this keyword
• Result Url — URL of the page that is the main result for the keyword
• Position Type — is it an organic results position or a featured snippet

What are the alternatives to Nightwatch?

There are many industry tools that offer position tracking. Many of them charge a lot for use of their API. Few have Data Studio connectors. 

Hence, using Nightwatch is definitely a budget-friendly alternative to these approaches.

Nonetheless, it’s worth mentioning that Semrush also does position tracking, has an API, and a Data Studio connector. I’ve previously written about how to connect Semrush to Data Studio, as well as the limitations of doing so. 

Semrush position tracking Data Studio connectors require a Guru or above plan, but this also includes other position tracking tools they have, as well as their SEO writing assistant (which I used to explore the performance of GPT-3 in a previous article of mine). 

From personal experience, I am also aware that the API pulls with Semrush can become quite expensive, especially on an agency account, where multiple clients are managed simultaneously.

Final Thoughts

Hope you’ve enjoyed this tutorial. 

Let me know on LinkedIn or Twitter (@lazarinastoy) how you have visualized your position tracking data from Nightwatch. 

Linking below some other useful links: Working with Nightwatch Data Studio Connector
Nightwatch allows you to connect your account with Google Data Studio to create dynamic reports of your preference with…docs.nightwatch.io

~Some links on this article are affiliate links. Buying a subscription to Nightwatch via my links will earn me a small commission. If you end up clicking on the link and liking it after your free trial, thank you for supporting me. ?