BigQuery Hack to Track Total Rankings in Data Studio

BigQuery Hack to Track Total Rankings in Data Studio

Tracking keywords mean a lot to SMBs and some mid-market businesses, but as we look at our content marketing on a larger scale, we need to track a smaller set of keywords.

Customers want a bird’s eye view of how their keyword set is performing. However, SEO professionals did not find a simple way to achieve this using existing means.

In this column, you will learn a BigQuery hack to track total rankings with Data Studio.

You will also have access to a free Data Studio template to visualize BigQuery data. ready? Let’s jump right in.

Google sheet issue

Usually, I show clients the total amount they are ranking, broken down by position. Semrush’s biological research tool has a great view for it.

This chart can be filtered to show only certain posts and only certain sections of the site, which is useful.

However, this chart alone has several filtering and reporting limitations that can be resolved through Data Studio.

Semrush and other SEO tools have similar charts and most of them are connected to Data Studio. However, none of them has a chart in Data Studio that allows us to see all the ranking keywords broken down by month.

They only see us tracked keywords or currently perform keyword rankings for that given month.

SEO professionals have tried to achieve this difference by downloading a new list of keywords every month and adding them to Google Sheets as their connector for the data sheet.

So what is the problem with this?

Adding large sets of data to Google Sheets can take a very long time. Once you reach hundreds of thousands of rows of data, Google Sheets can freeze or even crash your browser.

The biggest burden here is time.

The bigwick fix

BigQuery allows us to upload exported data without any modification and to sync directly to Data Studio in a fraction of the time.


Continue reading below

The downside is that you still have to export your total ranking keywords every month, but bypassing Google Sheets will save a ton in the future.

Disclaimer: BigQuery has a paid component. However, until you reach TB of data, it is penis. The cost is nominal and it is worth the time lost waiting for Google Sheets to load, praying that an accident will not happen.

How to set up your first BigQuery project

Getting started is quite easy. The only condition is that you already have some kind of Google account; Even a Gmail will work.

I know that working in the Google Cloud Platform can be very intimidating. There are many advanced features and it is not the most intuitive platform.

However, I have included a screenshot for every step of the process to help guide you through the setup.

If you are stuck, please do not hesitate to reach out to me in the comments below.


Continue reading below

1. Log into the Google Cloud Platform and create a new project

Create a new project.Name the project.

Go to the Google Cloud Platform, create a new project and specify the name and location.

2. Go to BigQuery

Go to BigQuery.

3. You may need to set up billing if you have not already done so.

4. Once you are ready to go, choose Create Dataset.

A quick tip: it should only contain letters, numbers, or underscores.

Create a dataset.Fill in dataset information.

5. Go to the new dataset.

Go to the dataset.

6. Create a new table:

Under Source, change from “Create Table:” to upload and select your CSV file. This should be the first set of your keyword ranking.


Continue reading below

Under Destination, give your table a table name and make sure that you use the same table name for each data upload. In the example below, I named my table “Cardinal”, but you can name your table whatever you want. I recommend using your website name to make it easier to identify later.

Under Schema, be sure to select the checkbox under Auto Detect.

Select the blue “Create Table” button at the bottom.

create TABLE.
Steps to create a table.

7. Now, you can add more data.

Select Create Table again.


Continue reading below

Upload your second set of data. For example, if your first upload from step 6 was February keyword ranking, then this step you will upload March keyword ranking and next month you will follow these steps and upload April keyword ranking.

Under Destination, give your table a table name and make sure that you use the same table name for each data upload.

Under Schema, be sure to select the checkbox under Auto Detect.

Select the blue “Create Table” button at the bottom.

Important: Under Advanced Options for the Write Priority dropdown, select “Add to Table”.

Add more data.

Repeat step 7 for the set of data for each new month.


Continue reading below

Okay, the hard part is over!

Moving forward each month, instead of uploading your data to Google Sheets, you can follow step 7 to update your data.

Free Data Studio template for visualizing BigQuery data

Bigquery Data Studio Template.

I have created a free data studio template that requires very little setup to get you started.

Page one of the template includes the instructions below, but with screenshots. Feel free to go directly to the template!


Continue reading below

How to set up template

Open template.

Duplicate the report by clicking the Duplicate button at the top right of the report.

You will need to create a new data source and connect it to your BigQuery project. Click “Create New Data Source”.

On Connection Options, select BigQuery.

When adding a new data source, create a custom field to sort the status distribution. This can also be done after connecting to the data source.

Create a custom field in Data Studio.

Name this field, “position (custom)” and copy the formula below in the field.


Continue reading below

    WHEN Position <= 3 THEN "Top 3"
    WHEN Position <= 10 THEN "Top 10"
    WHEN Position <= 20 THEN "Top 20"
    WHEN Position <= 50 THEN "Top 50"
    WHEN Position <= 100 THEN "Top 100"

Click done.

From here, the report should be fully updated with your data.

this much only! I hope this BigQuery hack helps you track the overall ranking which is more interesting and scalable.

more resources:

Image credit

All screenshots by author, April 2021