Traffic Unveiled: The Power of Google Sheet and Search Console Data Blending

Monitoring & extracting search console data for Large sites can be challenging.

There are tools and mechanisms like regex that can help a great deal to simplify data extraction & monitoring of URL performance.

But what if the URLs that you want to monitor exist on different folders and the regex is not able to handle it?

I mean you can always use pipe regex to match different subfolders but what if your use case requires you to only match a distinct list of URLs that simply cannot be matched via regex?

The solution is GSC + Google Sheets Data Blending 🏆

In this article we will learn step by step how to set it up for constant monitoring & extraction.

Step 1: Add GSC URL > Web

Google-Sheet-GSC-Data-Blending step 1

In this step simply click on Add Data & from list select Google Search Console & choose URL Impression > Web.

Step 2: Create a Google Sheet with your List of URLs

List-of-URLs-Decode-Digital-Market-Google-Sheets Step 2

Simply create a Google Sheet that contains the list of your distinct URLs.

Step 3: Connect Google Sheets in Looker Dashboard

Google-Sheet-GSC-Data-Blending step 3

Simply click on Add Data & connect your Google Sheet. The specific Google Sheet file contains the list of URLs.

Step 4: Begin Blending GSC with Google Sheets

data blending step 4

In this step click on Add a chart & choose Table by default GSC URL Data source will be selected. In the setup right below the GSC data source, an option of Blending will be available click on it.

Once you click on blending you will see the screen like how it appears on the above screenshot.

Now click on Join another table and this is where you will choose the Google Sheet file to connect.

Google-Sheet-GSC-Data-Blending step 5

While configuring the join from GSC table choose the metrics like URL Clicks, Impressions, and URL CTR.

From Google Sheet Table choose URL.

Your join configuration would be right outer. Once you do this in the right side Blended Data you will be able to see all the dimensions being added.

997a93b5-003d-4f5a-840c-f0be056ae520 step 6

As you can see in the above screenshot we are matching Landing Page with URL from Google Sheet which will act as a mutual party.

Step 5: Configure the Table

While configuring the table in dimension add URL & in metrics add URL Clicks, Impressions, URL CTR.

That’s it you’re done.

End Result: Your Expected Table

Google-Sheet-GSC-Data-Blending end result

You can add a date range drop-down on top of your table to see GSC data for your selected URLs.

Leave a Comment